I have 2 app components - myutil and myic. myic is using myutil as component whereas both of these components are used by another app named myar. when I am trying to generate DB Script, i am getting exception like the 3rd module is trying to create the tables that has already been created by myic. This is preventing me using this feature, is is normal behavior of CUBA by design or I am doing anything not correct?
I recreated the database and getting the same… Note that, I have applied all the DB scripts in MYIC before trying in MYAR.
Script 60-myic/init/postgres/20.create-db.sql has not been applied, running init scripts
Executing SQL script: D:\Studio Projects\ems\myar\modules\core\build\db\60-myic\init\postgres\10.create-db.sql
Failed to execute: -- begin MYIC_MATERIAL_ISSUE_CUSTOMER
create table MYIC_MATERIAL_ISSUE_CUSTOMER (
ID uuid,
VERSION integer not null,
CREATE_TS timestamp,
CREATED_BY varchar(50),
UPDATE_TS timestamp,
UPDATED_BY varchar(50),
DELETE_TS timestamp,
DELETED_BY varchar(50),
--
DOC_NUMBER bigint,
DOC_DATE date,
SHIP_TO_ADDRESS varchar(255),
--
primary key (ID)
) because: ERROR: relation "myic_material_issue_customer" already exists
Failed to execute: insert into SYS_DB_CHANGELOG (SCRIPT_NAME, IS_INIT) values (?, ?) because: ERROR: duplicate key value violates unique constraint "sys_db_changelog_pkey"
Detail: Key (script_name)=(60-myic/update/postgres/16/161014-1-createMaterial.sql) already exists.
:accountsReceivable-core:updateDb FAILED
FAILURE: Build failed with an exception.
* What went wrong:
Execution failed for task ':accountsReceivable-core:updateDb'.
> org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "sys_db_changelog_pkey"
Detail: Key (script_name)=(60-myic/update/postgres/16/161014-1-createMaterial.sql) already exists.
* Try:
Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output.
BUILD FAILED
Total time: 18.059 secs
[12:21:26.146] Task 'updateDb' failed
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "sys_db_changelog_pkey"
Detail: Key (script_name)=(60-myic/update/postgres/16/161014-1-createMaterial.sql) already exists.
I have created demo project regenerated the exception.
Here is the demo projects which are app components:
demoutil
demoic (component: demoutil)
demoar (components: demoutil, demoic)
demoacct (components: demoutil, demoic, demoar)
all component worked ok but project demoacct through exception when I compile:
Total time: 34.449 secs
[00:37:45.164] Unapplied scripts deleted: 161222-2-updateJournal.sql 161222-2-updateJournal01.sql
[00:37:45.188] Updating database jdbc:hsqldb:hsql://localhost/demo
:demoAccounts-core:assembleDbScripts
:demoAccounts-core:updateDb
Executing script D:\Studio Projects\demo\demoacct\modules\core\build\db\70-demoar\update\hsql\16\161222-2-updateCustomerInvoice.sql
Failed to execute: alter table DEMOAR_CUSTOMER_INVOICE add column MATERIAL_ISSUE_CUSTOMER_ID varchar(36) ;
because: object name already exists in statement [alter table DEMOAR_CUSTOMER_INVOICE add column MATERIAL_ISSUE_CUSTOMER_ID varchar(36) ]
:demoAccounts-core:updateDb FAILED
FAILURE: Build failed with an exception.
* What went wrong:
Execution failed for task ':demoAccounts-core:updateDb'.
> java.sql.SQLSyntaxErrorException: object name already exists in statement [alter table DEMOAR_CUSTOMER_INVOICE add column MATERIAL_ISSUE_CUSTOMER_ID varchar(36) ]
* Try:
Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output.
BUILD FAILED
Total time: 5.457 secs
[00:37:51.441] Task 'updateDb' failed
java.sql.SQLSyntaxErrorException: object name already exists in statement [alter table DEMOAR_CUSTOMER_INVOICE add column MATERIAL_ISSUE_CUSTOMER_ID varchar(36) ]
I have attached all those demo projects. I think this is a bug.
You are using the same DB (url=“jdbc:hsqldb:hsql://localhost/demo”) for all your projects it might lead to its inconsistent state.
The demoar/modules/core/db/update/hsql have strange contents. Creation of the MATERIAL_ISSUE_CUSTOMER_ID column is doubled for some reason in two scripts with different names(“161221-2-updateCustomerInvoice”, “161221-2-updateCustomerInvoice”).
There is also “161222-2-updateDemoicMaterialIssueCustomer” in the folder. Could you please explain how it has appeared there?
Assumably, you have incorrectly recreated the DB scripts working with the “demoar” project.
Let me explain how the platform works with DB update scripts.
In your component. When you generate the scripts they are saved in the modules/core/db folder.
When you install the component to Maven, the scripts are packed to the -db.zip artifact, and put to the app-core subfolder in the Maven repository.
When you add the custom component to project and assembleDbScripts Gradle task is executed the scripts from all the components (including CUBA) appear in the core/build/db directory of your project.
When you launch createDB or updateDb Gradle task, scripts from that folder are executed. The following logic is applied:
The createDb task: the DB is dropped and all init scripts are executed. All the update scripts are marked as executed. Note: when we make some changes in the model, update scripts are generated and init scripts are modified, so there is no need to execute update scripts on a newly created database.
The updateDB task: the plugin ensures all the init script from components were applied - if some init script in the component folder was not applied all the init scripts for the component are launched.
After that, the plugin executes not applied update scripts. The executed scripts are registered in the sys_db_changelog table (if the script is in the folder but was not registered it is considered unapplied). As usual, the logic works perfectly.
The sufficient aspect is that scripts from all the components are executed on the database of the extension.
So, when you make some changes in the component, You should:
Ensure that the DB scripts are consistent. Regenerate the scripts, run them on the component database, start the server, look whether entity screens work well.
Reinstall the component to Maven and ensure the -db.zip artifact is new.
Rebuild the Extension project, look whether the External Libraries which correspond to the component, are refreshed.
Update or recreate the database of the Extension project.
Hi
Thanks for your answer. Before going to the other points, could you please clarify that if my components are using the same database then it will not work? Isn’t the model that ithr components will be using the same database? This might be the reason and roadblocks.
Opened componet project demoutil and created DB scripts. I see all are already applied. To eliminate any doubts, I recreated the database demo. Started the server and the application is running as attached.
sorry, i couldn’t finish my response above (pls ignore that).
I followed the same sequence what you explained. However, I noticed that the component is not refreshed in extended project if the extended project is already been open. I was using more than one tab to keep the component and extended open and I think this is the main issue. However, there might have some issue when the component extended project relation is deep i.e. component’s component and it’s component etc… but i shall confirm it when more testing is done.