@Override
  protected void configureColumnType(Column column, ResultSet rs) throws SQLException {
    if (integerList.contains(column.getTable().getName() + "." + column.getName())) {
      column.setDataType(Types.INTEGER);
    } else {
      column.setDataType(rs.getInt("DATA_TYPE"));
    }
    column.setColumnSize(rs.getInt("COLUMN_SIZE"));
    column.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));

    // Set true, if precision should be initialize
    column.setInitPrecision(
        !((column.getDataType() == Types.DECIMAL
                || column.getDataType() == Types.NUMERIC
                || column.getDataType() == Types.REAL)
            && rs.getString("DECIMAL_DIGITS") == null));
  }
예제 #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
  @Override
  protected DatabaseObject snapshotObject(DatabaseObject example, DatabaseSnapshot snapshot)
      throws DatabaseException, InvalidExampleException {
    Database database = snapshot.getDatabase();
    Relation relation = ((Column) example).getRelation();
    if (((Column) example).getComputed() != null && ((Column) example).getComputed()) {
      return example;
    }
    Schema schema = relation.getSchema();

    List<CachedRow> columnMetadataRs = null;
    try {

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

      columnMetadataRs =
          databaseMetaData.getColumns(
              ((AbstractJdbcDatabase) database).getJdbcCatalogName(schema),
              ((AbstractJdbcDatabase) database).getJdbcSchemaName(schema),
              relation.getName(),
              example.getName());

      if (columnMetadataRs.size() > 0) {
        CachedRow data = columnMetadataRs.get(0);
        Column column = readColumn(data, relation, database);

        if (column != null
            && database instanceof MSSQLDatabase
            && database.getDatabaseMajorVersion() >= 8) {
          String sql;
          if (database.getDatabaseMajorVersion() >= 9) {
            // SQL Server 2005 or later
            // https://technet.microsoft.com/en-us/library/ms177541.aspx
            sql =
                "SELECT CAST([ep].[value] AS [nvarchar](MAX)) AS [REMARKS] "
                    + "FROM [sys].[extended_properties] AS [ep] "
                    + "WHERE [ep].[class] = 1 "
                    + "AND [ep].[major_id] = OBJECT_ID(N'"
                    + database.escapeStringForDatabase(
                        database.escapeTableName(
                            schema.getCatalogName(), schema.getName(), relation.getName()))
                    + "') "
                    + "AND [ep].[minor_id] = COLUMNPROPERTY([ep].[major_id], N'"
                    + database.escapeStringForDatabase(column.getName())
                    + "', 'ColumnId') "
                    + "AND [ep].[name] = 'MS_Description'";
          } else {
            // SQL Server 2000
            // https://technet.microsoft.com/en-us/library/aa224810%28v=sql.80%29.aspx
            sql =
                "SELECT CAST([p].[value] AS [ntext]) AS [REMARKS] "
                    + "FROM [dbo].[sysproperties] AS [p] "
                    + "WHERE [p].[id] = OBJECT_ID(N'"
                    + database.escapeStringForDatabase(
                        database.escapeTableName(
                            schema.getCatalogName(), schema.getName(), relation.getName()))
                    + "') "
                    + "AND [p].[smallid] = COLUMNPROPERTY([p].[id], N'"
                    + database.escapeStringForDatabase(column.getName())
                    + "', 'ColumnId') "
                    + "AND [p].[type] = 4 "
                    + "AND [p].[name] = 'MS_Description'";
          }

          List<String> remarks =
              ExecutorService.getInstance()
                  .getExecutor(snapshot.getDatabase())
                  .queryForList(new RawSqlStatement(sql), String.class);
          if (remarks != null && remarks.size() > 0) {
            column.setRemarks(StringUtils.trimToNull(remarks.iterator().next()));
          }
        }

        return column;
      } else {
        return null;
      }
    } catch (Exception e) {
      throw new DatabaseException(e);
    }
  }