@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());
}
@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()));
}
@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);
}
}
private DataStore getImportedKeyList(TableIdentifier tbl) throws SQLException {
// I'm not adding an ORDER BY because the statement is terribly slow anyway
// and an ORDER BY makes it even slower for large results
StringBuilder sql = new StringBuilder(baseSql.length() + 50);
sql.append(getQuery(tbl));
sql.append("AND f.table_name = ? \n");
sql.append("AND f.owner = ? \n");
if (Settings.getInstance().getDebugMetadataSql()) {
LogMgr.logDebug(
"OracleFKHandler.getImportedKeyList()",
"Retrieving imported foreign keys using:\n"
+ SqlUtil.replaceParameters(sql, tbl.getRawTableName(), tbl.getRawSchema()));
}
ResultSet rs;
DataStore result = null;
try {
retrievalStatement = this.getConnection().getSqlConnection().prepareStatement(sql.toString());
retrievalStatement.setString(1, tbl.getRawTableName());
retrievalStatement.setString(2, tbl.getRawSchema());
rs = retrievalStatement.executeQuery();
result = processResult(rs);
} finally {
// the result set is closed by processResult
SqlUtil.closeStatement(retrievalStatement);
retrievalStatement = null;
}
sortResult(result);
return result;
}
/**
* Adjust the baseSql query to reflect if a table for the current user is queried.
*
* <p>If the table belongs to the current user, the user_XXX views can be used instead of the
* all_XXX views. Using the user_XXX views is faster (at least on my system) than the all_XXX
* views - although it is still an awfully slow statement... <br>
* Querying user_constraints instead of all_constraints means that constraints between two schemas
* will not be shown. In order to still enable this, the config property: <br>
* <code>workbench.db.oracle.optimize_fk_query</code> <br>
* can be set to false, if all_constraints should always be queried.
*
* @param tbl the table for which the query should be generated
* @return the query to use
* @see OracleUtils#optimizeCatalogQueries()
*/
private String getQuery(TableIdentifier tbl) {
if (OracleUtils.optimizeCatalogQueries()) {
String schema = tbl.getRawSchema();
if (StringUtil.isEmptyString(schema) || schema.equalsIgnoreCase(currentUser)) {
return baseSql.replace(" all_c", " user_c");
}
}
return baseSql;
}
@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 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 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 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);
}
}
protected void retrieveCurrentTrigger() {
if (this.dbConnection == null || this.reader == null) return;
int row = this.triggerList.getSelectedRow();
if (!WbSwingUtilities.isConnectionIdle(this, this.dbConnection)) return;
if (row < 0) return;
final String triggerName =
this.triggerList.getValueAsString(row, TriggerReader.COLUMN_IDX_TABLE_TRIGGERLIST_TRG_NAME);
final String tableName =
this.triggerList.getValueAsString(
row, TriggerReader.COLUMN_IDX_TABLE_TRIGGERLIST_TRG_TABLE);
final String comment =
this.triggerList.getValueAsString(
row, TriggerReader.COLUMN_IDX_TABLE_TRIGGERLIST_TRG_COMMENT);
Container parent = this.getParent();
WbSwingUtilities.showWaitCursor(parent);
try {
if (dbConnection.getProfile().getUseSeparateConnectionPerTab()) {
levelChanger.changeIsolationLevel(dbConnection);
}
dbConnection.setBusy(true);
try {
TableIdentifier tbl = null;
if (tableName != null) {
tbl = new TableIdentifier(tableName, dbConnection);
if (tbl.getCatalog() == null) tbl.setCatalog(currentCatalog);
if (tbl.getSchema() == null) tbl.setSchema(currentSchema);
}
DropType dropType =
DbExplorerSettings.getDropTypeToGenerate(TriggerDefinition.TRIGGER_TYPE_NAME);
String sql =
reader.getTriggerSource(currentCatalog, currentSchema, triggerName, tbl, comment, true);
Object obj = triggerList.getUserObject(row);
boolean isReplace = SqlUtil.isReplaceDDL(sql, dbConnection, dropType);
if (dropType != DropType.none
&& obj instanceof TriggerDefinition
&& sql != null
&& !isReplace) {
TriggerDefinition trg = (TriggerDefinition) obj;
String drop = trg.getDropStatement(dbConnection, dropType == DropType.cascaded);
if (StringUtil.isNonBlank(drop)) {
sql = drop + getDelimiterForDrop() + "\n\n" + sql;
}
}
final String sourceSql = sql == null ? "" : sql;
WbSwingUtilities.invoke(
() -> {
source.setText(sourceSql, triggerName, TRG_TYPE_NAME);
});
} catch (Throwable ex) {
LogMgr.logError(
"TriggerListPanel.retrieveTriggerSource() thread", "Could not read trigger source", ex);
source.setPlainText(ExceptionUtil.getDisplay(ex));
}
} finally {
WbSwingUtilities.showDefaultCursor(parent);
levelChanger.restoreIsolationLevel(dbConnection);
dbConnection.setBusy(false);
}
if (this.triggerList.getSelectedRowCount() == 1) {
EventQueue.invokeLater(
() -> {
source.setCaretPosition(0, false);
if (DbExplorerSettings.getSelectSourcePanelAfterRetrieve()) {
source.requestFocusInWindow();
}
});
}
}