[Portfolio] 2.7.0 conversion scripts: OSP datatype mismatches, missing foreign keys
Anthony Whyte
arwhyte at umich.edu
Thu May 13 16:50:51 PDT 2010
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