Is there a way to make CUBA reset its database scripts entirely from the model?

Hi

Since I work with addons (a few months), and especially when I upgrade their version, I regularly run into trouble with db scripts.

E.g, when an updateDb fails, the failing script is nevertheless stored in sys_db_changelog so afterwards with retries you will have duplicate key exception.

Another example, when generating update scripts in studio I see 3 update scripts to be executed that I validate, and then the updateDb failed. I then see surprisingly that a create script for an add-on has been silently executed without being displayed to me as to be executed, and so removing me the choice of excluding it.

Sometimes ago I learned in this forum that when generating scripts CUBA just make the difference between the java model and the actual database schema.

With that in mind I deleted the content of sys_db_changelog, thinking that CUBA will then analyse the actual schema and generates only needed scripts.

Seems I was wrong, now Studio wants to create “sys_server”, a platform base table already existing when I run updateDb.

It is an homologation database with tons of data, I cannot afford the time to recreate it from scratch.

It would be nice to have this option in Studio “reset database scripts”, that would do exactly that: compare java & db schema and just generate needed update scripts.

Michael

Hi Michael,

when an updateDb fails, the failing script is nevertheless stored in sys_db_changelog

Failing scripts must not be stored. Are you talking about scripts from app components or from the app itself?

I then see surprisingly that a create script for an add-on has been silently executed without being displayed to me as to be executed

This is true, scripts from app components are executed automatically, and they cannot be edited in Studio when working with the app by the obvious reason - they are the part of another project.

It would be nice to have this option in Studio “reset database scripts”, that would do exactly that: compare java & db schema and just generate needed update scripts.

In fact, when you click “Generate DB scripts” in Studio, it does exactly this, but only for the tables having the prefix corresponding to your project namespace. In other words, for the model of your application only.

Thank you for the idea, we’ll think how to implement it. I’ve created an issue.

Thanks Konstantin, this would really help.

I have deleted “sys_db_changelog” and now updateDb wants to create “sys_server” table while it already exists in the schema, so updateDb task should not try to recreate it.

I guess this is because of the “sys” prefix right ? I saw sometime ago that cuba platform is managed likish an application component.

On the implementation side, it could be identical to the “database scripts” Studio window (which is quite handy), but with a subdivision for each namespace/addon.

Also I suggest this subdivision stuff to be applied to all script kinds, because the scripts menu topside of this window (look below) grows rightwards, I have currently 12 addons incl cuba ones, when I reach 15 or 20 this will be an issue.

image

I have deleted “sys_db_changelog” and now updateDb wants to create “sys_server” table while it already exists in the schema, so updateDb task should not try to recreate it.

updateDb task (as well as app server on startup) does not analyze your DB schema in any way, it just executes all scripts available in the db folder of your app if they are not yet registered in the sys_db_changelog table. This is why it executes CUBA scripts too. It’s explained in the docs.

About the visual layout in Studio - we’ll take it into account, thanks.

Thanks @knstvk

In the meantime how can I do this reset ?

For instance, I’ve done the following:

  • extract production database schema
  • create a fresh & empty database with this schema
  • run studio generate scripts

The updated scripts generated are ok, meaning if all scripts I have in front of my eyes are executed (and only them) the production database will have the correct schema.

But when I try to update recreated database I have “error : sys_server already exists”. Behind the scene, it seems that Studio is trying to recreate CUBA tables whereas they already exist.

Is there any way I can make CUBA just run the generated updated scripts ?

Michael

CUBA runs all scripts available in modules/core/build/db (when run from the project source) or tomcat/webapps/app-core/WEB-INF/db (when run on the server startup) that are not registered in SYS_DB_CHANGELOG table.
I suppose the table is empty in your new database, so you should populate it with records related to scripts from CUBA and add-ons. You can get them from an existing database, which can be easily created if you create a new empty project with the same CUBA version and the same set of add-ons.

1 Like