diff options
Diffstat (limited to 'salt/openbook/database/files/create_openbook_schemas.sql')
| -rw-r--r-- | salt/openbook/database/files/create_openbook_schemas.sql | 1416 |
1 files changed, 1416 insertions, 0 deletions
diff --git a/salt/openbook/database/files/create_openbook_schemas.sql b/salt/openbook/database/files/create_openbook_schemas.sql new file mode 100644 index 0000000..f6544fc --- /dev/null +++ b/salt/openbook/database/files/create_openbook_schemas.sql @@ -0,0 +1,1416 @@ +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; |