@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);
    }
  }
  @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 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()));
  }
Пример #5
0
  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();
            }
          });
    }
  }