diff options
author | 2021-04-20 08:22:56 -0400 | |
---|---|---|
committer | 2021-04-22 10:00:22 -0400 | |
commit | 05855d531b912698ee63b1fd9de4303baff83670 (patch) | |
tree | 0496540ac248dff37629cdcffde6215b3096a343 /src/main/resources | |
parent | 472d2e8d8df431e46b0f0dead8448bff5e80ab0d (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')
-rw-r--r-- | src/main/resources/application.yml | 23 | ||||
-rw-r--r-- | src/main/resources/db/changelog/changelog-master.xml | 29 | ||||
-rw-r--r-- | src/main/resources/db/changelog/data/02-init-data.dat | 537 | ||||
-rw-r--r-- | src/main/resources/db/changelog/data/02-init-data.xml | 51 | ||||
-rw-r--r-- | src/main/resources/db/changelog/schema/01-init-schema.xml | 92 |
5 files changed, 732 insertions, 0 deletions
diff --git a/src/main/resources/application.yml b/src/main/resources/application.yml new file mode 100644 index 0000000..0db1792 --- /dev/null +++ b/src/main/resources/application.yml @@ -0,0 +1,23 @@ +# ============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========================================================= + +spring: + datasource: + url: jdbc:postgresql://${DB_HOST}:${DB_PORT}/${DB_NAME} + username: ${DB_USERNAME} + password: ${DB_PASSWORD} + liquibase: + change-log: classpath:/db/changelog/changelog-master.xml 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> |