SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; DROP SCHEMA IF EXISTS openbook; CREATE SCHEMA openbook DEFAULT CHARACTER SET utf8; GRANT ALL PRIVILEGES ON openbook.* TO openbook@'%' IDENTIFIED BY '{% raw %}{{ openbook_database_password }}{% endraw %}'; GRANT ALL PRIVILEGES ON openbook.* TO openbook@localhost IDENTIFIED BY '{% raw %}{{ openbook_database_password }}{% endraw %}'; FLUSH PRIVILEGES; USE openbook; CREATE TABLE t_audit_event ( event_id INT UNSIGNED NOT NULL AUTO_INCREMENT, event_time DATETIME(6) NOT NULL, event_type VARCHAR(32) NOT NULL, entity_id VARCHAR(64) NOT NULL, entity_name VARCHAR(255) NULL, entity_type VARCHAR(45) NOT NULL, user_id VARCHAR(128) NULL, event_data LONGTEXT NULL, PRIMARY KEY (event_id), INDEX ix_audit_event_1 (event_time, event_type, entity_id), INDEX ix_audit_event_2 (user_id, event_time)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_billable_feature ( billable_feature_id INT UNSIGNED NOT NULL AUTO_INCREMENT, billable_feature_category VARCHAR(128) NOT NULL, billable_feature_vendor_type VARCHAR(128) NOT NULL, billable_feature_type VARCHAR(128) NOT NULL, billable_feature_subtypes LONGTEXT NULL DEFAULT NULL, billable_feature_subtype_classifier LONGTEXT NULL DEFAULT NULL, billable_feature_description LONGTEXT NULL DEFAULT NULL, billable_feature_details LONGTEXT NULL DEFAULT NULL, PRIMARY KEY (billable_feature_id), UNIQUE INDEX ix_billable_feature_1 (billable_feature_vendor_type ASC, billable_feature_type ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_credit ( credit_id INT UNSIGNED NOT NULL AUTO_INCREMENT, payer_id INT UNSIGNED NOT NULL, payer_type VARCHAR(64) NOT NULL, credit_amount DECIMAL(19,2) NULL, remaining_amount DECIMAL(19,2) NULL, credit_type VARCHAR(16) NOT NULL, granted_by VARCHAR(256) NOT NULL, posted_date DATETIME(6) NOT NUll, expire_date DATETIME(6) NOT NULL, currency VARCHAR(3) NOT NULL, PRIMARY KEY (credit_id), INDEX ix_credit_1 (payer_id, payer_type, expire_date)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_customer ( customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT, resource_manager_id VARCHAR(36) NULL DEFAULT NULL, resource_manager_foreign_id VARCHAR(512) NULL DEFAULT NULL, customer_name VARCHAR(512) NOT NULL, other_foreign_ids LONGTEXT NULL DEFAULT NULL, tax_rate DECIMAL(18,4) NULL DEFAULT 0.0000, currency CHAR(6) NULL DEFAULT NULL, discount_percent DECIMAL(18,4) NULL DEFAULT 0.0000, billing_frequency VARCHAR(40) NULL DEFAULT NULL, last_bill_date DATETIME(6) NULL DEFAULT NULL, next_bill_date DATETIME(6) NULL DEFAULT NULL, is_active TINYINT(1) NULL DEFAULT 1, create_date DATETIME(6) NULL DEFAULT NULL, activate_date DATETIME(6) NULL DEFAULT NULL, billing_start_date DATETIME(6) NULL DEFAULT NULL, last_payment_date DATETIME(6) NULL DEFAULT NULL, deactivate_date DATETIME(6) NULL DEFAULT NULL, description VARCHAR(5120) NULL DEFAULT NULL, minimum_invoice_commitment DECIMAL(19,4) NULL, invoicing_method VARCHAR(32) NOT NULL, management_region_id VARCHAR(64) NULL, infrastructure_region_id VARCHAR(64) NULL, billing_address_line_1 VARCHAR(512) NULL, billing_address_line_2 VARCHAR(512) NULL, billing_address_city VARCHAR(512) NULL, billing_address_region VARCHAR(128) NULL, billing_address_country VARCHAR(2) NULL, billing_address_postal_code VARCHAR(64) NULL, payer_payment_details VARCHAR(5120) NULL, payment_method_confirmed TINYINT(1) DEFAULT 0, balance_due DECIMAL(19,4) NULL DEFAULT 0, PRIMARY KEY (customer_id), INDEX ix_customer_1 (resource_manager_id ASC), INDEX ix_customer_2 (resource_manager_foreign_id(255) ASC), UNIQUE INDEX ix_customer_3 (management_region_id, customer_name(255))) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_discount ( discount_id INT UNSIGNED NOT NULL AUTO_INCREMENT, payer_id INT UNSIGNED NOT NULL, payer_type VARCHAR(64) NOT NULL, discount_percent DECIMAL(5,2) NOT NULL, granted_by VARCHAR(256) NOT NULL, apply_to_charge_category VARCHAR(32) NOT NULL, trigger_charge_category VARCHAR(32) NULL, trigger_amount DECIMAL(19,2) NULL, start_date DATETIME(6) NOT NULL, end_date DATETIME(6) NOT NULL, PRIMARY KEY (discount_id), INDEX ix_discount_1 (payer_id, payer_type, end_date)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_email_notification ( message_id INT UNSIGNED NOT NULL AUTO_INCREMENT, recipient VARCHAR(128) NOT NULL, sender VARCHAR(128) NOT NULL, subject VARCHAR(256) NOT NULL, message_text LONGTEXT NOT NULL, smtp_server_details LONGTEXT NOT NULL, date_generated DATETIME(6) NOT NULL, date_sent DATETIME(6) NULL, date_last_send_attempt DATETIME(6) NULL, send_status VARCHAR(16) NULL, send_status_detail VARCHAR(5120) NULL, send_attempt_count SMALLINT NULL, PRIMARY KEY (message_id), INDEX ix_email_notification_1 (recipient, date_generated), INDEX ix_email_notification_2 (sender, date_generated), INDEX ix_email_notification_3 (date_sent)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_email_template ( provider_id INT UNSIGNED NOT NULL AUTO_INCREMENT, template_subject VARCHAR(256) NOT NULL, template_name VARCHAR(128) NOT NULL, template_text LONGTEXT NOT NULL, PRIMARY KEY (provider_id, template_name)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_entity_attribute ( entity_id INT UNSIGNED NOT NULL AUTO_INCREMENT, attribute_class VARCHAR(32) NOT NULL, attribute_category VARCHAR(128) NOT NULL, attribute_name VARCHAR(128) NOT NULL, attribute_value VARCHAR(512) NOT NULL, attribute_unit VARCHAR(64) NULL DEFAULT NULL, add_date DATETIME(6) NULL DEFAULT NULL, expire_date DATETIME(6) NOT NULL, PRIMARY KEY (entity_id ASC, expire_date ASC, attribute_class ASC, attribute_category ASC, attribute_name ASC), INDEX ix_entity_attribute_1 (attribute_class ASC), INDEX ix_entity_attribute_2 (attribute_category ASC), INDEX ix_entity_attribute_3 (attribute_name ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_invoice ( invoice_id INT UNSIGNED NOT NULL AUTO_INCREMENT, payer_id INT UNSIGNED NOT NULL, payer_type VARCHAR(64) NOT NULL, payer_name VARCHAR(512) NOT NULL, invoice_number VARCHAR(45) NULL, invoice_date DATETIME(6) NOT NULL, period_begin DATETIME(6) NOT NULL, period_end DATETIME(6) NOT NULL, invoice_amount DECIMAL(19,2) NOT NULL, actual_charges DECIMAL(19,2) NOT NULL, invoice_currency VARCHAR(16) NULL, management_region_id INT UNSIGNED NOT NULL, balance_due DECIMAL(19,2) NOT NULL, PRIMARY KEY (invoice_id), INDEX ix_invoice_1 (management_region_id, payer_type, payer_id ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_invoice_detail ( invoice_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, resource_group_id VARCHAR(36) NULL, invoice_group VARCHAR(128) NULL, item_name VARCHAR(512) NULL, item_vendor_type VARCHAR(128) NULL, item_type VARCHAR(128) NOT NULL, item_subtype VARCHAR(128) NOT NULL, item_description VARCHAR(512) NOT NULL, rate_plan_id VARCHAR(36) NULL, rate_plan_billable_feature_id VARCHAR(36) NULL DEFAULT NULL, billing_interval VARCHAR(64) NULL DEFAULT NULL, item_invoice_amount DECIMAL(19,2) NOT NULL, num_units VARCHAR(32) NOT NULL, num_units_unit VARCHAR(16) NULL, INDEX ix_invoice_detail_1 (invoice_id ASC), INDEX ix_invoice_detail_2 (customer_id ASC), INDEX ix_invoice_detail_3 (item_vendor_type, item_type, item_subtype ASC), INDEX ix_invoice_detail_4 (rate_plan_id ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_job_status ( job_id VARCHAR(256) NOT NULL, start_time DATETIME(6) NOT NULL, end_time DATETIME(6) NULL DEFAULT NULL, status VARCHAR(32) NULL DEFAULT NULL, result VARCHAR(32) NULL DEFAULT NULL, result_detail LONGTEXT NULL DEFAULT NULL, PRIMARY KEY (job_id(255), start_time)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_lookup_values ( type VARCHAR(128) NOT NULL, name VARCHAR(256) NOT NULL, value LONGTEXT NOT NULL, PRIMARY KEY (type, name(255))) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_parent_child_relationships ( parent_id INT UNSIGNED NOT NULL, parent_type VARCHAR(64) NOT NULL, child_id INT UNSIGNED NOT NULL, child_type VARCHAR(64) NOT NULL, assigned_quantity INT NOT NULL DEFAULT 1, add_date DATETIME(6) NOT NULL, remove_date DATETIME(6) NOT NULL, PRIMARY KEY (parent_id, parent_type, child_id, child_type, add_date, remove_date), INDEX ix_parent_child_relationships_1 (child_id, child_type ASC), INDEX ix_parent_child_relationships_2 (parent_id, parent_type ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_payment ( payment_id INT UNSIGNED NOT NULL AUTO_INCREMENT, payer_id INT UNSIGNED NOT NULL, payer_type VARCHAR(64) NOT NULL, payment_date DATETIME(6) NOT NULL, amount DECIMAL(19,2) NOT NULL, payment_method VARCHAR(64) NOT NULL, payment_reference VARCHAR(256) NULL, payment_method_detail LONGTEXT NULL DEFAULT NULL, payment_status VARCHAR(64) NULL DEFAULT NULL, payment_status_detail LONGTEXT NULL DEFAULT NULL, PRIMARY KEY (payment_id), INDEX ix_payment_1 (payer_type, payer_id, payment_date)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_promo_code ( management_region_id INT UNSIGNED NOT NULL, promo_code_id INT UNSIGNED NOT NULL AUTO_INCREMENT, promo_code VARCHAR(128) NOT NULL, salesperson VARCHAR(128) NULL, discount_or_credit VARCHAR(8) NOT NULL, credit_amount DECIMAL(19,2) NULL, credit_type VARCHAR(16) NULL, apply_to_charge_category VARCHAR(32) NULL, trigger_charge_category VARCHAR(32) NULL, trigger_amount DECIMAL(19,2) NULL, discount_percent DECIMAL(5,2) NULL, available_invoice_periods INT NULL, expire_date DATETIME(6) NULL, currency VARCHAR(3) NULL DEFAULT NULL, PRIMARY KEY (promo_code_id), INDEX ix_promo_code_1 (promo_code), INDEX ix_promo_code_2 (salesperson), UNIQUE INDEX ix_promo_code_3 (management_region_id, promo_code)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_promo_code_redemption ( promo_code_id INT UNSIGNED NOT NULL, payer_id INT UNSIGNED NOT NULL, payer_type VARCHAR(64) NOT NULL, redemption_date DATETIME(6) NULL, PRIMARY KEY (payer_id, promo_code_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_provider ( provider_id INT UNSIGNED NOT NULL AUTO_INCREMENT, provider_name VARCHAR(512) NOT NULL, provider_type VARCHAR(64) NOT NULL, other_foreign_ids LONGTEXT NULL DEFAULT NULL, tax_rate DECIMAL(18,4) NULL DEFAULT 0.0000, currency CHAR(6) NULL DEFAULT NULL, discount_percent DECIMAL(18,4) NULL DEFAULT 0.0000, billing_frequency VARCHAR(40) NULL DEFAULT NULL, last_bill_date DATETIME(6) NULL DEFAULT NULL, next_bill_date DATETIME(6) NULL DEFAULT NULL, last_payment_date DATETIME(6) NULL DEFAULT NULL, is_active TINYINT(1) NULL DEFAULT 1, create_date DATETIME(6) NULL DEFAULT NULL, activate_date DATETIME(6) NULL DEFAULT NULL, billing_start_date DATETIME(6) NULL DEFAULT NULL, deactivate_date DATETIME(6) NULL DEFAULT NULL, description LONGTEXT NULL DEFAULT NULL, minimum_invoice_commitment DECIMAL(19,2) NULL, invoicing_method VARCHAR(32) NULL DEFAULT NULL, invoice_processing_method VARCHAR(32) NULL DEFAULT NULL, billing_address_line_1 VARCHAR(512) NULL, billing_address_line_2 VARCHAR(512) NULL, billing_address_city VARCHAR(512) NULL, billing_address_region VARCHAR(128) NULL, billing_address_country VARCHAR(2) NULL, billing_address_postal_code VARCHAR(64) NULL, legal_address_line_1 VARCHAR(512) NULL, legal_address_line_2 VARCHAR(512) NULL, legal_address_city VARCHAR(512) NULL, legal_address_region VARCHAR(128) NULL, legal_address_country VARCHAR(2) NULL, legal_address_postal_code VARCHAR(64) NULL, payer_payment_details VARCHAR (5120) NULL, payee_payment_details VARCHAR(5120) NULL, public_url VARCHAR(512) NOT NULL, private_url VARCHAR(512) NOT NULL, management_region_id VARCHAR(64) NOT NULL, notification_settings LONGTEXT NOT NULL, personalization_settings LONGTEXT NOT NULL, balance_due DECIMAL(19,2) NULL, openbook_processes_payments TINYINT(1) NULL DEFAULT 1, openbook_processes_past_due_invoices TINYINT(1) NULL DEFAULT 1, openstack_tenant_id VARCHAR(36) NULL DEFAULT NULL, PRIMARY KEY (provider_id), UNIQUE INDEX ix_provider_1 (public_url(255)), UNIQUE INDEX ix_provider_2 (management_region_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_provider_region ( provider_id INT UNSIGNED NOT NULL, region_id INT UNSIGNED NOT NULL, PRIMARY KEY (provider_id, region_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_provisioned_entity ( entity_id INT UNSIGNED NOT NULL AUTO_INCREMENT, billable_feature_id INT UNSIGNED NOT NULL, entity_name VARCHAR(256) NOT NULL, entity_subtype VARCHAR(128) NULL DEFAULT NULL, customer_id INT UNSIGNED NOT NULL, resource_manager_id INT UNSIGNED NOT NULL, infrastructure_region_id INT UNSIGNED NOT NULL, entity_foreign_id VARCHAR(256) NULL DEFAULT NULL, provisioned_date DATETIME(6) NULL DEFAULT NULL, deprovisioned_date DATETIME(6) NULL DEFAULT NULL, PRIMARY KEY (entity_id), INDEX ix_provisioned_entity_1 (entity_foreign_id(255) ASC), INDEX ix_provisioned_entity_2 (customer_id), INDEX ix_provisioned_entity_3 (resource_manager_id ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_rate_plan ( rate_plan_id INT UNSIGNED NOT NULL AUTO_INCREMENT, rate_plan_name VARCHAR(255) NULL DEFAULT NULL, applies_to VARCHAR(32) NOT NULL, rate_plan_description VARCHAR(1024) NULL DEFAULT NULL, proration_details LONGTEXT NULL DEFAULT NULL, currency CHAR(6) NULL DEFAULT NULL, discount_percent VARCHAR(20) NULL DEFAULT NULL, management_region_id VARCHAR(64) NOT NULL, PRIMARY KEY (rate_plan_id), INDEX ix_rate_plan_1 (management_region_id ASC), UNIQUE INDEX ix_rate_plan_2 (management_region_id, rate_plan_name)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_rate_plan_assignment ( rate_plan_id INT UNSIGNED NOT NULL, assignee_id INT UNSIGNED NOT NULL, assignee_type VARCHAR(64) NOT NULL, assignment_start_date DATETIME(6) NOT NULL, assignment_end_date DATETIME(6) NOT NULL, assignment_type VARCHAR(64) NOT NULL, PRIMARY KEY (rate_plan_id, assignee_id, assignment_start_date, assignment_end_date), INDEX ix_rate_plan_assignment_1 (assignee_type, assignee_id, assignment_start_date, assignment_end_date ASC), INDEX ix_rate_plan_assignment_2 (assignment_type, assignee_id, assignment_start_date, assignment_end_date ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_rate_plan_billable_attribute ( rate_plan_id INT UNSIGNED NOT NULL, feature_type VARCHAR(128) NOT NULL, attribute_class VARCHAR(32) NOT NULL, attribute_category VARCHAR(128) NOT NULL, attribute_name VARCHAR(128) NOT NULL, attribute_value VARCHAR(512) NOT NULL, attribute_unit VARCHAR(64) NULL DEFAULT NULL, attribute_datatype VARCHAR(32) NOT NULL, charge VARCHAR(32) NOT NULL, operator VARCHAR(32) NULL DEFAULT NULL, billing_scheme VARCHAR(64) NOT NULL, cost_calculation_frequency VARCHAR(64) NOT NULL, filter TEXT NULL, infrastructure_region_id INT UNSIGNED NOT NULL DEFAULT 0, INDEX ix_rate_plan_billable_attribute_1 (rate_plan_id, infrastructure_region_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_rate_plan_billable_feature ( rate_plan_id INT UNSIGNED NOT NULL, billable_feature_id INT UNSIGNED NOT NULL, billable_feature_subtype VARCHAR(128) NOT NULL, billable_interval_type VARCHAR(64) NOT NULL, cost_calculation_frequency VARCHAR(64) NOT NULL, charge VARCHAR(100) NULL DEFAULT NULL, infrastructure_region_id INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (rate_plan_id, infrastructure_region_id, billable_feature_id, billable_feature_subtype, billable_interval_type)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_rate_plan_billable_metric ( rate_plan_id INT UNSIGNED NOT NULL, vendor_type VARCHAR(128) NOT NULL, metric_name VARCHAR(128) NOT NULL, metric_unit VARCHAR(32) NULL DEFAULT NULL, metric_type VARCHAR(32) NULL DEFAULT NULL, billing_scheme VARCHAR(64) NOT NULL, billing_scheme_details LONGTEXT NULL DEFAULT NULL, infrastructure_region_id INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (rate_plan_id, infrastructure_region_id, vendor_type, metric_name, billing_scheme)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_rate_plan_billable_event ( rate_plan_id INT UNSIGNED NOT NULL, billable_feature_id INT UNSIGNED NOT NULL, billable_feature_subtype VARCHAR(128) NOT NULL, event_type VARCHAR(128) NOT NULL, charge VARCHAR(100) NOT NULL, billing_scheme_details LONGTEXT NULL DEFAULT NULL, infrastructure_region_id INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (rate_plan_id, infrastructure_region_id, billable_feature_id, billable_feature_subtype, event_type)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_region ( region_id INT UNSIGNED NOT NULL AUTO_INCREMENT, region_name VARCHAR(128) NOT NULL, region_type VARCHAR(64) NOT NULL, parent_region_id INT UNSIGNED NULL DEFAULT NULL, PRIMARY KEY (region_id), UNIQUE INDEX ix_region_1 (parent_region_id, region_type, region_name ASC), INDEX ix_region_2 (parent_region_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_resource_configuration_history ( resource_id INT UNSIGNED NOT NULL, resource_name VARCHAR(512) NULL DEFAULT NULL, resource_foreign_id VARCHAR(146) NULL DEFAULT NULL, resource_configuration LONGTEXT NOT NULL, start_date DATETIME(6) NOT NULL, end_date DATETIME(6) NOT NULL, customer_id LONGTEXT NULL DEFAULT NULL, customer_foreign_id VARCHAR(146) NULL DEFAULT NULL, customer_name VARCHAR(512) NULL DEFAULT NULL, resource_manager_id VARCHAR(72) NULL DEFAULT NULL, resource_manager_name VARCHAR(512) NULL DEFAULT NULL, resource_manager_foreign_id VARCHAR(72) NULL DEFAULT NULL) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_resource_event ( event_id VARCHAR(100) NOT NULL, event_type VARCHAR(128) NOT NULL, event_date DATETIME(6) NOT NULL, resource_id VARCHAR(36) NULL DEFAULT NULL, resource_name VARCHAR(256) NULL DEFAULT NULL, resource_foreign_id VARCHAR(512) NULL DEFAULT NULL, customer_id VARCHAR(36) NULL DEFAULT NULL, customer_name VARCHAR(512) NULL DEFAULT NULL, customer_foreign_id VARCHAR(512) NULL DEFAULT NULL, resource_manager_id VARCHAR(36) NOT NULL, resource_manager_foreign_id VARCHAR(512) NULL DEFAULT NULL, infrastructure_region_id INT UNSIGNED NULL DEFAULT NULL, management_region_id INT UNSIGNED NULL DEFAULT NULL, PRIMARY KEY (resource_manager_id, event_id), INDEX ix_vm_event_1 (resource_foreign_id(255) ASC, event_date ASC), INDEX ix_vm_event_2 (customer_id ASC), INDEX ix_vm_event_3 (resource_name(255), event_date DESC), INDEX ix_vm_event_4 (resource_manager_id ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_resource_group ( resource_group_id INT UNSIGNED NOT NULL AUTO_INCREMENT, resource_group_name VARCHAR(512) NOT NULL, resource_group_type VARCHAR(64) NULL DEFAULT NULL, customer_id INT UNSIGNED NOT NULL, parent_id INT UNSIGNED NOT NULL, parent_type VARCHAR(64) NOT NULL, PRIMARY KEY (resource_group_id), INDEX ix_resource_group_1 (parent_id ASC), INDEX ix_resource_group_2 (customer_id ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_resource_in_use_interval ( resource_id VARCHAR(36) NULL DEFAULT NULL, resource_name VARCHAR(512) NULL DEFAULT NULL, resource_foreign_id LONGTEXT NULL DEFAULT NULL, interval_type VARCHAR(64) NOT NULL, start_date DATETIME(6) NOT NULL, end_date DATETIME(6) NOT NULL, interval_length_milliseconds BIGINT(20) NULL DEFAULT NULL, resource_manager_id VARCHAR(36) NULL DEFAULT NULL, resource_manager_name VARCHAR(512) NULL DEFAULT NULL, resource_manager_foreign_id VARCHAR(512) NULL DEFAULT NULL, customer_id VARCHAR(36) NULL DEFAULT NULL, customer_name VARCHAR(512) NULL DEFAULT NULL, customer_foreign_id VARCHAR(512) NULL DEFAULT NULL, management_region_id INT UNSIGNED NOT NULL, PRIMARY KEY (resource_id, interval_type, start_date, end_date), INDEX ix_resource_in_use_interval_1 (resource_name(255) ASC), INDEX ix_resource_in_use_interval_2 (customer_name(255) ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_resource_manager ( resource_manager_id INT UNSIGNED NOT NULL AUTO_INCREMENT, resource_manager_name VARCHAR(512) NOT NULL, resource_manager_description VARCHAR(1024) NULL DEFAULT NULL, resource_manager_vendor_type VARCHAR(64) NULL DEFAULT NULL, resource_manager_foreign_id VARCHAR(512) NULL DEFAULT NULL, metadata LONGTEXT NULL DEFAULT NULL, last_metric_sync_times VARCHAR(5120) NULL DEFAULT NULL, last_entity_sync_times VARCHAR(5120) NULL DEFAULT NULL, infrastructure_region_id INT UNSIGNED NOT NULL, PRIMARY KEY (resource_manager_id)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_resource_manager_sync_times ( resource_manager_id INT UNSIGNED NOT NULL, sync_time_key VARCHAR(255) NOT NULL, sync_time VARCHAR(255) NOT NULL, PRIMARY KEY (resource_manager_id, sync_time_key)) ENGINE=InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_usage_metric ( resource_id INT UNSIGNED NULL DEFAULT 0, resource_foreign_id VARCHAR(255) NOT NULL, resource_name VARCHAR(256) NULL DEFAULT NULL, resource_type VARCHAR(256) NULL DEFAULT NULL, metric_name VARCHAR(128) NOT NULL, metric_interval VARCHAR(64) NULL DEFAULT 600, collection_time DATETIME(6) NOT NULL, collection_hour varchar(10) NOT NULL, metric_value DOUBLE NOT NULL, metric_unit VARCHAR(64) NOT NULL, customer_id INT UNSIGNED NOT NULL, customer_name VARCHAR(256) NULL DEFAULT NULL, customer_foreign_id VARCHAR(255) NULL DEFAULT NULL, resource_manager_id INT UNSIGNED NOT NULL, infrastructure_region_id INT UNSIGNED NOT NULL, management_region_id INT UNSIGNED NOT NULL, PRIMARY KEY (resource_foreign_id, metric_name, collection_time), INDEX ix_usage_metrics_2 (customer_id, collection_time, resource_foreign_id, metric_name)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_user_permissions ( user_id INT UNSIGNED NOT NULL, permission LONGTEXT NOT NULL, PRIMARY KEY (user_id, permission(255))) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_user_roles ( user_id INT UNSIGNED NOT NULL, role_name VARCHAR(128) NOT NULL, role_context INT UNSIGNED NOT NULL, context_type VARCHAR(64) NOT NULL, UNIQUE INDEX (user_id, role_name, role_context, context_type)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_users ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(128) NOT NULL, first_name VARCHAR(256) NULL DEFAULT NULL, last_name VARCHAR(256) NULL DEFAULT NULL, email_address VARCHAR(512) NULL DEFAULT NULL, phone_number VARCHAR(64) NULL DEFAULT NULL, password VARCHAR(512) NOT NULL, is_active TINYINT(1) NULL DEFAULT NULL, password_change_token VARCHAR(64) NULL, last_password_change_date DATETIME(6) NULL, password_change_token_expire_date DATETIME(6) NULL, PRIMARY KEY (user_id), UNIQUE INDEX (username)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_unbilled_charges ( invoice_id INT UNSIGNED NOT NULL AUTO_INCREMENT, payer_id INT UNSIGNED NOT NULL, payer_type VARCHAR(64) NOT NULL, payer_name VARCHAR(512) NOT NULL, invoice_number VARCHAR(45) NULL, invoice_date DATETIME(6) NOT NULL, period_begin DATETIME(6) NOT NULL, period_end DATETIME(6) NOT NULL, invoice_amount DECIMAL(19,2) NOT NULL, actual_charges DECIMAL(19,2) NOT NULL, invoice_currency VARCHAR(16) NULL, management_region_id INT UNSIGNED NOT NULL, PRIMARY KEY (invoice_id), INDEX t_unbilled_charges_1 (management_region_id, payer_type, payer_id ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; CREATE TABLE t_unbilled_charges_detail ( invoice_id INT UNSIGNED NOT NULL, customer_id INT UNSIGNED NOT NULL, resource_group_id VARCHAR(36) NULL, invoice_group VARCHAR(128) NULL, item_name VARCHAR(512) NULL, item_vendor_type VARCHAR(128) NULL, item_type VARCHAR(128) NOT NULL, item_subtype VARCHAR(128) NOT NULL, item_description VARCHAR(512) NOT NULL, rate_plan_id VARCHAR(36) NULL, rate_plan_billable_feature_id VARCHAR(36) NULL DEFAULT NULL, billing_interval VARCHAR(64) NULL DEFAULT NULL, item_invoice_amount DECIMAL(19,2) NOT NULL, num_units VARCHAR(32) NOT NULL, num_units_unit VARCHAR(16) NULL, INDEX ix_unbilled_charges_detail_1 (invoice_id ASC), INDEX ix_unbilled_charges_detail_2 (customer_id ASC), INDEX ix_unbilled_charges_detail_3 (item_vendor_type, item_type, item_subtype ASC), INDEX ix_unbilled_charges_detail_4 (rate_plan_id ASC)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8; -- ----------------------------------------------------- -- Views -- ----------------------------------------------------- CREATE VIEW v_invoice_detail AS SELECT i.*, c.customer_name FROM (t_invoice_detail i LEFT JOIN t_customer c ON ((i.customer_id = c.customer_id))); CREATE VIEW v_customer AS SELECT c.*, r1.region_name as management_region_name, r2.region_name as infrastructure_region_name FROM (t_customer c LEFT JOIN t_region r1 ON (c.management_region_id = r1.region_id) LEFT JOIN t_region r2 ON (c.infrastructure_region_id = r2.region_id)); CREATE VIEW v_rate_plan_assignment AS SELECT a.*, r.management_region_id FROM (t_rate_plan_assignment a LEFT JOIN t_rate_plan r ON (a.rate_plan_id = r.rate_plan_id)); CREATE VIEW v_users AS SELECT u.*, r.role_name, r.role_context FROM (t_users u LEFT JOIN t_user_roles r ON (u.user_id = r.user_id)); CREATE VIEW v_provider AS SELECT t_provider.*, t_region.parent_region_id as parent_management_region_id FROM (t_provider LEFT JOIN t_region ON (t_region.region_id = t_provider.management_region_id)); CREATE VIEW v_provider_region AS SELECT t_region.*, t_provider_region.provider_id FROM (t_region LEFT JOIN t_provider_region ON (t_region.region_id = t_provider_region.region_id)); CREATE VIEW v_unbilled_charges_detail AS SELECT i.*, c.customer_name FROM (t_unbilled_charges_detail i LEFT JOIN t_customer c ON ((i.customer_id = c.customer_id))); commit; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; -- ----------------------------------------------------- -- Recreate workflow schema and tables. -- ----------------------------------------------------- DROP SCHEMA IF EXISTS openbook_workflow; CREATE SCHEMA openbook_workflow DEFAULT CHARACTER SET utf8; GRANT ALL PRIVILEGES ON openbook_workflow.* TO openbook@'%' IDENTIFIED BY '{% raw %}{{ openbook_database_password }}{% endraw %}'; GRANT ALL PRIVILEGES ON openbook_workflow.* TO openbook@localhost IDENTIFIED BY '{% raw %}{{ openbook_database_password }}{% endraw %}'; FLUSH PRIVILEGES; USE openbook_workflow; create table ACT_GE_PROPERTY ( NAME_ varchar(64), VALUE_ varchar(300), REV_ integer, primary key (NAME_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; insert into ACT_GE_PROPERTY values ('schema.version', '5.20.0.1', 1); insert into ACT_GE_PROPERTY values ('schema.history', 'create(5.20.0.1)', 1); insert into ACT_GE_PROPERTY values ('next.dbid', '1', 1); create table ACT_GE_BYTEARRAY ( ID_ varchar(64), REV_ integer, NAME_ varchar(255), DEPLOYMENT_ID_ varchar(64), BYTES_ LONGBLOB, GENERATED_ TINYINT, primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_RE_DEPLOYMENT ( ID_ varchar(64), NAME_ varchar(255), CATEGORY_ varchar(255), TENANT_ID_ varchar(255) default '', DEPLOY_TIME_ timestamp(3) NULL, primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_RE_MODEL ( ID_ varchar(64) not null, REV_ integer, NAME_ varchar(255), KEY_ varchar(255), CATEGORY_ varchar(255), CREATE_TIME_ timestamp(3) null, LAST_UPDATE_TIME_ timestamp(3) null, VERSION_ integer, META_INFO_ varchar(4000), DEPLOYMENT_ID_ varchar(64), EDITOR_SOURCE_VALUE_ID_ varchar(64), EDITOR_SOURCE_EXTRA_VALUE_ID_ varchar(64), TENANT_ID_ varchar(255) default '', primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_RU_EXECUTION ( ID_ varchar(64), REV_ integer, PROC_INST_ID_ varchar(64), BUSINESS_KEY_ varchar(255), PARENT_ID_ varchar(64), PROC_DEF_ID_ varchar(64), SUPER_EXEC_ varchar(64), ACT_ID_ varchar(255), IS_ACTIVE_ TINYINT, IS_CONCURRENT_ TINYINT, IS_SCOPE_ TINYINT, IS_EVENT_SCOPE_ TINYINT, SUSPENSION_STATE_ integer, CACHED_ENT_STATE_ integer, TENANT_ID_ varchar(255) default '', NAME_ varchar(255), LOCK_TIME_ timestamp(3) NULL, primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_RU_JOB ( ID_ varchar(64) NOT NULL, REV_ integer, TYPE_ varchar(255) NOT NULL, LOCK_EXP_TIME_ timestamp(3) NULL, LOCK_OWNER_ varchar(255), EXCLUSIVE_ boolean, EXECUTION_ID_ varchar(64), PROCESS_INSTANCE_ID_ varchar(64), PROC_DEF_ID_ varchar(64), RETRIES_ integer, EXCEPTION_STACK_ID_ varchar(64), EXCEPTION_MSG_ varchar(4000), DUEDATE_ timestamp(3) NULL, REPEAT_ varchar(255), HANDLER_TYPE_ varchar(255), HANDLER_CFG_ varchar(4000), TENANT_ID_ varchar(255) default '', primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_RE_PROCDEF ( ID_ varchar(64) not null, REV_ integer, CATEGORY_ varchar(255), NAME_ varchar(255), KEY_ varchar(255) not null, VERSION_ integer not null, DEPLOYMENT_ID_ varchar(64), RESOURCE_NAME_ varchar(4000), DGRM_RESOURCE_NAME_ varchar(4000), DESCRIPTION_ varchar(4000), HAS_START_FORM_KEY_ TINYINT, HAS_GRAPHICAL_NOTATION_ TINYINT, SUSPENSION_STATE_ integer, TENANT_ID_ varchar(255) default '', primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_RU_TASK ( ID_ varchar(64), REV_ integer, EXECUTION_ID_ varchar(64), PROC_INST_ID_ varchar(64), PROC_DEF_ID_ varchar(64), NAME_ varchar(255), PARENT_TASK_ID_ varchar(64), DESCRIPTION_ varchar(4000), TASK_DEF_KEY_ varchar(255), OWNER_ varchar(255), ASSIGNEE_ varchar(255), DELEGATION_ varchar(64), PRIORITY_ integer, CREATE_TIME_ timestamp(3) NULL, DUE_DATE_ datetime(3), CATEGORY_ varchar(255), SUSPENSION_STATE_ integer, TENANT_ID_ varchar(255) default '', FORM_KEY_ varchar(255), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_RU_IDENTITYLINK ( ID_ varchar(64), REV_ integer, GROUP_ID_ varchar(255), TYPE_ varchar(255), USER_ID_ varchar(255), TASK_ID_ varchar(64), PROC_INST_ID_ varchar(64), PROC_DEF_ID_ varchar(64), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_RU_VARIABLE ( ID_ varchar(64) not null, REV_ integer, TYPE_ varchar(255) not null, NAME_ varchar(255) not null, EXECUTION_ID_ varchar(64), PROC_INST_ID_ varchar(64), TASK_ID_ varchar(64), BYTEARRAY_ID_ varchar(64), DOUBLE_ double, LONG_ bigint, TEXT_ varchar(4000), TEXT2_ varchar(4000), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_RU_EVENT_SUBSCR ( ID_ varchar(64) not null, REV_ integer, EVENT_TYPE_ varchar(255) not null, EVENT_NAME_ varchar(255), EXECUTION_ID_ varchar(64), PROC_INST_ID_ varchar(64), ACTIVITY_ID_ varchar(64), CONFIGURATION_ varchar(255), CREATED_ timestamp(3) not null DEFAULT CURRENT_TIMESTAMP(3), PROC_DEF_ID_ varchar(64), TENANT_ID_ varchar(255) default '', primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_EVT_LOG ( LOG_NR_ bigint auto_increment, TYPE_ varchar(64), PROC_DEF_ID_ varchar(64), PROC_INST_ID_ varchar(64), EXECUTION_ID_ varchar(64), TASK_ID_ varchar(64), TIME_STAMP_ timestamp(3) not null, USER_ID_ varchar(255), DATA_ LONGBLOB, LOCK_OWNER_ varchar(255), LOCK_TIME_ timestamp(3) null, IS_PROCESSED_ tinyint default 0, primary key (LOG_NR_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_PROCDEF_INFO ( ID_ varchar(64) not null, PROC_DEF_ID_ varchar(64) not null, REV_ integer, INFO_JSON_ID_ varchar(64), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create index ACT_IDX_EXEC_BUSKEY on ACT_RU_EXECUTION(BUSINESS_KEY_); create index ACT_IDX_TASK_CREATE on ACT_RU_TASK(CREATE_TIME_); create index ACT_IDX_IDENT_LNK_USER on ACT_RU_IDENTITYLINK(USER_ID_); create index ACT_IDX_IDENT_LNK_GROUP on ACT_RU_IDENTITYLINK(GROUP_ID_); create index ACT_IDX_EVENT_SUBSCR_CONFIG_ on ACT_RU_EVENT_SUBSCR(CONFIGURATION_); create index ACT_IDX_VARIABLE_TASK_ID on ACT_RU_VARIABLE(TASK_ID_); create index ACT_IDX_ATHRZ_PROCEDEF on ACT_RU_IDENTITYLINK(PROC_DEF_ID_); create index ACT_IDX_INFO_PROCDEF on ACT_PROCDEF_INFO(PROC_DEF_ID_); alter table ACT_GE_BYTEARRAY add constraint ACT_FK_BYTEARR_DEPL foreign key (DEPLOYMENT_ID_) references ACT_RE_DEPLOYMENT (ID_); alter table ACT_RE_PROCDEF add constraint ACT_UNIQ_PROCDEF unique (KEY_,VERSION_, TENANT_ID_); alter table ACT_RU_EXECUTION add constraint ACT_FK_EXE_PROCINST foreign key (PROC_INST_ID_) references ACT_RU_EXECUTION (ID_) on delete cascade on update cascade; alter table ACT_RU_EXECUTION add constraint ACT_FK_EXE_PARENT foreign key (PARENT_ID_) references ACT_RU_EXECUTION (ID_); alter table ACT_RU_EXECUTION add constraint ACT_FK_EXE_SUPER foreign key (SUPER_EXEC_) references ACT_RU_EXECUTION (ID_); alter table ACT_RU_EXECUTION add constraint ACT_FK_EXE_PROCDEF foreign key (PROC_DEF_ID_) references ACT_RE_PROCDEF (ID_); alter table ACT_RU_IDENTITYLINK add constraint ACT_FK_TSKASS_TASK foreign key (TASK_ID_) references ACT_RU_TASK (ID_); alter table ACT_RU_IDENTITYLINK add constraint ACT_FK_ATHRZ_PROCEDEF foreign key (PROC_DEF_ID_) references ACT_RE_PROCDEF(ID_); alter table ACT_RU_IDENTITYLINK add constraint ACT_FK_IDL_PROCINST foreign key (PROC_INST_ID_) references ACT_RU_EXECUTION (ID_); alter table ACT_RU_TASK add constraint ACT_FK_TASK_EXE foreign key (EXECUTION_ID_) references ACT_RU_EXECUTION (ID_); alter table ACT_RU_TASK add constraint ACT_FK_TASK_PROCINST foreign key (PROC_INST_ID_) references ACT_RU_EXECUTION (ID_); alter table ACT_RU_TASK add constraint ACT_FK_TASK_PROCDEF foreign key (PROC_DEF_ID_) references ACT_RE_PROCDEF (ID_); alter table ACT_RU_VARIABLE add constraint ACT_FK_VAR_EXE foreign key (EXECUTION_ID_) references ACT_RU_EXECUTION (ID_); alter table ACT_RU_VARIABLE add constraint ACT_FK_VAR_PROCINST foreign key (PROC_INST_ID_) references ACT_RU_EXECUTION(ID_); alter table ACT_RU_VARIABLE add constraint ACT_FK_VAR_BYTEARRAY foreign key (BYTEARRAY_ID_) references ACT_GE_BYTEARRAY (ID_); alter table ACT_RU_JOB add constraint ACT_FK_JOB_EXCEPTION foreign key (EXCEPTION_STACK_ID_) references ACT_GE_BYTEARRAY (ID_); alter table ACT_RU_EVENT_SUBSCR add constraint ACT_FK_EVENT_EXEC foreign key (EXECUTION_ID_) references ACT_RU_EXECUTION(ID_); alter table ACT_RE_MODEL add constraint ACT_FK_MODEL_SOURCE foreign key (EDITOR_SOURCE_VALUE_ID_) references ACT_GE_BYTEARRAY (ID_); alter table ACT_RE_MODEL add constraint ACT_FK_MODEL_SOURCE_EXTRA foreign key (EDITOR_SOURCE_EXTRA_VALUE_ID_) references ACT_GE_BYTEARRAY (ID_); alter table ACT_RE_MODEL add constraint ACT_FK_MODEL_DEPLOYMENT foreign key (DEPLOYMENT_ID_) references ACT_RE_DEPLOYMENT (ID_); alter table ACT_PROCDEF_INFO add constraint ACT_FK_INFO_JSON_BA foreign key (INFO_JSON_ID_) references ACT_GE_BYTEARRAY (ID_); alter table ACT_PROCDEF_INFO add constraint ACT_FK_INFO_PROCDEF foreign key (PROC_DEF_ID_) references ACT_RE_PROCDEF (ID_); alter table ACT_PROCDEF_INFO add constraint ACT_UNIQ_INFO_PROCDEF unique (PROC_DEF_ID_); create table ACT_HI_PROCINST ( ID_ varchar(64) not null, PROC_INST_ID_ varchar(64) not null, BUSINESS_KEY_ varchar(255), PROC_DEF_ID_ varchar(64) not null, START_TIME_ datetime(3) not null, END_TIME_ datetime(3), DURATION_ bigint, START_USER_ID_ varchar(255), START_ACT_ID_ varchar(255), END_ACT_ID_ varchar(255), SUPER_PROCESS_INSTANCE_ID_ varchar(64), DELETE_REASON_ varchar(4000), TENANT_ID_ varchar(255) default '', NAME_ varchar(255), primary key (ID_), unique (PROC_INST_ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_HI_ACTINST ( ID_ varchar(64) not null, PROC_DEF_ID_ varchar(64) not null, PROC_INST_ID_ varchar(64) not null, EXECUTION_ID_ varchar(64) not null, ACT_ID_ varchar(255) not null, TASK_ID_ varchar(64), CALL_PROC_INST_ID_ varchar(64), ACT_NAME_ varchar(255), ACT_TYPE_ varchar(255) not null, ASSIGNEE_ varchar(255), START_TIME_ datetime(3) not null, END_TIME_ datetime(3), DURATION_ bigint, TENANT_ID_ varchar(255) default '', primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_HI_TASKINST ( ID_ varchar(64) not null, PROC_DEF_ID_ varchar(64), TASK_DEF_KEY_ varchar(255), PROC_INST_ID_ varchar(64), EXECUTION_ID_ varchar(64), NAME_ varchar(255), PARENT_TASK_ID_ varchar(64), DESCRIPTION_ varchar(4000), OWNER_ varchar(255), ASSIGNEE_ varchar(255), START_TIME_ datetime(3) not null, CLAIM_TIME_ datetime(3), END_TIME_ datetime(3), DURATION_ bigint, DELETE_REASON_ varchar(4000), PRIORITY_ integer, DUE_DATE_ datetime(3), FORM_KEY_ varchar(255), CATEGORY_ varchar(255), TENANT_ID_ varchar(255) default '', primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_HI_VARINST ( ID_ varchar(64) not null, PROC_INST_ID_ varchar(64), EXECUTION_ID_ varchar(64), TASK_ID_ varchar(64), NAME_ varchar(255) not null, VAR_TYPE_ varchar(100), REV_ integer, BYTEARRAY_ID_ varchar(64), DOUBLE_ double, LONG_ bigint, TEXT_ varchar(4000), TEXT2_ varchar(4000), CREATE_TIME_ datetime(3), LAST_UPDATED_TIME_ datetime(3), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_HI_DETAIL ( ID_ varchar(64) not null, TYPE_ varchar(255) not null, PROC_INST_ID_ varchar(64), EXECUTION_ID_ varchar(64), TASK_ID_ varchar(64), ACT_INST_ID_ varchar(64), NAME_ varchar(255) not null, VAR_TYPE_ varchar(255), REV_ integer, TIME_ datetime(3) not null, BYTEARRAY_ID_ varchar(64), DOUBLE_ double, LONG_ bigint, TEXT_ varchar(4000), TEXT2_ varchar(4000), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_HI_COMMENT ( ID_ varchar(64) not null, TYPE_ varchar(255), TIME_ datetime(3) not null, USER_ID_ varchar(255), TASK_ID_ varchar(64), PROC_INST_ID_ varchar(64), ACTION_ varchar(255), MESSAGE_ varchar(4000), FULL_MSG_ LONGBLOB, primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_HI_ATTACHMENT ( ID_ varchar(64) not null, REV_ integer, USER_ID_ varchar(255), NAME_ varchar(255), DESCRIPTION_ varchar(4000), TYPE_ varchar(255), TASK_ID_ varchar(64), PROC_INST_ID_ varchar(64), URL_ varchar(4000), CONTENT_ID_ varchar(64), TIME_ datetime(3), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_HI_IDENTITYLINK ( ID_ varchar(64), GROUP_ID_ varchar(255), TYPE_ varchar(255), USER_ID_ varchar(255), TASK_ID_ varchar(64), PROC_INST_ID_ varchar(64), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create index ACT_IDX_HI_PRO_INST_END on ACT_HI_PROCINST(END_TIME_); create index ACT_IDX_HI_PRO_I_BUSKEY on ACT_HI_PROCINST(BUSINESS_KEY_); create index ACT_IDX_HI_ACT_INST_START on ACT_HI_ACTINST(START_TIME_); create index ACT_IDX_HI_ACT_INST_END on ACT_HI_ACTINST(END_TIME_); create index ACT_IDX_HI_DETAIL_PROC_INST on ACT_HI_DETAIL(PROC_INST_ID_); create index ACT_IDX_HI_DETAIL_ACT_INST on ACT_HI_DETAIL(ACT_INST_ID_); create index ACT_IDX_HI_DETAIL_TIME on ACT_HI_DETAIL(TIME_); create index ACT_IDX_HI_DETAIL_NAME on ACT_HI_DETAIL(NAME_); create index ACT_IDX_HI_DETAIL_TASK_ID on ACT_HI_DETAIL(TASK_ID_); create index ACT_IDX_HI_PROCVAR_PROC_INST on ACT_HI_VARINST(PROC_INST_ID_); create index ACT_IDX_HI_PROCVAR_NAME_TYPE on ACT_HI_VARINST(NAME_, VAR_TYPE_); create index ACT_IDX_HI_PROCVAR_TASK_ID on ACT_HI_VARINST(TASK_ID_); create index ACT_IDX_HI_ACT_INST_PROCINST on ACT_HI_ACTINST(PROC_INST_ID_, ACT_ID_); create index ACT_IDX_HI_ACT_INST_EXEC on ACT_HI_ACTINST(EXECUTION_ID_, ACT_ID_); create index ACT_IDX_HI_IDENT_LNK_USER on ACT_HI_IDENTITYLINK(USER_ID_); create index ACT_IDX_HI_IDENT_LNK_TASK on ACT_HI_IDENTITYLINK(TASK_ID_); create index ACT_IDX_HI_IDENT_LNK_PROCINST on ACT_HI_IDENTITYLINK(PROC_INST_ID_); create index ACT_IDX_HI_TASK_INST_PROCINST on ACT_HI_TASKINST(PROC_INST_ID_); create table ACT_ID_GROUP ( ID_ varchar(64), REV_ integer, NAME_ varchar(255), TYPE_ varchar(255), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_ID_MEMBERSHIP ( USER_ID_ varchar(64), GROUP_ID_ varchar(64), primary key (USER_ID_, GROUP_ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_ID_USER ( ID_ varchar(64), REV_ integer, FIRST_ varchar(255), LAST_ varchar(255), EMAIL_ varchar(255), PWD_ varchar(255), PICTURE_ID_ varchar(64), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; create table ACT_ID_INFO ( ID_ varchar(64), REV_ integer, USER_ID_ varchar(64), TYPE_ varchar(64), KEY_ varchar(255), VALUE_ varchar(255), PASSWORD_ LONGBLOB, PARENT_ID_ varchar(255), primary key (ID_) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin; alter table ACT_ID_MEMBERSHIP add constraint ACT_FK_MEMB_GROUP foreign key (GROUP_ID_) references ACT_ID_GROUP (ID_); alter table ACT_ID_MEMBERSHIP add constraint ACT_FK_MEMB_USER foreign key (USER_ID_) references ACT_ID_USER (ID_); COMMIT; -- ----------------------------------------------------- -- Create Quartz tables -- ----------------------------------------------------- DROP SCHEMA IF EXISTS openbook_jobs; CREATE SCHEMA openbook_jobs DEFAULT CHARACTER SET utf8; GRANT ALL PRIVILEGES ON openbook_jobs.* TO openbook@'%' IDENTIFIED BY '{% raw %}{{ openbook_database_password }}{% endraw %}'; GRANT ALL PRIVILEGES ON openbook_jobs.* TO openbook@localhost IDENTIFIED BY '{% raw %}{{ openbook_database_password }}{% endraw %}'; FLUSH PRIVILEGES; USE openbook_jobs; CREATE TABLE QRTZ_JOB_DETAILS( SCHED_NAME VARCHAR(120) NOT NULL, JOB_NAME VARCHAR(200) NOT NULL, JOB_GROUP VARCHAR(200) NOT NULL, DESCRIPTION VARCHAR(250) NULL, JOB_CLASS_NAME VARCHAR(250) NOT NULL, IS_DURABLE VARCHAR(1) NOT NULL, IS_NONCONCURRENT VARCHAR(1) NOT NULL, IS_UPDATE_DATA VARCHAR(1) NOT NULL, REQUESTS_RECOVERY VARCHAR(1) NOT NULL, JOB_DATA BLOB NULL, PRIMARY KEY (SCHED_NAME,JOB_NAME,JOB_GROUP)) ENGINE=InnoDB; CREATE TABLE QRTZ_TRIGGERS ( SCHED_NAME VARCHAR(120) NOT NULL, TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, JOB_NAME VARCHAR(200) NOT NULL, JOB_GROUP VARCHAR(200) NOT NULL, DESCRIPTION VARCHAR(250) NULL, NEXT_FIRE_TIME BIGINT(13) NULL, PREV_FIRE_TIME BIGINT(13) NULL, PRIORITY INTEGER NULL, TRIGGER_STATE VARCHAR(16) NOT NULL, TRIGGER_TYPE VARCHAR(8) NOT NULL, START_TIME BIGINT(13) NOT NULL, END_TIME BIGINT(13) NULL, CALENDAR_NAME VARCHAR(200) NULL, MISFIRE_INSTR SMALLINT(2) NULL, JOB_DATA BLOB NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (SCHED_NAME,JOB_NAME,JOB_GROUP) REFERENCES QRTZ_JOB_DETAILS(SCHED_NAME,JOB_NAME,JOB_GROUP)) ENGINE=InnoDB; CREATE TABLE QRTZ_SIMPLE_TRIGGERS ( SCHED_NAME VARCHAR(120) NOT NULL, TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, REPEAT_COUNT BIGINT(7) NOT NULL, REPEAT_INTERVAL BIGINT(12) NOT NULL, TIMES_TRIGGERED BIGINT(10) NOT NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) ENGINE=InnoDB; CREATE TABLE QRTZ_CRON_TRIGGERS ( SCHED_NAME VARCHAR(120) NOT NULL, TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, CRON_EXPRESSION VARCHAR(120) NOT NULL, TIME_ZONE_ID VARCHAR(80), PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) ENGINE=InnoDB; CREATE TABLE QRTZ_SIMPROP_TRIGGERS ( SCHED_NAME VARCHAR(120) NOT NULL, TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, STR_PROP_1 VARCHAR(512) NULL, STR_PROP_2 VARCHAR(512) NULL, STR_PROP_3 VARCHAR(512) NULL, INT_PROP_1 INT NULL, INT_PROP_2 INT NULL, LONG_PROP_1 BIGINT NULL, LONG_PROP_2 BIGINT NULL, DEC_PROP_1 NUMERIC(13,4) NULL, DEC_PROP_2 NUMERIC(13,4) NULL, BOOL_PROP_1 VARCHAR(1) NULL, BOOL_PROP_2 VARCHAR(1) NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) ENGINE=InnoDB; CREATE TABLE QRTZ_BLOB_TRIGGERS ( SCHED_NAME VARCHAR(120) NOT NULL, TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, BLOB_DATA BLOB NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP), INDEX (SCHED_NAME,TRIGGER_NAME, TRIGGER_GROUP), FOREIGN KEY (SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP) REFERENCES QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP)) ENGINE=InnoDB; CREATE TABLE QRTZ_CALENDARS ( SCHED_NAME VARCHAR(120) NOT NULL, CALENDAR_NAME VARCHAR(200) NOT NULL, CALENDAR BLOB NOT NULL, PRIMARY KEY (SCHED_NAME,CALENDAR_NAME)) ENGINE=InnoDB; CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS ( SCHED_NAME VARCHAR(120) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, PRIMARY KEY (SCHED_NAME,TRIGGER_GROUP)) ENGINE=InnoDB; CREATE TABLE QRTZ_FIRED_TRIGGERS ( SCHED_NAME VARCHAR(120) NOT NULL, ENTRY_ID VARCHAR(95) NOT NULL, TRIGGER_NAME VARCHAR(200) NOT NULL, TRIGGER_GROUP VARCHAR(200) NOT NULL, INSTANCE_NAME VARCHAR(200) NOT NULL, FIRED_TIME BIGINT(13) NOT NULL, SCHED_TIME BIGINT(13) NOT NULL, PRIORITY INTEGER NOT NULL, STATE VARCHAR(16) NOT NULL, JOB_NAME VARCHAR(200) NULL, JOB_GROUP VARCHAR(200) NULL, IS_NONCONCURRENT VARCHAR(1) NULL, REQUESTS_RECOVERY VARCHAR(1) NULL, PRIMARY KEY (SCHED_NAME,ENTRY_ID)) ENGINE=InnoDB; CREATE TABLE QRTZ_SCHEDULER_STATE ( SCHED_NAME VARCHAR(120) NOT NULL, INSTANCE_NAME VARCHAR(200) NOT NULL, LAST_CHECKIN_TIME BIGINT(13) NOT NULL, CHECKIN_INTERVAL BIGINT(13) NOT NULL, PRIMARY KEY (SCHED_NAME,INSTANCE_NAME)) ENGINE=InnoDB; CREATE TABLE QRTZ_LOCKS ( SCHED_NAME VARCHAR(120) NOT NULL, LOCK_NAME VARCHAR(40) NOT NULL, PRIMARY KEY (SCHED_NAME,LOCK_NAME)) ENGINE=InnoDB; CREATE INDEX IDX_QRTZ_J_REQ_RECOVERY ON QRTZ_JOB_DETAILS(SCHED_NAME,REQUESTS_RECOVERY); CREATE INDEX IDX_QRTZ_J_GRP ON QRTZ_JOB_DETAILS(SCHED_NAME,JOB_GROUP); CREATE INDEX IDX_QRTZ_T_J ON QRTZ_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP); CREATE INDEX IDX_QRTZ_T_JG ON QRTZ_TRIGGERS(SCHED_NAME,JOB_GROUP); CREATE INDEX IDX_QRTZ_T_C ON QRTZ_TRIGGERS(SCHED_NAME,CALENDAR_NAME); CREATE INDEX IDX_QRTZ_T_G ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP); CREATE INDEX IDX_QRTZ_T_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE); CREATE INDEX IDX_QRTZ_T_N_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_STATE); CREATE INDEX IDX_QRTZ_T_N_G_STATE ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_GROUP,TRIGGER_STATE); CREATE INDEX IDX_QRTZ_T_NEXT_FIRE_TIME ON QRTZ_TRIGGERS(SCHED_NAME,NEXT_FIRE_TIME); CREATE INDEX IDX_QRTZ_T_NFT_ST ON QRTZ_TRIGGERS(SCHED_NAME,TRIGGER_STATE,NEXT_FIRE_TIME); CREATE INDEX IDX_QRTZ_T_NFT_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME); CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_STATE); CREATE INDEX IDX_QRTZ_T_NFT_ST_MISFIRE_GRP ON QRTZ_TRIGGERS(SCHED_NAME,MISFIRE_INSTR,NEXT_FIRE_TIME,TRIGGER_GROUP,TRIGGER_STATE); CREATE INDEX IDX_QRTZ_FT_TRIG_INST_NAME ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME); CREATE INDEX IDX_QRTZ_FT_INST_JOB_REQ_RCVRY ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,INSTANCE_NAME,REQUESTS_RECOVERY); CREATE INDEX IDX_QRTZ_FT_J_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_NAME,JOB_GROUP); CREATE INDEX IDX_QRTZ_FT_JG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,JOB_GROUP); CREATE INDEX IDX_QRTZ_FT_T_G ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_NAME,TRIGGER_GROUP); CREATE INDEX IDX_QRTZ_FT_TG ON QRTZ_FIRED_TRIGGERS(SCHED_NAME,TRIGGER_GROUP); COMMIT;