Howto integrate custom database scripts

Support
#1

Hello dear Cuba Team,

Is there a way to integrate custom database scripts, which will be executed while updating/upgrading process? Is there a naming convention or any other specification for those scripts?

I have read your Documention about Scripts to Create and Update the Database now several times but I am not sure whether only generated scripts are supported. Did you’ve an example?

We would use it to deploy master data like custom objects like user or to maintain the database e.g. to create constraints, indexes, etc.

1 Like
#2

Hi Mike,

Sure you can provide your own DB scripts. When you generate scripts in Studio, you can see the INIT DATA: 30.create-db.sql tab. It’s empty, and Studio never touches its content, so you can add your DDL/DML statements here. Also, you can manually add any number of *.create-db.sql scripts to the db folder, and they will be executed in alphabetical order.

If you need to add something to the update process, click New update script on the UPDATE SCRIPTS tab. Studio will create a file with appropriate prefix in the name, again in order to run the script in alphabetical order.

The documentation you mentioned mostly explains “how it works” and not how to do something with Studio. We know about this shortcoming, will fix it someday.

1 Like
#3

Hi Konstantin,

Thank you for your detailed answer, I have tried it by adding scripts to create additional indexes. It’s working!

For updating a development database it’s easier, there are the known ways such as the studio, the gradle script or even manually. How to be delivered changes to a production environment both initially and incrementally later in lifecycle?

I saw the concept the Execution of Database Scripts by the Server. The documentation also does not cover the deployment scenario in all details. Could you please explain something about the general procedure and the “best practice”, or should be explained with a separate entry.

#4

How to be delivered changes to a production environment both initially and incrementally later in lifecycle?

It’s covered in this section: Execution of Database Scripts by Server - CUBA Platform. Developer’s Manual

  • The following actions should be completed to initialize a new database:

    • Enable the cuba.automaticDatabaseUpdate application property by adding the following line to the local.app.properties file of the Middleware block:

      cuba.automaticDatabaseUpdate = true

      For fast deployment to Tomcat, this file is located in the tomcat/conf/app-core directory. If the file does not exist, create it.

    • Create an empty database corresponding to the URL specified in the data source description in context.xml.

    • Start the application server containing the Middleware block. At application start, the database will be initialized and ready for work.

  • After that, each time when the application server starts, a scripts execution mechanism will compare the set of scripts located in the database scripts directory with the list of already executed scripts registered in the database. If new scripts are found, they will be executed and registered as well. Typically it is enough to include the update scripts in each new application version, and the database will be actualized each time when the application server is restarted.

Of course you can run the database update scripts in production manually using an appropriate tool. For example, a DBA might want to review all scripts before execution and apply them carefully.

1 Like