Attachments addon sql script issue

michael.renaud · October 10, 2019, 12:02pm

Hi

Using addon Attachment 0.51 with platform 6.10.12. Generated sql scripts create twice the table ddca_attachment wich prevent creating a database, even after clean the project.

Both following scripts create the table, sadly it seems I have no way to exclude the faulty one with studio as it is does not appear in Database Scripts window.

70-cuba-component-attachable\init\postgres\10.create-db.sql

-- begin DDCA_ATTACHEMENT
create table DDCA_ATTACHEMENT (
    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),
    --
    NAME varchar(255) not null,
    ATTACHABLE varchar(255) not null,
    FILE_ID uuid,
    CATEGORY_ID uuid,
    --
    primary key (ID)
)^
-- end DDCA_ATTACHEMENT
-- begin DDCA_ATTACHMENT_CATEGORY
create table DDCA_ATTACHMENT_CATEGORY (
    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),
    --
    NAME varchar(255) not null,
    CODE varchar(255),
    --
    primary key (ID)
)^
-- end DDCA_ATTACHMENT_CATEGORY

80-busy\init\postgres\01.attachable-create-db.sql => this one seems to be an old version of the addon, as there is no category

-- begin DDCA_ATTACHEMENT
create table DDCA_ATTACHEMENT (
    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),
    --
    NAME varchar(255) not null,
    ATTACHABLE varchar(255) not null,
    FILE_ID uuid,
    --
    primary key (ID)
)^
-- end DDCA_ATTACHEMENT

Regards
Michael

Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\10-cuba\init\postgres\create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\20-reports\init\postgres\create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\50-cuba-component-runtime-diagnose\init\postgres\10.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\50-cuba-component-runtime-diagnose\init\postgres\20.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\50-cuba-component-runtime-diagnose\init\postgres\30.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\50-sec-forgot-password\init\postgres\10.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\50-sec-forgot-password\init\postgres\20.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\50-sec-forgot-password\init\postgres\30.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\60-cuba-component-taggable\init\postgres\10.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\60-cuba-component-taggable\init\postgres\20.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\60-cuba-component-taggable\init\postgres\30.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\70-cuba-component-attachable\init\postgres\10.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\70-cuba-component-attachable\init\postgres\20.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\70-cuba-component-attachable\init\postgres\30.create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\80-busy\init\postgres\01.admintools-create-db.sql
Executing SQL script: C:\dev\studio-projects\busy\modules\core\build\db\80-busy\init\postgres\01.attachable-create-db.sql
Failed to execute: -- begin DDCA_ATTACHEMENT
create table DDCA_ATTACHEMENT (
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),
--
NAME varchar(255) not null,
ATTACHABLE varchar(255) not null,
FILE_ID uuid,
--
primary key (ID)
) because: ERROR: relation "ddca_attachement" already exists

mario · October 10, 2019, 1:07pm

Hi,

i think this is a problem of the Studio feature that it will autogenerate SQL scripts that are not delivered by the addon itself.

I assume what happened is the following:

you enabled the addon with a version prior to 0.4.0 (where Postgres support was not there, meaning no SQL scripts for Postgres have been delivered)
Studio generated therefore the Postgres Script: 80-busy\init\postgres\01.attachable-create-db.sql
you updated at some point to 0.4.0 or higher
now you installed the app to a blank Postgres DB, which requires to add all init scripts.
CUBA tries to run both scripts, which will cause the error

I assume you can just get rid of the 80-busy\init\postgres\01.attachable-create-db.sql file and run the application again, since attachable now contains the correct postgres SQL files.

Bye
Mario

michael.renaud · October 10, 2019, 1:26pm

Hi @mario, thanks for your feedback

Actually I enabled it quite recently, but strangely Studio keeps generating the faulty script even after a clean (including deleting manually the script) I do not know why.

This is what I did (and it worked), once Studio has generated scripts, I deleted the faulty one then launched create database. But each time I clean + generate db scripts it appears again, I have no clue why.

Just to be sure, I checked project conf, build.gradle for an old version, nothing. I use several of your adds-on (which are great by the way) with no trouble.

Michael

iskandarov · October 11, 2019, 5:41pm

Hi @michael.renaud
Thank you for reporting the problem. We were able to reproduce it. So we have created the issue.

iskandarov · October 11, 2019, 5:59pm

@michael.renaud, try to remove the unnecessary scripts from the modules/core/db folder. It should probably solve the problem.

michael.renaud · October 14, 2019, 1:08pm

Hi @iskandarov,

I did remove the faulty script manually to have db updates done, but Studio keeps regenerating it.

Regards
Michael

iskandarov · October 18, 2019, 10:47am

Hi Michael,
Which version of Studio do you use?

Let me explain how it probably works:

Studio generates db-scripts in the modules/core/db/init/postgres folder.
And during the project assembling the scripts are copied to the modules/core/build/db/80-busy/init/postgres
Due to the Studio issue, after the component update, the unnecessary scripts were not deleted from the modules/core/db folder.
But you have deleted them from modules/core/build/db/
And after the next project assembling the scripts are there again.

Ensure you have removed the scripts from modules/core/db, clean and assemble the project.

Please, let me know if I have missed something.

Regards.

michael.renaud · October 22, 2019, 7:26am

HI @iskandarov

Using Studio 6.10.3.

I have following db scripts in the project:

Am i supposed to delete all scripts that are addon related (meaning all scripts except 10/20/30.create-db.sql ?

Michael

iskandarov · October 25, 2019, 8:17am

Yes. It seems it should solve the problem.
If some of the components still have no DB-scripts for Postgres, Studio is able regenerate them. Run CUBA->Generate Database Scripts.
But note, that 10/20/30.create-db.sql might be also renewed in some conditions and if you have customised the scripts (added some data in 30.create-db.sql, for instance), please save a copy before re-generation.

Regards.

michael.renaud · October 28, 2019, 6:19am

Seems to work, thanks @iskandarov