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 -> {
 172             column.getPostgresIndexList().forEach(postgresIndex -> {
 173                 indexStmt.append(generateIndexStatement(postgresIndex));
 174             });
 175         });
 176         return indexStmt;
 177     }
 178
 179     private String generateConstraintSql(PostgresConstraint postgresConstraint) {
 180         if (postgresConstraint instanceof PrimaryKeyConstraint) {
 181             return String.format(ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "PRIMARY KEY (\"%s\")", postgresConstraint
 182                     .getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), postgresConstraint
 183                             .getColumnToAddConstraintTo());
 184         } else if (postgresConstraint instanceof ForeignKeyConstraint) {
 185             return String.format(
 186                     ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "FOREIGN KEY (\"%s\") REFERENCES ties_data.\"%s\" (id) ON DELETE CASCADE",
 187                     postgresConstraint.getTableToAddConstraintTo(), postgresConstraint.getConstraintName(),
 188                     postgresConstraint.getColumnToAddConstraintTo(), ((ForeignKeyConstraint) postgresConstraint)
 189                             .getReferencedTable());
 190         } else if (postgresConstraint instanceof UniqueConstraint) {
 191             return String.format(ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "UNIQUE (\"%s\")", postgresConstraint
 192                     .getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), postgresConstraint
 193                             .getColumnToAddConstraintTo());
 194         } else if (postgresConstraint instanceof NotNullConstraint) {
 195             return String.format(ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "NOT NULL (\"%s\")", postgresConstraint
 196                     .getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), postgresConstraint
 197                             .getColumnToAddConstraintTo());
 198         } else {
 199             return "";
 200         }
 201     }
 202
 203     /**
 204      * Generates SQL statements for ALTER TABLE from mapped entity attributes.
 205      */
 206     private StringBuilder generateAlterStatementsFromDifferences(List<Table> tables) {
 207         StringBuilder storeSchemaForAlterStatements = new StringBuilder();
 208         StringBuilder storeAlterStatementsForPrimaryKeyConstraints = new StringBuilder();
 209         StringBuilder storeAlterStatementsForAllOtherConstraints = new StringBuilder();
 210         StringBuilder storeIndexStatements = new StringBuilder();
 211         for (Table table : tables) {
 212             storeSchemaForAlterStatements.append(generateAlterStatements(table.getColumns(), table.getName()));
 213             storeAlterStatementsForPrimaryKeyConstraints.append(generateAlterStatementsForPrimaryKeyConstraints(table
 214                     .getColumns()));
 215             storeAlterStatementsForAllOtherConstraints.append(generateAlterStatementsForAllOtherConstraints(table
 216                     .getColumns()));
 217             storeIndexStatements.append(generateIndexStatementForColumns(table.getColumns()));
 218         }
 219         return storeSchemaForAlterStatements.append(storeAlterStatementsForPrimaryKeyConstraints).append(
 220                 storeAlterStatementsForAllOtherConstraints).append(storeIndexStatements);
 221     }
 222
 223     /**
 224      * Generates SQL statements for altering tables based on mapped entity attributes.
 225      */
 226     private StringBuilder generateAlterStatements(List<Column> columns, String tableName) {
 227         StringBuilder storeSchema = new StringBuilder();
 228         for (Column newColumn : columns) {
 229             if (newColumn.getName().equals(GEO_LOCATION)) {
 230                 newColumn.setDataType(GEOGRAPHY);
 231             }
 232             storeSchema.append(generateAlterTableStatements(newColumn, tableName));
 233             if (newColumn.getDefaultValue() != null) {
 234                 storeSchema.append(generateDefaultValueStatements(newColumn, tableName));
 235             }
 236         }
 237         return storeSchema;
 238     }
 239
 240     /**
 241      * Generates ALTER SQL statements to add new default values in newly identified columns.
 242      */
 243     private StringBuilder generateDefaultStatementsFromDifferences(List<Table> tables) {
 244         StringBuilder storeSchemaForDefaultStatements = new StringBuilder();
 245         for (Table table : tables) {
 246             StringBuilder storeSchema = new StringBuilder();
 247             for (Column newColumn : table.getColumns()) {
 248                 if (newColumn.getDefaultValue() != null) {
 249                     storeSchema.append(generateDefaultValueStatements(newColumn, table.getName()));
 250                 }
 251             }
 252             storeSchemaForDefaultStatements.append(storeSchema);
 253         }
 254         return storeSchemaForDefaultStatements;
 255     }
 256
 257     /**
 258      * Generate ALTER sql statements for attributes who have no constraints, default value or enums defined.
 259      */
 260     private StringBuilder generateAlterTableStatements(Column newColumn, String tableName) {
 261         return new StringBuilder(String.format(ALTER_TABLE_TIES_DATA_S + "ADD COLUMN IF NOT EXISTS \"%s\" %s;%n%n",
 262                 tableName, newColumn.getName(), newColumn.getDataType()));
 263     }
 264
 265     private StringBuilder generateIndexStatementsFromDifferences(List<Table> tables) {
 266         StringBuilder storeSchemaForIndexStatements = new StringBuilder();
 267         for (Table table : tables) {
 268             StringBuilder storeSchema = new StringBuilder();
 269             for (Column column : table.getColumns()) {
 270                 if (!column.getPostgresIndexList().isEmpty()) {
 271                     column.getPostgresIndexList().forEach(index -> {
 272                         storeSchema.append(generateIndexStatement(index));
 273                     });
 274                 }
 275             }
 276             storeSchemaForIndexStatements.append(storeSchema);
 277         }
 278         return storeSchemaForIndexStatements;
 279     }
 280
 281     private String generateIndexStatement(PostgresIndex postgresIndex) {
 282         return String.format(postgresIndex.getIndexType().getCreateIndexStmt(), postgresIndex.getIndexName(), postgresIndex
 283                 .getTableNameToAddIndexTo(), postgresIndex.getColumnNameToAddIndexTo()) + "\n\n";
 284     }
 285 }