gerrit.o-ran-sc Code Review - smo/teiv.git/blob

   1 /*
   2  *  ============LICENSE_START=======================================================
   3  *  Copyright (C) 2024 Ericsson
   4  *  Modifications Copyright (C) 2024 OpenInfra Foundation Europe
   5  *  ================================================================================
   6  *  Licensed under the Apache License, Version 2.0 (the "License");
   7  *  you may not use this file except in compliance with the License.
   8  *  You may obtain a copy of the License at
   9  *
  10  *        http://www.apache.org/licenses/LICENSE-2.0
  11  *
  12  *  Unless required by applicable law or agreed to in writing, software
  13  *  distributed under the License is distributed on an "AS IS" BASIS,
  14  *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  15  *  See the License for the specific language governing permissions and
  16  *  limitations under the License.
  17  *
  18  *  SPDX-License-Identifier: Apache-2.0
  19  *  ============LICENSE_END=========================================================
  20  */
  21 package org.oran.smo.teiv.pgsqlgenerator.schema.data;
  22
  23 import java.util.List;
  24 import java.util.Map;
  25
  26 import org.oran.smo.teiv.pgsqlgenerator.PostgresIndex;
  27 import org.springframework.stereotype.Component;
  28
  29 import lombok.extern.slf4j.Slf4j;
  30
  31 import org.oran.smo.teiv.pgsqlgenerator.Column;
  32 import org.oran.smo.teiv.pgsqlgenerator.PostgresConstraint;
  33 import org.oran.smo.teiv.pgsqlgenerator.ForeignKeyConstraint;
  34 import org.oran.smo.teiv.pgsqlgenerator.NotNullConstraint;
  35 import org.oran.smo.teiv.pgsqlgenerator.PrimaryKeyConstraint;
  36 import org.oran.smo.teiv.pgsqlgenerator.Table;
  37 import org.oran.smo.teiv.pgsqlgenerator.UniqueConstraint;
  38
  39 import static org.oran.smo.teiv.pgsqlgenerator.Constants.CREATE;
  40 import static org.oran.smo.teiv.pgsqlgenerator.Constants.ALTER;
  41 import static org.oran.smo.teiv.pgsqlgenerator.Constants.ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S;
  42 import static org.oran.smo.teiv.pgsqlgenerator.Constants.DEFAULT;
  43 import static org.oran.smo.teiv.pgsqlgenerator.Constants.GEOGRAPHY;
  44 import static org.oran.smo.teiv.pgsqlgenerator.Constants.GEO_LOCATION;
  45 import static org.oran.smo.teiv.pgsqlgenerator.Constants.ID;
  46 import static org.oran.smo.teiv.pgsqlgenerator.Constants.ALTER_TABLE_TIES_DATA_S;
  47 import static org.oran.smo.teiv.pgsqlgenerator.Constants.INDEX;
  48
  49 @Slf4j
  50 @Component
  51 public class DataSchemaHelper {
  52
  53     /**
  54      * Generates SQL statements for schema alterations based on identified changes.
  55      *
  56      * @param differences
  57      *     Map of identified changes to models
  58      * @return StringBuilder containing SQL statements
  59      */
  60     public StringBuilder generateSchemaFromDifferences(Map<String, List<Table>> differences) {
  61         StringBuilder generatedSchema = new StringBuilder();
  62         if (differences.isEmpty()) {
  63             log.info("No differences identified!!");
  64         } else {
  65             for (Map.Entry<String, List<Table>> entry : differences.entrySet()) {
  66                 switch (entry.getKey()) {
  67                     case DEFAULT -> generatedSchema.append(generateDefaultStatementsFromDifferences(entry.getValue()));
  68                     case INDEX -> generatedSchema.append(generateIndexStatementsFromDifferences(entry.getValue()));
  69                     case ALTER -> generatedSchema.append(generateAlterStatementsFromDifferences(entry.getValue()));
  70                     case CREATE -> generatedSchema.append(generateCreateStatementsFromDifferences(entry.getValue()));
  71                 }
  72             }
  73         }
  74         return generatedSchema;
  75     }
  76
  77     /**
  78      * Generates SQL statements for CREATE TABLE from differences.
  79      */
  80     private StringBuilder generateCreateStatementsFromDifferences(List<Table> tables) {
  81         StringBuilder storeSchemaForCreateStatements = new StringBuilder();
  82         StringBuilder storeAlterStatementsForPrimaryKeyConstraints = new StringBuilder();
  83         StringBuilder storeAlterStatementsForAllOtherConstraints = new StringBuilder();
  84         StringBuilder storeIndexStatements = new StringBuilder();
  85         for (Table table : tables) {
  86             storeAlterStatementsForPrimaryKeyConstraints.append(generateAlterStatementsForPrimaryKeyConstraints(table
  87                     .getColumns()));
  88             storeAlterStatementsForAllOtherConstraints.append(generateAlterStatementsForAllOtherConstraints(table
  89                     .getColumns()));
  90             storeIndexStatements.append(generateIndexStatementForColumns(table.getColumns()));
  91             storeSchemaForCreateStatements.append(generateCreateTableStatements(table.getColumns(), table.getName()));
  92         }
  93         storeSchemaForCreateStatements.append(storeAlterStatementsForPrimaryKeyConstraints).append(
  94                 storeAlterStatementsForAllOtherConstraints).append(storeIndexStatements);
  95         return storeSchemaForCreateStatements;
  96     }
  97
  98     /**
  99      * Generates SQL statements for creating new tables.
 100      */
 101     private StringBuilder generateCreateTableStatements(List<Column> newColumns, String tableName) {
 102
 103         StringBuilder storeTableSchema = new StringBuilder(String.format("CREATE TABLE IF NOT EXISTS ties_data.\"%s\" (%n",
 104                 tableName));
 105         StringBuilder storeColumns = new StringBuilder();
 106         StringBuilder storeDefaultValues = new StringBuilder();
 107
 108         for (Column newColumn : newColumns) {
 109             if (newColumn.getDefaultValue() != null) {
 110                 storeDefaultValues.append(generateDefaultValueStatements(newColumn, tableName));
 111             }
 112             // id column must come in the top of the table
 113             if (newColumn.getName().equals(ID)) {
 114                 storeTableSchema.append(String.format("\t\"%s\"\t\t\t%s,%n", newColumn.getName(), newColumn.getDataType()));
 115             } else {
 116                 storeColumns.append(generateCreateColumnStatements(newColumn));
 117             }
 118         }
 119         storeColumns.deleteCharAt(storeColumns.lastIndexOf(","));
 120         storeTableSchema.append(storeColumns).append(");\n\n");
 121         return storeTableSchema.append(storeDefaultValues);
 122     }
 123
 124     /**
 125      * Generate CREATE sql statements for columns who have no constraints, default value or enums defined.
 126      */
 127     private StringBuilder generateCreateColumnStatements(Column newColumn) {
 128         return new StringBuilder(String.format("\t\"%s\"\t\t\t%s,%n", newColumn.getName(), newColumn.getDataType()));
 129     }
 130
 131     /**
 132      * Generate ALTER sql statements for attributes with default values.
 133      */
 134     private StringBuilder generateDefaultValueStatements(Column newColumn, String tableName) {
 135         return new StringBuilder(String.format(
 136                 "ALTER TABLE ONLY ties_data.\"%s\" ALTER COLUMN \"%s\" SET DEFAULT '%s';%n%n", tableName, newColumn
 137                         .getName(), newColumn.getDefaultValue()));
 138     }
 139
 140     /**
 141      * Write sql statements for UNIQUE, NOT NULL and FOREIGN KEY constraints.
 142      */
 143     private StringBuilder generateAlterStatementsForAllOtherConstraints(List<Column> columns) {
 144         StringBuilder storeOtherAlterStatements = new StringBuilder();
 145
 146         columns.stream().flatMap(newColumn -> newColumn.getPostgresConstraints().stream()).filter(
 147                 constraint -> !(constraint instanceof PrimaryKeyConstraint)).forEach(constraint -> storeOtherAlterStatements
 148                         .append(generateConstraintStatement(constraint)));
 149
 150         return storeOtherAlterStatements;
 151     }
 152
 153     private StringBuilder generateAlterStatementsForPrimaryKeyConstraints(List<Column> columns) {
 154         StringBuilder storePKAlterStatements = new StringBuilder();
 155
 156         columns.stream().flatMap(newColumn -> newColumn.getPostgresConstraints().stream()).filter(
 157                 PrimaryKeyConstraint.class::isInstance).forEach(constraint -> storePKAlterStatements.append(
 158                         generateConstraintStatement(constraint)));
 159
 160         return storePKAlterStatements;
 161     }
 162
 163     private String generateConstraintStatement(PostgresConstraint postgresConstraint) {
 164         String constraintSql = generateConstraintSql(postgresConstraint);
 165         return String.format("SELECT ties_data.create_constraint_if_not_exists(%n\t'%s',%n '%s',%n '%s;'%n);%n%n",
 166                 postgresConstraint.getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), constraintSql);
 167     }
 168
 169     private StringBuilder generateIndexStatementForColumns(List<Column> columns) {
 170         StringBuilder indexStmt = new StringBuilder();
 171         columns.forEach(column -> column.getPostgresIndexList().forEach(postgresIndex -> indexStmt.append(
 172                 generateIndexStatement(postgresIndex))));
 173         return indexStmt;
 174     }
 175
 176     private String generateConstraintSql(PostgresConstraint postgresConstraint) {
 177         if (postgresConstraint instanceof PrimaryKeyConstraint) {
 178             return String.format(ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "PRIMARY KEY (\"%s\")", postgresConstraint
 179                     .getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), postgresConstraint
 180                             .getColumnToAddConstraintTo());
 181         } else if (postgresConstraint instanceof ForeignKeyConstraint foreignKeyConstraint) {
 182             return String.format(
 183                     ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "FOREIGN KEY (\"%s\") REFERENCES ties_data.\"%s\" (id) ON DELETE CASCADE",
 184                     postgresConstraint.getTableToAddConstraintTo(), postgresConstraint.getConstraintName(),
 185                     postgresConstraint.getColumnToAddConstraintTo(), foreignKeyConstraint.getReferencedTable());
 186         } else if (postgresConstraint instanceof UniqueConstraint) {
 187             return String.format(ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "UNIQUE (\"%s\")", postgresConstraint
 188                     .getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), postgresConstraint
 189                             .getColumnToAddConstraintTo());
 190         } else if (postgresConstraint instanceof NotNullConstraint) {
 191             return String.format(ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "NOT NULL (\"%s\")", postgresConstraint
 192                     .getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), postgresConstraint
 193                             .getColumnToAddConstraintTo());
 194         } else {
 195             return "";
 196         }
 197     }
 198
 199     /**
 200      * Generates SQL statements for ALTER TABLE from mapped entity attributes.
 201      */
 202     private StringBuilder generateAlterStatementsFromDifferences(List<Table> tables) {
 203         StringBuilder storeSchemaForAlterStatements = new StringBuilder();
 204         StringBuilder storeAlterStatementsForPrimaryKeyConstraints = new StringBuilder();
 205         StringBuilder storeAlterStatementsForAllOtherConstraints = new StringBuilder();
 206         StringBuilder storeIndexStatements = new StringBuilder();
 207         for (Table table : tables) {
 208             storeSchemaForAlterStatements.append(generateAlterStatements(table.getColumns(), table.getName()));
 209             storeAlterStatementsForPrimaryKeyConstraints.append(generateAlterStatementsForPrimaryKeyConstraints(table
 210                     .getColumns()));
 211             storeAlterStatementsForAllOtherConstraints.append(generateAlterStatementsForAllOtherConstraints(table
 212                     .getColumns()));
 213             storeIndexStatements.append(generateIndexStatementForColumns(table.getColumns()));
 214         }
 215         return storeSchemaForAlterStatements.append(storeAlterStatementsForPrimaryKeyConstraints).append(
 216                 storeAlterStatementsForAllOtherConstraints).append(storeIndexStatements);
 217     }
 218
 219     /**
 220      * Generates SQL statements for altering tables based on mapped entity attributes.
 221      */
 222     private StringBuilder generateAlterStatements(List<Column> columns, String tableName) {
 223         StringBuilder storeSchema = new StringBuilder();
 224         for (Column newColumn : columns) {
 225             if (newColumn.getName().equals(GEO_LOCATION)) {
 226                 newColumn.setDataType(GEOGRAPHY);
 227             }
 228             storeSchema.append(generateAlterTableStatements(newColumn, tableName));
 229             if (newColumn.getDefaultValue() != null) {
 230                 storeSchema.append(generateDefaultValueStatements(newColumn, tableName));
 231             }
 232         }
 233         return storeSchema;
 234     }
 235
 236     /**
 237      * Generates ALTER SQL statements to add new default values in newly identified columns.
 238      */
 239     private StringBuilder generateDefaultStatementsFromDifferences(List<Table> tables) {
 240         StringBuilder storeSchemaForDefaultStatements = new StringBuilder();
 241         for (Table table : tables) {
 242             StringBuilder storeSchema = new StringBuilder();
 243             for (Column newColumn : table.getColumns()) {
 244                 if (newColumn.getDefaultValue() != null) {
 245                     storeSchema.append(generateDefaultValueStatements(newColumn, table.getName()));
 246                 }
 247             }
 248             storeSchemaForDefaultStatements.append(storeSchema);
 249         }
 250         return storeSchemaForDefaultStatements;
 251     }
 252
 253     /**
 254      * Generate ALTER sql statements for attributes who have no constraints, default value or enums defined.
 255      */
 256     private StringBuilder generateAlterTableStatements(Column newColumn, String tableName) {
 257         return new StringBuilder(String.format(ALTER_TABLE_TIES_DATA_S + "ADD COLUMN IF NOT EXISTS \"%s\" %s;%n%n",
 258                 tableName, newColumn.getName(), newColumn.getDataType()));
 259     }
 260
 261     private StringBuilder generateIndexStatementsFromDifferences(List<Table> tables) {
 262         StringBuilder storeSchemaForIndexStatements = new StringBuilder();
 263         for (Table table : tables) {
 264             StringBuilder storeSchema = new StringBuilder();
 265             for (Column column : table.getColumns()) {
 266                 if (!column.getPostgresIndexList().isEmpty()) {
 267                     column.getPostgresIndexList().forEach(index -> storeSchema.append(generateIndexStatement(index)));
 268                 }
 269             }
 270             storeSchemaForIndexStatements.append(storeSchema);
 271         }
 272         return storeSchemaForIndexStatements;
 273     }
 274
 275     private String generateIndexStatement(PostgresIndex postgresIndex) {
 276         return String.format(postgresIndex.getIndexType().getCreateIndexStmt(), postgresIndex.getIndexName(), postgresIndex
 277                 .getTableNameToAddIndexTo(), postgresIndex.getColumnNameToAddIndexTo()) + "\n\n";
 278     }
 279 }