@Override
public SequenceDefinition getSequenceDefinition(String catalog, String owner, String sequence) {
DataStore ds = getRawSequenceDefinition(catalog, owner, sequence);
if (ds == null || ds.getRowCount() == 0) return null;
SequenceDefinition def = createDefinition(ds, 0);
return def;
}
@Override
public List<SequenceDefinition> getSequences(String catalog, String owner, String namePattern) {
DataStore ds = getRawSequenceDefinition(catalog, owner, namePattern);
if (ds == null || ds.getRowCount() == 0) return Collections.emptyList();
ArrayList<SequenceDefinition> result = new ArrayList<>();
for (int row = 0; row < ds.getRowCount(); row++) {
result.add(createDefinition(ds, row));
}
return result;
}
@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);
}
}
@Override
public DerbyTypeDefinition getObjectDefinition(WbConnection con, DbObject name) {
DataStore def = getObjectDetails(con, name);
if (def.getRowCount() < 1) return null;
String schema = def.getValueAsString(0, 0);
String typeName = def.getValueAsString(0, 1);
String javaClass = def.getValueAsString(0, 2);
String info = def.getValueAsString(0, 3);
DerbyTypeDefinition type = new DerbyTypeDefinition(schema, typeName, javaClass, info);
return type;
}
@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;
}
@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);
}
}
@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();
}
@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));
}
public void handleAnnotations(
PanelReloader reloader, DwPanel panel, AutomaticRefreshMgr refreshMgr) {
if (panel == null) return;
WbTable tbl = panel.getTable();
DataStore ds = tbl.getDataStore();
if (ds == null) return;
String sql = ds.getGeneratingSql();
if (StringUtil.isEmptyString(sql)) return;
Set<String> keys =
CollectionUtil.treeSet(
WbAnnotation.getTag(ScrollAnnotation.ANNOTATION),
WbAnnotation.getTag(MacroAnnotation.ANNOTATION),
WbAnnotation.getTag(RefreshAnnotation.ANNOTATION));
List<WbAnnotation> annotations = WbAnnotation.readAllAnnotations(sql, keys);
List<MacroAnnotation> macros = new ArrayList<>();
boolean scrollToEnd = false;
int line = -1;
MainWindow main = (MainWindow) SwingUtilities.getWindowAncestor(tbl);
MacroStorage macroMgr = MacroManager.getInstance().getMacros(main.getMacroClientId());
for (WbAnnotation annotation : annotations) {
if (annotation
.getKeyWord()
.equalsIgnoreCase(WbAnnotation.getTag(ScrollAnnotation.ANNOTATION))) {
String scrollValue = annotation.getValue();
if (scrollValue != null) {
scrollToEnd = ScrollAnnotation.scrollToEnd(scrollValue);
line = ScrollAnnotation.scrollToLine(scrollValue);
}
} else if (refreshMgr != null
&& annotation
.getKeyWord()
.equalsIgnoreCase(WbAnnotation.getTag(RefreshAnnotation.ANNOTATION))) {
String interval = annotation.getValue();
int milliSeconds = AutomaticRefreshMgr.parseInterval(interval);
refreshMgr.addRefresh(reloader, panel, milliSeconds);
} else {
MacroAnnotation macro = new MacroAnnotation();
macro.setValue(annotation.getValue());
String macroName = macro.getMacroName();
if (macroName != null && macroMgr.getMacro(macroName) != null) {
macros.add(macro);
}
}
}
if (macros.size() > 0 && tbl != null) {
try {
MacroMenuBuilder builder = new MacroMenuBuilder();
WbMenu menu = builder.buildDataMacroMenu(main, tbl, macros);
tbl.addMacroMenu(menu);
} catch (Exception ex) {
// ignore
}
}
if (scrollToEnd && tbl != null) {
tbl.scrollToRow(tbl.getRowCount() - 1);
} else if (line > 0) {
tbl.scrollToRow(line - 1);
}
}
protected DataStore getKeyList(
TableIdentifier tbl, boolean getOwnFk, boolean includeNumericRuleValue) {
if (cancel) return null;
String cols[] = null;
String refColName = null;
DbSettings dbSettings = dbConnection.getDbSettings();
if (getOwnFk) {
refColName = "REFERENCES";
} else {
refColName = "REFERENCED BY";
}
int types[];
int sizes[];
if (includeNumericRuleValue) {
cols =
new String[] {
"FK_NAME",
"COLUMN",
refColName,
"UPDATE_RULE",
"DELETE_RULE",
"DEFERRABLE",
"ENABLED",
"VALIDATED",
"UPDATE_RULE_VALUE",
"DELETE_RULE_VALUE",
"DEFER_RULE_VALUE"
};
types =
new int[] {
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.INTEGER,
Types.INTEGER,
Types.INTEGER
};
sizes = new int[] {25, 10, 30, 12, 12, 15, 5, 5, 1, 1, 1};
} else {
cols =
new String[] {
"FK_NAME",
"COLUMN",
refColName,
"UPDATE_RULE",
"DELETE_RULE",
"DEFERRABLE",
"ENABLED",
"VALIDATED"
};
types =
new int[] {
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR,
Types.VARCHAR
};
sizes = new int[] {25, 10, 30, 12, 12, 15, 5, 5};
}
DataStore ds = new DataStore(cols, types, sizes);
if (tbl == null) return ds;
DataStore rawList = null;
try {
int tableCol;
int fkNameCol;
int colCol;
int fkColCol;
int deleteActionCol = 10;
int updateActionCol = 9;
int schemaCol;
int catalogCol;
if (getOwnFk) {
rawList = getRawKeyList(tbl, false);
tableCol = 2;
schemaCol = 1;
catalogCol = 0;
fkNameCol = 11;
colCol = 7;
fkColCol = 3;
} else {
rawList = getRawKeyList(tbl, true);
tableCol = 6;
schemaCol = 5;
catalogCol = 4;
fkNameCol = 11;
colCol = 3;
fkColCol = 7;
}
if (rawList == null) {
// this means retrieval was cancelled
this.cancel = true;
return null;
}
for (int rawRow = 0; rawRow < rawList.getRowCount(); rawRow++) {
String tname = rawList.getValueAsString(rawRow, tableCol);
String schema = rawList.getValueAsString(rawRow, schemaCol);
String catalog = rawList.getValueAsString(rawRow, catalogCol);
TableIdentifier tid = new TableIdentifier(catalog, schema, tname, false);
tid.setNeverAdjustCase(true);
String tableName = tid.getTableExpression(dbConnection);
String fk_col = rawList.getValueAsString(rawRow, fkColCol);
String col = rawList.getValueAsString(rawRow, colCol);
String fk_name = rawList.getValueAsString(rawRow, fkNameCol);
int updateAction =
rawList.getValueAsInt(rawRow, updateActionCol, DatabaseMetaData.importedKeyNoAction);
String updActionDesc = dbSettings.getRuleDisplay(updateAction);
int deleteAction =
rawList.getValueAsInt(rawRow, deleteActionCol, DatabaseMetaData.importedKeyNoAction);
String delActionDesc = dbSettings.getRuleDisplay(deleteAction);
int deferrableCode =
rawList.getValueAsInt(rawRow, 13, DatabaseMetaData.importedKeyNoAction);
String deferrable = dbSettings.getRuleDisplay(deferrableCode);
int row = ds.addRow();
ds.setValue(row, COLUMN_IDX_FK_DEF_FK_NAME, fk_name.trim());
ds.setValue(row, COLUMN_IDX_FK_DEF_COLUMN_NAME, col.trim());
ds.setValue(row, COLUMN_IDX_FK_DEF_REFERENCE_COLUMN_NAME, tableName + "." + fk_col);
ds.setValue(row, COLUMN_IDX_FK_DEF_UPDATE_RULE, updActionDesc);
ds.setValue(row, COLUMN_IDX_FK_DEF_DELETE_RULE, delActionDesc);
ds.setValue(row, COLUMN_IDX_FK_DEF_DEFERRABLE, deferrable);
if (includeNumericRuleValue) {
ds.setValue(row, COLUMN_IDX_FK_DEF_DELETE_RULE_VALUE, Integer.valueOf(deleteAction));
ds.setValue(row, COLUMN_IDX_FK_DEF_UPDATE_RULE_VALUE, Integer.valueOf(updateAction));
ds.setValue(
row, COLUMN_IDX_FK_DEF_DEFERRABLE_RULE_VALUE, Integer.valueOf(deferrableCode));
}
if (containsStatusCol) {
ds.setValue(row, COLUMN_IDX_FK_DEF_ENABLED, rawList.getValue(rawRow, "ENABLED"));
ds.setValue(row, COLUMN_IDX_FK_DEF_VALIDATED, rawList.getValue(rawRow, "VALIDATED"));
}
if (cancel) {
LogMgr.logWarning(
"DefaultFKHandler.getKeyList()", "Processing of rows has been cancelled");
break;
}
}
ds.resetStatus();
} catch (Exception e) {
LogMgr.logError("FKHandler.getKeyList()", "Error when retrieving foreign keys", e);
ds.reset();
}
return ds;
}
protected DataStore processResult(ResultSet rs) throws SQLException {
if (rs == null) return null;
DataStore ds = new DataStore(rs, false);
boolean useColumnNames = dbConnection.getDbSettings().useColumnNameForMetadata();
int enabledCol = -1;
int validCol = -1;
if (containsStatusCol) {
enabledCol = JdbcUtils.getColumnIndex(rs, "ENABLED");
validCol = JdbcUtils.getColumnIndex(rs, "VALIDATED");
}
try {
while (rs.next()) {
if (cancel) {
LogMgr.logWarning(
"DefaultFKHandler.processResult()", "Processing of rows has been cancelled");
break;
}
int row = ds.addRow();
ds.setValue(
row, 0, useColumnNames ? rs.getString("PKTABLE_CAT") : rs.getString(1)); // PKTABLE_CAT
ds.setValue(
row,
1,
useColumnNames ? rs.getString("PKTABLE_SCHEM") : rs.getString(2)); // PKTABLE_SCHEM
ds.setValue(
row,
2,
useColumnNames ? rs.getString("PKTABLE_NAME") : rs.getString(3)); // PKTABLE_NAME
ds.setValue(
row,
3,
useColumnNames ? rs.getString("PKCOLUMN_NAME") : rs.getString(4)); // PKCOLUMN_NAME
ds.setValue(
row, 4, useColumnNames ? rs.getString("FKTABLE_CAT") : rs.getString(5)); // FKTABLE_CAT
ds.setValue(
row,
5,
useColumnNames ? rs.getString("FKTABLE_SCHEM") : rs.getString(6)); // FKTABLE_SCHEM
ds.setValue(
row,
6,
useColumnNames ? rs.getString("FKTABLE_NAME") : rs.getString(7)); // FKTABLE_NAME
ds.setValue(
row,
7,
useColumnNames ? rs.getString("FKCOLUMN_NAME") : rs.getString(8)); // FKCOLUMN_NAME
ds.setValue(
row,
8,
Integer.valueOf(useColumnNames ? rs.getInt("KEY_SEQ") : rs.getInt(9))); // KEY_SEQ
int updRule = fixRule(useColumnNames ? rs.getInt("UPDATE_RULE") : rs.getInt(10));
ds.setValue(row, 9, Integer.valueOf(updRule)); // UPDATE_RULE
int delRule = fixRule(useColumnNames ? rs.getInt("DELETE_RULE") : rs.getInt(11));
ds.setValue(row, 10, Integer.valueOf(delRule)); // DELETE_RULE
ds.setValue(
row, 11, useColumnNames ? rs.getString("FK_NAME") : rs.getString(12)); // FK_NAME
ds.setValue(
row, 12, useColumnNames ? rs.getString("PK_NAME") : rs.getString(13)); // PK_NAME
int defer = fixDeferrableRule(useColumnNames ? rs.getInt("DEFERRABILITY") : rs.getInt(14));
ds.setValue(row, 13, Integer.valueOf(defer)); // DEFERRABILITY
if (enabledCol > 0) {
ds.setValue(row, enabledCol - 1, rs.getString(enabledCol));
ds.setValue(row, validCol - 1, rs.getString(validCol));
}
}
ds.resetStatus();
} finally {
SqlUtil.closeResult(rs);
}
return ds;
}
private SequenceDefinition createDefinition(DataStore ds, int row) {
if (ds == null || row >= ds.getRowCount()) return null;
String name = ds.getValueAsString(row, "SEQUENCE_NAME");
String schema = ds.getValueAsString(row, "SEQUENCE_SCHEMA");
String db = ds.getValueAsString(row, "SEQUENCE_CATALOG");
SequenceDefinition result = new SequenceDefinition(schema, name);
result.setCatalog(db);
result.setSequenceProperty(PROP_MIN_VALUE, ds.getValue(row, "minimum_value"));
result.setSequenceProperty(PROP_MAX_VALUE, ds.getValue(row, "maximum_value"));
result.setSequenceProperty(PROP_START_VALUE, ds.getValue(row, "start_value"));
result.setSequenceProperty(PROP_INCREMENT, ds.getValue(row, "increment"));
result.setSequenceProperty(
PROP_CYCLE, Boolean.valueOf("CYCLE".equals(ds.getValueAsString(row, "cycle_flag"))));
result.setSequenceProperty(
PROP_IS_CACHED,
Boolean.valueOf(StringUtil.stringToBool(ds.getValueAsString(row, "is_cached"))));
result.setSequenceProperty(PROP_CACHE_SIZE, ds.getValue(row, "cache_size"));
result.setSequenceProperty(PROP_CURRENT_VALUE, ds.getValue(row, "current_value"));
result.setSequenceProperty(PROP_DATA_TYPE, ds.getValue(row, "data_type"));
result.setSequenceProperty(PROP_USER_DATA_TYPE, ds.getValue(row, "user_type"));
result.setSequenceProperty(PROP_PRECISION, ds.getValue(row, "precision"));
return result;
}
private void sortResult(DataStore ds) {
if (ds == null) return;
// sort by the second and third column
SortDefinition def = new SortDefinition(new int[] {1, 2}, new boolean[] {true, true});
ds.sort(def);
}
