DbSpecBuilder.java

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSchema;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSpec;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbTable;

/**
 * Creates {@link DbSpec} object from a {@link Connection} metadata Calling it is as simple as writing
 * <pre>
 * Connection connection = ... // Create JDBC connection
 * DBSpec spec = DbSpecBuilder.createDbSpec(connection);
 * </pre>
 * 
 * Notice that some schemas and tables can be excluded by giving a custom {@link BuilderAuthorizer}
 * 
 * @author ndx
 * 
 */
public class DbSpecBuilder {

	/**
	 * Interface defining what elements must be put in DbSpec tree.
	 * 
	 * @author ndx
	 * 
	 */
	public interface BuilderAuthorizer {
		/**
		 * Authorize addition of schema in DbSpec
		 * 
		 * @param schemaName
		 * @return true to authorize schema, false to forbid
		 */
		boolean authorizeSchema(String schemaName);

		/**
		 * Authorize addition of table from schema in DbSpec
		 * 
		 * @param schemaName
		 * @param tableName
		 * @return true to authorize table, false to forbid
		 */
		boolean authorizeTable(String schemaName, String tableName);

	}

	/**
	 * Authorizer based upon a schema and table list
	 * 
	 * @author ndx
	 * 
	 */
	public static class SchemaTableList implements BuilderAuthorizer {
		private Collection<String> schemaNames;
		private Collection<String> tableNames;

		/**
		 * Build authorizer with given authorized schemas and tables
		 * @param schemaNames list of authorized schemas. if of 0 size (or null), all are authorized
		 * @param tableNames list of authorized tables. if of 0 size (or null), all are authorized
		 */
		public SchemaTableList(Collection<String> schemaNames,
				Collection<String> tableNames) {
			this.schemaNames = schemaNames;
			this.tableNames = tableNames;
		}

		@Override
		public boolean authorizeSchema(String schemaName) {
			if (schemaNames!=null && schemaNames.size() > 0)
				return schemaNames.contains(schemaName);
			return true;
		}

		@Override
		public boolean authorizeTable(String schemaName, String tableName) {
			if (tableNames!=null && tableNames.size() > 0)
				return tableNames.contains(schemaName);
			return true;
		}
	}

	private static final Logger logger = Logger.getLogger(DbSpecBuilder.class
			.getName());

	/**
	 * Create and fill DB spec object from given database connection
	 * 
	 * @param connection
	 * @return
	 */
	public static DbSpec createDbSpec(Connection connection,
			BuilderAuthorizer authorizer) throws SQLException {
		DatabaseMetaData metadata = connection.getMetaData();
		return createDbSpec(metadata, authorizer);
	}

	/**
	 * Create and fill DB spec object from given database connection
	 * 
	 * @param connection
	 * @return
	 */
	public static DbSpec createDbSpec(Connection connection)
			throws SQLException {
		DatabaseMetaData metadata = connection.getMetaData();
		// Here, null authorizes all
		return createDbSpec(metadata, new SchemaTableList(null, null));
	}

	/**
	 * Create and fill {@link DbSpec} object from given database metadata
	 * 
	 * @param metadata
	 *            input metadata
	 * @return generated DbSpec object
	 * @throws SQLException
	 */
	private static DbSpec createDbSpec(DatabaseMetaData metadata)
			throws SQLException {
		// Here, null authorizes all
		return createDbSpec(metadata, new SchemaTableList(null, null));
	}

	private static DbSpec createDbSpec(DatabaseMetaData metadata,
			BuilderAuthorizer authorizer) throws SQLException {
		DbSpec returned = new DbSpec();
		fillSchemasList(returned, metadata, authorizer);
		return returned;
	}

	/**
	 * Recursively fill schemas, tables, columns from database informations TODO
	 * maybe insert some way to limit DB exploration
	 * 
	 * @param returned
	 *            filled DbSpec
	 * @param metadata
	 *            input metadata
	 * @param authorizer
	 *            authorization handler
	 * @return the input DbSpec object, for the sake of fluent code !
	 * @throws SQLException
	 */
	private static DbSpec fillSchemasList(DbSpec returned,
			DatabaseMetaData metadata, BuilderAuthorizer authorizer)
			throws SQLException {
		ResultSet schemas = metadata.getSchemas();
		while (schemas.next()) {
			String schemaName = schemas.getString("TABLE_SCHEM");
			if (authorizer.authorizeSchema(schemaName)) {
				DbSchema schema = returned.addSchema(schemaName);
				logger.log(Level.FINE,
						"browsing schema " + schema.getAbsoluteName());
				fillSchemaInfos(schema, metadata, authorizer);
			}
		}
		return returned;
	}

	/**
	 * Fill single schema infos
	 * 
	 * @param schema
	 *            input schema object. only its name is initially set
	 * @param metadata
	 * @param authorizer
	 *            authorization handler
	 * @throws SQLException
	 *             if
	 *             {@link DatabaseMetaData#getTables(String, String, String, String[])}
	 *             call failed for some reason
	 */
	private static DbSchema fillSchemaInfos(DbSchema schema,
			DatabaseMetaData metadata, BuilderAuthorizer authorizer)
			throws SQLException {
		ResultSet tables = metadata.getTables(null, schema.getName(), null,
				null);
		while (tables.next()) {
			String tableName = tables.getString("TABLE_NAME");
			if (authorizer.authorizeTable(schema.getName(), tableName)) {
				DbTable table = schema.addTable(tableName);
				logger.log(Level.FINE,
						"browsing table " + table.getAbsoluteName());
				fillTableInfos(table, metadata, authorizer);
			}
		}
		return schema;
	}

	private static DbTable fillTableInfos(DbTable table,
			DatabaseMetaData metadata, BuilderAuthorizer authorizer)
			throws SQLException {
		ResultSet columns = metadata.getColumns(null, null, table.getName(),
				null);
		while (columns.next()) {
			table.addColumn(columns.getString("COLUMN_NAME"),
					columns.getString("TYPE_NAME"),
					columns.getInt("COLUMN_SIZE"));
		}
		return table;
	}
}