/**
 * Implementation details - MySQL
 *
 * - Use DATETIME.
 *      MSSQL can only have one TIMESTAMP in each table. So there we use
 *      DATETIME instead. But Derby does not have DATETIME, so we use
 *      TIMESTAMP everywhere in Derby.
 */

CREATE TABLE version (
    database_version int NOT NULL
)ENGINE=INNODB;

CREATE TABLE version_history (
    from_version int NOT NULL,
    to_version int NOT NULL,
    success smallint NOT NULL,
    updated_time datetime NOT NULL,
    message MEDIUMTEXT
)ENGINE=INNODB;

INSERT INTO version VALUES (77);
INSERT INTO version_history (from_version, to_version, success, updated_time, message)
VALUES (0, 77, 1, CURRENT_TIMESTAMP, 'Created');



/**

        A

**/


/**

        B

**/



/**

        C

**/

CREATE TABLE color_scheme (
    id bigint NOT NULL PRIMARY KEY,
    scheme_name varchar(255) NOT NULL UNIQUE,
    scheme_data MEDIUMTEXT,
    is_default smallint NOT NULL DEFAULT 0
)ENGINE=INNODB;

/**
 * Configuration templates that the user can choose from when creating a new
 * repository on the server.
 */
CREATE TABLE config_template (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    title varchar(255) NOT NULL,
    config MEDIUMTEXT
)ENGINE=INNODB;



/**

        D

**/

CREATE TABLE description (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    fk_type bigint NOT NULL DEFAULT 1,
    fk_symbol bigint,
    fk_model bigint,
    fk_repository bigint NOT NULL
)ENGINE=INNODB;

CREATE TABLE description_type (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    fk_repository bigint NOT NULL,
    data_type_descr_xml text NOT NULL, /*data_type*/
    last_modified datetime NOT NULL,
    history_path text,
    history_edited smallint NOT NULL,
    input_type varchar(255) NOT NULL DEFAULT 'rich-text-editor',
    icon varchar(255) NOT NULL DEFAULT 'icon:o16/object_ball.png'
)ENGINE=INNODB;

CREATE TABLE description_type_entry (
    id bigint NOT NULL PRIMARY KEY,
    object_type varchar(255) NOT NULL,
    list_pos int NOT NULL,
    fk_type bigint NOT NULL,
    hidden text /*A set of model type ids where the entry is hidden*/
)ENGINE=INNODB;

CREATE TABLE doc_type (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    fk_repository bigint NOT NULL,
    last_modified datetime NOT NULL,
    history_path text,
    history_edited smallint NOT NULL
)ENGINE=INNODB;






/**

        E

**/

CREATE TABLE edge (
    id bigint NOT NULL ,
    from_vertex bigint NOT NULL ,
    to_vertex bigint NOT NULL ,
    edge_type varchar(50) NOT NULL ,
    fk_model bigint NOT NULL,
    uuid varchar(36) NOT NULL,
    txt_font varchar(50) NOT NULL,
    txt_size int NOT NULL,
    txt_style int NOT NULL,
    txt_color int NOT NULL,
    z_order int NOT NULL,
    manual_label_color smallint NOT NULL,
    hide_arrows smallint NOT NULL,
    router_id varchar(50) NOT NULL,
    history_path text,
    history_edited smallint NOT NULL,
    rotate_label smallint NOT NULL,
    stroke varchar(255) DEFAULT NULL,
    points varchar(1024)
)ENGINE=INNODB;

CREATE TABLE edge_user_data (
    id bigint NOT NULL,
    uuid varchar(36) NOT NULL,
    user_data text,
    fk_edge bigint NOT NULL
)ENGINE=INNODB;

CREATE TABLE external_doc (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    copy_on_publish smallint NOT NULL,
    fk_repository bigint NOT NULL,
    fk_owner varchar(50) NOT NULL,
    fk_group bigint NOT NULL,
    permissions int NOT NULL,
    created datetime NOT NULL,
    fk_created_by varchar(50) NOT NULL,
    last_modified datetime NOT NULL,
    fk_last_modified_by varchar(50) NOT NULL,
    fk_responsible bigint,
    document_data_type varchar(50) NOT NULL,
    history_path text,
    history_edited smallint NOT NULL,
    icon_url varchar(255),
    show_in_panel smallint NOT NULL,
    short_id varchar(20),
    short_id_locked smallint NOT NULL DEFAULT 0
)ENGINE=INNODB;

CREATE TABLE external_doc_doc_type (
    doc_id bigint NOT NULL,
    type_id bigint NOT NULL
)ENGINE=INNODB;

CREATE TABLE external_doc_property (
    fk_doc bigint NOT NULL,
    plugin_uuid varchar(255) NOT NULL,
    property_name varchar(255) NOT NULL,
    property_value varchar(1024),
    PRIMARY KEY (fk_doc, plugin_uuid, property_name)
)ENGINE=INNODB;

CREATE TABLE extension_document (
        id bigint NOT NULL PRIMARY KEY,
        is_leaf smallint NOT NULL,
        name_ varchar(512) NOT NULL,
        provider varchar(512) NOT NULL,
        list_position int NOT NULL,
        fk_repository bigint NOT NULL,
        href varchar(1024),
        uuid varchar(36),
        created datetime,
        is_new smallint,
        icon BLOB,
        fk_model bigint,
        fk_symbol bigint
)ENGINE=INNODB;



/**

        F

**/


/**

        G

**/


/**

        H

**/


/**

        I

**/

CREATE TABLE id_gen (
    table_id varchar (255) NOT NULL,
    id bigint NOT NULL
)ENGINE=INNODB;


/**

        J

**/





/**

        K

**/

CREATE TABLE keyed_value (
    id varchar (255) NOT NULL,
    string varchar (255),
    bool smallint NOT NULL ,
    long_value bigint,
    uuid varchar(36) NOT NULL
)ENGINE=INNODB;



/**

        L

**/

CREATE TABLE language_description (
    id bigint NOT NULL PRIMARY KEY,
    description_text LONGTEXT,
    fk_description bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid varchar(36) NOT NULL
)ENGINE=INNODB;

CREATE TABLE language_description_type (
    id bigint NOT NULL PRIMARY KEY,
    title varchar(255) NOT NULL,
    summary varchar(255),
    fk_description_type bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid varchar(36) NOT NULL,
    refinement text
)ENGINE=INNODB;

CREATE TABLE language_document_type (
    id bigint NOT NULL PRIMARY KEY,
    title varchar(255) NOT NULL,
    fk_document_type bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid varchar(36) NOT NULL
)ENGINE=INNODB;

CREATE TABLE language_external_doc (
    id bigint NOT NULL PRIMARY KEY,
    title varchar(255) NOT NULL,
    fk_external_doc bigint NOT NULL,
    fk_language bigint NOT NULL,
    uuid varchar(36) NOT NULL,
    translated smallint NOT NULL,
    xml_data LONGTEXT
)ENGINE=INNODB;


CREATE TABLE language_bounds (
    id bigint NOT NULL PRIMARY KEY,
    x int NOT NULL,
    y int NOT NULL,
    width int NOT NULL,
    height int NOT NULL,
    fk_language bigint NOT NULL,
    fk_vertex bigint,
    fk_edge bigint,
    uuid varchar(36) NOT NULL,
    cpindex int NOT NULL
)ENGINE=INNODB;



CREATE TABLE language_model (
    id bigint NOT NULL PRIMARY KEY,
    title varchar(255) NOT NULL,
    fk_model bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid varchar(36) NOT NULL
)ENGINE=INNODB;

CREATE TABLE language_edge (
    id bigint NOT NULL PRIMARY KEY,
    title varchar(255) NOT NULL,
    fk_edge bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid varchar(36) NOT NULL,
    raw_title varchar(512)
)ENGINE=INNODB;

CREATE TABLE language_symbol (
    id bigint NOT NULL PRIMARY KEY,
    title varchar(255) NOT NULL,
    field1 varchar(255),
    field2 text,
    fk_symbol bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    uuid varchar(36) NOT NULL,
    raw_title varchar(512)
)ENGINE=INNODB;

CREATE TABLE language_layer (
    id bigint NOT NULL PRIMARY KEY,
    title varchar(255) NOT NULL,
    fk_layer_type bigint NOT NULL,
    fk_language bigint NOT NULL,
    translated smallint NOT NULL,
    CONSTRAINT language_layer_unique UNIQUE (fk_layer_type, fk_language)
)ENGINE=INNODB;

CREATE TABLE layer (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    fk_model bigint NOT NULL,
    fk_layer_type bigint NOT NULL
)ENGINE=INNODB;

CREATE TABLE layer_type (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    zorder int NOT NULL,
    model_type varchar(50) NOT NULL,
    object_types text,
    fk_repository bigint NOT NULL,
    ikon varchar(50)
)ENGINE=INNODB;

CREATE TABLE list (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    action_object varchar(60) NOT NULL,
    script text NOT NULL,
    ui_generated smallint NOT NULL DEFAULT 0,
    columns text NOT NULL,
    fk_repository bigint NOT NULL,
    last_modified datetime NOT NULL,
    history_path text,
    history_edited smallint NOT NULL
)ENGINE=INNODB;

CREATE TABLE language_list (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    title varchar(255) NOT NULL,
    fk_list bigint NOT NULL,
    fk_language bigint NOT NULL
)ENGINE=INNODB;

/**

        M

**/

CREATE TABLE maintainers (
    fk_model bigint NOT NULL,
    fk_user varchar(50) NOT NULL,
    workflow_role varchar(50) NOT NULL,
    PRIMARY KEY (fk_model, fk_user, workflow_role)
)ENGINE=INNODB;

CREATE TABLE model (
    model_id bigint NOT NULL ,
    model_type varchar(50) NOT NULL,
    uuid varchar(36) NOT NULL,
    ikon varchar(255),
    discriminator varchar(50) NOT NULL,
    last_modified datetime NOT NULL,
    created datetime NOT NULL,
    fk_repository bigint NOT NULL,
    fk_owner varchar(50) NOT NULL,
    fk_group bigint NOT NULL,
    permissions int NOT NULL,
    fk_last_modified_by varchar(50) NOT NULL,
    fk_created_by varchar(50) NOT NULL,
    locked_date datetime,
    rev int NOT NULL,
    rev_from int,
    rev_locked smallint NOT NULL,
    alt_id varchar(255),
    locked_by varchar(50),
    history_path text,
    history_edited smallint NOT NULL,
    short_id varchar(20),
    short_id_locked smallint NOT NULL DEFAULT 0,
    workflow_state varchar(20) NOT NULL,
    notify smallint NOT NULL DEFAULT 1,
    CONSTRAINT model_unique UNIQUE (uuid, fk_repository, rev)
)ENGINE=INNODB;
CREATE INDEX model_idx1 ON model (workflow_state);

CREATE TABLE model_context (
    id bigint NOT NULL PRIMARY KEY
)ENGINE=INNODB;

CREATE TABLE model_context_entry (
    fk_context bigint NOT NULL,
    fk_model bigint NOT NULL,
    PRIMARY KEY (fk_context, fk_model)
)ENGINE=INNODB;

CREATE TABLE model_breakdown (
    model_breakdown_id bigint NOT NULL,
    is_owning smallint NOT NULL,
    fk_model bigint NOT NULL,
    fk_edge bigint,
    uuid varchar(36) NOT NULL,
    fk_symbol bigint
)ENGINE=INNODB;

CREATE TABLE model_property (
    fk_model bigint NOT NULL,
    plugin_uuid varchar(255) NOT NULL,
    property_name varchar(255) NOT NULL,
    property_value varchar(1024),
    PRIMARY KEY (fk_model, plugin_uuid, property_name)
)ENGINE=INNODB;

CREATE TABLE model_removal_history (
    fk_model bigint NOT NULL,
    uuid varchar(36) NOT NULL,
    history_path text
)ENGINE=INNODB;

CREATE TABLE model_subscriber (
    fk_model bigint NOT NULL,
    fk_subscriber bigint NOT NULL,
    PRIMARY KEY (fk_model, fk_subscriber)
)ENGINE=INNODB;


/**

        N

**/

CREATE TABLE notification_model (
    id bigint NOT NULL PRIMARY KEY,
    fk_publication bigint NOT NULL,
    model_uuid varchar(36) NOT NULL,
    workflow_state varchar(20) NOT NULL,
    rev int NOT NULL,
    publish_date datetime NOT NULL,
    CONSTRAINT notification_model_unique UNIQUE (fk_publication, model_uuid, rev)
)ENGINE=INNODB;

CREATE TABLE notification_receipt (
    fk_notification_model bigint NOT NULL,
    fk_subscriber bigint NOT NULL,
    confirmed smallint NOT NULL,
    token varchar(7) NOT NULL UNIQUE,
    bulk_token varchar(7),
    confirmed_time datetime,
    PRIMARY KEY (fk_notification_model, fk_subscriber)
)ENGINE=INNODB;


/**

        O

**/

CREATE TABLE object_link (
    id bigint NOT NULL PRIMARY KEY,
    link_pos int NOT NULL,
    uuid varchar(36) NOT NULL,
    fk_doc bigint NOT NULL,
    fk_symbol bigint,
    fk_model bigint
)ENGINE=INNODB;





/**

        P

**/

CREATE TABLE plugin_data (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    plugin_uuid varchar(36) NOT NULL,
    data_key varchar(50) NOT NULL,
    content MEDIUMBLOB,
    fk_model bigint,
    fk_symbol bigint,
    fk_document bigint,
    fk_repository bigint,
    fk_snapshot bigint,
    text_content LONGTEXT,
    is_root smallint NOT NULL
)ENGINE=INNODB;
CREATE INDEX plugin_data_idx1 ON plugin_data (data_key);
CREATE INDEX plugin_data_idx2 ON plugin_data (is_root);

CREATE TABLE plugin_relation (
    fk_parent bigint NOT NULL,
    fk_child bigint NOT NULL,
    relation_type varchar(50) NOT NULL,
    pos int NOT NULL,
    strong smallint NOT NULL,
    PRIMARY KEY (fk_parent, fk_child, relation_type)
)ENGINE=INNODB;
CREATE INDEX plugin_relation_idx1 ON plugin_relation (relation_type);
CREATE INDEX plugin_relation_idx2 ON plugin_relation (strong);

CREATE TABLE publish_profile (
    id bigint NOT NULL PRIMARY KEY,
    profile_type varchar(50) NOT NULL,
    profile_name varchar(255) NOT NULL,
    profile_description varchar(255),
    profile_data MEDIUMBLOB NOT NULL,
    fk_repository bigint NOT NULL
)ENGINE=INNODB;

CREATE TABLE publish_template (
    id bigint NOT NULL PRIMARY KEY,
    template_name varchar(255) NOT NULL,
    template_data MEDIUMBLOB NOT NULL,
    is_default smallint NOT NULL DEFAULT 0,
    template_type varchar(50) NOT NULL
)ENGINE=INNODB;

CREATE TABLE publication (
    id bigint NOT NULL PRIMARY KEY,
    fk_plugin_data bigint NOT NULL,
    fk_snapshot bigint NOT NULL,
    publish_date datetime NOT NULL,
    base_url varchar(255) NOT NULL,
    CONSTRAINT publication_unique UNIQUE (fk_plugin_data, fk_snapshot)
)ENGINE=INNODB;

/**

        Q

**/

/**

        R

**/

CREATE TABLE roles (
    id bigint NOT NULL PRIMARY KEY,
    role_name varchar(255),
    uuid varchar(36),
    fk_repository bigint NOT NULL,
    privilege bigint NOT NULL DEFAULT 0
)ENGINE=INNODB;

CREATE TABLE user_roles (
    fk_user varchar(50) NOT NULL,
    fk_roles bigint NOT NULL,
    PRIMARY KEY (fk_user, fk_roles)
)ENGINE=INNODB;

/**
 * This is used by the glassfish app server for specifying a group. This
 * group is then translated to a role that can be used by EJB.
 * We do not use this currently. The glassfish realm sets a default
 * role for all authenticated users called "user" which is the only
 * EJB role that we use (assign group). See table user_group.
 *
 * TODO: create constraint
 */
CREATE TABLE realm_groups (
    user_id varchar(50) NOT NULL,
    group_id varchar(50) NOT NULL,
    PRIMARY KEY(user_id, group_id)
)ENGINE=INNODB;

CREATE TABLE repository (
    id bigint NOT NULL PRIMARY KEY,
    name varchar(255) NOT NULL,
    uuid varchar(36) NOT NULL,
    status varchar(50) NOT NULL DEFAULT 'ONLINE',
    creation_date DATETIME,
    last_modified_date DATETIME,
    fk_owner varchar(50) NOT NULL,

    /* To create a group we need a repository and to create a repository we
     * need a group. This could be solved with transactions but EJB did not
     * like that. (wrt. NOT NULL) */
    fk_group bigint,

    fk_color_scheme bigint,
    permissions int NOT NULL,
    admin_lock smallint NOT NULL,
    template smallint NOT NULL,
    fk_server_directory bigint
)ENGINE=INNODB;

CREATE TABLE repository_timestamp (
    id bigint NOT NULL PRIMARY KEY ,
    uuid varchar(36) NOT NULL,
    type_id varchar(50) NOT NULL,
    title varchar(255) NOT NULL,
    time_value DATETIME NOT NULL,
    fk_repository bigint NOT NULL
)ENGINE=INNODB;


/**
 * Join table between repository and users
 */
CREATE TABLE repository_users (
    fk_repository bigint NOT NULL,
    fk_user varchar(50) NOT NULL,
    privilege bigint NOT NULL,
    permissions int NOT NULL,
    fk_group bigint NOT NULL,
    PRIMARY KEY (fk_repository, fk_user)
)ENGINE=INNODB;

CREATE TABLE repository_variable (
    id bigint NOT NULL PRIMARY KEY,
    var_key varchar(255) NOT NULL,
    var_value varchar(255),
    var_type varchar(50) NOT NULL,
    fk_repository bigint NOT NULL,
    last_modified datetime NOT NULL,
    history_path text,
    history_edited smallint NOT NULL
)ENGINE=INNODB;
CREATE INDEX variable_type ON repository_variable (var_type);

CREATE TABLE language_repository_variable (
    id bigint NOT NULL PRIMARY KEY,
    fk_repository_variable bigint NOT NULL,
    fk_language bigint NOT NULL,
    title varchar(255) NOT NULL
)ENGINE=INNODB;

CREATE TABLE revision_log_item (
    id bigint NOT NULL PRIMARY KEY,
    fk_model bigint,
    fk_symbol bigint,
    fk_trigger bigint,
    fk_user varchar(50) NOT NULL,
    log_message varchar(512),
    reason varchar(50),
    is_new_revision smallint NOT NULL,
    rev_time DATETIME NOT NULL
)ENGINE=INNODB;


/**

        S

**/

CREATE TABLE server_directory (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    name varchar(255) NOT NULL,
    fk_server_directory bigint
)ENGINE=INNODB;

CREATE TABLE server_settings (
    id bigint NOT NULL PRIMARY KEY,
    setting_id varchar(50) NOT NULL,
    setting_value varchar(255)
)ENGINE=INNODB;

CREATE TABLE snapshot (
    id bigint NOT NULL PRIMARY KEY,
    title varchar(255),
    creation_date datetime NOT NULL,
    locked smallint NOT NULL,
    fk_parent bigint,
    fk_repository bigint NOT NULL,
    config MEDIUMTEXT,
    active smallint NOT NULL
)ENGINE=INNODB;

CREATE TABLE snapshot_removal_history (
    fk_snapshot bigint NOT NULL,
    uuid varchar(36) NOT NULL,
    history_path text
)ENGINE=INNODB;

CREATE TABLE snapshot_model (
    fk_snapshot bigint NOT NULL,
    fk_model bigint NOT NULL,
    model_uuid varchar(36) NOT NULL
)ENGINE=INNODB;

CREATE TABLE snapshot_symbol (
    fk_snapshot bigint NOT NULL,
    fk_symbol bigint NOT NULL,
    symbol_uuid varchar(36) NOT NULL
)ENGINE=INNODB;

CREATE TABLE stored_query (
    id bigint NOT NULL ,
    `name` varchar (255),
    query_string text,
    uuid varchar(36) NOT NULL
)ENGINE=INNODB;

CREATE TABLE subscribers (
    id bigint NOT NULL PRIMARY KEY,
    e_mail varchar(255) NOT NULL UNIQUE,
    first_name varchar(255),
    last_name varchar(255),
    fk_user varchar(50),
    is_group smallint NOT NULL DEFAULT 0
)ENGINE=INNODB;

CREATE TABLE group_subscriber ( 
    fk_group bigint NOT NULL,
    fk_member bigint NOT NULL,
    PRIMARY KEY (fk_group, fk_member)
)ENGINE=INNODB;

CREATE TABLE swimlane_relation (
    id bigint NOT NULL PRIMARY KEY,
    object_type varchar(50) NOT NULL,
    edge_type varchar(50) NOT NULL,
    fk_settings bigint NOT NULL
)ENGINE=INNODB;

CREATE TABLE swimlane_settings (
    id bigint NOT NULL PRIMARY KEY,
    fk_model bigint NOT NULL ,
    object_type varchar(50) NOT NULL
)ENGINE=INNODB;

CREATE TABLE symbol (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    ikon varchar(50),
    alt_symbol varchar(50),
    fk_repository bigint NOT NULL,
    discriminator varchar(50),
    fk_owner varchar(50) NOT NULL,
    fk_group bigint NOT NULL,
    permissions int NOT NULL,
    created datetime NOT NULL,
    last_modified datetime NOT NULL,
    fk_last_modified_by varchar(50) NOT NULL,
    locked_date datetime,
    fk_created_by varchar(50) NOT NULL,
    rev int NOT NULL,
    rev_from int,
    rev_locked smallint NOT NULL,
    alt_id varchar(255),
    locked_by varchar(50),
    history_path text,
    history_edited smallint NOT NULL,
    short_id varchar(20),
    short_id_locked smallint NOT NULL DEFAULT 0
)ENGINE=INNODB;

CREATE TABLE symbol_property (
    fk_symbol bigint NOT NULL,
    plugin_uuid varchar(255) NOT NULL,
    property_name varchar(255) NOT NULL,
    property_value varchar(1024),
    PRIMARY KEY (fk_symbol, plugin_uuid, property_name)
)ENGINE=INNODB;



/**

        T

**/

CREATE TABLE thumbnail (
    model_id bigint NOT NULL PRIMARY KEY,
    image_data BLOB
)ENGINE=INNODB;


/**

        U

**/


/**
 * This table is used by both glassfish and the application.
 * Glassfish looks in user_id and PASSWORD.
 *
 */
CREATE TABLE users (
    user_id varchar(50) NOT NULL PRIMARY KEY,
    password varchar(32) NOT NULL,
    first_name varchar(255) NOT NULL,
    last_name varchar(255) NOT NULL,
    ikon varchar(50) NOT NULL,
    administrator smallint NOT NULL,
    privilege bigint NOT NULL,
    disabled smallint NOT NULL,
    uuid varchar(36) NOT NULL,
    last_seen bigint,
    global_id varchar(1024)
)ENGINE=INNODB;



/**
 * This table is used for the application handled access control.
 * There can be two groups with the same name in two separate repositories.
 */
CREATE TABLE users_group (
    group_id bigint NOT NULL PRIMARY KEY,
    fk_repository bigint NOT NULL,
    group_name varchar(255) NOT NULL,
    repo_default smallint NOT NULL,
    uuid varchar(36) NOT NULL
)ENGINE=INNODB;


/**
 * Join table between users_group and users
 */
CREATE TABLE users_group_user (
    fk_user varchar(50) NOT NULL,
    fk_group bigint NOT NULL,
    PRIMARY KEY (fk_user, fk_group)
)ENGINE=INNODB;


CREATE TABLE user_image (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    image_name varchar(255),
    image_data MEDIUMBLOB,
    fk_repository bigint NOT NULL
)ENGINE=INNODB;

CREATE TABLE user_language (
    id bigint NOT NULL PRIMARY KEY,
    uuid varchar(36) NOT NULL,
    language_name varchar(255) NOT NULL,
    ikon varchar(50),
    is_default smallint NOT NULL,
    fk_repository bigint NOT NULL,
    hyphen_dict varchar(10),
    iso varchar(50)
)ENGINE=INNODB;
CREATE INDEX user_language_idx1 ON user_language (is_default);


/**

        V

**/

CREATE TABLE vertex (
    id bigint NOT NULL ,
    bounds_x int NOT NULL ,
    bounds_y int NOT NULL ,
    bounds_width int NOT NULL ,
    bounds_height int NOT NULL ,
    do_owner smallint NOT NULL ,
    fk_model bigint NOT NULL,
    z_order int NOT NULL,
    uuid varchar(36) NOT NULL,
    txt_font varchar(50) NOT NULL,
    txt_size int NOT NULL,
    txt_style int NOT NULL,
    txt_color int NOT NULL,
    is_breakdown_symbol smallint NOT NULL DEFAULT 0,
    image_name varchar(255),
    show_label smallint NOT NULL DEFAULT 1,
    lock_label smallint NOT NULL DEFAULT 0,
    transform_rgb int DEFAULT NULL,
    transform_hue float DEFAULT NULL,
    transform_sat float DEFAULT NULL,
    transform_br float DEFAULT NULL,
    center_label smallint NOT NULL DEFAULT 0,
    manual_label_color smallint NOT NULL DEFAULT 0,
    lock_proportions smallint NOT NULL DEFAULT 0,
    size_ratio float NOT NULL DEFAULT 1,
    fk_symbol bigint NOT NULL,
    elidable smallint NOT NULL,
    locked smallint NOT NULL,
    fold_surrounding smallint NOT NULL DEFAULT 0,
    foldable smallint NOT NULL DEFAULT 0,
    fk_layer bigint,
    history_path text,
    history_edited smallint NOT NULL,
    border varchar(255)
)ENGINE=INNODB;

CREATE TABLE vertex_property (
    fk_vertex bigint NOT NULL,
    plugin_uuid varchar(255) NOT NULL,
    property_name varchar(255) NOT NULL,
    property_value varchar(1024),
    PRIMARY KEY (fk_vertex, plugin_uuid, property_name)
)ENGINE=INNODB;



/**

    ADD INDEXES ON uuid

**/

CREATE INDEX uuid_description ON description (uuid);
CREATE INDEX uuid_doc_type ON doc_type (uuid);
CREATE INDEX uuid_edge ON edge (uuid);
CREATE INDEX uuid_external_doc ON external_doc (uuid);
CREATE INDEX uuid_keyed_value ON keyed_value (uuid);
CREATE INDEX uuid_model ON model (uuid);
CREATE INDEX uuid_model_breakdown ON model_breakdown (uuid);
CREATE INDEX uuid_object_link ON object_link(uuid);
CREATE INDEX uuid_stored_query ON stored_query (uuid);
CREATE INDEX uuid_symbol ON symbol (uuid);
CREATE INDEX uuid_vertex ON vertex (uuid);
CREATE INDEX uuid_repository ON repository (uuid);
CREATE INDEX uuid_user_language ON user_language (uuid);
CREATE INDEX uuid_snapshot_model ON snapshot_model (model_uuid);
CREATE INDEX uuid_snapshot_symbol ON snapshot_symbol (symbol_uuid);





/**

    SET PRIMARY KEY CONSTRAINTS

**/


ALTER TABLE edge ADD CONSTRAINT pk_edge PRIMARY KEY   (id);
ALTER TABLE edge_user_data ADD CONSTRAINT pk_edge_user_data PRIMARY KEY (id);
ALTER TABLE external_doc_doc_type ADD CONSTRAINT pk_external_doc_doc_type PRIMARY KEY (doc_id, type_id);
ALTER TABLE id_gen ADD CONSTRAINT pk_table_generator PRIMARY KEY   (table_id);
ALTER TABLE keyed_value ADD CONSTRAINT pk_keyed_value PRIMARY KEY   (id);
ALTER TABLE model ADD CONSTRAINT pk_model PRIMARY KEY   (model_id);
ALTER TABLE model_breakdown ADD CONSTRAINT pk_model_breakdown PRIMARY KEY( model_breakdown_id );
ALTER TABLE vertex ADD CONSTRAINT pk_vertex PRIMARY KEY   (id);
ALTER TABLE snapshot_model ADD CONSTRAINT pk_snapshot_model PRIMARY KEY (fk_snapshot, fk_model);
ALTER TABLE snapshot_symbol ADD CONSTRAINT pk_snapshot_symbol PRIMARY KEY (fk_snapshot, fk_symbol);






/**

    SET FOREIGN KEY CONSTRAINTS

**/

ALTER TABLE description ADD CONSTRAINT fk_description_description_type FOREIGN KEY
    (fk_type) REFERENCES description_type (id) ON DELETE CASCADE;
ALTER TABLE description ADD CONSTRAINT fk_description_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE description ADD CONSTRAINT fk_description_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE description_type_entry ADD CONSTRAINT fk_description_type_entry_description_type FOREIGN KEY
    (fk_type) REFERENCES description_type (id) ON DELETE CASCADE;
ALTER TABLE edge ADD CONSTRAINT fk_edge_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE edge ADD CONSTRAINT fk_edge_from_vertex FOREIGN KEY
    (from_vertex) REFERENCES vertex (id) ON DELETE CASCADE;
ALTER TABLE edge ADD CONSTRAINT fk_edge_to_vertex FOREIGN KEY
    (to_vertex) REFERENCES vertex (id) ON DELETE CASCADE;
ALTER TABLE edge_user_data ADD CONSTRAINT fk_edge_user_data_edge FOREIGN KEY
    (fk_edge) REFERENCES edge (id) ON DELETE CASCADE;
ALTER TABLE extension_document ADD CONSTRAINT fk_extension_document_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE extension_document ADD CONSTRAINT fk_extension_document_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE extension_document ADD CONSTRAINT fk_extension_document_repository FOREIGN KEY
    (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE external_doc_doc_type ADD CONSTRAINT fk_external_doc_doc_type_doc
    FOREIGN KEY (doc_id) REFERENCES external_doc (id) ON DELETE CASCADE;
ALTER TABLE external_doc_doc_type ADD CONSTRAINT fk_external_doc_doc_type_type
    FOREIGN KEY (type_id) REFERENCES doc_type (id) ON DELETE CASCADE;
ALTER TABLE model_breakdown ADD CONSTRAINT fk_model_breakdown_model FOREIGN KEY
    ( fk_model ) REFERENCES model ( model_id ) ON DELETE CASCADE;
ALTER TABLE model_breakdown ADD CONSTRAINT fk_model_breakdown_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE model_breakdown ADD CONSTRAINT fk_model_breakdown_edge FOREIGN KEY
    (fk_edge) REFERENCES edge (id) ON DELETE CASCADE;
ALTER TABLE object_link ADD CONSTRAINT fk_object_link_external_doc
    FOREIGN KEY (fk_doc) REFERENCES external_doc(id) ON DELETE CASCADE;
ALTER TABLE object_link ADD CONSTRAINT fk_object_link_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE object_link ADD CONSTRAINT fk_object_link_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE repository ADD CONSTRAINT fk_repository_color_scheme FOREIGN KEY
    (fk_color_scheme) REFERENCES color_scheme (id) ON DELETE SET NULL;
ALTER TABLE symbol ADD CONSTRAINT fk_created_symbol_users
  FOREIGN KEY (fk_created_by) REFERENCES users (user_id);
ALTER TABLE vertex ADD CONSTRAINT fk_vertex_model FOREIGN KEY
    (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE vertex ADD CONSTRAINT fk_vertex_symbol FOREIGN KEY
    (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE description ADD CONSTRAINT fk_description_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE description_type ADD CONSTRAINT fk_description_type_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE doc_type ADD CONSTRAINT fk_doc_type_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE model ADD CONSTRAINT fk_model_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE model_context_entry ADD CONSTRAINT fk_context_context
  FOREIGN KEY (fk_context) REFERENCES model_context (id) ON DELETE CASCADE;
ALTER TABLE model_context_entry ADD CONSTRAINT fk_context_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE symbol ADD CONSTRAINT fk_symbol_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE user_image ADD CONSTRAINT fk_user_image_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE user_language ADD CONSTRAINT fk_user_language_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE swimlane_settings ADD CONSTRAINT fk_swimlane_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE swimlane_relation ADD CONSTRAINT fk_swimlane_relation_settings
    FOREIGN KEY (fk_settings) REFERENCES swimlane_settings (id) ON DELETE CASCADE;
ALTER TABLE publish_profile ADD CONSTRAINT fk_publish_profile_repository
    FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE symbol_property ADD CONSTRAINT fk_symbol_property_symbol
    FOREIGN KEY (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE model_property ADD CONSTRAINT fk_model_property_model
    FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE vertex_property ADD CONSTRAINT fk_vertex_property_vertex
    FOREIGN KEY (fk_vertex) REFERENCES vertex (id) ON DELETE CASCADE;
ALTER TABLE external_doc_property ADD CONSTRAINT fk_external_doc_property_doc
    FOREIGN KEY (fk_doc) REFERENCES external_doc (id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_model
    FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_symbol
    FOREIGN KEY (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_document
    FOREIGN KEY (fk_document) REFERENCES external_doc (id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_repository
    FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE plugin_data ADD CONSTRAINT fk_plugin_data_snapshot
    FOREIGN KEY (fk_snapshot) REFERENCES snapshot (id) ON DELETE CASCADE;
ALTER TABLE plugin_relation ADD CONSTRAINT fk_plugin_relation_parent
    FOREIGN KEY (fk_parent) REFERENCES plugin_data (id) ON DELETE CASCADE;
ALTER TABLE plugin_relation ADD CONSTRAINT fk_plugin_relation_child
    FOREIGN KEY (fk_child) REFERENCES plugin_data (id) ON DELETE CASCADE;
ALTER TABLE repository_variable ADD CONSTRAINT fk_repository_var_rep
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE language_repository_variable ADD CONSTRAINT fk_language_rep_var_var
  FOREIGN KEY (fk_repository_variable) REFERENCES repository_variable (id) ON DELETE CASCADE;
ALTER TABLE language_repository_variable ADD CONSTRAINT fk_language_rep_var_lang
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE repository_timestamp ADD CONSTRAINT fk_timestamp_repo
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE server_directory ADD CONSTRAINT fk_server_directory_server_directory
  FOREIGN KEY (fk_server_directory) REFERENCES server_directory (id) ON DELETE CASCADE;
ALTER TABLE repository ADD CONSTRAINT fk_repository_server_directory
  FOREIGN KEY (fk_server_directory) REFERENCES server_directory (id) ON DELETE CASCADE;

/**
 * Users and groups.
 */
ALTER TABLE repository_users ADD CONSTRAINT fk_repository_users_user
  FOREIGN KEY (fk_user) REFERENCES users (user_id);
ALTER TABLE repository_users ADD CONSTRAINT fk_repository_users_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE repository_users ADD CONSTRAINT fk_repository_users_g
  FOREIGN KEY (fk_group) REFERENCES users_group (group_id);
ALTER TABLE users_group ADD CONSTRAINT fk_users_group_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE users_group_user ADD CONSTRAINT fk_users_group_user_user
  FOREIGN KEY (fk_user) REFERENCES users (user_id);
ALTER TABLE users_group_user ADD CONSTRAINT fk_users_group_user_group
  FOREIGN KEY (fk_group) REFERENCES users_group (group_id);
ALTER TABLE model ADD CONSTRAINT fk_model_users
  FOREIGN KEY (fk_owner) REFERENCES users (user_id);
ALTER TABLE model ADD CONSTRAINT fk_model_users_group
  FOREIGN KEY (fk_group) REFERENCES users_group (group_id);
ALTER TABLE symbol ADD CONSTRAINT fk_symbol_users
  FOREIGN KEY (fk_owner) REFERENCES users (user_id);
ALTER TABLE symbol ADD CONSTRAINT fk_symbol_users_group
  FOREIGN KEY (fk_group) REFERENCES users_group (group_id);
ALTER TABLE symbol ADD CONSTRAINT fk_symbol_users_modified
  FOREIGN KEY (fk_last_modified_by) REFERENCES users (user_id);
ALTER TABLE repository ADD CONSTRAINT fk_repository_users
  FOREIGN KEY (fk_owner) REFERENCES users (user_id);
ALTER TABLE repository ADD CONSTRAINT fk_repository_users_group
  FOREIGN KEY (fk_group) REFERENCES users_group (group_id);
ALTER TABLE model ADD CONSTRAINT fk_modified_model_users
  FOREIGN KEY (fk_last_modified_by) REFERENCES users (user_id);
ALTER TABLE model ADD CONSTRAINT fk_created_model_users
  FOREIGN KEY (fk_created_by) REFERENCES users (user_id);
ALTER TABLE roles ADD CONSTRAINT fk_roles_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE user_roles ADD CONSTRAINT fk_user_roles_user
  FOREIGN KEY (fk_user) REFERENCES users (user_id);
ALTER TABLE user_roles ADD CONSTRAINT fk_user_roles_role
  FOREIGN KEY (fk_roles) REFERENCES roles (id);
ALTER TABLE maintainers ADD CONSTRAINT fk_maintainers_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id);
ALTER TABLE maintainers ADD CONSTRAINT fk_maintainers_user
  FOREIGN KEY (fk_user) REFERENCES users (user_id);
ALTER TABLE model_subscriber ADD CONSTRAINT fk_model_subscriber_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE model_subscriber ADD CONSTRAINT fk_model_subscriber_subscriber
  FOREIGN KEY (fk_subscriber) REFERENCES subscribers (id) ON DELETE CASCADE;
ALTER TABLE publication ADD CONSTRAINT fk_publication_plugin_data
  FOREIGN KEY (fk_plugin_data) REFERENCES plugin_data (id) ON DELETE CASCADE;
ALTER TABLE publication ADD CONSTRAINT fk_publication_snapshot
  FOREIGN KEY (fk_snapshot) REFERENCES snapshot (id) ON DELETE CASCADE;
ALTER TABLE notification_model ADD CONSTRAINT fk_notification_model_publication
  FOREIGN KEY (fk_publication) REFERENCES publication (id) ON DELETE CASCADE;
ALTER TABLE notification_receipt ADD CONSTRAINT fk_notification_receipt_notification_model
  FOREIGN KEY (fk_notification_model) REFERENCES notification_model (id) ON DELETE CASCADE;
ALTER TABLE notification_receipt ADD CONSTRAINT fk_notification_receipt_subscriber
  FOREIGN KEY (fk_subscriber) REFERENCES subscribers (id) ON DELETE CASCADE;
ALTER TABLE subscribers ADD CONSTRAINT fk_subscriber_user
  FOREIGN KEY (fk_user) REFERENCES users (user_id) ON DELETE CASCADE;
ALTER TABLE group_subscriber ADD CONSTRAINT fk_group_subscriber_group
  FOREIGN KEY (fk_group) REFERENCES subscribers (id) ON DELETE CASCADE;
ALTER TABLE group_subscriber ADD CONSTRAINT fk_group_subscriber_member
  FOREIGN KEY (fk_member) REFERENCES subscribers (id) ON DELETE CASCADE;

/**
 * External document
 */
ALTER TABLE external_doc ADD CONSTRAINT fk_external_doc_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id);
ALTER TABLE external_doc ADD CONSTRAINT fk_external_doc_users
  FOREIGN KEY (fk_owner) REFERENCES users (user_id);
ALTER TABLE external_doc ADD CONSTRAINT fk_external_doc_users_group
  FOREIGN KEY (fk_group) REFERENCES users_group (group_id);
ALTER TABLE external_doc ADD CONSTRAINT fk_modified_external_doc_users
  FOREIGN KEY (fk_last_modified_by) REFERENCES users (user_id);
ALTER TABLE external_doc ADD CONSTRAINT fk_created_external_doc_users
  FOREIGN KEY (fk_created_by) REFERENCES users (user_id);
ALTER TABLE external_doc ADD CONSTRAINT fk_external_doc_responsible
  FOREIGN KEY (fk_responsible) REFERENCES symbol (id);


/**
 * Languages
 */
ALTER TABLE language_description ADD CONSTRAINT fk_language_description_description
  FOREIGN KEY (fk_description) REFERENCES description (id) ON DELETE CASCADE;
ALTER TABLE language_description ADD CONSTRAINT fk_language_description_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_description_type ADD CONSTRAINT fk_language_description_type_description_type
  FOREIGN KEY (fk_description_type) REFERENCES description_type (id) ON DELETE CASCADE;
ALTER TABLE language_description_type ADD CONSTRAINT fk_language_description_type_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_document_type ADD CONSTRAINT fk_language_document_type_doc_type
  FOREIGN KEY (fk_document_type) REFERENCES doc_type (id) ON DELETE CASCADE;
ALTER TABLE language_document_type ADD CONSTRAINT fk_language_document_type_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_external_doc ADD CONSTRAINT fk_language_external_doc_external_doc
  FOREIGN KEY (fk_external_doc) REFERENCES external_doc (id) ON DELETE CASCADE;
ALTER TABLE language_external_doc ADD CONSTRAINT fk_language_external_doc_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_model ADD CONSTRAINT fk_language_model_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE language_model ADD CONSTRAINT fk_language_model_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_edge ADD CONSTRAINT fk_language_edge_edge
  FOREIGN KEY (fk_edge) REFERENCES edge (id) ON DELETE CASCADE;
ALTER TABLE language_edge ADD CONSTRAINT fk_language_edge_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_symbol ADD CONSTRAINT fk_language_symbol_symbol
  FOREIGN KEY (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE language_symbol ADD CONSTRAINT fk_language_symbol_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_bounds ADD CONSTRAINT fk_language_bounds_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;
ALTER TABLE language_bounds ADD CONSTRAINT fk_language_bounds_edge
  FOREIGN KEY (fk_edge) REFERENCES edge (id) ON DELETE CASCADE;
ALTER TABLE language_bounds ADD CONSTRAINT fk_language_bounds_vertex
  FOREIGN KEY (fk_vertex) REFERENCES vertex(id) ON DELETE CASCADE;
ALTER TABLE language_list ADD CONSTRAINT fk_language_list_list
  FOREIGN KEY (fk_list) REFERENCES list (id) ON DELETE CASCADE;
ALTER TABLE language_list ADD CONSTRAINT fk_language_list_language
  FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;

/**
 * Snapshots/revisions
 */
ALTER TABLE snapshot ADD CONSTRAINT fk_snapshot_snapshot
  FOREIGN KEY (fk_parent) REFERENCES snapshot (id);
ALTER TABLE snapshot ADD CONSTRAINT fk_snapshot_repository
  FOREIGN KEY (fk_repository) REFERENCES repository (id) ON DELETE CASCADE;
ALTER TABLE snapshot_model ADD CONSTRAINT fk_snapshot_model_snapshot
  FOREIGN KEY (fk_snapshot) REFERENCES snapshot (id) ON DELETE CASCADE;
ALTER TABLE snapshot_model ADD CONSTRAINT fk_snapshot_model_model
  FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE snapshot_symbol ADD CONSTRAINT fk_snapshot_symbol_snapshot
  FOREIGN KEY (fk_snapshot) REFERENCES snapshot (id) ON DELETE CASCADE;
ALTER TABLE snapshot_symbol ADD CONSTRAINT fk_snapshot_symbol_symbol
  FOREIGN KEY (fk_symbol) REFERENCES symbol (id) ON DELETE CASCADE;
ALTER TABLE revision_log_item ADD CONSTRAINT fk_revision_log_item_model
    FOREIGN KEY (fk_model) REFERENCES model(model_id) ON DELETE CASCADE;
ALTER TABLE revision_log_item ADD CONSTRAINT fk_revision_log_item_symbol
    FOREIGN KEY (fk_symbol) REFERENCES symbol(id) ON DELETE CASCADE;
ALTER TABLE revision_log_item ADD CONSTRAINT fk_revision_log_item_user
    FOREIGN KEY (fk_user) REFERENCES users(user_id);

/**
 * layers
 */
ALTER TABLE layer ADD CONSTRAINT fk_layer_model
    FOREIGN KEY (fk_model) REFERENCES model (model_id) ON DELETE CASCADE;
ALTER TABLE layer ADD CONSTRAINT fk_layer_layer_type
    FOREIGN KEY (fk_layer_type) REFERENCES layer_type (id) ON DELETE CASCADE;
ALTER TABLE vertex ADD CONSTRAINT fk_vertex_layer
    FOREIGN KEY (fk_layer) REFERENCES layer (id);
ALTER TABLE language_layer ADD CONSTRAINT fk_language_layer_layer_type
    FOREIGN KEY (fk_layer_type) REFERENCES layer_type (id) ON DELETE CASCADE;
ALTER TABLE language_layer ADD CONSTRAINT fk_language_layer_language
    FOREIGN KEY (fk_language) REFERENCES user_language (id) ON DELETE CASCADE;




/**
*	Create triggers instead of foreign keys that could cause cycles
*   with CASCADE DELETE or SET NULL
**/

delimiter |

CREATE TRIGGER DTrigger10 BEFORE DELETE ON layer FOR EACH ROW BEGIN
UPDATE vertex SET fk_layer = NULL WHERE fk_layer = OLD.id;
END;
|

delimiter ;

INSERT INTO id_gen VALUES('COLOR_SCHEME', 2);
INSERT INTO id_gen VALUES('CONFIG_TEMPLATE', 1);
INSERT INTO id_gen VALUES('DESCRIPTION', 1);
INSERT INTO id_gen VALUES('DESCRIPTION_TYPE', 2);
INSERT INTO id_gen VALUES('DESCRIPTION_TYPE_ENTRY', 1);
INSERT INTO id_gen VALUES('DOC_TYPE', 1);
INSERT INTO id_gen VALUES('EDGE', 1);
INSERT INTO id_gen VALUES('EDGE_USER_DATA', 1);
INSERT INTO id_gen VALUES('EXTERNAL_LINK', 1);
INSERT INTO id_gen VALUES('LANGUAGE_DESCRIPTION', 1);
INSERT INTO id_gen VALUES('LANGUAGE_DESCRIPTION_TYPE', 1);
INSERT INTO id_gen VALUES('LANGUAGE_DOCUMENT_TYPE', 1);
INSERT INTO id_gen VALUES('LANGUAGE_EXTERNAL_DOC', 1);
INSERT INTO id_gen VALUES('LANGUAGE_BOUNDS', 1);
INSERT INTO id_gen VALUES('LANGUAGE_MODEL', 1);
INSERT INTO id_gen VALUES('LANGUAGE_EDGE', 1);
INSERT INTO id_gen VALUES('LANGUAGE_SYMBOL', 1);
INSERT INTO id_gen VALUES('LANGUAGE_LAYER', 1);
INSERT INTO id_gen VALUES('LANGUAGE_LIST', 1);
INSERT INTO id_gen VALUES('LIST', 1);
INSERT INTO id_gen VALUES('LAYER', 7);
INSERT INTO id_gen VALUES('MODEL', 1);
INSERT INTO id_gen VALUES('MODEL_BREAKDOWN', 1);
INSERT INTO id_gen VALUES('OBJECT_LINK', 1);
INSERT INTO id_gen VALUES('REPOSITORY', 2);
INSERT INTO id_gen VALUES('SERVER_DIRECTORY', 1);
INSERT INTO id_gen VALUES('REVISION_LOG_ITEM', 1);
INSERT INTO id_gen VALUES('SERVER_SETTING', 1);
INSERT INTO id_gen VALUES('STORED_QUERY', 1);
INSERT INTO id_gen VALUES('SYMBOL', 1);
INSERT INTO id_gen VALUES('VERTEX', 1);
INSERT INTO id_gen VALUES('QLIK_VIEW_SYMBOL_PROPERTY', 1);
INSERT INTO id_gen VALUES('QLIK_VIEW_MODEL_PROPERTY', 1);
INSERT INTO id_gen VALUES('USER_IMAGE', 1);
INSERT INTO id_gen VALUES('USER_LANGUAGE', 1);
INSERT INTO id_gen VALUES('USERS_GROUP', 1);
INSERT INTO id_gen VALUES('SWIMLANE_SETTINGS', 1);
INSERT INTO id_gen VALUES('SWIMLANE_RELATION', 1);
INSERT INTO id_gen VALUES('EXTENSION_DOCUMENT', 7);
INSERT INTO id_gen VALUES('SNAPSHOT', 1);
INSERT INTO id_gen VALUES('ROLES', 1);
INSERT INTO id_gen VALUES('PUBLISH_PROFILE', 1);
INSERT INTO id_gen VALUES('PUBLISH_TEMPLATE', 1);
INSERT INTO id_gen VALUES('LAYER_TYPE', 1);
INSERT INTO id_gen VALUES('WEB_COMPONENT', 1);
INSERT INTO id_gen (table_id, id) VALUES('PLUGIN_DATA', 1);
INSERT INTO id_gen (table_id, id) VALUES('REPOSITORY_VARIABLE', 1);
INSERT INTO id_gen (table_id, id) VALUES('LANGUAGE_REPOSITORY_VARIABLE', 1);
INSERT INTO id_gen (table_id, id) VALUES('REPOSITORY_TIMESTAMP', 1);
INSERT INTO id_gen (table_id, id) VALUES('MODEL_CONTEXT', 1);
INSERT INTO id_gen (table_id, id) VALUES('PUBLICATION', 1);
INSERT INTO id_gen (table_id, id) VALUES('NOTIFICATION_MODEL', 1);
INSERT INTO id_gen (table_id, id) VALUES('SUBSCRIBER', 1);

/**
 * Create a default administrator account with password '2c8'.
 */
INSERT INTO users VALUES ('admin', '6ac96a01f1a5a89e133480856d7cfcf0', 'Administrator', '', 'USER_00', 1, 0, 0, UUID(), 0, NULL);
