예제 #1
0
  @Override
  protected void addTo(DatabaseObject foundObject, DatabaseSnapshot snapshot)
      throws DatabaseException, InvalidExampleException {
    if (!snapshot.getSnapshotControl().shouldInclude(Column.class)) {
      return;
    }
    if (foundObject instanceof Relation) {
      Database database = snapshot.getDatabase();
      Relation relation = (Relation) foundObject;
      List<CachedRow> allColumnsMetadataRs = null;
      try {

        JdbcDatabaseSnapshot.CachingDatabaseMetaData databaseMetaData =
            ((JdbcDatabaseSnapshot) snapshot).getMetaData();

        Schema schema;

        schema = relation.getSchema();
        allColumnsMetadataRs =
            databaseMetaData.getColumns(
                ((AbstractJdbcDatabase) database).getJdbcCatalogName(schema),
                ((AbstractJdbcDatabase) database).getJdbcSchemaName(schema),
                relation.getName(),
                null);

        for (CachedRow row : allColumnsMetadataRs) {
          Column exampleColumn =
              new Column()
                  .setRelation(relation)
                  .setName(StringUtils.trimToNull(row.getString("COLUMN_NAME")));
          relation.getColumns().add(exampleColumn);
        }
      } catch (Exception e) {
        throw new DatabaseException(e);
      }
    }
  }
예제 #2
0
  protected DataType readDataType(
      CachedRow columnMetadataResultSet, Column column, Database database) throws SQLException {

    if (database instanceof OracleDatabase) {
      String dataType = columnMetadataResultSet.getString("DATA_TYPE");
      dataType = dataType.replace("VARCHAR2", "VARCHAR");
      dataType = dataType.replace("NVARCHAR2", "NVARCHAR");

      DataType type = new DataType(dataType);
      //            type.setDataTypeId(dataType);
      if (dataType.equalsIgnoreCase("NUMBER")) {
        type.setColumnSize(columnMetadataResultSet.getInt("DATA_PRECISION"));
        //                if (type.getColumnSize() == null) {
        //                    type.setColumnSize(38);
        //                }
        type.setDecimalDigits(columnMetadataResultSet.getInt("DATA_SCALE"));
        //                if (type.getDecimalDigits() == null) {
        //                    type.setDecimalDigits(0);
        //                }
        //            type.setRadix(10);
      } else {
        type.setColumnSize(columnMetadataResultSet.getInt("DATA_LENGTH"));

        if (dataType.equalsIgnoreCase("NCLOB")
            || dataType.equalsIgnoreCase("BLOB")
            || dataType.equalsIgnoreCase("CLOB")) {
          type.setColumnSize(null);
        } else if (dataType.equalsIgnoreCase("NVARCHAR") || dataType.equalsIgnoreCase("NCHAR")) {
          type.setColumnSize(columnMetadataResultSet.getInt("CHAR_LENGTH"));
          type.setColumnSizeUnit(DataType.ColumnSizeUnit.CHAR);
        } else {
          String charUsed = columnMetadataResultSet.getString("CHAR_USED");
          DataType.ColumnSizeUnit unit = null;
          if ("C".equals(charUsed)) {
            unit = DataType.ColumnSizeUnit.CHAR;
            type.setColumnSize(columnMetadataResultSet.getInt("CHAR_LENGTH"));
          }
          type.setColumnSizeUnit(unit);
        }
      }

      return type;
    }

    String columnTypeName = (String) columnMetadataResultSet.get("TYPE_NAME");

    if (database instanceof FirebirdDatabase) {
      if (columnTypeName.equals("BLOB SUB_TYPE 0")) {
        columnTypeName = "BLOB";
      }
      if (columnTypeName.equals("BLOB SUB_TYPE 1")) {
        columnTypeName = "CLOB";
      }
    }

    if (database instanceof MySQLDatabase
        && (columnTypeName.equalsIgnoreCase("ENUM") || columnTypeName.equalsIgnoreCase("SET"))) {
      try {
        String boilerLength;
        if (columnTypeName.equalsIgnoreCase("ENUM")) boilerLength = "7";
        else // SET
        boilerLength = "6";
        List<String> enumValues =
            ExecutorService.getInstance()
                .getExecutor(database)
                .queryForList(
                    new RawSqlStatement(
                        "SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(COLUMN_TYPE, "
                            + boilerLength
                            + ", LENGTH(COLUMN_TYPE) - "
                            + boilerLength
                            + " - 1 ), \"','\", 1 + units.i + tens.i * 10) , \"','\", -1)\n"
                            + "FROM INFORMATION_SCHEMA.COLUMNS\n"
                            + "CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units\n"
                            + "CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens\n"
                            + "WHERE TABLE_NAME = '"
                            + column.getRelation().getName()
                            + "' \n"
                            + "AND COLUMN_NAME = '"
                            + column.getName()
                            + "'"),
                    String.class);
        String enumClause = "";
        for (String enumValue : enumValues) {
          enumClause += "'" + enumValue + "', ";
        }
        enumClause = enumClause.replaceFirst(", $", "");
        return new DataType(columnTypeName + "(" + enumClause + ")");
      } catch (DatabaseException e) {
        LogFactory.getLogger().warning("Error fetching enum values", e);
      }
    }
    DataType.ColumnSizeUnit columnSizeUnit = DataType.ColumnSizeUnit.BYTE;

    int dataType = columnMetadataResultSet.getInt("DATA_TYPE");
    Integer columnSize = null;
    Integer decimalDigits = null;
    if (!database.dataTypeIsNotModifiable(
        columnTypeName)) { // don't set size for types like int4, int8 etc
      columnSize = columnMetadataResultSet.getInt("COLUMN_SIZE");
      decimalDigits = columnMetadataResultSet.getInt("DECIMAL_DIGITS");
      if (decimalDigits != null && decimalDigits.equals(0)) {
        decimalDigits = null;
      }
    }

    Integer radix = columnMetadataResultSet.getInt("NUM_PREC_RADIX");

    Integer characterOctetLength = columnMetadataResultSet.getInt("CHAR_OCTET_LENGTH");

    if (database instanceof DB2Database) {
      String typeName = columnMetadataResultSet.getString("TYPE_NAME");
      if (typeName.equalsIgnoreCase("DBCLOB")
          || typeName.equalsIgnoreCase("GRAPHIC")
          || typeName.equalsIgnoreCase("VARGRAPHIC")) {
        if (columnSize != null) {
          columnSize = columnSize / 2; // Stored as double length chars
        }
      }
    }

    DataType type = new DataType(columnTypeName);
    type.setDataTypeId(dataType);
    type.setColumnSize(columnSize);
    type.setDecimalDigits(decimalDigits);
    type.setRadix(radix);
    type.setCharacterOctetLength(characterOctetLength);
    type.setColumnSizeUnit(columnSizeUnit);

    return type;
  }
예제 #3
0
  protected Column readColumn(CachedRow columnMetadataResultSet, Relation table, Database database)
      throws SQLException, DatabaseException {
    String rawTableName = (String) columnMetadataResultSet.get("TABLE_NAME");
    String rawColumnName = (String) columnMetadataResultSet.get("COLUMN_NAME");
    String rawSchemaName =
        StringUtils.trimToNull((String) columnMetadataResultSet.get("TABLE_SCHEM"));
    String rawCatalogName =
        StringUtils.trimToNull((String) columnMetadataResultSet.get("TABLE_CAT"));
    String remarks = StringUtils.trimToNull((String) columnMetadataResultSet.get("REMARKS"));
    if (remarks != null) {
      remarks = remarks.replace("''", "'"); // come back escaped sometimes
    }

    Column column = new Column();
    column.setName(StringUtils.trimToNull(rawColumnName));
    column.setRelation(table);
    column.setRemarks(remarks);

    if (database instanceof OracleDatabase) {
      String nullable = columnMetadataResultSet.getString("NULLABLE");
      if (nullable.equals("Y")) {
        column.setNullable(true);
      } else {
        column.setNullable(false);
      }
    } else {
      int nullable = columnMetadataResultSet.getInt("NULLABLE");
      if (nullable == DatabaseMetaData.columnNoNulls) {
        column.setNullable(false);
      } else if (nullable == DatabaseMetaData.columnNullable) {
        column.setNullable(true);
      } else if (nullable == DatabaseMetaData.columnNullableUnknown) {
        LogFactory.getLogger()
            .info("Unknown nullable state for column " + column.toString() + ". Assuming nullable");
        column.setNullable(true);
      }
    }

    if (database.supportsAutoIncrement()) {
      if (table instanceof Table) {
        if (columnMetadataResultSet.containsColumn("IS_AUTOINCREMENT")) {
          String isAutoincrement = (String) columnMetadataResultSet.get("IS_AUTOINCREMENT");
          isAutoincrement = StringUtils.trimToNull(isAutoincrement);
          if (isAutoincrement == null) {
            column.setAutoIncrementInformation(null);
          } else if (isAutoincrement.equals("YES")) {
            column.setAutoIncrementInformation(new Column.AutoIncrementInformation());
          } else if (isAutoincrement.equals("NO")) {
            column.setAutoIncrementInformation(null);
          } else if (isAutoincrement.equals("")) {
            LogFactory.getLogger()
                .info(
                    "Unknown auto increment state for column "
                        + column.toString()
                        + ". Assuming not auto increment");
            column.setAutoIncrementInformation(null);
          } else {
            throw new UnexpectedLiquibaseException(
                "Unknown is_autoincrement value: '" + isAutoincrement + "'");
          }
        } else {
          // probably older version of java, need to select from the column to find out if it is
          // auto-increment
          String selectStatement;
          if (database.getDatabaseProductName().startsWith("DB2 UDB for AS/400")) {
            selectStatement =
                "select "
                    + database.escapeColumnName(
                        rawCatalogName, rawSchemaName, rawTableName, rawColumnName)
                    + " from "
                    + rawSchemaName
                    + "."
                    + rawTableName
                    + " where 0=1";
            LogFactory.getLogger().debug("rawCatalogName : <" + rawCatalogName + ">");
            LogFactory.getLogger().debug("rawSchemaName : <" + rawSchemaName + ">");
            LogFactory.getLogger().debug("rawTableName : <" + rawTableName + ">");
            LogFactory.getLogger().debug("raw selectStatement : <" + selectStatement + ">");

          } else {
            selectStatement =
                "select "
                    + database.escapeColumnName(
                        rawCatalogName, rawSchemaName, rawTableName, rawColumnName)
                    + " from "
                    + database.escapeTableName(rawCatalogName, rawSchemaName, rawTableName)
                    + " where 0=1";
          }
          LogFactory.getLogger()
              .debug(
                  "Checking "
                      + rawTableName
                      + "."
                      + rawCatalogName
                      + " for auto-increment with SQL: '"
                      + selectStatement
                      + "'");
          Connection underlyingConnection =
              ((JdbcConnection) database.getConnection()).getUnderlyingConnection();
          Statement statement = null;
          ResultSet columnSelectRS = null;

          try {
            statement = underlyingConnection.createStatement();
            columnSelectRS = statement.executeQuery(selectStatement);
            if (columnSelectRS.getMetaData().isAutoIncrement(1)) {
              column.setAutoIncrementInformation(new Column.AutoIncrementInformation());
            } else {
              column.setAutoIncrementInformation(null);
            }
          } finally {
            try {
              if (statement != null) {
                statement.close();
              }
            } catch (SQLException ignore) {
            }
            if (columnSelectRS != null) {
              columnSelectRS.close();
            }
          }
        }
      }
    }

    DataType type = readDataType(columnMetadataResultSet, column, database);
    column.setType(type);

    column.setDefaultValue(readDefaultValue(columnMetadataResultSet, column, database));

    return column;
  }