versioning.sql

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,