SQL Scripts for Manual Schema Upgrade - IJC 5.12

These scripts are intended to be used only by experienced DB administrators who want to fully control IJC 5.12 schema upgrade by using SQL script. Instant JChem will ask for this upgrade automatically and it must be performed in order to use the schema with IJC 5.12. Preferable way is to upgrade it from IJC. However the scripts may be useful for analyzing the changes to be done.

There are two versions of scripts. One for Oracle and other for MySQL.

This is the Oracle version:

--
-- Upgrade IJC schema from v5.7 to v5.12
--
--
-- IMPORTANT: Run this script in single-user mode
--
-- BACKUP YOUR DATABASE BEFORE STARTING!
--

-- Change IJC_CUSTOM_ITEMS primary key from SCHEMA_ID, ITEM_ID, ID
alter table IJC_CUSTOM_ITEMS drop primary key;
alter table IJC_CUSTOM_ITEMS add primary key (SCHEMA_ID, id);

rename IJC_VIEWS to IJC_VIEWS_BACKUP;
CREATE TABLE IJC_VIEWS (
    DUMMY_COL NUMBER(1)
);

CREATE TABLE IJC_CUSTOM_ITEMS_SHARING (
    SCHEMA_ID              VARCHAR2(32)      NOT NULL,
    CUSTOM_ITEM_ID         VARCHAR2(32)      NOT NULL,
    AUTHORITY              VARCHAR2(50)      NOT NULL,
    PRIV                   VARCHAR2(100)     NOT NULL,
    CONSTRAINT PK_IJC_CUSTOM_ITEMS_SHARING PRIMARY KEY (SCHEMA_ID, CUSTOM_ITEM_ID, AUTHORITY, PRIV),
    CONSTRAINT FK_IJC_CUSTOM_ITEMS_SHARING FOREIGN KEY (SCHEMA_ID, CUSTOM_ITEM_ID) REFERENCES IJC_CUSTOM_ITEMS (SCHEMA_ID, ID) ON DELETE CASCADE,
    CONSTRAINT FK_IJC_C_I_S_AUHORITY FOREIGN KEY (AUTHORITY) REFERENCES IJC_AUTHORITIES (AUTHORITY) ON DELETE CASCADE
);

BEGIN
insert into IJC_CUSTOM_ITEMS (  ID,
                                SCHEMA_ID,
                                ITEM_ID,
                                USERNAME,
                                NAME,
                                DESCRIPTION,
                                ITEM_INDEX,
                                GENERIC_TYPE,
                                "TYPE",
                                CREATED_BY,
                                LAST_CHANGED_BY,
                                CREATED_ON,
                                LAST_CHANGED_ON,
                                data,
                                SHARING)
                      select VIEW_ID,
                             SCHEMA_ID,
                             DATATREE_ID,
                             USERNAME,
                             VIEW_NAME,
                             VIEW_DESCRIPTION,
                             VIEW_INDEX,
                             'VIEW',
                             IMPL_TYPE,
                             USERNAME,
                             USERNAME,
                             sysdate,
                             sysdate,
                             VIEW_CONFIG,
                             SHARING
                          from IJC_VIEWS_BACKUP;

insert into IJC_CUSTOM_ITEMS_SHARING (SCHEMA_ID, CUSTOM_ITEM_ID, AUTHORITY, PRIV)
    select SCHEMA_ID, id, 'ROLE_USER', 'READ'
        from IJC_CUSTOM_ITEMS
        where SHARING >= 2;

insert into IJC_CUSTOM_ITEMS_SHARING (SCHEMA_ID, CUSTOM_ITEM_ID, AUTHORITY, PRIV)
    select SCHEMA_ID, id, 'ROLE_USER', 'COPY'
        from IJC_CUSTOM_ITEMS
        where SHARING >= 1;

UPDATE IJC_SCHEMA
    SET ITEM_VALUE = '5.12.0'
    WHERE GENERIC_TYPE='Database'
        AND IMPL_TYPE='IJC_Default_Implementation_v1_DatabaseVersion';

END;
/

This is the MySQL version:

--
-- Upgrade IJC schema from v5.7 to v5.12
--
--
-- IMPORTANT: Run this script in single-user mode
--
-- BACKUP YOUR DATABASE BEFORE STARTING!
--

-- Change IJC_CUSTOM_ITEMS primary key from SCHEMA_ID, ITEM_ID, ID

alter table IJC_CUSTOM_ITEMS
  add constraint IJC_CUSTOM_ITEMS_UNIQUE unique (SCHEMA_ID, ITEM_ID, id);

alter table IJC_CUSTOM_ITEMS drop primary key;
alter table IJC_CUSTOM_ITEMS add primary key (SCHEMA_ID, id);

alter table IJC_CUSTOM_ITEMS
  add index FK_IJC_CUSTOM_ITEMS_SCHEMA (`SCHEMA_ID`, `ITEM_ID`),
  drop index IJC_CUSTOM_ITEMS_UNIQUE;

rename table IJC_VIEWS to IJC_VIEWS_BACKUP;
CREATE TABLE IJC_VIEWS (
    DUMMY_COL INT
);

CREATE TABLE IJC_CUSTOM_ITEMS_SHARING (
    SCHEMA_ID              VARCHAR(32)      NOT NULL,
    CUSTOM_ITEM_ID         VARCHAR(32)      NOT NULL,
    AUTHORITY              VARCHAR(50)      NOT NULL,
    PRIV                   VARCHAR(100)      NOT NULL,
    CONSTRAINT PK_IJC_CUSTOM_ITEMS_SHARING PRIMARY KEY (SCHEMA_ID, CUSTOM_ITEM_ID, AUTHORITY, PRIV),
    CONSTRAINT FK_IJC_CUSTOM_ITEMS_SHARING FOREIGN KEY (SCHEMA_ID, CUSTOM_ITEM_ID) REFERENCES IJC_CUSTOM_ITEMS (SCHEMA_ID, ID) ON DELETE CASCADE,
    CONSTRAINT FK_IJC_C_I_S_AUHORITY FOREIGN KEY (AUTHORITY) REFERENCES IJC_AUTHORITIES (AUTHORITY) ON DELETE CASCADE
) ENGINE=InnoDB;

set transaction isolation level serializable;
start transaction;

insert into IJC_CUSTOM_ITEMS (ID,
                                SCHEMA_ID,
                                ITEM_ID,
                                USERNAME,
                                NAME,
                                DESCRIPTION,
                                ITEM_INDEX,
                                GENERIC_TYPE,
                                TYPE,
                                CREATED_BY,
                                LAST_CHANGED_BY,
                                CREATED_ON,
                                LAST_CHANGED_ON,
                                data,
                                SHARING)
                      select VIEW_ID,
                                 SCHEMA_ID,
                                 DATATREE_ID,
                                 USERNAME,
                                 VIEW_NAME,
                                 VIEW_DESCRIPTION,
                                 VIEW_INDEX,
                                 'VIEW',
                                 IMPL_TYPE,
                                 USERNAME,
                                 USERNAME,
                                 NOW(),
                                 NOW(),
                                 VIEW_CONFIG,
                                 SHARING
                          FROM IJC_VIEWS_BACKUP;

insert into IJC_CUSTOM_ITEMS_SHARING (SCHEMA_ID, CUSTOM_ITEM_ID, AUTHORITY, PRIV)
    select SCHEMA_ID, id, 'ROLE_USER', 'READ'
        from IJC_CUSTOM_ITEMS
        where SHARING >= 2;

insert into IJC_CUSTOM_ITEMS_SHARING (SCHEMA_ID, CUSTOM_ITEM_ID, AUTHORITY, PRIV)
    select SCHEMA_ID, id, 'ROLE_USER', 'COPY'
        from IJC_CUSTOM_ITEMS
        where SHARING >= 1;

UPDATE IJC_SCHEMA
    SET ITEM_VALUE = '5.12.0'
    WHERE GENERIC_TYPE='Database'
        AND IMPL_TYPE='IJC_Default_Implementation_v1_DatabaseVersion';

commit;