Hi!
Belgium PoisonCentre has decided to standardize on MySQL.
PostgreSQL was rejected because its level of integration with Microsoft
Entity Framework (used for the development of other PoisonCentre
applications) is far behind what MySQL achieves
(MySQL seems to be the only open-source DBMS well integrated with Entity
Framework)
For that reason, I was obliged to modify DSpace for MySQL support.
Here some results that may be of interest to developers:
1) "canonicalize": don't add "`" around identifiers! the result of
canonicalize is used in SQL statements but also as keys for various maps.
2) Sequences: new object ids (AUTO_INCREMENT in MySQL) are created in a
different way (insert with object key "0") than Oracle and PostgreSQL
(DatabaseManager class in DSpace 1.4)
3) DDL: Tables creation ask for a different DDL than PostgreSQL
4) MySQL does not support BOOLEAN SQL type, it uses TINYINT(1).
Converting from PostgreSQL to MySQL must take this into account.
TINYINT must be treated and converted to/from boolean in
DatabaseManager class (DSpace 1.4): look where BOOLEAN is managed.
5) Fields greated than 255 characters are TEXT (LONGVARCHAR) in MySQL
LONGVARCHAR must be treated and converted to/from String in
DatabaseManager class (DSpace 1.4): look where VARCHAR is managed.
6) Care must be taken with the AUTO_INCREMENT counter when converting
the data.
As I forked from DSpace at version 1.4, the issues I state must be
reconciled with current version.
The conversion work was done by Mr. Vehzdin Hamid who makes a three
months internship at PoisonCentre.
I attach the DDL in SQL to create the DSpace 1.4 (without browsing
tables) in MySQL if it helps anyone wishing to add support to MySQL in
current DSpace version.
Wishing you a very nice day!
Christophe
CREATE DATABASE IF NOT EXISTS `bibl2` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `bibl2`;
-- MySQL dump 10.13 Distrib 5.6.10, for Win64 (x86_64)
--
-- Host: localhost Database: bibl2
-- ------------------------------------------------------
-- Server version 5.6.10-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `bitstream`
--
DROP TABLE IF EXISTS `bitstream`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bitstream` (
`bitstream_id` int(11) NOT NULL AUTO_INCREMENT,
`bitstream_format_id` int(11) DEFAULT NULL,
`name` varchar(256) DEFAULT NULL,
`size_bytes` bigint(20) DEFAULT NULL,
`checksum` varchar(64) DEFAULT NULL,
`checksum_algorithm` varchar(32) DEFAULT NULL,
`description` text,
`user_format_description` text,
`source` varchar(256) DEFAULT NULL,
`internal_id` varchar(256) DEFAULT NULL,
`deleted` varchar(5) DEFAULT NULL,
`store_number` int(11) DEFAULT NULL,
`sequence_id` int(11) DEFAULT NULL,
PRIMARY KEY (`bitstream_id`),
KEY `bitstream_bitstream_format_id_fkey` (`bitstream_format_id`),
CONSTRAINT `bitstream_bitstream_format_id_fkey` FOREIGN KEY (`bitstream_format_id`) REFERENCES `bitstreamformatregistry` (`bitstream_format_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `bitstreamformatregistry`
--
DROP TABLE IF EXISTS `bitstreamformatregistry`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bitstreamformatregistry` (
`bitstream_format_id` int(11) NOT NULL AUTO_INCREMENT,
`mimetype` varchar(120) DEFAULT NULL,
`short_description` varchar(128) DEFAULT NULL,
`description` text,
`support_level` int(11) DEFAULT NULL,
`internal` BOOLEAN DEFAULT NULL,
PRIMARY KEY (`bitstream_format_id`),
UNIQUE KEY `bitstreamformatregistry_short_description_key` (`short_description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `bookmark`
--
DROP TABLE IF EXISTS `bookmark`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bookmark` (
`bookmark_id` int(11) NOT NULL AUTO_INCREMENT,
`eperson_id` int(11) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
`tag_value` varchar(128) DEFAULT NULL,
PRIMARY KEY (`bookmark_id`),
KEY `bookmark_idx2` (`item_id`),
KEY `bookmark_idx1` (`eperson_id`),
KEY `bookmark_idx3` (`tag_value`),
CONSTRAINT `bookmark_eperson_id_fkey` FOREIGN KEY (`eperson_id`) REFERENCES `eperson` (`eperson_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `bookmark_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `bundle`
--
DROP TABLE IF EXISTS `bundle`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bundle` (
`bundle_id` int(11) NOT NULL AUTO_INCREMENT,
`mets_bitstream_id` int(11) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL,
`primary_bitstream_id` int(11) DEFAULT NULL,
PRIMARY KEY (`bundle_id`),
KEY `bundle_primary_bitstream_id_fkey` (`primary_bitstream_id`),
KEY `bundle_mets_bitstream_id_fkey` (`mets_bitstream_id`),
CONSTRAINT `bundle_mets_bitstream_id_fkey` FOREIGN KEY (`mets_bitstream_id`) REFERENCES `bitstream` (`bitstream_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `bundle_primary_bitstream_id_fkey` FOREIGN KEY (`primary_bitstream_id`) REFERENCES `bitstream` (`bitstream_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `bundle2bitstream`
--
DROP TABLE IF EXISTS `bundle2bitstream`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bundle2bitstream` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`bundle_id` int(11) DEFAULT NULL,
`bitstream_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `bundle2bitstream_bundle_idx` (`bundle_id`),
KEY `bundle2bitstream_bitstream_id_fkey` (`bitstream_id`),
CONSTRAINT `bundle2bitstream_bitstream_id_fkey` FOREIGN KEY (`bitstream_id`) REFERENCES `bitstream` (`bitstream_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `bundle2bitstream_bundle_id_fkey` FOREIGN KEY (`bundle_id`) REFERENCES `bundle` (`bundle_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `collection`
--
DROP TABLE IF EXISTS `collection`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `collection` (
`collection_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
`short_description` varchar(512) DEFAULT NULL,
`introductory_text` text,
`logo_bitstream_id` int(11) DEFAULT NULL,
`template_item_id` int(11) DEFAULT NULL,
`provenance_description` text,
`license` text,
`copyright_text` text,
`side_bar_text` text,
`workflow_step_1` int(11) DEFAULT NULL,
`workflow_step_2` int(11) DEFAULT NULL,
`workflow_step_3` int(11) DEFAULT NULL,
`submitter` int(11) DEFAULT NULL,
`admin` int(11) DEFAULT NULL,
PRIMARY KEY (`collection_id`),
KEY `collection_workflow_step_2_fkey` (`workflow_step_2`),
KEY `collection_template_item_id_fkey` (`template_item_id`),
KEY `collection_workflow_step_1_fkey` (`workflow_step_1`),
KEY `collection_submitter_fkey` (`submitter`),
KEY `collection_workflow_step_3_fkey` (`workflow_step_3`),
KEY `collection_logo_bitstream_id_fkey` (`logo_bitstream_id`),
KEY `collection_admin_fkey` (`admin`),
CONSTRAINT `collection_admin_fkey` FOREIGN KEY (`admin`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `collection_logo_bitstream_id_fkey` FOREIGN KEY (`logo_bitstream_id`) REFERENCES `bitstream` (`bitstream_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `collection_submitter_fkey` FOREIGN KEY (`submitter`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `collection_template_item_id_fkey` FOREIGN KEY (`template_item_id`) REFERENCES `item` (`item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `collection_workflow_step_1_fkey` FOREIGN KEY (`workflow_step_1`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `collection_workflow_step_2_fkey` FOREIGN KEY (`workflow_step_2`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `collection_workflow_step_3_fkey` FOREIGN KEY (`workflow_step_3`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `collection2item`
--
DROP TABLE IF EXISTS `collection2item`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `collection2item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`collection_id` int(11) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `collection2item_collection_idx` (`collection_id`),
KEY `collection2item_item_id_idx` (`item_id`),
CONSTRAINT `collection2item_collection_id_fkey` FOREIGN KEY (`collection_id`) REFERENCES `collection` (`collection_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `collection2item_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `communities2item`
--
DROP TABLE IF EXISTS `communities2item`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `communities2item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`community_id` int(11) DEFAULT NULL,
`item_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `communities2item_item_id_idx` (`item_id`),
KEY `communities2item_community_id_fkey` (`community_id`),
CONSTRAINT `communities2item_community_id_fkey` FOREIGN KEY (`community_id`) REFERENCES `community` (`community_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `communities2item_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `community`
--
DROP TABLE IF EXISTS `community`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `community` (
`community_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) DEFAULT NULL,
`short_description` varchar(512) DEFAULT NULL,
`introductory_text` text,
`logo_bitstream_id` int(11) DEFAULT NULL,
`copyright_text` text,
`side_bar_text` text,
PRIMARY KEY (`community_id`),
KEY `community_logo_bitstream_id_fkey` (`logo_bitstream_id`),
CONSTRAINT `community_logo_bitstream_id_fkey` FOREIGN KEY (`logo_bitstream_id`) REFERENCES `bitstream` (`bitstream_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `community2collection`
--
DROP TABLE IF EXISTS `community2collection`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `community2collection` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`community_id` int(11) DEFAULT NULL,
`collection_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `community2collection_collection_id_idx` (`collection_id`),
KEY `community2collection_community_id_idx` (`community_id`),
CONSTRAINT `community2collection_collection_id_fkey` FOREIGN KEY (`collection_id`) REFERENCES `collection` (`collection_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `community2collection_community_id_fkey` FOREIGN KEY (`community_id`) REFERENCES `community` (`community_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `community2community`
--
DROP TABLE IF EXISTS `community2community`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `community2community` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_comm_id` int(11) DEFAULT NULL,
`child_comm_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `community2community_child_comm_id_fkey` (`child_comm_id`),
KEY `community2community_parent_comm_id_fkey` (`parent_comm_id`),
CONSTRAINT `community2community_child_comm_id_fkey` FOREIGN KEY (`child_comm_id`) REFERENCES `community` (`community_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `community2community_parent_comm_id_fkey` FOREIGN KEY (`parent_comm_id`) REFERENCES `community` (`community_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `eperson`
--
DROP TABLE IF EXISTS `eperson`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `eperson` (
`eperson_id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(64) DEFAULT NULL,
`password` varchar(64) DEFAULT NULL,
`firstname` varchar(64) DEFAULT NULL,
`lastname` varchar(64) DEFAULT NULL,
`can_log_in` BOOLEAN DEFAULT NULL,
`require_certificate` BOOLEAN DEFAULT NULL,
`self_registered` BOOLEAN DEFAULT NULL,
`last_active` timestamp NULL DEFAULT NULL,
`sub_frequency` int(11) DEFAULT NULL,
`phone` varchar(32) DEFAULT NULL,
`netid` varchar(64) DEFAULT NULL,
PRIMARY KEY (`eperson_id`),
UNIQUE KEY `eperson_email_key` (`email`),
KEY `eperson_email_idx` (`email`),
KEY `eperson_netid_idx` (`netid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `epersongroup`
--
DROP TABLE IF EXISTS `epersongroup`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `epersongroup` (
`eperson_group_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`eperson_group_id`),
UNIQUE KEY `epersongroup_name_key` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `epersongroup2eperson`
--
DROP TABLE IF EXISTS `epersongroup2eperson`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `epersongroup2eperson` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`eperson_group_id` int(11) DEFAULT NULL,
`eperson_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `epersongroup2eperson_group_idx` (`eperson_group_id`),
KEY `epersongroup2eperson_eperson_id_fkey` (`eperson_id`),
CONSTRAINT `epersongroup2eperson_eperson_group_id_fkey` FOREIGN KEY (`eperson_group_id`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `epersongroup2eperson_eperson_id_fkey` FOREIGN KEY (`eperson_id`) REFERENCES `eperson` (`eperson_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `epersongroup2workspaceitem`
--
DROP TABLE IF EXISTS `epersongroup2workspaceitem`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `epersongroup2workspaceitem` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`eperson_group_id` int(11) DEFAULT NULL,
`workspace_item_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `epersongroup2workspaceitem_eperson_group_id_fkey` (`eperson_group_id`),
KEY `epersongroup2workspaceitem_workspace_item_id_fkey` (`workspace_item_id`),
CONSTRAINT `epersongroup2workspaceitem_eperson_group_id_fkey` FOREIGN KEY (`eperson_group_id`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `epersongroup2workspaceitem_workspace_item_id_fkey` FOREIGN KEY (`workspace_item_id`) REFERENCES `workspaceitem` (`workspace_item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `fileextension`
--
DROP TABLE IF EXISTS `fileextension`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `fileextension` (
`file_extension_id` int(11) NOT NULL AUTO_INCREMENT,
`bitstream_format_id` int(11) DEFAULT NULL,
`extension` varchar(16) DEFAULT NULL,
PRIMARY KEY (`file_extension_id`),
KEY `fileextension_bitstream_format_id_fkey` (`bitstream_format_id`),
CONSTRAINT `fileextension_bitstream_format_id_fkey` FOREIGN KEY (`bitstream_format_id`) REFERENCES `bitstreamformatregistry` (`bitstream_format_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `group2group`
--
DROP TABLE IF EXISTS `group2group`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `group2group` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `group2group_child_id_fkey` (`child_id`),
KEY `group2group_parent_id_fkey` (`parent_id`),
CONSTRAINT `group2group_child_id_fkey` FOREIGN KEY (`child_id`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `group2group_parent_id_fkey` FOREIGN KEY (`parent_id`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `group2groupcache`
--
DROP TABLE IF EXISTS `group2groupcache`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `group2groupcache` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`child_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `group2groupcache_parent_id_fkey` (`parent_id`),
KEY `group2groupcache_child_id_fkey` (`child_id`),
CONSTRAINT `group2groupcache_child_id_fkey` FOREIGN KEY (`child_id`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `group2groupcache_parent_id_fkey` FOREIGN KEY (`parent_id`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `handle`
--
DROP TABLE IF EXISTS `handle`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `handle` (
`handle_id` int(11) NOT NULL AUTO_INCREMENT,
`handle` varchar(255) DEFAULT NULL,
`resource_type_id` int(11) DEFAULT NULL,
`resource_id` int(11) DEFAULT NULL,
PRIMARY KEY (`handle_id`),
UNIQUE KEY `handle_handle_key` (`handle`),
KEY `handle_handle_idx` (`handle`),
KEY `handle_resource_id_and_type_idx` (`resource_id`,`resource_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `item`
--
DROP TABLE IF EXISTS `item`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `item` (
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`submitter_id` int(11) DEFAULT NULL,
`in_archive` BOOLEAN DEFAULT NULL,
`withdrawn` BOOLEAN DEFAULT NULL,
`last_modified` timestamp NULL DEFAULT NULL,
`owning_collection` int(11) DEFAULT NULL,
PRIMARY KEY (`item_id`),
KEY `item_submitter_id_fkey` (`submitter_id`),
CONSTRAINT `item_submitter_id_fkey` FOREIGN KEY (`submitter_id`) REFERENCES `eperson` (`eperson_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `item2bundle`
--
DROP TABLE IF EXISTS `item2bundle`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `item2bundle` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item_id` int(11) DEFAULT NULL,
`bundle_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `item2bundle_item_idx` (`item_id`),
KEY `item2bundle_bundle_id_fkey` (`bundle_id`),
CONSTRAINT `item2bundle_bundle_id_fkey` FOREIGN KEY (`bundle_id`) REFERENCES `bundle` (`bundle_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `item2bundle_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `itemsbydateaccessioned`
--
DROP TABLE IF EXISTS `itemsbydateaccessioned`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `itemsbydateaccessioned` (
`items_by_date_accessioned_id` int(11) NOT NULL,
`item_id` int(11) DEFAULT NULL,
`date_accessioned` text,
PRIMARY KEY (`items_by_date_accessioned_id`),
KEY `itemsbydateaccessioned_item_id_idx` (`item_id`),
CONSTRAINT `itemsbydateaccessioned_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `metadatafieldregistry`
--
DROP TABLE IF EXISTS `metadatafieldregistry`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `metadatafieldregistry` (
`metadata_field_id` int(11) NOT NULL AUTO_INCREMENT,
`metadata_schema_id` int(11) DEFAULT NULL,
`element` varchar(64) DEFAULT NULL,
`qualifier` varchar(64) DEFAULT NULL,
`scope_note` text,
PRIMARY KEY (`metadata_field_id`),
KEY `metadatafield_schema_idx` (`metadata_schema_id`),
CONSTRAINT `metadatafieldregistry_metadata_schema_id_fkey` FOREIGN KEY (`metadata_schema_id`) REFERENCES `metadataschemaregistry` (`metadata_schema_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `metadataschemaregistry`
--
DROP TABLE IF EXISTS `metadataschemaregistry`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `metadataschemaregistry` (
`metadata_schema_id` int(11) NOT NULL AUTO_INCREMENT,
`namespace` varchar(255) DEFAULT NULL,
`short_id` varchar(32) DEFAULT NULL,
PRIMARY KEY (`metadata_schema_id`),
UNIQUE KEY `metadataschemaregistry_namespace_key` (`namespace`),
UNIQUE KEY `metadataschemaregistry_short_id_key` (`short_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `metadatavalue`
--
DROP TABLE IF EXISTS `metadatavalue`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `metadatavalue` (
`metadata_value_id` int(11) NOT NULL AUTO_INCREMENT,
`item_id` int(11) DEFAULT NULL,
`metadata_field_id` int(11) DEFAULT NULL,
`text_value` text,
`text_lang` varchar(24) DEFAULT NULL,
`place` int(11) DEFAULT NULL,
PRIMARY KEY (`metadata_value_id`),
KEY `value_idx` (`metadata_field_id`),
KEY `metadatavalue_item_idx3` (`metadata_field_id`),
KEY `metadatavalue_item_idx` (`item_id`),
KEY `metadatavalue_item_idx2` (`item_id`,`metadata_field_id`),
CONSTRAINT `metadatavalue_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `metadatavalue_metadata_field_id_fkey` FOREIGN KEY (`metadata_field_id`) REFERENCES `metadatafieldregistry` (`metadata_field_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `registrationdata`
--
DROP TABLE IF EXISTS `registrationdata`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `registrationdata` (
`registrationdata_id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(64) DEFAULT NULL,
`token` varchar(48) DEFAULT NULL,
`expires` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`registrationdata_id`),
UNIQUE KEY `registrationdata_email_key` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `resourcepolicy`
--
DROP TABLE IF EXISTS `resourcepolicy`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `resourcepolicy` (
`policy_id` int(11) NOT NULL AUTO_INCREMENT,
`resource_type_id` int(11) DEFAULT NULL,
`resource_id` int(11) DEFAULT NULL,
`action_id` int(11) DEFAULT NULL,
`eperson_id` int(11) DEFAULT NULL,
`epersongroup_id` int(11) DEFAULT NULL,
`start_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
PRIMARY KEY (`policy_id`),
KEY `resourcepolicy_type_id_idx` (`resource_type_id`,`resource_id`),
KEY `resourcepolicy_epersongroup_id_fkey` (`epersongroup_id`),
KEY `resourcepolicy_eperson_id_fkey` (`eperson_id`),
CONSTRAINT `resourcepolicy_epersongroup_id_fkey` FOREIGN KEY (`epersongroup_id`) REFERENCES `epersongroup` (`eperson_group_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `resourcepolicy_eperson_id_fkey` FOREIGN KEY (`eperson_id`) REFERENCES `eperson` (`eperson_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `subscription`
--
DROP TABLE IF EXISTS `subscription`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `subscription` (
`subscription_id` int(11) NOT NULL AUTO_INCREMENT,
`eperson_id` int(11) DEFAULT NULL,
`collection_id` int(11) DEFAULT NULL,
PRIMARY KEY (`subscription_id`),
KEY `subscription_collection_id_fkey` (`collection_id`),
KEY `subscription_eperson_id_fkey` (`eperson_id`),
CONSTRAINT `subscription_collection_id_fkey` FOREIGN KEY (`collection_id`) REFERENCES `collection` (`collection_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `subscription_eperson_id_fkey` FOREIGN KEY (`eperson_id`) REFERENCES `eperson` (`eperson_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `tasklistitem`
--
DROP TABLE IF EXISTS `tasklistitem`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tasklistitem` (
`tasklist_id` int(11) NOT NULL AUTO_INCREMENT,
`eperson_id` int(11) DEFAULT NULL,
`workflow_id` int(11) DEFAULT NULL,
PRIMARY KEY (`tasklist_id`),
KEY `tasklistitem_eperson_id_fkey` (`eperson_id`),
KEY `tasklistitem_workflow_id_fkey` (`workflow_id`),
CONSTRAINT `tasklistitem_eperson_id_fkey` FOREIGN KEY (`eperson_id`) REFERENCES `eperson` (`eperson_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `tasklistitem_workflow_id_fkey` FOREIGN KEY (`workflow_id`) REFERENCES `workflowitem` (`workflow_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `workflowitem`
--
DROP TABLE IF EXISTS `workflowitem`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `workflowitem` (
`workflow_id` int(11) NOT NULL AUTO_INCREMENT,
`item_id` int(11) DEFAULT NULL,
`collection_id` int(11) DEFAULT NULL,
`state` int(11) DEFAULT NULL,
`owner` int(11) DEFAULT NULL,
`multiple_titles` BOOLEAN DEFAULT NULL,
`published_before` BOOLEAN DEFAULT NULL,
`multiple_files` BOOLEAN DEFAULT NULL,
PRIMARY KEY (`workflow_id`),
UNIQUE KEY `workflowitem_item_id_key` (`item_id`),
KEY `workflowitem_collection_id_fkey` (`collection_id`),
KEY `workflowitem_owner_fkey` (`owner`),
CONSTRAINT `workflowitem_collection_id_fkey` FOREIGN KEY (`collection_id`) REFERENCES `collection` (`collection_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `workflowitem_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `workflowitem_owner_fkey` FOREIGN KEY (`owner`) REFERENCES `eperson` (`eperson_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `workspaceitem`
--
DROP TABLE IF EXISTS `workspaceitem`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `workspaceitem` (
`workspace_item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_id` int(11) DEFAULT NULL,
`collection_id` int(11) DEFAULT NULL,
`multiple_titles` BOOLEAN DEFAULT NULL,
`published_before` BOOLEAN DEFAULT NULL,
`multiple_files` BOOLEAN DEFAULT NULL,
`stage_reached` int(11) DEFAULT NULL,
PRIMARY KEY (`workspace_item_id`),
KEY `workspaceitem_collection_id_fkey` (`collection_id`),
KEY `workspaceitem_item_id_fkey` (`item_id`),
CONSTRAINT `workspaceitem_collection_id_fkey` FOREIGN KEY (`collection_id`) REFERENCES `collection` (`collection_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `workspaceitem_item_id_fkey` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2013-04-09 12:14:29
------------------------------------------------------------------------------
Precog is a next-generation analytics platform capable of advanced
analytics on semi-structured data. The platform includes APIs for building
apps and a phenomenal toolset for data science. Developers can use
our toolset for easy data analysis & visualization. Get a free account!
http://www2.precog.com/precogplatform/slashdotnewsletter
_______________________________________________
Dspace-devel mailing list
Dspace-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspace-devel
