1 package org.apache.torque.util;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 import java.io.IOException;
23 import java.io.Writer;
24 import java.sql.Connection;
25 import java.util.Iterator;
26 import java.util.List;
27 import java.util.Vector;
28
29 import org.apache.commons.collections.OrderedMapIterator;
30 import org.apache.commons.logging.Log;
31 import org.apache.commons.logging.LogFactory;
32 import org.apache.torque.TorqueException;
33 import org.apache.torque.util.functions.SQLFunction;
34
35 import com.workingdogs.village.DataSetException;
36 import com.workingdogs.village.Record;
37 import com.workingdogs.village.Value;
38
39 /***
40 * <p>A utility to help produce aggregate summary information about a table.
41 * The default assumes that the underlying DB supports the SQL 99 Standard
42 * Aggregate functions, e.g. COUNT, SUM, AVG, MAX, & MIN. However, some
43 * non-standard functions (like MySQL's older LEAST instead of MIN can be
44 * handled programatically if needed (@see Aggregate class)</p>
45 *
46 * <P>Here is a simple example to generate the results of a query like:</P>
47 *
48 * <pre>
49 * SELECT EMPLOYEE, SUM(HOURS), MIN(HOURS), MAX(HOURS)
50 * FROM TIMESHEET WHERE TYPE = 1 GROUP BY EMPLOYEE ORDER BY EMPLOYEE ASC
51 * </pre>
52 * <p>Use the following code</p>
53 * <pre>
54 * SummaryHelper sHelp = new SummaryHelper();
55 * Criteria c = new Criteria();
56 * c.add(TimeSheetPeer.TYPE, 1);
57 * c.addAscendingOrderBy(TimeSheetPeer.EMPLOYEE);
58 * sHelper.addGroupBy(TimeSheetPeer.EMPLOYEE);
59 * sHelper.addAggregate(FunctionFactory.Sum(TimeSheetPeer.HOURS),"Hours");
60 * sHelper.addAggregate(FunctionFactory.Min(TimeSheetPeer.HOURS),"Min_Hrs");
61 * sHelper.addAggregate(FunctionFactory.Max(TimeSheetPeer.HOURS),"Max_Hrs");
62 * List results = sHelper.summarize( c );
63 * </pre>
64 * <p>The results list will be an ListOrderedMapCI with a key of either the
65 * group by column name or the name specified for the aggregate function (e.g.
66 * EMPLOYEE or Hours). The value will be a Village Value Class. Below is
67 * a simple way to do this. See the dumpResults* method code for a more
68 * complex example.
69 * </p>
70 * <pre>
71 * String emp = results.get("EMPLOYEE").asString();
72 * int hours = results.get("Hours").asInt();
73 * </pre>
74 * <p>
75 * Notes:</p>
76 * <p>
77 * If there are no group by columns specified, the aggregate is over the
78 * whole table. The from table is defined either via the Criteria.addAlias(...)
79 * method or by the first table prefix in an aggregate function.</p>
80 * <p>
81 * This will also work with joined tables if the criteria is creates as
82 * to create valid SQL.</p>
83 *
84 * @see org.apache.torque.util.functions.FunctionFactory
85 * @author <a href="mailto:greg.monroe@dukece.com">Greg Monroe</a>
86 * @version $Id$
87 */
88 public class SummaryHelper
89 {
90 static Log logger = LogFactory.getLog(SummaryHelper.class);
91
92 /*** A list of the group by columns names (e.g. TABLE.COLUMN) */
93 private List groupByColumns;
94 /***
95 * A ListOrderMapCI<String,Aggregate.Function> with the aggregate
96 * functions to use in generating results.
97 */
98 private ListOrderedMapCI aggregates;
99 /*** Flag for excluding unnamed columns. */
100 private boolean excludeExprColumns = false;
101
102 /***
103 * Simple constructor
104 */
105 public SummaryHelper()
106 {
107 super();
108 }
109
110 /***
111 * Return a list of ListOrderedMapCI objects with the results of the summary
112 * query. The ListOrderedMapCI objects have a key of the column name or
113 * function alias and are in the order generated by the query.
114 *
115 * @param crit The base criteria to build on.
116 * @return Results as a ListOrderMapCI<String,Values> object.
117 * @throws TorqueException
118 * @throws DataSetException
119 */
120 public List summarize( Criteria crit )
121 throws TorqueException, DataSetException
122 {
123 return summarize( crit, null );
124 }
125
126 /***
127 * Return a list of ListOrderedMapCI objects with the results of the
128 * summary query. The ListOrderedMapCI objects have a key of the column
129 * name or function alias and are in the order generated by the query.
130 *
131 * @param crit The base criteria to build on.
132 * @param conn The DB Connection to use.
133 * @return Results as a ListOrderMapCI<String,Values> object.
134 * @throws TorqueException
135 * @throws DataSetException
136 * @see ListOrderedMapCI
137 */
138 public List summarize( Criteria crit, Connection conn )
139 throws TorqueException, DataSetException
140 {
141 Criteria c = buildCriteria( crit );
142
143 List results;
144 if (conn == null)
145 {
146 results = BasePeer.doSelect(c);
147 }
148 else
149 {
150 results = BasePeer.doSelect(c, conn);
151 }
152
153 Iterator r = results.iterator();
154
155 Vector resultsList = new Vector(results.size());
156 while ( r.hasNext() )
157 {
158 ListOrderedMapCI recordMap = new ListOrderedMapCI();
159 Record rec = (Record) r.next();
160 String cName = null;
161 Value value = null;
162 for ( int i = 1; i <= rec.size(); i++ )
163 {
164 value = rec.getValue(i);
165 cName = rec.schema().column(i).name();
166 if ( cName == null || cName.equals("") )
167 {
168 if ( excludeExprColumns() ) {
169 continue;
170 }
171 cName = "Expr" + i;
172 }
173 recordMap.put(cName, value);
174 }
175 resultsList.add(recordMap);
176 }
177 return resultsList;
178 }
179
180 /***
181 * Builds the criteria to use in summarizing the information. Note that
182 * the criteria passed in will be modified.
183 *
184 * @param c The base criteria to build the summary criteria from.
185 * @return A criteria to use in summarizing the information.
186 * @throws TorqueException
187 */
188 public Criteria buildCriteria( Criteria c ) throws TorqueException {
189
190 c.getSelectColumns().clear();
191 c.getGroupByColumns().clear();
192
193 UniqueList criteriaSelectModifiers;
194 criteriaSelectModifiers = c.getSelectModifiers();
195
196 if (criteriaSelectModifiers != null
197 && criteriaSelectModifiers.size() > 0
198 && criteriaSelectModifiers.contains(SqlEnum.DISTINCT.toString()))
199 {
200 criteriaSelectModifiers.remove(SqlEnum.DISTINCT.toString());
201 }
202 c.setIgnoreCase(false);
203
204 List cols = null;
205 Iterator i = null;
206
207 cols = getGroupByColumns();
208 i = cols.iterator();
209 boolean haveFromTable = i.hasNext();
210 while ( i.hasNext() )
211 {
212 String col = (String) i.next();
213 c.addGroupByColumn( col );
214 c.addSelectColumn(col);
215 }
216 if ( haveFromTable )
217 logger.debug("From table defined by Group By Cols");
218
219
220 if ( ! haveFromTable && c.keys().hasMoreElements() )
221 {
222 haveFromTable = true;
223 logger.debug("From table defined by a where clause");
224 }
225
226 ListOrderedMapCI cMap = getAggregates();
227 OrderedMapIterator iMap = cMap.orderedMapIterator();
228 while ( iMap.hasNext() )
229 {
230 String key = (String) iMap.next();
231 SQLFunction f = (SQLFunction) iMap.getValue();
232 c.addAsColumn( key, f.toSQL() );
233 if ( ! haveFromTable )
234 {
235 String col = f.getArgument(0).toString();
236 if ( col.contains(".") )
237 {
238
239
240 c.add( col,(Object)(col + "=" + col), SqlEnum.CUSTOM );
241 haveFromTable = true;
242
243 String table = col.substring(0,col.indexOf('.'));
244 logger.debug("From table, '" + table +
245 "', defined from aggregate column");
246 }
247 }
248 }
249 if ( ! haveFromTable )
250 {
251 throw new TorqueException(
252 "No FROM table defined by the GroupBy set, " +
253 "criteria.setAlias, or specified function column!");
254 }
255 return c;
256 }
257
258 /***
259 * <p>
260 * Add a column that will be used to group the aggregate results by.
261 * This is a first added / first listed on SQL method. E.g.,
262 * </p>
263 * <pre>
264 * add(TablePeer.COL1);
265 * add(TablePeer.COL2);
266 * </pre>
267 *
268 * <p>Generates SQL like: SELECT .... GROUP BY Table.COL1, TABLE.COL2</p>
269 *
270 * @param column
271 */
272 public void addGroupBy( String column )
273 {
274 getGroupByColumns().add(column);
275 }
276
277 /***
278 * Add in an Aggregate function to the summary information.
279 *
280 * @param alias A valid SQL99 column identifier ([_A-Z0-9] no spaces and
281 * no key words, e.g. function names.
282 * @param function One of the inner classes from the Aggregate class.
283 */
284 public void addAggregate( String alias, SQLFunction function )
285 {
286 getAggregates().put( alias, function );
287 }
288
289 /***
290 * Resets the class internal variables to their initial states so
291 * the class can be re-used like a new class.
292 */
293 public void clear()
294 {
295 getGroupByColumns().clear();
296 getAggregates().clear();
297 setExcludeExprColumns(false);
298 }
299
300 public List getGroupByColumns()
301 {
302 if ( groupByColumns == null )
303 {
304 groupByColumns = new Vector();
305 }
306 return groupByColumns;
307 }
308
309 /***
310 * Get the order map list of aggregate functions to use in
311 * summarizing this table's informations. The key is used
312 * as the result column alias.
313 *
314 * @return the avgColumns. Will always return a ListOrderedMap object.
315 */
316 public ListOrderedMapCI getAggregates()
317 {
318 if ( aggregates == null )
319 {
320 aggregates = new ListOrderedMapCI();
321 }
322 return aggregates;
323 }
324
325 /***
326 * Convenience method to dump a summary results list to an output writer
327 * in a semi-CSV format. E.g., there is no handling of embedded
328 * quotes/special characters.
329 *
330 * @param out
331 * @param results
332 * @param includeHeader
333 * @throws IOException
334 */
335 public void dumpResults(Writer out, List results, boolean includeHeader )
336 throws IOException
337 {
338 Iterator i = results.iterator();
339 boolean first = includeHeader;
340
341 while ( i.hasNext() )
342 {
343 ListOrderedMapCI rec = (ListOrderedMapCI) i.next();
344 OrderedMapIterator rI = rec.orderedMapIterator();
345 String heading = "";
346 String recString = "";
347 while ( rI.hasNext() )
348 {
349 String colId = (String) rI.next();
350 if ( first )
351 {
352 heading += "\"" + colId + "\"";
353 if ( rI.hasNext() )
354 {
355 heading += ", ";
356 }
357 }
358 Value v = (Value) rI.getValue();
359 if ( v.isString() )
360 {
361 recString += "\"" + v.toString() + "\"";
362 }
363 else
364 {
365 recString += v.toString();
366 }
367 if ( rI.hasNext() )
368 {
369 recString += ", ";
370 }
371 }
372 if ( first )
373 {
374 first = false;
375 out.write(heading);
376 out.write("\n");
377 }
378 out.write(recString);
379 out.write("\n");
380 }
381 }
382
383 /***
384 * Should the results include unnamed columns, e.g. EXPR{index#}.
385 *
386 * @return the excludeExprColumns
387 */
388 public boolean excludeExprColumns()
389 {
390 return excludeExprColumns;
391 }
392
393 /***
394 * <p>Define if unnamed output columns which get labeled as EXPR{index#})
395 * should be included in the the output set.</p>
396 * <p>
397 * Note these are generally added by the criteria
398 * processing to handle special cases such as case insensitive ordering.
399 * </p>
400 *
401 * @param excludeExprColumns if True, these columns won't be included.
402 */
403 public void setExcludeExprColumns(boolean excludeExprColumns)
404 {
405 this.excludeExprColumns = excludeExprColumns;
406 }
407 }