aboutsummaryrefslogtreecommitdiffstats
path: root/src/main/resources/db
diff options
context:
space:
mode:
authorBruno Sakoto <bruno.sakoto@bell.ca>2021-04-20 08:22:56 -0400
committerBruno Sakoto <bruno.sakoto@bell.ca>2021-04-22 10:00:22 -0400
commit05855d531b912698ee63b1fd9de4303baff83670 (patch)
tree0496540ac248dff37629cdcffde6215b3096a343 /src/main/resources/db
parent472d2e8d8df431e46b0f0dead8448bff5e80ab0d (diff)
Implement db schema with liquibase
Issue-ID: CPS-193 Signed-off-by: Bruno Sakoto <bruno.sakoto@bell.ca> Change-Id: Ieabee4b86bbe7a42c05bb225499487cfcc9deb1d
Diffstat (limited to 'src/main/resources/db')
-rw-r--r--src/main/resources/db/changelog/changelog-master.xml29
-rw-r--r--src/main/resources/db/changelog/data/02-init-data.dat537
-rw-r--r--src/main/resources/db/changelog/data/02-init-data.xml51
-rw-r--r--src/main/resources/db/changelog/schema/01-init-schema.xml92
4 files changed, 709 insertions, 0 deletions
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>