import com.allaire.cfx.* ;
import java.sql.* ;

public class DatabaseMetaData implements CustomTag
{
   // Constant string for error messages
   final String msgError = "Error occurred in a <CFX_DATABASEMETADATA> tag. ";

   
   // This gets called each time the tag is used in a ColdFusion page
   public void processRequest( Request request, Response response )
   throws Exception
   {
     // Obtain the values for the tag's attributes, and throw exceptions
     // if any of the required attributes have not been provided.
     String strAction = getTagAttr("ACTION", request);
     String userName = getTagAttr("USERNAME", request);
     String password = getTagAttr("PASSWORD", request);
     String driver = getTagAttr("DRIVER", request);
     String connect = getTagAttr("CONNECT", request);
     String strQueryName = getTagAttr("NAME", request);
     // These are optional attributes
     String DBCatalog = getTagAttr("DBCATALOG", request, null);
     String DBSchema = getTagAttr("DBSCHEMA", request, null);
     String DBTableName = getTagAttr("DBTABLENAME", request, "%");
     String DBColumnName = getTagAttr("DBCOLUMNNAME", request, "%");
     String DBProcedureName = getTagAttr("DBPROCEDURENAME", request, "%");

     // For the DBTableName attribute, consider an empty string to mean null     
     //if (DBTableName.equals("")) DBTableName = null;

     // Load the specified database driver
     Class.forName(driver).newInstance();
     
     // Attempt to connect to the data source
     Connection conn = DriverManager.getConnection(connect, userName, password);

     // Get the metadata object from the database connection
     java.sql.DatabaseMetaData dbmd = conn.getMetaData();
     
     // This ResultSet will be returned to ColdFusion as a query object
     ResultSet rs = null;
     
     // Handle ACTION="GetTables" (user wants a list of tables)
     if ( strAction.equalsIgnoreCase("GetTables") ) {
       String[] types = {"TABLE"};
       rs = dbmd.getTables(DBCatalog, DBSchema, DBTableName, types);

     // Handle ACTION="GetViews" (user wants a list of views)
     } else if ( strAction.equalsIgnoreCase("GetViews") ) {
       String[] types = {"VIEW"};
       rs = dbmd.getTables(DBCatalog, DBSchema, DBTableName, types);

     // Handle ACTION="GetColumns" (user wants a list of columns)
     } else if ( strAction.equalsIgnoreCase("GetColumns") ) {
       rs = dbmd.getColumns(DBCatalog, DBSchema, DBTableName, DBColumnName);
       
     // Handle ACTION="GetCatalogs" (user wants a list of catalogs)
     } else if ( strAction.equalsIgnoreCase("GetCatalogs") ) {
       rs = dbmd.getCatalogs();
       
     // Handle ACTION="GetSchemas" (user wants a list of schemas)
     } else if ( strAction.equalsIgnoreCase("GetSchemas") ) {
       rs = dbmd.getSchemas();
       
     // Handle ACTION="GetProcedures" (a list of stored procedures)
     } else if ( strAction.equalsIgnoreCase("GetProcedures") ) {
       rs = dbmd.getProcedures(DBCatalog, DBSchema, DBProcedureName);
       
     // Handle ACTION="GetProcedureColumns" (list of stored procedure columns)
     } else if ( strAction.equalsIgnoreCase("GetProcedureColumns") ) {
       rs = dbmd.getProcedureColumns(DBCatalog, DBSchema, DBProcedureName, DBColumnName);
       
     // Handle ACTION="GetPrimaryKeys" 
     } else if ( strAction.equalsIgnoreCase("GetPrimaryKeys") ) {
       rs = dbmd.getPrimaryKeys(DBCatalog, DBSchema, DBTableName);
       
     // Handle ACTION="GetIndexInfo"
     } else if ( strAction.equalsIgnoreCase("GetIndexInfo") ) {
       rs = dbmd.getIndexInfo(DBCatalog, DBSchema, DBTableName, false, false);
       
     // Throw an error if the ACTION attribute was not recognized
     } else {
       throw new Exception("Unknown ACTION attribute!");
     }  
     
     // The rs ResultSet now contains the metadata info about tables/columns
     // Return it to the calling ColdFusion page as a query
     returnAsQuery(rs, strQueryName, response);
   }
   
   
   
   // Helper function that accepts any java.sql.ResultSet 
   // and returns it to ColdFusion as a CFML query object
   private void returnAsQuery(ResultSet rs, String name, Response response)
   throws Exception
   {
     // Get metadata about the ResultSet (so we can find out its column names)
     ResultSetMetaData rsmd = rs.getMetaData();

     // Create an array of strings to hold the ResultSet's column names     
     String[] arColumns = new String[rsmd.getColumnCount()];

     // Fill the array with this ResultSet's column names     
     for (int col = 1; col <= rsmd.getColumnCount(); col++ ) {
       arColumns[col-1] = rsmd.getColumnName(col);
     };

     // Create a new com.allaire.Query object
     Query q = response.addQuery(name, arColumns);

     // For each row of the ResultSet...     
     while ( rs.next() ) {
       // Add a row to the CFML query
       int row = q.addRow();
      
       // For each column of the ResultSet...
       for (int col = 1; col <= rsmd.getColumnCount(); col++) {
         // Copy the data at this row/column from ResultSet to Query
         // (an error will be thrown if the data can't be read as a string)
         q.setData(row, col, rs.getString(col));
       };
     };
     
   }
   
   
   // Helper function that returns the value of an attribute
   // An exception is thrown if the attribute was not provided
   private String getTagAttr(String name, Request request) 
   throws Exception 
   {
     if ( request.attributeExists(name) ) {
       return request.getAttribute(name);
     } else {
       throw new Exception(msgError + "The "+name+" attribute is required.");
     };
   }
   

   // Helper function that returns the value of an attribute
   // If the attribute is not provided, it defaults to def.
   private String getTagAttr(String name, Request request, String def) 
   throws Exception 
   {
     return request.attributeExists(name) ? request.getAttribute(name) : def;
   }
      
}