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