package com.dbxml.db.enterprise.dbfiler;

/*
 * dbXML - Native XML Database
 * Copyright (C) 1999-2004  The dbXML Group, L.L.C.
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program 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 General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 * $Id: DBFiler.java,v 1.4 2004/02/12 00:17:57 bradford Exp $
 */

import com.dbxml.db.core.data.*;
import java.sql.*;
import java.util.*;

import com.dbxml.db.core.ClassResolver;
import com.dbxml.db.core.Collection;
import com.dbxml.db.core.filer.Filer;
import com.dbxml.db.core.transaction.Transaction;
import com.dbxml.db.core.transaction.TransactionLog;
import com.dbxml.util.Configuration;
import com.dbxml.util.ObjectPool;
import com.dbxml.util.SimpleConfigurable;
import com.dbxml.util.SoftHashMap;
import com.dbxml.util.dbXMLException;
import com.dbxml.xml.SymbolTable;
import com.dbxml.xml.dom.DOMHelper;
import com.dbxml.xml.dtsm.CompressedTable;
import com.dbxml.xml.dtsm.DTSMHelper;
import com.dbxml.xml.dtsm.DocumentTable;
import java.io.File;
import java.io.IOException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.Text;
import org.xml.sax.SAXException;

/**
 * DBFiler quickly maps relational data into XML documents by utilizing
 * a single query with a set of outer joins.  The Filer does the work of
 * consolidating redunant data, thus eliminating the need for many
 * SELECT statements.
 */

public final class DBFiler extends SimpleConfigurable implements Filer {
   private static final String MAPFILE = "mapfile";

   private static final String DRIVER = "driver";
   private static final String URL = "url";
   private static final String USERNAME = "username";
   private static final String PASSWORD = "password";

   private static final String NAMESPACE = "namespace";
   private static final String SORT = "sort";
   private static final String DISTINCT = "distinct";
   private static final String PASSTHRU = "passthru";

   private static final String TABLE = "table";
   private static final String NAME = "name";
   private static final String ITEM = "item";
   private static final String SRC = "src";
   private static final String KEY = "key";
   private static final String FKEY = "fkey";
   private static final String JOIN = "join";
   private static final String RESULT = "result";
   private static final String ROW = "row";
   private static final String COLUMN = "column";
   private static final String ATTRIB = "attrib";
   private static final String REQUIRED = "required";

   private Collection collection;
   private SymbolTable st;

   private TableInfo map;
   private String select;
   private String ns;

   private boolean sorting;
   private boolean distinct;
   private boolean passthru;

   private ObjectPool dbPool;
   private Map statements = new SoftHashMap();

   public DBFiler() {
      super();
   }

   public void setConfig(Configuration config) throws dbXMLException {
      super.setConfig(config);

      ns = config.getAttribute(NAMESPACE, ns);
      sorting = config.getBooleanAttribute(SORT, true);
      distinct = config.getBooleanAttribute(DISTINCT, true);
      passthru = config.getBooleanAttribute(PASSTHRU, false);

      String mapFile = config.getAttribute(MAPFILE);

      if ( !passthru ) {
         SQLBuilder sb;
         if ( mapFile != null && mapFile.length() > 0 ) {
            try {
               Document doc = DOMHelper.parse(new File(mapFile));
               Configuration mapCfg = new Configuration(doc);
               sb = new SQLBuilder(mapCfg);
            }
            catch ( IOException e ) {
               throw new dbXMLException(e);
            }
            catch ( SAXException e ) {
               throw new dbXMLException(e);
            }
         }
         else
            sb = new SQLBuilder(config.getChild(TABLE));
         map = sb.getMap();
         select = sb.getSQL();
      }

      try {
         String driver = config.getAttribute(DRIVER);
         String url = config.getAttribute(URL);
         String user = config.getAttribute(USERNAME);
         String password = config.getAttribute(PASSWORD);

         ClassResolver.get(driver).newInstance();
         dbPool = new DBFilerPool(url, user, password);
      }
      catch ( Exception e ) {
         e.printStackTrace(System.err);
      }
   }

   public String getName() {
      return "DBFiler";
   }

   public void setCollection(Collection collection) {
      this.collection = collection;
      try {
         st = collection.getSymbols();
      }
      catch ( Exception e ) {
      }
   }

   private void close(Connection conn, Statement s, ResultSet rs) {
      try {
         if ( rs != null )
            rs.close();
         if ( s != null )
            s.close();
         if ( conn != null )
            conn.close();
      }
      catch ( Exception e ) {
      }
   }

   public void flush(Transaction tx) {
      // NO-OP
   }

   public RecordMetaData getRecordMetaData(Transaction tx, Key key) {
      return null;
   }

   private Statements getStatements(Connection conn) {
      Statements s = (Statements)statements.get(conn);
      if ( s == null ) {
         s = new Statements();
         statements.put(conn, s);
      }
      return s;
   }

   public Record readRecord(Transaction tx, Key key) {
      if ( passthru )
         return readRecordPassthru(key);
      else
         return readRecordMapped(key);
   }

   private Record readRecordPassthru(Key key) {
      Connection conn = null;
      Statement s = null;
      ResultSet rs = null;

      try {
         String query = key.toString();
         conn = getConnection();
         s = conn.createStatement();
         rs = s.executeQuery(query);

         if ( !rs.next() )
            return null;

         ResultSetMetaData meta = rs.getMetaData();
         String[] labels = new String[meta.getColumnCount()];
         for ( int i = 0; i < labels.length; i++ )
            labels[i] = meta.getColumnName(i + 1);

         Document doc = DOMHelper.newDocument();
         Element root = doc.createElement(RESULT);
         root.setAttribute(KEY, query);
         doc.appendChild(root);
         if ( ns != null )
            root.setAttribute("xmlns", ns);
         root.appendChild(doc.createTextNode("\n"));

         do {
            Element row = doc.createElement(ROW);
            root.appendChild(row);
            row.appendChild(doc.createTextNode("\n"));
            for ( int i = 0; i < labels.length; i++ ) {
               Element col = doc.createElement(labels[i]);
               row.appendChild(col);
               col.appendChild(doc.createTextNode(rs.getString(i + 1)));
               row.appendChild(doc.createTextNode("\n"));
            }
            root.appendChild(doc.createTextNode("\n"));
         }
         while ( rs.next() );

         DocumentTable dt = DTSMHelper.documentToTable(doc, st);
         CompressedTable ct = DTSMHelper.compressTable(dt);
         return new Record(key, new Value(ct.getData()));
      }
      catch ( Exception e ) {
         e.printStackTrace(System.err);
      }
      finally {
         close(conn, s, rs);
      }
      return null;
   }

   private Record readRecordMapped(Key key) {
      Connection conn = null;
      PreparedStatement ps = null;
      ResultSet rs = null;

      try {
         conn = getConnection();
         Statements s = getStatements(conn);
         if ( s.readRecordMapped == null ) {
            ps = conn.prepareStatement(select);
            s.readRecordMapped = ps;
         }
         ps.setString(1, key.toString());
         ps.execute();
         rs = ps.getResultSet();

         DocBuilder builder = new DocBuilder(rs);
         Document doc = builder.getDocument();

         if ( doc != null ) {
            DocumentTable dt = DTSMHelper.documentToTable(doc, st);
            CompressedTable ct = DTSMHelper.compressTable(dt);
            return new Record(key, new Value(ct.getData()));
         }
      }
      catch ( Exception e ) {
         e.printStackTrace(System.err);
      }
      finally {
         close(conn, null, rs);
      }
      return null;
   }

   public boolean writeRecord(Transaction tx, Key key, Value value) {
      return false;
   }

   public boolean deleteRecord(Transaction tx, Key key) {
      return false;
   }

   public long getRecordCount(Transaction tx) {
      if ( passthru )
         return -1;
      else {
         Connection conn = null;
         PreparedStatement ps = null;
         ResultSet rs = null;
         long count = -1;

         try {
            conn = getConnection();
            Statements s = getStatements(conn);
            if ( s.getRecordCount == null ) {
               String sql = "SELECT COUNT(" + map.key + ") FROM " + map.src;
               ps = conn.prepareStatement(sql);
               s.getRecordCount = ps;
            }

            ps.execute();
            rs = ps.getResultSet();

            if ( rs.next() )
               count = rs.getLong(1);
         }
         catch ( Exception e ) {
            e.printStackTrace(System.err);
         }
         finally {
            close(conn, null, rs);
         }
         return count;
      }
   }

   public RecordSet getRecordSet(Transaction tx) {
      if ( passthru )
         return new EmptyRecordSet();
      else
         return new DBRecordSet(tx);
   }

   public final boolean open() {
      return true;
   }

   public boolean isOpened() {
      return true;
   }

   public boolean exists() {
      return true;
   }

   public boolean close() {
      return true;
   }

   public boolean create() {
      return true;
   }

   public boolean drop() {
      return true;
   }

   public TransactionLog getTransactionLog() {
      return null;
   }


   /**
    * DBRecordSet
    */

   private class DBRecordSet implements RecordSet {
      private Transaction tx;
      private Connection conn;
      private ResultSet rs;
      private PreparedStatement ps;
      private boolean hasNext;

      public DBRecordSet(Transaction tx) {
         this.tx = tx;
         try {
            conn = getConnection();
            Statements s = getStatements(conn);
            if ( s.getRecordSet == null ) {
               String sql = "SELECT " + map.key + " FROM " + map.src;
               ps = conn.prepareStatement(sql);
               s.getRecordSet = ps;
            }

            ps.execute();
            rs = ps.getResultSet();

            hasNext = rs.next();
            if ( !hasNext )
               close(conn, null, rs);
         }
         catch ( Exception e ) {
            e.printStackTrace(System.err);
         }
      }

      public boolean hasMoreRecords() {
         return hasNext;
      }

      public Record getNextRecord() {
         try {
            Key key = new Key(rs.getString(1));
            hasNext = rs.next();
            if ( !hasNext )
               close(conn, null, rs);
            return readRecord(tx, key);
         }
         catch ( Exception e ) {
            e.printStackTrace(System.err);
         }
         return null;
      }

      public Key getNextKey() {
         try {
            Key key = new Key(rs.getString(1));
            hasNext = rs.next();
            if ( !hasNext )
               close(conn, null, rs);
            return key;
         }
         catch ( Exception e ) {
            e.printStackTrace(System.err);
         }
         return null;
      }

      public Value getNextValue() {
         try {
            Key key = new Key(rs.getString(1));
            hasNext = rs.next();
            if ( !hasNext )
               close(conn, null, rs);
            return readRecord(tx, key).getValue();
         }
         catch ( Exception e ) {
            e.printStackTrace(System.err);
         }
         return null;
      }

      protected void finalize() {
         close();
      }
   }

   private Connection getConnection() {
      return (Connection)dbPool.getObject();
   }

   // All Of The Mapping/Builder Code Follows *********************************


   /**
    * TableInfo
    */

   private class TableInfo {
      public TableInfo parent;

      public String key;    // Key determines uniqueness
      public int keyPos;    // Column index for the key
      public String fkey;   // fkey is a foreign key for join
      public String jkey;   // jkey is a local key for join

      public String elem;   // Table Element name to create
      public String item;   // Item Element name to create
      public String src;    // Table Name
      public String prefix; // SQL Table Prefix

      public List tables = Collections.synchronizedList(new ArrayList());  // of TableInfo
      public List columns = Collections.synchronizedList(new ArrayList()); // of ColumnInfo
   }


   /**
    * ColumnInfo
    */

   private class ColumnInfo {
      public boolean attrib;   // Is this column an attribute?
      public String elem;      // Element/Attribute name to create
      public int colPos;       // Column index to retrieve
      public boolean required; // Must this column be represented?
   }


   /**
    * SQLBuilder
    */

   private class SQLBuilder {
      private List cols = new ArrayList();
      private int tn = 1; // For numbering table prefixes
      private TableInfo map = new TableInfo();
      private String sql;

      public SQLBuilder(Configuration cfg) {
         generateMap(map, cfg);
         generateSQL();
      }

      public TableInfo getMap() {
         return map;
      }

      public String getSQL() {
         return sql;
      }

      private int addColDef(List cols, TableInfo map, String colName) {
         String val = map.prefix + "." + colName + " AS \"" + map.prefix + "$" + colName + "\"";
         if ( !cols.contains(val) )
            cols.add(val);
         return cols.indexOf(val) + 1;
      }

      private void generateMap(TableInfo map, Configuration cfg) {
         List tbls = new ArrayList();

         map.elem = cfg.getAttribute(NAME, null);
         map.src = cfg.getAttribute(SRC, null);
         map.item = cfg.getAttribute(ITEM, null);

         if ( map.src == null )
            map.src = map.elem;

         map.prefix = "t" + (tn++);

         // Primary Key
         map.key = cfg.getAttribute(KEY, null);
         map.keyPos = addColDef(cols, map, map.key);

         // Foreign Key
         map.fkey = cfg.getAttribute(FKEY, null);
         if ( map.fkey == null )
            map.fkey = map.key;

         // Join Key
         map.jkey = cfg.getAttribute(JOIN, null);
         if ( map.jkey == null )
            map.jkey = map.fkey;

         // Process child table, column and attrib elements
         Configuration[] child = cfg.getChildren();
         for ( int i = 0; i < child.length; i++ ) {
            String name = child[i].getName();
            if ( name.equals(TABLE) )
               tbls.add(child[i]);
            else if ( name.equals(COLUMN) || name.equals(ATTRIB) ) {
               ColumnInfo col = new ColumnInfo();
               col.attrib = name.equals(ATTRIB);
               col.elem = child[i].getAttribute(NAME, null);
               col.required = child[i].getBooleanAttribute(REQUIRED, true);
               String tmp = child[i].getAttribute(SRC, null);
               if ( tmp == null )
                  tmp = col.elem;
               map.columns.add(col);
               col.colPos = addColDef(cols, map, tmp);
            }
         }

         // Process each child table recursively
         Iterator enum = tbls.iterator();
         while ( enum.hasNext() ) {
            Configuration tbl = (Configuration)enum.next();
            TableInfo mapTbl = new TableInfo();
            mapTbl.parent = map;
            map.tables.add(mapTbl);
            generateMap(mapTbl, tbl);
         }
      }

      private void generateSQL() {
         StringBuffer sb = new StringBuffer();
         List sort = new ArrayList();

         if ( distinct )
            sb.append("SELECT DISTINCT\n   ");
         else
            sb.append("SELECT\n   ");

         Iterator enum = cols.iterator();
         while ( enum.hasNext() ) {
            sb.append((String)enum.next());
            if ( enum.hasNext() )
               sb.append(",\n   ");
            else
               sb.append("\n");
         }
         sb.append("FROM\n");
         sb.append("   " + map.src + " " + map.prefix + "\n");

         // Add LEFT OUTER joins for the child tables
         generateJoins(map, sb, sort);

         sb.append("WHERE\n");
         sb.append("   " + map.prefix + "." + map.key + "=\'?\'\n");

         // Perform a nested sort on all keys
         if ( sorting ) {
            sb.append("ORDER BY\n");
            sb.append("   ");
            enum = sort.iterator();
            while ( enum.hasNext() ) {
               sb.append((String)enum.next());
               if ( enum.hasNext() )
                  sb.append(", ");
            }
         }

         sql = sb.toString();
      }

      private void generateJoins(TableInfo map, StringBuffer sb, List sort) {
         if ( sorting ) {
            String ob = map.prefix + "." + map.key;
            if ( !sort.contains(ob) )
               sort.add(ob);
         }

         Iterator enum = map.tables.iterator();
         while ( enum.hasNext() ) {
            TableInfo info = (TableInfo)enum.next();
            sb.append("   LEFT OUTER JOIN ");
            sb.append(info.src);
            sb.append(" ");
            sb.append(info.prefix);
            sb.append(" ON ");
            sb.append(map.prefix + "." + info.fkey);
            sb.append("=");
            sb.append(info.prefix + "." + info.jkey);
            sb.append("\n");
            generateJoins(info, sb, sort);
         }
      }
   }


   /**
    * DocBuilder
    *
    * @todo Use a TableBuilder instead of a DOM
    */

   private class DocBuilder {
      public Map keyMap = new HashMap();  // TableInfo to String
      public Map elemMap = new HashMap(); // String to Element
      public Document doc;
      public ResultSet rs;

      public DocBuilder(ResultSet rs) {
         this.rs = rs;
         build();
      }

      private Text newLine() {
         return doc.createTextNode("\n");
      }

      private void build() {
         try {
            doc = DOMHelper.newDocument();
            int rows = 0;
            while ( rs.next() ) {
               keyMap.clear();
               mapRow(doc, map);
               rows++;
            }
            Element root = doc.getDocumentElement();
            if ( ns != null )
               root.setAttribute("xmlns", ns);
            if ( rows > 0 )
               return;
         }
         catch ( Exception e ) {
            //e.printStackTrace(System.err);
         }

         doc = null;
      }

      private void mapRow(Node owner, TableInfo map) throws SQLException {
         // Check to see if the join generated anything
         String idVal = rs.getString(map.keyPos);
         if ( idVal == null )
            return;

         // Check for keys to do a redundancy check
         String pkey;
         String key;
         if ( map.parent != null ) {
            pkey = (String)keyMap.get(map.parent);
            key = pkey + ";" + map.prefix + "=" + idVal;
         }
         else {
            pkey = null;
            key = map.prefix + "=" + idVal;
         }
         keyMap.put(map, key);

         int size;

         // If there's an elem, we need to resolve a holder
         if ( map.elem != null ) {
            String hkey;
            if ( pkey != null )
               hkey = pkey + "^" + map.elem;
            else
               hkey = "^" + map.elem;

            Element holder = (Element)elemMap.get(hkey);
            if ( holder == null ) {
               holder = doc.createElement(map.elem);
               holder.appendChild(newLine());
               owner.appendChild(holder);
               owner.appendChild(newLine());
               elemMap.put(hkey, holder);
            }
            owner = holder;
         }

         // Check to see if we need to create the element
         Element elem = (Element)elemMap.get(key);
         if ( elem == null ) {
            elem = doc.createElement(map.item);
            owner.appendChild(elem);
            owner.appendChild(newLine());
            elemMap.put(key, elem);
            elem.appendChild(newLine());

            // Add columns and attributes to the element
            size = map.columns.size();
            for ( int i = 0; i < size; i++ ) {
               ColumnInfo c = (ColumnInfo)map.columns.get(i);
               String val = rs.getString(c.colPos);

               // If null and not required just ignore
               if ( val == null && !c.required )
                  continue;

               if ( !c.attrib ) {
                  Element col = doc.createElement(c.elem);
                  if ( val != null )
                     col.appendChild(doc.createTextNode(val));
                  elem.appendChild(col);
                  elem.appendChild(newLine());
               }
               else {
                  if ( val != null )
                     elem.setAttribute(c.elem, val);
                  else
                     elem.setAttribute(c.elem, "");
               }
            }
         }

         // Process the set of child tables
         for ( int i = 0; i < map.tables.size(); i++ )
            mapRow(elem, (TableInfo)map.tables.get(i));
      }

      public Document getDocument() {
         return doc;
      }
   }


   /**
    * Statements
    */

   private class Statements {
      public PreparedStatement readRecordMapped;
      public PreparedStatement getRecordCount;
      public PreparedStatement getRecordSet;
   }
}


