/*
*
* SchemaCrawler
* http://sourceforge.net/projects/schemacrawler
* Copyright (c) 2000-2009, Sualeh Fatehi.
*
* This library is free software; you can redistribute it and/or modify it under the terms
* of the GNU Lesser General Public License as published by the Free Software Foundation;
* either version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License along with this
* library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
* Boston, MA 02111-1307, USA.
*
*/
package schemacrawler.crawl;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import schemacrawler.schema.ActionOrientationType;
import schemacrawler.schema.CheckOptionType;
import schemacrawler.schema.ConditionTimingType;
import schemacrawler.schema.DatabaseObject;
import schemacrawler.schema.EventManipulationType;
import schemacrawler.schemacrawler.InformationSchemaViews;
/**
* A retriever uses database metadata to get the extended details about
* the database tables.
*
* @author Sualeh Fatehi
*/
final class TableExRetriever
extends AbstractRetriever
{
private static final Logger LOGGER = Logger.getLogger(TableExRetriever.class
.getName());
TableExRetriever(final RetrieverConnection retrieverConnection)
throws SQLException
{
super(retrieverConnection);
}
/**
* Retrieves a check constraint information from the database, in the
* INFORMATION_SCHEMA format.
*
* @param tables
* List of tables and views.
* @throws SQLException
* On a SQL exception
*/
void retrieveCheckConstraintInformation(final NamedObjectList<MutableTable> tables)
throws SQLException
{
final Map<String, MutableCheckConstraint> checkConstraintsMap = new HashMap<String, MutableCheckConstraint>();
final InformationSchemaViews informationSchemaViews = getRetrieverConnection()
.getInformationSchemaViews();
if (!informationSchemaViews.hasTableConstraintsSql())
{
LOGGER
.log(Level.FINE, "Table constraints SQL statement was not provided");
return;
}
final String tableConstraintsInformationSql = informationSchemaViews
.getTableConstraints().getQuery();
final Connection connection = getDatabaseConnection();
Statement statement = connection.createStatement();
MetadataResultSet results = null;
try
{
results = new MetadataResultSet(statement
.executeQuery(tableConstraintsInformationSql));
}
catch (final SQLException e)
{
LOGGER.log(Level.WARNING,
"Could not retrieve check constraint information",
e);
return;
}
final String catalogName = getRetrieverConnection().getCatalogName();
try
{
while (results.next())
{
// final String catalogName =
// results.getString("CONSTRAINT_CATALOG");
final String schemaName = results.getString("CONSTRAINT_SCHEMA");
final String constraintName = results.getString("CONSTRAINT_NAME");
LOGGER.log(Level.FINEST, "Retrieving constraint information for "
+ constraintName);
// final String tableCatalogName =
// results.getString("TABLE_CATALOG");
// final String tableSchemaName =
// results.getString("TABLE_SCHEMA");
final String tableName = results.getString("TABLE_NAME");
final MutableTable table = tables.lookup(catalogName,
schemaName,
tableName);
if (!belongsToSchema(table, catalogName, schemaName))
{
LOGGER.log(Level.FINEST, "Table not found: " + tableName);
continue;
}
final String constraintType = results.getString("CONSTRAINT_TYPE");
final boolean deferrable = results.getBoolean("IS_DEFERRABLE");
final boolean initiallyDeferred = results
.getBoolean("INITIALLY_DEFERRED");
if (constraintType.equalsIgnoreCase("check"))
{
final MutableCheckConstraint checkConstraint = new MutableCheckConstraint(table,
constraintName);
checkConstraint.setDeferrable(deferrable);
checkConstraint.setInitiallyDeferred(initiallyDeferred);
checkConstraint.addAttributes(results.getAttributes());
// Add to map, since we will need this later
checkConstraintsMap.put(constraintName, checkConstraint);
}
}
}
finally
{
statement.close();
results.close();
}
if (!informationSchemaViews.hasCheckConstraintsSql())
{
LOGGER
.log(Level.FINE, "Check constraints SQL statement was not provided");
return;
}
final String checkConstraintInformationSql = informationSchemaViews
.getCheckConstraints().getQuery();
// Get check constraint definitions
statement = connection.createStatement();
results = new MetadataResultSet(statement
.executeQuery(checkConstraintInformationSql));
try
{
while (results.next())
{
// final String catalogName =
// results.getString("CONSTRAINT_CATALOG");
// final String schemaName =
// results.getString("CONSTRAINT_SCHEMA");
final String constraintName = results.getString("CONSTRAINT_NAME");
LOGGER.log(Level.FINEST, "Retrieving constraint definition for "
+ constraintName);
String definition = results.getString("CHECK_CLAUSE");
final MutableCheckConstraint checkConstraint = checkConstraintsMap
.get(constraintName);
if (checkConstraint == null)
{
LOGGER.log(Level.FINEST, "Could not add check constraint to table: "
+ constraintName);
continue;
}
final String text = checkConstraint.getDefinition();
if (!(text == null || text.trim().length() == 0))
{
definition = checkConstraint.getDefinition() + definition;
}
checkConstraint.setDefinition(definition);
}
}
finally
{
statement.close();
results.close();
}
// Add check constraints to tables
final Collection<MutableCheckConstraint> checkConstraintsCollection = checkConstraintsMap
.values();
for (final MutableCheckConstraint checkConstraint: checkConstraintsCollection)
{
final MutableTable table = (MutableTable) checkConstraint.getParent();
table.addCheckConstraint(checkConstraint);
}
}
void retrieveTableColumnPrivileges(final MutableTable table,
final NamedObjectList<MutableColumn> tableColumnList)
throws SQLException
{
retrievePrivileges(table, tableColumnList);
}
void retrieveTablePrivileges(final NamedObjectList<MutableTable> tableList)
throws SQLException
{
retrievePrivileges(null, tableList);
}
/**
* Retrieves a trigger information from the database, in the
* INFORMATION_SCHEMA format.
*
* @param tables
* List of tables and views.
* @throws SQLException
* On a SQL exception
*/
void retrieveTriggerInformation(final NamedObjectList<MutableTable> tables)
throws SQLException
{
final InformationSchemaViews informationSchemaViews = getRetrieverConnection()
.getInformationSchemaViews();
if (!informationSchemaViews.hasTriggerSql())
{
LOGGER.log(Level.FINE,
"Trigger definition SQL statement was not provided");
return;
}
final String triggerInformationSql = informationSchemaViews.getTriggers()
.getQuery();
final Connection connection = getDatabaseConnection();
final Statement statement = connection.createStatement();
MetadataResultSet results = null;
try
{
results = new MetadataResultSet(statement
.executeQuery(triggerInformationSql));
}
catch (final SQLException e)
{
LOGGER.log(Level.WARNING, "Could not retrieve trigger information", e);
return;
}
final String catalogName = getRetrieverConnection().getCatalogName();
try
{
while (results.next())
{
// final String catalogName =
// results.getString("TRIGGER_CATALOG");
final String schema = results.getString("TRIGGER_SCHEMA");
final String triggerName = results.getString("TRIGGER_NAME");
LOGGER.log(Level.FINEST, "Retrieving trigger information for "
+ triggerName);
EventManipulationType eventManipulationType;
try
{
eventManipulationType = EventManipulationType.valueOf(results
.getString("EVENT_MANIPULATION").toLowerCase(Locale.ENGLISH));
}
catch (final IllegalArgumentException e)
{
eventManipulationType = EventManipulationType.unknown;
}
// final String eventObjectCatalog = results
// .getString("EVENT_OBJECT_CATALOG");
// final String eventObjectSchema = results
// .getString("EVENT_OBJECT_SCHEMA");
final String tableName = results.getString("EVENT_OBJECT_TABLE");
final MutableTable table = tables
.lookup(catalogName, schema, tableName);
if (!belongsToSchema(table, catalogName, schema))
{
LOGGER.log(Level.FINEST, "Skipping trigger " + triggerName
+ " for table " + tableName);
continue;
}
final int actionOrder = results.getInt("ACTION_ORDER", 0);
final String actionCondition = results.getString("ACTION_CONDITION");
String actionStatement = results.getString("ACTION_STATEMENT");
final ActionOrientationType actionOrientation = ActionOrientationType
.valueOf(results.getString("ACTION_ORIENTATION")
.toLowerCase(Locale.ENGLISH));
final ConditionTimingType conditionTiming = ConditionTimingType
.valueOfFromValue(results.getString("CONDITION_TIMING")
.toLowerCase(Locale.ENGLISH));
MutableTrigger trigger = table.lookupTrigger(triggerName);
if (trigger == null)
{
trigger = new MutableTrigger(table, triggerName);
}
else
{
actionStatement = trigger.getActionStatement() + actionStatement;
}
trigger.setEventManipulationType(eventManipulationType);
trigger.setActionOrder(actionOrder);
trigger.setActionCondition(actionCondition);
trigger.setActionStatement(actionStatement);
trigger.setActionOrientation(actionOrientation);
trigger.setConditionTiming(conditionTiming);
trigger.addAttributes(results.getAttributes());
// Add trigger to the table
table.addTrigger(trigger);
}
}
finally
{
results.close();
statement.close();
}
}
/**
* Retrieves a view information from the database, in the
* INFORMATION_SCHEMA format.
*
* @param tables
* List of tables and views.
* @throws SQLException
* On a SQL exception
*/
void retrieveViewInformation(final NamedObjectList<MutableTable> tables)
throws SQLException
{
final InformationSchemaViews informationSchemaViews = getRetrieverConnection()
.getInformationSchemaViews();
if (!informationSchemaViews.hasViewsSql())
{
LOGGER.log(Level.FINE, "Views SQL statement was not provided");
return;
}
final String viewInformationSql = informationSchemaViews.getViews()
.getQuery();
final Connection connection = getDatabaseConnection();
final Statement statement = connection.createStatement();
MetadataResultSet results = null;
try
{
results = new MetadataResultSet(statement
.executeQuery(viewInformationSql));
}
catch (final SQLException e)
{
LOGGER.log(Level.WARNING, "Could not retrieve view information", e);
return;
}
try
{
while (results.next())
{
final String catalog = results.getString("TABLE_CATALOG");
final String schema = results.getString("TABLE_SCHEMA");
final String viewName = results.getString("TABLE_NAME");
final MutableView view = (MutableView) tables.lookup(catalog,
schema,
viewName);
if (!belongsToSchema(view, catalog, schema))
{
LOGGER.log(Level.FINEST, "Skipping definition for view " + viewName);
continue;
}
LOGGER.log(Level.FINEST, "Retrieving view information for " + viewName);
String definition = results.getString("VIEW_DEFINITION");
final CheckOptionType checkOption = CheckOptionType.valueOf(results
.getString("CHECK_OPTION").toLowerCase(Locale.ENGLISH));
final boolean updatable = results.getBoolean("IS_UPDATABLE");
final String text = view.getDefinition();
if (!(text == null || text.trim().length() == 0))
{
definition = view.getDefinition() + definition;
}
view.setDefinition(definition);
view.setCheckOption(checkOption);
view.setUpdatable(updatable);
view.addAttributes(results.getAttributes());
}
}
finally
{
statement.close();
results.close();
}
}
private void createPrivileges(final MetadataResultSet results,
final NamedObjectList<?> namedObjectList,
final boolean privilegesForTable)
throws SQLException
{
final String catalogName = getRetrieverConnection().getCatalogName();
while (results.next())
{
// final String catalogName = results.getString("TABLE_CAT");
final String schemaName = results.getString("TABLE_SCHEM");
final String name;
if (privilegesForTable)
{
name = results.getString(TABLE_NAME);
}
else
{
name = results.getString(COLUMN_NAME);
}
final DatabaseObject databaseObject = (DatabaseObject) namedObjectList
.lookup(catalogName, schemaName, name);
if (databaseObject != null)
{
final String privilegeName = results.getString("PRIVILEGE");
final String grantor = results.getString("GRANTOR");
final String grantee = results.getString("GRANTEE");
final boolean isGrantable = results.getBoolean("IS_GRANTABLE");
final MutablePrivilege privilege = new MutablePrivilege(databaseObject,
privilegeName);
privilege.setGrantor(grantor);
privilege.setGrantee(grantee);
privilege.setGrantable(isGrantable);
privilege.addAttributes(results.getAttributes());
if (privilegesForTable)
{
final MutableTable table = (MutableTable) databaseObject;
table.addPrivilege(privilege);
}
else
{
final MutableColumn column = (MutableColumn) databaseObject;
column.addPrivilege(privilege);
}
}
}
}
private void retrievePrivileges(final DatabaseObject parent,
final NamedObjectList<?> namedObjectList)
throws SQLException
{
final MetadataResultSet results;
final boolean privilegesForTable = parent == null;
final String catalogName = getRetrieverConnection().getCatalogName();
final String schemaPattern = getRetrieverConnection().getSchemaPattern();
final String privilegePattern = "%";
if (privilegesForTable)
{
results = new MetadataResultSet(getRetrieverConnection().getMetaData()
.getTablePrivileges(catalogName, schemaPattern, privilegePattern));
}
else
{
results = new MetadataResultSet(getRetrieverConnection().getMetaData()
.getColumnPrivileges(catalogName,
schemaPattern,
parent.getName(),
privilegePattern));
}
try
{
createPrivileges(results, namedObjectList, privilegesForTable);
}
finally
{
results.close();
}
}
}