Rework the Clamp db model
Rework the DB model + new SQL associated to that
Change-Id: Ibfa6e0bc9942f8b51a1681fab98502c462d34def
Issue-Id: CLAMP-1
Signed-off-by: Determe, Sebastien (sd378r) <sd378r@intl.att.com>
diff --git a/extra/sql/bulkload/clds-create-db-objects.sql b/extra/sql/bulkload/clds-create-db-objects.sql
index 5cbb7e0..3312daf 100644
--- a/extra/sql/bulkload/clds-create-db-objects.sql
+++ b/extra/sql/bulkload/clds-create-db-objects.sql
@@ -1,18 +1,18 @@
-#
+#
# Create CLDS database objects (tables, etc.)
#
#
CREATE DATABASE `camundabpm`;
-USE `camundabpm`;
-DROP USER 'camunda';
-CREATE USER 'camunda';
-GRANT ALL on camundabpm.* to 'camunda' identified by 'ndMSpw4CAM' with GRANT OPTION;
-FLUSH PRIVILEGES;
+USE `camundabpm`;
+DROP USER 'camunda';
+CREATE USER 'camunda';
+GRANT ALL on camundabpm.* to 'camunda' identified by 'ndMSpw4CAM' with GRANT OPTION;
+FLUSH PRIVILEGES;
CREATE DATABASE `cldsdb4`;
-USE `cldsdb4`;
-DROP USER 'clds';
-CREATE USER 'clds';
+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;
@@ -32,7 +32,7 @@
template_bpmn_id VARCHAR(36) NOT NULL,
template_id VARCHAR(36) NOT NULL,
template_bpmn_text MEDIUMTEXT NOT NULL,
- userid VARCHAR(8),
+ 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;
@@ -41,7 +41,7 @@
template_image_id VARCHAR(36) NOT NULL,
template_id VARCHAR(36) NOT NULL,
template_image_text MEDIUMTEXT NULL,
- userid VARCHAR(8),
+ 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;
@@ -50,7 +50,7 @@
template_doc_id VARCHAR(36) NOT NULL,
template_id VARCHAR(36) NOT NULL,
template_doc_text MEDIUMTEXT NULL,
- userid VARCHAR(8),
+ 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;
@@ -64,16 +64,20 @@
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 (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,
- userid VARCHAR(8),
+ 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;
@@ -82,7 +86,7 @@
model_blueprint_id VARCHAR(36) NOT NULL,
model_id VARCHAR(36) NOT NULL,
model_blueprint_text MEDIUMTEXT NULL,
- userid VARCHAR(8),
+ 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;
@@ -104,7 +108,7 @@
action_state_cd VARCHAR(80) NULL,
prev_event_id VARCHAR(36) NULL,
process_instance_id VARCHAR(80) NULL,
- userid VARCHAR(8) 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;
@@ -131,7 +135,7 @@
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)
@@ -145,8 +149,8 @@
ALTER TABLE template_doc
ADD CONSTRAINT template_id_fkey04
FOREIGN KEY (template_id)
- REFERENCES template (template_id);
-
+ REFERENCES template (template_id);
+
ALTER TABLE model
ADD CONSTRAINT template_id_fkey01
FOREIGN KEY (template_id)
@@ -161,7 +165,7 @@
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)
@@ -170,20 +174,19 @@
ALTER TABLE model_properties
ADD CONSTRAINT model_id_fkey01
FOREIGN KEY (model_id)
- REFERENCES model (model_id);
+ REFERENCES model (model_id);
ALTER TABLE model_blueprint
ADD CONSTRAINT model_id_fkey02
FOREIGN KEY (model_id)
- REFERENCES model (model_id);
+ REFERENCES model (model_id);
ALTER TABLE model_instance
ADD CONSTRAINT model_id_fkey04
FOREIGN KEY (model_id)
- REFERENCES model (model_id);
-
+ REFERENCES model (model_id);
+
ALTER TABLE event
ADD CONSTRAINT model_id_fkey03
FOREIGN KEY (model_id)
- REFERENCES model (model_id);
-
+ REFERENCES model (model_id);