| /* |
| * Copyright (c) 2010, Stanislav Muhametsin. All Rights Reserved. |
| * |
| * Licensed under the Apache License, Version 2.0 (the "License"); |
| * you may not use this file except in compliance with the License. |
| * You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, software |
| * distributed under the License is distributed on an "AS IS" BASIS, |
| * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or |
| * implied. |
| * |
| * See the License for the specific language governing permissions and |
| * limitations under the License. |
| */ |
| package org.qi4j.index.sql.support.common; |
| |
| import java.sql.Connection; |
| import java.sql.DatabaseMetaData; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.util.ArrayList; |
| import java.util.HashMap; |
| import java.util.List; |
| import java.util.Map; |
| import org.qi4j.library.sql.common.SQLUtil; |
| import org.qi4j.spi.query.IndexExporter; |
| import org.sql.generation.api.grammar.builders.query.QuerySpecificationBuilder; |
| import org.sql.generation.api.grammar.factories.QueryFactory; |
| import org.sql.generation.api.grammar.factories.TableReferenceFactory; |
| import org.sql.generation.api.vendor.SQLVendor; |
| |
| /** |
| * This is a helper class to traverse through all content in specified tables in database. Typical usecase would be by |
| * {@link IndexExporter} implementation. |
| */ |
| public final class GenericDatabaseExplorer |
| { |
| public static enum IntegrityActions |
| { |
| CASCADE, |
| NO_ACTION, |
| RESTRICT, |
| SET_DEFAULT, |
| SET_NULL |
| } |
| |
| public static enum Deferrability |
| { |
| INITIALLY_DEFERRED, |
| INITIALLY_IMMEDIATE, |
| NOT_DEFERRABLE |
| } |
| |
| private static final Map<Integer, IntegrityActions> INTEGRITY_ACTIONS; |
| |
| private static final Map<Integer, Deferrability> DEFERRABILITIES; |
| |
| static |
| { |
| DEFERRABILITIES = new HashMap<>( 3 ); |
| DEFERRABILITIES.put( DatabaseMetaData.importedKeyInitiallyDeferred, Deferrability.INITIALLY_DEFERRED ); |
| DEFERRABILITIES.put( DatabaseMetaData.importedKeyInitiallyImmediate, Deferrability.INITIALLY_IMMEDIATE ); |
| DEFERRABILITIES.put( DatabaseMetaData.importedKeyNotDeferrable, Deferrability.NOT_DEFERRABLE ); |
| |
| INTEGRITY_ACTIONS = new HashMap<>( 5 ); |
| INTEGRITY_ACTIONS.put( DatabaseMetaData.importedKeyCascade, IntegrityActions.CASCADE ); |
| INTEGRITY_ACTIONS.put( DatabaseMetaData.importedKeyNoAction, IntegrityActions.NO_ACTION ); |
| INTEGRITY_ACTIONS.put( DatabaseMetaData.importedKeyRestrict, IntegrityActions.RESTRICT ); |
| INTEGRITY_ACTIONS.put( DatabaseMetaData.importedKeySetDefault, IntegrityActions.SET_DEFAULT ); |
| INTEGRITY_ACTIONS.put( DatabaseMetaData.importedKeySetNull, IntegrityActions.SET_NULL ); |
| } |
| |
| public static class ColumnInfo |
| { |
| |
| private final String _name; |
| |
| private final Integer _sqlType; |
| |
| private final String _typeName; |
| |
| private final Integer _size; |
| |
| private final Integer _scale; |
| |
| private final String _nullable; |
| |
| private final String _defaultValue; |
| |
| private final String _remarks; |
| |
| private ColumnInfo( String name, Integer sqlType, String typeName, Integer size, Integer scale, |
| String nullable, String defaultValue, String remarks ) |
| { |
| this._name = name; |
| this._sqlType = sqlType; |
| this._typeName = typeName; |
| this._size = size; |
| this._scale = scale; |
| this._nullable = nullable; |
| this._defaultValue = defaultValue; |
| this._remarks = remarks; |
| } |
| |
| public String getName() |
| { |
| return this._name; |
| } |
| |
| public String getTypeName() |
| { |
| return this._typeName; |
| } |
| |
| public Integer getSize() |
| { |
| return this._size; |
| } |
| |
| public Integer getScale() |
| { |
| return this._scale; |
| } |
| |
| public String getNullable() |
| { |
| return this._nullable; |
| } |
| |
| public String getDefaultValue() |
| { |
| return this._defaultValue; |
| } |
| |
| public String getRemarks() |
| { |
| return this._remarks; |
| } |
| |
| public Integer getSQLType() |
| { |
| return this._sqlType; |
| } |
| } |
| |
| public static class ForeignKeyInfo |
| { |
| |
| private final String _pkSchemaName; |
| |
| private final String _pkTableName; |
| |
| private final String _pkTablePKColumnName; |
| |
| private final IntegrityActions _onUpdateAction; |
| |
| private final IntegrityActions _onDeleteAction; |
| |
| private final Deferrability _deferrability; |
| |
| private ForeignKeyInfo( String pkSchemaName, String pkTableName, String pkTablePKColumnName, short onUpdate, |
| short onDelete, short deferrability ) |
| { |
| this._pkSchemaName = pkSchemaName; |
| this._pkTableName = pkTableName; |
| this._pkTablePKColumnName = pkTablePKColumnName; |
| this._onUpdateAction = INTEGRITY_ACTIONS.get( (int) onUpdate ); |
| this._onDeleteAction = INTEGRITY_ACTIONS.get( (int) onDelete ); |
| this._deferrability = DEFERRABILITIES.get( (int) deferrability ); |
| } |
| |
| public String getPkSchemaName() |
| { |
| return this._pkSchemaName; |
| } |
| |
| public String getPkTableName() |
| { |
| return this._pkTableName; |
| } |
| |
| public String getPkTablePKColumnName() |
| { |
| return this._pkTablePKColumnName; |
| } |
| |
| public IntegrityActions getOnUpdateAction() |
| { |
| return this._onUpdateAction; |
| } |
| |
| public IntegrityActions getOnDeleteAction() |
| { |
| return this._onDeleteAction; |
| } |
| |
| public Deferrability getDeferrability() |
| { |
| return this._deferrability; |
| } |
| |
| } |
| |
| public static interface DatabaseProcessor |
| { |
| public void beginProcessSchemaInfo( String schemaName ); |
| |
| public void endProcessSchemaInfo( String schemaName ); |
| |
| public void beginProcessTableInfo( String schemaName, String tableName, String remarks ); |
| |
| public void endProcessTableInfo( String schemaName, String tableName, String remarks ); |
| |
| public void beginProcessColumns( String schemaName, String tableName, String tableRemarks ); |
| |
| public void beginProcessColumnInfo( String schemaName, String tableName, |
| ColumnInfo colInfo, ForeignKeyInfo fkInfo ); |
| |
| public void endProcessColumnInfo( String schemaName, String tableName, |
| ColumnInfo colInfo, ForeignKeyInfo fkInfo ); |
| |
| public void endProcessColumns( String schemaName, String tableName, String tableRemarks ); |
| |
| public void beginProcessRows( String schemaName, String tableName, String tableRemarks ); |
| |
| public void beginProcessRowInfo( String schemaName, String tableName, Object[] rowContents ); |
| |
| public void endProcessRowInfo( String schemaName, String tableName, Object[] rowContents ); |
| |
| public void endProcessRows( String schemaName, String tableName, String tableRemarks ); |
| } |
| |
| public static abstract class DatabaseProcessorAdapter |
| implements DatabaseProcessor |
| { |
| |
| @Override |
| public void beginProcessColumnInfo( String schemaName, String tableName, |
| ColumnInfo colInfo, ForeignKeyInfo fkInfo ) |
| { |
| } |
| |
| @Override |
| public void beginProcessColumns( String schemaName, String tableName, String tableRemarks ) |
| { |
| } |
| |
| @Override |
| public void beginProcessRowInfo( String schemaName, String tableName, Object[] rowContents ) |
| { |
| } |
| |
| @Override |
| public void beginProcessRows( String schemaName, String tableName, String tableRemarks ) |
| { |
| } |
| |
| @Override |
| public void beginProcessSchemaInfo( String schemaName ) |
| { |
| } |
| |
| @Override |
| public void beginProcessTableInfo( String schemaName, String tableName, String remarks ) |
| { |
| } |
| |
| @Override |
| public void endProcessColumnInfo( String schemaName, String tableName, |
| ColumnInfo colInfo, ForeignKeyInfo fkInfo ) |
| { |
| } |
| |
| @Override |
| public void endProcessColumns( String schemaName, String tableName, String tableRemarks ) |
| { |
| } |
| |
| @Override |
| public void endProcessRowInfo( String schemaName, String tableName, Object[] rowContents ) |
| { |
| } |
| |
| @Override |
| public void endProcessRows( String schemaName, String tableName, String tableRemarks ) |
| { |
| } |
| |
| @Override |
| public void endProcessSchemaInfo( String schemaName ) |
| { |
| } |
| |
| @Override |
| public void endProcessTableInfo( String schemaName, String tableName, String remarks ) |
| { |
| } |
| |
| } |
| |
| public static void visitDatabaseTables( Connection connection, String catalogName, |
| String schemaNamePattern, String tableNamePattern, |
| DatabaseProcessor processor, SQLVendor sqlSyntaxVendor ) |
| throws SQLException |
| { |
| DatabaseMetaData metaData = connection.getMetaData(); |
| connection.setReadOnly( true ); |
| ResultSet rs = metaData.getTables( catalogName, schemaNamePattern, tableNamePattern, new String[] |
| { |
| "TABLE" |
| } ); |
| try |
| { |
| while( rs.next() ) |
| { |
| String schemaName = rs.getString( 2 ); |
| try |
| { |
| processor.beginProcessSchemaInfo( schemaName ); |
| String tableName = rs.getString( 3 ); |
| String tableRemarks = rs.getString( 5 ); |
| try |
| { |
| processor.beginProcessTableInfo( schemaName, tableName, tableRemarks ); |
| List<ColumnInfo> colInfos = new ArrayList<>(); |
| ResultSet rsCols = metaData.getColumns( null, schemaName, tableName, null ); |
| try |
| { |
| while( rsCols.next() ) |
| { |
| String nullable = rsCols.getString( 18 ); |
| colInfos.add( new ColumnInfo( |
| rsCols.getString( 4 ), |
| rsCols.getInt( 5 ), |
| rsCols.getString( 6 ), |
| rsCols.getInt( 7 ), |
| rsCols.getInt( 9 ), |
| nullable.length() > 0 ? Boolean.toString( nullable.equals( "YES" ) ) : "unknown", |
| rsCols.getString( 13 ), |
| rsCols.getString( 12 ) ) ); |
| } |
| } |
| finally |
| { |
| rsCols.close(); |
| } |
| |
| rsCols = metaData.getImportedKeys( null, schemaName, tableName ); |
| Map<String, ForeignKeyInfo> fkInfos = new HashMap<>(); |
| try |
| { |
| while( rsCols.next() ) |
| { |
| fkInfos.put( |
| // |
| rsCols.getString( 8 ), // |
| new ForeignKeyInfo( rsCols.getString( 2 ), rsCols.getString( 3 ), rsCols |
| .getString( 4 ), rsCols.getShort( 10 ), rsCols.getShort( 11 ), rsCols |
| .getShort( 14 ) ) ); |
| } |
| } |
| finally |
| { |
| rsCols.close(); |
| } |
| |
| try |
| { |
| processor.beginProcessColumns( schemaName, tableName, tableRemarks ); |
| for( ColumnInfo colInfo : colInfos ) |
| { |
| try |
| { |
| processor.beginProcessColumnInfo( schemaName, tableName, colInfo, |
| fkInfos.get( colInfo._name ) ); |
| } |
| finally |
| { |
| processor.endProcessColumnInfo( schemaName, tableName, colInfo, |
| fkInfos.get( colInfo._name ) ); |
| } |
| } |
| } |
| finally |
| { |
| processor.endProcessColumns( schemaName, tableName, tableRemarks ); |
| } |
| |
| QueryFactory q = sqlSyntaxVendor.getQueryFactory(); |
| TableReferenceFactory t = sqlSyntaxVendor.getTableReferenceFactory(); |
| QuerySpecificationBuilder builda = q.querySpecificationBuilder(); |
| builda.getSelect().selectAll(); |
| builda.getFrom().addTableReferences( |
| t.tableBuilder( t.table( t.tableName( schemaName, tableName ) ) ) ); |
| String sql = sqlSyntaxVendor.toString( q.createQuery( builda.createExpression() ) ); |
| Statement stmt = connection.createStatement(); |
| ResultSet rowsRs = null; |
| try |
| { |
| rowsRs = stmt.executeQuery( sql ); |
| processor.beginProcessRows( schemaName, tableName, tableRemarks ); |
| while( rowsRs.next() ) |
| { |
| Object[] rowContents = new Object[ colInfos.size() ]; |
| for( Integer x = 0; x < rowContents.length; ++x ) |
| { |
| rowContents[x] = rowsRs.getObject( x + 1 ); |
| } |
| |
| try |
| { |
| processor.beginProcessRowInfo( schemaName, tableName, rowContents ); |
| } |
| finally |
| { |
| processor.endProcessRowInfo( schemaName, tableName, rowContents ); |
| } |
| } |
| } |
| finally |
| { |
| processor.endProcessRows( schemaName, tableName, tableRemarks ); |
| if( rowsRs != null ) |
| { |
| rowsRs.close(); |
| } |
| stmt.close(); |
| } |
| } |
| finally |
| { |
| processor.endProcessTableInfo( schemaName, tableName, tableRemarks ); |
| } |
| } |
| finally |
| { |
| processor.endProcessSchemaInfo( schemaName ); |
| } |
| } |
| } |
| finally |
| { |
| SQLUtil.closeQuietly( rs ); |
| } |
| } |
| |
| private GenericDatabaseExplorer() |
| { |
| } |
| } |