Java SchemaCrawlerException类代码示例

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
/**
 * The lint that does the job
 * @param table table
 * @param connection connection
 */
@Override
protected void lint(final Table table, final Connection connection) throws SchemaCrawlerException{

    try (Statement stmt = connection.createStatement()){

        List<Column> columns = getColumns(table);
        int columnDataType;
        int count;
        int distinctCount;

        for (Column column : columns) {
            columnDataType = column.getColumnDataType().getJavaSqlType().getJavaSqlType();
            if(columnDataType == Types.BINARY || columnDataType == Types.BLOB) {
                inspectBlobData(stmt, table, column);
            }
        }
    }catch (SQLException ex) {
        LOGGER.severe(ex.getMessage());
        throw new SchemaCrawlerException(ex.getMessage(), ex);
    }

}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
/**
 * The lint that does the job
 *
 * @param table      table
 * @param connection connection
 */
@Override
protected void lint(final Table table, final Connection connection) throws SchemaCrawlerException {
    try {
        if ("PostgreSQL".equalsIgnoreCase(connection.getMetaData().getDatabaseProductName()) && "9.4".compareTo(connection.getMetaData().getDatabaseProductVersion()) <= 0) {
            List<Column> columns = getColumns(table);
            for (Column column : columns) {
                LOGGER.log(Level.INFO, "Checking {0}...", column.getFullName() + " - " + column.getColumnDataType().getDatabaseSpecificTypeName());
                if (Objects.equals("bytea", column.getColumnDataType().getDatabaseSpecificTypeName())) {
                    addLint(table, getDescription(), column.getFullName());
                }
            }
        }
    } catch (SQLException ex) {
        LOGGER.severe(ex.getMessage());
        throw new SchemaCrawlerException(ex.getMessage(), ex);
    }
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
/**
 * The lint that does the job
 * @param table table
 * @param connection connection
 */
@Override
protected void lint(final Table table, Connection connection) throws SchemaCrawlerException
{
    requireNonNull(table, "No table provided");
    
    try {
        if("PostgreSQL".equalsIgnoreCase(connection.getMetaData().getDatabaseProductName()) &&
                "9.4".compareTo(connection.getMetaData().getDatabaseProductVersion()) <= 0){
            List<String> names = findJsonTypeColumn(getColumns(table));
            for (String name : names) {
                addLint(table, getDescription(), name);
            }
        }
        
    } catch (SQLException e) {
        LOGGER.log(Level.SEVERE, "Unable to get database product name info. Lint will not be executed.");
        throw new SchemaCrawlerException(e.getMessage(), e);
    }
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
private static int getSelectCount(Statement stmt, Table table, Column column) throws SchemaCrawlerException{
    String tableName = table.getName().replaceAll("\"", "");
    String columnName = column.getName().replaceAll("\"", "");

    String sql = "select count (\"" + columnName + "\") as countRow from \"" + tableName +"\"";
    LOGGER.log(Level.CONFIG, "SQL : {0}", sql);

    int count = 0;
    try(ResultSet rs = stmt.executeQuery(sql)){
        if(rs.next()){
            count = rs.getInt("countRow");
        }
    }catch (SQLException ex) {
        LOGGER.severe(ex.getMessage());
        throw new SchemaCrawlerException(ex.getMessage(), ex);
    }
    return count;
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
private static int getSelectDistinctCount(Statement stmt, Table table, Column column) throws SchemaCrawlerException{
    String tableName = table.getName().replaceAll("\"", "");
    String columnName = column.getName().replaceAll("\"", "");
    
    String sql = "select count (distinct \"" + columnName + "\") as countDistinctRow from \"" + tableName +"\"";
    LOGGER.log(Level.CONFIG, "SQL : {0}", sql);
    
    int count = 0;
    try(ResultSet rs = stmt.executeQuery(sql)){
        if(rs.next()){
            count = rs.getInt("countDistinctRow");
        }
    }catch (SQLException ex) {
        LOGGER.severe(ex.getMessage());
        throw new SchemaCrawlerException(ex.getMessage(), ex);
    }
    return count;
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
@Override
public DatabaseSpecificOverrideOptionsBuilder getDbSpecificOptionsBuilder(Connection conn, PhysicalSchema physicalSchema) {
    try {
        DatabaseConnectorRegistry registry = new DatabaseConnectorRegistry();
        DatabaseConnector databaseConnector = registry.lookupDatabaseConnector(conn);
        return databaseConnector.getDatabaseSpecificOverrideOptionsBuilder();
    } catch (SchemaCrawlerException e) {
        throw new RuntimeException(e);
    }
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
/**
 * 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 {
    
    requireNonNull(table, "No table provided");
    findMismatchedForeignKeys(table);
    
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
/**
 * 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()){
        
        List<Column> columns = getColumns(table);
        int columnDataType;
        int count;
        int distinctCount;

        for (Column column : columns) {
            //skip column if PK or FK
            if(!column.isPartOfPrimaryKey() && !column.isPartOfForeignKey()) {

                columnDataType = column.getColumnDataType().getJavaSqlType().getJavaSqlType();
                if (LintUtils.isSqlTypeNumericBased(columnDataType)) {
                    LOGGER.log(Level.INFO, "Checking {0}...", column.getFullName());
                    count = getSelectCount(stmt, table, column);
                    if (count > 2) {
                        distinctCount = getSelectDistinctCount(stmt, table, column);
                        if (distinctCount == 2) {
                            checkIfBooleanValuesAndLint(stmt, table, column);
                        }
                    }
                }

            }
        }
    }catch (SQLException ex) {
        LOGGER.severe(ex.getMessage());
        throw new SchemaCrawlerException(ex.getMessage(), ex);
    }
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
private void checkIfBooleanValuesAndLint(Statement stmt, final Table table, Column column) throws SchemaCrawlerException{
    String tableName = table.getName().replaceAll("\"", "");
    String columnName = column.getName().replaceAll("\"", "");
    
    String sql = "select distinct \"" + columnName + "\" from \"" + tableName + "\"" ;
    LOGGER.log(Level.CONFIG, "SQL : {0}", sql);
    
    try(ResultSet rs = stmt.executeQuery(sql)){
       
        boolean trueFound = false;
        boolean falseFound = false;
        while (rs.next()) {
            if(rs.getObject(columnName) != null) {
                int data = ((Number) rs.getObject(columnName)).intValue();
                if(data == 1)
                    trueFound = true;
                else if(data == 0)
                    falseFound = true;
            }
        }
        
        if(trueFound && falseFound){
            addLint(table, getDescription(), column.getFullName());
        }
        
    }catch (SQLException ex) {
        LOGGER.severe(ex.getMessage());
        throw new SchemaCrawlerException(ex.getMessage(), ex);
    }
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
public DatabaseImporter(String jdbcUrl, String schema, String storeDir, Rules rules) throws SQLException, SchemaCrawlerException {
    this.storeDir = storeDir;
    this.rules = rules;
    Connection conn = DriverManager.getConnection(jdbcUrl);
    tables = MetaDataReader.extractTables(conn, schema, rules);
    reader = new DataReader(conn);
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
public static void main(String[] args) throws SQLException, SchemaCrawlerException, ClassNotFoundException {

        Driver driver = DriverManager.getDriver("jdbc:derby:memory:test;create=true");
        Connection connection = DriverManager.getConnection("jdbc:derby:memory:test;create=true", new Properties());
        Statement statement = connection.createStatement();
        // id INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)
        statement.execute("CREATE TABLE USERS (id INT NOT NULL, name varchar(20), constraint users_pk_id primary key(id))");
        statement.execute("CREATE TABLE FRIENDS (id1 INT, id2 INT, " +
                " constraint fk_users_id1 foreign key(id1) references users(id)," +
                " constraint fk_users_id2 foreign key(id2) references users(id)" +
                ")");

        final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
        options.setSchemaInfoLevel(SchemaInfoLevel.standard());

        final Catalog catalog = SchemaCrawlerUtility.getCatalog(connection, options);

        for (final Schema schema : catalog.getSchemas()) {
            System.out.println(schema);
            for (final Table table : catalog.getTables(schema)) {
                System.out.println("o--> " + table + " pk " + table.getPrimaryKey() + " fks " + table.getForeignKeys() + " type " + table.getTableType());
                for (final Column column : table.getColumns()) {
                    System.out.println("     o--> " + column + " pk: " + column.isPartOfPrimaryKey() + " fk: " + column.isPartOfForeignKey());
                }
            }
        }
    }
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
private Database crawl(Connection dbconn, SchemaCrawlerOptions options) {
    try {
        SchemaCrawler crawler = new SchemaCrawler(dbconn);
        return crawler.crawl(options);
    } catch (SchemaCrawlerException e) {
        throw new RuntimeException(e);
    }
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
private void inspectBlobData(Statement stmt, Table table, Column column) throws SchemaCrawlerException{
    String tableName = table.getName().replaceAll("\"", "");
    String columnName = column.getName().replaceAll("\"", "");

    String sql = "select " + columnName + " from " + tableName ;
    LOGGER.log(Level.CONFIG, "SQL : {0}", sql);

    InputStream byte_stream;
    try(ResultSet rs = stmt.executeQuery(sql)){
        if(rs.next()){
            File file = new File("target/file");

            try(FileOutputStream fos = new FileOutputStream(file)){
                byte[] buffer = new byte[1];
                byte_stream = rs.getBinaryStream(columnName);
                int nbBytes = 0;
                while (byte_stream.read(buffer) > 0 && nbBytes < 512000) {
                    fos.write(buffer);
                    nbBytes++;
                }
                byte_stream.close();
                int extract_size = nbBytes-1;

                /* Create Output Stream that will have final zip files */
                OutputStream zip_output = new FileOutputStream(new File("target/zip_output.zip"));
                /* Create Archive Output Stream that attaches File Output Stream / and specifies type of compression */
                ArchiveOutputStream logical_zip = null;
                logical_zip = new ArchiveStreamFactory().createArchiveOutputStream(ArchiveStreamFactory.ZIP, zip_output);
                /* Create Archieve entry - write header information*/
                logical_zip.putArchiveEntry(new ZipArchiveEntry("target/file"));
                /* Copy input file */
                IOUtils.copy(new FileInputStream(new File("target/file")), logical_zip);
                /* Close Archieve entry, write trailer information */
                logical_zip.closeArchiveEntry();
                /* Finish addition of entries to the file */
                logical_zip.finish();
                /* Close output stream, our files are zipped */
                zip_output.close();

                File zip_file = new File("target/zip_output.zip");
                if(zip_file.length() < extract_size) {
                    double rate = (zip_file.length() - extract_size)*100/extract_size;
                    if (Math.abs(rate) >= minCompressionPercent) {
                        LOGGER.info("Extract size :" + extract_size + "ko, zip file size :" + zip_file.length() + " rate :" +rate+"%");
                        addLint(table, "Blob should be compressed", column.getFullName());
                    }
                }

            } catch (IOException | ArchiveException e){
                LOGGER.severe(e.getMessage());
                throw new SchemaCrawlerException(e.getMessage(), e);
            }
        }
    }catch (SQLException ex) {
        LOGGER.severe(ex.getMessage());
        throw new SchemaCrawlerException(ex.getMessage(), ex);
    }
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
@Test
public void testLint_getDescription () throws SchemaCrawlerException{
    final LinterRegistry registry = new LinterRegistry();
    LinterJsonContent linter = (LinterJsonContent)registry.newLinter(LinterJsonContent.class.getName());
    Assert.assertEquals("should be JSON or JSONB type", linter.getDescription());
}
 

import schemacrawler.schemacrawler.SchemaCrawlerException; //导入依赖的package包/类
@Test
public void testLint_getSummary () throws SchemaCrawlerException{
    final LinterRegistry registry = new LinterRegistry();
    LinterJsonContent linter = (LinterJsonContent)registry.newLinter(LinterJsonContent.class.getName());
    Assert.assertEquals(linter.getSummary(), linter.getDescription());
}