[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