text: #
  # Create CLDS database objects (tables, etc.)
  #
  #
  CREATE DATABASE `cldsdb4`;
  USE `cldsdb4`;
  DROP USER 'clds';
  CREATE USER 'clds';
  GRANT ALL on cldsdb4.* to 'clds' identified by 'sidnnd83K' with GRANT OPTION;
  GRANT SELECT on mysql.proc TO 'clds';
  FLUSH PRIVILEGES;
  
  
  CREATE TABLE template (
    template_id VARCHAR(36) NOT NULL,
    template_name VARCHAR(80) NOT NULL,
    template_bpmn_id VARCHAR(36) NULL,
    template_image_id VARCHAR(36) NULL,
    template_doc_id VARCHAR(36) NULL,
    PRIMARY KEY (template_id),
    UNIQUE (template_name)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE template_bpmn (
    template_bpmn_id VARCHAR(36) NOT NULL,
    template_id VARCHAR(36) NOT NULL,
    template_bpmn_text MEDIUMTEXT NOT NULL,
    user_id VARCHAR(80),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (template_bpmn_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE template_image (
    template_image_id VARCHAR(36) NOT NULL,
    template_id VARCHAR(36) NOT NULL,
    template_image_text MEDIUMTEXT NULL,
    user_id VARCHAR(80),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (template_image_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE template_doc (
    template_doc_id VARCHAR(36) NOT NULL,
    template_id VARCHAR(36) NOT NULL,
    template_doc_text MEDIUMTEXT NULL,
    user_id VARCHAR(80),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (template_doc_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE model (
    model_id VARCHAR(36) NOT NULL,
    model_name VARCHAR(80) NOT NULL,
    template_id VARCHAR(36) NULL,
    model_prop_id VARCHAR(36) NULL,
    model_blueprint_id VARCHAR(36) NULL,
    event_id VARCHAR(36) NULL,
    control_name_prefix VARCHAR(80) NULL,
    control_name_uuid VARCHAR(36) NOT NULL,
    service_type_id VARCHAR(80) NULL,
    deployment_id VARCHAR(80) NULL,
    PRIMARY KEY (model_id),
    UNIQUE (model_name),
    UNIQUE (control_name_uuid),
    UNIQUE (service_type_id),
    UNIQUE (deployment_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE model_properties (
    model_prop_id VARCHAR(36) NOT NULL,
    model_id VARCHAR(36) NOT NULL,
    model_prop_text MEDIUMTEXT NULL,
    user_id VARCHAR(80),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (model_prop_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE model_blueprint (
    model_blueprint_id VARCHAR(36) NOT NULL,
    model_id VARCHAR(36) NOT NULL,
    model_blueprint_text MEDIUMTEXT NULL,
    user_id VARCHAR(80),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (model_blueprint_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE model_instance (
    model_instance_id VARCHAR(36) NOT NULL,
    model_id VARCHAR(36) NOT NULL,
    vm_name VARCHAR(250) NOT NULL,
    location VARCHAR(250) NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (model_instance_id),
    UNIQUE (model_id, vm_name)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE event (
    event_id VARCHAR(36) NOT NULL,
    model_id VARCHAR(36) NULL,
    action_cd VARCHAR(80) NOT NULL,
    action_state_cd VARCHAR(80) NULL,
    prev_event_id VARCHAR(36) NULL,
    process_instance_id VARCHAR(80) NULL,
    user_id VARCHAR(80) NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (event_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE clds_service_cache (
    invariant_service_id VARCHAR(36) NOT NULL,
    service_id VARCHAR(36) NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    object_data MEDIUMBLOB NULL,
    PRIMARY KEY (invariant_service_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE IF NOT EXISTS tosca_model (
    tosca_model_id VARCHAR(36) NOT NULL,
    tosca_model_name VARCHAR(80) NOT NULL,
    policy_type VARCHAR(80) NULL,
    user_id VARCHAR(80),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (tosca_model_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE IF NOT EXISTS tosca_model_revision (
    tosca_model_revision_id VARCHAR(36) NOT NULL,
    tosca_model_id VARCHAR(36) NOT NULL,
    version DOUBLE NOT NULL DEFAULT 1,
    tosca_model_yaml MEDIUMTEXT NULL,
    tosca_model_json MEDIUMTEXT NULL,
    user_id VARCHAR(80),
    createdTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    lastUpdatedTimestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (tosca_model_revision_id),
    CONSTRAINT tosca_model_revision_ukey UNIQUE KEY (tosca_model_id, version),
    CONSTRAINT tosca_model_revision_fkey01 FOREIGN KEY (tosca_model_id) REFERENCES tosca_model (tosca_model_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE IF NOT EXISTS dictionary (
    dictionary_id VARCHAR(36) NOT NULL,
    dictionary_name VARCHAR(80) NOT NULL,
    created_by VARCHAR(80),
    modified_by VARCHAR(80),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (dictionary_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  CREATE TABLE IF NOT EXISTS dictionary_elements (
    dict_element_id VARCHAR(36) NOT NULL,
    dictionary_id VARCHAR(36) NOT NULL,
    dict_element_name VARCHAR(250) NOT NULL,
    dict_element_short_name VARCHAR(80) NOT NULL,
    dict_element_description VARCHAR(250),
    dict_element_type VARCHAR(80) NOT NULL,
    created_by VARCHAR(80),
    modified_by VARCHAR(80),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (dict_element_id),
    CONSTRAINT dictionary_elements_ukey UNIQUE KEY (dict_element_name, dict_element_short_name),
    CONSTRAINT dictionary_elements_ukey_fkey01 FOREIGN KEY (dictionary_id) REFERENCES dictionary (dictionary_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin;
  
  ALTER TABLE template
      ADD CONSTRAINT template_bpmn_id_fkey01
      FOREIGN KEY (template_bpmn_id)
      REFERENCES template_bpmn (template_bpmn_id);
  
  ALTER TABLE template
      ADD CONSTRAINT template_image_id_fkey01
      FOREIGN KEY (template_image_id)
      REFERENCES template_image (template_image_id);
  
  ALTER TABLE template
      ADD CONSTRAINT template_doc_id_fkey01
      FOREIGN KEY (template_doc_id)
      REFERENCES template_doc (template_doc_id);
  
  ALTER TABLE template_bpmn
      ADD CONSTRAINT template_id_fkey02
      FOREIGN KEY (template_id)
      REFERENCES template (template_id);
  
  ALTER TABLE template_image
      ADD CONSTRAINT template_id_fkey03
      FOREIGN KEY (template_id)
      REFERENCES template (template_id);
  
  ALTER TABLE template_doc
      ADD CONSTRAINT template_id_fkey04
      FOREIGN KEY (template_id)
      REFERENCES template (template_id);
  
  ALTER TABLE model
      ADD CONSTRAINT template_id_fkey01
      FOREIGN KEY (template_id)
      REFERENCES template (template_id);
  
  ALTER TABLE model
      ADD CONSTRAINT model_prop_id_fkey01
      FOREIGN KEY (model_prop_id)
      REFERENCES model_properties (model_prop_id);
  
  ALTER TABLE model
      ADD CONSTRAINT model_blueprint_id_fkey01
      FOREIGN KEY (model_blueprint_id)
      REFERENCES model_blueprint (model_blueprint_id);
  
  ALTER TABLE model
      ADD CONSTRAINT event_id_fkey01
      FOREIGN KEY (event_id)
      REFERENCES event (event_id);
  
  ALTER TABLE model_properties
      ADD CONSTRAINT model_id_fkey01
      FOREIGN KEY (model_id)
      REFERENCES model (model_id);
  
  ALTER TABLE model_blueprint
      ADD CONSTRAINT model_id_fkey02
      FOREIGN KEY (model_id)
      REFERENCES model (model_id);
  
  ALTER TABLE model_instance
      ADD CONSTRAINT model_id_fkey04
      FOREIGN KEY (model_id)
      REFERENCES model (model_id);
  
  ALTER TABLE event
      ADD CONSTRAINT model_id_fkey03
      FOREIGN KEY (model_id)
      REFERENCES model (model_id);
  