@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 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 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);
    }
  }
예제 #5
0
  private DataStore getImportedKeyList(TableIdentifier tbl) throws SQLException {
    // I'm not adding an ORDER BY because the statement is terribly slow anyway
    // and an ORDER BY makes it even slower for large results
    StringBuilder sql = new StringBuilder(baseSql.length() + 50);
    sql.append(getQuery(tbl));
    sql.append("AND f.table_name = ? \n");
    sql.append("AND f.owner = ? \n");

    if (Settings.getInstance().getDebugMetadataSql()) {
      LogMgr.logDebug(
          "OracleFKHandler.getImportedKeyList()",
          "Retrieving imported foreign keys using:\n"
              + SqlUtil.replaceParameters(sql, tbl.getRawTableName(), tbl.getRawSchema()));
    }

    ResultSet rs;
    DataStore result = null;
    try {
      retrievalStatement = this.getConnection().getSqlConnection().prepareStatement(sql.toString());
      retrievalStatement.setString(1, tbl.getRawTableName());
      retrievalStatement.setString(2, tbl.getRawSchema());
      rs = retrievalStatement.executeQuery();
      result = processResult(rs);
    } finally {
      // the result set is closed by processResult
      SqlUtil.closeStatement(retrievalStatement);
      retrievalStatement = null;
    }
    sortResult(result);
    return result;
  }
예제 #6
0
 /**
  * Adjust the baseSql query to reflect if a table for the current user is queried.
  *
  * <p>If the table belongs to the current user, the user_XXX views can be used instead of the
  * all_XXX views. Using the user_XXX views is faster (at least on my system) than the all_XXX
  * views - although it is still an awfully slow statement... <br>
  * Querying user_constraints instead of all_constraints means that constraints between two schemas
  * will not be shown. In order to still enable this, the config property: <br>
  * <code>workbench.db.oracle.optimize_fk_query</code> <br>
  * can be set to false, if all_constraints should always be queried.
  *
  * @param tbl the table for which the query should be generated
  * @return the query to use
  * @see OracleUtils#optimizeCatalogQueries()
  */
 private String getQuery(TableIdentifier tbl) {
   if (OracleUtils.optimizeCatalogQueries()) {
     String schema = tbl.getRawSchema();
     if (StringUtil.isEmptyString(schema) || schema.equalsIgnoreCase(currentUser)) {
       return baseSql.replace(" all_c", " user_c");
     }
   }
   return baseSql;
 }
  @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 searchList(
      List<DbObject> toSearch,
      List<String> searchValues,
      boolean matchAll,
      boolean ignoreCase,
      boolean useRegex) {
    if (monitor != null) {
      monitor.setMonitorType(RowActionMonitor.MONITOR_PROCESS);
    }
    int total = toSearch.size();
    int current = 1;

    for (DbObject object : toSearch) {
      numSearched++;
      if (cancelSearch) return;

      if (monitor != null) {
        monitor.setCurrentObject(object.getObjectName(), current, total);
      }

      try {
        CharSequence source = null;
        if (connection.getMetadata().isTableType(object.getObjectType())) {
          ((TableIdentifier) object).setRetrieveFkSource(true);
        }

        ProcedureDefinition def = null;
        if (object instanceof ProcedureDefinition) {
          def = (ProcedureDefinition) object;
        }

        String key = getObjectKey(object);
        if (!searchedObjects.contains(key)) {
          source = object.getSource(connection);
          if (StringUtil.isBlank(source)) {
            LogMgr.logWarning(
                "ObjectSourceSearcher.searchObjects()",
                "Empty source returned for " + object.toString());
          }

          if (StringUtil.containsWords(source, searchValues, matchAll, ignoreCase, useRegex)) {
            searchResult.add(object);
          }
          searchedObjects.add(key);
        }
      } catch (SQLException sql) {
        LogMgr.logError(
            "ObjectSourceSearcher.searchObjects()", "Error retrieving object source", sql);
      }
      current++;
    }
  }
  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);
    }
  }
  private void updateDateColumns(TableDefinition table, WbConnection conn) {
    String catalog = table.getTable().getRawCatalog();

    String systemSchema = conn.getDbSettings().getProperty("systemschema", "informix");
    TableIdentifier sysTabs = new TableIdentifier(catalog, systemSchema, "systables");
    TableIdentifier sysCols = new TableIdentifier(catalog, systemSchema, "syscolumns");

    String systables = sysTabs.getFullyQualifiedName(conn);
    String syscolumns = sysCols.getFullyQualifiedName(conn);

    String typeValues = conn.getDbSettings().getProperty("qualifier.typevalues", "10,14,266,270");

    String sql =
        "select c.colname, c.collength \n"
            + "from "
            + systables
            + " t \n"
            + "  join "
            + syscolumns
            + " c on t.tabid = c.tabid \n"
            + "where t.tabname = ? \n"
            + "  and t.owner = ? \n"
            + "  and c.coltype in ("
            + typeValues
            + ")";

    String tablename = table.getTable().getRawTableName();
    String schema = table.getTable().getRawSchema();

    LogMgr.logDebug(
        "InformixColumnEnhancer.updateDateColumns()",
        "Query to retrieve column details:\n" + SqlUtil.replaceParameters(sql, tablename, schema));

    PreparedStatement stmt = null;
    ResultSet rs = null;

    Map<String, ColumnIdentifier> cols = new TreeMap<>(CaseInsensitiveComparator.INSTANCE);
    for (ColumnIdentifier col : table.getColumns()) {
      cols.put(col.getColumnName(), col);
    }

    try {
      stmt = conn.getSqlConnection().prepareStatement(sql);
      stmt.setString(1, tablename);
      stmt.setString(2, schema);
      rs = stmt.executeQuery();

      while (rs.next()) {
        String colname = rs.getString(1);
        int colLength = rs.getInt(2);
        ColumnIdentifier col = cols.get(colname);
        if (col != null) {
          String typeDesc = getQualifier(colLength);

          String dbms = SqlUtil.getPlainTypeName(col.getDbmsType());
          String newType = dbms + " " + typeDesc;
          LogMgr.logDebug(
              "InformixColumnEnhancer.updateDateColumns()",
              "Column "
                  + tablename
                  + "."
                  + colname
                  + " has collength of: "
                  + colLength
                  + ". Changing type '"
                  + col.getDbmsType()
                  + "' to '"
                  + newType
                  + "'");
          col.setDbmsType(newType);
        } else {
          LogMgr.logError(
              "InformixColumnEnhancer.updateDateColumns()",
              "The query returned a column name ("
                  + colname
                  + ") that was not part of the passed table definition!",
              null);
        }
      }
    } catch (Exception e) {
      LogMgr.logError(
          "InformixColumnEnhancer.updateDateColumns()",
          "Error retrieving datetime qualifiers using:\n"
              + SqlUtil.replaceParameters(sql, tablename, schema),
          e);
    } finally {
      SqlUtil.closeAll(rs, stmt);
    }
  }
예제 #14
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();
            }
          });
    }
  }