@Test
public void testDetectUpdateTable2() throws Exception {
WbConnection con = PostgresTestUtil.getPostgresConnection();
assertNotNull(con);
TestUtil.executeScript(con, "set search_path=path_2,path3,path_1");
Statement stmt = null;
ResultSet rs = null;
try {
stmt = con.createStatement();
String sql = "select * from t2";
rs = stmt.executeQuery(sql);
DataStore ds1 = new DataStore(rs, con);
SqlUtil.closeResult(rs);
ds1.setGeneratingSql(sql);
ds1.checkUpdateTable(con);
TableIdentifier tbl1 = ds1.getUpdateTable();
assertNotNull(tbl1);
assertEquals("path_2", tbl1.getSchema());
assertEquals("t2", tbl1.getTableName());
assertTrue(ds1.hasPkColumns());
List<ColumnIdentifier> missing = ds1.getMissingPkColumns();
assertTrue(CollectionUtil.isEmpty(missing));
} finally {
SqlUtil.closeStatement(stmt);
}
}
@Test
public void testGetErrorInfo() throws Exception {
WbConnection con = OracleTestUtil.getOracleConnection();
assertNotNull(con);
String sql = "create procedure nocando\n" + "as \n" + "begin \n" + " null; \n" + "ende;\n/\n";
TestUtil.executeScript(con, sql, DelimiterDefinition.DEFAULT_ORA_DELIMITER);
try {
con.setBusy(true);
OracleErrorInformationReader reader = new OracleErrorInformationReader(con);
ErrorDescriptor errorInfo = reader.getErrorInfo(null, "nocando", "procedure", true);
con.setBusy(false);
assertNotNull(errorInfo);
assertTrue(errorInfo.getErrorMessage().startsWith("Errors for PROCEDURE NOCANDO"));
assertTrue(errorInfo.getErrorMessage().contains("PLS-00103"));
assertEquals(4, errorInfo.getErrorLine());
assertEquals(4, errorInfo.getErrorColumn());
errorInfo = reader.getErrorInfo(null, "nocando", "procedure", false);
assertNotNull(errorInfo);
String msg = errorInfo.getErrorMessage();
assertFalse(msg.contains("Errors for PROCEDURE NOCANDO"));
assertTrue(msg.startsWith("L:5"));
} finally {
con.setBusy(false);
}
}
@Test
public void testMultipleSchemas() throws Exception {
TestUtil util = getTestUtil();
WbConnection con = util.getConnection();
TestUtil.executeScript(
con,
"create schema one;\n"
+ "create schema two;\n"
+ "create table public.foobar (id integer not null primary key, somedata varchar(50));\n"
+ "create table one.foobar (id_one integer not null primary key, somedata varchar(50));\n"
+ "create table two.foobar (id_two integer not null primary key, somedata varchar(50));\n"
+ "commit;");
String sql = "select id, somedata from foobar";
ResultInfo info = null;
try (Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
info = new ResultInfo(rs.getMetaData(), con);
}
UpdateTableDetector detector = new UpdateTableDetector(con);
detector.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("foobar");
detector.checkUpdateTable(toCheck, info);
TableIdentifier tbl = detector.getUpdateTable();
assertEquals("FOOBAR", tbl.getTableName());
assertEquals("PUBLIC", tbl.getSchema());
assertTrue(CollectionUtil.isEmpty(detector.getMissingPkColumns()));
}
@Override
public void selectionChanged(WbSelectionModel list) {
WbConnection conn = this.source.getConnection();
if (conn == null || conn.isSessionReadOnly()) {
setEnabled(false);
} else {
List<DbObject> objects = source.getSelectedObjects();
if (CollectionUtil.isEmpty(objects)) {
setEnabled(false);
return;
}
int colCount = 0;
int selCount = objects.size();
for (DbObject dbo : objects) {
if (dbo instanceof ColumnIdentifier) {
colCount++;
}
}
if (colCount > 0 && colCount == selCount) {
TableIdentifier tbl = source.getObjectTable();
setEnabled(tbl != null);
} else {
setEnabled(this.available && (colCount == 0 && selCount > 0));
}
}
}
@Test
public void testMissingPKColumns() throws Exception {
TestUtil util = getTestUtil();
WbConnection con = util.getConnection();
TestUtil.executeScript(
con,
"create table foobar (id1 integer not null, id2 integer not null, somedata varchar(50), primary key (id1, id2));\n"
+ "commit;");
String sql = "select id1, somedata from FOOBAR";
ResultInfo info = null;
try (Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
info = new ResultInfo(rs.getMetaData(), con);
}
UpdateTableDetector detector = new UpdateTableDetector(con);
detector.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("foobar");
detector.checkUpdateTable(toCheck, info);
TableIdentifier tbl = detector.getUpdateTable();
assertEquals("FOOBAR", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
List<ColumnIdentifier> cols = detector.getMissingPkColumns();
assertNotNull(cols);
assertEquals(1, cols.size());
assertEquals("ID2", cols.get(0).getColumnName());
}
@Test
public void testNotNullIndex() throws Exception {
TestUtil util = getTestUtil();
WbConnection conn = util.getConnection();
TestUtil.executeScript(
conn,
"create table person (id integer, id2 integer not null, name varchar(20) not null);\n"
+ "create unique index aaaa on person (id);\n"
+ "create unique index zzzz on person (id2);\n"
+ "commit;");
String sql = "select id, id2, name from person";
ResultInfo info = null;
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
info = new ResultInfo(rs.getMetaData(), conn);
}
UpdateTableDetector detector = new UpdateTableDetector(conn);
detector.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("person");
detector.checkUpdateTable(toCheck, info);
TableIdentifier tbl = detector.getUpdateTable();
assertEquals("PERSON", tbl.getTableName());
assertFalse(info.getColumn(0).isPkColumn());
assertTrue(info.getColumn(1).isPkColumn());
}
/**
* Searches all objects for the given search string(s).
*
* <p>If multiple search strings are given, the parameter matchAll defines if they all of them
* have to match or at least one
*
* @param searchValues the patterns to be searched in all object sources. This can be a regular
* expression
* @param matchAll if true all patterns must be found in a single source
* @param ignoreCase if false, the patterns must match exactly
* @return a list of objects where the searchvalue has been found
*/
public synchronized List<DbObject> searchObjects(
List<String> searchValues, boolean matchAll, boolean ignoreCase, boolean useRegex) {
searchedObjects.clear();
cancelSearch = false;
isRunning = true;
numSearched = 0;
try {
searchResult = CollectionUtil.sizedArrayList(50);
if (CollectionUtil.isEmpty(schemas)) {
schemas = CollectionUtil.arrayList(connection.getCurrentSchema());
}
if (CollectionUtil.isEmpty(names)) {
names = CollectionUtil.arrayList("%");
}
Set<String> typesToRetrieve = CollectionUtil.caseInsensitiveSet();
typesToRetrieve.addAll(types);
if (typeIncluded("trigger", typesToRetrieve)) {
List<DbObject> trigger = retrieveTriggers();
if (cancelSearch) return null;
typesToRetrieve.remove("trigger");
searchList(trigger, searchValues, matchAll, ignoreCase, useRegex);
}
if (cancelSearch) return null;
if (typeIncluded("procedure", typesToRetrieve) || typeIncluded("function", typesToRetrieve)) {
List<DbObject> procs = retrieveProcedures();
if (cancelSearch) return null;
typesToRetrieve.remove("procedure");
typesToRetrieve.remove("function");
searchList(procs, searchValues, matchAll, ignoreCase, useRegex);
}
if (cancelSearch) return null;
if (typesToRetrieve.size() > 0) {
if (typesToRetrieve.contains("*") || typesToRetrieve.contains("%")) {
typesToRetrieve.clear();
typesToRetrieve.addAll(connection.getMetadata().getObjectTypes());
}
List<DbObject> objects = retrieveObjects(typesToRetrieve);
searchList(objects, searchValues, matchAll, ignoreCase, useRegex);
}
} catch (SQLException sql) {
LogMgr.logError("ObjectSourceSearcher.searchObjects()", "Error retrieving objects", sql);
} finally {
isRunning = false;
}
return searchResult;
}
@Override
public int adjustTableSequences(
WbConnection connection, TableIdentifier table, boolean includeCommit) throws SQLException {
List<String> columns = getIdentityColumns(connection, table);
for (String column : columns) {
syncSingleSequence(connection, table, column);
}
if (includeCommit && !connection.getAutoCommit()) {
connection.commit();
}
return columns.size();
}
private List<DbObject> retrieveObjects(Set<String> types) throws SQLException {
if (this.monitor != null) {
monitor.setMonitorType(RowActionMonitor.MONITOR_PLAIN);
monitor.setCurrentObject(ResourceMgr.getString("MsgRetrievingTables"), -1, -1);
}
List<DbObject> result = CollectionUtil.sizedArrayList(50);
String[] typeNames = new String[types.size()];
int i = 0;
for (String type : types) {
if (type != null) {
// the JDBC calls all use upper-case type names, even
// if the DBMS stores them lower case
typeNames[i] = type.toUpperCase();
i++;
}
}
for (String schema : schemas) {
for (String name : names) {
if (cancelSearch) return null;
List<TableIdentifier> objects =
connection.getMetadata().getObjectList(name, schema, typeNames);
result.addAll(objects);
}
}
return result;
}
@Override
public void updateColumnDefinition(TableDefinition table, WbConnection conn) {
String typeNames = conn.getDbSettings().getProperty("qualifier.typenames", "datetime,interval");
Set<String> types = CollectionUtil.caseInsensitiveSet();
types.addAll(StringUtil.stringToList(typeNames, ",", true, true, false, false));
boolean checkRequired = false;
for (ColumnIdentifier col : table.getColumns()) {
String plainType = SqlUtil.getPlainTypeName(col.getDbmsType());
if (types.contains(plainType)) {
checkRequired = true;
}
int type = col.getDataType();
String val = col.getDefaultValue();
if (defaultNeedsQuotes(val, type, plainType)) {
val = "'" + val + "'";
col.setDefaultValue(val);
}
}
if (checkRequired) {
updateDateColumns(table, conn);
}
}
public OracleFKHandler(WbConnection conn) {
super(conn);
currentUser = conn.getCurrentUser();
containsStatusCol = true;
// This is essentially a copy of the Statement used by the Oracle driver
// the driver does not take unique constraints into account, and this statement does.
// Otherwise foreign keys referencing unique constraints (rather than primary keys) would
// not be displayed (DbExplorer, WbSchemaReport) or correctly processed (TableDependency)
baseSql =
"-- SQLWorkbench \n"
+ "SELECT "
+ OracleUtils.getCacheHint()
+ " NULL AS pktable_cat, \n"
+ " p.owner AS pktable_schem, \n"
+ " p.table_name AS pktable_name, \n"
+ " pc.column_name AS pkcolumn_name, \n"
+ " NULL AS fktable_cat, \n"
+ " f.owner AS fktable_schem, \n"
+ " f.table_name AS fktable_name, \n"
+ " fc.column_name AS fkcolumn_name, \n"
+ " fc.position AS key_seq, \n"
+ " 3 AS update_rule, \n"
+ " decode (f.delete_rule, \n"
+ " 'CASCADE', 0, \n"
+ " 'SET NULL', 2, \n"
+ " 1 \n"
+ " ) AS delete_rule, \n"
+ " f.constraint_name AS fk_name, \n"
+ " p.constraint_name AS pk_name, \n"
+ " decode(f.deferrable, \n"
+ " 'DEFERRABLE', decode(f.deferred, 'IMMEDIATE', "
+ DatabaseMetaData.importedKeyInitiallyImmediate
+ ", "
+ DatabaseMetaData.importedKeyInitiallyDeferred
+ ") , \n"
+ " 'NOT DEFERRABLE',"
+ DatabaseMetaData.importedKeyNotDeferrable
+ " \n"
+ " ) deferrability, \n"
+ " case when f.status = 'ENABLED' then 'YES' else 'NO' end as enabled, \n"
+ " case when f.validated = 'VALIDATED' then 'YES' else 'NO' end as validated \n "
+ "FROM all_cons_columns pc, \n"
+ " all_constraints p, \n"
+ " all_cons_columns fc, \n"
+ " all_constraints f \n"
+ "WHERE f.constraint_type = 'R' \n"
+ "AND p.owner = f.r_owner \n"
+ "AND p.constraint_name = f.r_constraint_name \n"
+ "AND p.constraint_type IN ('P', 'U') \n"
+ // this is the difference to the original statement from the Oracle driver (it uses =
// 'P')
"AND pc.owner = p.owner \n"
+ "AND pc.constraint_name = p.constraint_name \n"
+ "AND pc.table_name = p.table_name \n"
+ "AND fc.owner = f.owner \n"
+ "AND fc.constraint_name = f.constraint_name \n"
+ "AND fc.table_name = f.table_name \n"
+ "AND fc.position = pc.position \n";
}
public void setSchemasToSearch(List<String> searchSchemas) {
if (CollectionUtil.isEmpty(searchSchemas)) return;
schemas.clear();
for (String schema : searchSchemas) {
String s = connection.getMetadata().adjustSchemaNameCase(schema);
schemas.add(s);
}
}
public void setNamesToSearch(List<String> searchNames) {
if (CollectionUtil.isEmpty(searchNames)) return;
names.clear();
for (String name : searchNames) {
String n = connection.getMetadata().adjustObjectnameCase(name);
names.add(n);
}
}
@Test
public void testDuplicateNames() throws Exception {
TestUtil util = getTestUtil();
WbConnection con = util.getConnection();
String sql =
"CREATE TABLE one (ident int, refid int, PRIMARY KEY(ident));\n"
+ "CREATE TABLE two (ident int, refid int, PRIMARY KEY(ident));\n"
+ "commit;";
TestUtil.executeScript(con, sql);
String query =
"SELECT one.ident, two.ident \n" + "FROM one, two \n" + "WHERE one.refid = two.refid;";
ResultInfo info = null;
try (Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query)) {
info = new ResultInfo(rs.getMetaData(), con);
}
SourceTableDetector std = new SourceTableDetector();
std.checkColumnTables(query, info, con);
UpdateTableDetector utd = new UpdateTableDetector(con);
utd.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("TWO");
utd.checkUpdateTable(toCheck, info);
TableIdentifier tbl = utd.getUpdateTable();
assertEquals("TWO", tbl.getTableName());
assertEquals("PUBLIC", tbl.getSchema());
assertEquals("one", info.getColumn(0).getSourceTableName());
assertEquals("two", info.getColumn(1).getSourceTableName());
assertTrue(info.getColumn(1).isUpdateable());
assertTrue(info.getColumn(1).isPkColumn());
assertTrue(CollectionUtil.isEmpty(utd.getMissingPkColumns()));
}
private void searchList(
List<DbObject> toSearch,
List<String> searchValues,
boolean matchAll,
boolean ignoreCase,
boolean useRegex) {
if (monitor != null) {
monitor.setMonitorType(RowActionMonitor.MONITOR_PROCESS);
}
int total = toSearch.size();
int current = 1;
for (DbObject object : toSearch) {
numSearched++;
if (cancelSearch) return;
if (monitor != null) {
monitor.setCurrentObject(object.getObjectName(), current, total);
}
try {
CharSequence source = null;
if (connection.getMetadata().isTableType(object.getObjectType())) {
((TableIdentifier) object).setRetrieveFkSource(true);
}
ProcedureDefinition def = null;
if (object instanceof ProcedureDefinition) {
def = (ProcedureDefinition) object;
}
String key = getObjectKey(object);
if (!searchedObjects.contains(key)) {
source = object.getSource(connection);
if (StringUtil.isBlank(source)) {
LogMgr.logWarning(
"ObjectSourceSearcher.searchObjects()",
"Empty source returned for " + object.toString());
}
if (StringUtil.containsWords(source, searchValues, matchAll, ignoreCase, useRegex)) {
searchResult.add(object);
}
searchedObjects.add(key);
}
} catch (SQLException sql) {
LogMgr.logError(
"ObjectSourceSearcher.searchObjects()", "Error retrieving object source", sql);
}
current++;
}
}
private String getDelimiterForDrop() {
if (dbConnection == null) return ";";
DelimiterDefinition delim = dbConnection.getAlternateDelimiter();
if (delim == null) return ";";
ScriptParser parser = ScriptParser.createScriptParser(dbConnection);
if (parser.supportsMixedDelimiter()) {
return ";";
}
return "\n" + delim.getDelimiter() + "\n";
}
@Test
public void testCompletion() throws Exception {
WbConnection con = PostgresTestUtil.getPostgresConnection();
assertNotNull(con);
TestUtil.executeScript(con, "set search_path=path_2,path_1");
List<ColumnIdentifier> columns = con.getObjectCache().getColumns(new TableIdentifier("t1"));
assertNotNull(columns);
assertEquals(1, columns.size());
assertEquals("id1", columns.get(0).getColumnName());
TestUtil.executeScript(con, "set search_path=path_1,path_2");
columns = con.getObjectCache().getColumns(new TableIdentifier("t2"));
assertNotNull(columns);
assertEquals(2, columns.size());
assertEquals("id2", columns.get(0).getColumnName());
assertEquals("c2", columns.get(1).getColumnName());
Set<TableIdentifier> tables = con.getObjectCache().getTables("PATH_1");
assertNotNull(tables);
assertEquals(1, tables.size());
assertTrue(tables.contains(new TableIdentifier("t1")));
}
@Test
public void testSpecialName() throws Exception {
TestUtil util = getTestUtil();
WbConnection con = util.getConnection();
TestUtil.executeScript(
con,
"create table \"FOO.BAR\" (id integer primary key, somedata varchar(50));\n" + "commit;");
String sql = "select id, somedata from \"FOO.BAR\"";
ResultInfo info = null;
try (Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
info = new ResultInfo(rs.getMetaData(), con);
}
UpdateTableDetector detector = new UpdateTableDetector(con);
detector.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("\"FOO.BAR\"");
detector.checkUpdateTable(toCheck, info);
TableIdentifier tbl = detector.getUpdateTable();
assertEquals("FOO.BAR", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
resetInfo(info);
detector.setCheckPKOnly(true);
detector.checkUpdateTable(toCheck, info);
tbl = detector.getUpdateTable();
assertEquals("FOO.BAR", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
}
@Test
public void testSynonyms() throws Exception {
TestUtil util = getTestUtil();
WbConnection conn = DerbyTestUtil.getDerbyConnection(util.getBaseDir());
TestUtil.executeScript(
conn,
"create table person (id integer primary key, name varchar(20) not null);\n"
+ "create synonym psyn for person;\n");
String sql = "select id, name from psyn";
ResultInfo info = null;
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
info = new ResultInfo(rs.getMetaData(), conn);
}
info.getColumn(0).setIsPkColumn(false);
UpdateTableDetector detector = new UpdateTableDetector(conn);
detector.setCheckPKOnly(false);
TableIdentifier toCheck = new TableIdentifier("psyn");
detector.checkUpdateTable(toCheck, info);
TableIdentifier tbl = detector.getUpdateTable();
assertEquals("PSYN", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
resetInfo(info);
detector.setCheckPKOnly(true);
detector.checkUpdateTable(toCheck, info);
assertEquals("PSYN", tbl.getTableName());
assertTrue(info.getColumn(0).isPkColumn());
assertFalse(info.getColumn(1).isPkColumn());
}
@Test
public void testDetectUpdateTable() throws Exception {
WbConnection con = PostgresTestUtil.getPostgresConnection();
assertNotNull(con);
TestUtil.executeScript(con, "set search_path=path_2,path_1");
Statement stmt = null;
ResultSet rs = null;
try {
stmt = con.createStatement();
String sql = "select * from t1";
rs = stmt.executeQuery(sql);
DataStore ds1 = new DataStore(rs, con);
SqlUtil.closeResult(rs);
ds1.setGeneratingSql(sql);
ds1.checkUpdateTable(con);
TableIdentifier tbl1 = ds1.getUpdateTable();
assertNotNull(tbl1);
assertEquals("path_1", tbl1.getSchema());
sql = "select * from t2";
rs = stmt.executeQuery(sql);
DataStore ds2 = new DataStore(rs, con);
SqlUtil.closeResult(rs);
ds2.setGeneratingSql(sql);
ds2.checkUpdateTable(con);
TableIdentifier tbl2 = ds2.getUpdateTable();
assertNotNull(tbl2);
assertEquals("path_2", tbl2.getSchema());
} finally {
SqlUtil.closeStatement(stmt);
}
}
private List<String> getIdentityColumns(WbConnection dbConnection, TableIdentifier table) {
List<String> result = new ArrayList<>(1);
try {
List<ColumnIdentifier> columns = dbConnection.getMetadata().getTableColumns(table, false);
for (ColumnIdentifier col : columns) {
if (col.isAutoincrement()) {
result.add(col.getColumnName());
}
}
} catch (SQLException ex) {
LogMgr.logError(
"FirebirdSequenceAdjuster.getIdentityColumns()", "Could not read sequence columns", ex);
}
return result;
}
@Test
public void testDeferrable() throws SQLException {
WbConnection conn = OracleTestUtil.getOracleConnection();
assertNotNull("No Oracle connection available", conn);
OracleFKHandler fkHandler = new OracleFKHandler(conn);
String create =
"create table parent (id integer not null primary key);\n"
+ "create table child_deferred (id integer not null primary key, pid integer not null,\n"
+ " constraint fk_aaa foreign key (pid) references parent (id) deferrable initially deferred);\n"
+ "create table child_immediate (id integer not null primary key, pid integer not null,\n"
+ " constraint fk_bbb foreign key (pid) references parent (id) deferrable initially immediate);\n"
+ "create table child_not_deferred (id integer not null primary key, pid integer not null,\n"
+ " constraint fk_ccc foreign key (pid) references parent (id));\n";
TestUtil.executeScript(conn, create);
TableIdentifier parent = conn.getMetadata().findTable(new TableIdentifier("PARENt"));
DataStore fklist = fkHandler.getReferencedBy(parent);
assertNotNull(fklist);
assertEquals(3, fklist.getRowCount());
fklist.sortByColumn(0, true);
// DataStorePrinter printer = new DataStorePrinter(fklist);
// printer.printTo(System.out);
String deferrable = fklist.getValueAsString(0, "DEFERRABLE");
assertEquals("INITIALLY DEFERRED", deferrable);
deferrable = fklist.getValueAsString(1, "DEFERRABLE");
assertEquals("INITIALLY IMMEDIATE", deferrable);
deferrable = fklist.getValueAsString(2, "DEFERRABLE");
assertEquals("NOT DEFERRABLE", deferrable);
OracleTestUtil.cleanUpTestCase();
}
private void updateComputedColumns(TableDefinition table, WbConnection conn) {
PreparedStatement stmt = null;
ResultSet rs = null;
String tablename = table.getTable().getRawTableName();
String schema = table.getTable().getRawSchema();
String sql =
"select column_name, \n"
+ " generation_type \n"
+ "from sys.table_columns \n"
+ "where table_name = ? \n"
+ "and schema_name = ? \n"
+ "and generation_type is not null";
Map<String, String> expressions = new HashMap<>();
try {
stmt = conn.getSqlConnection().prepareStatement(sql);
stmt.setString(1, tablename);
stmt.setString(2, schema);
rs = stmt.executeQuery();
while (rs.next()) {
String colname = rs.getString(1);
String generated = rs.getString(2);
if (StringUtil.isNonEmpty(generated)) {
expressions.put(colname, "GENERATED " + generated);
}
}
} catch (Exception e) {
LogMgr.logError("HanaColumnEnhancer.updateComputedColumns()", "Error retrieving remarks", e);
} finally {
SqlUtil.closeAll(rs, stmt);
}
for (ColumnIdentifier col : table.getColumns()) {
String expr = expressions.get(col.getColumnName());
if (StringUtil.isNonBlank(expr)) {
col.setDefaultValue(null);
col.setComputedColumnExpression(expr);
col.setIsAutoincrement(true);
}
}
}
private List<DbObject> retrieveProcedures() throws SQLException {
if (this.monitor != null) {
monitor.setMonitorType(RowActionMonitor.MONITOR_PLAIN);
monitor.setCurrentObject(ResourceMgr.getString("MsgRetrievingProcedures"), -1, -1);
}
List<DbObject> result = CollectionUtil.sizedArrayList(50);
ProcedureReader reader = connection.getMetadata().getProcedureReader();
if (reader == null) return result;
for (String schema : schemas) {
for (String name : names) {
if (cancelSearch) return null;
List<ProcedureDefinition> procs = reader.getProcedureList(null, schema, name);
result.addAll(procs);
}
}
return result;
}
@Override
public boolean extendObjectList(
WbConnection con,
DataStore result,
String catalog,
String schemaPattern,
String objectPattern,
String[] requestedTypes) {
if (!DbMetadata.typeIncluded("TYPE", requestedTypes)) return false;
String select = getSelect(schemaPattern, objectPattern);
select += " ORDER BY a.alias, s.schemaname ";
Statement stmt = null;
ResultSet rs = null;
if (Settings.getInstance().getDebugMetadataSql()) {
LogMgr.logDebug("DerbyTypeReader.extendObjectList()", "Using sql=\n" + select);
}
try {
stmt = con.createStatementForQuery();
rs = stmt.executeQuery(select);
while (rs.next()) {
String schema = rs.getString("schemaname");
String name = rs.getString("type_name");
String classname = rs.getString("javaclassname");
String info = rs.getString("aliasinfo");
int row = result.addRow();
result.setValue(row, DbMetadata.COLUMN_IDX_TABLE_LIST_CATALOG, null);
result.setValue(row, DbMetadata.COLUMN_IDX_TABLE_LIST_SCHEMA, schema);
result.setValue(row, DbMetadata.COLUMN_IDX_TABLE_LIST_NAME, name);
result.setValue(row, DbMetadata.COLUMN_IDX_TABLE_LIST_TYPE, "TYPE");
result.setValue(row, DbMetadata.COLUMN_IDX_TABLE_LIST_REMARKS, null);
DerbyTypeDefinition def = new DerbyTypeDefinition(schema, name, classname, info);
result.getRow(row).setUserObject(def);
}
} catch (Exception e) {
LogMgr.logError("DerbyTypeReader.extendObjectList()", "Error retrieving object types", e);
} finally {
SqlUtil.closeAll(rs, stmt);
}
return true;
}
@Override
public DataStore getObjectDetails(WbConnection con, DbObject object) {
Statement stmt = null;
ResultSet rs = null;
try {
String select = getSelect(object.getSchema(), object.getObjectName());
stmt = con.createStatementForQuery();
rs = stmt.executeQuery(select);
DataStore result = new DataStore(rs, true);
return result;
} catch (Exception e) {
LogMgr.logError("DerbyTypeReader.extendObjectList()", "Error retrieving object types", e);
return null;
} finally {
SqlUtil.closeAll(rs, stmt);
}
}
private void syncSingleSequence(WbConnection dbConnection, TableIdentifier table, String column)
throws SQLException {
Statement stmt = null;
ResultSet rs = null;
try {
stmt = dbConnection.createStatement();
long maxValue = -1;
rs =
stmt.executeQuery(
"select max(" + column + ") from " + table.getTableExpression(dbConnection));
if (rs.next()) {
maxValue = rs.getLong(1) + 1;
SqlUtil.closeResult(rs);
}
if (maxValue > 0) {
String ddl =
"alter table "
+ table.getTableExpression(dbConnection)
+ " alter column "
+ column
+ " restart with "
+ Long.toString(maxValue);
LogMgr.logDebug(
"FirebirdSequenceAdjuster.syncSingleSequence()", "Syncing sequence using: " + ddl);
stmt.execute(ddl);
}
} catch (SQLException ex) {
LogMgr.logError(
"FirebirdSequenceAdjuster.getColumnSequences()", "Could not read sequences", ex);
throw ex;
} finally {
SqlUtil.closeAll(rs, stmt);
}
}
@Test
public void testDomainRetrieval() throws Exception {
WbConnection con = PostgresTestUtil.getPostgresConnection();
if (con == null) {
System.out.println("No PostgreSQL connection available. Skipping test...");
return;
}
Collection<String> types = con.getMetadata().getObjectTypes();
assertTrue(types.contains("DOMAIN"));
List<TableIdentifier> objects =
con.getMetadata().getObjectList(TEST_SCHEMA, new String[] {"DOMAIN"});
assertEquals(2, objects.size());
DbObject salary = objects.get(0);
DbObject zz_int = objects.get(1);
objects = con.getMetadata().getObjectList("other", new String[] {"DOMAIN"});
assertEquals(1, objects.size());
objects = con.getMetadata().getObjectList("%", new String[] {"DOMAIN"});
assertEquals(objects.toString(), 3, objects.size());
PostgresDomainReader reader = new PostgresDomainReader();
List<DomainIdentifier> domains = reader.getDomainList(con, "%", "%");
assertEquals(3, domains.size());
assertEquals("DOMAIN", salary.getObjectType());
assertTrue(domains.get(0) instanceof DomainIdentifier);
String sql = salary.getSource(con).toString().trim();
String expected =
"CREATE DOMAIN "
+ TEST_SCHEMA.toLowerCase()
+ ".salary AS numeric(12,2)\n"
+ " CONSTRAINT NOT NULL CHECK (VALUE > 0::numeric);";
assertEquals(expected, sql);
sql = zz_int.getSource(con).toString().trim();
expected =
"CREATE DOMAIN "
+ TEST_SCHEMA.toLowerCase()
+ ".zz_int AS integer\n"
+ " CONSTRAINT NOT NULL;";
assertEquals(expected, sql);
GenericObjectDropper dropper = new GenericObjectDropper();
dropper.setCascade(true);
dropper.setObjects(domains);
dropper.setConnection(con);
String drop = dropper.getScript().toString().trim();
// System.out.println(drop);
assertTrue(drop.contains("DROP DOMAIN IF EXISTS " + TEST_SCHEMA + ".zz_int"));
assertTrue(drop.contains("DROP DOMAIN IF EXISTS " + TEST_SCHEMA + ".salary"));
assertTrue(drop.contains("DROP DOMAIN IF EXISTS other.positive_int"));
DataStore details = reader.getObjectDetails(con, salary);
assertNotNull(details);
assertEquals(1, details.getRowCount());
assertEquals("salary", details.getValueAsString(0, 0));
assertEquals("numeric(12,2)", details.getValueAsString(0, 1));
}
private void updateDateColumns(TableDefinition table, WbConnection conn) {
String catalog = table.getTable().getRawCatalog();
String systemSchema = conn.getDbSettings().getProperty("systemschema", "informix");
TableIdentifier sysTabs = new TableIdentifier(catalog, systemSchema, "systables");
TableIdentifier sysCols = new TableIdentifier(catalog, systemSchema, "syscolumns");
String systables = sysTabs.getFullyQualifiedName(conn);
String syscolumns = sysCols.getFullyQualifiedName(conn);
String typeValues = conn.getDbSettings().getProperty("qualifier.typevalues", "10,14,266,270");
String sql =
"select c.colname, c.collength \n"
+ "from "
+ systables
+ " t \n"
+ " join "
+ syscolumns
+ " c on t.tabid = c.tabid \n"
+ "where t.tabname = ? \n"
+ " and t.owner = ? \n"
+ " and c.coltype in ("
+ typeValues
+ ")";
String tablename = table.getTable().getRawTableName();
String schema = table.getTable().getRawSchema();
LogMgr.logDebug(
"InformixColumnEnhancer.updateDateColumns()",
"Query to retrieve column details:\n" + SqlUtil.replaceParameters(sql, tablename, schema));
PreparedStatement stmt = null;
ResultSet rs = null;
Map<String, ColumnIdentifier> cols = new TreeMap<>(CaseInsensitiveComparator.INSTANCE);
for (ColumnIdentifier col : table.getColumns()) {
cols.put(col.getColumnName(), col);
}
try {
stmt = conn.getSqlConnection().prepareStatement(sql);
stmt.setString(1, tablename);
stmt.setString(2, schema);
rs = stmt.executeQuery();
while (rs.next()) {
String colname = rs.getString(1);
int colLength = rs.getInt(2);
ColumnIdentifier col = cols.get(colname);
if (col != null) {
String typeDesc = getQualifier(colLength);
String dbms = SqlUtil.getPlainTypeName(col.getDbmsType());
String newType = dbms + " " + typeDesc;
LogMgr.logDebug(
"InformixColumnEnhancer.updateDateColumns()",
"Column "
+ tablename
+ "."
+ colname
+ " has collength of: "
+ colLength
+ ". Changing type '"
+ col.getDbmsType()
+ "' to '"
+ newType
+ "'");
col.setDbmsType(newType);
} else {
LogMgr.logError(
"InformixColumnEnhancer.updateDateColumns()",
"The query returned a column name ("
+ colname
+ ") that was not part of the passed table definition!",
null);
}
}
} catch (Exception e) {
LogMgr.logError(
"InformixColumnEnhancer.updateDateColumns()",
"Error retrieving datetime qualifiers using:\n"
+ SqlUtil.replaceParameters(sql, tablename, schema),
e);
} finally {
SqlUtil.closeAll(rs, stmt);
}
}
public static boolean isOwnTransaction(WbConnection dbConnection) {
if (dbConnection == null) return false;
if (dbConnection.getAutoCommit()) return false;
return (dbConnection.getProfile().getUseSeparateConnectionPerTab()
|| getAlwaysUseSeparateConnForDbExpWindow());
}