SQLite metadata migration as of requisis_ReX 2.10

This article describes how to migrate your existing metadata in SQL-Lite format from version requisis_ReX 2.10, for the current version requisis_ReX 2.12.6.

The procedure requires a tool to read the SQL-Lite data and a SQL script that we provide. The procedure must be performed with any SQLite database that you want to continue using.

Necessary data

SQL-Lite Browser: http://sqlitebrowser.org/

• Download the latest version as 32 or 64Bit version and install the program
  SQLite script for migration:

update enumdefvalues set ED_RIFID=(select typedef.RIFID from typedef join enumdef on ((typedef.M_RIFID=enumdef.M_RIFID) AND (typedef.Name=enumdef.Name)) where enumdef.RIFID=enumdefvalues.ED_RIFID)

where (select typedef.RIFID from typedef join enumdef on ((typedef.M_RIFID=enumdef.M_RIFID) AND (typedef.Name=enumdef.Name)) where enumdef.RIFID=enumdefvalues.ED_RIFID) is not null;


update enumdef set RIFID=(select typedef.RIFID from typedef where ((typedef.M_RIFID=enumdef.M_RIFID) AND (typedef.Name=enumdef.Name)))

where (select typedef.RIFID from typedef where ((typedef.M_RIFID=enumdef.M_RIFID) AND (typedef.Name=enumdef.Name))) is not null;


CREATE TABLE IF NOT EXISTS 'enumdef_new' ('M_RIFID' varchar(255) NOT NULL,

'RIFID' varchar(255) NOT NULL,

'Name' varchar(255) NOT NULL,

PRIMARY KEY ('M_RIFID','RIFID'),

CONSTRAINT 'EnumDefF_Key' FOREIGN KEY ('M_RIFID') REFERENCES 'module' ('RIFID') ON DELETE CASCADE,

CONSTRAINT 'EnumDefMRIFIDName_Unique' UNIQUE('M_RIFID','Name')

CONSTRAINT 'enumdefFkTRifid' FOREIGN KEY ('M_RIFID','RIFID') REFERENCES 'typedef' ('M_RIFID','RIFID') ON DELETE CASCADE);


INSERT INTO enumdef_new SELECT * FROM enumdef;


DROP TABLE enumdef;


ALTER TABLE enumdef_new RENAME TO enumdef;


UPDATE metadataversion SET version = '2.11';

commit;

Step-by-step guide

Perform the following steps for migration:

Preparing the SQLite database and settings

1. Open the SQLite database you want to edit with the SQLite browser
2. Switch to the "Edit Pragmas" tab
3. Deactivate the option "Foreign Keys" (Screenshot 1, Step 1)
4. Save the changed settings (Screenshot 1, Step 2)

Executing the data migration

1. Switch to the tab "Execute SQL" (screenshot 2, step 1)
2. Copy the SQLite-Script code to the clipboard
3. Paste the code in the "SQL 1" window in the "SQL Execute" tab (screenshot 2, step 2)
4. Execute the entire code (Screenshot 2, Step 3)

Closing

1. Check in the output if there were any errors (Screenshot 3, Step 1)
2. If there were no errors, save everything (screenshot 3, step 2)
3. After saving you can close the SQLite-Browser and continue working with requisis_ReX as usual.