@Override
  protected void readColumns(
      DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
      throws SQLException, DatabaseException {
    findIntegerColumns(snapshot, schema);
    super.readColumns(snapshot, schema, databaseMetaData);

    /*
     * Code Description:
     * Finding all 'tablespace' attributes of column's PKs
     * */
    Database database = snapshot.getDatabase();
    Statement statement = null;
    ResultSet rs = null;
    try {
      statement =
          ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();

      // Setting default schema name. Needed for correct statement generation
      if (schema == null) schema = database.convertRequestedSchemaToSchema(schema);

      String query =
          "select ui.tablespace_name TABLESPACE, ucc.table_name TABLE_NAME, ucc.column_name COLUMN_NAME FROM all_indexes ui , all_constraints uc , all_cons_columns ucc where uc.constraint_type = 'P' and ucc.constraint_name = uc.constraint_name and uc.index_name = ui.index_name and uc.owner = '"
              + schema
              + "' and ui.table_owner = '"
              + schema
              + "' and ucc.owner = '"
              + schema
              + "'";
      rs = statement.executeQuery(query);

      while (rs.next()) {
        Column column = snapshot.getColumn(rs.getString("TABLE_NAME"), rs.getString("COLUMN_NAME"));
        // setting up tablespace property to column, to configure it's PK-index
        if (column == null) {
          continue; // probably a different schema
        }
        column.setTablespace(rs.getString("TABLESPACE"));
      }
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException ignore) {
        }
      }
      if (statement != null) {
        try {
          statement.close();
        } catch (SQLException ignore) {
        }
      }
    }
  }
  protected Object readDefaultValue(
      CachedRow columnMetadataResultSet, Column columnInfo, Database database)
      throws SQLException, DatabaseException {
    if (database instanceof MSSQLDatabase) {
      Object defaultValue = columnMetadataResultSet.get("COLUMN_DEF");

      if (defaultValue != null && defaultValue instanceof String) {
        if (defaultValue.equals("(NULL)")) {
          columnMetadataResultSet.set("COLUMN_DEF", null);
        }
      }
    }

    if (database instanceof OracleDatabase) {
      if (columnMetadataResultSet.get("COLUMN_DEF") == null) {
        columnMetadataResultSet.set("COLUMN_DEF", columnMetadataResultSet.get("DATA_DEFAULT"));

        if (columnMetadataResultSet.get("COLUMN_DEF") != null
            && ((String) columnMetadataResultSet.get("COLUMN_DEF")).equalsIgnoreCase("NULL")) {
          columnMetadataResultSet.set("COLUMN_DEF", null);
        }

        Object columnDef = columnMetadataResultSet.get("COLUMN_DEF");
        if (columnInfo.getType().getTypeName().equalsIgnoreCase("CHAR")
            && columnDef instanceof String
            && !((String) columnDef).startsWith("'")
            && !((String) columnDef).endsWith("'")) {
          return new DatabaseFunction((String) columnDef);
        }

        if (columnMetadataResultSet.get("VIRTUAL_COLUMN").equals("YES")) {
          Object column_def = columnMetadataResultSet.get("COLUMN_DEF");
          if (column_def != null && !column_def.equals("null")) {
            columnMetadataResultSet.set("COLUMN_DEF", "GENERATED ALWAYS AS (" + column_def + ")");
          }
        }
      }
    }

    return SqlUtil.parseValue(
        database, columnMetadataResultSet.get("COLUMN_DEF"), columnInfo.getType());
  }
  @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));
  }
  /** Oracle specific implementation */
  @Override
  protected void getColumnTypeAndDefValue(Column columnInfo, ResultSet rs, Database database)
      throws SQLException, DatabaseException {
    super.getColumnTypeAndDefValue(columnInfo, rs, database);

    // Exclusive setting for oracle INTEGER type
    // Details:
    // INTEGER means NUMBER type with 'data_precision IS NULL and scale = 0'
    if (columnInfo.getDataType() == Types.INTEGER) {
      columnInfo.setTypeName("INTEGER");
    }

    String columnTypeName = rs.getString("TYPE_NAME");
    if ("VARCHAR2".equals(columnTypeName)) {
      int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");
      int columnSize = rs.getInt("COLUMN_SIZE");
      if (columnSize == charOctetLength) {
        columnInfo.setLengthSemantics(Column.LengthSemantics.BYTE);
      } else {
        columnInfo.setLengthSemantics(Column.LengthSemantics.CHAR);
      }
    }
  }
  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;
  }
  @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);
    }
  }
  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;
  }
  private void jbInit() throws Exception {
    dbAlias = com.limosys.dbaccess.Connection.getAddress(dbAlias);
    dbAlias.setTransactionIsolation(java.sql.Connection.TRANSACTION_READ_COMMITTED);
    dbAlias.setUseStatementCaching(false);

    pRowSelect.addColumn("ID", Variant.INT);

    colId.setColumnName("ID");
    colId.setServerColumnName("ID");
    colId.setTableName("T_6467063785");
    colId.setRowId(true);
    colId.setDataType(Variant.INT);
    colId.setSqlType(4);
    colName.setColumnName("NAME");
    colName.setServerColumnName("NAME");
    colName.setTableName("T_6467063785");
    colName.setDataType(Variant.STRING);
    colName.setPrecision(255);
    colName.setSqlType(12);
    colCompanyName.setColumnName("COMPANY_NAME");
    colCompanyName.setServerColumnName("COMPANY_NAME");
    colCompanyName.setTableName("T_6467063785");
    colCompanyName.setDataType(Variant.STRING);
    colCompanyName.setPrecision(255);
    colCompanyName.setSqlType(12);
    prT_6467063785.setDatabase(dbAlias);
    // prT_6467063785.setInsertProcedure(new ProcedureDescriptor(dbAlias, "exec
    // lsp_T_6467063785_INotNull_v2  :ID, :NAME, :COMPANY_NAME", pdsT_6467063785, true, Load.ALL));
    prT_6467063785.setDeleteProcedure(
        new ProcedureDescriptor(
            dbAlias, "exec lsp_T_6467063785_D_v2  :ID", pdsT_6467063785, true, Load.ALL));
    pdsT_6467063785.setMetaDataUpdate(MetaDataUpdate.NONE);
    pdsT_6467063785.setResolver(prT_6467063785);
    pdsT_6467063785.setSchemaName("");
    pdsT_6467063785.setProcedure(
        new ProcedureDescriptor(
            dbAlias, "exec lsp_T_6467063785_SRow_v2 :ID", pRowSelect, true, Load.ALL));
    pdsT_6467063785.setColumns(new Column[] {colId, colName, colCompanyName});
  }
Example #9
0
  /**
   * 事实表和关联报表属于当前传入数组的交叉报表
   *
   * @param request
   * @param tables elements are table.id
   * @return elements are ArrayList, first is cxtab id, second is cxtab name
   */
  public List getCxtabs(HttpServletRequest request, List<Integer> tables) {
    TableManager manager = TableManager.getInstance();
    UserWebImpl userWeb =
        ((UserWebImpl)
            WebUtils.getSessionContextManager(request.getSession())
                .getActor(nds.util.WebKeys.USER));
    StringBuffer sb = new StringBuffer();
    for (int i = 0; i < tables.size(); i++) {
      // Table t= tables.get(i);
      if (i > 0) sb.append(",");
      sb.append(tables.get(i));
    }
    String ts = sb.toString();
    try {
      Table cxtabTable = manager.getTable("AD_CXTAB");
      QueryRequestImpl queryData;
      // only pk,dk will be selected, order by ak asc
      queryData = QueryEngine.getInstance().createRequest(userWeb.getSession());
      queryData.setMainTable(cxtabTable.getId());

      queryData.addSelection(cxtabTable.getPrimaryKey().getId());
      queryData.addSelection(cxtabTable.getDisplayKey().getId());

      Column colOrderNo = cxtabTable.getColumn("orderno");
      queryData.setOrderBy(new int[] {colOrderNo.getId()}, true);
      queryData.setRange(0, Integer.MAX_VALUE);

      Expression expr =
          new Expression(
              null,
              "(AD_CXTAB.AD_TABLE_ID in ("
                  + ts
                  + ") or exists (select 1 from ad_cxtab_reftable r where r.ad_cxtab_id=AD_CXTAB.id and r.ad_table_id in ("
                  + ts
                  + ")))",
              null);

      // set reporttype to "S"
      expr =
          expr.combine(
              new Expression(new ColumnLink("AD_CXTAB.REPORTTYPE"), "=S", null),
              SQLCombination.SQL_AND,
              null);
      expr =
          expr.combine(
              new Expression(new ColumnLink("AD_CXTAB.ISACTIVE"), "=Y", null),
              SQLCombination.SQL_AND,
              null);
      expr =
          expr.combine(
              new Expression(new ColumnLink("AD_CXTAB.ISPUBLIC"), "=Y", null),
              SQLCombination.SQL_AND,
              null);
      expr =
          expr.combine(
              userWeb.getSecurityFilter(cxtabTable.getName(), 1), SQLCombination.SQL_AND, null);
      queryData.addParam(expr); // read permission

      return QueryEngine.getInstance().doQueryList(queryData.toSQL());
    } catch (Throwable t) {
      logger.error(
          "Fail to load reports for user " + userWeb.getUserId() + " with table ids: " + ts, t);
    }
    return Collections.EMPTY_LIST;
  }