TailoredQuery

/*
 * $Source$
 * $Revision$
 *
 * Copyright (C) 2000 William Chesters
 *
 * Part of Melati (http://melati.org), a framework for the rapid
 * development of clean, maintainable web applications.
 *
 * Melati is free software; Permission is granted to copy, distribute
 * and/or modify this software under the terms either:
 *
 * a) 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,
 *
 *    or
 *
 * b) any version of the Melati Software License, as published
 *    at http://melati.org
 *
 * You should have received a copy of the GNU General Public License and
 * the Melati Software License along with this program;
 * if not, write to the Free Software Foundation, Inc.,
 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA to obtain the
 * GNU General Public License and visit http://melati.org to obtain the
 * Melati Software License.
 *
 * Feel free to contact the Developers of Melati (http://melati.org),
 * if you would like to work out a different arrangement than the options
 * outlined here.  It is our intention to allow Melati to be used by as
 * wide an audience as possible.
 *
 * 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.
 *
 * Contact details for copyright holder:
 *
 *     William Chesters <williamc At paneris.org>
 *     http://paneris.org/~williamc
 *     Obrechtstraat 114, 2517VX Den Haag, The Netherlands
 */

package org.melati.poem;

import java.util.Enumeration;
import java.util.Vector;
import java.util.Hashtable;
import java.sql.ResultSet;

/**
 * This is how you run low-level SQL queries, including joins, and get the
 * results back in the form of convenient Melati {@link Field}s which can be
 * rendered automatically in templates.  A complement to the high-level
 * {@link Table#selection()} on the one hand, and the low-level
 * <TT>ResultSet</TT> on the other.
 *
 *
 * <blockquote>
 *
 * TailoredQuery is specifically for when you want a few fields back
 * (possibly joined from several tables) rather than whole objects.
 * Suppose you want to do
 * <blockquote><code>
 *   SELECT a.foo, b.bar FROM a, b WHERE a.something AND b.id = a.b
 * </code></blockquote>
 * There is nothing to stop you doing this with a good old ResultSet =
 * Database.sqlQuery("SELECT ...").  However if you want to get the same
 * effect, without forgoing the nice features offered by
 * POEM---e.g. access control, rich metadata that makes rendering
 * trivial---you can use a TailoredQuery.
 *
 * </blockquote>
 *
 * <p>
 *
 * If Postgresql's <TT>ResultSetMetaData</TT> supported <TT>getTableName</TT>
 * even approximately, this would all be "even simpler" to use and somewhat
 * more flexible.  Because it doesn't, and because of the requirement to
 * perform (as far as possible) read access checks on the records accessed
 * during the query, the interface necessarily takes a slightly structured form
 * rather than just being raw SQL---although the programmer does get complete
 * freedom as far as the core <TT>WHERE</TT> clause is concerned.
 *
 * @see Table#selection(java.lang.String, java.lang.String, boolean)
 */

public class TailoredQuery {

  protected Database database;
  protected String sql;
  int selectedColumnsCount;
  Column<?>[] columns;
  boolean[] isCanReadColumn;
  Table<?>[] tables;
  Table<?>[] tablesWithoutCanReadColumn;

  Hashtable<String, Integer> table_columnMap = new Hashtable<String,Integer>();

  /**
   * Construct a low-level SQL query, possibly including joins, from which
   * results come back in form of automatically-renderable Melati
   * <TT>Field</TT>s.  The queries you can construct are of the form
   *
   * <BLOCKQUOTE><TT>
   * SELECT <I>t1</I>.<I>c1</I>, <I>t2</I>.<I>c2</I> </TT>...<TT>
   * FROM <I>t1</I>, <I>t2</I> </TT>...<TT>,
   *      <I>t10</I>, <I>t11</I> </TT>...<TT>
   * WHERE <I>whereClause</I>
   * ORDER BY <I>orderByClause</I>
   * </TT></BLOCKQUOTE>
   *
   * You specify the columns you want to return
   * (<TT><I>t1</I>.<I>c1</I></TT>&nbsp;...) in the <TT>selectedColumns</TT>
   * parameter, the selection criteria (including joins) in the
   * <TT>whereClause</TT> parameter, and the ordering criteria in the
   * <TT>orderByClause</TT>.  If your <TT>whereClause</TT> or
   * <TT>orderByClause</TT> use tables <TT><I>t10</I></TT>&nbsp;... not implied
   * by <TT>selectedColumns</TT>, you must include them in the
   * <TT>otherTables</TT> parameter.
   *
   * <P>
   *
   * Note that there is no provision for aliasing, which does restrict the
   * available queries somewhat.
   *
   * <P>
   *
   * To get the results of the query, use <TT>selection()</TT> (below).
   *
   * <P>
   *
   * Example:
   * <BLOCKQUOTE><PRE>
   * Column[] columns = {
   *     database.getUserTable().getNameColumn(),
   *     database.getGroupTable().getNameColumn(),
   * };
   * Table[] tables = { database.getGroupMembershipTable() };
   *
   * TailoredQuery q =
   *     new TailoredQuery(
   *    columns, tables,
   *    "\"user\" = \"user\".id AND \"group\" = \"group\".id",
   *    null);
   *
   * for (Enumeration ms = q.selection(); ms.hasMoreElements();) {
   *   FieldSet fs = (FieldSet)ms.nextElement();
   *   System.out.println(fs.get("user_name").getCookedString(PoemLocale.HERE, DateFormat.MEDIUM) +
   *                 ", " +
   *                 fs.get("group_name").getCookedString(PoemLocale.HERE, DateFormat.MEDIUM));
   * }
   * </PRE></BLOCKQUOTE>
   *
   * @param selectedColumns     The columns you want to select out
   * @param otherTables         Tables aside from those to which your
   *                            <TT>selectedColumns</TT> are attached which
   *                            you need to use in the <TT>whereClause</TT>
   * @param whereClause         Search criteria for your query; note that
   *                            you will have to do any necessary quoting of
   *                            identifiers/values yourself (or use
   *                            <TT>Column.quotedName</TT> and
   *                            <TT>PoemType.quotedRaw</TT>)
   * @param orderByClause       Ordering criteria for your query
   *
   * @see #selection
   * @see Column#quotedName()
   * @see BasePoemType#quotedRaw(java.lang.Object)
   */

  public TailoredQuery(Column<?>[] selectedColumns, Table<?>[] otherTables,
                       String whereClause, String orderByClause) {
    this(null, selectedColumns, otherTables, whereClause, orderByClause);
  }

  /**
   * Same as without the first argument except that it is inserted
   * between <code>SELECT</code> and the column list.
   *
   * @param modifier  HACK Allow SQL modifier eg DISTINCT
   * @param selectedColumns
   * @param otherTables
   * @param whereClause
   * @param orderByClause
   * @see #TailoredQuery(Column[], Table[], String, String)
   */
  public TailoredQuery(String modifier,
                       Column<?>[] selectedColumns, Table<?>[] otherTables,
                       String whereClause, String orderByClause) {

    this.database = selectedColumns[0].getDatabase();

    // Make a list of all the tables used

    Vector<Table<?>> tablesV = new Vector<Table<?>>();

    for (int c = 0; c < selectedColumns.length; ++c) {
      Table<Persistent> table = selectedColumns[c].getTable();
      if (!tablesV.contains(table))
        tablesV.addElement(table);
    }
    for (int t = 0; t < otherTables.length; ++t)
      if (!tablesV.contains(otherTables[t]))
        tablesV.addElement(otherTables[t]);

    tables = new Table[tablesV.size()];
    tablesV.copyInto(tables);

    // Record the access protection sources for all the tables used (of course
    // this doesn't include computed `Persistent.assertCanRead's written by the
    // programmer).  Make up a list of all the columns we need, included any
    // `canRead' access control columns for tables.

    Vector<Column<?>> columnsV = new Vector<Column<?>>();
    Vector<Integer> canReadColumnIndices = new Vector<Integer>();
    Vector<Table<?>> tablesWithoutCanReadColumnV = new Vector<Table<?>>();

    selectedColumnsCount = selectedColumns.length;
    for (int c = 0; c < selectedColumns.length; ++c) {
      columnsV.addElement(selectedColumns[c]);
    }

    for (int t = 0; t < tables.length; t++) {
      Table<?> table = tables[t];
      Column<Capability> canRead = table.canReadColumn();
      if (canRead == null) {
        // No specific canRead column, revert to the table default protection
        if (!tablesWithoutCanReadColumnV.contains(table))
          tablesWithoutCanReadColumnV.addElement(table);
      }
      else {
        if (!columnsV.contains(canRead)) {
          canReadColumnIndices.addElement(new Integer(columnsV.size()));
          columnsV.addElement(canRead);
        } else {
          canReadColumnIndices.addElement(new Integer(columnsV.indexOf(canRead)));
        }
      }
    }

    this.columns = new Column[columnsV.size()];
    columnsV.copyInto(this.columns);

    isCanReadColumn = new boolean[columnsV.size()];
    for (int i = 0; i < canReadColumnIndices.size(); ++i) {
      int c = ((Integer)canReadColumnIndices.elementAt(i)).intValue();
      isCanReadColumn[c] = true;
    }

    this.tablesWithoutCanReadColumn = new Table[tablesWithoutCanReadColumnV.size()];
    tablesWithoutCanReadColumnV.copyInto(this.tablesWithoutCanReadColumn);

    // Make up the SQL for the query

    StringBuffer sqlLocal = new StringBuffer();

    sqlLocal.append("SELECT ");

    if (modifier != null) {
      sqlLocal.append(modifier);
      sqlLocal.append(' ');
    }

    for (int c = 0; c < columnsV.size(); ++c) {
      if (c > 0) sqlLocal.append(", ");
      Column<?> column = (Column<?>)columnsV.elementAt(c);
      sqlLocal.append(column.getTable().quotedName());
      sqlLocal.append('.');
      sqlLocal.append(column.quotedName());
    }

    sqlLocal.append(" FROM ");

    for (int t = 0; t < tables.length; ++t) {
      if (t > 0) sqlLocal.append(", ");
      sqlLocal.append((tables[t]).quotedName());
    }

    if (whereClause != null && !whereClause.trim().equals("")) {
      sqlLocal.append(" WHERE ");
      sqlLocal.append(whereClause);
    }

    if (orderByClause != null && !orderByClause.trim().equals("")) {
      sqlLocal.append(" ORDER BY ");
      sqlLocal.append(orderByClause);
    }

    this.sql = sqlLocal.toString();

    // Set up mappings from column name (<table>_<column>) to position
    // (including the canRead columns, if anyone ever wants them)

    for (int c = 0; c < columnsV.size(); ++c) {
      Column<?> column = columnsV.elementAt(c);
      table_columnMap.put(
          column.getTable().getName() + "_" + column.getName(),
          new Integer(c));
    }
  }

  /**
   * Run the query.
   *
   *
   * <P>
   *
   * Here's an example of presenting the results of a <TT>TailoredQuery</TT> in
   * a WebMacro template:
   *
   * <BLOCKQUOTE><TT>
   * &lt;TABLE&gt;<BR>
   * &nbsp;#foreach $fieldSet in $tailoredQuery.selection() #begin<BR>
   * &nbsp;&nbsp;&lt;TR&gt;<BR>
   * &nbsp;&nbsp;&nbsp;#foreach $field in $fieldSet #begin<BR>
   * &nbsp;&nbsp;&nbsp;&nbsp;&lt;TD&gt;$ml.rendered($field.DisplayName)&lt;/TD&gt;<BR>
   * &nbsp;&nbsp;&nbsp;&nbsp;&lt;TD&gt;$ml.rendered($field)&lt;/TD&gt;<BR>
   * &nbsp;&nbsp;&nbsp;#end<BR>
   * &nbsp;&nbsp;&lt;/TR&gt;<BR>
   * &nbsp;#end<BR>
   * &lt;/TABLE&gt;
   * </TT></BLOCKQUOTE>
   *
   * <P>
   *
   * Read access checks are performed against the POEM access token associated
   * with the thread running this method (see
   * <TT>PoemThread.accessToken()</TT>) on all the tables implied by the
   * <TT>selectedColumns</TT> and <TT>otherTables</TT> arguments given at
   * construct time.  If the checks fail for a given row, all the fields in the
   * corresponding <TT>FieldSet</TT> are booby-trapped to throw the relevant
   * <TT>AccessPoemException</TT> (<I>all</I> of them, because,
   * without parsing your <TT>whereClause</TT>, there's no way for POEM to
   * know which
   * columns are `tainted'; note that it's probably possible for you to bypass
   * access checks by using sub-SELECTs).
   *
   * <P>
   *
   * Normally, Melati's response to an "access-denied" fields is to terminate
   * template expansion and ask the user to log in, then continue where they
   * were left off.  If, however, you put
   * <TT>$melati.setPassbackExceptionHandling()</TT>
   * at the top of the template, or in the servlet,
   * they will be rendered as warnings by <I>e.g.</I> <TT>HTMLMarkupLanguage</TT>.
   *
   * <P>
   *
   * NOTE Since this way of doing queries involves named fields rather
   * than whole <TT>Persistent</TT> objects, it inevitably bypasses any
   * Java-coded access rules put in place by the programmer by overriding
   * <TT>Persistent.assertCanRead</TT>.
   *
   * @see FieldSet#elements()
   * @see FieldSet#get(java.lang.String)
   * @see PoemThread#accessToken()
   * @see Persistent#assertCanRead(org.melati.poem.AccessToken)
   * Used in org.melati.template.MarkupLanguage#rendered(Object)
   *
   * @return
   * An <TT>Enumeration</TT> of <TT>FieldSet</TT>s, one per row returned from
   * the DBMS.  You can invoke each <TT>FieldSet</TT>'s <TT>elements</TT>
   * method to get an <TT>Enumeration</TT> of all the <TT>Field</TT>s in the
   * row, ready for rendering.  Or you can fetch them by name using the
   * <TT>FieldSet</TT>'s <TT>get</TT> method.  A field corresponding to
   * column <TT><I>col</I></TT> of table <TT><I>tab</I></TT> is named
   * <TT><I>tab</I>_<I>col</I></TT>.
   */

  public Enumeration<FieldSet> selection() {
    return new TailoredResultSetEnumeration<FieldSet>(this, database.sqlQuery(sql));
  }

  /**
   * Return an Enumeration of the columns in the first row of
   * a ResultSet.
   */
  public class FirstRawTailoredResultSetEnumeration<T>
      extends TailoredResultSetEnumeration<T> {

   /**
    * Retrieve the first row of a {@link TailoredQuery}.
    */
    public FirstRawTailoredResultSetEnumeration(TailoredQuery query,
                                                ResultSet resultSet) {
      super(query, resultSet);
    }

    protected Object mapped(ResultSet them) {
      checkTableAccess();
      for (int c = 1; c < query.columns.length; ++c)
        column(them, c);
      return column(them, 0);
    }
  }

  /**
   * @return the first row of this <code>TailoredQuery</code>
   */
  public Enumeration<Object> selection_firstRaw() {
    return new FirstRawTailoredResultSetEnumeration<Object>(this,
                                                    database.sqlQuery(sql));
  }

  /**
   * The SQL of the query.
   * {@inheritDoc}
   * @see java.lang.Object#toString()
   */
  public String toString() {
    return super.toString() + "(SQL=" + sql +  ")";
  }
}