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

[Berg, Alan]

Hi OSP team,

the changes needed look significant, can you please get into contact with Anthony today as soon as possible.

Alan

Alan Berg
Interim QA Director - The Sakai Foundation

Senior Developer / Quality Assurance
Group Education and Research Services
Central Computer Services
University of Amsterdam

http://home.uva.nl/a.m.berg




-----Original Message-----
From: sakai-qa-bounces at collab.sakaiproject.org on behalf of Anthony Whyte
Sent: Fri 5/14/2010 1:50
To: Kirschner Beth; Noah Botimer
Cc: osp; Sakai QA
Subject: [WG: Sakai QA] 2.7.0 conversion scripts: OSP datatype mismatches,missing foreign keys
 
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;

   
_______________________________________________
sakai-qa mailing list
sakai-qa at collab.sakaiproject.org
http://collab.sakaiproject.org/mailman/listinfo/sakai-qa

TO UNSUBSCRIBE: send email to sakai-qa-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-qa/attachments/20100514/e31dffe3/attachment.html