[WG: Sakai QA] 2.7.0: conversion script testing

[Anthony Whyte]

I've been running a series of DIFF comparisons between a Sakai 2.7.0 MySQL 5.1 database converted from a Sakai 2.6.2 instance against a Sakai 2.7.0 MySQL 5.1 database generated via auto.ddl.  Seth has been doing the same for Oracle.  As issues have been encountered Beth, Chris, Nuno, Karen, Seth and myself have worked to address them. 

We've caught and fixed a missing table, missing fields, missing indexes, a number of datatype mismatches, etc.  The work is tracked here:

The latest test results for MySQL are noted below.  Seth and I are reviewing the missing/unexpected foreign keys for OSP and the Quartz Scheduler (Quartz I believe is a false positive).  The list of indexes NOT generated when creating a 2.7.0 database via auto.ddl involves a bug in the version Hibernate (3.2.7.ga) we use (HHH-1012).  Upgrading to a new version of Hibernate at this point in time in the 2.7.0 release cycle is too risky in the opinion of Alan, Nuno, myself et al.  I plan to highlight it as a known issue in the release notes.

The Oracle DIFF is not as far along as the MySQL comparison in terms of cleanliness.  Seth and I are working on it and Adam is handling DB2.  Once we are satisfied with Oracle (auto.ddl generated vs converted) I'll tag sakai-2.7.0-rc01.

Cheers,

Anth


Conversion script review: http://jira.sakaiproject.org/browse/SAK-18505
Hibernate bug: http://jira.sakaiproject.org/browse/SAK-18510  (see http://opensource.atlassian.com/projects/hibernate/browse/HHH-1012)


____________________________________________
MYSQL test run (no. 5)

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/sakai --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/sakai
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
Missing Foreign Keys: 
     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: 
     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: NONE
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-qa/attachments/20100524/ecce278a/attachment.html