@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}); }
/** * 事实表和关联报表属于当前传入数组的交叉报表 * * @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; }