/**
   * Looks up a column in the database. If the column and table are not found, they are created, and
   * added to the schema. This is prevent foreign key relationships from having a null pointer.
   */
  private Column lookupOrCreateColumn(
      final String catalogName,
      final String schemaName,
      final String tableName,
      final String columnName) {
    Column column = null;

    final SchemaReference schema = new SchemaReference(catalogName, schemaName);
    final Optional<MutableTable> tableOptional = catalog.lookupTable(schema, tableName);
    if (tableOptional.isPresent()) {
      final Table table = tableOptional.get();
      final Optional<? extends Column> columnOptional = table.lookupColumn(columnName);
      if (columnOptional.isPresent()) {
        column = columnOptional.get();
      }
    }

    if (column == null) {
      // Create the table and column, but do not add it to the schema
      final Table table = new TablePartial(schema, tableName);
      column = new ColumnPartial(table, columnName);
      ((TablePartial) table).addColumn(column);

      LOGGER.log(
          Level.FINER,
          String.format(
              "Creating column reference for a column that is referenced by a foreign key: %s",
              column.getFullName()));
    }
    return column;
  }
 @Override
 public boolean equals(final Object obj) {
   if (this == obj) {
     return true;
   }
   if (obj == null) {
     return false;
   }
   if (getClass() != obj.getClass()) {
     return false;
   }
   final WeakAssociation other = (WeakAssociation) obj;
   if (foreignKeyColumn == null) {
     if (other.foreignKeyColumn != null) {
       return false;
     }
   } else if (!foreignKeyColumn.equals(other.foreignKeyColumn)) {
     return false;
   }
   if (primaryKeyColumn == null) {
     if (other.primaryKeyColumn != null) {
       return false;
     }
   } else if (!primaryKeyColumn.equals(other.primaryKeyColumn)) {
     return false;
   }
   return true;
 }
Esempio n. 3
0
  public static void main(final String[] args) throws Exception {
    // Create a database connection
    final DataSource dataSource =
        new DatabaseConnectionOptions(
            "org.hsqldb.jdbcDriver", "jdbc:hsqldb:hsql://localhost:9001/schemacrawler");
    final Connection connection = dataSource.getConnection("sa", "");

    // Create the options
    final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
    // Set what details are required in the schema - this affects the
    // time taken to crawl the schema
    options.setSchemaInfoLevel(SchemaInfoLevel.standard());
    options.setRoutineInclusionRule(new InclusionRule(InclusionRule.NONE, InclusionRule.ALL));
    options.setSchemaInclusionRule(new InclusionRule("PUBLIC.BOOKS", InclusionRule.NONE));

    // Get the schema definition
    final Database database = SchemaCrawlerUtility.getDatabase(connection, options);

    for (final Schema schema : database.getSchemas()) {
      System.out.println(schema);
      for (final Table table : database.getTables(schema)) {
        System.out.print("o--> " + table);
        if (table instanceof View) {
          System.out.println(" (VIEW)");
        } else {
          System.out.println();
        }

        for (final Column column : table.getColumns()) {
          System.out.println("     o--> " + column + " (" + column.getColumnDataType() + ")");
        }
      }
    }
  }
  /**
   * The lint that does the job
   *
   * @param table table
   * @param connection connection
   * @throws SchemaCrawlerException SchemaCrawlerException
   */
  @Override
  protected void lint(final Table table, final Connection connection)
      throws SchemaCrawlerException {

    try (Statement stmt = connection.createStatement()) {
      String sql;
      List<Column> columns = table.getColumns();
      for (Column column : columns) {
        if (LintUtils.isSqlTypeTextBased(
            column.getColumnDataType().getJavaSqlType().getJavaSqlType())) {

          sql = "select " + column.getName() + " from " + table.getName();
          LOGGER.log(Level.INFO, "SQL : {0}", sql);

          ResultSet rs = stmt.executeQuery(sql);
          boolean found = false;
          while (rs.next() && !found) {
            String data = rs.getString(column.getName());

            if (JSonUtils.isJsonContent(data)) {
              LOGGER.log(
                  Level.INFO, "Adding lint as data is JSON but column type is not JSONB or JSON.");
              addLint(table, getDescription(), column.getFullName());
              found = true;
            }
          }
        }
      }

    } catch (SQLException ex) {
      LOGGER.severe(ex.getMessage());
    }
  }
Esempio n. 5
0
  /**
   * {@inheritDoc}
   *
   * <p>Note: Since indexes are not always explicitly named in databases, the sorting routine orders
   * the indexes by the names of the columns in the index.
   */
  @Override
  public int compareTo(final NamedObject obj) {
    if (obj == null) {
      return -1;
    }

    final Index other = (Index) obj;
    int comparison = 0;
    final Column[] thisColumns = getColumns();
    final Column[] otherColumns = other.getColumns();

    if (comparison == 0) {
      comparison = thisColumns.length - otherColumns.length;
    }
    if (comparison == 0) {
      for (int i = 0; i < thisColumns.length; i++) {
        final Column thisColumn = thisColumns[i];
        final Column otherColumn = otherColumns[i];
        if (comparison == 0) {
          comparison = thisColumn.compareTo(otherColumn);
        } else {
          break;
        }
      }
    }
    if (comparison == 0) {
      comparison = super.compareTo(other);
    }

    return comparison;
  }
 @Override
 public int hashCode() {
   final int prime = 31;
   int result = 1;
   result = prime * result + (foreignKeyColumn == null ? 0 : foreignKeyColumn.hashCode());
   result = prime * result + (primaryKeyColumn == null ? 0 : primaryKeyColumn.hashCode());
   return result;
 }
 public int compareTo(final ColumnMap o) {
   int compare = 0;
   if (compare == 0) {
     compare = primaryKeyColumn.compareTo(o.getPrimaryKeyColumn());
   }
   if (compare == 0) {
     compare = foreignKeyColumn.compareTo(o.getForeignKeyColumn());
   }
   return compare;
 }
 private List<String> findColumnsWithQuotedNames(final Column[] columns) {
   final List<String> columnsWithQuotedNames = new ArrayList<String>();
   for (final Column column : columns) {
     final String columnName = column.getName();
     if (isQuotedName(columnName)) {
       columnsWithQuotedNames.add(columnName);
     }
   }
   return columnsWithQuotedNames;
 }
  @Test
  public void tables() throws Exception {

    final String referenceFile = "tables.txt";
    final File testOutputFile =
        File.createTempFile("schemacrawler." + referenceFile + ".", ".test");
    testOutputFile.delete();

    final PrintWriter writer = new PrintWriter(new BufferedWriter(new FileWriter(testOutputFile)));

    final Config config = Config.loadResource("/hsqldb.INFORMATION_SCHEMA.config.properties");
    final SchemaCrawlerOptions schemaCrawlerOptions = new SchemaCrawlerOptions(config);
    schemaCrawlerOptions.setSchemaInfoLevel(SchemaInfoLevel.maximum());
    schemaCrawlerOptions.setSchemaInclusionRule(
        new InclusionRule(InclusionRule.ALL, ".*\\.FOR_LINT"));

    final Database database = getDatabase(schemaCrawlerOptions);
    final Schema[] schemas = database.getSchemas().toArray(new Schema[0]);
    assertEquals("Schema count does not match", 5, schemas.length);
    for (final Schema schema : schemas) {
      final Table[] tables = database.getTables(schema).toArray(new Table[0]);
      Arrays.sort(tables, NamedObjectSort.alphabetical);
      for (final Table table : tables) {
        writer.println(String.format("o--> %s [%s]", table.getFullName(), table.getTableType()));
        final SortedMap<String, Object> tableAttributes =
            new TreeMap<String, Object>(table.getAttributes());
        for (final Entry<String, Object> tableAttribute : tableAttributes.entrySet()) {
          writer.println(
              String.format("      ~ %s=%s", tableAttribute.getKey(), tableAttribute.getValue()));
        }
        final Column[] columns = table.getColumns().toArray(new Column[0]);
        for (final Column column : columns) {
          writer.println(
              String.format("   o--> %s [%s]", column.getFullName(), column.getColumnDataType()));
          final SortedMap<String, Object> columnAttributes =
              new TreeMap<String, Object>(column.getAttributes());
          for (final Entry<String, Object> columnAttribute : columnAttributes.entrySet()) {
            writer.println(
                String.format(
                    "          ~ %s=%s", columnAttribute.getKey(), columnAttribute.getValue()));
          }
        }
      }
    }

    writer.flush();
    writer.close();

    final List<String> failures =
        TestUtility.compareOutput(METADATA_OUTPUT + referenceFile, testOutputFile);
    if (failures.size() > 0) {
      fail(failures.toString());
    }
  }
Esempio n. 10
0
  private static Element createForeignKeyColumnElement(
      final Document dom, final ForeignKeyColumnReference fkcm) {
    final Element eForeignKey = dom.createElement("column");
    final Column referencingColumn = fkcm.getForeignKeyColumn();
    final Column referencedColumn = fkcm.getPrimaryKeyColumn();

    eForeignKey.setAttribute("name", referencingColumn.getName());
    eForeignKey.setAttribute("referencedtable", referencedColumn.getParent().getName());
    eForeignKey.setAttribute("referencedcolumn", referencedColumn.getName());

    return eForeignKey;
  }
  private Multimap<String, IncrementingColumn> findIncrementingColumns(final Column[] columns) {
    if (columns == null || columns.length <= 1) {
      return new Multimap<String, IncrementingColumn>();
    }

    final Pattern pattern = Pattern.compile("([^0-9]*)([0-9]+)");

    final Map<String, Integer> incrementingColumnsMap = new HashMap<String, Integer>();
    for (final Column column : columns) {
      final String columnName = Utility.convertForComparison(column.getName());
      incrementingColumnsMap.put(columnName, 1);
      final Matcher matcher = pattern.matcher(columnName);
      if (matcher.matches()) {
        final String columnNameBase = matcher.group(1);
        if (incrementingColumnsMap.containsKey(columnNameBase)) {
          incrementingColumnsMap.put(
              columnNameBase, incrementingColumnsMap.get(columnNameBase) + 1);
        } else {
          incrementingColumnsMap.put(columnNameBase, 1);
        }
      }
    }

    final Set<String> columnNameBases = new HashSet<String>(incrementingColumnsMap.keySet());
    for (final String columnNameBase : columnNameBases) {
      if (incrementingColumnsMap.get(columnNameBase) == 1) {
        incrementingColumnsMap.remove(columnNameBase);
      }
    }

    final Multimap<String, IncrementingColumn> incrementingColumns =
        new Multimap<String, IncrementingColumn>();

    for (final Column column : columns) {
      final String columnName = Utility.convertForComparison(column.getName());
      if (incrementingColumnsMap.containsKey(columnName)) {
        incrementingColumns.add(columnName, new IncrementingColumn(columnName, "0", column));
      }
      final Matcher matcher = pattern.matcher(columnName);
      if (matcher.matches()) {
        final String columnNameBase = matcher.group(1);
        final String columnIncrement = matcher.group(2);
        if (incrementingColumnsMap.containsKey(columnNameBase)) {
          incrementingColumns.add(
              columnNameBase, new IncrementingColumn(columnNameBase, columnIncrement, column));
        }
      }
    }

    return incrementingColumns;
  }
Esempio n. 12
0
  private static boolean isOutgoingForeignKey(final Table table, final ForeignKey foreignKey) {
    final List<ForeignKeyColumnReference> fkcms = foreignKey.getColumnReferences();
    ThreadContext.assertFault(
        fkcms.size() > 0,
        "Table [%s] reports a foreign key [%s] with no columns",
        table.getName(),
        foreignKey.getName());
    final ForeignKeyColumnReference fkcm = fkcms.get(0);

    final Column referencedColumn = fkcm.getPrimaryKeyColumn();
    final String referencedtableName = referencedColumn.getParent().getName();

    return !referencedtableName.equals(table.getName());
  }
Esempio n. 13
0
 @Override
 public Column getColumn(final String name) {
   if (column.getName().equals(name)) {
     return column;
   } else {
     return null;
   }
 }
  private boolean hasUselessSurrogateKey(final Table table) {

    if (!(table instanceof View) && table.getPrimaryKey() != null) {
      boolean hasUselessSurrogateKey = true;
      for (final Column column : table.getColumns()) {
        if (column.isPartOfPrimaryKey()) {
          continue;
        }
        if (!column.isPartOfForeignKey()) {
          hasUselessSurrogateKey = false;
          break;
        }
      }
      return hasUselessSurrogateKey;
    }

    return false;
  }
Esempio n. 15
0
  private Element createColumnElement(final Document dom, final Column column) {
    final Element eColumn = dom.createElement("column");

    final String columnName = getPhysicalName(column.getName());
    eColumn.setAttribute("name", getLogicalName(columnName));
    eColumn.setAttribute("physicalname", columnName);

    setColumnTypeAttributes(eColumn, column, "table");

    String requirement = null;
    if (column.isPartOfPrimaryKey()) {
      requirement = "primarykey";
    } else {
      requirement = column.isNullable() ? "optional" : "mandatory";
    }
    eColumn.setAttribute("requirement", requirement);

    return eColumn;
  }
  public static Connectivity getConnectivity(final Column fkColumn) {
    if (fkColumn == null) {
      return Connectivity.unknown;
    }

    boolean isColumnReference;
    try {
      fkColumn.getColumnDataType();
      isColumnReference = false;
    } catch (final Exception e) {
      isColumnReference = true;
    }
    if (isColumnReference) {
      return Connectivity.unknown;
    }

    if (fkColumn.isPartOfPrimaryKey() || fkColumn.isPartOfUniqueIndex()) {
      return Connectivity.zero_one;
    } else {
      return Connectivity.zero_many;
    }
  }
Esempio n. 17
0
 ColumnPartial(final Column column) {
   this(requireNonNull(column, "No column provided").getParent(), column.getName());
   addAttributes(column.getAttributes());
 }
  private void createForeignKeys(
      final MetadataResultSet results, final NamedObjectList<MutableForeignKey> foreignKeys)
      throws SQLException {
    try {
      while (results.next()) {
        String foreignKeyName = quotedName(results.getString("FK_NAME"));
        LOGGER.log(Level.FINER, "Retrieving foreign key: " + foreignKeyName);

        final String pkTableCatalogName = quotedName(results.getString("PKTABLE_CAT"));
        final String pkTableSchemaName = quotedName(results.getString("PKTABLE_SCHEM"));
        final String pkTableName = quotedName(results.getString("PKTABLE_NAME"));
        final String pkColumnName = quotedName(results.getString("PKCOLUMN_NAME"));

        final String fkTableCatalogName = quotedName(results.getString("FKTABLE_CAT"));
        final String fkTableSchemaName = quotedName(results.getString("FKTABLE_SCHEM"));
        final String fkTableName = quotedName(results.getString("FKTABLE_NAME"));
        final String fkColumnName = quotedName(results.getString("FKCOLUMN_NAME"));

        final int keySequence = results.getInt("KEY_SEQ", 0);
        final int updateRule = results.getInt("UPDATE_RULE", ForeignKeyUpdateRule.unknown.getId());
        final int deleteRule = results.getInt("DELETE_RULE", ForeignKeyUpdateRule.unknown.getId());
        final int deferrability =
            results.getInt("DEFERRABILITY", ForeignKeyDeferrability.unknown.getId());

        final Column pkColumn =
            lookupOrCreateColumn(pkTableCatalogName, pkTableSchemaName, pkTableName, pkColumnName);
        final Column fkColumn =
            lookupOrCreateColumn(fkTableCatalogName, fkTableSchemaName, fkTableName, fkColumnName);
        final boolean isPkColumnPartial = pkColumn instanceof ColumnPartial;
        final boolean isFkColumnPartial = fkColumn instanceof ColumnPartial;

        if (pkColumn == null || fkColumn == null || (isFkColumnPartial && isPkColumnPartial)) {
          continue;
        }

        if (Utility.isBlank(foreignKeyName)) {
          foreignKeyName = MetaDataUtility.constructForeignKeyName(pkColumn, fkColumn);
        }

        final Optional<MutableForeignKey> foreignKeyOptional = foreignKeys.lookup(foreignKeyName);
        final MutableForeignKey foreignKey;
        if (foreignKeyOptional.isPresent()) {
          foreignKey = foreignKeyOptional.get();
        } else {
          foreignKey = new MutableForeignKey(foreignKeyName);
          foreignKeys.add(foreignKey);
        }

        foreignKey.addColumnReference(keySequence, pkColumn, fkColumn);
        foreignKey.setUpdateRule(ForeignKeyUpdateRule.valueOf(updateRule));
        foreignKey.setDeleteRule(ForeignKeyUpdateRule.valueOf(deleteRule));
        foreignKey.setDeferrability(ForeignKeyDeferrability.valueOf(deferrability));
        foreignKey.addAttributes(results.getAttributes());

        if (fkColumn instanceof MutableColumn) {
          ((MutableColumn) fkColumn).setReferencedColumn(pkColumn);
          ((MutableTable) fkColumn.getParent()).addForeignKey(foreignKey);
        } else if (isFkColumnPartial) {
          ((ColumnPartial) fkColumn).setReferencedColumn(pkColumn);
          ((TablePartial) fkColumn.getParent()).addForeignKey(foreignKey);
        }

        if (pkColumn instanceof MutableColumn) {
          ((MutableTable) pkColumn.getParent()).addForeignKey(foreignKey);
        } else if (isPkColumnPartial) {
          ((TablePartial) pkColumn.getParent()).addForeignKey(foreignKey);
        }
      }
    } finally {
      results.close();
    }
  }