From 05855d531b912698ee63b1fd9de4303baff83670 Mon Sep 17 00:00:00 2001
From: Bruno Sakoto <bruno.sakoto@bell.ca>
Date: Tue, 20 Apr 2021 08:22:56 -0400
Subject: Implement db schema with liquibase

Issue-ID: CPS-193
Signed-off-by: Bruno Sakoto <bruno.sakoto@bell.ca>
Change-Id: Ieabee4b86bbe7a42c05bb225499487cfcc9deb1d
---
 .../resources/db/changelog/changelog-master.xml    |  29 ++
 .../resources/db/changelog/data/02-init-data.dat   | 537 +++++++++++++++++++++
 .../resources/db/changelog/data/02-init-data.xml   |  51 ++
 .../db/changelog/schema/01-init-schema.xml         |  92 ++++
 4 files changed, 709 insertions(+)
 create mode 100644 src/main/resources/db/changelog/changelog-master.xml
 create mode 100644 src/main/resources/db/changelog/data/02-init-data.dat
 create mode 100644 src/main/resources/db/changelog/data/02-init-data.xml
 create mode 100644 src/main/resources/db/changelog/schema/01-init-schema.xml

(limited to 'src/main/resources/db/changelog')

diff --git a/src/main/resources/db/changelog/changelog-master.xml b/src/main/resources/db/changelog/changelog-master.xml
new file mode 100644
index 0000000..630d399
--- /dev/null
+++ b/src/main/resources/db/changelog/changelog-master.xml
@@ -0,0 +1,29 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+  ============LICENSE_START=======================================================
+  Copyright (c) 2021 Bell Canada.
+  ================================================================================
+  Licensed under the Apache License, Version 2.0 (the "License");
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+
+        http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+  ============LICENSE_END=========================================================
+-->
+
+<databaseChangeLog
+        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
+        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
+            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
+
+    <include file="db/changelog/schema/01-init-schema.xml"/>
+    <include file="db/changelog/data/02-init-data.xml"/>
+
+</databaseChangeLog>
diff --git a/src/main/resources/db/changelog/data/02-init-data.dat b/src/main/resources/db/changelog/data/02-init-data.dat
new file mode 100644
index 0000000..338444b
--- /dev/null
+++ b/src/main/resources/db/changelog/data/02-init-data.dat
@@ -0,0 +1,537 @@
+timestamp|dataspace|schema_set|anchor|version|payload
+(select now())|NFP-Operational|cps-ran-schema-model|ran-network-simulation|(select now())|'
+{
+    "NearRTRIC": [
+        {
+            "idNearRTRIC": "22",
+            "GNBCUUPFunction": [
+                {
+                    "idGNBCUUPFunction": "2222",
+                    "attributes": {
+                        "gNBId": 98763.0
+                    }
+                }
+            ],
+            "GNBDUFunction": [
+                {
+                    "idGNBDUFunction": "5",
+                    "NRCellDU": [
+                        {
+                            "idNRCellDU": "13910",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 13910.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "10897",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 10897.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "14656",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 14656.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "13905",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 13905.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "14655",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 14655.0
+                            }
+                        }
+                    ],
+                    "attributes": {
+                        "gNBDUId": 5.0,
+                        "gNBDUName": "gnduserver5",
+                        "gNBIdLength": 32.0
+                    }
+                },
+                {
+                    "idGNBDUFunction": "4",
+                    "NRCellDU": [
+                        {
+                            "idNRCellDU": "11569",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 11569.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "11568",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 11568.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "11561",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 11561.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "10896",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 10896.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "11562",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 11562.0
+                            }
+                        }
+                    ],
+                    "attributes": {
+                        "gNBDUId": 4.0,
+                        "gNBDUName": "gnduserver4",
+                        "gNBIdLength": 32.0
+                    }
+                },
+                {
+                    "idGNBDUFunction": "6",
+                    "NRCellDU": [
+                        {
+                            "idNRCellDU": "14427",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 14427.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15361",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15361.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15548",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15548.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15360",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15360.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15549",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15549.0
+                            }
+                        }
+                    ],
+                    "attributes": {
+                        "gNBDUId": 6.0,
+                        "gNBDUName": "gnduserver6",
+                        "gNBIdLength": 32.0
+                    }
+                }
+            ],
+            "GNBCUCPFunction": [
+                {
+                    "idGNBCUCPFunction": "cucpserver2",
+                    "NRCellCU": [
+                        {
+                            "idNRCellCU": "13910",
+                            "attributes": {
+                                "cellLocalId": 13910.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "14656",
+                            "attributes": {
+                                "cellLocalId": 14656.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "10897",
+                            "attributes": {
+                                "cellLocalId": 10897.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "14655",
+                            "attributes": {
+                                "cellLocalId": 14655.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "11561",
+                            "attributes": {
+                                "cellLocalId": 11561.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "11569",
+                            "attributes": {
+                                "cellLocalId": 11569.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "13905",
+                            "attributes": {
+                                "cellLocalId": 13905.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15549",
+                            "attributes": {
+                                "cellLocalId": 15549.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15361",
+                            "attributes": {
+                                "cellLocalId": 15361.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15548",
+                            "attributes": {
+                                "cellLocalId": 15548.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15360",
+                            "attributes": {
+                                "cellLocalId": 15360.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "14427",
+                            "attributes": {
+                                "cellLocalId": 14427.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "11562",
+                            "attributes": {
+                                "cellLocalId": 11562.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "11568",
+                            "attributes": {
+                                "cellLocalId": 11568.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "10896",
+                            "attributes": {
+                                "cellLocalId": 10896.0
+                            }
+                        }
+                    ],
+                    "attributes": {
+                        "gNBId": 98763.0,
+                        "gNBCUName": "cucpserver2",
+                        "gNBIdLength": 32.0
+                    }
+                }
+            ]
+        },
+        {
+            "idNearRTRIC": "11",
+            "GNBCUUPFunction": [
+                {
+                    "idGNBCUUPFunction": "1111",
+                    "attributes": {
+                        "gNBId": 98763.0
+                    }
+                }
+            ],
+            "GNBDUFunction": [
+                {
+                    "idGNBDUFunction": "1",
+                    "NRCellDU": [
+                        {
+                            "idNRCellDU": "15289",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15289.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15290",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15290.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15689",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15689.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15687",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15687.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15296",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15296.0
+                            }
+                        }
+                    ],
+                    "attributes": {
+                        "gNBDUId": 1.0,
+                        "gNBDUName": "gnduserver1",
+                        "gNBIdLength": 32.0
+                    }
+                },
+                {
+                    "idGNBDUFunction": "2",
+                    "NRCellDU": [
+                        {
+                            "idNRCellDU": "15175",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15175.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15176",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15176.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15155",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15155.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15825",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15825.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15174",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15174.0
+                            }
+                        }
+                    ],
+                    "attributes": {
+                        "gNBDUId": 2.0,
+                        "gNBDUName": "gnduserver2",
+                        "gNBIdLength": 32.0
+                    }
+                },
+                {
+                    "idGNBDUFunction": "3",
+                    "NRCellDU": [
+                        {
+                            "idNRCellDU": "15826",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15826.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15425",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15425.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "14000",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 14000.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "13999",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 13999.0
+                            }
+                        },
+                        {
+                            "idNRCellDU": "15426",
+                            "attributes": {
+                                "nRPCI": 12.0,
+                                "nRTAC": 310.0,
+                                "cellLocalId": 15426.0
+                            }
+                        }
+                    ],
+                    "attributes": {
+                        "gNBDUId": 3.0,
+                        "gNBDUName": "gnduserver3",
+                        "gNBIdLength": 32.0
+                    }
+                }
+            ],
+            "GNBCUCPFunction": [
+                {
+                    "idGNBCUCPFunction": "cucpserver1",
+                    "NRCellCU": [
+                        {
+                            "idNRCellCU": "15176",
+                            "attributes": {
+                                "cellLocalId": 15176.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15296",
+                            "attributes": {
+                                "cellLocalId": 15296.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15174",
+                            "attributes": {
+                                "cellLocalId": 15174.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15290",
+                            "attributes": {
+                                "cellLocalId": 15290.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15825",
+                            "attributes": {
+                                "cellLocalId": 15825.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15689",
+                            "attributes": {
+                                "cellLocalId": 15689.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15175",
+                            "attributes": {
+                                "cellLocalId": 15175.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15426",
+                            "attributes": {
+                                "cellLocalId": 15426.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15687",
+                            "attributes": {
+                                "cellLocalId": 15687.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15826",
+                            "attributes": {
+                                "cellLocalId": 15826.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "14000",
+                            "attributes": {
+                                "cellLocalId": 14000.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15425",
+                            "attributes": {
+                                "cellLocalId": 15425.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15289",
+                            "attributes": {
+                                "cellLocalId": 15289.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "13999",
+                            "attributes": {
+                                "cellLocalId": 13999.0
+                            }
+                        },
+                        {
+                            "idNRCellCU": "15155",
+                            "attributes": {
+                                "cellLocalId": 15155.0
+                            }
+                        }
+                    ],
+                    "attributes": {
+                        "gNBId": 98763.0,
+                        "gNBCUName": "cucpserver1",
+                        "gNBIdLength": 32.0
+                    }
+                }
+            ]
+        }
+    ]
+}
+'
diff --git a/src/main/resources/db/changelog/data/02-init-data.xml b/src/main/resources/db/changelog/data/02-init-data.xml
new file mode 100644
index 0000000..24898ba
--- /dev/null
+++ b/src/main/resources/db/changelog/data/02-init-data.xml
@@ -0,0 +1,51 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+  ============LICENSE_START=======================================================
+  Copyright (c) 2021 Bell Canada.
+  ================================================================================
+  Licensed under the Apache License, Version 2.0 (the "License");
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+
+        http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+  ============LICENSE_END=========================================================
+-->
+
+<databaseChangeLog
+        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
+        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
+            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
+
+    <changeSet id="2" author="cps">
+        <comment>Create initialization data</comment>
+        <loadData
+                tableName="network_data"
+                file="db/changelog/data/02-init-data.dat"
+                separator="|"
+                quotchar="'">
+            <column name="timestamp"/>
+            <column name="dataspace"/>
+            <column name="schema_set"/>
+            <column name="anchor"/>
+            <column name="payload"/>
+            <column name="version"/>
+        </loadData>
+        <rollback>
+            <delete tableName="network_data">
+                <where>
+                    dataspace = 'NFP-Operational'
+                    and schema_set = 'cps-ran-schema-model'
+                    and anchor ='ran-network-simulation'
+                </where>
+            </delete>
+        </rollback>
+    </changeSet>
+
+</databaseChangeLog>
diff --git a/src/main/resources/db/changelog/schema/01-init-schema.xml b/src/main/resources/db/changelog/schema/01-init-schema.xml
new file mode 100644
index 0000000..b10cd00
--- /dev/null
+++ b/src/main/resources/db/changelog/schema/01-init-schema.xml
@@ -0,0 +1,92 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+  ============LICENSE_START=======================================================
+  Copyright (c) 2021 Bell Canada.
+  ================================================================================
+  Licensed under the Apache License, Version 2.0 (the "License");
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+
+        http://www.apache.org/licenses/LICENSE-2.0
+
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+  ============LICENSE_END=========================================================
+-->
+
+<databaseChangeLog
+        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
+        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
+            http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
+
+    <changeSet id="1.1" author="cps">
+        <comment>Create network data timescale table</comment>
+        <createTable tableName="network_data"
+                remarks="Table storing network configuration or state data along with its timestamp.">
+            <column name="timestamp" type="TIMESTAMP WITH TIMEZONE"
+                    remarks="The timestamp when configuration or state has been observed.">
+                <constraints nullable="false"/>
+            </column>
+            <column name="dataspace" type="VARCHAR(255)"
+                    remarks="The dataspace, configuration or state belongs to. Refers to CPS Core dataspace.">
+                <constraints nullable="false"/>
+            </column>
+            <column name="anchor" type="VARCHAR(255)"
+                    remarks="The anchor, configuration or state is attached to. Refers to CPS Core anchor.">
+                <constraints nullable="false"/>
+            </column>
+            <column name="schema_set" type="VARCHAR(255)"
+                    remarks="The schema set, configuration or state complies to. Refers to CPS Core schema set.">
+                <constraints nullable="false"/>
+            </column>
+            <column name="payload" type="JSONB"
+                    remarks="The complete json payload representing configuration or state data.">
+                <constraints nullable="false"/>
+            </column>
+            <column name="version" type="TIMESTAMP WITH TIMEZONE"
+                    remarks="The system version of the record, automatically set by the application with the timestamp when the record is persisted.">
+                <constraints nullable="false"/>
+            </column>
+        </createTable>
+        <sql>
+            SELECT create_hypertable('network_data', 'timestamp', chunk_time_interval => interval '1 minute');
+        </sql>
+        <rollback>
+            <sql>
+                DROP table network_data;
+            </sql>
+        </rollback>
+    </changeSet>
+
+    <changeSet id="1.2" author="cps">
+        <comment>Create indexes on network data</comment>
+        <createIndex indexName="network_data_dataspace_anchor_timestamp_idx" tableName="network_data"
+                unique="true">
+            <column name="dataspace"/>
+            <column name="anchor"/>
+            <column name="timestamp" descending="true"/>
+        </createIndex>
+        <createIndex indexName="network_data_dataspace_schema_set_timestamp_idx" tableName="network_data">
+            <column name="dataspace"/>
+            <column name="schema_set"/>
+            <column name="timestamp" descending="true"/>
+        </createIndex>
+    </changeSet>
+
+    <changeSet id="1.3" author="cps">
+        <comment>Create GIN index on network data payload</comment>
+        <sql>
+            CREATE INDEX network_data_payload_idx ON network_data USING GIN (payload);
+        </sql>
+        <rollback>
+            <sql>
+                DROP INDEX network_data_payload_idx;
+            </sql>
+        </rollback>
+    </changeSet>
+
+</databaseChangeLog>
-- 
cgit 1.2.3-korg