package org.paneris.jal.model;
   
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.Statement;
   
   import org.paneris.util.ExtendedHash;
   import org.webmacro.servlet.HandlerException;
   
  /**
   * A TableMetaData encapsulates meta data for a single table in the datadictionary.
   * It has a hashtable for datadictionary fields.
   *
   * <p>It can be constructed from the table name or from the table id number.</p>
   *
   * <p>You need to pass a table identifier and a database to the constructors </p>
   *
   */
  public class TableMetaData {
  
      private String tableName;
      private String displayName;
      private ExtendedHash fields;
      private Integer displayOrder;
      private Integer resultsPerPage;
      private Integer maxResults;
      private boolean cached;
      private boolean listEdit;
      private Integer tableNumber;
      private String type;
      private String updateController;
      private String database;
      private DBConnectionManager connMgr;
      private DataCache dataCache = DataCache.getInstance();
  
  /**
   Constructor to build a table using a table name
  **/
      public TableMetaData(String db, String tn) throws Exception {
          database = db;
          connMgr = DBConnectionManager.getInstance();
          Connection conn = connMgr.getConnection("TableMetaData",database);
          Statement s = conn.createStatement();
          String sqlString = "SELECT * FROM datadictionarytables WHERE tablename = '";
          sqlString = sqlString.concat(tn + "'");
          try {
              ResultSet rs = s.executeQuery(sqlString);
              if (rs.next()) {
                  Integer tableNumber = new Integer(rs.getInt("id"));
                  init(tableNumber, conn);
              } else {
                  throw new Exception("Tablename not found in DatadictionaryTables Table: " + tn);
              }
          } catch (Exception e) {
              throw new HandlerException(e.toString() + " sqlString: " + sqlString);
          }
          s.close();
          connMgr.freeConnection(database, conn);
      }
  
  /**
   constructor to build a table given it's id in the datadictionarytables table
  **/
      public TableMetaData(String db, Integer tn) throws Exception {
          database = db;
          connMgr = DBConnectionManager.getInstance();
          Connection conn = connMgr.getConnection("TableMetaData",db);
          init(tn, conn);
          connMgr.freeConnection(database, conn);
      }
  
  /**
  initise the datadictionary data structure
  **/
      private void init(Integer tn, Connection c) throws Exception {
          Statement s = c.createStatement();
          Statement s2 = c.createStatement();
          tableNumber = tn;
          String sqlString = new String("SELECT * FROM datadictionarytables WHERE id = " + tableNumber);
          try {
          //
          //get the stuff for the table
              ResultSet rs = s.executeQuery(sqlString);
              rs.next();
              tableName = rs.getString("tablename").trim();
              displayName = rs.getString("displayname").trim();
              displayOrder = new Integer(rs.getInt("displayorder"));
              Integer shutUp = displayOrder;
              displayOrder = shutUp;
              try {
                  resultsPerPage = new Integer(rs.getInt("defaultresultsperpage"));
              } catch (Exception e) {
                  resultsPerPage = new Integer(0);
              }
              try {
                  maxResults = new Integer(rs.getInt("maxresults"));
              } catch (Exception e) {
                  maxResults = new Integer(0);
              }
             cached = rs.getBoolean("cached");
             try {
                 listEdit = rs.getBoolean("listedit");
             } catch (Exception e) {
                 listEdit = true;
             }
             try {
                 updateController = rs.getString("updatecontroller");
             } catch (Exception e) {
                 updateController = null;
             }
             if (updateController == null) updateController = "";
             int typeInt = rs.getInt("type");
             if (typeInt != 0) {
                 sqlString = new String("SELECT type FROM datadictionarytabletypes WHERE id = " + typeInt);
                 ResultSet rs2 = s2.executeQuery(sqlString);
                 rs2.next();
                 type = rs2.getString("type").trim();
             } else {
                 type = "";
             }
             //
             // get the stuff for the fields
             sqlString = "SELECT id FROM datadictionary WHERE tablename = ";
             sqlString = sqlString.concat(tableNumber.toString());
             sqlString = sqlString.concat(" ORDER BY displayorder");
             rs = s.executeQuery(sqlString);
             fields = new ExtendedHash();
             while (rs.next()) {
                 FieldMetaData fieldMetaData = dataCache.getFieldMetaData(database, new Integer(rs.getInt("id")));
                 fields.put(fieldMetaData.getFieldName(),fieldMetaData);
             }
         } catch (Exception e) {
             throw new Exception(e.toString() + " TableMetaData SQL: " + sqlString);
         }
         s.close();
     }
 
 /**
  * @return a hash of field data for this table
  */
     public ExtendedHash getFields() {
         return fields;
     }
 
 /**
  * get the tablename
  */
     public String getTableName() {
         return tableName;
     }
 
 /**
  * allow this table to be edited as part of a list
  */
     public boolean getListEdit() {
         return listEdit;
     }
 
 /**
  * allow this table to be edited as part of a list
  */
     public String getUpdateController() {
         return updateController;
     }
 
 /**
  * get the type of the table
  */
     public String getType() {
         return type;
     }
 
 /**
  * get the tablenumber
  */
     public Integer getTableNumber() {
         return tableNumber;
     }
 
 /**
  * get the default results per page
  */
     public Integer getResultsPerPage() {
         return resultsPerPage;
     }
 
 /**
  * get the maximum number of results for this table
  */
     public int getMaxResults() {
         return maxResults.intValue();
     }
 
 /**
  * apply the max results if appropriate
  */
     public String applyMaxResults(String db, String sql) throws Exception {
         String sqlString = sql;
         if (getMaxResults() > 0) {
             if (connMgr.getDatabaseEngineType(db) == DBConnectionManager.POSTGRES && 
                 !connMgr.getDatabaseProductVersion(database).equals("6.4")) {
                 sqlString += " LIMIT " + getMaxResults();
             }
         }
         return sqlString;
     }
 
 /**
  * Get the display name for the table.
  */
     public String getDisplayName() {
         return displayName;
     }
 
 /**
  * find out if the table is cached
  */
     public boolean getCached() {
         return cached;
     }
 }