@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;
}