private String generateFinalNamePattern(DatabaseMetaData meta, String name)
     throws SQLException {
   if (meta.storesLowerCaseIdentifiers()) {
     return name.toLowerCase(Locale.ROOT);
   } else {
     return name;
   }
 }
 public boolean storesLowerCaseIdentifiers() throws SQLException {
   {
     try {
       return _meta.storesLowerCaseIdentifiers();
     } catch (SQLException e) {
       handleException(e);
       return false;
     }
   }
 }
Exemple #3
0
  private DBIndex getDBIndex(String tableName, String indexName, final String trxName)
      throws SQLException {

    final String[] indexColsFromDB = new String[30];
    int indexLengthDB = 0;
    DBIndex dbIndex = null;

    final DatabaseMetaData md = Trx.get(trxName, true).getConnection().getMetaData();
    if (md.storesUpperCaseIdentifiers()) tableName = tableName.toUpperCase();
    else if (md.storesLowerCaseIdentifiers()) tableName = tableName.toLowerCase();
    final String catalog = "REFERENCE";
    final String schema = null;
    ResultSet rs = null;
    try {
      rs = md.getIndexInfo(catalog, schema, tableName, false, true);
      while (rs.next()) {
        final String dbIndexName = rs.getString("INDEX_NAME");
        if (dbIndexName != null && indexName.equalsIgnoreCase(dbIndexName)) {
          if (dbIndex == null) {
            dbIndex = new DBIndex();
            dbIndex.name = dbIndexName;
            dbIndex.isUnique = true;
          }
          String columnName = rs.getString("COLUMN_NAME");
          int pos = rs.getShort("ORDINAL_POSITION");
          if (pos > 0) {
            // EDB returns varchar index columns wrapped with double
            // quotes, hence comparing
            // after stripping the quotes
            if (columnName.startsWith("\"") && columnName.endsWith("\"")) {
              columnName = columnName.substring(1, columnName.length() - 1);
            }
            indexColsFromDB[pos - 1] = columnName;
            if (pos > indexLengthDB) indexLengthDB = pos;
          }
          boolean isNonUnique = rs.getBoolean("NON_UNIQUE");
          if (isNonUnique) dbIndex.isUnique = false;
          dbIndex.filterCondition = rs.getString("FILTER_CONDITION");
        }
      }
    } finally {
      DB.close(rs);
      rs = null;
    }
    //
    if (dbIndex == null || indexLengthDB <= 0) return null;
    //
    dbIndex.columnNames = new String[indexLengthDB];
    for (int i = 0; i < indexLengthDB; i++) {
      dbIndex.columnNames[i] = indexColsFromDB[i];
    }
    // Arrays.sort(dbIndex.columnNames);
    //
    return dbIndex;
  }
  protected String normalizeName(String name, DatabaseMetaData databaseMetaData)
      throws SQLException {

    if (databaseMetaData.storesLowerCaseIdentifiers()) {
      return StringUtil.toLowerCase(name);
    }

    if (databaseMetaData.storesUpperCaseIdentifiers()) {
      return StringUtil.toUpperCase(name);
    }

    return name;
  }
  @Test
  public void testMatchInParametersAndSqlTypeInfoWrapping() throws Exception {
    final String TABLE = "customers";
    final String USER = "******";

    ResultSet metaDataResultSet = mock(ResultSet.class);
    given(metaDataResultSet.next()).willReturn(true, false);
    given(metaDataResultSet.getString("TABLE_SCHEM")).willReturn(USER);
    given(metaDataResultSet.getString("TABLE_NAME")).willReturn(TABLE);
    given(metaDataResultSet.getString("TABLE_TYPE")).willReturn("TABLE");

    ResultSet columnsResultSet = mock(ResultSet.class);
    given(columnsResultSet.next()).willReturn(true, true, true, true, false);
    given(columnsResultSet.getString("COLUMN_NAME"))
        .willReturn("id", "name", "customersince", "version");
    given(columnsResultSet.getInt("DATA_TYPE"))
        .willReturn(Types.INTEGER, Types.VARCHAR, Types.DATE, Types.NUMERIC);
    given(columnsResultSet.getBoolean("NULLABLE")).willReturn(false, true, true, false);

    given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
    given(databaseMetaData.getDatabaseProductName()).willReturn("1.0");
    given(databaseMetaData.getUserName()).willReturn(USER);
    given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
    given(databaseMetaData.getTables(null, null, TABLE, null)).willReturn(metaDataResultSet);
    given(databaseMetaData.getColumns(null, USER, TABLE, null)).willReturn(columnsResultSet);

    MapSqlParameterSource map = new MapSqlParameterSource();
    map.addValue("id", 1);
    map.addValue("name", "Sven");
    map.addValue("customersince", new Date());
    map.addValue("version", 0);
    map.registerSqlType("customersince", Types.DATE);
    map.registerSqlType("version", Types.NUMERIC);

    context.setTableName(TABLE);
    context.processMetaData(dataSource, new ArrayList<>(), new String[] {});

    List<Object> values = context.matchInParameterValuesWithInsertColumns(map);

    assertEquals("wrong number of parameters: ", 4, values.size());
    assertTrue("id not wrapped with type info", values.get(0) instanceof Number);
    assertTrue("name not wrapped with type info", values.get(1) instanceof String);
    assertTrue("date wrapped with type info", values.get(2) instanceof SqlParameterValue);
    assertTrue("version wrapped with type info", values.get(3) instanceof SqlParameterValue);
    verify(metaDataResultSet, atLeastOnce()).next();
    verify(columnsResultSet, atLeastOnce()).next();
    verify(metaDataResultSet).close();
    verify(columnsResultSet).close();
  }
  @Test
  public void testTableWithSingleColumnGeneratedKey() throws Exception {
    final String TABLE = "customers";
    final String USER = "******";

    ResultSet metaDataResultSet = mock(ResultSet.class);
    given(metaDataResultSet.next()).willReturn(true, false);
    given(metaDataResultSet.getString("TABLE_SCHEM")).willReturn(USER);
    given(metaDataResultSet.getString("TABLE_NAME")).willReturn(TABLE);
    given(metaDataResultSet.getString("TABLE_TYPE")).willReturn("TABLE");

    ResultSet columnsResultSet = mock(ResultSet.class);
    given(columnsResultSet.next()).willReturn(true, false);
    given(columnsResultSet.getString("COLUMN_NAME")).willReturn("id");
    given(columnsResultSet.getInt("DATA_TYPE")).willReturn(Types.INTEGER);
    given(columnsResultSet.getBoolean("NULLABLE")).willReturn(false);

    given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
    given(databaseMetaData.getDatabaseProductName()).willReturn("1.0");
    given(databaseMetaData.getUserName()).willReturn(USER);
    given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
    given(databaseMetaData.getTables(null, null, TABLE, null)).willReturn(metaDataResultSet);
    given(databaseMetaData.getColumns(null, USER, TABLE, null)).willReturn(columnsResultSet);

    MapSqlParameterSource map = new MapSqlParameterSource();
    String[] keyCols = new String[] {"id"};
    context.setTableName(TABLE);
    context.processMetaData(dataSource, new ArrayList<>(), keyCols);
    List<Object> values = context.matchInParameterValuesWithInsertColumns(map);
    String insertString = context.createInsertString(keyCols);

    assertEquals("wrong number of parameters: ", 0, values.size());
    assertEquals(
        "empty insert not generated correctly", "INSERT INTO customers () VALUES()", insertString);
    verify(metaDataResultSet, atLeastOnce()).next();
    verify(columnsResultSet, atLeastOnce()).next();
    verify(metaDataResultSet).close();
    verify(columnsResultSet).close();
  }
Exemple #7
0
  public Map<String, List<String>> querySchema(DBConfig config) {
    Map<String, List<String>> map = new HashMap<String, List<String>>();
    List<String> schemaList = config.getSchemaList();
    Connection conn = null;
    DatabaseMetaData databaseMetaData = null;
    try {
      conn = ConnectionFactory.getInstance().getConnection(config);
      databaseMetaData = conn.getMetaData();
    } catch (Exception e) {
      e.printStackTrace();
      return map;
    }

    for (String schema : schemaList) {
      List<String> tableList = new ArrayList<String>();
      try {
        String localSchema = schema;
        if (databaseMetaData.storesLowerCaseIdentifiers()) {
          localSchema = localSchema == null ? null : localSchema.toLowerCase();
        } else if (databaseMetaData.storesUpperCaseIdentifiers()) {
          localSchema = localSchema == null ? null : localSchema.toUpperCase();
        }

        ResultSet rs = databaseMetaData.getTables(null, localSchema, null, null);
        while (rs.next()) {
          String tableName = rs.getString("TABLE_NAME");
          tableList.add(tableName);
          // System.out.println(tableName);
        }
        map.put(schema, tableList);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }

    return map;
  }
  public JSONObject execute(
      HttpServletRequest request,
      HttpServletResponse response,
      EntityManager em,
      EntityTransaction et)
      throws Exception {
    DatabaseNode dn = (DatabaseNode) IDManager.get().get(id);
    JSONObject results = new JSONObject();
    JSONArray meta = new JSONArray();
    JSONArray data = new JSONArray();

    String[] strs = {"Property", "Value"};

    for (int i = 0; i < strs.length; i++) {
      meta.put(strs[i]);
    }

    SQLDatabaseMetaData metaData = dn.getConn().getSQLMetaData();
    DatabaseMetaData jdbcmetadata = metaData.getJDBCMetaData();
    JSONArray record = new JSONArray();
    record.put("Database Product Name");
    try {
      record.put(metaData.getDatabaseProductName());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Database Product Version");
    try {
      record.put(metaData.getDatabaseProductVersion());

    } catch (Throwable e) {
      record.put("Unsupported");
    }

    data.put(record);

    record = new JSONArray();
    record.put("Driver Major Version");
    try {
      record.put(jdbcmetadata.getDriverMajorVersion());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Driver Minor Version");
    try {
      record.put(jdbcmetadata.getDriverMinorVersion());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Driver Name");
    try {
      record.put(metaData.getDriverName());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Driver Version");
    try {
      record.put(jdbcmetadata.getDriverVersion());
    } catch (Throwable e) {
      record.put("Unsupported");
    }

    data.put(record);

    record = new JSONArray();
    record.put("Username");
    try {
      record.put(metaData.getUserName());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("URL");
    try {
      record.put(jdbcmetadata.getURL());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    //		record=new JSONArray();
    //		record.put("Auto Commit");
    //		try{
    //			record.put(dn.getConn().getAutoCommit());
    //
    //		}catch(Throwable e){record.put("Unsupported");}
    //		data.put(record);

    record = new JSONArray();
    record.put("All Procedures Are Callable");
    try {
      record.put(jdbcmetadata.allProceduresAreCallable());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("All Tables Are Selectable");
    try {
      record.put(jdbcmetadata.allTablesAreSelectable());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Nulls are sorted High");
    try {
      record.put(jdbcmetadata.nullsAreSortedHigh());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Nulls are sorted Low");
    try {
      record.put(jdbcmetadata.nullsAreSortedLow());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Nulls are sorted at Start");
    try {
      record.put(jdbcmetadata.nullsAreSortedAtStart());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Nulls are sorted at End");
    try {
      record.put(jdbcmetadata.nullsAreSortedAtEnd());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Is Read Only");
    try {
      record.put(jdbcmetadata.isReadOnly());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Result Set Holdability");
    try {
      record.put(jdbcmetadata.getResultSetHoldability());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Uses Local Files");
    try {
      record.put(jdbcmetadata.usesLocalFiles());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Uses Local File per Table");

    try {
      record.put(jdbcmetadata.usesLocalFilePerTable());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Mixed Case Identifiers");

    try {
      record.put(jdbcmetadata.supportsMixedCaseIdentifiers());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Stores Upper Case Identifiers");

    try {
      record.put(jdbcmetadata.storesUpperCaseIdentifiers());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Stores Lower Case Identifiers");
    try {
      record.put(jdbcmetadata.storesLowerCaseIdentifiers());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Stores Mixed Case Identifiers");
    try {
      record.put(jdbcmetadata.storesMixedCaseIdentifiers());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Mixed Case Quoted Identifiers");
    try {
      record.put(jdbcmetadata.supportsMixedCaseQuotedIdentifiers());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Stores Upper Case Quoted Identifiers");

    try {
      record.put(jdbcmetadata.storesUpperCaseQuotedIdentifiers());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Stores Lower Case Quoted Identifiers");

    try {
      record.put(jdbcmetadata.storesLowerCaseQuotedIdentifiers());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Stores Mixed Case Quoted Identifiers");
    try {
      record.put(jdbcmetadata.storesMixedCaseQuotedIdentifiers());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Identifier Quote");
    try {
      record.put(jdbcmetadata.getIdentifierQuoteString());
    } catch (Throwable e) {
    }
    data.put(record);

    record = new JSONArray();
    record.put("Search String Escape");
    try {
      record.put(jdbcmetadata.getSearchStringEscape());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Extra Name Characters");
    try {
      record.put(jdbcmetadata.getExtraNameCharacters());

    } catch (Throwable e) {
      record.put("Unsupported");
    }

    data.put(record);

    record = new JSONArray();
    record.put("Supports Alter Table With Add Column");
    try {
      record.put(jdbcmetadata.supportsAlterTableWithAddColumn());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Alter Table With Drop Column");
    try {

      record.put(jdbcmetadata.supportsAlterTableWithDropColumn());

    } catch (Throwable e) {
      record.put("Unsupported");
    }

    data.put(record);

    record = new JSONArray();
    record.put("Supports Column Aliasing");
    try {

      record.put(jdbcmetadata.supportsColumnAliasing());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Null Plus Non Null Is Null");
    try {

      record.put(jdbcmetadata.nullPlusNonNullIsNull());

    } catch (Throwable e) {
      record.put("Unsupported");
    }

    data.put(record);

    record = new JSONArray();
    record.put("Supports Convert");
    try {

      record.put(jdbcmetadata.supportsConvert());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Table Correlation Names");
    try {

      record.put(jdbcmetadata.supportsTableCorrelationNames());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Expressions in Order By");
    try {

      record.put(jdbcmetadata.supportsExpressionsInOrderBy());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Order By Unrelated");
    try {

      record.put(jdbcmetadata.supportsOrderByUnrelated());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Group By");
    try {

      record.put(jdbcmetadata.supportsGroupBy());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Group By Unrelated");
    try {

      record.put(jdbcmetadata.supportsGroupByUnrelated());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Group By Beyond Select");
    try {
      record.put(jdbcmetadata.supportsGroupByBeyondSelect());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Like Escape Clause");
    try {
      record.put(jdbcmetadata.supportsLikeEscapeClause());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Multiple Result Sets");
    try {
      record.put(jdbcmetadata.supportsMultipleResultSets());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Multiple Open Results");
    try {
      record.put(jdbcmetadata.supportsMultipleOpenResults());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Multiple Transactions");
    try {

      record.put(jdbcmetadata.supportsMultipleTransactions());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Non Nullable Columns");
    try {
      record.put(jdbcmetadata.supportsNonNullableColumns());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Minimum SQL Grammar");
    try {
      record.put(jdbcmetadata.supportsMinimumSQLGrammar());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Core SQL Grammar");
    try {
      record.put(jdbcmetadata.supportsCoreSQLGrammar());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Extended SQL Grammar");
    try {

      record.put(jdbcmetadata.supportsExtendedSQLGrammar());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports ANSI92 Entry Level SQL");
    try {
      record.put(jdbcmetadata.supportsANSI92EntryLevelSQL());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports ANSI92 Intermediate SQL");
    try {

      record.put(jdbcmetadata.supportsANSI92IntermediateSQL());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports ANSI92 Full SQL");
    try {

      record.put(jdbcmetadata.supportsANSI92FullSQL());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Integrity Enhancement Facility");
    try {

      record.put(jdbcmetadata.supportsIntegrityEnhancementFacility());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Outer Joins");
    try {

      record.put(jdbcmetadata.supportsOuterJoins());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Full Outer Joins");
    try {

      record.put(jdbcmetadata.supportsFullOuterJoins());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Limited Outer Joins");
    try {
      record.put(jdbcmetadata.supportsLimitedOuterJoins());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Schema Term");
    try {
      record.put(jdbcmetadata.getSchemaTerm());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Procedure Term");
    try {
      record.put(jdbcmetadata.getProcedureTerm());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Catalog Term");
    try {
      record.put(jdbcmetadata.getCatalogTerm());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Is Catalog at Start");
    try {
      record.put(jdbcmetadata.isCatalogAtStart());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Catalog Separator");
    try {
      record.put(jdbcmetadata.getCatalogSeparator());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Schemas In Data Manipulation");
    try {
      record.put(jdbcmetadata.supportsSchemasInDataManipulation());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Schemas In Procedure Calls");
    try {
      record.put(jdbcmetadata.supportsSchemasInProcedureCalls());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Schemas In Table Definitions");
    try {
      record.put(jdbcmetadata.supportsSchemasInTableDefinitions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Schemas In Index Definitions");
    try {
      record.put(jdbcmetadata.supportsSchemasInIndexDefinitions());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Schemas In Privilege Definitions");
    try {
      record.put(jdbcmetadata.supportsSchemasInPrivilegeDefinitions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Catalogs In Data Manipulation");
    try {
      record.put(jdbcmetadata.supportsCatalogsInDataManipulation());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Catalogs In Procedure Calls");
    try {
      record.put(jdbcmetadata.supportsCatalogsInProcedureCalls());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Catalogs In Table Definitions");
    try {
      record.put(jdbcmetadata.supportsCatalogsInTableDefinitions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Catalogs In Index Definitions");
    try {
      record.put(jdbcmetadata.supportsCatalogsInIndexDefinitions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Catalogs In Privilege Definitions");
    try {
      record.put(jdbcmetadata.supportsCatalogsInPrivilegeDefinitions());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Positioned Delete");
    try {
      record.put(jdbcmetadata.supportsPositionedDelete());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Positioned Update");
    try {
      record.put(jdbcmetadata.supportsPositionedUpdate());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Select For Update");
    try {
      record.put(jdbcmetadata.supportsSelectForUpdate());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Stored Procedures");
    try {
      record.put(jdbcmetadata.supportsStoredProcedures());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Subqueries In Comparisons");

    try {
      record.put(jdbcmetadata.supportsSubqueriesInComparisons());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Subqueries In Exists");
    try {
      record.put(jdbcmetadata.supportsSubqueriesInExists());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Subqueries in IN Statements");
    try {
      record.put(jdbcmetadata.supportsSubqueriesInIns());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Correlated Subqueries");
    try {
      record.put(jdbcmetadata.supportsCorrelatedSubqueries());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Union");
    try {
      record.put(jdbcmetadata.supportsUnion());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Union All");
    try {
      record.put(jdbcmetadata.supportsUnionAll());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Open Cursors Across Commit");
    try {
      record.put(jdbcmetadata.supportsOpenCursorsAcrossCommit());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Open Cursors Across Rollback");
    try {
      record.put(jdbcmetadata.supportsOpenCursorsAcrossRollback());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Open Statements Across Commit");
    try {
      record.put(jdbcmetadata.supportsOpenStatementsAcrossCommit());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Open Statements Across Rollback");
    try {
      record.put(jdbcmetadata.supportsOpenStatementsAcrossRollback());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Binary Literal Length");
    try {
      record.put(jdbcmetadata.getMaxBinaryLiteralLength());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Char Literal Length");
    try {
      record.put(jdbcmetadata.getMaxCharLiteralLength());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Column Name Length");
    try {
      record.put(jdbcmetadata.getMaxColumnNameLength());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Columns In Group By");
    try {
      record.put(jdbcmetadata.getMaxColumnsInGroupBy());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Columns In Index");
    try {
      record.put(jdbcmetadata.getMaxColumnsInIndex());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Columns In Order By");
    try {
      record.put(jdbcmetadata.getMaxColumnsInOrderBy());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Columns In Select");
    try {

      record.put(jdbcmetadata.getMaxColumnsInSelect());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Columns In Table");
    try {

      record.put(jdbcmetadata.getMaxColumnsInTable());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Connections");
    try {
      record.put(jdbcmetadata.getMaxConnections());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Cursor Name Length");
    try {
      record.put(jdbcmetadata.getMaxCursorNameLength());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Index Length");
    try {
      record.put(jdbcmetadata.getMaxIndexLength());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Schema Name Length");
    try {
      record.put(jdbcmetadata.getMaxSchemaNameLength());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Procedure Name Length");
    try {

      record.put(jdbcmetadata.getMaxProcedureNameLength());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Catalog Name Length");
    try {

      record.put(jdbcmetadata.getMaxCatalogNameLength());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Row Size");
    try {

      record.put(jdbcmetadata.getMaxRowSize());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Row Size Include Blobs");
    try {

      record.put(jdbcmetadata.doesMaxRowSizeIncludeBlobs());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Statement Length");
    try {

      record.put(jdbcmetadata.getMaxStatementLength());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Statements");
    try {
      record.put(jdbcmetadata.getMaxStatements());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Table Name Length");
    try {
      record.put(jdbcmetadata.getMaxTableNameLength());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max Tables In Select");
    try {
      record.put(jdbcmetadata.getMaxTablesInSelect());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Max User Name Length");
    try {
      record.put(jdbcmetadata.getMaxUserNameLength());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Default Transaction Isolation");
    try {

      int isol = jdbcmetadata.getDefaultTransactionIsolation();
      String is = null;
      switch (isol) {
        case java.sql.Connection.TRANSACTION_NONE:
          {
            is = "TRANSACTION_NONE";
            break;
          }
        case java.sql.Connection.TRANSACTION_READ_COMMITTED:
          {
            is = "TRANSACTION_READ_COMMITTED";
            break;
          }
        case java.sql.Connection.TRANSACTION_READ_UNCOMMITTED:
          {
            is = "TRANSACTION_READ_UNCOMMITTED";
            break;
          }
        case java.sql.Connection.TRANSACTION_REPEATABLE_READ:
          {
            is = "TRANSACTION_REPEATABLE_READ";
            break;
          }
        case java.sql.Connection.TRANSACTION_SERIALIZABLE:
          {
            is = "TRANSACTION_SERIALIZABLE";
            break;
          }
        default:
          {
            is = "";
            break;
          }
      }
      record.put(is);

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Transactions");
    try {
      record.put(jdbcmetadata.supportsTransactions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Data Definition and Data Manipulation Transactions");
    try {
      record.put(jdbcmetadata.supportsDataDefinitionAndDataManipulationTransactions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Different Table Correlation Names");
    try {
      record.put(jdbcmetadata.supportsDifferentTableCorrelationNames());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Data Manipulation Transactions Only");
    try {
      record.put(jdbcmetadata.supportsDataManipulationTransactionsOnly());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Data Definition Causes Transaction Commit");
    try {
      record.put(jdbcmetadata.dataDefinitionCausesTransactionCommit());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Data Definition Ignored in Transactions");
    try {
      record.put(jdbcmetadata.dataDefinitionIgnoredInTransactions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Batch Updates");
    try {
      record.put(jdbcmetadata.supportsBatchUpdates());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Savepoints");
    try {
      record.put(jdbcmetadata.supportsSavepoints());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Named Parameters");
    try {
      record.put(jdbcmetadata.supportsNamedParameters());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Get Generated Keys");
    try {
      record.put(jdbcmetadata.supportsGetGeneratedKeys());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Database Major Version");
    try {
      record.put(jdbcmetadata.getDatabaseMajorVersion());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Database Minor Version");
    try {
      record.put(jdbcmetadata.getDatabaseMinorVersion());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("JDBC Minor Version");
    try {
      record.put(jdbcmetadata.getJDBCMinorVersion());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("JDBC Major Version");
    try {
      record.put(jdbcmetadata.getJDBCMajorVersion());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("SQL State Type");
    try {
      int sqlStateType = jdbcmetadata.getSQLStateType();
      String is = null;
      switch (sqlStateType) {
        case DatabaseMetaData.sqlStateXOpen:
          {
            is = "sqlStateXOpen";
            break;
          }
        case DatabaseMetaData.sqlStateSQL:
          {
            is = "sqlStateSQL";
            break;
          }
        default:
          is = "";
      }
      record.put(is);

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Locators Update Copy");
    try {
      record.put(jdbcmetadata.locatorsUpdateCopy());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Statement Pooling");
    try {
      record.put(jdbcmetadata.supportsStatementPooling());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("SQL Keywords");
    try {

      record.put(jdbcmetadata.getSQLKeywords());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Numeric Functions");
    try {
      record.put(jdbcmetadata.getNumericFunctions());

    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("String Functions");
    try {
      record.put(jdbcmetadata.getStringFunctions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("System Functions");
    try {
      record.put(jdbcmetadata.getSystemFunctions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Time and Date Functions");
    try {
      record.put(jdbcmetadata.getTimeDateFunctions());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Auto Commit Failure Closes All ResultSets");
    try {
      record.put(jdbcmetadata.autoCommitFailureClosesAllResultSets());
    } catch (Throwable e) {
      record.put("Unsupported");
    }

    data.put(record);

    record = new JSONArray();
    record.put("Supports Stored Functions Using Call Syntax");
    try {
      record.put(jdbcmetadata.supportsStoredFunctionsUsingCallSyntax());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Supports Subqueries in Quantified Expressions");
    try {
      record.put(jdbcmetadata.supportsSubqueriesInQuantifieds());
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("Client Info Properties");
    try {
      ResultSet rs = jdbcmetadata.getClientInfoProperties();
      while (rs.next()) {
        rs.getString("NAME");
      }
      rs.close();
    } catch (Throwable e) {
      record.put("Unsupported");
    }
    data.put(record);

    record = new JSONArray();
    record.put("ROWID Lifetime");
    try {
      RowIdLifetime rid = jdbcmetadata.getRowIdLifetime();
      if (rid.equals(RowIdLifetime.ROWID_UNSUPPORTED)) record.put("ROWID_UNSUPPORTED");
      else if (rid.equals(RowIdLifetime.ROWID_VALID_FOREVER)) record.put("ROWID_VALID_FOREVER");
      else if (rid.equals(RowIdLifetime.ROWID_VALID_OTHER)) record.put("ROWID_VALID_OTHER");
      else if (rid.equals(RowIdLifetime.ROWID_VALID_SESSION)) record.put("ROWID_VALID_SESSION");
      else if (rid.equals(RowIdLifetime.ROWID_VALID_TRANSACTION))
        record.put("ROWID_VALID_TRANSACTION");
      else record.put("");
    } catch (Throwable e) {
      record.put("Unsupported");
    }

    data.put(record);

    results.put("meta", meta);
    results.put("data", data);
    return results;
  }
  private void prepareTables() throws NamingException, SQLException {
    if (_createTables) {
      boolean autocommit = true;
      Connection connection = getConnection();
      try (Statement stmt = connection.createStatement()) {
        autocommit = connection.getAutoCommit();
        connection.setAutoCommit(false);
        DatabaseMetaData metaData = connection.getMetaData();

        // check if tables exist
        String tableName =
            (metaData.storesLowerCaseIdentifiers()
                ? _userTableName.toLowerCase(Locale.ENGLISH)
                : (metaData.storesUpperCaseIdentifiers()
                    ? _userTableName.toUpperCase(Locale.ENGLISH)
                    : _userTableName));
        try (ResultSet result = metaData.getTables(null, null, tableName, null)) {
          if (!result.next()) {
            // user table default
            /*
             * create table _userTableName (_userTableKey integer,
             * _userTableUserField varchar(100) not null unique,
             * _userTablePasswordField varchar(20) not null, primary key(_userTableKey));
             */
            stmt.executeUpdate(
                "create table "
                    + _userTableName
                    + "("
                    + _userTableKey
                    + " integer,"
                    + _userTableUserField
                    + " varchar(100) not null unique,"
                    + _userTablePasswordField
                    + " varchar(20) not null, primary key("
                    + _userTableKey
                    + "))");
            if (LOG.isDebugEnabled()) LOG.debug("Created table " + _userTableName);
          }
        }

        tableName =
            (metaData.storesLowerCaseIdentifiers()
                ? _roleTableName.toLowerCase(Locale.ENGLISH)
                : (metaData.storesUpperCaseIdentifiers()
                    ? _roleTableName.toUpperCase(Locale.ENGLISH)
                    : _roleTableName));
        try (ResultSet result = metaData.getTables(null, null, tableName, null)) {
          if (!result.next()) {
            // role table default
            /*
             * create table _roleTableName (_roleTableKey integer,
             * _roleTableRoleField varchar(100) not null unique, primary key(_roleTableKey));
             */
            String str =
                "create table "
                    + _roleTableName
                    + " ("
                    + _roleTableKey
                    + " integer, "
                    + _roleTableRoleField
                    + " varchar(100) not null unique, primary key("
                    + _roleTableKey
                    + "))";
            stmt.executeUpdate(str);
            if (LOG.isDebugEnabled()) LOG.debug("Created table " + _roleTableName);
          }
        }

        tableName =
            (metaData.storesLowerCaseIdentifiers()
                ? _userRoleTableName.toLowerCase(Locale.ENGLISH)
                : (metaData.storesUpperCaseIdentifiers()
                    ? _userRoleTableName.toUpperCase(Locale.ENGLISH)
                    : _userRoleTableName));
        try (ResultSet result = metaData.getTables(null, null, tableName, null)) {
          if (!result.next()) {
            // user-role table
            /*
             * create table _userRoleTableName (_userRoleTableUserKey integer,
             * _userRoleTableRoleKey integer,
             * primary key (_userRoleTableUserKey, _userRoleTableRoleKey));
             *
             * create index idx_user_role on _userRoleTableName (_userRoleTableUserKey);
             */
            stmt.executeUpdate(
                "create table "
                    + _userRoleTableName
                    + " ("
                    + _userRoleTableUserKey
                    + " integer, "
                    + _userRoleTableRoleKey
                    + " integer, "
                    + "primary key ("
                    + _userRoleTableUserKey
                    + ", "
                    + _userRoleTableRoleKey
                    + "))");
            stmt.executeUpdate(
                "create index indx_user_role on "
                    + _userRoleTableName
                    + "("
                    + _userRoleTableUserKey
                    + ")");
            if (LOG.isDebugEnabled())
              LOG.debug("Created table " + _userRoleTableName + " and index");
          }
        }
        connection.commit();
      } finally {
        try {
          connection.setAutoCommit(autocommit);
        } catch (SQLException e) {
          if (LOG.isDebugEnabled()) LOG.debug("Prepare tables", e);
        } finally {
          try {
            connection.close();
          } catch (SQLException e) {
            if (LOG.isDebugEnabled()) LOG.debug("Prepare tables", e);
          }
        }
      }
    } else if (LOG.isDebugEnabled()) {
      LOG.debug("createTables false");
    }
  }
Exemple #10
0
  /**
   * Sync this column with the database
   *
   * @return
   */
  public String syncDatabase() {

    MTable table = new MTable(getCtx(), getAD_Table_ID(), get_TrxName());
    table.set_TrxName(get_TrxName()); // otherwise table.getSQLCreate may miss current column
    if (table.get_ID() == 0)
      throw new AdempiereException("@NotFound@ @AD_Table_ID@ " + getAD_Table_ID());

    // Find Column in Database
    Connection conn = null;
    try {
      conn = DB.getConnectionRO();
      DatabaseMetaData md = conn.getMetaData();
      String catalog = DB.getDatabase().getCatalog();
      String schema = DB.getDatabase().getSchema();
      String tableName = table.getTableName();
      if (md.storesUpperCaseIdentifiers()) {
        tableName = tableName.toUpperCase();
      } else if (md.storesLowerCaseIdentifiers()) {
        tableName = tableName.toLowerCase();
      }
      int noColumns = 0;
      String sql = null;
      //
      ResultSet rs = md.getColumns(catalog, schema, tableName, null);
      while (rs.next()) {
        noColumns++;
        String columnName = rs.getString("COLUMN_NAME");
        if (!columnName.equalsIgnoreCase(getColumnName())) continue;

        // update existing column
        boolean notNull = DatabaseMetaData.columnNoNulls == rs.getInt("NULLABLE");
        sql = getSQLModify(table, isMandatory() != notNull);
        break;
      }
      rs.close();
      rs = null;

      // No Table
      if (noColumns == 0) sql = table.getSQLCreate();
      // No existing column
      else if (sql == null) sql = getSQLAdd(table);

      if (sql == null) return "No sql";

      int no = 0;
      if (sql.indexOf(DB.SQLSTATEMENT_SEPARATOR) == -1) {
        DB.executeUpdateEx(sql, get_TrxName());
      } else {
        String statements[] = sql.split(DB.SQLSTATEMENT_SEPARATOR);
        for (int i = 0; i < statements.length; i++) {
          DB.executeUpdateEx(statements[i], get_TrxName());
        }
      }

      return sql;

    } catch (SQLException e) {
      throw new AdempiereException(e);
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (Exception e) {
        }
      }
    }
  }
Exemple #11
0
  private void tryReadMetaData(Connection conn, String oCatalog, String oSchema, String tableName)
      throws SQLException {

    DatabaseMetaData meta = conn.getMetaData();
    storesLowerCase = meta.storesLowerCaseIdentifiers();
    storesMixedCase = meta.storesMixedCaseIdentifiers();
    storesMixedCaseQuoted = meta.storesMixedCaseQuotedIdentifiers();
    supportsMixedCaseIdentifiers = meta.supportsMixedCaseIdentifiers();

    ResultSet rs = meta.getTables(oCatalog, oSchema, tableName, null);
    if (rs.next() && rs.next()) {
      throw DbException.get(ErrorCode.SCHEMA_NAME_MUST_MATCH, tableName);
    }
    rs.close();
    rs = meta.getColumns(null, null, tableName, null);
    int i = 0;
    ArrayList<Column> columnList = New.arrayList();
    HashMap<String, Column> columnMap = New.hashMap();
    String catalog = null, schema = null;
    while (rs.next()) {
      String thisCatalog = rs.getString("TABLE_CAT");
      if (catalog == null) {
        catalog = thisCatalog;
      }
      String thisSchema = rs.getString("TABLE_SCHEM");
      if (schema == null) {
        schema = thisSchema;
      }
      if (!StringUtils.equals(catalog, thisCatalog) || !StringUtils.equals(schema, thisSchema)) {
        // if the table exists in multiple schemas or tables,
        // use the alternative solution
        columnMap.clear();
        columnList.clear();
        break;
      }
      String n = rs.getString("COLUMN_NAME");
      n = convertColumnName(n);
      int sqlType = rs.getInt("DATA_TYPE");
      long precision = rs.getInt("COLUMN_SIZE");
      precision = convertPrecision(sqlType, precision);
      int scale = rs.getInt("DECIMAL_DIGITS");
      scale = convertScale(sqlType, scale);
      int displaySize = MathUtils.convertLongToInt(precision);
      int type = DataType.convertSQLTypeToValueType(sqlType);
      Column col = new Column(n, type, precision, scale, displaySize);
      col.setTable(this, i++);
      columnList.add(col);
      columnMap.put(n, col);
    }
    rs.close();
    // check if the table is accessible
    Statement stat = null;
    try {
      stat = conn.createStatement();
      rs = stat.executeQuery("SELECT * FROM " + tableName + " T WHERE 1=0");
      if (columnList.size() == 0) {
        // alternative solution
        ResultSetMetaData rsMeta = rs.getMetaData();
        for (i = 0; i < rsMeta.getColumnCount(); ) {
          String n = rsMeta.getColumnName(i + 1);
          n = convertColumnName(n);
          int sqlType = rsMeta.getColumnType(i + 1);
          long precision = rsMeta.getPrecision(i + 1);
          precision = convertPrecision(sqlType, precision);
          int scale = rsMeta.getScale(i + 1);
          scale = convertScale(sqlType, scale);
          int displaySize = rsMeta.getColumnDisplaySize(i + 1);
          int type = DataType.getValueTypeFromResultSet(rsMeta, i + 1);
          Column col = new Column(n, type, precision, scale, displaySize);
          col.setTable(this, i++);
          columnList.add(col);
          columnMap.put(n, col);
        }
      }
      rs.close();
    } catch (Exception e) {
      throw DbException.get(
          ErrorCode.TABLE_OR_VIEW_NOT_FOUND_1, e, tableName + "(" + e.toString() + ")");
    } finally {
      JdbcUtils.closeSilently(stat);
    }
    Column[] cols = new Column[columnList.size()];
    columnList.toArray(cols);
    setColumns(cols);
    // create scan index

    // load primary keys
    try {
      rs = meta.getPrimaryKeys(null, null, tableName);
    } catch (Exception e) {
      // Some ODBC bridge drivers don't support it:
      // some combinations of "DataDirect SequeLink(R) for JDBC"
      // http://www.datadirect.com/index.ssp
      rs = null;
    }
    String pkName = "";
    ArrayList<Column> list;
    if (rs != null && rs.next()) {
      // the problem is, the rows are not sorted by KEY_SEQ
      list = New.arrayList();
      do {
        int idx = rs.getInt("KEY_SEQ");
        if (pkName == null) {
          pkName = rs.getString("PK_NAME");
        }
        while (list.size() < idx) {
          list.add(null);
        }
        String col = rs.getString("COLUMN_NAME");
        col = convertColumnName(col);
        Column column = columnMap.get(col);
        if (idx == 0) {
          // workaround for a bug in the SQLite JDBC driver
          list.add(column);
        } else {
          list.set(idx - 1, column);
        }
      } while (rs.next());
      addIndex(pkName, list, IndexType.createPrimaryKey(false));
      rs.close();
    }

    try {
      rs = meta.getIndexInfo(null, null, tableName, false, true);
    } catch (Exception e) {
      // Oracle throws an exception if the table is not found or is a
      // SYNONYM
      rs = null;
    }
    String indexName = null;
    list = New.arrayList();
    IndexType indexType = null;
    if (rs != null) {
      while (rs.next()) {
        if (rs.getShort("TYPE") == DatabaseMetaData.tableIndexStatistic) {
          // ignore index statistics
          continue;
        }
        String newIndex = rs.getString("INDEX_NAME");
        if (pkName.equals(newIndex)) {
          continue;
        }
        if (indexName != null && !indexName.equals(newIndex)) {
          addIndex(indexName, list, indexType);
          indexName = null;
        }
        if (indexName == null) {
          indexName = newIndex;
          list.clear();
        }
        boolean unique = !rs.getBoolean("NON_UNIQUE");
        indexType = unique ? IndexType.createUnique(false) : IndexType.createNonUnique();
        String col = rs.getString("COLUMN_NAME");
        col = convertColumnName(col);
        Column column = columnMap.get(col);
        list.add(column);
      }
      rs.close();
    }
    if (indexName != null) {
      addIndex(indexName, list, indexType);
    }
    shardingKeyIndex();
  }
 public boolean storesLowerCaseIdentifiers() throws SQLException {
   return throwExceptionDelegate.storesLowerCaseIdentifiers();
 }
Exemple #13
0
  public List<Table> queryFields(DBConfig config) {
    List<Table> tableInfoList = new ArrayList<Table>();
    Connection conn = null;
    try {
      List<TableConfiguration> tableList = (List<TableConfiguration>) config.getTableList();
      if (tableList == null || tableList.size() <= 0) return tableInfoList;

      conn = ConnectionFactory.getInstance().getConnection(config);
      DatabaseMetaData databaseMetaData = conn.getMetaData();
      for (TableConfiguration table : tableList) {
        Table tableInfo = new Table();

        String localCatalog = table.getCatalog();
        String localSchema = table.getSchema();
        String localTableName = table.getTableName();
        if (databaseMetaData.storesLowerCaseIdentifiers()) {
          localCatalog = localCatalog == null ? null : localCatalog.toLowerCase();
          localSchema = localSchema == null ? null : localSchema.toLowerCase();
          localTableName = localTableName == null ? null : localTableName.toLowerCase();
        } else if (databaseMetaData.storesUpperCaseIdentifiers()) {
          localCatalog = localCatalog == null ? null : localCatalog.toUpperCase();
          localSchema = localSchema == null ? null : localSchema.toUpperCase();
          localTableName = localTableName == null ? null : localTableName.toUpperCase();
        }

        Statement stmt = conn.createStatement();
        ResultSet tableRs = stmt.executeQuery("SHOW CREATE TABLE " + localTableName);
        if (tableRs != null && tableRs.next()) {
          String create = tableRs.getString(2);
          String comment = parse(create);
          tableInfo.setComment(comment);
        }

        ResultSet rs = databaseMetaData.getColumns(localCatalog, localSchema, localTableName, null);
        tableInfo.setSerialVersionUID(System.nanoTime() + "L");
        while (rs.next()) {
          tableInfo.setCatalog(rs.getString("TABLE_CAT"));
          tableInfo.setSchema(rs.getString("TABLE_SCHEM"));
          tableInfo.setName(rs.getString("TABLE_NAME"));
          tableInfo.setCode(rs.getString("TABLE_NAME"));

          Column introspectedColumn = new Column();
          introspectedColumn.setTableAlias(table.getTableName());
          introspectedColumn.setName(rs.getString("COLUMN_NAME"));
          introspectedColumn.setJdbcType(rs.getInt("DATA_TYPE"));
          introspectedColumn.setDataType(
              JdbcTypeNameTranslator.getJdbcTypeName(rs.getInt("DATA_TYPE"))); // $NON-NLS-1$
          introspectedColumn.setLength(rs.getInt("COLUMN_SIZE")); // $NON-NLS-1$
          introspectedColumn.setCode(rs.getString("COLUMN_NAME"));
          /*introspectedColumn.setActualColumnName(rs
          .getString("COLUMN_NAME"));*/
          //$NON-NLS-1$
          introspectedColumn.setNullable(
              rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable); // $NON-NLS-1$
          introspectedColumn.setScale(rs.getInt("DECIMAL_DIGITS")); // $NON-NLS-1$
          introspectedColumn.setComment(rs.getString("REMARKS"));

          tableInfo.addColumn(introspectedColumn);

          PropertyBean pb = new PropertyBean();

          pb.setName(convertFirstUpper(getFieldName(rs.getString("COLUMN_NAME"))));
          pb.setType(JdbcType2Java.calculateJavaType(introspectedColumn));
          String importType = JdbcType2Java.importJavaType(introspectedColumn);
          if (importType != null && !importType.equals("")) {
            if (importType.indexOf("java.lang") < 0
                && !tableInfo.getImportList().contains(importType))
              tableInfo.getImportList().add(importType);
          }
          tableInfo.getPropertyBeanList().add(pb);
        }
        closeResultSet(rs);

        rs = databaseMetaData.getPrimaryKeys(localCatalog, localSchema, localTableName);
        while (rs.next()) {
          tableInfo.addPrimaryKeyColumn(rs.getString("COLUMN_NAME"));
        }
        closeResultSet(rs);
        tableInfoList.add(tableInfo);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }

    return tableInfoList;
  }