DM: Introduce Postgres and Script generated Schema

Jira ID:CCSDK-2869
Jira Link: https://jira.onap.org/browse/CCSDK-2869

Signed-off-by: Rishi Chail <rishi.chail@est.tech>
Change-Id: I54007daf1ad2491d49142168e8cb44e6eb2da8b9

3 files changed, 125 insertions, 65 deletions

diff --git a/cps/cps-rest/src/main/resources/application.yml b/cps/cps-rest/src/main/resources/application.yml
index a8a4690f6..5cfa97415 100644
--- a/cps/cps-rest/src/main/resources/application.yml
+++ b/cps/cps-rest/src/main/resources/application.yml
@@ -2,23 +2,25 @@ server:
   port: 8080

 

 spring:

-  main:

-    banner-mode: "off"

-  # for POC only, later this should move to cpi-ri module

-  jpa:

-    hibernate:

-    ddl-auto: create

-    open-in-view: false

-    properties:

-      hibernate:

-        enable_lazy_load_no_trans: true

-  datasource:

-    url: jdbc:mariadb://${DB_HOST}:3306/${DB_NAME}

-    username: ${USERNAME}

-    password: ${PWD}

-    driverClassName: org.mariadb.jdbc.Driver

+    main:

+        banner-mode: "off"

+# for POC only, later this should move to cpi-ri module

+    jpa:

+        ddl-auto: create

+        open-in-view: false

+        properties:

+            hibernate:

+                enable_lazy_load_no_trans: true

+                dialect: org.hibernate.dialect.PostgreSQLDialect

+

+    datasource:

+        url: jdbc:postgresql://${DB_HOST}:5432/cpsdb

+        username: ${DB_USERNAME}

+        password: ${DB_PASSWORD}

+        driverClassName: org.postgresql.Driver

+        initialization-mode: always

 

 logging:

-  level:

-    org:

-      springframework: INFO
\ No newline at end of file
+    level:

+        org:

+            springframework: INFO

diff --git a/cps/cps-rest/src/main/resources/schema.sql b/cps/cps-rest/src/main/resources/schema.sql
new file mode 100644
index 000000000..446d6e584
--- /dev/null
+++ b/cps/cps-rest/src/main/resources/schema.sql
@@ -0,0 +1,63 @@
+/*  Initialisation script for CPS.

+

+To be moved to cps-ri in Honolulu.  */

+

+

+CREATE TABLE IF NOT EXISTS RELATION_TYPE

+(

+    RELATION_TYPE TEXT NOT NULL,

+    ID SERIAL PRIMARY KEY

+);

+

+CREATE TABLE IF NOT EXISTS DATASPACE

+(

+    ID SERIAL PRIMARY KEY,

+    NAME TEXT NOT NULL,

+    CONSTRAINT "UQ_NAME" UNIQUE (NAME)

+);

+

+CREATE TABLE IF NOT EXISTS SCHEMA_NODE

+(

+    SCHEMA_NODE_IDENTIFIER TEXT NOT NULL,

+    ID SERIAL PRIMARY KEY

+);

+

+CREATE TABLE IF NOT EXISTS MODULE_SET

+(

+    MODULE_SET_REFERENCE TEXT NOT NULL,

+    ID SERIAL PRIMARY KEY

+);

+

+CREATE TABLE IF NOT EXISTS FRAGMENT

+(

+    ID BIGSERIAL PRIMARY KEY,

+    XPATH TEXT NOT NULL,

+    DATASPACE_ID INTEGER NOT NULL REFERENCES DATASPACE(ID),

+    ATTRIBUTES JSONB,

+    ANCHOR_ID BIGINT REFERENCES FRAGMENT(ID),

+    PARENT_ID BIGINT REFERENCES FRAGMENT(ID),

+    MODULE_SET_ID INTEGER REFERENCES MODULE_SET(ID),

+    SCHEMA_NODE_ID INTEGER REFERENCES SCHEMA_NODE(ID)

+);

+

+CREATE TABLE IF NOT EXISTS RELATION

+(

+    FROM_FRAGMENT_ID BIGINT NOT NULL REFERENCES FRAGMENT(ID),

+    TO_FRAGMENT_ID   BIGINT NOT NULL REFERENCES FRAGMENT(ID),

+    RELATION_TYPE_ID  INTEGER NOT NULL REFERENCES RELATION_TYPE(ID),

+    FROM_REL_XPATH TEXT NOT NULL,

+    TO_REL_XPATH TEXT NOT NULL,

+    CONSTRAINT RELATION_PKEY PRIMARY KEY (TO_FRAGMENT_ID, FROM_FRAGMENT_ID, RELATION_TYPE_ID)

+);

+

+CREATE INDEX  IF NOT EXISTS "FKI_FRAGMENT_DATASPACE_ID_FK"     ON FRAGMENT USING BTREE(DATASPACE_ID) ;

+CREATE INDEX  IF NOT EXISTS "FKI_FRAGMENT_MODULE_SET_ID_FK"    ON FRAGMENT USING BTREE(MODULE_SET_ID) ;

+CREATE INDEX  IF NOT EXISTS "FKI_FRAGMENT_PARENT_ID_FK"        ON FRAGMENT USING BTREE(PARENT_ID) ;

+CREATE INDEX  IF NOT EXISTS "FKI_FRAGMENT_ANCHOR_ID_FK"        ON FRAGMENT USING BTREE(ANCHOR_ID) ;

+CREATE INDEX  IF NOT EXISTS "PERF_SCHEMA_NODE_SCHEMA_NODE_ID"  ON SCHEMA_NODE USING BTREE(SCHEMA_NODE_IDENTIFIER) ;

+CREATE INDEX  IF NOT EXISTS "FKI_SCHEMA_NODE_ID_TO_ID"         ON FRAGMENT USING BTREE(SCHEMA_NODE_ID) ;

+CREATE INDEX  IF NOT EXISTS "FKI_RELATION_TYPE_ID_FK"          ON RELATION USING BTREE(RELATION_TYPE_ID);

+CREATE INDEX  IF NOT EXISTS "FKI_RELATIONS_FROM_ID_FK"         ON RELATION USING BTREE(FROM_FRAGMENT_ID);

+CREATE INDEX  IF NOT EXISTS "FKI_RELATIONS_TO_ID_FK"           ON RELATION USING BTREE(TO_FRAGMENT_ID);

+CREATE INDEX  IF NOT EXISTS "PERF_MODULE_SET_MODULE_SET_REFERENCE" ON MODULE_SET USING BTREE(MODULE_SET_REFERENCE) ;

+CREATE UNIQUE INDEX  IF NOT EXISTS "UQ_FRAGMENT_XPATH"ON FRAGMENT USING btree(xpath COLLATE pg_catalog."default" text_pattern_ops, dataspace_id);
\ No newline at end of file
diff --git a/cps/cps-ri/pom.xml b/cps/cps-ri/pom.xml
index 781f8e832..334e47662 100644
--- a/cps/cps-ri/pom.xml
+++ b/cps/cps-ri/pom.xml
@@ -1,48 +1,43 @@
 <project xmlns="http://maven.apache.org/POM/4.0.0"

-  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

-  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

-  <modelVersion>4.0.0</modelVersion>

-  <parent>

-    <groupId>org.onap.cps</groupId>

-    <artifactId>cps</artifactId>

-    <version>0.0.1-SNAPSHOT</version>

-  </parent>

-  <artifactId>cps-ri</artifactId>

-

-  <dependencies>

-

-    <dependency>

-      <groupId>org.onap.cps</groupId>

-      <artifactId>cps-service</artifactId>

-      <version>${project.version}</version>

-    </dependency>

-

-    <dependency>

-      <groupId>org.springframework.boot</groupId>

-      <artifactId>spring-boot-starter-data-jpa</artifactId>

-    </dependency>

-

-    <dependency>

-      <groupId>org.springframework.boot</groupId>

-      <artifactId>spring-boot-starter-validation</artifactId>

-    </dependency>

-

-    <dependency>

-      <groupId>org.mariadb.jdbc</groupId>

-      <artifactId>mariadb-java-client</artifactId>

-    </dependency>

-

-    <dependency>

-      <groupId>org.projectlombok</groupId>

-      <artifactId>lombok</artifactId>

-    </dependency>

-

-    <dependency>

-      <!-- Provides mariadb object mapping to an entity -->

-      <groupId>jakarta.persistence</groupId>

-      <artifactId>jakarta.persistence-api</artifactId>

-    </dependency>

-

-  </dependencies>

-

-</project>
\ No newline at end of file
+    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

+    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

+    <modelVersion>4.0.0</modelVersion>

+    <parent>

+        <groupId>org.onap.cps</groupId>

+        <artifactId>cps</artifactId>

+        <version>0.0.1-SNAPSHOT</version>

+    </parent>

+    <artifactId>cps-ri</artifactId>

+

+    <dependencies>

+

+        <dependency>

+            <groupId>org.onap.cps</groupId>

+            <artifactId>cps-service</artifactId>

+            <version>${project.version}</version>

+        </dependency>

+

+        <dependency>

+            <groupId>org.springframework.boot</groupId>

+            <artifactId>spring-boot-starter-data-jpa</artifactId>

+        </dependency>

+

+        <dependency>

+            <groupId>org.springframework.boot</groupId>

+            <artifactId>spring-boot-starter-validation</artifactId>

+        </dependency>

+

+        <dependency>

+            <groupId>org.postgresql</groupId>

+            <artifactId>postgresql</artifactId>

+        </dependency>

+

+

+        <dependency>

+            <groupId>org.projectlombok</groupId>

+            <artifactId>lombok</artifactId>

+        </dependency>

+

+    </dependencies>

+

+</project>