[Portfolio] 2.7.0 conversion scripts: OSP datatype mismatches, missing foreign keys
Beth Kirschner
bkirschn at umich.edu
Fri May 14 11:28:35 PDT 2010
Anthony,
I'm just starting to look into this. The column changes are easy
to resolve -- I'll have to look a little harder at the indices and
foreign key constraints. Both Noah and Chris M are out today, so the
osp team is a bit short-handed.
- 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;
>
>
>
More information about the portfolio
mailing list