[Portfolio] SAK-18505: conversion scripts review: round II (OSP and Quartz scheduler issues remain)
Anthony Whyte
arwhyte at umich.edu
Fri May 14 15:18:30 PDT 2010
Seth has jumped in to review the Oracle scripts while I work on the MySQL 2.7.0 conversion script. I've committed a number of fixes and Karen and Nuno have also made commits. The result is that we've eliminated a missing table (sam), a missing field (polls), several redundant SQL statements (msgcntr, osp), a few bad statements (sam, msgcntr, sitestates) and fixed certain inconsistencies in comments that were triggering script failures. All these changes have been committed to the trunk MySQL 2.7.0 conversion script (they have yet to be merged to 2.7.x.
WORK REMAINING (OSP, QUARTZ, SAM)
There remain datatype mismatches (osp, quartz, sam) and missing/unexpected foreign keys (osp, quartz). The missing indexes in the auto-generated 2.7.0 database is due to a Hibernate bug; this will not be fixed for 2.7.0.
Cheers,
Anth
The review is being tracked here: http://jira.sakaiproject.org/browse/SAK-18505
ROUND II
arwhyte-macbookpro-2010: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/sakai270autogen --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai262to270convert --baseUsername=sakaiuser --basePassword=sushi
Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai262to270convert
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai270autogen
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:
gb_category_t.ENFORCE_POINT_WEIGHTING
from BIT to BIT
gb_category_t.IS_EQUAL_WEIGHT_ASSNS
from BIT to BIT
gb_category_t.IS_UNWEIGHTED
from BIT to BIT
gb_gradable_object_t.IS_NULL_ZERO
from BIT to BIT
gb_gradebook_t.DO_SHOW_ITEM_STATS
from BIT to BIT
gb_gradebook_t.DO_SHOW_MEAN
from BIT to BIT
gb_gradebook_t.DO_SHOW_MEDIAN
from BIT to BIT
gb_gradebook_t.DO_SHOW_MODE
from BIT to BIT
gb_gradebook_t.DO_SHOW_RANK
from BIT to BIT
gb_gradebook_t.IS_EQUAL_WEIGHT_CATS
from BIT to BIT
gb_gradebook_t.IS_SCALED_EXTRA_CREDIT
from BIT to BIT
osp_presentation.isCollab
from BIT to TINYINT
now not null
osp_wizard_page_def.type
now nullable
qrtz_fired_triggers.PRIORITY
from SMALLINT to INT
qrtz_triggers.PRIORITY
from SMALLINT to INT
sam_publishedassessment_t.LASTNEEDRESUBMITDATE
from DATETIME to DATE
Missing Foreign Keys:
FK4EBCD0F5A6286438(osp_scaffolding_levels.scaffolding_id, id, scaffolding_id, scaffolding_id, id, scaffolding_id ->osp_scaffolding.id, id, id, id, id, id)
FK2065879242A62872(osp_workflow.id, id, id, parent_id ->osp_workflow_parent.id, id, id, id)
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:
FK4EBCD0F5A6286438(osp_scaffolding_levels.scaffolding_id, scaffolding_id, scaffolding_id, scaffolding_id ->osp_scaffolding.id, id, id, id)
FK2065879242A62872(osp_workflow.id, id, parent_id ->osp_workflow_parent.id, id, id)
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:
FK529713EAE023FB45 unique on osp_scaffolding_attachments(id)
FK95431263E023FB45 unique on osp_scaffolding_form_defs(id)
Unexpected Indexes:
MFR_EMAIL_CONTEXT_ID_I unique on mfr_email_notification_t(CONTEXT_ID)
MFR_EMAIL_USER_ID_I unique on mfr_email_notification_t(USER_ID)
PROFILE_FRIENDS_FRIEND_UUID_I unique on profile_friends_t(FRIEND_UUID)
PROFILE_FRIENDS_USER_UUID_I unique on profile_friends_t(USER_UUID)
PROFILE_IMAGES_IS_CURRENT_I unique on profile_images_t(IS_CURRENT)
PROFILE_IMAGES_USER_UUID_I unique on profile_images_t(USER_UUID)
SAKAI_PERSON_META_PROPERTY_I unique on sakai_person_meta_t(PROPERTY)
SAKAI_PERSON_META_USER_UUID_I unique on sakai_person_meta_t(USER_UUID)
SST_EVENTS_DATE_IX unique on sst_events(EVENT_DATE)
SST_EVENTS_EVENT_ID_IX unique on sst_events(EVENT_ID)
SST_EVENTS_SITEEVENTUSER_ID_IX unique on sst_events(SITE_ID, EVENT_ID, USER_ID)
SST_EVENTS_SITE_ID_IX unique on sst_events(SITE_ID)
SST_EVENTS_USER_ID_IX unique on sst_events(USER_ID)
SST_PREFERENCES_SITE_ID_IX unique on sst_preferences(SITE_ID)
SST_REPORTS_SITE_ID_IX unique on sst_reports(SITE_ID)
SST_RESOURCES_DATE_IX unique on sst_resources(RESOURCE_DATE)
SST_RESOURCES_RES_ACT_IDX unique on sst_resources(RESOURCE_ACTION)
SST_RESOURCES_SITE_ID_IX unique on sst_resources(SITE_ID)
SST_RESOURCES_USER_ID_IX unique on sst_resources(USER_ID)
SST_SITEACTIVITY_DATE_IX unique on sst_siteactivity(ACTIVITY_DATE)
SST_SITEACTIVITY_EVENT_ID_IX unique on sst_siteactivity(EVENT_ID)
SST_SITEACTIVITY_SITE_ID_IX unique on sst_siteactivity(SITE_ID)
SST_SITEVISITS_DATE_IX unique on sst_sitevisits(VISITS_DATE)
SST_SITEVISITS_SITE_ID_IX unique on sst_sitevisits(SITE_ID)
Missing Sequences: NONE
Unexpected Sequences: NONE
ROUND II: base/target reversed to match original run
arwhyte-macbookpro-2010: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/sakai262to270convert --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai270autogen --baseUsername=sakaiuser --basePassword=sushi
Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai270autogen
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai262to270convert
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:
gb_category_t.ENFORCE_POINT_WEIGHTING
from BIT to BIT
gb_category_t.IS_EQUAL_WEIGHT_ASSNS
from BIT to BIT
gb_category_t.IS_UNWEIGHTED
from BIT to BIT
gb_gradable_object_t.IS_NULL_ZERO
from BIT to BIT
gb_gradebook_t.DO_SHOW_ITEM_STATS
from BIT to BIT
gb_gradebook_t.DO_SHOW_MEAN
from BIT to BIT
gb_gradebook_t.DO_SHOW_MEDIAN
from BIT to BIT
gb_gradebook_t.DO_SHOW_MODE
from BIT to BIT
gb_gradebook_t.DO_SHOW_RANK
from BIT to BIT
gb_gradebook_t.IS_EQUAL_WEIGHT_CATS
from BIT to BIT
gb_gradebook_t.IS_SCALED_EXTRA_CREDIT
from BIT to BIT
osp_presentation.isCollab
from TINYINT to BIT
now nullable
osp_wizard_page_def.type
now not null
qrtz_fired_triggers.PRIORITY
from INT to SMALLINT
qrtz_triggers.PRIORITY
from INT to SMALLINT
sam_publishedassessment_t.LASTNEEDRESUBMITDATE
from DATE to DATETIME
Missing Foreign Keys:
FK4EBCD0F5A6286438(osp_scaffolding_levels.scaffolding_id, scaffolding_id, scaffolding_id, scaffolding_id ->osp_scaffolding.id, id, id, id)
FK2065879242A62872(osp_workflow.id, id, parent_id ->osp_workflow_parent.id, id, id)
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:
FK4EBCD0F5A6286438(osp_scaffolding_levels.scaffolding_id, id, scaffolding_id, scaffolding_id, id, scaffolding_id ->osp_scaffolding.id, id, id, id, id, id)
FK2065879242A62872(osp_workflow.id, id, id, parent_id ->osp_workflow_parent.id, id, id, id)
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:
MFR_EMAIL_CONTEXT_ID_I unique on mfr_email_notification_t(CONTEXT_ID)
MFR_EMAIL_USER_ID_I unique on mfr_email_notification_t(USER_ID)
PROFILE_FRIENDS_FRIEND_UUID_I unique on profile_friends_t(FRIEND_UUID)
PROFILE_FRIENDS_USER_UUID_I unique on profile_friends_t(USER_UUID)
PROFILE_IMAGES_IS_CURRENT_I unique on profile_images_t(IS_CURRENT)
PROFILE_IMAGES_USER_UUID_I unique on profile_images_t(USER_UUID)
SAKAI_PERSON_META_PROPERTY_I unique on sakai_person_meta_t(PROPERTY)
SAKAI_PERSON_META_USER_UUID_I unique on sakai_person_meta_t(USER_UUID)
SST_EVENTS_DATE_IX unique on sst_events(EVENT_DATE)
SST_EVENTS_EVENT_ID_IX unique on sst_events(EVENT_ID)
SST_EVENTS_SITEEVENTUSER_ID_IX unique on sst_events(SITE_ID, EVENT_ID, USER_ID)
SST_EVENTS_SITE_ID_IX unique on sst_events(SITE_ID)
SST_EVENTS_USER_ID_IX unique on sst_events(USER_ID)
SST_PREFERENCES_SITE_ID_IX unique on sst_preferences(SITE_ID)
SST_REPORTS_SITE_ID_IX unique on sst_reports(SITE_ID)
SST_RESOURCES_DATE_IX unique on sst_resources(RESOURCE_DATE)
SST_RESOURCES_RES_ACT_IDX unique on sst_resources(RESOURCE_ACTION)
SST_RESOURCES_SITE_ID_IX unique on sst_resources(SITE_ID)
SST_RESOURCES_USER_ID_IX unique on sst_resources(USER_ID)
SST_SITEACTIVITY_DATE_IX unique on sst_siteactivity(ACTIVITY_DATE)
SST_SITEACTIVITY_EVENT_ID_IX unique on sst_siteactivity(EVENT_ID)
SST_SITEACTIVITY_SITE_ID_IX unique on sst_siteactivity(SITE_ID)
SST_SITEVISITS_DATE_IX unique on sst_sitevisits(VISITS_DATE)
SST_SITEVISITS_SITE_ID_IX unique on sst_sitevisits(SITE_ID)
Unexpected Indexes:
FK529713EAE023FB45 unique on osp_scaffolding_attachments(id)
FK95431263E023FB45 unique on osp_scaffolding_form_defs(id)
Missing Sequences: NONE
Unexpected Sequences: NONE
ROUND I
arwhyte-macbookpro-2010: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/sakai262to270convert --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai270autogen --baseUsername=sakaiuser --basePassword=sushi
Diff Results:
Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai270autogen
Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai262to270convert
Product Name: EQUAL
Product Version: EQUAL
Missing Tables: NONE
Unexpected Tables:
sam_gradingattachment_t
Missing Views: NONE
Unexpected Views: NONE
Missing Columns: NONE
Unexpected Columns:
poll_option.DELETED
Changed Columns:
gb_category_t.ENFORCE_POINT_WEIGHTING
from BIT to BIT
gb_category_t.IS_EQUAL_WEIGHT_ASSNS
from BIT to BIT
gb_category_t.IS_UNWEIGHTED
from BIT to BIT
gb_gradable_object_t.IS_NULL_ZERO
from BIT to BIT
gb_gradebook_t.DO_SHOW_ITEM_STATS
from BIT to BIT
gb_gradebook_t.DO_SHOW_MEAN
from BIT to BIT
gb_gradebook_t.DO_SHOW_MEDIAN
from BIT to BIT
gb_gradebook_t.DO_SHOW_MODE
from BIT to BIT
gb_gradebook_t.DO_SHOW_RANK
from BIT to BIT
gb_gradebook_t.IS_EQUAL_WEIGHT_CATS
from BIT to BIT
gb_gradebook_t.IS_SCALED_EXTRA_CREDIT
from BIT to BIT
osp_presentation.isCollab
from TINYINT to BIT
now nullable
osp_wizard_page_def.type
now not null
qrtz_fired_triggers.PRIORITY
from INT to SMALLINT
qrtz_triggers.PRIORITY
from INT to SMALLINT
sam_publishedassessment_t.LASTNEEDRESUBMITDATE
from DATE to DATETIME
Missing Foreign Keys:
FK4EBCD0F5A6286438(osp_scaffolding_levels.scaffolding_id, scaffolding_id, scaffolding_id, scaffolding_id ->osp_scaffolding.id, id, id, id)
FK2065879242A62872(osp_workflow.id, id, parent_id ->osp_workflow_parent.id, id, id)
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)
FKD4CF5A194D7EA7B3(sam_media_t.ITEMGRADINGID ->sam_itemgrading_t.ITEMGRADINGID)
Unexpected Foreign Keys:
FK4EBCD0F5A6286438(osp_scaffolding_levels.scaffolding_id, id, scaffolding_id, scaffolding_id, id, scaffolding_id ->osp_scaffolding.id, id, id, id, id, id)
FK2065879242A62872(osp_workflow.id, id, id, parent_id ->osp_workflow_parent.id, id, id, id)
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)
FKD4CF5A194D7EA7B3(sam_media_t.ITEMGRADINGID, ITEMGRADINGID ->sam_itemgrading_t.ITEMGRADINGID, ITEMGRADINGID)
Missing Primary Keys: NONE
Unexpected Primary Keys:
null on sam_gradingattachment_t(ATTACHMENTID)
Missing Unique Constraints: NONE
Unexpected Unique Constraints: NONE
Missing Indexes:
MFR_EMAIL_CONTEXT_ID_I unique on mfr_email_notification_t(CONTEXT_ID)
MFR_EMAIL_USER_ID_I unique on mfr_email_notification_t(USER_ID)
PROFILE_FRIENDS_FRIEND_UUID_I unique on profile_friends_t(FRIEND_UUID)
PROFILE_FRIENDS_USER_UUID_I unique on profile_friends_t(USER_UUID)
PROFILE_IMAGES_IS_CURRENT_I unique on profile_images_t(IS_CURRENT)
PROFILE_IMAGES_USER_UUID_I unique on profile_images_t(USER_UUID)
SAKAI_PERSON_META_PROPERTY_I unique on sakai_person_meta_t(PROPERTY)
SAKAI_PERSON_META_USER_UUID_I unique on sakai_person_meta_t(USER_UUID)
SST_EVENTS_DATE_IX unique on sst_events(EVENT_DATE)
SST_EVENTS_EVENT_ID_IX unique on sst_events(EVENT_ID)
SST_EVENTS_SITEEVENTUSER_ID_IX unique on sst_events(SITE_ID, EVENT_ID, USER_ID)
SST_EVENTS_SITE_ID_IX unique on sst_events(SITE_ID)
SST_EVENTS_USER_ID_IX unique on sst_events(USER_ID)
SST_PREFERENCES_SITE_ID_IX unique on sst_preferences(SITE_ID)
SST_REPORTS_SITE_ID_IX unique on sst_reports(SITE_ID)
SST_RESOURCES_DATE_IX unique on sst_resources(RESOURCE_DATE)
SST_RESOURCES_RES_ACT_IDX unique on sst_resources(RESOURCE_ACTION)
SST_RESOURCES_SITE_ID_IX unique on sst_resources(SITE_ID)
SST_RESOURCES_USER_ID_IX unique on sst_resources(USER_ID)
SST_SITEACTIVITY_DATE_IX unique on sst_siteactivity(ACTIVITY_DATE)
SST_SITEACTIVITY_EVENT_ID_IX unique on sst_siteactivity(EVENT_ID)
SST_SITEACTIVITY_SITE_ID_IX unique on sst_siteactivity(SITE_ID)
SST_SITEVISITS_DATE_IX unique on sst_sitevisits(VISITS_DATE)
SST_SITEVISITS_SITE_ID_IX unique on sst_sitevisits(SITE_ID)
Unexpected Indexes:
FK529713EAE023FB45 unique on osp_scaffolding_attachments(id)
FK95431263E023FB45 unique on osp_scaffolding_form_defs(id)
FK28156C6C4D7EA7B3 unique on sam_gradingattachment_t(ITEMGRADINGID)
FKD4CF5A194D7EA7B3 unique on sam_media_t(ITEMGRADINGID)
SITE_ID on sst_preferences(SITE_ID)
Missing Sequences: NONE
Unexpected Sequences: NONE
More information about the portfolio
mailing list