Skip to content

Instantly share code, notes, and snippets.

@Antiarchitect
Created February 14, 2020 09:18
Show Gist options
  • Save Antiarchitect/a0f7a866b9734cf5d99f4b7367cd50b2 to your computer and use it in GitHub Desktop.
Save Antiarchitect/a0f7a866b9734cf5d99f4b7367cd50b2 to your computer and use it in GitHub Desktop.
-- ------------ 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment