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