hawkbit-pg.sql

-- ------------ Write DROP-FOREIGN-KEY-CONSTRAINT-stage scripts -----------

ALTER TABLE hawkbit.sp_action DROP CONSTRAINT fk_action_ds;



ALTER TABLE hawkbit.sp_action DROP CONSTRAINT fk_action_rollout;



ALTER TABLE hawkbit.sp_action DROP CONSTRAINT fk_action_rolloutgroup;



ALTER TABLE hawkbit.sp_action DROP CONSTRAINT fk_targ_act_hist_targ;



ALTER TABLE hawkbit.sp_action_status DROP CONSTRAINT fk_act_stat_action;



ALTER TABLE hawkbit.sp_action_status_messages DROP CONSTRAINT fk_stat_msg_act_stat;



ALTER TABLE hawkbit.sp_artifact DROP CONSTRAINT fk_assigned_sm;



ALTER TABLE hawkbit.sp_base_software_module DROP CONSTRAINT fk_module_type;



ALTER TABLE hawkbit.sp_distribution_set DROP CONSTRAINT fk_ds_dstype_ds;



ALTER TABLE hawkbit.sp_ds_dstag DROP CONSTRAINT fk_ds_dstag_ds;



ALTER TABLE hawkbit.sp_ds_dstag DROP CONSTRAINT fk_ds_dstag_tag;



ALTER TABLE hawkbit.sp_ds_metadata DROP CONSTRAINT fk_metadata_ds;



ALTER TABLE hawkbit.sp_ds_module DROP CONSTRAINT fk_ds_module_ds;



ALTER TABLE hawkbit.sp_ds_module DROP CONSTRAINT fk_ds_module_module;



ALTER TABLE hawkbit.sp_ds_type_element DROP CONSTRAINT fk_ds_type_element_element;



ALTER TABLE hawkbit.sp_ds_type_element DROP CONSTRAINT fk_ds_type_element_smtype;



ALTER TABLE hawkbit.sp_rollout DROP CONSTRAINT fk_rollout_ds;



ALTER TABLE hawkbit.sp_rolloutgroup DROP CONSTRAINT fk_rolloutgroup_rollout;



ALTER TABLE hawkbit.sp_rollouttargetgroup DROP CONSTRAINT fk_rollouttargetgroup_rolloutgroup;



ALTER TABLE hawkbit.sp_rollouttargetgroup DROP CONSTRAINT fk_rollouttargetgroup_target;



ALTER TABLE hawkbit.sp_sw_metadata DROP CONSTRAINT fk_metadata_sw;



ALTER TABLE hawkbit.sp_target DROP CONSTRAINT fk_target_assign_ds;



ALTER TABLE hawkbit.sp_target DROP CONSTRAINT fk_target_inst_ds;



ALTER TABLE hawkbit.sp_target_attributes DROP CONSTRAINT fk_targ_attrib_target;



ALTER TABLE hawkbit.sp_target_filter_query DROP CONSTRAINT fk_filter_auto_assign_ds;



ALTER TABLE hawkbit.sp_target_metadata DROP CONSTRAINT fk_metadata_target;



ALTER TABLE hawkbit.sp_target_target_tag DROP CONSTRAINT fk_targ_targtag_tag;



ALTER TABLE hawkbit.sp_target_target_tag DROP CONSTRAINT fk_targ_targtag_target;



ALTER TABLE hawkbit.sp_tenant DROP CONSTRAINT fk_tenant_md_default_ds_type;



-- ------------ Write DROP-CONSTRAINT-stage scripts -----------

ALTER TABLE hawkbit.schema_version DROP CONSTRAINT pk_schema_version;



ALTER TABLE hawkbit.sp_action DROP CONSTRAINT pk_sp_action;



ALTER TABLE hawkbit.sp_action_status DROP CONSTRAINT pk_sp_action_status;



ALTER TABLE hawkbit.sp_artifact DROP CONSTRAINT pk_sp_artifact;



ALTER TABLE hawkbit.sp_base_software_module DROP CONSTRAINT pk_sp_base_software_module;



ALTER TABLE hawkbit.sp_base_software_module DROP CONSTRAINT uk_base_sw_mod_sp_base_software_module;



ALTER TABLE hawkbit.sp_distribution_set DROP CONSTRAINT pk_sp_distribution_set;



ALTER TABLE hawkbit.sp_distribution_set DROP CONSTRAINT uk_distrib_set_sp_distribution_set;



ALTER TABLE hawkbit.sp_distribution_set_type DROP CONSTRAINT pk_sp_distribution_set_type;



ALTER TABLE hawkbit.sp_distribution_set_type DROP CONSTRAINT uk_dst_key_sp_distribution_set_type;



ALTER TABLE hawkbit.sp_distribution_set_type DROP CONSTRAINT uk_dst_name_sp_distribution_set_type;



ALTER TABLE hawkbit.sp_distributionset_tag DROP CONSTRAINT pk_sp_distributionset_tag;



ALTER TABLE hawkbit.sp_distributionset_tag DROP CONSTRAINT uk_ds_tag_sp_distributionset_tag;



ALTER TABLE hawkbit.sp_ds_dstag DROP CONSTRAINT pk_sp_ds_dstag;



ALTER TABLE hawkbit.sp_ds_metadata DROP CONSTRAINT pk_sp_ds_metadata;



ALTER TABLE hawkbit.sp_ds_module DROP CONSTRAINT pk_sp_ds_module;



ALTER TABLE hawkbit.sp_ds_type_element DROP CONSTRAINT pk_sp_ds_type_element;



ALTER TABLE hawkbit.sp_rollout DROP CONSTRAINT pk_sp_rollout;



ALTER TABLE hawkbit.sp_rollout DROP CONSTRAINT uk_rollout_sp_rollout;



ALTER TABLE hawkbit.sp_rolloutgroup DROP CONSTRAINT pk_sp_rolloutgroup;



ALTER TABLE hawkbit.sp_rolloutgroup DROP CONSTRAINT uk_rolloutgroup_sp_rolloutgroup;



ALTER TABLE hawkbit.sp_rollouttargetgroup DROP CONSTRAINT pk_sp_rollouttargetgroup;



ALTER TABLE hawkbit.sp_software_module_type DROP CONSTRAINT pk_sp_software_module_type;



ALTER TABLE hawkbit.sp_software_module_type DROP CONSTRAINT uk_smt_name_sp_software_module_type;



ALTER TABLE hawkbit.sp_software_module_type DROP CONSTRAINT uk_smt_type_key_sp_software_module_type;



ALTER TABLE hawkbit.sp_sw_metadata DROP CONSTRAINT pk_sp_sw_metadata;



ALTER TABLE hawkbit.sp_target DROP CONSTRAINT pk_sp_target;



ALTER TABLE hawkbit.sp_target DROP CONSTRAINT uk_tenant_controller_id_sp_target;



ALTER TABLE hawkbit.sp_target_attributes DROP CONSTRAINT pk_sp_target_attributes;



ALTER TABLE hawkbit.sp_target_filter_query DROP CONSTRAINT pk_sp_target_filter_query;



ALTER TABLE hawkbit.sp_target_filter_query DROP CONSTRAINT uk_tenant_custom_filter_name_sp_target_filter_query;



ALTER TABLE hawkbit.sp_target_metadata DROP CONSTRAINT pk_sp_target_metadata;



ALTER TABLE hawkbit.sp_target_tag DROP CONSTRAINT pk_sp_target_tag;



ALTER TABLE hawkbit.sp_target_tag DROP CONSTRAINT uk_targ_tag_sp_target_tag;



ALTER TABLE hawkbit.sp_target_target_tag DROP CONSTRAINT pk_sp_target_target_tag;



ALTER TABLE hawkbit.sp_tenant DROP CONSTRAINT pk_sp_tenant;



ALTER TABLE hawkbit.sp_tenant DROP CONSTRAINT uk_tenantmd_tenant_sp_tenant;



ALTER TABLE hawkbit.sp_tenant_configuration DROP CONSTRAINT pk_sp_tenant_configuration;



ALTER TABLE hawkbit.sp_tenant_configuration DROP CONSTRAINT uk_tenant_key_sp_tenant_configuration;



-- ------------ Write DROP-INDEX-stage scripts -----------

DROP INDEX IF EXISTS hawkbit.schema_version_s_idx_schema_version;



DROP INDEX IF EXISTS hawkbit.sp_idx_action_01_sp_action;



DROP INDEX IF EXISTS hawkbit.sp_idx_action_02_sp_action;



DROP INDEX IF EXISTS hawkbit.sp_idx_action_external_ref_sp_action;



DROP INDEX IF EXISTS hawkbit.sp_idx_action_prim_sp_action;



DROP INDEX IF EXISTS hawkbit.sp_idx_action_status_02_sp_action_status;



DROP INDEX IF EXISTS hawkbit.sp_idx_action_status_prim_sp_action_status;



DROP INDEX IF EXISTS hawkbit.sp_idx_action_status_msgs_01_sp_action_status_messages;



DROP INDEX IF EXISTS hawkbit.sp_idx_artifact_01_sp_artifact;



DROP INDEX IF EXISTS hawkbit.sp_idx_artifact_02_sp_artifact;



DROP INDEX IF EXISTS hawkbit.sp_idx_artifact_prim_sp_artifact;



DROP INDEX IF EXISTS hawkbit.sp_idx_base_sw_module_01_sp_base_software_module;



DROP INDEX IF EXISTS hawkbit.sp_idx_base_sw_module_02_sp_base_software_module;



DROP INDEX IF EXISTS hawkbit.sp_idx_base_sw_module_prim_sp_base_software_module;



DROP INDEX IF EXISTS hawkbit.sp_idx_distribution_set_01_sp_distribution_set;



DROP INDEX IF EXISTS hawkbit.sp_idx_distribution_set_prim_sp_distribution_set;



DROP INDEX IF EXISTS hawkbit.sp_idx_distribution_set_type_01_sp_distribution_set_type;



DROP INDEX IF EXISTS hawkbit.sp_idx_distribution_set_type_prim_sp_distribution_set_type;



DROP INDEX IF EXISTS hawkbit.sp_idx_distribution_set_tag_01_sp_distributionset_tag;



DROP INDEX IF EXISTS hawkbit.sp_idx_distribution_set_tag_prim_sp_distributionset_tag;



DROP INDEX IF EXISTS hawkbit.fk_rolloutgroup_rolloutgroup_sp_rolloutgroup;



DROP INDEX IF EXISTS hawkbit.sp_idx_software_module_type_01_sp_software_module_type;



DROP INDEX IF EXISTS hawkbit.sp_idx_software_module_type_prim_sp_software_module_type;



DROP INDEX IF EXISTS hawkbit.sp_idx_target_01_sp_target;



DROP INDEX IF EXISTS hawkbit.sp_idx_target_03_sp_target;



DROP INDEX IF EXISTS hawkbit.sp_idx_target_04_sp_target;



DROP INDEX IF EXISTS hawkbit.sp_idx_target_prim_sp_target;



DROP INDEX IF EXISTS hawkbit.sp_idx_target_tag_01_sp_target_tag;



DROP INDEX IF EXISTS hawkbit.sp_idx_target_tag_prim_sp_target_tag;



DROP INDEX IF EXISTS hawkbit.sp_idx_tenant_prim_sp_tenant;



-- ------------ Write DROP-TABLE-stage scripts -----------

DROP TABLE IF EXISTS hawkbit.schema_version;



DROP TABLE IF EXISTS hawkbit.sp_action;



DROP TABLE IF EXISTS hawkbit.sp_action_status;



DROP TABLE IF EXISTS hawkbit.sp_action_status_messages;



DROP TABLE IF EXISTS hawkbit.sp_artifact;



DROP TABLE IF EXISTS hawkbit.sp_base_software_module;



DROP TABLE IF EXISTS hawkbit.sp_distribution_set;



DROP TABLE IF EXISTS hawkbit.sp_distribution_set_type;



DROP TABLE IF EXISTS hawkbit.sp_distributionset_tag;



DROP TABLE IF EXISTS hawkbit.sp_ds_dstag;



DROP TABLE IF EXISTS hawkbit.sp_ds_metadata;



DROP TABLE IF EXISTS hawkbit.sp_ds_module;



DROP TABLE IF EXISTS hawkbit.sp_ds_type_element;



DROP TABLE IF EXISTS hawkbit.sp_rollout;



DROP TABLE IF EXISTS hawkbit.sp_rolloutgroup;



DROP TABLE IF EXISTS hawkbit.sp_rollouttargetgroup;



DROP TABLE IF EXISTS hawkbit.sp_software_module_type;



DROP TABLE IF EXISTS hawkbit.sp_sw_metadata;



DROP TABLE IF EXISTS hawkbit.sp_target;



DROP TABLE IF EXISTS hawkbit.sp_target_attributes;



DROP TABLE IF EXISTS hawkbit.sp_target_filter_query;



DROP TABLE IF EXISTS hawkbit.sp_target_metadata;



DROP TABLE IF EXISTS hawkbit.sp_target_tag;



DROP TABLE IF EXISTS hawkbit.sp_target_target_tag;



DROP TABLE IF EXISTS hawkbit.sp_tenant;



DROP TABLE IF EXISTS hawkbit.sp_tenant_configuration;



-- ------------ Write DROP-SEQUENCE-stage scripts -----------

DROP SEQUENCE IF EXISTS hawkbit.sp_action_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_action_status_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_artifact_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_base_software_module_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_distribution_set_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_distribution_set_type_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_distributionset_tag_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_rollout_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_rolloutgroup_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_software_module_type_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_target_filter_query_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_target_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_target_tag_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_tenant_configuration_seq;



DROP SEQUENCE IF EXISTS hawkbit.sp_tenant_seq;



-- ------------ Write DROP-DATABASE-stage scripts -----------

-- ------------ Write CREATE-DATABASE-stage scripts -----------

CREATE SCHEMA IF NOT EXISTS hawkbit;



-- ------------ Write CREATE-SEQUENCE-stage scripts -----------

CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_action_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_action_status_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_artifact_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_base_software_module_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_distribution_set_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_distribution_set_type_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_distributionset_tag_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_rollout_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_rolloutgroup_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_software_module_type_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_target_filter_query_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_target_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_target_tag_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_tenant_configuration_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



CREATE SEQUENCE IF NOT EXISTS hawkbit.sp_tenant_seq
INCREMENT BY 1
START WITH 1
NO CYCLE;



-- ------------ Write CREATE-TABLE-stage scripts -----------

CREATE TABLE hawkbit.schema_version(
    installed_rank INTEGER NOT NULL,
    version VARCHAR(50),
    description VARCHAR(200) NOT NULL,
    type VARCHAR(20) NOT NULL,
    script VARCHAR(1000) NOT NULL,
    checksum INTEGER,
    installed_by VARCHAR(100) NOT NULL,
    installed_on TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT 'epoch'::TIMESTAMP,
    execution_time INTEGER NOT NULL,
    success SMALLINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_action(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_action_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    active BIT(1),
    forced_time BIGINT,
    status INTEGER NOT NULL,
    distribution_set BIGINT NOT NULL,
    target BIGINT NOT NULL,
    rollout BIGINT,
    rolloutgroup BIGINT,
    action_type INTEGER NOT NULL,
    maintenance_cron_schedule VARCHAR(40),
    maintenance_duration VARCHAR(40),
    maintenance_time_zone VARCHAR(40),
    external_ref VARCHAR(512),
    weight INTEGER
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_action_status(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_action_status_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    target_occurred_at BIGINT NOT NULL,
    status INTEGER NOT NULL,
    action BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_action_status_messages(
    action_status_id BIGINT NOT NULL,
    detail_message VARCHAR(512) NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_artifact(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_artifact_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    md5_hash VARCHAR(32),
    file_size BIGINT,
    provided_file_name VARCHAR(256),
    sha1_hash VARCHAR(40) NOT NULL,
    software_module BIGINT NOT NULL,
    sha256_hash CHAR(64)
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_base_software_module(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_base_software_module_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    description VARCHAR(512),
    name VARCHAR(128),
    version VARCHAR(64) NOT NULL,
    deleted BIT(1),
    vendor VARCHAR(256),
    module_type BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_distribution_set(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_distribution_set_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    description VARCHAR(512),
    name VARCHAR(128),
    version VARCHAR(64) NOT NULL,
    complete BIT(1),
    deleted BIT(1),
    required_migration_step BIT(1),
    ds_id BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_distribution_set_type(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_distribution_set_type_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    description VARCHAR(512),
    name VARCHAR(128),
    colour VARCHAR(16),
    deleted BIT(1),
    type_key VARCHAR(64) NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_distributionset_tag(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_distributionset_tag_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    description VARCHAR(512),
    name VARCHAR(128),
    colour VARCHAR(16)
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_ds_dstag(
    ds BIGINT NOT NULL,
    tag BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_ds_metadata(
    meta_key VARCHAR(128) NOT NULL,
    meta_value VARCHAR(4000),
    ds_id BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_ds_module(
    ds_id BIGINT NOT NULL,
    module_id BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_ds_type_element(
    mandatory BIT(1),
    distribution_set_type BIGINT NOT NULL,
    software_module_type BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_rollout(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_rollout_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    description VARCHAR(512),
    name VARCHAR(128),
    last_check BIGINT,
    group_theshold REAL,
    status INTEGER NOT NULL,
    distribution_set BIGINT NOT NULL,
    target_filter VARCHAR(1024),
    forced_time BIGINT,
    total_targets BIGINT,
    rollout_groups_created BIGINT,
    start_at BIGINT,
    deleted SMALLINT,
    action_type INTEGER NOT NULL,
    approval_decided_by VARCHAR(64),
    approval_remark VARCHAR(255),
    weight INTEGER
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_rolloutgroup(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_rolloutgroup_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    description VARCHAR(512),
    name VARCHAR(128),
    error_condition INTEGER,
    error_condition_exp VARCHAR(512),
    error_action INTEGER,
    error_action_exp VARCHAR(512),
    success_condition INTEGER NOT NULL,
    success_condition_exp VARCHAR(512) NOT NULL,
    success_action INTEGER NOT NULL,
    success_action_exp VARCHAR(512),
    status INTEGER NOT NULL,
    parent_id BIGINT,
    rollout BIGINT NOT NULL,
    total_targets BIGINT,
    target_percentage REAL,
    target_filter VARCHAR(1024)
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_rollouttargetgroup(
    target_id BIGINT NOT NULL,
    rolloutgroup_id BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_software_module_type(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_software_module_type_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    description VARCHAR(512),
    name VARCHAR(128),
    colour VARCHAR(16),
    deleted BIT(1),
    type_key VARCHAR(64) NOT NULL,
    max_ds_assignments INTEGER NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_sw_metadata(
    meta_key VARCHAR(128) NOT NULL,
    meta_value VARCHAR(4000),
    sw_id BIGINT NOT NULL,
    target_visible BIT(1)
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_target(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_target_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    description VARCHAR(512),
    name VARCHAR(128),
    controller_id VARCHAR(256),
    sec_token VARCHAR(128) NOT NULL,
    assigned_distribution_set BIGINT,
    install_date BIGINT,
    address VARCHAR(512),
    last_target_query BIGINT,
    request_controller_attributes BIT(1) NOT NULL,
    installed_distribution_set BIGINT,
    update_status INTEGER NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_target_attributes(
    target_id BIGINT NOT NULL,
    attribute_value VARCHAR(128),
    attribute_key VARCHAR(128) NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_target_filter_query(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_target_filter_query_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    name VARCHAR(128),
    query VARCHAR(1024) NOT NULL,
    auto_assign_distribution_set BIGINT,
    auto_assign_action_type INTEGER,
    auto_assign_weight INTEGER
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_target_metadata(
    meta_key VARCHAR(128) NOT NULL,
    meta_value VARCHAR(4000),
    target_id BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_target_tag(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_target_tag_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    description VARCHAR(512),
    name VARCHAR(128),
    colour VARCHAR(16)
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_target_target_tag(
    target BIGINT NOT NULL,
    tag BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_tenant(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_tenant_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    default_ds_type BIGINT NOT NULL
)
        WITH (
        OIDS=FALSE
        );



CREATE TABLE hawkbit.sp_tenant_configuration(
    id BIGINT NOT NULL DEFAULT nextval('hawkbit.sp_tenant_configuration_seq'),
    created_at BIGINT,
    created_by VARCHAR(64),
    last_modified_at BIGINT,
    last_modified_by VARCHAR(64),
    optlock_revision BIGINT,
    tenant VARCHAR(40) NOT NULL,
    conf_key VARCHAR(128) NOT NULL,
    conf_value VARCHAR(512) NOT NULL
)
        WITH (
        OIDS=FALSE
        );



-- ------------ Write CREATE-INDEX-stage scripts -----------

CREATE INDEX schema_version_s_idx_schema_version
ON hawkbit.schema_version
USING BTREE (success);



CREATE INDEX sp_idx_action_01_sp_action
ON hawkbit.sp_action
USING BTREE (tenant, distribution_set);



CREATE INDEX sp_idx_action_02_sp_action
ON hawkbit.sp_action
USING BTREE (tenant, target, active);



CREATE INDEX sp_idx_action_external_ref_sp_action
ON hawkbit.sp_action
USING BTREE (external_ref);



CREATE INDEX sp_idx_action_prim_sp_action
ON hawkbit.sp_action
USING BTREE (tenant, id);



CREATE INDEX sp_idx_action_status_02_sp_action_status
ON hawkbit.sp_action_status
USING BTREE (tenant, action, status);



CREATE INDEX sp_idx_action_status_prim_sp_action_status
ON hawkbit.sp_action_status
USING BTREE (tenant, id);



CREATE INDEX sp_idx_action_status_msgs_01_sp_action_status_messages
ON hawkbit.sp_action_status_messages
USING BTREE (action_status_id);



CREATE INDEX sp_idx_artifact_01_sp_artifact
ON hawkbit.sp_artifact
USING BTREE (tenant, software_module);



CREATE INDEX sp_idx_artifact_02_sp_artifact
ON hawkbit.sp_artifact
USING BTREE (tenant, sha1_hash);



CREATE INDEX sp_idx_artifact_prim_sp_artifact
ON hawkbit.sp_artifact
USING BTREE (tenant, id);



CREATE INDEX sp_idx_base_sw_module_01_sp_base_software_module
ON hawkbit.sp_base_software_module
USING BTREE (tenant, deleted, name, version);



CREATE INDEX sp_idx_base_sw_module_02_sp_base_software_module
ON hawkbit.sp_base_software_module
USING BTREE (tenant, deleted, module_type);



CREATE INDEX sp_idx_base_sw_module_prim_sp_base_software_module
ON hawkbit.sp_base_software_module
USING BTREE (tenant, id);



CREATE INDEX sp_idx_distribution_set_01_sp_distribution_set
ON hawkbit.sp_distribution_set
USING BTREE (tenant, deleted, complete);



CREATE INDEX sp_idx_distribution_set_prim_sp_distribution_set
ON hawkbit.sp_distribution_set
USING BTREE (tenant, id);



CREATE INDEX sp_idx_distribution_set_type_01_sp_distribution_set_type
ON hawkbit.sp_distribution_set_type
USING BTREE (tenant, deleted);



CREATE INDEX sp_idx_distribution_set_type_prim_sp_distribution_set_type
ON hawkbit.sp_distribution_set_type
USING BTREE (tenant, id);



CREATE INDEX sp_idx_distribution_set_tag_01_sp_distributionset_tag
ON hawkbit.sp_distributionset_tag
USING BTREE (tenant, name);



CREATE INDEX sp_idx_distribution_set_tag_prim_sp_distributionset_tag
ON hawkbit.sp_distributionset_tag
USING BTREE (tenant, id);



CREATE INDEX fk_rolloutgroup_rolloutgroup_sp_rolloutgroup
ON hawkbit.sp_rolloutgroup
USING BTREE (parent_id);



CREATE INDEX sp_idx_software_module_type_01_sp_software_module_type
ON hawkbit.sp_software_module_type
USING BTREE (tenant, deleted);



CREATE INDEX sp_idx_software_module_type_prim_sp_software_module_type
ON hawkbit.sp_software_module_type
USING BTREE (tenant, id);



CREATE INDEX sp_idx_target_01_sp_target
ON hawkbit.sp_target
USING BTREE (tenant, name, assigned_distribution_set);



CREATE INDEX sp_idx_target_03_sp_target
ON hawkbit.sp_target
USING BTREE (tenant, controller_id, assigned_distribution_set);



CREATE INDEX sp_idx_target_04_sp_target
ON hawkbit.sp_target
USING BTREE (tenant, created_at);



CREATE INDEX sp_idx_target_prim_sp_target
ON hawkbit.sp_target
USING BTREE (tenant, id);



CREATE INDEX sp_idx_target_tag_01_sp_target_tag
ON hawkbit.sp_target_tag
USING BTREE (tenant, name);



CREATE INDEX sp_idx_target_tag_prim_sp_target_tag
ON hawkbit.sp_target_tag
USING BTREE (tenant, id);



CREATE INDEX sp_idx_tenant_prim_sp_tenant
ON hawkbit.sp_tenant
USING BTREE (tenant, id);



-- ------------ Write CREATE-CONSTRAINT-stage scripts -----------

ALTER TABLE hawkbit.schema_version
ADD CONSTRAINT pk_schema_version PRIMARY KEY (installed_rank);



ALTER TABLE hawkbit.sp_action
ADD CONSTRAINT pk_sp_action PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_action_status
ADD CONSTRAINT pk_sp_action_status PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_artifact
ADD CONSTRAINT pk_sp_artifact PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_base_software_module
ADD CONSTRAINT pk_sp_base_software_module PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_base_software_module
ADD CONSTRAINT uk_base_sw_mod_sp_base_software_module UNIQUE (module_type, name, version, tenant);



ALTER TABLE hawkbit.sp_distribution_set
ADD CONSTRAINT pk_sp_distribution_set PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_distribution_set
ADD CONSTRAINT uk_distrib_set_sp_distribution_set UNIQUE (name, version, tenant);



ALTER TABLE hawkbit.sp_distribution_set_type
ADD CONSTRAINT pk_sp_distribution_set_type PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_distribution_set_type
ADD CONSTRAINT uk_dst_key_sp_distribution_set_type UNIQUE (type_key, tenant);



ALTER TABLE hawkbit.sp_distribution_set_type
ADD CONSTRAINT uk_dst_name_sp_distribution_set_type UNIQUE (name, tenant);



ALTER TABLE hawkbit.sp_distributionset_tag
ADD CONSTRAINT pk_sp_distributionset_tag PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_distributionset_tag
ADD CONSTRAINT uk_ds_tag_sp_distributionset_tag UNIQUE (name, tenant);



ALTER TABLE hawkbit.sp_ds_dstag
ADD CONSTRAINT pk_sp_ds_dstag PRIMARY KEY (ds, tag);



ALTER TABLE hawkbit.sp_ds_metadata
ADD CONSTRAINT pk_sp_ds_metadata PRIMARY KEY (ds_id, meta_key);



ALTER TABLE hawkbit.sp_ds_module
ADD CONSTRAINT pk_sp_ds_module PRIMARY KEY (ds_id, module_id);



ALTER TABLE hawkbit.sp_ds_type_element
ADD CONSTRAINT pk_sp_ds_type_element PRIMARY KEY (distribution_set_type, software_module_type);



ALTER TABLE hawkbit.sp_rollout
ADD CONSTRAINT pk_sp_rollout PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_rollout
ADD CONSTRAINT uk_rollout_sp_rollout UNIQUE (name, tenant);



ALTER TABLE hawkbit.sp_rolloutgroup
ADD CONSTRAINT pk_sp_rolloutgroup PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_rolloutgroup
ADD CONSTRAINT uk_rolloutgroup_sp_rolloutgroup UNIQUE (name, rollout, tenant);



ALTER TABLE hawkbit.sp_rollouttargetgroup
ADD CONSTRAINT pk_sp_rollouttargetgroup PRIMARY KEY (rolloutgroup_id, target_id);



ALTER TABLE hawkbit.sp_software_module_type
ADD CONSTRAINT pk_sp_software_module_type PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_software_module_type
ADD CONSTRAINT uk_smt_name_sp_software_module_type UNIQUE (name, tenant);



ALTER TABLE hawkbit.sp_software_module_type
ADD CONSTRAINT uk_smt_type_key_sp_software_module_type UNIQUE (type_key, tenant);



ALTER TABLE hawkbit.sp_sw_metadata
ADD CONSTRAINT pk_sp_sw_metadata PRIMARY KEY (meta_key, sw_id);



ALTER TABLE hawkbit.sp_target
ADD CONSTRAINT pk_sp_target PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_target
ADD CONSTRAINT uk_tenant_controller_id_sp_target UNIQUE (controller_id, tenant);



ALTER TABLE hawkbit.sp_target_attributes
ADD CONSTRAINT pk_sp_target_attributes PRIMARY KEY (target_id, attribute_key);



ALTER TABLE hawkbit.sp_target_filter_query
ADD CONSTRAINT pk_sp_target_filter_query PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_target_filter_query
ADD CONSTRAINT uk_tenant_custom_filter_name_sp_target_filter_query UNIQUE (name, tenant);



ALTER TABLE hawkbit.sp_target_metadata
ADD CONSTRAINT pk_sp_target_metadata PRIMARY KEY (target_id, meta_key);



ALTER TABLE hawkbit.sp_target_tag
ADD CONSTRAINT pk_sp_target_tag PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_target_tag
ADD CONSTRAINT uk_targ_tag_sp_target_tag UNIQUE (name, tenant);



ALTER TABLE hawkbit.sp_target_target_tag
ADD CONSTRAINT pk_sp_target_target_tag PRIMARY KEY (target, tag);



ALTER TABLE hawkbit.sp_tenant
ADD CONSTRAINT pk_sp_tenant PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_tenant
ADD CONSTRAINT uk_tenantmd_tenant_sp_tenant UNIQUE (tenant);



ALTER TABLE hawkbit.sp_tenant_configuration
ADD CONSTRAINT pk_sp_tenant_configuration PRIMARY KEY (id);



ALTER TABLE hawkbit.sp_tenant_configuration
ADD CONSTRAINT uk_tenant_key_sp_tenant_configuration UNIQUE (conf_key, tenant);



-- ------------ Write CREATE-FOREIGN-KEY-CONSTRAINT-stage scripts -----------

ALTER TABLE hawkbit.sp_action
ADD CONSTRAINT fk_action_ds FOREIGN KEY (distribution_set) 
REFERENCES hawkbit.sp_distribution_set (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;



ALTER TABLE hawkbit.sp_action
ADD CONSTRAINT fk_action_rollout FOREIGN KEY (rollout) 
REFERENCES hawkbit.sp_rollout (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;



ALTER TABLE hawkbit.sp_action
ADD CONSTRAINT fk_action_rolloutgroup FOREIGN KEY (rolloutgroup) 
REFERENCES hawkbit.sp_rolloutgroup (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;



ALTER TABLE hawkbit.sp_action
ADD CONSTRAINT fk_targ_act_hist_targ FOREIGN KEY (target) 
REFERENCES hawkbit.sp_target (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_action_status
ADD CONSTRAINT fk_act_stat_action FOREIGN KEY (action) 
REFERENCES hawkbit.sp_action (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_action_status_messages
ADD CONSTRAINT fk_stat_msg_act_stat FOREIGN KEY (action_status_id) 
REFERENCES hawkbit.sp_action_status (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_artifact
ADD CONSTRAINT fk_assigned_sm FOREIGN KEY (software_module) 
REFERENCES hawkbit.sp_base_software_module (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_base_software_module
ADD CONSTRAINT fk_module_type FOREIGN KEY (module_type) 
REFERENCES hawkbit.sp_software_module_type (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;



ALTER TABLE hawkbit.sp_distribution_set
ADD CONSTRAINT fk_ds_dstype_ds FOREIGN KEY (ds_id) 
REFERENCES hawkbit.sp_distribution_set_type (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;



ALTER TABLE hawkbit.sp_ds_dstag
ADD CONSTRAINT fk_ds_dstag_ds FOREIGN KEY (ds) 
REFERENCES hawkbit.sp_distribution_set (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_ds_dstag
ADD CONSTRAINT fk_ds_dstag_tag FOREIGN KEY (tag) 
REFERENCES hawkbit.sp_distributionset_tag (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_ds_metadata
ADD CONSTRAINT fk_metadata_ds FOREIGN KEY (ds_id) 
REFERENCES hawkbit.sp_distribution_set (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_ds_module
ADD CONSTRAINT fk_ds_module_ds FOREIGN KEY (ds_id) 
REFERENCES hawkbit.sp_distribution_set (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_ds_module
ADD CONSTRAINT fk_ds_module_module FOREIGN KEY (module_id) 
REFERENCES hawkbit.sp_base_software_module (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_ds_type_element
ADD CONSTRAINT fk_ds_type_element_element FOREIGN KEY (distribution_set_type) 
REFERENCES hawkbit.sp_distribution_set_type (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_ds_type_element
ADD CONSTRAINT fk_ds_type_element_smtype FOREIGN KEY (software_module_type) 
REFERENCES hawkbit.sp_software_module_type (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_rollout
ADD CONSTRAINT fk_rollout_ds FOREIGN KEY (distribution_set) 
REFERENCES hawkbit.sp_distribution_set (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;



ALTER TABLE hawkbit.sp_rolloutgroup
ADD CONSTRAINT fk_rolloutgroup_rollout FOREIGN KEY (rollout) 
REFERENCES hawkbit.sp_rollout (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_rollouttargetgroup
ADD CONSTRAINT fk_rollouttargetgroup_rolloutgroup FOREIGN KEY (rolloutgroup_id) 
REFERENCES hawkbit.sp_rolloutgroup (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_rollouttargetgroup
ADD CONSTRAINT fk_rollouttargetgroup_target FOREIGN KEY (target_id) 
REFERENCES hawkbit.sp_target (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_sw_metadata
ADD CONSTRAINT fk_metadata_sw FOREIGN KEY (sw_id) 
REFERENCES hawkbit.sp_base_software_module (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_target
ADD CONSTRAINT fk_target_assign_ds FOREIGN KEY (assigned_distribution_set) 
REFERENCES hawkbit.sp_distribution_set (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;



ALTER TABLE hawkbit.sp_target
ADD CONSTRAINT fk_target_inst_ds FOREIGN KEY (installed_distribution_set) 
REFERENCES hawkbit.sp_distribution_set (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;



ALTER TABLE hawkbit.sp_target_attributes
ADD CONSTRAINT fk_targ_attrib_target FOREIGN KEY (target_id) 
REFERENCES hawkbit.sp_target (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_target_filter_query
ADD CONSTRAINT fk_filter_auto_assign_ds FOREIGN KEY (auto_assign_distribution_set) 
REFERENCES hawkbit.sp_distribution_set (id)
ON UPDATE RESTRICT
ON DELETE SET NULL;



ALTER TABLE hawkbit.sp_target_metadata
ADD CONSTRAINT fk_metadata_target FOREIGN KEY (target_id) 
REFERENCES hawkbit.sp_target (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_target_target_tag
ADD CONSTRAINT fk_targ_targtag_tag FOREIGN KEY (tag) 
REFERENCES hawkbit.sp_target_tag (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_target_target_tag
ADD CONSTRAINT fk_targ_targtag_target FOREIGN KEY (target) 
REFERENCES hawkbit.sp_target (id)
ON UPDATE RESTRICT
ON DELETE CASCADE;



ALTER TABLE hawkbit.sp_tenant
ADD CONSTRAINT fk_tenant_md_default_ds_type FOREIGN KEY (default_ds_type) 
REFERENCES hawkbit.sp_distribution_set_type (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT;