1 /* 2 * $Source$ 3 * $Revision$ 4 * 5 * Copyright (C) 2000 William Chesters 6 * 7 * Part of Melati (http://melati.org), a framework for the rapid 8 * development of clean, maintainable web applications. 9 * 10 * Melati is free software; Permission is granted to copy, distribute 11 * and/or modify this software under the terms either: 12 * 13 * a) the GNU General Public License as published by the Free Software 14 * Foundation; either version 2 of the License, or (at your option) 15 * any later version, 16 * 17 * or 18 * 19 * b) any version of the Melati Software License, as published 20 * at http://melati.org 21 * 22 * You should have received a copy of the GNU General Public License and 23 * the Melati Software License along with this program; 24 * if not, write to the Free Software Foundation, Inc., 25 * 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA to obtain the 26 * GNU General Public License and visit http://melati.org to obtain the 27 * Melati Software License. 28 * 29 * Feel free to contact the Developers of Melati (http://melati.org), 30 * if you would like to work out a different arrangement than the options 31 * outlined here. It is our intention to allow Melati to be used by as 32 * wide an audience as possible. 33 * 34 * This program is distributed in the hope that it will be useful, 35 * but WITHOUT ANY WARRANTY; without even the implied warranty of 36 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 37 * GNU General Public License for more details. 38 * 39 * Contact details for copyright holder: 40 * 41 * William Chesters <williamc At paneris.org> 42 * http://paneris.org/~williamc 43 * Obrechtstraat 114, 2517VX Den Haag, The Netherlands 44 */ 45 46 package org.melati.poem; 47 48 import java.util.Enumeration; 49 import java.util.Vector; 50 import java.util.Hashtable; 51 import java.sql.ResultSet; 52 53 /** 54 * This is how you run low-level SQL queries, including joins, and get the 55 * results back in the form of convenient Melati {@link Field}s which can be 56 * rendered automatically in templates. A complement to the high-level 57 * {@link Table#selection()} on the one hand, and the low-level 58 * <TT>ResultSet</TT> on the other. 59 * 60 * 61 * <blockquote> 62 * 63 * TailoredQuery is specifically for when you want a few fields back 64 * (possibly joined from several tables) rather than whole objects. 65 * Suppose you want to do 66 * <blockquote><code> 67 * SELECT a.foo, b.bar FROM a, b WHERE a.something AND b.id = a.b 68 * </code></blockquote> 69 * There is nothing to stop you doing this with a good old ResultSet = 70 * Database.sqlQuery("SELECT ..."). However if you want to get the same 71 * effect, without forgoing the nice features offered by 72 * POEM---e.g. access control, rich metadata that makes rendering 73 * trivial---you can use a TailoredQuery. 74 * 75 * </blockquote> 76 * 77 * <p> 78 * 79 * If Postgresql's <TT>ResultSetMetaData</TT> supported <TT>getTableName</TT> 80 * even approximately, this would all be "even simpler" to use and somewhat 81 * more flexible. Because it doesn't, and because of the requirement to 82 * perform (as far as possible) read access checks on the records accessed 83 * during the query, the interface necessarily takes a slightly structured form 84 * rather than just being raw SQL---although the programmer does get complete 85 * freedom as far as the core <TT>WHERE</TT> clause is concerned. 86 * 87 * @see Table#selection(java.lang.String, java.lang.String, boolean) 88 */ 89 90 public class TailoredQuery { 91 92 protected Database database; 93 protected String sql; 94 int selectedColumnsCount; 95 Column<?>[] columns; 96 boolean[] isCanReadColumn; 97 Table<?>[] tables; 98 Table<?>[] tablesWithoutCanReadColumn; 99 100 Hashtable<String, Integer> table_columnMap = new Hashtable<String,Integer>(); 101 102 /** 103 * Construct a low-level SQL query, possibly including joins, from which 104 * results come back in form of automatically-renderable Melati 105 * <TT>Field</TT>s. The queries you can construct are of the form 106 * 107 * <BLOCKQUOTE><TT> 108 * SELECT <I>t1</I>.<I>c1</I>, <I>t2</I>.<I>c2</I> </TT>...<TT> 109 * FROM <I>t1</I>, <I>t2</I> </TT>...<TT>, 110 * <I>t10</I>, <I>t11</I> </TT>...<TT> 111 * WHERE <I>whereClause</I> 112 * ORDER BY <I>orderByClause</I> 113 * </TT></BLOCKQUOTE> 114 * 115 * You specify the columns you want to return 116 * (<TT><I>t1</I>.<I>c1</I></TT> ...) in the <TT>selectedColumns</TT> 117 * parameter, the selection criteria (including joins) in the 118 * <TT>whereClause</TT> parameter, and the ordering criteria in the 119 * <TT>orderByClause</TT>. If your <TT>whereClause</TT> or 120 * <TT>orderByClause</TT> use tables <TT><I>t10</I></TT> ... not implied 121 * by <TT>selectedColumns</TT>, you must include them in the 122 * <TT>otherTables</TT> parameter. 123 * 124 * <P> 125 * 126 * Note that there is no provision for aliasing, which does restrict the 127 * available queries somewhat. 128 * 129 * <P> 130 * 131 * To get the results of the query, use <TT>selection()</TT> (below). 132 * 133 * <P> 134 * 135 * Example: 136 * <BLOCKQUOTE><PRE> 137 * Column[] columns = { 138 * database.getUserTable().getNameColumn(), 139 * database.getGroupTable().getNameColumn(), 140 * }; 141 * Table[] tables = { database.getGroupMembershipTable() }; 142 * 143 * TailoredQuery q = 144 * new TailoredQuery( 145 * columns, tables, 146 * "\"user\" = \"user\".id AND \"group\" = \"group\".id", 147 * null); 148 * 149 * for (Enumeration ms = q.selection(); ms.hasMoreElements();) { 150 * FieldSet fs = (FieldSet)ms.nextElement(); 151 * System.out.println(fs.get("user_name").getCookedString(PoemLocale.HERE, DateFormat.MEDIUM) + 152 * ", " + 153 * fs.get("group_name").getCookedString(PoemLocale.HERE, DateFormat.MEDIUM)); 154 * } 155 * </PRE></BLOCKQUOTE> 156 * 157 * @param selectedColumns The columns you want to select out 158 * @param otherTables Tables aside from those to which your 159 * <TT>selectedColumns</TT> are attached which 160 * you need to use in the <TT>whereClause</TT> 161 * @param whereClause Search criteria for your query; note that 162 * you will have to do any necessary quoting of 163 * identifiers/values yourself (or use 164 * <TT>Column.quotedName</TT> and 165 * <TT>PoemType.quotedRaw</TT>) 166 * @param orderByClause Ordering criteria for your query 167 * 168 * @see #selection 169 * @see Column#quotedName() 170 * @see BasePoemType#quotedRaw(java.lang.Object) 171 */ 172 173 public TailoredQuery(Column<?>[] selectedColumns, Table<?>[] otherTables, 174 String whereClause, String orderByClause) { 175 this(null, selectedColumns, otherTables, whereClause, orderByClause); 176 } 177 178 /** 179 * Same as without the first argument except that it is inserted 180 * between <code>SELECT</code> and the column list. 181 * 182 * @param modifier HACK Allow SQL modifier eg DISTINCT 183 * @param selectedColumns 184 * @param otherTables 185 * @param whereClause 186 * @param orderByClause 187 * @see #TailoredQuery(Column[], Table[], String, String) 188 */ 189 public TailoredQuery(String modifier, 190 Column<?>[] selectedColumns, Table<?>[] otherTables, 191 String whereClause, String orderByClause) { 192 193 this.database = selectedColumns[0].getDatabase(); 194 195 // Make a list of all the tables used 196 197 Vector<Table<?>> tablesV = new Vector<Table<?>>(); 198 199 for (int c = 0; c < selectedColumns.length; ++c) { 200 Table<Persistent> table = selectedColumns[c].getTable(); 201 if (!tablesV.contains(table)) 202 tablesV.addElement(table); 203 } 204 for (int t = 0; t < otherTables.length; ++t) 205 if (!tablesV.contains(otherTables[t])) 206 tablesV.addElement(otherTables[t]); 207 208 tables = new Table[tablesV.size()]; 209 tablesV.copyInto(tables); 210 211 // Record the access protection sources for all the tables used (of course 212 // this doesn't include computed `Persistent.assertCanRead's written by the 213 // programmer). Make up a list of all the columns we need, included any 214 // `canRead' access control columns for tables. 215 216 Vector<Column<?>> columnsV = new Vector<Column<?>>(); 217 Vector<Integer> canReadColumnIndices = new Vector<Integer>(); 218 Vector<Table<?>> tablesWithoutCanReadColumnV = new Vector<Table<?>>(); 219 220 selectedColumnsCount = selectedColumns.length; 221 for (int c = 0; c < selectedColumns.length; ++c) { 222 columnsV.addElement(selectedColumns[c]); 223 } 224 225 for (int t = 0; t < tables.length; t++) { 226 Table<?> table = tables[t]; 227 Column<Capability> canRead = table.canReadColumn(); 228 if (canRead == null) { 229 // No specific canRead column, revert to the table default protection 230 if (!tablesWithoutCanReadColumnV.contains(table)) 231 tablesWithoutCanReadColumnV.addElement(table); 232 } 233 else { 234 if (!columnsV.contains(canRead)) { 235 canReadColumnIndices.addElement(new Integer(columnsV.size())); 236 columnsV.addElement(canRead); 237 } else { 238 canReadColumnIndices.addElement(new Integer(columnsV.indexOf(canRead))); 239 } 240 } 241 } 242 243 this.columns = new Column[columnsV.size()]; 244 columnsV.copyInto(this.columns); 245 246 isCanReadColumn = new boolean[columnsV.size()]; 247 for (int i = 0; i < canReadColumnIndices.size(); ++i) { 248 int c = ((Integer)canReadColumnIndices.elementAt(i)).intValue(); 249 isCanReadColumn[c] = true; 250 } 251 252 this.tablesWithoutCanReadColumn = new Table[tablesWithoutCanReadColumnV.size()]; 253 tablesWithoutCanReadColumnV.copyInto(this.tablesWithoutCanReadColumn); 254 255 // Make up the SQL for the query 256 257 StringBuffer sqlLocal = new StringBuffer(); 258 259 sqlLocal.append("SELECT "); 260 261 if (modifier != null) { 262 sqlLocal.append(modifier); 263 sqlLocal.append(' '); 264 } 265 266 for (int c = 0; c < columnsV.size(); ++c) { 267 if (c > 0) sqlLocal.append(", "); 268 Column<?> column = (Column<?>)columnsV.elementAt(c); 269 sqlLocal.append(column.getTable().quotedName()); 270 sqlLocal.append('.'); 271 sqlLocal.append(column.quotedName()); 272 } 273 274 sqlLocal.append(" FROM "); 275 276 for (int t = 0; t < tables.length; ++t) { 277 if (t > 0) sqlLocal.append(", "); 278 sqlLocal.append((tables[t]).quotedName()); 279 } 280 281 if (whereClause != null && !whereClause.trim().equals("")) { 282 sqlLocal.append(" WHERE "); 283 sqlLocal.append(whereClause); 284 } 285 286 if (orderByClause != null && !orderByClause.trim().equals("")) { 287 sqlLocal.append(" ORDER BY "); 288 sqlLocal.append(orderByClause); 289 } 290 291 this.sql = sqlLocal.toString(); 292 293 // Set up mappings from column name (<table>_<column>) to position 294 // (including the canRead columns, if anyone ever wants them) 295 296 for (int c = 0; c < columnsV.size(); ++c) { 297 Column<?> column = columnsV.elementAt(c); 298 table_columnMap.put( 299 column.getTable().getName() + "_" + column.getName(), 300 new Integer(c)); 301 } 302 } 303 304 /** 305 * Run the query. 306 * 307 * 308 * <P> 309 * 310 * Here's an example of presenting the results of a <TT>TailoredQuery</TT> in 311 * a WebMacro template: 312 * 313 * <BLOCKQUOTE><TT> 314 * <TABLE><BR> 315 * #foreach $fieldSet in $tailoredQuery.selection() #begin<BR> 316 * <TR><BR> 317 * #foreach $field in $fieldSet #begin<BR> 318 * <TD>$ml.rendered($field.DisplayName)</TD><BR> 319 * <TD>$ml.rendered($field)</TD><BR> 320 * #end<BR> 321 * </TR><BR> 322 * #end<BR> 323 * </TABLE> 324 * </TT></BLOCKQUOTE> 325 * 326 * <P> 327 * 328 * Read access checks are performed against the POEM access token associated 329 * with the thread running this method (see 330 * <TT>PoemThread.accessToken()</TT>) on all the tables implied by the 331 * <TT>selectedColumns</TT> and <TT>otherTables</TT> arguments given at 332 * construct time. If the checks fail for a given row, all the fields in the 333 * corresponding <TT>FieldSet</TT> are booby-trapped to throw the relevant 334 * <TT>AccessPoemException</TT> (<I>all</I> of them, because, 335 * without parsing your <TT>whereClause</TT>, there's no way for POEM to 336 * know which 337 * columns are `tainted'; note that it's probably possible for you to bypass 338 * access checks by using sub-SELECTs). 339 * 340 * <P> 341 * 342 * Normally, Melati's response to an "access-denied" fields is to terminate 343 * template expansion and ask the user to log in, then continue where they 344 * were left off. If, however, you put 345 * <TT>$melati.setPassbackExceptionHandling()</TT> 346 * at the top of the template, or in the servlet, 347 * they will be rendered as warnings by <I>e.g.</I> <TT>HTMLMarkupLanguage</TT>. 348 * 349 * <P> 350 * 351 * NOTE Since this way of doing queries involves named fields rather 352 * than whole <TT>Persistent</TT> objects, it inevitably bypasses any 353 * Java-coded access rules put in place by the programmer by overriding 354 * <TT>Persistent.assertCanRead</TT>. 355 * 356 * @see FieldSet#elements() 357 * @see FieldSet#get(java.lang.String) 358 * @see PoemThread#accessToken() 359 * @see Persistent#assertCanRead(org.melati.poem.AccessToken) 360 * Used in org.melati.template.MarkupLanguage#rendered(Object) 361 * 362 * @return 363 * An <TT>Enumeration</TT> of <TT>FieldSet</TT>s, one per row returned from 364 * the DBMS. You can invoke each <TT>FieldSet</TT>'s <TT>elements</TT> 365 * method to get an <TT>Enumeration</TT> of all the <TT>Field</TT>s in the 366 * row, ready for rendering. Or you can fetch them by name using the 367 * <TT>FieldSet</TT>'s <TT>get</TT> method. A field corresponding to 368 * column <TT><I>col</I></TT> of table <TT><I>tab</I></TT> is named 369 * <TT><I>tab</I>_<I>col</I></TT>. 370 */ 371 372 public Enumeration<FieldSet> selection() { 373 return new TailoredResultSetEnumeration<FieldSet>(this, database.sqlQuery(sql)); 374 } 375 376 /** 377 * Return an Enumeration of the columns in the first row of 378 * a ResultSet. 379 */ 380 public class FirstRawTailoredResultSetEnumeration<T> 381 extends TailoredResultSetEnumeration<T> { 382 383 /** 384 * Retrieve the first row of a {@link TailoredQuery}. 385 */ 386 public FirstRawTailoredResultSetEnumeration(TailoredQuery query, 387 ResultSet resultSet) { 388 super(query, resultSet); 389 } 390 391 protected Object mapped(ResultSet them) { 392 checkTableAccess(); 393 for (int c = 1; c < query.columns.length; ++c) 394 column(them, c); 395 return column(them, 0); 396 } 397 } 398 399 /** 400 * @return the first row of this <code>TailoredQuery</code> 401 */ 402 public Enumeration<Object> selection_firstRaw() { 403 return new FirstRawTailoredResultSetEnumeration<Object>(this, 404 database.sqlQuery(sql)); 405 } 406 407 /** 408 * The SQL of the query. 409 * {@inheritDoc} 410 * @see java.lang.Object#toString() 411 */ 412 public String toString() { 413 return super.toString() + "(SQL=" + sql + ")"; 414 } 415 }