Skip to content
Snippets Groups Projects
Select Git revision
  • 7660acd7a9ab06557f5451b0f7bd0a007b1f740b
  • main default protected
  • dev protected
  • f-linkahead-rename
  • f-real-id
  • f-filesystem-import
  • f-filesystem-link
  • f-filesystem-directory
  • f-filesystem-core
  • f-filesystem-cleanup
  • f-filesystem-main
  • f-name
  • keep_changes
  • f-permission-checks-2
  • f-mysql8-tests
  • f-retrieve-history
  • t-distinct-parents
  • v8.1.0
  • v8.0.0
  • v7.0.2
  • v7.0.1
  • v7.0.0
  • v6.0.1
  • v6.0.0
  • v5.0.0
  • v4.1.0
  • v4.0.0
  • v3.0
  • v2.0.30
29 results

db_2_0.sql

Blame
    • Code owners
    Assign users and groups as approvers for specific file changes. Learn more.
    db_2_0.sql 14.63 KiB 
    /*
 * ** header v3.0
 * This file is a part of the CaosDB Project.
 *
 * Copyright (C) 2018 Research Group Biomedical Physics,
 * Max-Planck-Institute for Dynamics and Self-Organization Göttingen
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 *
 * ** end header
 */
#-- MySQL batch stack to create the CaosDB database structure, v2.0.1
#-- AUTHOR: Timm Fitschen (timm.fitschen@ds.mpg.de)
#-- DATE: 2015-10-22
#--
#--
#--




#-- *************
#-- Drop database
#-- *************

DROP DATABASE IF EXISTS db_2_0;



#-- ***************
#-- Create database
#-- ***************

CREATE DATABASE IF NOT EXISTS db_2_0 DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_unicode_ci;
USE db_2_0;

#-- ***********************
#-- Create function version
#-- ***********************

CREATE FUNCTION CaosDBVersion() RETURNS VARCHAR(255) DETERMINISTIC 
RETURN 'v2.0.1';

#-- ************************
#-- Create table entities
#-- ************************

CREATE TABLE IF NOT EXISTS entities (
id	 				INT UNSIGNED 		NOT NULL AUTO_INCREMENT PRIMARY KEY 	COMMENT 'Unique identifier.',
name 				VARCHAR(255)		NULL									COMMENT 'Name of the entity.',
description			VARCHAR(255)		NULL									COMMENT 'Description of the entity.',
role				ENUM('RECORDTYPE', 'RECORD', 'FILE', 'DOMAIN', 'PROPERTY', 'USER', 'GROUP', 'UNIT', 'DATATYPE') 
										NULL									COMMENT 'Role of the entity',
acl					INT UNSIGNED		NULL									COMMENT 'Access Control List for the entity.'
#-- , CONSTRAINT `ent_acl_acl_id` FOREIGN KEY (`acl`) REFERENCES `acl` (`id`)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS name_overrides (
    	domain_id INT UNSIGNED,
	entity_id INT UNSIGNED,
	name VARCHAR(255),
	UNIQUE KEY (domain_id, entity_id),
	CONSTRAINT `nameov_d_ent_id` FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`),
	CONSTRAINT `nameov_e_ent_id` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;


#-- ************************************
#-- Create table is-a
#-- ************************************

CREATE TABLE IF NOT EXISTS isa (
child			INT UNSIGNED		NOT NULL								COMMENT 'Child',
parent			INT UNSIGNED		NOT NULL								COMMENT 'Parent',
type			Enum('INHERITANCE', 'SUBTYPING')
									NOT NULL								COMMENT 'Type of is-a relation.',
UNIQUE KEY (child, parent),
CONSTRAINT `parent_entity` FOREIGN KEY (`parent`) REFERENCES `entities` (`id`),
CONSTRAINT `child_entity` FOREIGN KEY (`child`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;



#-- ***************************
#-- Create table reference_data
#-- ***************************

CREATE TABLE IF NOT EXISTS reference_data (
domain_id			INT UNSIGNED		NOT NULL								COMMENT 'Domain.',
entity_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Entity.',
property_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Property.',
value 				INT UNSIGNED		NULL									COMMENT 'Value.',
status			Enum('OBLIGATORY', 'RECOMMENDED', 'SUGGESTED', 'FIX', 'REPLACEMENT') 
										NOT NULL								COMMENT 'Status of this statement.',
pidx TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Index of the property.',
INDEX(entity_id,property_id),
CONSTRAINT `ref_domain_id_entity` FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`),
CONSTRAINT `ref_entity_id_entity` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`),
CONSTRAINT `ref_property_id_entity` FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`),
CONSTRAINT `ref_value_entity` FOREIGN KEY (`value`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;



#-- *************************
#-- Create table integer_data
#-- *************************

CREATE TABLE IF NOT EXISTS integer_data (
domain_id			INT UNSIGNED		NOT NULL								COMMENT 'Domain.',
entity_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Entity.',
property_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Property.',
value 				INT					NULL									COMMENT 'Value.',
status			Enum('OBLIGATORY', 'RECOMMENDED', 'SUGGESTED', 'FIX', 'REPLACEMENT') 
										NOT NULL								COMMENT 'Status of this statement.',
pidx TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Index of the property.',
INDEX(domain_id,entity_id),
CONSTRAINT `int_domain_id_entity` FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`),
CONSTRAINT `int_entity_id_entity` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`),
CONSTRAINT `int_property_id_entity` FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;



#-- ************************
#-- Create table double_data
#-- ************************

    CREATE TABLE IF NOT EXISTS double_data (
domain_id			INT UNSIGNED		NOT NULL								COMMENT 'Domain.',
entity_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Entity.',
property_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Property.',
value 				DOUBLE				NULL									COMMENT 'Value.',
status			Enum('OBLIGATORY', 'RECOMMENDED', 'SUGGESTED', 'FIX', 'REPLACEMENT') 
										NOT NULL								COMMENT 'Status of this statement.',
pidx TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Index of the property.',
INDEX(domain_id,entity_id),
CONSTRAINT `dou_domain_id_entity` FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`),
CONSTRAINT `dou_entity_id_entity` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`),
CONSTRAINT `dou_property_id_entity` FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;


#-- **************************
#-- Create table datetime_data
#-- **************************

CREATE TABLE IF NOT EXISTS datetime_data (
domain_id			INT UNSIGNED		NOT NULL								COMMENT 'Domain.',
entity_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Entity.',
property_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Property.',
value 				DATETIME			NULL									COMMENT 'Value.',
status			Enum('OBLIGATORY', 'RECOMMENDED', 'SUGGESTED', 'FIX', 'REPLACEMENT') 
										NOT NULL								COMMENT 'Status of this statement.',
pidx TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Index of the property.',
INDEX(domain_id,entity_id),
CONSTRAINT `dat_domain_id_entity` FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`),
CONSTRAINT `dat_entity_id_entity` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`),
CONSTRAINT `dat_property_id_entity` FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;



#-- **********************
#-- Create table text_data
#-- **********************

CREATE TABLE IF NOT EXISTS text_data (
domain_id			INT UNSIGNED		NOT NULL								COMMENT 'Domain.',
entity_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Entity.',
property_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Property.',
value 				TEXT				NULL									COMMENT 'Value.',
status			Enum('OBLIGATORY', 'RECOMMENDED', 'SUGGESTED', 'FIX', 'REPLACEMENT') 
										NOT NULL								COMMENT 'Status of this statement.',
pidx TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Index of the property.',
INDEX(domain_id,entity_id),
CONSTRAINT `str_domain_id_entity` FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`),
CONSTRAINT `str_entity_id_entity` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`),
CONSTRAINT `str_property_id_entity` FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;


#-- **********************
#-- Create table data_type
#-- **********************

CREATE TABLE IF NOT EXISTS data_type (
domain_id			INT UNSIGNED		NOT NULL								COMMENT 'Domain.',
entity_id	 		INT UNSIGNED		NOT NULL								COMMENT 'Entity.',
datatype 			INT UNSIGNED		NOT	NULL								COMMENT 'Datatype.',
UNIQUE INDEX(domain_id,entity_id),
CONSTRAINT `dt_domain_id_entity` FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`),
CONSTRAINT `dt_entity_id_entity` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`),
CONSTRAINT `dt_datatype_id_entity` FOREIGN KEY (`datatype`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;



    #-- *******************
#-- Create table files
#-- *******************

CREATE TABLE IF NOT EXISTS files (
file_id				INT UNSIGNED		NOT NULL PRIMARY KEY					COMMENT 'The file\'s ID.',
path				VARCHAR(255)		NOT NULL								COMMENT 'Directory of the file.',
size				BIGINT UNSIGNED		NOT NULL								COMMENT 'Size in kB (oktet bytes).',
hash				BINARY(64)			NOT NULL								COMMENT 'SHA-512 Hash of the file.',
CONSTRAINT `fil_file_id_entity` FOREIGN KEY (`file_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;



#-- ******************
#-- Create table passwords
#-- ******************

CREATE TABLE IF NOT EXISTS passwords (
entity_id INT UNSIGNED NOT NULL PRIMARY KEY COMMENT 'User ID.',
password VARCHAR(255) NOT NULL COMMENT 'Password.',
CONSTRAINT `use_entity_id_entity` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;

#-- *******************
#-- Create table groups
#-- *******************

CREATE TABLE IF NOT EXISTS groups (
user_id INT UNSIGNED NOT NULL,
group_id INT UNSIGNED NOT NULL,
UNIQUE KEY(user_id, group_id)
, CONSTRAINT `user_id_entities_id` FOREIGN KEY (`user_id`) REFERENCES `entities` (`id`)
, CONSTRAINT `group_id_entities_id` FOREIGN KEY (`group_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;


#-- ********************
#-- Create table history
#-- ********************

CREATE TABLE IF NOT EXISTS transaction_log (
transaction         VARCHAR(255)    NOT NULL                                    COMMENT 'Transaction.',
user_id				INT UNSIGNED 	NOT NULL 									COMMENT 'User.',
date 				INT 			NOT NULL 									COMMENT 'Date of transaction.',
time 				MEDIUMINT UNSIGNED NOT NULL									COMMENT 'Time of transaction.',
ns                  INT UNSIGNED NULL                                     COMMENT 'Nanosecond part of transaction time.',
entity_id 			INT UNSIGNED 	NOT NULL            						COMMENT 'Entity ID.',
INDEX (entity_id), INDEX (date, time)
) ENGINE=InnoDB;



#-- ******************
#-- Create table rules
#-- ******************

CREATE TABLE IF NOT EXISTS rules (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
transaction ENUM('INSERT', 'RETRIEVE', 'UPDATE', 'DELETE') NOT NULL,
criterion VARCHAR(255) NOT NULL,
#--ENUM('UNIT_PRESENT', 'ROLE_PRESENT', 'NAME_PRESENT', 'DESC_PRESENT', 'REFID_PRESENT', 'REFID_VALID', 'REFID_ISA_PAR_REFID', 'PROP_PRESENT') NOT NULL,
modus ENUM('MUST', 'SHOULD', 'SHOULDNT', 'MUSTNOT') NOT NULL,
CONSTRAINT `rule_entity_id_entity` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`),
CONSTRAINT `rule_domain_id_entity` FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`)
) ENGINE=InnoDB;


#-- magic stuff
    ALTER TABLE entities AUTO_INCREMENT=100;

INSERT INTO entities (id, name, description, role, acl) VALUES (1,'DOMAIN', 'The default domain.', 'domain', 0);
UPDATE entities SET id=0 where name='domain';
INSERT INTO entities (id, name, description, role, acl) VALUES (1,'RECORDTYPE', 'The default recordtype.', 'recordtype', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (2,'RECORD', 'The default record.', 'record', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (3,'FILE', 'The default file.', 'file', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (4,'PROPERTY', 'The default property.', 'PROPERTY', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (5,'USER', 'The default user.', 'USER', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (6,'UNIT', 'The default unit.', 'UNIT', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (7,'DATATYPE', 'The default datatype.', 'DATATYPE', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (8,'GROUP', 'The default group.', 'GROUP', 0);



INSERT INTO entities (id, name, description, role, acl) VALUES (11,'REFERENCE', 'The default reference data type.', 'DATATYPE', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (12,'INTEGER', 'The default integer data type.', 'DATATYPE', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (13,'DOUBLE', 'The default double data type.', 'DATATYPE', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (14,'TEXT', 'The default text data type.', 'DATATYPE', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (15,'DATETIME', 'The default datetime data type.', 'DATATYPE', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (16,'TIMESPAN', 'The default timespan data type.', 'DATATYPE', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (17,'FILE', 'The default file reference data type.', 'DATATYPE', 0);
#--INSERT INTO data_type (domain_id, entity_id, datatype) VALUES (0, 17, 17);
#--INSERT INTO data_type (domain_id, entity_id, datatype) VALUES (0, 11, 11);

INSERT INTO entities (id, name, description, role, acl) VALUES (20,'name', 'Name of an entity', 'PROPERTY', 0);
INSERT INTO data_type (domain_id, entity_id, datatype) VALUES (0, 20, 14);
INSERT INTO entities (id, name, description, role, acl) VALUES (21,'unit', 'Unit of an entity.', 'PROPERTY', 0); 
INSERT INTO data_type (domain_id, entity_id, datatype) VALUES (0, 21, 14);
INSERT INTO entities (id, name, description, role, acl) VALUES (22,'exponent', 'Unit prefix exponent of an entity.', 'PROPERTY', 0); 
INSERT INTO data_type (domain_id, entity_id, datatype) VALUES (0, 22, 12);
INSERT INTO entities (id, name, description, role, acl) VALUES (24,'description', 'Description of an entity.', 'PROPERTY', 0); 
INSERT INTO data_type (domain_id, entity_id, datatype) VALUES (0, 24, 14);
INSERT INTO entities (id, name, description, role, acl) VALUES (25,'refid', 'Refid of an entity.', 'PROPERTY', 0); 
INSERT INTO data_type (domain_id, entity_id, datatype) VALUES (0, 25, 11);

#--INSERT INTO entities (id, name, description, role, acl) VALUES (23,'role', 'Role of an entity.', 'PROPERTY', 0); 

INSERT INTO entities (id, name, description, role, acl) VALUES (50,'SQLITE', 'The SQLite file data type.', 'DATATYPE', 0);

#-- USER STUFF
INSERT INTO entities (id, name, description, role, acl) VALUES (98, 'heartdb', 'The anonymous user.', 'USER', 0);
INSERT INTO entities (id, name, description, role, acl) VALUES (99, 'administration', 'The admin users.', 'GROUP', 0);

INSERT INTO passwords (entity_id, password) VALUES (98, '37d7bd8a833261b4e4653644ee0a065f522b92b3738ca9ae2cb43a83844bf352c4a59c386a44965997a508c61988c9484c093775027425091d6d3d435c3c0e0c');
INSERT INTO passwords (entity_id, password) VALUES (99, '37d7bd8a833261b4e4653644ee0a065f522b92b3738ca9ae2cb43a83844bf352c4a59c386a44965997a508c61988c9484c093775027425091d6d3d435c3c0e0c');