Nuxeo SQL Database Schema Creation
We have to run Nuxeo with db.user and db.password having no table creation rights and we have successfully configured nuxeo but we have to do the following steps:
- Start nuxeo with db.user and db.password having table creation rights. (This step creates all the necessary tables)
- Set noDDL as “true” and added custom plugin having directories' table creation policies as “never”
- Start nuxeo with db.user and db.password having no table creation rights.
Nuxeo starts perfectly fine.
We want to avoid step # 1 (first starting nuxeo with db.user and db.password having table creation rights) So we want to create tables via sql db scripts. Is this possible?
I'm not sure to understand your question. Do you ask our permission to do that ? :D
If you really want, why not :) but you will do exactly the same that what Nuxeo do automatically. Maybe my advices would be
- is to allow table creation just for the first start and remove the right after.
- or you do the initialization of a pre-production instance identical of your production one and dump the data to inject it into the production instance…
But becareful if you do maintenance action (hot fix deployment) or add more feature through studio project or any else action that need the table rights, you may have some instabilities. My real advice will be to let the right.
Hope this will help you :D
So we have now made nuxeo db scripts that includes table creation, stored procedures, triggers, sequences and insert scripts.
Now, we do the following steps and have successfully run nuxeo with user having no table creation rights.
- we execute db scripts
- Give READ, WRITE, UPDATE grants of the schema to user that dont have table creation rights; and create synonyms for that user.
- Set noDDL as "true" and added custom plugin having directories' table creation policies as "never"
- Start nuxeo with user having no table creation rights.
So have successfully avoided step # 1 ;)
But yes, the problem is that if in future nuxeo do any change that have any impact with database schema, so we will have to update our sql script. And so we will have to keep the track of such changes :)
The simplest way is to start Nuxeo once on an empty database, then use a database script generation tool to dump its content and turn it into a SQL script.
For instance using PostgreSQL you can use