content: /*
   * Copyright (C) 2012-2023 52°North Spatial Information Research GmbH
   *
   * This program is free software; you can redistribute it and/or modify it
   * under the terms of the GNU General Public License version 2 as published
   * by the Free Software Foundation.
   *
   * If the program is linked with libraries which are licensed under one of
   * the following licenses, the combination of the program with the linked
   * library is not considered a "derivative work" of the program:
   *
   *     - Apache License, version 2.0
   *     - Apache Software License, version 1.0
   *     - GNU Lesser General Public License, version 3
   *     - Mozilla Public License, versions 1.0, 1.1 and 2.0
   *     - Common Development and Distribution License (CDDL), version 1.0
   *
   * Therefore the distribution of the program linked with libraries licensed
   * under the aforementioned licenses, is permitted by the copyright holders
   * if the distribution is compliant with both the GNU General Public
   * License version 2 and the aforementioned licenses.
   *
   * This program is distributed in the hope that it will be useful, but
   * WITHOUT ANY WARRANTY; without even the implied warranty of
   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
   * Public License for more details.
   */
  package org.n52.sos.ds.datasource;
  
  import java.util.ArrayList;
  import java.util.HashSet;
  import java.util.List;
  import java.util.Map;
  import java.util.Set;
  
  import com.google.common.base.Strings;
  
  /**
   * MS SQL Server datasource
   *
   * @author <a href="mailto:c.hollmann@52north.org">Carsten Hollmann</a>
   * @since 4.2.0
   *
   */
  public class SqlServerDatasource extends AbstractSqlServerDatasource {
  
      private static final String TN_FEATURE_OF_INTEREST = "feature";
  
      private static final String TN_OBSERVATION = "observation";
  
      private static final String TN_SERIES = "series";
  
      private static final String CN_IDENTIFIER = "identifier";
  
      private static final String CN_FROM_LEVEL_VALUE = "from_level";
  
      private static final String CN_TO_LEVEL_VALUE = "to_level";
  
      private static final String CN_URL = "url";
  
      private static final String DECLARE_VARIABLE = "DECLARE @ObjectName NVARCHAR(100);";
  
      private static final String DIALECT_NAME = "SQL Server";
  
      public SqlServerDatasource() {
          super();
      }
  
      @Override
      public String getDialectName() {
          return DIALECT_NAME;
      }
  
      @Override
      public boolean isPostCreateSchema() {
          return true;
      }
  
      @Override
      public void executePostCreateSchema(Map<String, Object> databaseSettings) {
          List<String> statements = new ArrayList<>();
          for (TableColumn tableColumn : getTableColumns(databaseSettings)) {
              statements.add(getGetAndDropConstraint(tableColumn.getTable(), tableColumn.getColumn(), databaseSettings));
              statements
                      .add(getCreateUniqueConstraint(databaseSettings, tableColumn.getTable(), tableColumn.getColumn()));
              execute(statements.toArray(new String[statements.size()]), databaseSettings);
              statements.clear();
          }
      }
  
      private String getGetAndDropConstraint(String table, String column, Map<String, Object> databaseSettings) {
          StringBuilder builder = new StringBuilder();
          builder.append(DECLARE_VARIABLE);
          builder.append(getSelectConstraintNameToVariable(table, column));
          builder.append(getExecuteDropConstraint(table, databaseSettings));
          return builder.toString();
      }
  
      private Set<TableColumn> getTableColumns(Map<String, Object> s) {
          Set<TableColumn> tableColumns = new HashSet<>();
          tableColumns.add(new TableColumn(TN_FEATURE_OF_INTEREST, CN_IDENTIFIER));
          tableColumns.add(new TableColumn(TN_FEATURE_OF_INTEREST, CN_URL));
          tableColumns.add(new TableColumn(TN_OBSERVATION, CN_IDENTIFIER));
          tableColumns.add(new TableColumn(TN_OBSERVATION, CN_FROM_LEVEL_VALUE));
          tableColumns.add(new TableColumn(TN_OBSERVATION, CN_TO_LEVEL_VALUE));
          tableColumns.add(new TableColumn(TN_SERIES, CN_IDENTIFIER));
          return tableColumns;
      }
  
      private String getSelectConstraintNameToVariable(String table, String colum) {
          StringBuilder builder = new StringBuilder();
          builder.append("SELECT @ObjectName = ccu.CONSTRAINT_NAME ");
          builder.append("FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu, INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ");
          builder.append("WHERE ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME ");
          builder.append("AND ccu.TABLE_NAME=").append("'").append(table).append("'");
          builder.append(" AND ccu.COLUMN_NAME=").append("'").append(colum).append("';");
          return builder.toString();
      }
  
      private String getExecuteDropConstraint(String table, Map<String, Object> databaseSettings) {
          StringBuilder builder = new StringBuilder();
          builder.append("IF (OBJECT_ID(@ObjectName, 'UQ') IS NOT NULL) ");
          builder.append("BEGIN ");
          builder.append("EXEC('ALTER TABLE ");
          builder.append(getQualifiedTable(getDatabase(databaseSettings), getSchema(databaseSettings), table));
          builder.append(" DROP CONSTRAINT ' + @ObjectName); ");
          builder.append("END ");
          return builder.toString();
      }
  
      private String getCreateUniqueConstraint(Map<String, Object> databaseSettings, String table, String column) {
          StringBuilder builder = new StringBuilder();
          builder.append("CREATE UNIQUE NONCLUSTERED INDEX ").append(table).append("_").append(column);
          builder.append(" ON ")
                  .append(getQualifiedTable(getDatabase(databaseSettings), getSchema(databaseSettings), table))
                  .append("(").append(column).append(")");
          builder.append("WHERE ").append(column).append(" IS NOT NULL");
          return builder.toString();
      }
  
      private String getQualifiedTable(String database, String schema, String table) {
          StringBuilder builder = new StringBuilder();
          if (!Strings.isNullOrEmpty(database)) {
              builder.append(database).append(".");
          }
          if (!Strings.isNullOrEmpty(schema)) {
              builder.append(schema);
          }
          builder.append(table);
          return builder.toString();
      }
  
      protected String getDatabase(Map<String, Object> settings) {
          if (isSetSchema(settings)) {
              return (String) settings.get(DATABASE_KEY);
          }
          return "";
      }
  
      private static class TableColumn {
  
          private String table;
  
          private String column;
  
          TableColumn(String table, String column) {
              setTable(table);
              setColumn(column);
          }
  
          /**
           * @return the table
           */
          public String getTable() {
              return table;
          }
  
          /**
           * @param table
           *            the table to set
           */
          private void setTable(String table) {
              this.table = table;
          }
  
          /**
           * @return the column
           */
          public String getColumn() {
              return column;
          }
  
          /**
           * @param column
           *            the column to set
           */
          public void setColumn(String column) {
              this.column = column;
          }
      }
  }
  