[WG: Sakai QA] SAK-19894: sakai-2.8.0 conversion script testing (MySQL 5.1) -- first run

[Steve Swinsburg]

Hi Anthony,

I've committed the Profile2 1.3-1.4 conversion SQL to both the MySQL and Oracle Sakai 2.8.0 conversion scripts.. Let me know if there are any discrepancies, as it's rather large.

cheers,
Steve



On 01/02/2011, at 7:13 AM, Anthony Whyte wrote:

> First Liquibase run comparing an auto.ddl-generated sakai-2.8.0-b04 MySQL 5.1 db against a sakai-2.7.1 MySQL 5.1 db upgraded to 2.8.0 using trunk.  Diff'ing between the two databases reveals an anemic 2.7.1->2.8.0 converted db.  The missing tables, columns, etc. encountered tend to be clustered around the following projects:
> 
> announcement
> jobscheduler
> profile2
> reset-pass (account validator)
> sitestats
> shortenedurl
> 
> In short, the conversion scripts are incomplete at present and constitute a 2.8.0 blocker.  If you are responsible for missing scripts please add them and then let me know when complete so that I can test the scripts again.
> 
> Cheers,
> 
> Anthony
> 
> ___________________________________________
> 
> 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/sakai280convert --username=sakaiuser --password=sushi diff --baseUrl=jdbc:mysql://127.0.0.1:3306/sakai280autogen --baseUsername=sakaiuser --basePassword=sushi
> 
> Diff Results:
> Base Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai280autogen
> Target Database: sakaiuser jdbc:mysql://127.0.0.1:3306/sakai280convert
> 
> Product Name: EQUAL
> Product Version: EQUAL
> 2.8.0 auto.ddl=true Missing Tables:  [EXPECTED -- reports tool removed]
>     report_xsl_file
>     reports_def_xml
>     reports_param
>     reports_report
>     reports_result
> 
> 2.8.0 auto.ddl=true Unexpected Tables: [UNEXPECTED -- conversion scripts missing CREATE TABLE statements]
>     profile_company_profiles_t
>     profile_gallery_images_t
>     profile_images_official_t
>     profile_kudos_t
>     profile_message_participants_t
>     profile_message_threads_t
>     profile_messages_t
>     profile_social_info_t
>     sakai_message_bundle
>     scheduler_trigger_events
>     sst_presences
>     url_randomised_mappings_t
>     validationaccount_item
> 
> Missing Views: NONE
> Unexpected Views: NONE
> 
> 2.8.0 auto.ddl=true Missing Columns:  [UNEXPECTED -- do we need to move data stored in these fields elsewhere?]
>     profile_privacy_t.ACADEMIC_INFO
>     profile_privacy_t.SEARCH
> 
> 2.8.0 auto.ddl=true Unexpected Columns:  [UNEXPECTED -- conversion scripts missing ALTER TABLE...ADD COLUMN statements]
>     announcement_message.MESSAGE_ORDER
>     poll_poll.POLL_IS_PUBLIC
>     profile_preferences_t.EMAIL_MESSAGE_NEW
>     profile_preferences_t.EMAIL_MESSAGE_REPLY
>     profile_preferences_t.SHOW_GALLERY_FEED
>     profile_preferences_t.SHOW_KUDOS
>     profile_preferences_t.USE_OFFICIAL_IMAGE
>     profile_privacy_t.BUSINESS_INFO
>     profile_privacy_t.MESSAGES
>     profile_privacy_t.MY_KUDOS
>     profile_privacy_t.MY_PICTURES
>     profile_privacy_t.SOCIAL_NETWORKING_INFO
>     profile_privacy_t.STAFF_INFO
>     profile_privacy_t.STUDENT_INFO
> 
> 2.8.0 auto.ddl=true Changed Columns: [DATA TYPE MISMATCHES]
>     email_template_item.HTMLMESSAGE
>           from TEXT to LONGTEXT
>     gb_grade_record_t.USER_ENTERED_GRADE
>           from VARCHAR(127) to VARCHAR(255)
>     profile_images_t.RESOURCE_MAIN
>           from VARCHAR(255) to TEXT
>     profile_images_t.RESOURCE_THUMB
>           from VARCHAR(255) to TEXT
> 
> 2.8.0 auto.ddl=true 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)
>     FK6C32ED588EA2574(report_xsl_file.reportDefId ->reports_def_xml.reportDefId)
>     FKA6F2CE9DC896C347(reports_result.reportId, reportId ->reports_report.reportId, reportId)
> 
> 2.8.0 auto.ddl=true 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)
> 
> 2.8.0 auto.ddl=true Missing Primary Keys:  [EXPECTED, reports removed]
>     null on report_xsl_file(reportDefId, xslFileHash)
>     null on reports_def_xml(reportDefId)
>     null on reports_param(paramId)
>     null on reports_report(reportId)
>     null on reports_result(resultId)
> 
> 2.8.0 auto.ddl=true Unexpected Primary Keys: 
>     null on profile_company_profiles_t(ID)
>     null on profile_gallery_images_t(ID)
>     null on profile_images_official_t(USER_UUID)
>     null on profile_kudos_t(USER_UUID)
>     null on profile_message_participants_t(ID)
>     null on profile_message_threads_t(ID)
>     null on profile_messages_t(ID)
>     null on profile_social_info_t(USER_UUID)
>     null on sakai_message_bundle(ID)
>     null on scheduler_trigger_events(uuid)
>     null on sst_presences(ID)
>     null on url_randomised_mappings_t(ID)
>     null on validationaccount_item(id)
> 
> Missing Unique Constraints: NONE
> Unexpected Unique Constraints: NONE
> 
> 2.8.0 auto.ddl=true Missing Indexes: 
>     ANNOUNCEMENT_MESSAGE_CDD unique  on announcement_message(CHANNEL_ID, MESSAGE_DATE, DRAFT)
>     IE_ANNC_MSG_ATTRIB unique  on announcement_message(DRAFT, PUBVIEW, OWNER)
>     user_type_context_idx unique  on mfr_pvt_msg_usr_t(USER_ID, TYPE_UUID, CONTEXT_ID, READ_STATUS)
>     FKD3B8420DC896C347 unique  on reports_param(reportId)
>     FKA6F2CE9DC896C347 unique  on reports_result(reportId)
> 
> 2.8.0 auto.ddl=true Unexpected Indexes:
>     ANNOUNCEMENT_MESSAGE_CDD unique  on announcement_message(CHANNEL_ID, MESSAGE_DATE, MESSAGE_ORDER, DRAFT)
>     IE_ANNC_MSG_ATTRIB unique  on announcement_message(DRAFT, PUBVIEW, OWNER, MESSAGE_ORDER)
>     ASSESSMENTGRADINGID on sam_itemgrading_t(ASSESSMENTGRADINGID, PUBLISHEDITEMID, PUBLISHEDITEMTEXTID, AGENTID, PUBLISHEDANSWERID)
> 
> Missing Sequences: NONE
> Unexpected Sequences: NONE
>