TailoredQuery.java
/*
* $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> ...) 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> ... 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>
* <TABLE><BR>
* #foreach $fieldSet in $tailoredQuery.selection() #begin<BR>
* <TR><BR>
* #foreach $field in $fieldSet #begin<BR>
* <TD>$ml.rendered($field.DisplayName)</TD><BR>
* <TD>$ml.rendered($field)</TD><BR>
* #end<BR>
* </TR><BR>
* #end<BR>
* </TABLE>
* </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 + ")";
}
}