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.springframework.stereotype.Component;
  27
  28 import lombok.extern.slf4j.Slf4j;
  29
  30 import org.oran.smo.teiv.pgsqlgenerator.Column;
  31 import org.oran.smo.teiv.pgsqlgenerator.PostgresConstraint;
  32 import org.oran.smo.teiv.pgsqlgenerator.ForeignKeyConstraint;
  33 import org.oran.smo.teiv.pgsqlgenerator.NotNullConstraint;
  34 import org.oran.smo.teiv.pgsqlgenerator.PrimaryKeyConstraint;
  35 import org.oran.smo.teiv.pgsqlgenerator.Table;
  36 import org.oran.smo.teiv.pgsqlgenerator.UniqueConstraint;
  37
  38 import static org.oran.smo.teiv.pgsqlgenerator.Constants.CREATE;
  39 import static org.oran.smo.teiv.pgsqlgenerator.Constants.ALTER;
  40 import static org.oran.smo.teiv.pgsqlgenerator.Constants.ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S;
  41 import static org.oran.smo.teiv.pgsqlgenerator.Constants.ID;
  42 import static org.oran.smo.teiv.pgsqlgenerator.Constants.ALTER_TABLE_TIES_DATA_S;
  43
  44 @Slf4j
  45 @Component
  46 public class DataSchemaHelper {
  47
  48     /**
  49      * Generates SQL statements for schema alterations based on identified changes.
  50      *
  51      * @param differences
  52      *     Map of identified changes to models
  53      * @return StringBuilder containing SQL statements
  54      */
  55     public StringBuilder generateSchemaFromDifferences(Map<String, List<Table>> differences) {
  56         StringBuilder generatedSchema = new StringBuilder();
  57         if (differences.isEmpty()) {
  58             log.info("No differences identified!!");
  59         } else {
  60             for (Map.Entry<String, List<Table>> entry : differences.entrySet()) {
  61                 switch (entry.getKey()) {
  62                     case CREATE -> generatedSchema.append(generateCreateStatementsFromDifferences(entry.getValue()));
  63                     case ALTER -> generatedSchema.append(generateAlterStatementsFromDifferences(entry.getValue()));
  64                     default -> generatedSchema.append(generateDefaultStatementsFromDifferences(entry.getValue()));
  65                 }
  66             }
  67         }
  68         return generatedSchema;
  69     }
  70
  71     /**
  72      * Generates SQL statements for CREATE TABLE from differences.
  73      */
  74     private StringBuilder generateCreateStatementsFromDifferences(List<Table> tables) {
  75         StringBuilder storeSchemaForCreateStatements = new StringBuilder();
  76         StringBuilder storeAlterStatementsForPrimaryKeyConstraints = new StringBuilder();
  77         StringBuilder storeAlterStatementsForAllOtherConstraints = new StringBuilder();
  78         for (Table table : tables) {
  79             storeAlterStatementsForPrimaryKeyConstraints.append(generateAlterStatementsForPrimaryKeyConstraints(table
  80                     .getColumns()));
  81             storeAlterStatementsForAllOtherConstraints.append(generateAlterStatementsForAllOtherConstraints(table
  82                     .getColumns()));
  83             storeSchemaForCreateStatements.append(generateCreateTableStatements(table.getColumns(), table.getName()));
  84         }
  85         storeSchemaForCreateStatements.append(storeAlterStatementsForPrimaryKeyConstraints).append(
  86                 storeAlterStatementsForAllOtherConstraints);
  87         return storeSchemaForCreateStatements;
  88     }
  89
  90     /**
  91      * Generates SQL statements for creating new tables.
  92      */
  93     private StringBuilder generateCreateTableStatements(List<Column> newColumns, String tableName) {
  94
  95         StringBuilder storeTableSchema = new StringBuilder(String.format("CREATE TABLE IF NOT EXISTS ties_data.\"%s\" (%n",
  96                 tableName));
  97         StringBuilder storeColumns = new StringBuilder();
  98         StringBuilder storeDefaultValues = new StringBuilder();
  99
 100         for (Column newColumn : newColumns) {
 101             if (newColumn.getDefaultValue() != null) {
 102                 storeDefaultValues.append(generateDefaultValueStatements(newColumn, tableName));
 103             }
 104             // id column must come in the top of the table
 105             if (newColumn.getName().equals(ID)) {
 106                 storeTableSchema.append(String.format("\t\"%s\"\t\t\t%s,%n", newColumn.getName(), newColumn.getDataType()));
 107             } else {
 108                 storeColumns.append(generateCreateColumnStatements(newColumn));
 109             }
 110         }
 111         storeColumns.deleteCharAt(storeColumns.lastIndexOf(","));
 112         storeTableSchema.append(storeColumns).append(");\n\n");
 113         return storeTableSchema.append(storeDefaultValues);
 114     }
 115
 116     /**
 117      * Generate CREATE sql statements for columns who have no constraints, default value or enums defined.
 118      */
 119     private StringBuilder generateCreateColumnStatements(Column newColumn) {
 120         return new StringBuilder(String.format("\t\"%s\"\t\t\t%s,%n", newColumn.getName(), newColumn.getDataType()));
 121     }
 122
 123     /**
 124      * Generate ALTER sql statements for attributes with default values.
 125      */
 126     private StringBuilder generateDefaultValueStatements(Column newColumn, String tableName) {
 127         return new StringBuilder(String.format(
 128                 "ALTER TABLE ONLY ties_data.\"%s\" ALTER COLUMN \"%s\" SET DEFAULT '%s';%n%n", tableName, newColumn
 129                         .getName(), newColumn.getDefaultValue()));
 130     }
 131
 132     /**
 133      * Write sql statements for UNIQUE, NOT NULL and FOREIGN KEY constraints.
 134      */
 135     private StringBuilder generateAlterStatementsForAllOtherConstraints(List<Column> columns) {
 136         StringBuilder storeOtherAlterStatements = new StringBuilder();
 137
 138         columns.stream().flatMap(newColumn -> newColumn.getPostgresConstraints().stream()).filter(
 139                 constraint -> !(constraint instanceof PrimaryKeyConstraint)).forEach(constraint -> storeOtherAlterStatements
 140                         .append(generateConstraintStatement(constraint)));
 141
 142         return storeOtherAlterStatements;
 143     }
 144
 145     private StringBuilder generateAlterStatementsForPrimaryKeyConstraints(List<Column> columns) {
 146         StringBuilder storePKAlterStatements = new StringBuilder();
 147
 148         columns.stream().flatMap(newColumn -> newColumn.getPostgresConstraints().stream()).filter(
 149                 PrimaryKeyConstraint.class::isInstance).forEach(constraint -> storePKAlterStatements.append(
 150                         generateConstraintStatement(constraint)));
 151
 152         return storePKAlterStatements;
 153     }
 154
 155     private String generateConstraintStatement(PostgresConstraint postgresConstraint) {
 156         String constraintSql = generateConstraintSql(postgresConstraint);
 157         return String.format("SELECT ties_data.create_constraint_if_not_exists(%n\t'%s',%n '%s',%n '%s;'%n);%n%n",
 158                 postgresConstraint.getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), constraintSql);
 159     }
 160
 161     private String generateConstraintSql(PostgresConstraint postgresConstraint) {
 162         if (postgresConstraint instanceof PrimaryKeyConstraint) {
 163             return String.format(ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "PRIMARY KEY (\"%s\")", postgresConstraint
 164                     .getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), postgresConstraint
 165                             .getColumnToAddConstraintTo());
 166         } else if (postgresConstraint instanceof ForeignKeyConstraint) {
 167             return String.format(
 168                     ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "FOREIGN KEY (\"%s\") REFERENCES ties_data.\"%s\" (id) ON DELETE CASCADE",
 169                     postgresConstraint.getTableToAddConstraintTo(), postgresConstraint.getConstraintName(),
 170                     postgresConstraint.getColumnToAddConstraintTo(), ((ForeignKeyConstraint) postgresConstraint)
 171                             .getReferencedTable());
 172         } else if (postgresConstraint instanceof UniqueConstraint) {
 173             return String.format(ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "UNIQUE (\"%s\")", postgresConstraint
 174                     .getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), postgresConstraint
 175                             .getColumnToAddConstraintTo());
 176         } else if (postgresConstraint instanceof NotNullConstraint) {
 177             return String.format(ALTER_TABLE_TIES_DATA_S_ADD_CONSTRAINT_S + "NOT NULL (\"%s\")", postgresConstraint
 178                     .getTableToAddConstraintTo(), postgresConstraint.getConstraintName(), postgresConstraint
 179                             .getColumnToAddConstraintTo());
 180         } else {
 181             return "";
 182         }
 183     }
 184
 185     /**
 186      * Generates SQL statements for ALTER TABLE from mapped entity attributes.
 187      */
 188     private StringBuilder generateAlterStatementsFromDifferences(List<Table> tables) {
 189         StringBuilder storeSchemaForAlterStatements = new StringBuilder();
 190         StringBuilder storeAlterStatementsForPrimaryKeyConstraints = new StringBuilder();
 191         StringBuilder storeAlterStatementsForAllOtherConstraints = new StringBuilder();
 192         for (Table table : tables) {
 193             storeSchemaForAlterStatements.append(generateAlterStatements(table.getColumns(), table.getName()));
 194             storeAlterStatementsForPrimaryKeyConstraints.append(generateAlterStatementsForPrimaryKeyConstraints(table
 195                     .getColumns()));
 196             storeAlterStatementsForAllOtherConstraints.append(generateAlterStatementsForAllOtherConstraints(table
 197                     .getColumns()));
 198         }
 199         return storeSchemaForAlterStatements.append(storeAlterStatementsForPrimaryKeyConstraints).append(
 200                 storeAlterStatementsForAllOtherConstraints);
 201     }
 202
 203     /**
 204      * Generates SQL statements for altering tables based on mapped entity attributes.
 205      */
 206     private StringBuilder generateAlterStatements(List<Column> columns, String tableName) {
 207
 208         StringBuilder storeSchema = new StringBuilder();
 209         for (Column newColumn : columns) {
 210             if (newColumn.getName().equals("geo-location")) {
 211                 newColumn.setDataType("\"geography\"");
 212             }
 213             storeSchema.append(generateAlterTableStatements(newColumn, tableName));
 214         }
 215         return storeSchema;
 216     }
 217
 218     /**
 219      * Generates ALTER SQL statements to add new default values in newly identified columns.
 220      */
 221     private StringBuilder generateDefaultStatementsFromDifferences(List<Table> tables) {
 222         StringBuilder storeSchemaForDefaultStatements = new StringBuilder();
 223         for (Table table : tables) {
 224             StringBuilder storeSchema = new StringBuilder();
 225             for (Column newColumn : table.getColumns()) {
 226                 if (newColumn.getDefaultValue() != null) {
 227                     storeSchema.append(generateDefaultValueStatements(newColumn, table.getName()));
 228                 }
 229             }
 230             storeSchemaForDefaultStatements.append(storeSchema);
 231         }
 232         return storeSchemaForDefaultStatements;
 233     }
 234
 235     /**
 236      * Generate ALTER sql statements for attributes who have no constraints, default value or enums defined.
 237      */
 238     private StringBuilder generateAlterTableStatements(Column newColumn, String tableName) {
 239         return new StringBuilder(String.format(ALTER_TABLE_TIES_DATA_S + "ADD COLUMN IF NOT EXISTS \"%s\" %s;%n%n",
 240                 tableName, newColumn.getName(), newColumn.getDataType()));
 241     }
 242 }