Select Git revision
-
Timm Fitschen authoredTimm Fitschen authored
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
versioning.sql 14.10 KiB
DROP TABLE IF EXISTS archive_reference_data;
DROP TABLE IF EXISTS archive_null_data;
DROP TABLE IF EXISTS archive_text_data;
DROP TABLE IF EXISTS archive_name_data;
DROP TABLE IF EXISTS archive_enum_data;
DROP TABLE IF EXISTS archive_integer_data;
DROP TABLE IF EXISTS archive_double_data;
DROP TABLE IF EXISTS archive_datetime_data;
DROP TABLE IF EXISTS archive_date_data;
DROP TABLE IF EXISTS archive_name_overrides;
DROP TABLE IF EXISTS archive_desc_overrides;
DROP TABLE IF EXISTS archive_data_type;
DROP TABLE IF EXISTS archive_collection_type;
DROP TABLE IF EXISTS archive_query_template_def;
DROP TABLE IF EXISTS archive_files;
DROP TABLE IF EXISTS archive_entities;
DROP TABLE IF EXISTS archive_isa;
DROP TABLE IF EXISTS entity_version;
DROP TABLE IF EXISTS transactions;
CREATE TABLE transactions (
srid VARBINARY(255) PRIMARY KEY,
username VARBINARY(255) NOT NULL,
realm VARBINARY(255) NOT NULL,
seconds BIGINT UNSIGNED NOT NULL,
nanos INT(10) UNSIGNED NOT NULL
) ENGINE=InnoDB;
-- TODO remove ON DELETE CASCADE when feature is stable.
CREATE TABLE entity_version (
entity_id INT UNSIGNED NOT NULL,
hash VARBINARY(255) DEFAULT NULL,
version VARBINARY(255) NOT NULL,
_iversion INT UNSIGNED NOT NULL,
_ipparent INT UNSIGNED NULL,
srid VARBINARY(255) NOT NULL,
PRIMARY KEY (`entity_id`, `_iversion`),
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`srid`) REFERENCES `transactions` (`srid`),
UNIQUE (`entity_id`, `version`)
) ENGINE=InnoDB;
CREATE TABLE archive_isa (
child INT UNSIGNED NOT NULL,
child_iversion INT UNSIGNED NOT NULL,
parent INT UNSIGNED NOT NULL,
FOREIGN KEY (`parent`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`child`, `child_iversion`) REFERENCES `entity_version` (`entity_id`, `_iversion`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_reference_data (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
value INT UNSIGNED NOT NULL,
status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX','REPLACEMENT')
NOT NULL,
pidx INT(10) UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`value`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_null_data (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
NOT NULL,
pidx INT(10) UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_text_data (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
value TEXT NOT NULL,
status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
NOT NULL,
pidx INT(10) UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_name_data (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
value VARCHAR(255) NOT NULL,
status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
NOT NULL,
pidx INT(10) UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
KEY (`value`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_enum_data (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
value VARBINARY(255) NOT NULL,
status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
NOT NULL,
pidx INT(10) UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_integer_data (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
value BIGINT NOT NULL,
status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
NOT NULL,
pidx INT(10) UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
unit_sig BIGINT DEFAULT NULL,
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_double_data (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
value DOUBLE NOT NULL,
status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
NOT NULL,
pidx INT(10) UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
unit_sig BIGINT DEFAULT NULL,
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_datetime_data (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
value BIGINT NOT NULL,
value_ns INT(10) UNSIGNED DEFAULT NULL,
status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
NOT NULL,
pidx INT(10) UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_date_data (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
value INT(11) NOT NULL,
status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
NOT NULL,
pidx INT(10) UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_name_overrides (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
name VARCHAR(255) NOT NULL,
_iversion INT UNSIGNED NOT NULL,
UNIQUE KEY (`domain_id`, `entity_id`, `property_id`, `_iversion`),
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_desc_overrides (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
description TEXT NOT NULL,
_iversion INT UNSIGNED NOT NULL,
UNIQUE KEY (`domain_id`, `entity_id`, `property_id`, `_iversion`),
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_data_type (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
datatype INT UNSIGNED NOT NULL,
_iversion INT UNSIGNED NOT NULL,
UNIQUE KEY (`domain_id`, `entity_id`, `property_id`, `_iversion`),
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`datatype`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_collection_type (
domain_id INT UNSIGNED NOT NULL,
entity_id INT UNSIGNED NOT NULL,
property_id INT UNSIGNED NOT NULL,
collection VARCHAR(255) NOT NULL,
_iversion INT UNSIGNED NOT NULL,
UNIQUE KEY (`domain_id`, `entity_id`, `property_id`, `_iversion`),
KEY (`domain_id`, `entity_id`, `_iversion`),
KEY (`domain_id`, `_iversion`),
FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_query_template_def (
id INT UNSIGNED NOT NULL,
definition MEDIUMTEXT NOT NULL,
_iversion INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`, `_iversion`),
FOREIGN KEY (`id`, `_iversion`)
REFERENCES `entity_version` (`entity_id`, `_iversion`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_files (
file_id INT UNSIGNED NOT NULL,
`path` TEXT NOT NULL,
size BIGINT UNSIGNED NOT NULL,
hash BINARY(64) DEFAULT NULL,
_iversion INT UNSIGNED NOT NULL,
PRIMARY KEY (`file_id`, `_iversion`),
FOREIGN KEY (`file_id`, `_iversion`)
REFERENCES `entity_version` (`entity_id`, `_iversion`) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE archive_entities (
id INT UNSIGNED NOT NULL,
name VARCHAR(255) DEFAULT NULL,
description TEXT DEFAULT NULL,
role ENUM('RECORDTYPE','RECORD','FILE','DOMAIN',
'PROPERTY','DATATYPE','ROLE','QUERYTEMPLATE') NOT NULL,
acl INT(10) UNSIGNED DEFAULT NULL,
_iversion INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`, `_iversion`),
FOREIGN KEY (`id`, `_iversion`)
REFERENCES `entity_version` (`entity_id`, `_iversion`) ON DELETE CASCADE,
FOREIGN KEY (`acl`) REFERENCES `entity_acl` (`id`)
) ENGINE=InnoDB;
delimiter //
DROP PROCEDURE IF EXISTS set_transaction //
CREATE PROCEDURE set_transaction(
srid VARBINARY(255),
username VARCHAR(255),
realm VARCHAR(255),
seconds BIGINT UNSIGNED,
nanos INT(10) UNSIGNED)
BEGIN
SET @SRID = srid;
INSERT INTO transactions (srid, username, realm, seconds, nanos)
VALUES (srid, username, realm, seconds, nanos);
END //
DROP PROCEDURE IF EXISTS setFileProperties //
CREATE PROCEDURE setFileProperties (
in EntityID INT UNSIGNED,
in FilePath TEXT,
in FileSize BIGINT UNSIGNED,
in FileHash VARCHAR(255)
)
BEGIN
DECLARE IVersion INT UNSIGNED DEFAULT NULL;
IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
SELECT max(e._iversion) INTO IVersion
FROM entity_version AS e
WHERE e.entity_id = EntityID;
INSERT INTO archive_files (file_id, path, size, hash,
_iversion)
SELECT file_id, path, size, hash, IVersion AS _iversion
FROM files
WHERE file_id = EntityID;
END IF;
DELETE FROM files WHERE file_id = EntityID;
IF FilePath IS NOT NULL THEN
INSERT INTO files (file_id, path, size, hash)
VALUES (EntityID, FilePath, FileSize, unhex(FileHash));
END IF;
END //
DROP PROCEDURE IF EXISTS retrieveQueryTemplateDef //
CREATE PROCEDURE retrieveQueryTemplateDef (
in EntityID INT UNSIGNED,
in Version VARBINARY(255))
retrieveQueryTemplateDefBody: BEGIN
DECLARE IVersion INT UNSIGNED DEFAULT NULL;
DECLARE IsHead BOOLEAN DEFAULT TRUE;
IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
IF Version IS NOT NULL THEN
SELECT get_head_version(EntityID) = Version INTO IsHead;
END IF;
IF IsHead IS FALSE THEN
SELECT e._iversion INTO IVersion
FROM entity_version as e
WHERE e.entity_id = EntityID
AND e.version = Version;
IF IVersion IS NULL THEN
-- RETURN EARLY - Version does not exist.
LEAVE retrieveQueryTemplateDefBody;
END IF;
SELECT definition
FROM archive_query_template_def
WHERE id = EntityID
AND _iversion = IVersion;
LEAVE retrieveQueryTemplateDefBody;
END IF;
END IF;
SELECT definition
FROM query_template_def
WHERE id = EntityID;
END //
delimiter ;
INSERT INTO feature_config (_key, _value) VALUES ("ENTITY_VERSIONING", "ENABLED");