[WG: Sakai QA] 2.7.0 conversion scripts: OSP datatype mismatches, missing foreign keys

[Beth Kirschner]

I've created a new JIRA (SAK-18506) to track this. Unfortunately, I've  
only been able to fix the problem with the column creation in mysql  
(oracle looks fine). I was not able to resolve the differences in  
indexes and foreign keys without generating a syntax error, so any  
help in this area would be appreciated. The differences are caused by  
how Hibernate creates these tables, and I doubt any of the differences  
are significant. The constraints are enforced at the code level. I'll  
try to solicit some help on Monday, unless someone volunteers :-)

- Beth

On May 13, 2010, at 7:50 PM, Anthony Whyte wrote:

> I'm using Liquibase to compare a MySQL 2.6.2->2.7.0 db updated via  
> the 2.7.0 conversion scripts against an auto-generated 2.7.0  
> database.  DIFF'ing between the two dbs has uncovered some osp  
> object variations.  There is a datatype mismatch, a field no longer  
> NULL, a couple of indexes missing as well as complaints regarding   
> unexpected/missing foreign keys in the conversion script updated  
> database (see below).  The latter complaints look to me to involve  
> liquibase confusion over field naming patterns (e.g., the primary  
> key osp_workflow_parent.id = foreign key osp_workflow.parent_id) and  
> may not be anything to worry about but the datatype mismatch, NULL  
> change and missing indexes are real.
>
> Please have a look at these variations and please update the trunk  
> and 2.7.x conversion scripts (both MySQL and Oracle--Adam will  
> handle DB2) as quickly as possible.  We are delaying the release of  
> 2.7.0-rc01 until all conversion script issues are resolved.
>
> Cheers,
>
> Anth
>
>
>
> 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
>
> Changed Columns  (converted db vs auto-gen db):
>   osp_presentation.isCollab
>         from TINYINT to BIT
>         now nullable
>   osp_wizard_page_def.type
>         now not null
>
> Unexpected Indexes (missing in conv scripts):
>   FK529713EAE023FB45 unique  on osp_scaffolding_attachments(id)
>   FK95431263E023FB45 unique  on osp_scaffolding_form_defs(id)
>
> 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)
>
> 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)
>
> ______________________________________
>
>
> 2.6.2->2.7.0 converted db table structure dump
>
> CREATE TABLE `osp_scaffolding_levels` (
>  `scaffolding_id` varchar(36) COLLATE utf8_bin NOT NULL,
>  `elt` varchar(36) COLLATE utf8_bin NOT NULL,
>  `seq_num` int(11) NOT NULL,
>  PRIMARY KEY (`scaffolding_id`,`seq_num`),
>  KEY `FK4EBCD0F51EFC6CAF` (`elt`),
>  KEY `FK4EBCD0F5A6286438` (`scaffolding_id`),
>  CONSTRAINT `FK4EBCD0F5A6286438` FOREIGN KEY (`scaffolding_id`)  
> REFERENCES `osp_scaffolding` (`id`),
>  CONSTRAINT `FK4EBCD0F51EFC6CAF` FOREIGN KEY (`elt`) REFERENCES  
> `osp_matrix_label` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>
> CREATE TABLE `osp_scaffolding_form_defs` (
>  `id` varchar(36) COLLATE utf8_bin NOT NULL,
>  `form_def_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
>  `seq_num` int(11) NOT NULL,
>  PRIMARY KEY (`id`,`seq_num`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>
> CREATE TABLE `osp_scaffolding_attachments` (
>  `id` varchar(36) COLLATE utf8_bin NOT NULL,
>  `artifact_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
>  `seq_num` int(11) NOT NULL,
>  PRIMARY KEY (`id`,`seq_num`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>
> CREATE TABLE `osp_workflow` (
>  `id` varchar(36) COLLATE utf8_bin NOT NULL,
>  `title` varchar(255) COLLATE utf8_bin DEFAULT NULL,
>  `parent_id` varchar(36) COLLATE utf8_bin NOT NULL,
>  PRIMARY KEY (`id`),
>  KEY `FK2065879242A62872` (`parent_id`),
>  CONSTRAINT `FK2065879242A62872` FOREIGN KEY (`parent_id`)  
> REFERENCES `osp_workflow_parent` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>
>
> 2.7.0 auto-gen db table structure dump
>
> CREATE TABLE `osp_scaffolding_levels` (
>  `scaffolding_id` varchar(36) COLLATE utf8_bin NOT NULL,
>  `elt` varchar(36) COLLATE utf8_bin NOT NULL,
>  `seq_num` int(11) NOT NULL,
>  PRIMARY KEY (`scaffolding_id`,`seq_num`),
>  KEY `FK4EBCD0F51EFC6CAF` (`elt`),
>  KEY `FK4EBCD0F5A6286438` (`scaffolding_id`),
>  CONSTRAINT `FK4EBCD0F5A6286438` FOREIGN KEY (`scaffolding_id`)  
> REFERENCES `osp_scaffolding` (`id`),
>  CONSTRAINT `FK4EBCD0F51EFC6CAF` FOREIGN KEY (`elt`) REFERENCES  
> `osp_matrix_label` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>
> CREATE TABLE `osp_scaffolding_form_defs` (
>  `id` varchar(36) COLLATE utf8_bin NOT NULL,
>  `form_def_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
>  `seq_num` int(11) NOT NULL,
>  PRIMARY KEY (`id`,`seq_num`),
>  KEY `FK95431263E023FB45` (`id`),
>  CONSTRAINT `FK95431263E023FB45` FOREIGN KEY (`id`) REFERENCES  
> `osp_scaffolding` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>
> CREATE TABLE `osp_scaffolding_attachments` (
>  `id` varchar(36) COLLATE utf8_bin NOT NULL,
>  `artifact_id` varchar(255) COLLATE utf8_bin DEFAULT NULL,
>  `seq_num` int(11) NOT NULL,
>  PRIMARY KEY (`id`,`seq_num`),
>  KEY `FK529713EAE023FB45` (`id`),
>  CONSTRAINT `FK529713EAE023FB45` FOREIGN KEY (`id`) REFERENCES  
> `osp_scaffolding` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>
> CREATE TABLE `osp_workflow` (
>  `id` varchar(36) COLLATE utf8_bin NOT NULL,
>  `title` varchar(255) COLLATE utf8_bin DEFAULT NULL,
>  `parent_id` varchar(36) COLLATE utf8_bin NOT NULL,
>  PRIMARY KEY (`id`),
>  KEY `FK2065879242A62872` (`parent_id`),
>  CONSTRAINT `FK2065879242A62872` FOREIGN KEY (`parent_id`)  
> REFERENCES `osp_workflow_parent` (`id`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
>
>
>