DatabaseUpdates - Metadata Registry - Trac

Wiki Navigation

Database Update Scripts

August 08, 2008

ALTER TABLE `reg_schema_property` ADD COLUMN `parent_uri` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL AFTER `is_subproperty_of`;
UPDATE `profile_property` 
SET `label`='description'
WHERE `id` = 3;
COMMIT;

January 2/2009 -- Add ARC support tables

CREATE TABLE `arc_g2t` (
  `g` MEDIUMINT(8) UNSIGNED NOT NULL,
  `t` MEDIUMINT(8) UNSIGNED NOT NULL,
  UNIQUE KEY `gt` (`g`, `t`),
  KEY `tg` (`t`, `g`),
  KEY `g` (`g`),
  KEY `t` (`t`)) ENGINE=InnoDB COMMENT='InnoDB free: 8192 kB' CHECKSUM=0 DELAY_KEY_WRITE=1 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_unicode_ci' INSERT_METHOD=NO;

CREATE TABLE `arc_id2val` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL,
  `misc` TINYINT(1) NOT NULL DEFAULT '0',
  `val` TEXT COLLATE utf8_unicode_ci NOT NULL,
  `val_type` TINYINT(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`, `val_type`),
  KEY `v` (`val`(64)),
  KEY `id_2` (`id`)) ENGINE=InnoDB COMMENT='InnoDB free: 8192 kB' CHECKSUM=0 DELAY_KEY_WRITE=1 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_unicode_ci' INSERT_METHOD=NO;

CREATE TABLE `arc_o2val` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL,
  `cid` MEDIUMINT(8) UNSIGNED NOT NULL,
  `misc` TINYINT(1) NOT NULL DEFAULT '0',
  `val` TEXT COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `cid` (`cid`),
  KEY `v` (`val`(64))) ENGINE=InnoDB COMMENT='InnoDB free: 8192 kB' CHECKSUM=0 DELAY_KEY_WRITE=1 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_unicode_ci' INSERT_METHOD=NO;

CREATE TABLE `arc_s2val` (
  `id` MEDIUMINT(8) UNSIGNED NOT NULL,
  `cid` MEDIUMINT(8) UNSIGNED NOT NULL,
  `misc` TINYINT(1) NOT NULL DEFAULT '0',
  `val` TEXT COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `cid` (`cid`),
  KEY `v` (`val`(64))) ENGINE=InnoDB COMMENT='InnoDB free: 8192 kB' CHECKSUM=0 DELAY_KEY_WRITE=1 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_unicode_ci' INSERT_METHOD=NO;

CREATE TABLE `arc_setting` (
  `k` CHAR(32) COLLATE utf8_unicode_ci NOT NULL,
  `val` TEXT COLLATE utf8_unicode_ci NOT NULL,
  UNIQUE KEY `k` (`k`)) ENGINE=InnoDB COMMENT='InnoDB free: 8192 kB' CHECKSUM=0 DELAY_KEY_WRITE=1 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_unicode_ci' INSERT_METHOD=NO;

CREATE TABLE `arc_triple` (
  `t` MEDIUMINT(8) UNSIGNED NOT NULL,
  `s` MEDIUMINT(8) UNSIGNED NOT NULL,
  `p` MEDIUMINT(8) UNSIGNED NOT NULL,
  `o` MEDIUMINT(8) UNSIGNED NOT NULL,
  `o_lang_dt` MEDIUMINT(8) UNSIGNED NOT NULL,
  `o_comp` CHAR(35) COLLATE utf8_unicode_ci NOT NULL,
  `s_type` TINYINT(1) NOT NULL DEFAULT '0',
  `o_type` TINYINT(1) NOT NULL DEFAULT '0',
  `misc` TINYINT(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `t` (`t`),
  KEY `spo` (`s`, `p`, `o`),
  KEY `os` (`o`, `s`),
  KEY `po` (`p`, `o`),
  KEY `misc` (`misc`),
  KEY `s` (`s`),
  KEY `p` (`p`),
  KEY `o` (`o`),
  KEY `o_lang_dt` (`o_lang_dt`)) ENGINE=InnoDB COMMENT='InnoDB free: 8192 kB' CHECKSUM=0 DELAY_KEY_WRITE=1 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'utf8'
COLLATE 'utf8_unicode_ci' INSERT_METHOD=NO;

January 2, 2009 -- new columns

ALTER TABLE `reg_schema` ADD COLUMN `ns_type` CHAR(6) COLLATE utf8_general_ci NOT NULL DEFAULT 'slash';
ALTER TABLE `reg_schema_property` ADD COLUMN `domain` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL;
ALTER TABLE `reg_schema_property` ADD COLUMN `range` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL;
ALTER TABLE `reg_schema_property` MODIFY COLUMN `type` ENUM('property','subproperty','class','subclass') NOT NULL;

January 2, 2009 -- update Foreign Key constraints to support cascading deletes

ALTER TABLE `reg_concept` DROP FOREIGN KEY `reg_concept_FK_3`;
ALTER TABLE `reg_concept` ADD CONSTRAINT `reg_concept_FK_3` FOREIGN KEY (`pref_label_id`) REFERENCES `reg_concept_property` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE `reg_concept_property` DROP FOREIGN KEY `reg_concept_property_fk4`;
ALTER TABLE `reg_concept_property` ADD CONSTRAINT `reg_concept_property_fk4` FOREIGN KEY (`related_concept_id`) REFERENCES `reg_concept` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE `reg_concept_property` DROP FOREIGN KEY `reg_concept_property_fk2`;
ALTER TABLE `reg_concept_property` ADD CONSTRAINT `reg_concept_property_fk2` FOREIGN KEY (`scheme_id`) REFERENCES `reg_vocabulary` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE `reg_concept_property` DROP FOREIGN KEY `reg_concept_property_fk3`;
ALTER TABLE `reg_concept_property` ADD CONSTRAINT `reg_concept_property_fk3` FOREIGN KEY (`status_id`) REFERENCES `reg_lookup` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `reg_concept_property_history` DROP FOREIGN KEY `reg_concept_property_history_FK_1`;
ALTER TABLE `reg_concept_property_history` ADD CONSTRAINT `reg_concept_property_history_FK_1` FOREIGN KEY (`concept_property_id`) REFERENCES `reg_concept_property` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE `reg_schema` DROP FOREIGN KEY `schema_profile_fk`;
ALTER TABLE `reg_schema` ADD CONSTRAINT `schema_profile_fk` FOREIGN KEY (`profile_id`) REFERENCES `profile` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `reg_schema` DROP FOREIGN KEY `schema_status_fk`;
ALTER TABLE `reg_schema` ADD CONSTRAINT `schema_status_fk` FOREIGN KEY (`status_id`) REFERENCES `reg_status` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property` DROP FOREIGN KEY `reg_schema_property_fk3`;
ALTER TABLE `reg_schema_property` ADD CONSTRAINT `reg_schema_property_fk3` FOREIGN KEY (`is_subproperty_of`) REFERENCES `reg_schema_property` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property` DROP FOREIGN KEY `reg_schema_property_fk2`;
ALTER TABLE `reg_schema_property` ADD CONSTRAINT `reg_schema_property_fk2` FOREIGN KEY (`schema_id`) REFERENCES `reg_schema` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property_element` DROP FOREIGN KEY `reg_schema_property_property_fk2`;
ALTER TABLE `reg_schema_property_element` ADD CONSTRAINT `reg_schema_property_property_fk2` FOREIGN KEY (`schema_property_id`) REFERENCES `reg_schema_property` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property_element` DROP FOREIGN KEY `reg_schema_property_element_fk`;
ALTER TABLE `reg_schema_property_element` ADD CONSTRAINT `reg_schema_property_element_fk` FOREIGN KEY (`profile_property_id`) REFERENCES `profile_property` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property_element` DROP FOREIGN KEY `reg_schema_property_property_fk3`;
ALTER TABLE `reg_schema_property_element` ADD CONSTRAINT `reg_schema_property_property_fk3` FOREIGN KEY (`related_schema_property_id`) REFERENCES `reg_schema_property` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property_element_history` DROP FOREIGN KEY `reg_schema_property_element_history_fk3`;
ALTER TABLE `reg_schema_property_element_history` ADD CONSTRAINT `reg_schema_property_element_history_fk3` FOREIGN KEY (`schema_id`) REFERENCES `reg_schema` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property_element_history` DROP FOREIGN KEY `reg_schema_property_element_history_fk1`;
ALTER TABLE `reg_schema_property_element_history` ADD CONSTRAINT `reg_schema_property_element_history_fk1` FOREIGN KEY (`schema_property_element_id`) REFERENCES `reg_schema_property_element` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property_element_history` DROP FOREIGN KEY `reg_schema_property_element_history_fk2`;
ALTER TABLE `reg_schema_property_element_history` ADD CONSTRAINT `reg_schema_property_element_history_fk2` FOREIGN KEY (`schema_property_id`) REFERENCES `reg_schema_property` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property_element_history` DROP FOREIGN KEY `reg_schema_property_element_history_fk4`;
ALTER TABLE `reg_schema_property_element_history` ADD CONSTRAINT `reg_schema_property_element_history_fk4` FOREIGN KEY (`related_schema_property_id`) REFERENCES `reg_schema_property` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE `reg_schema_property_element_history` DROP FOREIGN KEY `reg_schema_property_element_history_fk6`;
ALTER TABLE `reg_schema_property_element_history` ADD CONSTRAINT `reg_schema_property_element_history_fk6` FOREIGN KEY (`profile_property_id`) REFERENCES `profile_property` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

Augusr 2, 2009 -- add columns to property tables to support extended output and display

ALTER TABLE `profile_property` ADD COLUMN `is_in_class_picklist` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'boolean - is in the property picklist';
ALTER TABLE `profile_property` ADD COLUMN `is_in_property_picklist` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'boolean - is in the property picklist';
ALTER TABLE `profile_property` ADD COLUMN `is_in_rdf` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'boolean - should this display in the RDF';
ALTER TABLE `profile_property` ADD COLUMN `is_in_xsd` TINYINT(1) NOT NULL DEFAULT '1' COMMENT 'boolean - should this display in the XSD';
ALTER TABLE `profile_property` ADD COLUMN `is_attribute` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'boolean - is this an attribute? attribute\'s aren\'t editable outside the main form';
ALTER TABLE `profile_property` ADD COLUMN `has_language` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Boolean that determines whether language attribute is displayed for this property';

ALTER TABLE `reg_schema_property` ADD COLUMN `parent_uri` VARCHAR(255) COLLATE utf8_general_ci DEFAULT NULL;
ALTER TABLE `reg_schema_property` ADD COLUMN `is_deprecated` TINYINT(1) DEFAULT NULL COMMENT 'Boolean. Has this class/property been deprecated';

August 2, 2009 -- update profile data for metadata schema

SET FOREIGN_KEY_CHECKS=0;

UPDATE `profile` 
SET `agent_id` = 58, `created_by` = 36, `updated_by` = 36, `name` = 'NSDL Registry Schema', `uri` = 'http://registry/uri/profile/registryschema', `base_domain` = 'http://registry/uri/profile/registryschema' 
WHERE `id` = 1;
COMMIT;


UPDATE `profile_property` 
SET `created_by` = 36, `updated_by` = 36, `uri` = 'reg:name', `is_in_picklist` = 0, `is_in_class_picklist` = 0, `is_in_property_picklist` = 0, `is_attribute` = 1, `has_language` = 1 
WHERE `id` = 1;
UPDATE `profile_property` 
SET `created_by` = 36, `updated_by` = 36, `uri` = 'rdfs:label', `is_singleton` = 0, `has_language` = 1 
WHERE `id` = 2;
UPDATE `profile_property` 
SET `created_by` = 36, `updated_by` = 36, `label` = 'description', `uri` = 'skos:definition', `has_language` = 1 
WHERE `id` = 3;
UPDATE `profile_property` 
SET `created_by` = 36, `updated_by` = 36, `uri` = 'rdf:type', `is_in_picklist` = 0, `is_in_class_picklist` = 0, `is_in_property_picklist` = 0, `is_attribute` = 1 
WHERE `id` = 4;
UPDATE `profile_property` 
SET `created_by` = 36, `updated_by` = 36, `uri` = 'rdfs:comment', `has_language` = 1 
WHERE `id` = 5;
UPDATE `profile_property` 
SET `created_by` = 36, `updated_by` = 36, `label` = 'subpropertyOf', `uri` = 'rdfs:subPropertyOf', `is_reciprocal` = 0, `is_in_picklist` = 0, `is_in_class_picklist` = 0, `inverse_profile_property_id` = 8 
WHERE `id` = 6;
UPDATE `profile_property` 
SET `created_by` = 36, `updated_by` = 36, `uri` = 'skos:scopeNote', `has_language` = 1 
WHERE `id` = 7;
UPDATE `profile_property` 
SET `created_by` = 36, `updated_by` = 36, `uri` = 'reg:hasSubproperty', `is_reciprocal` = 0, `is_in_picklist` = 0, `is_in_class_picklist` = 0, `is_attribute` = 1, `inverse_profile_property_id` = 6 
WHERE `id` = 8;
INSERT INTO `profile_property` (`id`, `created_at`, `updated_at`, `deleted_at`, `created_by`, `updated_by`, `deleted_by`, `profile_id`, `name`, `label`, `type`, `uri`, `status_id`, `language`, `display_order`, `picklist_order`, `examples`, `is_required`, `is_reciprocal`, `is_singleton`, `is_in_picklist`, `is_in_class_picklist`, `is_in_property_picklist`, `is_in_rdf`, `is_in_xsd`, `is_attribute`, `has_language`, `inverse_profile_property_id`, `schema_property_id`, `schema_id`)
VALUES (9, '2009-03-07 11:49:27', '2009-03-07 11:49:27', NULL, 36, 36, NULL, 1, 'isSubclassOf', 'subClassOf', 'property', 'rdfs:subClassOf', 1, 'en', 8, 8, '', 0, 0, 0, 0, 1, 0, 1, 1, 0, 0, 10, NULL, NULL);
INSERT INTO `profile_property` (`id`, `created_at`, `updated_at`, `deleted_at`, `created_by`, `updated_by`, `deleted_by`, `profile_id`, `name`, `label`, `type`, `uri`, `status_id`, `language`, `display_order`, `picklist_order`, `examples`, `is_required`, `is_reciprocal`, `is_singleton`, `is_in_picklist`, `is_in_class_picklist`, `is_in_property_picklist`, `is_in_rdf`, `is_in_xsd`, `is_attribute`, `has_language`, `inverse_profile_property_id`, `schema_property_id`, `schema_id`)
VALUES (10, '2009-03-07 11:53:34', '2009-03-07 11:53:34', NULL, 36, 36, NULL, 1, 'hasSubClass', 'hasSubClass', 'property', 'reg:hasSubClass', 1, 'en', 9, 9, NULL, 0, 0, 0, 0, 1, 0, 1, 1, 1, 0, 9, NULL, NULL);
INSERT INTO `profile_property` (`id`, `created_at`, `updated_at`, `deleted_at`, `created_by`, `updated_by`, `deleted_by`, `profile_id`, `name`, `label`, `type`, `uri`, `status_id`, `language`, `display_order`, `picklist_order`, `examples`, `is_required`, `is_reciprocal`, `is_singleton`, `is_in_picklist`, `is_in_class_picklist`, `is_in_property_picklist`, `is_in_rdf`, `is_in_xsd`, `is_attribute`, `has_language`, `inverse_profile_property_id`, `schema_property_id`, `schema_id`)
VALUES (11, '2009-03-07 11:57:15', '2009-03-07 11:57:15', NULL, 36, 36, NULL, 1, 'domain', 'domain', 'property', 'rdfs:domain', 1, 'en', 10, 10, NULL, 0, 0, 0, 1, 0, 1, 1, 1, 0, 0, NULL, NULL, NULL);
INSERT INTO `profile_property` (`id`, `created_at`, `updated_at`, `deleted_at`, `created_by`, `updated_by`, `deleted_by`, `profile_id`, `name`, `label`, `type`, `uri`, `status_id`, `language`, `display_order`, `picklist_order`, `examples`, `is_required`, `is_reciprocal`, `is_singleton`, `is_in_picklist`, `is_in_class_picklist`, `is_in_property_picklist`, `is_in_rdf`, `is_in_xsd`, `is_attribute`, `has_language`, `inverse_profile_property_id`, `schema_property_id`, `schema_id`)
VALUES (12, '2009-03-07 12:01:38', '2009-03-07 12:01:38', NULL, 36, 36, NULL, 1, 'range', 'range', 'property', 'rdfs:range', 1, 'en', 11, 11, NULL, 0, 0, 0, 1, 0, 1, 1, 1, 0, 0, NULL, NULL, NULL);
INSERT INTO `profile_property` (`id`, `created_at`, `updated_at`, `deleted_at`, `created_by`, `updated_by`, `deleted_by`, `profile_id`, `name`, `label`, `type`, `uri`, `status_id`, `language`, `display_order`, `picklist_order`, `examples`, `is_required`, `is_reciprocal`, `is_singleton`, `is_in_picklist`, `is_in_class_picklist`, `is_in_property_picklist`, `is_in_rdf`, `is_in_xsd`, `is_attribute`, `has_language`, `inverse_profile_property_id`, `schema_property_id`, `schema_id`)
VALUES (13, '2009-03-07 12:01:38', '2009-03-07 12:01:38', NULL, 36, 36, NULL, 1, 'uri', 'uri', 'property', 'reg:uri', 1, 'en', 12, 12, NULL, 1, 0, 1, 0, 0, 0, 0, 1, 1, 0, NULL, NULL, NULL);
INSERT INTO `profile_property` (`id`, `created_at`, `updated_at`, `deleted_at`, `created_by`, `updated_by`, `deleted_by`, `profile_id`, `name`, `label`, `type`, `uri`, `status_id`, `language`, `display_order`, `picklist_order`, `examples`, `is_required`, `is_reciprocal`, `is_singleton`, `is_in_picklist`, `is_in_class_picklist`, `is_in_property_picklist`, `is_in_rdf`, `is_in_xsd`, `is_attribute`, `has_language`, `inverse_profile_property_id`, `schema_property_id`, `schema_id`)
VALUES (14, '2009-03-07 12:01:38', '2009-03-07 12:01:38', NULL, 36, 36, NULL, 1, 'statusId', 'status', 'property', 'reg:status', 1, 'en', 12, 12, NULL, 1, 0, 1, 0, 0, 0, 0, 1, 1, 0, NULL, NULL, NULL);
COMMIT;

SET FOREIGN_KEY_CHECKS=1;

August 18, 2009 -- add table for batch ARC 3store update log

CREATE TABLE `reg_batch` (
  `id` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `run_time` DATETIME DEFAULT NULL,
  `run_description` TEXT COLLATE latin1_swedish_ci,
  `object_type` VARCHAR(20) COLLATE latin1_swedish_ci DEFAULT NULL,
  `object_id` INTEGER(11) DEFAULT NULL,
  `event_time` DATETIME DEFAULT NULL,
  `event_type` VARCHAR(20) COLLATE latin1_swedish_ci DEFAULT NULL,
  `event_description` TEXT COLLATE latin1_swedish_ci,
  `registry_uri` VARCHAR(255) COLLATE latin1_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)) ENGINE=InnoDB COMMENT='InnoDB free: 5120 kB' CHECKSUM=0 DELAY_KEY_WRITE=0 PACK_KEYS=0 MIN_ROWS=0 MAX_ROWS=0 ROW_FORMAT=COMPACT CHARACTER SET 'latin1'
COLLATE 'latin1_swedish_ci' INSERT_METHOD=NO;

Download in other formats:

Plain Text