001 package hep.aida.ref.sql;
002
003 import static hep.aida.ref.sql.Implementation.*;
004
005 // Java
006 import java.util.ArrayList;
007 import java.util.Map;
008 import java.util.HashMap;
009 import java.io.InputStream;
010 import java.io.File;
011 import java.io.FileOutputStream;
012 import java.io.FileInputStream;
013 import java.io.ObjectOutputStream;
014 import java.io.ObjectInputStream;
015 import java.io.FileNotFoundException;
016 import java.io.IOException;
017 import java.security.AccessControlException;
018
019 // AIDA
020 import hep.aida.ITree;
021 import hep.aida.ITuple;
022 import hep.aida.IFilter;
023 import hep.aida.IEvaluator;
024 import hep.aida.ref.AidaUtils;
025 import hep.aida.ref.tree.Tree;
026 import hep.aida.ref.tuple.Tuple;
027 import hep.aida.ref.tuple.TupleFactory;
028
029 // Log4J
030 import org.apache.log4j.Logger;
031
032 // SQL
033 import java.sql.ResultSet;
034 import java.sql.DatabaseMetaData;
035 import java.sql.SQLException;
036
037 /** <code>SQLTupleFactory</code> creates {@link SQLTuple}s. It creates SQL tables
038 * if neccessary. It reuses some funtionality from
039 * {@link hep.aida.ref.tuple.TupleFactory}.
040 * <br>
041 * <code>SQLTupleFactory</code> contains several extensions to
042 * {@link hep.aida.ITupleFactory}:
043 * <ul>
044 * <li><code>void delete(String path);</code></li>
045 * <p><font color="#880088">
046 * <pre>
047 * $Log: SQLTupleFactory.java,v $
048 * Revision 1.79 2008/07/25 13:25:03 hrivnac
049 * oracle schema for tags supported improved
050 *
051 * Revision 1.78 2007/09/26 07:58:51 hrivnac
052 * cache savable fromjas menu
053 *
054 * Revision 1.77 2007/06/11 14:02:29 hrivnac
055 * better support for tags in Oracle
056 *
057 * Revision 1.76 2007/05/23 16:38:44 hrivnac
058 * logical connections for Plotter; better UML
059 *
060 * Revision 1.75 2007/05/22 23:33:20 hrivnac
061 * Plotter uses real arguments parsing
062 *
063 * Revision 1.74 2006/12/12 15:21:43 hrivnac
064 * McKoi replaced with Derby; uppercase-only dbs handled better; moving to JAS 0.8.3
065 *
066 * Revision 1.73 2006/11/22 23:55:36 hrivnac
067 * fixed non-double values, added intr database
068 *
069 * Revision 1.72 2006/11/15 16:16:01 hrivnac
070 * Oracle work fine in CERN, cache enabled
071 *
072 * Revision 1.71 2006/11/14 16:06:18 hrivnac
073 * improving Web Service
074 *
075 * Revision 1.70 2005/09/02 14:10:31 hrivnac
076 * moved to JAS3 0.8.2
077 *
078 * Revision 1.69 2005/02/10 20:07:52 hrivnac
079 * bug fixes to support new ColMan
080 *
081 * Revision 1.68 2004/11/22 18:09:53 hrivnac
082 * Oracle treatment
083 *
084 * Revision 1.67 2004/10/29 22:27:25 hrivnac
085 * imports corrected
086 *
087 * Revision 1.66 2004/10/28 15:25:40 hrivnac
088 * *** empty log message ***
089 *
090 * Revision 1.65 2004/10/22 15:32:59 hrivnac
091 * cleaned
092 *
093 * Revision 1.64 2004/10/21 15:46:49 hrivnac
094 * doc improved
095 *
096 * Revision 1.63 2004/10/21 10:39:32 hrivnac
097 * works wel with ColMan
098 *
099 * Revision 1.62 2004/10/20 23:02:29 hrivnac
100 * Types mapping simplified
101 *
102 * Revision 1.61 2004/10/14 13:39:33 hrivnac
103 * unknown types are mapped to Strings
104 *
105 * Revision 1.60 2004/10/13 12:25:39 hrivnac
106 * defaults work fine
107 *
108 * Revision 1.59 2004/09/02 16:13:25 hrivnac
109 * support for ColMan Replicator
110 *
111 * Revision 1.58 2004/09/02 12:16:37 hrivnac
112 * Filter works correctly between different dbs
113 *
114 * Revision 1.57 2004/07/12 10:47:16 hrivnac
115 * Bugs/ToDos
116 *
117 * Revision 1.56 2004/06/30 13:01:05 hrivnac
118 * oracle sceleton
119 *
120 * Revision 1.55 2004/06/23 08:04:53 hrivnac
121 * documentation fixes
122 *
123 * Revision 1.54 2004/06/22 08:52:32 hrivnac
124 * allow to skip compatibility test
125 *
126 * Revision 1.53 2004/06/16 21:56:43 hrivnac
127 * StringBuffer in SQLTuple.addRow() -> speed
128 *
129 * Revision 1.52 2004/05/22 15:12:59 hrivnac
130 * class id reformated
131 *
132 * Revision 1.51 2004/04/21 22:04:03 hrivnac
133 * Implementation.properties to configure Inplementation.java, no SQL dependency in code
134 *
135 * Revision 1.50 2004/04/21 13:41:32 hrivnac
136 * explicit dependency on MySQL removed
137 *
138 * Revision 1.49 2004/04/21 08:24:50 hrivnac
139 * some explicit MySQL dependencies removed
140 *
141 * Revision 1.48 2004/04/20 13:42:26 hrivnac
142 * logs updated
143 *
144 * Revision 1.47 2004/04/20 12:46:49 hrivnac
145 * tuples use always primitive types
146 *
147 * Revision 1.46 2004/04/20 09:28:08 hrivnac
148 * bug fixes
149 *
150 * Revision 1.45 2004/04/16 15:32:57 hrivnac
151 * Type.properties to setup Type.class
152 *
153 * Revision 1.44 2004/04/15 16:25:04 hrivnac
154 * type mapping improved
155 *
156 * Revision 1.43 2004/04/14 13:39:47 hrivnac
157 * 1.5 warnings fixed
158 *
159 * Revision 1.42 2004/04/13 15:45:54 hrivnac
160 * AIDA URL introduced
161 *
162 * Revision 1.41 2004/03/23 23:41:15 hrivnac
163 * new AidaUtils
164 *
165 * Revision 1.40 2004/02/10 14:50:58 hrivnac
166 * JavaDoc tags completed
167 *
168 * Revision 1.39 2004/02/04 17:02:14 hrivnac
169 * deleting of non-existing ntuple
170 *
171 * Revision 1.38 2003/12/01 13:18:20 hrivnac
172 * Prepared for beta3
173 *
174 * Revision 1.37 2003/11/26 16:09:46 hrivnac
175 * Functional EventSelector WebService
176 *
177 * Revision 1.36 2003/11/24 15:13:22 hrivnac
178 * Logging improved.
179 *
180 * Revision 1.35 2003/11/20 17:21:58 hrivnac
181 * Java 1.5 natively supported, Log4J reporting improved.
182 *
183 * Revision 1.34 2003/11/18 22:03:45 hrivnac
184 * Root benchmarks.
185 *
186 * Revision 1.33 2003/11/12 14:13:48 hrivnac
187 * Access to Pool Collections is provided.
188 *
189 * Revision 1.32 2003/11/10 15:34:08 hrivnac
190 * SQLTupleFactory documented
191 *
192 * Revision 1.31 2003/11/06 22:47:46 hrivnac
193 * Updated documentation.
194 *
195 * Revision 1.30 2003/11/06 16:54:16 hrivnac
196 * Merger works for SQL DBs
197 *
198 * Revision 1.29 2003/11/05 19:46:22 hrivnac
199 * - FreeHEP 1.2.1
200 * - JAIDA 3.2.1
201 *
202 * Revision 1.16 2003/10/22 17:59:39 hrivnac
203 * Refactored.
204 *
205 * Revision 1.15 2003/10/21 13:56:00 hrivnac
206 * Default values supported.
207 *
208 * Revision 1.14 2003/10/21 13:24:52 hrivnac
209 * Constructor from columnString implemented.
210 *
211 * Revision 1.9 2003/10/10 13:21:36 hrivnac
212 * Works between different DB technologies.
213 *
214 * Revision 1.8 2003/10/10 10:12:21 hrivnac
215 * All three testing pgms work.
216 *
217 * Revision 1.4 2003/10/08 09:27:49 hrivnac
218 * createFiltered(...) naive way.
219 *
220 * Revision 1.3 2003/10/02 10:00:40 hrivnac
221 * Cleaning.
222 *
223 * Revision 1.2 2003/09/30 16:46:02 hrivnac
224 * - Writing test in TestWrite
225 * - SQLTuple projections supported
226 *
227 * Revision 1.1 2003/09/30 14:11:48 hrivnac
228 * Can created SQLTuples.
229 *
230 * </pre>
231 * </font></p>
232 * @opt attributes
233 * @opt operations
234 * @opt types
235 * @opt visibility
236 * @version $Id: SQLTupleFactory.java,v 1.79 2008/07/25 13:25:03 hrivnac Exp $
237 * @author <a href="mailto:Julius.Hrivnac@cern.ch">J.Hrivnac</a> */
238 public class SQLTupleFactory extends TupleFactory {
239
240 /** Call superconstructor and register {@link ITree}.
241 * Read descriptions cache from the file or resource if possible.
242 * @param tree The {@link ITree} to be used with this {@link hep.aida.ITupleFactory}.
243 * @throws SQLTupleException if the {@link hep.aida.ITupleFactory} can't be created. */
244 public SQLTupleFactory(ITree tree) throws SQLTupleException {
245 super(tree);
246 assert !(tree instanceof Tree) : "SQLTuple works only with hep.aida.ref.tree.Tree";
247 this.tree = (Tree)tree;
248 try {
249 //setOptions(AidaUtils.parseOptions(this.tree.getOptions()));
250 setOptions(this.tree.getOptions().toString().replace('{', ' ').replace('}', ' '));
251 }
252 catch (SQLTupleException e) {
253 throw new SQLTupleException("Can't create SQLTupleFactory", e);
254 }
255 if (_descriptions.isEmpty()) {
256 readCache();
257 }
258 }
259
260 /** Create {@link SQLTuple}. Reuse (append) existing table, if exists,
261 * create new one otherwise.
262 * All other creating methods just re-shuffle arguments and call this method.
263 * @param path The path to the {@link ITuple}.
264 * @param title The title of the {@link ITuple}.
265 * @param columnName The names of columns.
266 * @param columnType The types of columns.
267 * @param options The options to be used to create the ITuple.
268 * @throws IllegalArgumentException if the {@link ITuple} can't be created. */
269 public ITuple create(String path,
270 String title,
271 String[] columnName,
272 Class[] columnType,
273 String options) throws IllegalArgumentException {
274 log.debug("Creating SQLTuple " + title + " in " + path);
275 unbox(columnType);
276 SQLTuple tuple = null;
277 try {
278 // NTuple doesn't exist => the table is created in database
279 String[] columnNameSQL = new String[columnName.length];
280 if (!tableExists(title)) {
281 log.debug("SQLTuple doesn't exist => creating");
282 int nColumns = columnName.length;
283 String def;
284 int eq;
285 String values = "";
286 for (int i = 0; i < nColumns; i++) {
287 def = null;
288 columnNameSQL[i] = columnName[i];
289 eq = columnName[i].indexOf("=");
290 if (eq > -1) {
291 def = columnName[i].substring(eq + 1 ).trim();
292 columnNameSQL[i] = columnName[i].substring(0 , eq).trim();
293 }
294 values += columnNameSQL[i] + " " + _accessor.sqlName(columnType[i]);
295 if (def != null) {
296 values += " default " + def;
297 }
298 if (i < nColumns - 1) {
299 values += ", ";
300 }
301 }
302 String[] args = {title, values};
303 _stmtSrc.update("createTable", args);
304 }
305 // NTuple exists and is incompatible with requested NTuple => nothing can be done
306 else if (!tableCompatible(title, columnNameSQL, columnType, null)) {
307 throw new IllegalArgumentException("Can't create SQLTuple " + title + " because it already exists in the database " + _accessor.db() + " with a different signature");
308 }
309 // Compatible NTuple exists => it is reused
310 else {
311 log.debug("SQLTuple exists with the same signature => reusing");
312 }
313 // NTuple is created
314 tuple = new SQLTuple(nameInPath(path), title, columnName, columnType, options);
315 // NTuple is added into tree
316 if (tree != null) {
317 tree.add("/" + parentPath(path), tuple);
318 }
319 }
320 catch(SQLTupleException e) {
321 log.error(Util.report("Can't create table " + title + " in " + path, e), e);
322 throw new IllegalArgumentException("Couldn't create table " + title + " in " + path, e);
323 }
324 // Return created NTuple
325 return tuple;
326 }
327
328 /** Just call the the master creation method.
329 * @param path The path to the {@link ITuple}.
330 * @param title The title of the {@link ITuple}.
331 * @param columnName The names of columns.
332 * @param columnType The types of columns.
333 * @throws IllegalArgumentException if the {@link ITuple} can't be created. */
334 public ITuple create(String path,
335 String title,
336 String[] columnName,
337 Class[] columnType) {
338 return create(path, title, columnName, columnType, "");
339 }
340
341 /** Split columns string into columnName and columnType arrays and call master creation method.
342 * @param path The path to the {@link ITuple}.
343 * @param title The title of the {@link ITuple}.
344 * @param columns The columns definition.
345 * @param options The options to be used to create the {@link ITuple}.
346 * @throws IllegalArgumentException if the {@link ITuple} can't be created. */
347 public ITuple create(String path,
348 String title,
349 String columns,
350 String options) throws IllegalArgumentException {
351 String[] col = columns.split(";");
352 String[] columnName = new String[col.length];
353 Class[ ] columnType = new Class[ col.length];
354 int b;
355 String type;
356 for (int i = 0; i < col.length; i++) {
357 col[i] = col[i].trim();
358 b = col[i].indexOf(" ");
359 if (b > -1) {
360 columnName[i] = col[i].substring(b + 1 ).trim();
361 columnType[i] = Type.fromString(col[i].substring(0 , b).trim());
362 }
363 else {
364 throw new IllegalArgumentException("Can't create NTuple from columns: " + columns);
365 }
366 }
367 unbox(columnType);
368 return create(path, title, columnName, columnType, options);
369 }
370
371 /** Just call the master creation method.
372 * @param path The path to the {@link ITuple}.
373 * @param title The title of the {@link ITuple}.
374 * @param columns The columns definition.
375 * @throws IllegalArgumentException if the {@link ITuple} can't be created. */
376 public ITuple create(String path,
377 String title,
378 String columns) {
379 return create(path, title, columns, "");
380 }
381
382 /** Filter {@link SQLTuple}. Reuse (append) existing table, if exists,
383 * create new one otherwise.
384 * All other creating methods just re-shuffle arguments and call this method.
385 * @param path The path to the {@link ITuple}.
386 * @param tuple The original {@link ITuple}.
387 * @param filter The {@link IFilter} to be used.
388 * @param columns The columns to be copied into the new {@link ITuple}.
389 * @return The filtered {@link ITuple} (the result). */
390 public ITuple createFiltered(String path,
391 ITuple tuple,
392 IFilter filter,
393 String[] columns) throws IllegalArgumentException {
394 log.debug("Creating filtered SQLTuple in " + path);
395 String fexpr = "true";
396 if (filter != null &&
397 !filter.expression().trim().equals("")) {
398 fexpr = filter.expression();
399 }
400 SQLTuple newTuple = null;
401 try {
402 // Prepare arrays and values
403 int nColumns = columns.length;
404 int[] columnId = new int[nColumns];
405 Class[] columnTypes = new Class[nColumns];
406 for (int i = 0; i < nColumns; i++) {
407 columnId[i] = tuple.findColumn(columns[i]);
408 columnTypes[i] = tuple.columnType(columnId[i]);
409 }
410 unbox(columnTypes);
411 String title = tuple.title();
412 // Check creatability
413 if (tuple instanceof SQLTuple &&
414 ! tableCreatable(title, columns, columnTypes, ((SQLTuple)(tuple)).accessor())) {
415 throw new IllegalArgumentException("Can't create NTuple " + path + " from existing source NTuple " + tuple.title());
416 }
417 // SQLTuple in the same DB, doesn't exist yet, supports local replication => it will be replicated localy
418 if (tuple instanceof SQLTuple &&
419 _accessor.equals(((SQLTuple)(tuple)).accessor()) &&
420 _accessor.supportsReplication() &&
421 ! tableExists(nameInPath(path))) {
422 // New NTuple
423 log.debug("SQLTuple will be replicated within the same DB");
424 String values = "";
425 for (int i = 0; i < nColumns; i++) {
426 values += columns[i];
427 if (i < nColumns - 1) {
428 values += ", ";
429 }
430 }
431 values += "";
432 String[] args = {nameInPath(path), values, tuple.title(), fexpr};
433 _stmtSrc.update("createTableSelect", args);
434 newTuple = new SQLTuple(nameInPath(path), nameInPath(path), columns, columnTypes, _options);
435 // NTuple is added into tree
436 if (tree != null) {
437 tree.add("/" + parentPath(path), newTuple);
438 }
439 }
440 // Either change of technology, change of DB, doesn't support local replication or new NTuple
441 else {
442 // NTuple already exists => resused and appended
443 if (tableExists(nameInPath(path))) {
444 log.debug("SQLTuple will be appended");
445 log.warn("NTuple " + path + " already exists => reusing");
446 newTuple = new SQLTuple(nameInPath(path), nameInPath(path), columns, columnTypes, _options);
447 }
448 // NTuple will be created
449 else {
450 log.debug("SQLTuple will be created elsewhere");
451 newTuple = (SQLTuple)create(nameInPath(path), nameInPath(path), columns, columnTypes, _options);
452 }
453 if (tuple.rows() > 0) {
454 tuple.start();
455 if (filter != null) {
456 filter.initialize(tuple);
457 }
458 while (tuple.next()) {
459 if (filter == null || filter.accept()) {
460 for (int i = 0; i < nColumns; i++) {
461 int j = columnId[i];
462 if (columnTypes[i] == Integer.TYPE) newTuple.fill(i, tuple.getInt(j));
463 else if (columnTypes[i] == Short.TYPE) newTuple.fill(i, tuple.getShort(j));
464 else if (columnTypes[i] == Long.TYPE) newTuple.fill(i, tuple.getLong(j));
465 else if (columnTypes[i] == Float.TYPE) newTuple.fill(i, tuple.getFloat(j));
466 else if (columnTypes[i] == Double.TYPE) newTuple.fill(i, tuple.getDouble(j));
467 else if (columnTypes[i] == Boolean.TYPE) newTuple.fill(i, tuple.getBoolean(j));
468 else if (columnTypes[i] == Byte.TYPE) newTuple.fill(i, tuple.getByte(j));
469 else if (columnTypes[i] == Character.TYPE) newTuple.fill(i, tuple.getChar(j));
470 else if (columnTypes[i] == ITuple.class) {
471 Tuple tOld = (Tuple) tuple.getObject(j);
472 newTuple.fill(i, tOld);
473 }
474 else newTuple.fill(i, tuple.getObject(j));
475 }
476 newTuple.addRow();
477 }
478 }
479 }
480 }
481 }
482 catch(SQLTupleException e) {
483 log.error(Util.report("Can't create table " + tuple.title() + " in " + path, e), e);
484 throw new IllegalArgumentException("Couldn't create table " + tuple.title() + " in " + path, e);
485 }
486 // Return created NTuple
487 return newTuple;
488 }
489
490 /** Just extract columnNames and calls the master filtering method.
491 * @param path The path to the {@link ITuple}.
492 * @param tuple The original {@link ITuple}.
493 * @param filter The {@link IFilter} to be used.
494 * @return The filtered {@link ITuple} (the result). */
495 public ITuple createFiltered(String path,
496 ITuple tuple,
497 IFilter filter) {
498 int nColumns = tuple.columns();
499 String[] columnNames = new String[nColumns];
500 for (int i = 0; i < nColumns; i++) {
501 columnNames[i] = tuple.columnName(i);
502 }
503 return createFiltered(path, tuple, filter, columnNames);
504 }
505
506 /** Describe NTuple (table) from the SQL database.
507 * Connection to database is only made the first time,
508 * the cached results are used after.
509 * @param name The name of the existing {@link ITuple} (within the actual DB).
510 * @param accessor The {@link Accessor} of existing table to be used.
511 * If <code>null</code> the current {@link Accessor} is used.
512 * @return Two arrays: <code>String[]</code> containing column names
513 * and <code>Class[]</code> containing column types.
514 * @throws SQLTupleException if the information can't be obtained. */
515 public Object[] tupleDescription(String name,
516 Accessor accessor) throws SQLTupleException {
517 // Initiate
518 if (accessor == null) {
519 accessor = _accessor;
520 }
521 String key = accessor.toString() + "#" + name;
522 int n = 0;
523 String[] names = null;
524 Class[] types = null;
525 // Is already in cache
526 if (_descriptions.containsKey(key)) {
527 log.debug("Reusing cached description for " + key);
528 return _descriptions.get(key);
529 }
530 // Ask database
531 else {
532 try {
533 int firstDot = name.indexOf(".");
534 if (firstDot >= 0) {
535 name = name.substring(firstDot + 1, name.length());
536 }
537 DatabaseMetaData dbmd = accessor.connection().getMetaData();
538 ResultSet rs = dbmd.getColumns(null, null, name, null);
539 ArrayList<String> names0 = new ArrayList<String>();
540 ArrayList<Class> types0 = new ArrayList<Class>();
541 String cName;
542 String cTypeName;
543 int cTypeIdx;
544 Class cType;
545 log.debug("Table " + name + " :");
546 int iName = rs.findColumn("COLUMN_NAME");
547 int iTypeIdx = rs.findColumn("DATA_TYPE");
548 int iTypeName = rs.findColumn("TYPE_NAME");
549 while (rs.next()) {
550 cName = rs.getString(iName);
551 cTypeIdx = rs.getInt( iTypeIdx);
552 cTypeName = rs.getString(iTypeName);
553 cType = Type.fromSql(cTypeIdx, cTypeName);
554 names0.add(cName);
555 types0.add(cType);
556 n++;
557 log.debug(" " + cName + "[" + cTypeName + "(" + cTypeIdx + ") -> " + cType + "]");
558 }
559 rs.close();
560 names = new String[n];
561 types = new Class[n];
562 names = names0.toArray(names);
563 types = types0.toArray(types);
564 }
565 catch (SQLTupleException e) {
566 throw new SQLTupleException("Can't get table information for table " + name, e);
567 }
568 catch (SQLException e) {
569 throw new SQLTupleException("Can't get table information for table " + name, e);
570 }
571 // Put into cache and return
572 Object[] description = new Object[2];
573 description[0] = names;
574 description[1] = unbox(types);
575 _descriptions.put(key, description);
576 return description;
577 }
578 }
579
580 /** Verify if NTuple (table) in the DB is compatible with provided columns description.
581 * All names and types should be the same.
582 * @param name The name of the existing {@link ITuple}.
583 * @param names0 The column names to be tested.
584 * @param classes0 The column types to be tested.
585 * @param accessor The {@link Accessor} of existing table to be used
586 * as source (reference). If <code>null</code> the current
587 * {@link Accessor} is used.
588 * @return true if <code>names0</code> and <code>classes0</code>
589 * are compatibe with existing {@link ITuple}.
590 * @throws SQLTupleException if the information can't be obtained. */
591 public boolean tableCompatible(String name,
592 String[] names0,
593 Class[] classes0,
594 Accessor accessor) throws SQLTupleException {
595 if (!_test) {
596 log.warn("Compatibility test skipped");
597 return true;
598 }
599 Object[] description = tupleDescription(name, accessor);
600 String[] names = (String[])(description[0]);
601 Class[] types = (Class[] )(description[1]);
602 if (names.length != names0.length) {
603 log.debug("NTuples are not compatible, they have different lengths: " + names.length + " != " + names0.length);
604 for (int i = 0; i < names.length; i++) {
605 log.debug("Existing NTuple " + i + " : " + names[i]);
606 }
607 for (int i = 0; i < names0.length; i++) {
608 log.debug("Requested NTuple " + i + " : " + names0[i]);
609 }
610 return false;
611 }
612 for (int i = 0; i < names.length; i++) {
613 if (!names[i].equals(names0[i]) || types[i] != classes0[i]) {
614 log.debug("Columns " + i + " are not compatible: " + names[i] + "(" + types[i] + ") != " + names0[i] + "(" + classes0[i] + ")");
615 return false;
616 }
617 }
618 return true;
619 }
620
621 /** Verify if new NTuple (table) can be created from the existing one.
622 * New names and types should be the subset of old ones.
623 * @param name The name of the existing {@link ITuple}.
624 * @param names0 The column names to be tested.
625 * @param classes0 The column types to be tested.
626 * @param accessor The {@link Accessor} of existing table to be used
627 * as source (reference). If <code>null</code> the current
628 * {@link Accessor} is used.
629 * @return <code>true</code> if the {@link ITuple},
630 * defined by <code>names0</code> and <code>classes0</code>
631 * can be ceated from the existing {@link ITuple}.
632 * @throws SQLTupleException if the information can't be obtained. */
633 public boolean tableCreatable(String name,
634 String[] names0,
635 Class[] classes0,
636 Accessor accessor) throws SQLTupleException {
637 Object[] description = tupleDescription(name, accessor);
638 String[] names = (String[])(description[0]);
639 Class[] types = (Class[] )(description[1]);
640 if (names0.length > names.length) {
641 return false;
642 }
643 boolean exists;
644 for (int i = 0; i < names0.length; i++) {
645 exists = false;
646 for (int j = 0; j < names.length; j++) {
647 if (!names[j].equals(names0[i]) || types[j] == classes0[i]) {
648 exists = true;
649 }
650 }
651 if (!exists) {
652 return false;
653 }
654 }
655 return true;
656 }
657
658 /** Verify if NTuple (table) exists in the DB.
659 * @param name The name of the existing {@link ITuple}.
660 * @return <code>true</code> if The {@link ITuple} exists.
661 * @throws SQLTupleException if the information can't be obtained. */
662 private boolean tableExists(String name) throws SQLTupleException {
663 if (_accessor.supportsOnlyUpperCase()) {
664 name = name.toUpperCase();
665 }
666 boolean exists = false;
667 try {
668 DatabaseMetaData dbmd = _accessor.connection().getMetaData();
669 ResultSet rs = dbmd.getColumns(null, null, name, null);
670 if (rs != null) {
671 while (rs.next()) {
672 exists = true;
673 break;
674 }
675 }
676 }
677 catch (SQLTupleException e) {
678 throw new SQLTupleException("Can't get table information for table " + name, e);
679 }
680 catch (SQLException e) {
681 throw new SQLTupleException("Can't get table information for table " + name, e);
682 }
683 return exists;
684 }
685
686 /** Create a filter from a expression. Currently, SQL expresion
687 * should be used within {@link SQLTuple}.
688 * @param expression The algebraic (or SQL in case of {@link SQLTuple}) expression.
689 * @return The created {@link IFilter}. */
690 public IFilter createFilter(String expression) {
691 return new SQLFilter(expression);
692 }
693
694 /** Create a filter from a expression and a number of rows to be processed.
695 * Currently, SQL expresion should be used within {@link SQLTuple} and all rows are
696 * processed in {@link SQLTuple}.
697 * @param expression The algebraic (or SQL in case of {@link SQLTuple}) expression.
698 * @param rowsToProcess The number of rows to be processed (ignoed for {@link SQLTuple}).
699 * @return The created {@link IFilter}. */
700 public IFilter createFilter(String expression, int rowsToProcess) {
701 return new SQLFilter(expression, rowsToProcess);
702 }
703
704 /** Create a filter from a expression and, number of rows to be processed
705 * and thye starting row. Currently, SQL expresion should be used within
706 * {@link SQLTuple} and all rows are processed in {@link SQLTuple}.
707 * @param expression The algebraic (or SQL in case of {@link SQLTuple}) expression.
708 * @param rowsToProcess The number of rows to be processed (ignoed for {@link SQLTuple}).
709 * @param startingRow The starting row to be processed (ignoed for {@link SQLTuple}).
710 * @return The created {@link IFilter}. */
711 public IFilter createFilter(String expression, int rowsToProcess, int startingRow) {
712 return new SQLFilter(expression, rowsToProcess, startingRow);
713 }
714
715 /** Create an evaluator from a expression. Currently, SQL expresion
716 * should be used within {@link SQLTuple}.
717 * @param expression The algebraic (or SQL in case of {@link SQLTuple}) expression.
718 * @return The created {@link IEvaluator}. */
719 public IEvaluator createEvaluator(String expression) {
720 return new SQLEvaluator(expression);
721 }
722
723 /** Delete NTuple (table) from DB if it exists. Remove it from {@link ITree}.
724 * @param path The path to {@link ITuple} to be deleted. */
725 public void delete(String path) throws SQLTupleException {
726 log.debug("Deleting SQLTuple in " + path);
727 try {
728 if (tableExists(nameInPath(path))) {
729 String[] args = {nameInPath(path)};
730 _stmtSrc.update("dropTable", args);
731 tree.rm(nameInPath(path));
732 }
733 }
734 catch (IllegalArgumentException e) {
735 log.debug("NTuple " + path + " can't be removed as it doesn't exist");
736 }
737 catch (SQLTupleException e) {
738 throw new SQLTupleException("Couldn't delete table " + path, e);
739 }
740 }
741
742 /** Replace numeric classes with primitive types where possible.
743 * @param cl The array of {@link Class}es representing types,
744 * numeric {@link Class}es are replaced with prmitive
745 * types where possible.
746 * @return The input array with numeric {@link Class}es replaced
747 * with primitive types where possible. */
748 private Class[] unbox(Class[] cl) {
749 for (int i = 0; i < cl.length; i++) {
750 if (cl[i] == Boolean.class ) cl[i] = Boolean.TYPE;
751 else if (cl[i] == Byte.class ) cl[i] = Byte.TYPE;
752 else if (cl[i] == Character.class) cl[i] = Character.TYPE;
753 else if (cl[i] == Short.class ) cl[i] = Short.TYPE;
754 else if (cl[i] == Integer.class ) cl[i] = Integer.TYPE;
755 else if (cl[i] == Long.class ) cl[i] = Long.TYPE;
756 else if (cl[i] == Float.class ) cl[i] = Float.TYPE;
757 else if (cl[i] == Double.class ) cl[i] = Double.TYPE;
758 }
759 return cl;
760 }
761
762 /** Do not test for compatibility if existing table is reused.
763 * This is dangerous. */
764 public void skipCompatibilityTest() {
765 log.warn("Compatibility test will be skipped - it is dangerous");
766 _test = false;
767 }
768
769 /** Register options.
770 * @param options The options to be associated with this {@link hep.aida.ITupleFactory}.
771 * @throws SQLTupleException if anything goes wrong. */
772 public void setOptions(String options) throws SQLTupleException {
773 _options = options;
774 _accessor = Accessor.getAccessor(options);
775 _stmtSrc = new StmtSrc(_accessor, "");
776 }
777
778 /** Give associated {@link Accessor}.
779 * @return The associated {@link Accessor}. */
780 public Accessor accessor() {
781 return _accessor;
782 }
783
784 /** Give associated {@link StmtSrc}.
785 * @return The associated {@link StmtSrc}. */
786 public StmtSrc stmtSrc() {
787 return _stmtSrc;
788 }
789
790 private String nameInPath(String path) {
791 int index = path.lastIndexOf(separatorChar);
792 if (index == -1) {
793 return path;
794 }
795 return path.substring(index + 1);
796 }
797
798 private String parentPath(String path) {
799 int index = path.lastIndexOf(separatorChar);
800 if (index == -1) {
801 return null;
802 }
803 return path.substring(0, index);
804 }
805
806 // BUG: exceptions are different for file and resource
807 /** Read (meta)data description from cache:
808 * <ul>
809 * <li>It tries first local file cache (default is <code>.SQLTuple.descriptions</code></li>
810 * <li>Then it reads resource <code>hep/aida/ref/sql/SQLTuple.descriptions</code></li>
811 * </ul> */
812 private void readCache() {
813 try {
814 InputStream is;
815 File file = new File(_cacheFile);
816 if (file.canRead()) {
817 is = new FileInputStream(_cacheFile);
818 log.info("Reading cache from " + _cacheFile);
819 }
820 else {
821 is = getClass().getClassLoader().getResourceAsStream(_cacheResource);
822 log.info("Reading cache from " + _cacheResource);
823 }
824 if (is != null) {
825 ObjectInputStream ois = new ObjectInputStream(is);
826 Object o = ois.readObject();
827 if (o instanceof Map) {
828 _descriptions = (Map<String, Object[]>)o;
829 }
830 else {
831 log.warn("Don't understand " + _cacheFile);
832 }
833 }
834 else {
835 log.debug("Can't find " + _cacheFile);
836 }
837 }
838 catch(AccessControlException e) {
839 log.warn("Can't access " + _cacheFile, e);
840 }
841 catch(FileNotFoundException e) {
842 log.debug("Can't find " + _cacheFile, e);
843 }
844 catch(IOException e) {
845 log.warn("Can't read " + _cacheFile, e);
846 }
847 catch(ClassNotFoundException e) {
848 log.warn("Don't understant " + _cacheFile, e);
849 }
850 }
851
852 /** Save current (meta)data descriptions into file cache
853 * (default is <code>.SQLTuple.descriptions</code>). */
854 public static void saveCache() {
855 try {
856 log.info("Writing cache to " + _cacheFile);
857 for (String key : _descriptions.keySet()) {
858 log.debug("> " + key);
859 }
860 FileOutputStream fos = new FileOutputStream(_cacheFile);
861 ObjectOutputStream oos = new ObjectOutputStream(fos);
862 oos.writeObject(_descriptions);
863 }
864 catch(FileNotFoundException e) {
865 log.warn("Can't find " + _cacheFile, e);
866 }
867 catch(IOException e) {
868 log.warn("Can't write " + _cacheFile, e);
869 }
870 }
871
872 /** Set (meta)data descriptions cache file anme.
873 * @param cacheFile The name of the cache file,
874 * default is <code>.SQLTuple.descriptions</code>. */
875 public static void setCache(String cacheFile) {
876 _cacheFile = cacheFile;
877 }
878
879 private Tree tree;
880
881 private final static char separatorChar = '/';
882
883 private StmtSrc _stmtSrc;
884
885 private Accessor _accessor;
886
887 private String _options;
888
889 private boolean _test = true;
890
891 private static Map<String, Object[]> _descriptions = new HashMap<String, Object[]>();
892
893 private static String _cacheFile = ".SQLTuple.descriptions";
894
895 private static String _cacheResource = "hep/aida/ref/sql/SQLTuple.descriptions";
896
897 /** Logging . */
898 private static Logger log = Logger.getLogger(SQLTupleFactory.class);
899
900 }