Test sakai-2.7.2 MySQL conversion scripts

Conversion script requires an alter table statement for email_template_item. The following alter table statement was added:

– https://sakaiproject.atlassian.net/browse/SAK-19448#icft=SAK-19448
alter table EMAIL_TEMPLATE_ITEM modify HTMLMESSAGE LONGTEXT;

See SAK-19448, r97612.

Test no. 3

emailtemplateservice datatype mismatch detected.

ets trunk, 0.4.x, 0.4.6 (saka-2.8.1): EmailTemplate.hbm.xml

<!-- the email template message in html-->
<property name="htmlMessage" type="text">
<column name="HTMLMESSAGE" not-null="false" length="100000000"/>
</property>

ets 0.4.4 (sakai-2.7.1): EmailTemplate.hbm.xml

<!-- the email template message in html-->
<property name="htmlMessage" type="text">
<column name="HTMLMESSAGE" not-null="false" />
</property>

The length attribute was first specified on 4 Nov 2010, https://sakaiproject.atlassian.net/browse/SAK-19448#icft=SAK-19448 r84317. In MySQL the text datatype holds 65,535 characters while LONGTEXT can hold up to 4,294,967,295 or 4GB characters. This explains why the datatype is being set to LONGTEXT in the auto-generated email_template_item table. The 2.7.1 -> 2.7.2 converted table HTMLMESSAGE datatype remains text.

See http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html

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/sakai272autogen --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai271-272convert --baseUsername=sakaiuser --basePassword=sushi

Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai271-272convert
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai272autogen
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:
email_template_item.HTMLMESSAGE
from LONGTEXT to TEXT
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. 2

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/sakai27x-272autogen --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai271-272convert --baseUsername=sakaiuser --basePassword=sushi

Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai271-272convert
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai27x-272autogen
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

Steve Swinsburg fixed the profile2 nullability inconsistencies. See SAK-20926.

First test run looks pretty good. Two profile2 tables contain variations with respect to nullable fields.

2.7.x (auto-generated)
profile_images_external_t.URL_MAIN not null
profile_images_t.RESOURCE_MAIN not null
profile_images_t.RESOURCE_THUMB not null

2.7.1 -> 2.7.x (converted)
profile_images_external_t.URL_MAIN null
profile_images_t.RESOURCE_MAIN null
profile_images_t.RESOURCE_THUMB null

2.7.2 conversion script (current)

/* https://sakaiproject.atlassian.net/browse/PRFL-392#icft=PRFL-392 change row size of image URI columns */
alter table PROFILE_IMAGES_T modify RESOURCE_MAIN text;
alter table PROFILE_IMAGES_T modify RESOURCE_THUMB text;

alter table PROFILE_IMAGES_EXTERNAL_T modify URL_MAIN text;
alter table PROFILE_IMAGES_EXTERNAL_T modify URL_THUMB text;

Modify to

/* https://sakaiproject.atlassian.net/browse/PRFL-392#icft=PRFL-392 change row size of image URI columns */
alter table PROFILE_IMAGES_T modify RESOURCE_MAIN text not null;
alter table PROFILE_IMAGES_T modify RESOURCE_THUMB text not null;

alter table PROFILE_IMAGES_EXTERNAL_T modify URL_MAIN text not null;
alter table PROFILE_IMAGES_EXTERNAL_T modify URL_THUMB text;

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/sakai27x-272autogen --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai271-272convert --baseUsername=sakaiuser --basePassword=sushi

Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai271-272convert
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai27x-272autogen
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:
profile_images_external_t.URL_MAIN
now nullable
profile_images_t.RESOURCE_MAIN
now nullable
profile_images_t.RESOURCE_THUMB
now nullable
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