Skip to content
Snippets Groups Projects
Select Git revision
  • 4a2d395920070bb27f94da73fcf7dcff1dde8fc6
  • 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

patch.sh

Blame
  • 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");