Migration Script Manager
Migration Script Manager
As we developers all know, it's really hard to mantain a database in Adempiere while working with many saparate customizations. We have to deal with trunk / base revision scripts to do the migration of our databases and such. To ease this process, I developed a funcionality into Adempiere that allows the Database changes to be managed in a much easier way.
What changes from actual behaviour:
Actually, the migration scripts have a small header, that just explains why they were made, and who created it. Usually, it has this info as SQL comments in the SQL header. You open up your database manager, and then just apply the migration scripts.
What changes:
The new process here proposed has some differences from the actual process. To apply a migration script, you'll do it directly from Adempiere client, using the System role to fulfill this activity.
The process is separated into 3 basic steps. First, all the new migration scripts will have a new file skeleton, that will be like:
File name : 001-tst.sql
>>>>>>>File starts here<<<<<<<
--BEGINHEADER--
INSERT INTO ad_migrationscript (ad_migrationscript_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, NAME, description, projectname, releaseno, developername, reference, url, filename, status, isApply)
VALUES (?, 0, 0, 'Y', '2008-01-01 01:00:00.0', 0, '2000-01-01 02:00:00.0', 0, 'Test2', 'This is a test script2', 'Migration Scripts Automator', '340', 'fer_luck', '[FR 1234562]', 'http://www.faire.com.br', '001-tst.sql', 'IP', 'N');
--ENDHEADER--
--BEGINMS--
INSERT INTO test(test_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, name, description, help, t_integer, t_number, t_date, t_datetime, c_uom_id, t_qty, c_currency_id, t_amount, c_location_id, account_acct, c_payment_id, m_product_id, c_bpartner_id, m_locator_id, processing, binarydata, processed, characterdata)
VALUES(106, 0, 0, 'Y', '2003-11-26 00:32:53.0', 100, '2000-01-01 00:00:00.0', 0, 'Doh', 'Test record description', 'Test Comment', 10000002, 10000002, NULL, NULL, 100, 10000002, 195, 10000002, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL, 'N', '<html></html>');
INSERT INTO test(test_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby, name, description, help, t_integer, t_number, t_date, t_datetime, c_uom_id, t_qty, c_currency_id, t_amount, c_location_id, account_acct, c_payment_id, m_product_id, c_bpartner_id, m_locator_id, processing, binarydata, processed, characterdata)
VALUES(107, 0, 0, 'Y', '2003-11-26 00:32:53.0', 100, '2000-01-01 00:00:00.0', 0, 'Doh', 'Test record description', 'Test Comment', 10000002, 10000002, NULL, NULL, 100, 10000002, 195, 10000002, NULL, NULL, NULL, NULL, NULL, NULL, 'N', NULL, 'N', '<html></html>');
--ENDMS--
>>>>>>>File ends here<<<<<<<
Note that this new file has some new elements that we didn't use with the old approach. Now we add a --BEGINHEADER-- and --ENDHEADER-- identifiers to tell the info we need to know about each migration script to control how they're applied, and a --BEGINMS-- and --ENDMS-- that identifies the begin and the end of the migration script body.
Now that we have a new migration script, let's apply it against our database. To do that, we have to open Adempiere with the System Role, and then go into the Application Dictionary folder. When in there, click over the Prepare Migration Script process. A popup will ask you where you want to search for migration scripts (it will include all the .sql files found in the directory), you should set a directory and then just press the Ok button. After it's done, it will show you either a sucess message or error message, containing the scripts that couldn't be applied.
