@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 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 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()));
  }
  @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());
  }
 private void resetInfo(ResultInfo info) {
   for (ColumnIdentifier col : info.getColumns()) {
     col.setIsPkColumn(false);
     col.setIsNullable(true);
   }
 }