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>

1 files changed, 29 insertions, 26 deletions

diff --git a/extra/sql/bulkload/clds-create-db-objects.sql b/extra/sql/bulkload/clds-create-db-objects.sql
index 5cbb7e017..3312daf56 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 @@ CREATE TABLE template_bpmn (
   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 @@ CREATE TABLE template_image (
   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 @@ CREATE TABLE template_doc (
   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 @@ CREATE TABLE model (
   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 @@ CREATE TABLE model_blueprint (
   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 @@ CREATE TABLE event (
   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 @@ 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)
@@ -145,8 +149,8 @@ ALTER TABLE template_image
 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 @@ 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)
@@ -170,20 +174,19 @@ ALTER TABLE model
 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);