Update Database: problems with scripts

To connect to an existing SQL server 2017 database, I followed the steps in https://github.com/cuba-platform/cuba-vision-clinic/wiki. I got through step 3 and when attempting to launch the application, (step 4), Cuba studio advised that the database needed updating, so I attempted to do this. The scripts generated by the system are problematic. The first round attempted to create required columns which SQL complains about when the table contains data. So I deleted all data in all tables and reran update database. Some of the scripts ran, but then the system errors on a script: C:\Tmp\CubaProjs\ADGrecords\modules\core\build\db\50-ADGrecords\update\mssql\18\180731-2-updateAreapositions05.sql.

This script reads as follows:

alter table "AreaPositions" add constraint FK_AREAPOSITIONS_ON_AREAID foreign key ("AreaID") references "Areas"(\"AreaID\");
create index IDX_AREAPOSITIONS_ON_AREAID on "AreaPositions" ("AreaID");

I can see what’s wrong - the back slashed around AreaID - “Areas”(“AreaID”). So I open the script in notepad, remove the back slashes and rerun update database. Cuba puts the back slashes back in. I can’t figure out how to open DB Manager without deleting existing scripts.

What’s going on here? Are there bugs with this process? How can I open DB Manger to edit the scripts in Cuba Studio? Any suggestions?

I played with this some more and found the one needs to edit the script in the modules/core/db/ folder. So I edited the script in the other location and got past that one. However, further down the line another script failed. Apparently, the update database changed the datatype from nvarchar on one side of a relationship to varchar, but not the other side. So when a subsequent script tried to recreate the relationship it failed because the column types were different.

So I next deleted the database table and figured I’d just have Cuba Studio create a table from scratch. Well that didn’t work as the init scripts put back slashes back in causing syntax errors.

So I next changed the database type to HSQLDB thinking that surely will work - nope. Creating the database failed with a syntax error:
Executing SQL script: C:\Tmp\CubaProjs\ADGrecords\modules\core\build\db\50-ADGrecords\init\hsql\20.create-db.sql
Failed to execute: – begin “AREAPOSITIONS”
alter table “AreaPositions” add constraint FK_AREAPOSITIONS_ON_AREAID foreign key (“AreaID”) references “Areas”(“AreaID”) because: unknown token:
:app-core:createDb FAILED

Please advise.

I think this may be a problem with the migration tool. I just noticed that all the database table and column entries had escaped double quotes around them, .ie “AreaPositions” and “AreaID”. I went in a cleared this up, removing the backslash and double quotes, except for one column that Cuba studio has greyed out-uneditable. This is a string primary key which is weird that integer primary keys are editable. In any event, that didn’t fix the issue either. I then deleted a bunch of foreign key columns and tried running the create database again and the system produced an error referencing a deleted relationship.

1 Like

Hi,
Thank you for reporting the problem.
We have created a YouTrack issue: https://youtrack.cuba-platform.com/issue/STUDIO-4697

As a workaround you can exclude the incorrect scripts in DB manager in Studio.