Test sakai-2.8.1 MySQL conversion scripts

Test no. 3. Includes additions:

Luthuli:liquibase-1.9.5 arwhyte$ java -jar ./liquibase-1.9.5.jar --driver=com.mysql.jdbc.Driver --url=jdbc:mysql://127.0.0.1:3306/sakai281autogen --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai280-281convert --baseUsername=sakaiuser --basePassword=sushi

Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai280-281convert
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai281autogen
Product Name: EQUAL
Product Version: EQUAL
Missing Tables: NONE
Unexpected Tables: NONE
Missing Views: NONE
Unexpected Views: NONE
Missing Columns: NONE
Unexpected Columns: NONE
Changed Columns: NONE
Missing Foreign Keys:
qrtz_trigger_listeners_ibfk_1(qrtz_trigger_listeners.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP ->qrtz_triggers.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP)
qrtz_triggers_ibfk_1(qrtz_triggers.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP ->qrtz_job_details.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP)
Unexpected Foreign Keys:
qrtz_trigger_listeners_ibfk_1(qrtz_trigger_listeners.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP ->qrtz_triggers.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP)
qrtz_triggers_ibfk_1(qrtz_triggers.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP ->qrtz_job_details.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP)
Missing Primary Keys: NONE
Unexpected Primary Keys: NONE
Missing Unique Constraints: NONE
Unexpected Unique Constraints: NONE
Missing Indexes: NONE
Unexpected Indexes: NONE
Missing Sequences: NONE
Unexpected Sequences: NONE

Test no. 02:

Luthuli:liquibase-1.9.5 arwhyte$ java -jar ./liquibase-1.9.5.jar --driver=com.mysql.jdbc.Driver --url=jdbc:mysql://127.0.0.1:3306/sakai28x-281autogen --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai280-281convert --baseUsername=sakaiuser --basePassword=sushi

Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai280-281convert
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai28x-281autogen
Product Name: EQUAL
Product Version: EQUAL
Missing Tables: NONE
Unexpected Tables: NONE
Missing Views: NONE
Unexpected Views: NONE
Missing Columns: NONE
Unexpected Columns: NONE
Changed Columns: NONE
Missing Foreign Keys:
qrtz_trigger_listeners_ibfk_1(qrtz_trigger_listeners.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP ->qrtz_triggers.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP)
qrtz_triggers_ibfk_1(qrtz_triggers.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP ->qrtz_job_details.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP)
Unexpected Foreign Keys:
qrtz_trigger_listeners_ibfk_1(qrtz_trigger_listeners.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP ->qrtz_triggers.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP)
qrtz_triggers_ibfk_1(qrtz_triggers.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP ->qrtz_job_details.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP)
Missing Primary Keys: NONE
Unexpected Primary Keys: NONE
Missing Unique Constraints: NONE
Unexpected Unique Constraints: NONE
Missing Indexes: NONE
Unexpected Indexes: NONE
Missing Sequences: NONE
Unexpected Sequences: NONE

Liquibase test no. 01. One field, sakai_message_bundle.DEFAULT_VALUE varies in terms of nullability.

2.8.x
sakai_message_bundle.DEFAULT_VALUE null
2.8.0 -> 2.8.1 (converted)
sakai_message_bundle.DEFAULT_VALUE not null

Hibernate mappings declare the field null; the 2.8.0 conversion scripts (which I did not apply in the test) list the field as not null.

John Bush reports that the conversion script he provided in is incorrect. The field DEFAULT_VALUE should be null.

kernel-1.2.x MessageBundleProperty.hbm.xml
<property name="defaultValue" column="DEFAULT_VALUE" type="string" length="4000" not-null="false" />

kernel-1.3.x (trunk) MessageBundleProperty.hbm.xml
<property name="defaultValue" column="DEFAULT_VALUE" type="string" length="4000" not-null="false" />

2.8.0 conversion scripts:

– table structure for sakai_message_bundle
create table SAKAI_MESSAGE_BUNDLE (
ID bigint(20) not null auto_increment,
MODULE_NAME varchar(255) not null,
BASENAME varchar(255) not null,
PROP_NAME varchar(255) not null,
PROP_VALUE text,
LOCALE varchar(255) not null,
DEFAULT_VALUE text not null,
primary key (ID)
);

2.8.1 conversion scripts:

– SAK-8005/SAK-20560
– – The conversion for in 2.8.0 conversion do not handle the message_order data in the xml clob
update ANNOUNCEMENT_MESSAGE set MESSAGE_ORDER='1', XML=REPLACE(XML, ' subject=', ' message_order="1" subject=') WHERE MESSAGE_ORDER IS NULL;

– use a column type that stores the timezone
alter table SAKAI_CLUSTER change UPDATE_TIME UPDATE_TIME TIMESTAMP;

– mailarchive messages need updating with new field
UPDATE mailarchive_message SET xml = REPLACE(XML, ' mail-from="', ' message_order="1" mail-from="') WHERE xml NOT LIKE '% message_order="1" %';

Luthuli:liquibase-1.9.5 arwhyte$ java -jar ./liquibase-1.9.5.jar --driver=com.mysql.jdbc.Driver --url=jdbc:mysql://127.0.0.1:3306/sakai28x-281autogen --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai280-281convert --baseUsername=sakaiuser --basePassword=sushi

Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai280-281convert
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai28x-281autogen
Product Name: EQUAL
Product Version: EQUAL
Missing Tables: NONE
Unexpected Tables: NONE
Missing Views: NONE
Unexpected Views: NONE
Missing Columns: NONE
Unexpected Columns: NONE
Changed Columns:
sakai_message_bundle.DEFAULT_VALUE
now not null
Missing Foreign Keys:
qrtz_trigger_listeners_ibfk_1(qrtz_trigger_listeners.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP ->qrtz_triggers.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP)
qrtz_triggers_ibfk_1(qrtz_triggers.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP ->qrtz_job_details.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP)
Unexpected Foreign Keys:
qrtz_trigger_listeners_ibfk_1(qrtz_trigger_listeners.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP ->qrtz_triggers.TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_NAME, TRIGGER_GROUP)
qrtz_triggers_ibfk_1(qrtz_triggers.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP ->qrtz_job_details.JOB_NAME, JOB_GROUP, JOB_NAME, JOB_GROUP)
Missing Primary Keys: NONE
Unexpected Primary Keys: NONE
Missing Unique Constraints: NONE
Unexpected Unique Constraints: NONE
Missing Indexes: NONE
Unexpected Indexes: NONE
Missing Sequences: NONE
Unexpected Sequences: NONE