Source code: org/acs/damsel/srvr/db/AssetDB.java
1 package org.acs.damsel.srvr.db;
2
3 import java.io.*;
4 import java.lang.reflect.*;
5 import java.sql.*;
6 import java.util.*;
7
8 import org.acs.damsel.srvr.*;
9 import org.acs.damsel.srvr.asset.*;
10 import org.acs.damsel.srvr.collection.*;
11 import org.acs.damsel.srvr.collection.Collection;
12 import org.acs.damsel.srvr.schema.*;
13 import org.acs.damsel.srvr.user.*;
14 import org.apache.log4j.*;
15 import org.acs.damsel.srvr.schema.SchemaException;
16 import java.sql.SQLException;
17 import java.lang.Boolean;
18 import org.acs.damsel.srvr.Config;
19 import org.acs.damsel.srvr.db.DBUtils;
20 import org.acs.damsel.srvr.group.*;
21
22 /**
23 * <p>Title: High-level database abstraction that deals with domain objects</p>
24 * <p>Description: A singleton class which abstracts access to the sql database;
25 * including adding to, removing from, and querying.
26 * This class deals with domain objects. SQL generation and JDBC connection
27 * handling are peformed by MidAssetDB and LowAssetDB, respectively.
28 *
29 * The general form of an interaction with the AssetDB class is as follows:
30 * 1. Get a handle on the AssetDB object
31 * 2. Call one of the high level methods (addAsset, getSchema, etc)
32 * 3. That method extracts the information from the domain object and calls a
33 * middle-tier method.
34 * 4. The middle-tier method takes the information and prepares SQL code to
35 * accomplish the desired task. Note that the middle-tier methods are typically
36 * more generic than the high level methods. For instance, there are many
37 * add* methods, but only one insert. The SQL code is passed on to a low level
38 * method.
39 * 5. The low-level method (of which there are only a few) is reponsible for
40 * opening the connection to the database and handling the transaction. It also
41 * prepares the result (i.e., a vector of vectors of strings) in the case of a query,
42 * as well as a vector of result metadata. </p>
43 * 6. From there, the results are propogated back up through the layers. In the
44 * case of a get* method, the high level method takes the results and constructs
45 * an object of the appropriate type.
46 *
47 * **********************************************************************
48 * This class is actually now the "HighAssetDB" class and contains only the
49 * high level methods that AssetDB has. These methods include add, delete,
50 * get, and existence methods for things such as Collection, Schema, Assets,
51 * and Users.
52 * ***********************************************************************
53 *
54 * @version 1.9
55 */
56
57 public class AssetDB {
58 private static AssetDB instance = null;
59 private static Logger log = Logger.getLogger(AssetDB.class);
60 private MidAssetDB midAssetDB = null;
61
62 private AssetDB() throws SQLException {
63 Config config = Config.instance();
64 BasicConfigurator.resetConfiguration();
65 PropertyConfigurator.configure(config.getLogPropertiesFileName());
66 midAssetDB = MidAssetDB.instance();
67 }
68
69 /**
70 * This is to be used instead of the construction because it is a sigleton
71 * class. Establishes a connection to the database if one does not already exist
72 * and throws a SQLException if an error occurs.
73 * @return handle on the AssetDB object
74 * @throws SQLException
75 */
76 public static AssetDB instance() throws SQLException {
77 if (instance == null) {
78 instance = new AssetDB();
79 }
80 return instance;
81 }
82
83 /**
84 * Constructs a SQL query for all assets and returns the result. Throws
85 * a SQLException if the SQL is malformed, there is not a database connection,
86 * etc.
87 * @return CollectionView, the results of the search
88 * @throws SQLException
89 */
90 public CollectionView getAllAssets() throws SQLException {
91 log.debug("Entering AssetDB.getAllAssets()");
92 CollectionView myCol = new CollectionView();
93 String tagName = null;
94 String tagValue = null;
95 Asset a;
96 AssetDescriptor ad = null;
97 AssetDescriptorCollection adc = null;
98 Vector resultNames = new Vector();
99 Vector tableNames = new Vector();
100 resultNames.add("*");
101 tableNames.add("AssetTable");
102
103 Table assetResultTable = midAssetDB.andQuery(resultNames, tableNames,
104 new Vector(), new Vector());
105
106 resultNames = new Vector();
107 tableNames = new Vector();
108 resultNames.add("*");
109 tableNames.add("AssetMetaData");
110
111 Table assetMetaDataTable = midAssetDB.andQuery(resultNames, tableNames,
112 new Vector(), new Vector());
113
114 String FileName = new String();
115
116 for (int i = 0; i < assetResultTable.getRowCount(); i++) {
117 a = new Asset();
118 adc = new AssetDescriptorCollection();
119 for (int j = 0; j < assetResultTable.getSizeMetaData(); j++) {
120 ad = new AssetDescriptor();
121 tagName = assetResultTable.getMetaDataElement(j);
122 tagValue = assetResultTable.getResultsElement(i, j);
123 if (tagName.equals("FileName")) {
124 FileName = tagValue;
125 }
126 ad.setTag(tagName);
127 ad.setValue(tagValue);
128 adc.addAssetDescriptor(ad);
129 }
130 for (int k = 0; k < assetMetaDataTable.getRowCount(); k++) {
131 if (FileName.equals(assetMetaDataTable.getResultsElement(k, 0))) {
132 ad = new AssetDescriptor();
133 tagName = assetMetaDataTable.getResultsElement(k, 1);
134 tagValue = assetMetaDataTable.getResultsElement(k, 2);
135 ad.setTag(tagName);
136 ad.setValue(tagValue);
137 adc.addAssetDescriptor(ad);
138 }
139 }
140 adc.addAssetDescriptor(ad);
141 a.setAssetDescriptors(adc);
142 //add the asset to the Collection
143 myCol.addAsset(a);
144 }
145 log.debug("Exiting AssetDB.getAllAssets()");
146 return myCol;
147 }
148
149
150
151 /**
152 * A high level method for adding an asset to a given collection owned by a
153 * given user. Note that the username is necessary because collections are
154 * uniquely identified by both their own name and the name of their owner.
155 * @param a the asset to be added to the database
156 * @param collectionName , the name of the collection the asset is to be added to
157 * @return int, the number of rows affected by the add, either 1 or 0
158 * @throws SQLException
159 */
160 public int addAsset(Asset a, String collectionName) throws SQLException {
161 log.debug("Entering AssetDB.addAsset(Asset, String)");
162 Vector metaDataTags = new Vector();
163 Vector metaDataValues = new Vector();
164 Vector extraMetaDataTags = new Vector();
165 Vector extraMetaDataValues = new Vector();
166 AssetDescriptor ad = null;
167 String fileName = new String();
168
169 // Runs through all the asset descriptors and sorts the meta data tags
170 // into either the AssetTable group (metaData) or the
171 // AssetMetaData group (extraMetaData)
172
173 for (Iterator i = a.getAssetDescriptors().iterator(); i.hasNext(); ) {
174 ad = (AssetDescriptor) i.next();
175 if (ad.getTag().equals("FileName")) {
176 metaDataTags.add(ad.getTag());
177 metaDataValues.add(ad.getValue());
178 fileName = ad.getValue();
179 } // end of if statement
180 else if (ad.getTag().equals("PermissionID") ||
181 ad.getTag().equals("OwnerName") ||
182 ad.getTag().equals("GroupName")) {
183 metaDataTags.add(ad.getTag());
184 metaDataValues.add(ad.getValue());
185 } // end of else if statement
186 else {
187 extraMetaDataTags.add(ad.getTag());
188 extraMetaDataValues.add(ad.getValue());
189 } // end of else statement
190 } // end of for loop
191
192 Vector assetsCollectionsTableColumnValues = new Vector();
193 Vector assetsCollectionsTableColumnNames = new Vector();
194
195 assetsCollectionsTableColumnValues.add( (String) metaDataValues.get(
196 metaDataTags.indexOf("FileName")));
197 assetsCollectionsTableColumnValues.add(collectionName);
198
199 assetsCollectionsTableColumnNames.add("FileName");
200 assetsCollectionsTableColumnNames.add("CollectionName");
201
202 int alternateReturnValue = 0;
203 if (!isAssetInCollection(a.getFileName(), collectionName)) {
204 alternateReturnValue = midAssetDB.insert("AssetsCollectionsTable",
205 assetsCollectionsTableColumnNames,
206 assetsCollectionsTableColumnValues);
207 } // end of if statement
208 if (!isAssetInDB(a)) {
209 midAssetDB.insert("AssetTable", metaDataTags, metaDataValues);
210 Iterator it = extraMetaDataTags.iterator();
211 int count = 0;
212 Vector tempValueVec;
213 Vector tempTagVec = new Vector();
214 tempTagVec.add("FileName");
215 tempTagVec.add("MetaDataTag");
216 tempTagVec.add("Value");
217 while (it.hasNext()) {
218 tempValueVec = new Vector();
219 tempValueVec.add(fileName);
220 tempValueVec.add(extraMetaDataTags.elementAt(count));
221 tempValueVec.add(extraMetaDataValues.elementAt(count));
222 midAssetDB.insert("AssetMetaData", tempTagVec, tempValueVec);
223 count++;
224 it.next();
225 } // end of while loop
226 } // end of if statement
227 log.debug("Exiting AssetDB.addAsset(Asset, String)");
228 return alternateReturnValue;
229 } // end of method AddAsset
230
231 /**
232 * updateAsset works by first deleting the asset from the AssetTable and its
233 * metadata from the AssetMetaData table, and then re-adding the asset
234 * to both of those tables.
235 *
236 * @param asset : the new asset you want in the database
237 * @throws SQLException
238 */
239
240 public void updateAsset(Asset asset) throws SQLException {
241 log.debug("Entering AssetDB.updateAsset(Asset)");
242 String fileName = asset.getFileName();
243
244 Vector columnNames = new Vector();
245 Vector columnValues = new Vector();
246 columnNames.add("FileName");
247 columnValues.add(fileName);
248 midAssetDB.delete("AssetTable", columnNames, columnValues);
249 midAssetDB.delete("AssetMetaData", columnNames, columnValues);
250
251 this.addAsset(asset, "AllAssets");
252 log.debug("Exiting AssetDB.updateAsset(Asset)");
253 }
254
255 /**
256 * A high level method for deleting an Asset from the database.
257 * This only deletes the asset from the AssetTable, not the AssetsCollectionsTable
258 * @param a , the asset to be deleted
259 * @return int, the number of rows affected by the operation, 1 or 0
260 * @throws SQLException
261 */
262 public int deleteAsset(Asset a) throws SQLException {
263 log.debug("Entering AssetDB.deleteAsset(Asset)");
264 String fileName = a.getFileName();
265 File assetFile = new File(Config.instance().getAssetPrefix() + fileName);
266
267 Vector columnNames = new Vector();
268 Vector columnValues = new Vector();
269 columnNames.add("FileName");
270 columnValues.add(fileName);
271 assetFile.delete();
272 midAssetDB.delete("AssetTable", columnNames, columnValues);
273 midAssetDB.delete("UsersFavorites", columnNames, columnValues);
274 log.debug("Exiting AssetDB.deleteAsset(Asset)");
275 return midAssetDB.delete("AssetMetaData", columnNames, columnValues);
276 }
277
278 /**
279 * A high level method for adding a Schema to both the SchemaTable and the
280 * SchemasTagTable in the database.
281 * @param s , the schema to be added to the database
282 * @return int, the number of rows (in SchemasTagsTable) affected by the operation,
283 * 0 if nothing was added.
284 * @throws SQLException
285 */
286 public int addSchema(Schema s) throws SQLException {
287 log.debug("Entering AssetDB.addSchema(Schema)");
288 String schemaName = s.getName();
289 try {
290 if (!s.isInSchema("WebFileName") && !schemaName.endsWith("Mask")) {
291 MetaDataTag mdt = new MetaDataTag();
292 mdt.setName("WebFileName");
293 s.addTag(mdt);
294 }
295 if (!s.isInSchema("FileName") && !schemaName.endsWith("Mask")) {
296 MetaDataTag mdt = new MetaDataTag();
297 mdt.setName("FileName");
298 s.addTag(mdt);
299 }
300 }
301 catch (SchemaException ex) {
302 log.error("Caught unexpected Schema Exception in AssetDB.addSchema "+ex.getMessage());
303 }
304
305 MetaDataTag tag;
306 int numTags = 0;
307 Vector tagNames = s.getTags();
308 Vector columnNames = new Vector();
309 Vector columnValues = new Vector();
310 columnNames.add("SchemaName");
311 columnNames.add("OwnerName");
312 columnNames.add("GroupName");
313 columnNames.add("PermissionID");
314 columnValues.add(s.getName());
315 columnValues.add(s.getOwnerName());
316 columnValues.add(s.getGroupName());
317 columnValues.add(s.getPermissionID());
318 if (this.isSchemaInDB(s)) {
319 return 0;
320 }
321 else {
322 midAssetDB.insert("SchemaTable", columnNames, columnValues);
323
324 columnNames.clear();
325 columnValues.clear();
326 columnNames.add("SchemaName");
327 columnNames.add("MetaDataTag");
328 for (Iterator i = tagNames.iterator(); i.hasNext(); ) {
329 tag = (MetaDataTag) i.next();
330 columnValues.clear();
331 columnValues.add(schemaName);
332 columnValues.add(tag.getName());
333 midAssetDB.insert("SchemasTagsTable", columnNames, columnValues);
334 numTags++;
335 }
336 log.debug("Exiting AssetDB.addSchema(Schema)");
337 return numTags;
338 }
339 }
340
341 /**
342 * This method adds a metaDataTag of name tag to the SchemasTagsTable, to the schema
343 * schemaName.
344 * @param tag name of metaDataTag to be added.
345 * @param schemaName name of schema tag will be added to.
346 * @return int, number of rows affected, either 1 or 0.
347 * @throws SQLException
348 */
349 public int addSchemaTag(MetaDataTag tag, String schemaName) throws
350 SQLException {
351 Vector columnNames = new Vector();
352 Vector columnValues = new Vector();
353 columnNames.add("SchemaName");
354 columnNames.add("MetaDataTag");
355 columnValues.add(schemaName);
356 columnValues.add(tag.getName());
357 if (this.isSchemaTagInTable(tag, schemaName)) {
358 return 0;
359 }
360 return midAssetDB.insert("SchemasTagsTable", columnNames, columnValues);
361 }
362
363 /**
364 * A high level method for deleting a Schema from the database. Note that this method
365 * deletes the schema from the SchemaTable and the SchemasTagsTable.
366 * @param s the schema to be deleted from the database.
367 * @return int, the number of rows affected by the operation, 1 or 0
368 * @throws SQLException
369 */
370 public int deleteSchema(Schema s) throws SQLException {
371 log.debug("Entering AssetDB.deleteSchema(Schema)");
372 // if the schema is null, return 0, as no rows were affected by the operation
373 if(s == null)
374 return 0;
375 String schemaName = s.getName();
376 Vector tagNames = s.getTags();
377 Vector columnNames = new Vector();
378 Vector columnValues = new Vector();
379 columnNames.add("SchemaName");
380 columnValues.add(schemaName);
381 log.debug("Exiting AssetDB.deleteSchema(Schema)");
382 midAssetDB.delete("SchemasTagsTable", columnNames, columnValues);
383 return midAssetDB.delete("SchemaTable", columnNames, columnValues);
384 }
385
386 /**
387 * This method deletes the specified MetaDataTag in specified schema
388 * @param tag Tag to be deleted
389 * @param schemaName Name of the schema to be deleted from
390 * @return int, 0 if nothing was deleted or found, 1 if it was deleted
391 * @throws SQLException
392 */
393 public int deleteSchemaTag(MetaDataTag tag, String schemaName)throws SQLException{
394 Vector columnNames = new Vector();
395 Vector columnValues = new Vector();
396 columnNames.add("SchemaName");
397 columnNames.add("MetaDataTag");
398 columnValues.add(schemaName);
399 columnValues.add(tag.getName());
400 return midAssetDB.delete("SchemasTagsTable", columnNames, columnValues);
401 }
402
403 /**
404 * A high level method for getting a Schema from the database. The Schema is
405 * returned with all of the MetaDataTags associated with it in the database.
406 * @param schemaName the name of the Schema to be returned
407 * @return Schema, null if schema not found
408 * @throws SQLException
409 * @throws SchemaException
410 */
411 public Schema getSchema(String schemaName) throws SQLException,
412 SchemaException {
413 log.debug("Entering AssetDB.getSchema(String)");
414 Schema s = new Schema();
415 MetaDataTag tag;
416
417 s.setName(schemaName);
418 Vector resultNames = new Vector();
419 Vector tableNames = new Vector();
420 Vector columnNames = new Vector();
421 Vector columnValues = new Vector();
422
423 //get schema with schemaName from SchemaTable
424 resultNames.add("SchemaTable.*");
425 tableNames.add("SchemaTable");
426 columnNames.add("SchemaTable.SchemaName");
427 columnValues.add(schemaName);
428
429 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
430 columnNames,
431 columnValues);
432 // if the schema is not found, return null
433 if(resultTable.isEmpty())
434 return null;
435
436 //set values of schema
437 int index = 0;
438 Vector metadata = resultTable.getMetaData();
439 index = metadata.indexOf("OwnerName");
440 s.setOwnerName(resultTable.getResultsElement(0, index));
441 index = metadata.indexOf("GroupName");
442 s.setGroupName(resultTable.getResultsElement(0, index));
443 index = metadata.indexOf("PermissionID");
444 s.setPermissionID(resultTable.getResultsElement(0, index));
445 //get schema tags from SchemaTagsTable
446 resultNames.clear();
447 tableNames.clear();
448 columnNames.clear();
449 columnValues.clear();
450 resultNames.add("SchemasTagsTable.MetaDataTag");
451 tableNames.add("SchemasTagsTable");
452 columnNames.add("SchemasTagsTable.SchemaName");
453 columnValues.add(schemaName);
454
455 resultTable = midAssetDB.andQuery(resultNames, tableNames,
456 columnNames,
457 columnValues);
458
459 // results is now a column Vector containing many row Vectors each
460 // containing one String, the name of a MetaDataTag associated with the
461 // given Schema
462 for (int i = 0; i < resultTable.getRowCount(); i++) {
463 String tagName = (String) ( (Vector) resultTable.getRow(i)).firstElement();
464 tag = new MetaDataTag();
465 tag.setName(tagName);
466 s.addTag(tag);
467 }
468 log.debug("Exiting AssetDB.getSchema(String)");
469 return s;
470 }
471
472 /**
473 * Returns a list of the repositories in the database
474 * @return vector with list of repositories
475 * @throws SQLException
476 */
477 public Vector getRepositoryList() throws SQLException {
478 log.debug("Entering AssetDB.getRepositoryList()");
479 Vector resultNames = new Vector();
480 Vector tableNames = new Vector();
481 Vector columnNames = new Vector();
482 Vector columnValues = new Vector();
483 resultNames.add("RepositoryTable.RepositoryName");
484 tableNames.add("RepositoryTable");
485 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
486 columnNames,
487 columnValues);
488 Vector repList = new Vector();
489 for (int i = 0; i < resultTable.getRowCount(); i++) {
490 repList.add(resultTable.getResultsElement(i, 0));
491 }
492 log.debug("Exiting AssetDB.getRepositoryList()");
493 return repList;
494
495 }
496
497 /**
498 * A high level method that returns the schema associated with the collection
499 * of the given name.
500 * @param collectionName the name of the collection with the desired Schema
501 * @return Schema the schema associated with the collection of the given name
502 * @throws SQLException
503 * @throws SchemaException
504 */
505
506 public Schema getSchemaOfCollection(String collectionName) throws
507 SchemaException, SQLException {
508 log.debug("Entering AssetDB.getSchemaOfCollection(String)");
509
510 Vector resultNames = new Vector();
511 Vector tableNames = new Vector();
512 Vector columnNames = new Vector();
513 Vector columnValues = new Vector();
514 resultNames.add("CollectionTable.SchemaName");
515 tableNames.add("CollectionTable");
516 columnNames.add("CollectionTable.CollectionName");
517 columnValues.add(collectionName);
518 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
519 columnNames,
520 columnValues);
521 String schemaName = ( (String) ( (Vector) resultTable.getRow(0)).
522 firstElement());
523 log.debug("Exiting AssetDB.getSchemaOfCollection(String)");
524 return this.getSchema(schemaName);
525 } // end of method getSchemaOfCollection
526
527 /**
528 * This method returns a vector of strings containing names of all of the
529 * collections associated with the schema of schemaName.
530 * @param schemaName name of schema associated with collections.
531 * @return Vector vector of strings with collection names
532 * @throws SQLException
533 */
534 public Vector getCollectionsWithSchema(String schemaName) throws SQLException {
535 log.debug("Entering AssetDB.getCollectionWithSchema(String)");
536 Table resultTable = new Table();
537 Vector resultNames = new Vector();
538 Vector tableNames = new Vector();
539 Vector columnNames = new Vector();
540 Vector columnValues = new Vector();
541 resultNames.add("CollectionTable.CollectionName");
542 tableNames.add("CollectionTable");
543 columnNames.add("CollectionTable.SchemaName");
544 columnValues.add(schemaName);
545 resultTable = midAssetDB.andQuery(resultNames, tableNames, columnNames,
546 columnValues);
547 log.debug("Exiting AssetDB.getCollectionWithSchema(String)");
548 return resultTable.getCol("CollectionName");
549 }
550
551 /**
552 * A high level method for returning an asset by specifying its FileName
553 * attribute, which is the primary key for Assets in the database.
554 * @param fileName the file name associated with the Asset
555 * @return Asset, the desired Asset with ALL of its metadata, null if no
556 * Asset with that fileName was found
557 * @throws SQLException
558 */
559 public Asset getAsset(String fileName) throws SQLException {
560 log.debug("Entering AssetDB.getAsset(String)");
561 Asset a = new Asset();
562 AssetDescriptorCollection adc = new AssetDescriptorCollection();
563 AssetDescriptor ad = null;
564 String tagName;
565 String tagValue;
566
567 Vector resultNames = new Vector();
568 Vector tableNames = new Vector();
569 Vector columnNames = new Vector();
570 Vector columnValues = new Vector();
571 resultNames.add("*");
572 tableNames.add("AssetTable");
573 columnNames.add("AssetTable.FileName");
574 columnValues.add(fileName);
575 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
576 columnNames,
577 columnValues);
578
579 if (resultTable.getRowCount() == 0) {
580 return null;
581 }
582
583 for (int i = 0; i < resultTable.getSizeMetaData(); i++) {
584 ad = new AssetDescriptor();
585 tagName = resultTable.getMetaDataElement(i);
586 tagValue = resultTable.getResultsElement(0, i);
587 ad.setTag(tagName);
588 ad.setValue(tagValue);
589 adc.addAssetDescriptor(ad);
590 }
591
592 Vector extraResultNames = new Vector();
593 Vector extraTableNames = new Vector();
594 Vector extraColumnNames = new Vector();
595 Vector extraColumnValues = new Vector();
596 extraResultNames.add("*");
597 extraTableNames.add("AssetMetaData");
598 extraColumnNames.add("AssetMetaData.FileName");
599 extraColumnValues.add(fileName);
600 Table metaDataTable = midAssetDB.andQuery(extraResultNames, extraTableNames,
601 extraColumnNames,
602 extraColumnValues);
603
604 for (int k = 0; k < metaDataTable.getRowCount(); k++) {
605 ad = new AssetDescriptor();
606 ad.setTag(metaDataTable.getResultsElement(k, 1));
607 ad.setValue(metaDataTable.getResultsElement(k, 2));
608 adc.addAssetDescriptor(ad);
609 }
610
611 a.setAssetDescriptors(adc);
612 log.debug("Exiting AssetDB.getAsset(String)");
613 return a;
614 }
615
616 /**
617 * A high level method for getting an Asset containing only the metadata required
618 * by a particular collection that the Asset belongs to.
619 * @param fileName the file name assocated with the desired asset
620 * @param collectionName the collection whose Schema the Asset is to be masked by
621 * @return Asset, the desired Asset, with only those descriptors required by
622 * the Schema associated with the Collection of the given name
623 * @throws SQLException
624 * @throws SchemaException
625 */
626 public Asset getAssetFromCollection(String fileName, String collectionName) throws
627 SQLException, SchemaException {
628 log.debug("Entering AssetDB.getAssetFromCollection(String, String)");
629 Asset a = new Asset();
630 AssetDescriptorCollection adc = new AssetDescriptorCollection();
631 AssetDescriptor ad = null;
632 String tagName;
633 String tagValue;
634 String resultSetTagName = null;
635
636 Vector resultNames = new Vector();
637 Vector tableNames = new Vector();
638 Vector columnNames = new Vector();
639 Vector columnValues = new Vector();
640 resultNames.add("*");
641 tableNames.add("AssetTable");
642 columnNames.add("AssetTable.FileName");
643 columnValues.add(fileName);
644
645 Table results = midAssetDB.andQuery(resultNames, tableNames,
646 columnNames, columnValues);
647
648 for (int j = 0; j < results.getSizeMetaData(); j++) {
649 ad = new AssetDescriptor();
650 ad.setTag(results.getMetaDataElement(j));
651 ad.setValue(results.getResultsElement(0, j));
652 adc.addAssetDescriptor(ad);
653 }
654
655 Schema s = getSchemaOfCollection(collectionName);
656 Vector schemaResultNames = new Vector();
657 Vector schemaTableNames = new Vector();
658 Vector schemaColumnNames = new Vector();
659 Vector schemaColumnValues = new Vector();
660 schemaResultNames.add("*");
661 schemaTableNames.add("AssetMetaData");
662 schemaColumnNames.add("AssetMetaData.FileName");
663 schemaColumnValues.add(fileName);
664
665 Table resultTable = midAssetDB.andQuery(schemaResultNames, schemaTableNames,
666 schemaColumnNames,
667 schemaColumnValues);
668
669 for (int i = 0; i < resultTable.getRowCount(); i++) {
670 resultSetTagName = resultTable.getResultsElement(i, 1);
671 for (Iterator tagsIterator = s.tagsIterator(); tagsIterator.hasNext(); ) {
672 ad = new AssetDescriptor();
673 tagName = (String) ( (MetaDataTag) tagsIterator.next()).getName();
674 if (resultSetTagName.equals(tagName)) {
675 tagValue = resultTable.getResultsElement(i, 2);
676 ad.setTag(tagName);
677 ad.setValue(tagValue);
678 adc.addAssetDescriptor(ad);
679 }
680 }
681 }
682 a.setAssetDescriptors(adc);
683 log.debug("Exiting AssetDB.getAssetFromCollection(String, String)");
684 return a;
685 }
686
687 /**Obtain all the metadata tags used in the schematagstable
688 * @return Vector of Strings: all unique metadata tags */
689 public Vector getAllMetadataTags() {
690 log.debug("Entering AssetDB.getAllMetadataTags()");
691 Vector v = null;
692 try {
693 v = new Vector();
694
695 Vector resultNames = new Vector();
696 Vector tableNames = new Vector();
697 resultNames.add("DISTINCT SchemasTagsTable.MetaDataTag");
698 tableNames.add("SchemasTagsTable");
699 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
700 new Vector(),
701 new Vector());
702 for (int i = 0; i < resultTable.getRowCount(); i++) {
703 v.add(resultTable.getResultsElement(i, 0));
704 }
705 }
706 catch (SQLException ex) {
707 log.warn("Unexpected SQLException caught in AssetDB.getAllMetadataTags() " + ex.getMessage());
708 }
709 log.debug("Exiting AssetDB.getAllMetadataTags()");
710 return v;
711 }
712
713 /**
714 * Returns a vector of Strings containing the names of all of the Collections
715 * stored in the database.
716 * @return Vector of Strings
717 * @throws SQLException
718 */
719 public Vector getCollectionNames() throws SQLException {
720 log.debug("Entering AssetDB.getCollectionNames()");
721 Vector v = new Vector();
722
723 Vector resultNames = new Vector();
724 Vector tableNames = new Vector();
725 resultNames.add("CollectionTable.CollectionName");
726 tableNames.add("CollectionTable");
727
728 Table resultTable = midAssetDB.andQuery(resultNames, tableNames, new Vector(),
729 new Vector());
730
731 for (int i = 0; i < resultTable.getRowCount(); i++) {
732 v.add(resultTable.getResultsElement(i, 0));
733 }
734 log.debug("Exiting AssetDB.getCollectionNames()");
735 return v;
736 }
737
738 /**
739 * This method returns a vector of strings containing all of the schemas in the
740 * SchemaTagsTable.
741 * @return Vector, vector of all schema names in the SchemaTable.
742 * @throws SQLException
743 */
744 public Vector getSchemaNames() throws SQLException {
745 log.debug("Entering AssetDB.getSchemaNames()");
746 Vector v = new Vector();
747
748 Vector resultNames = new Vector();
749 Vector tableNames = new Vector();
750 resultNames.add("SchemaTable.SchemaName");
751 tableNames.add("SchemaTable");
752
753 Table resultTable = midAssetDB.andQuery(resultNames, tableNames, new Vector(),
754 new Vector());
755
756 for (int i = 0; i < resultTable.getRowCount(); i++) {
757 v.add(resultTable.getResultsElement(i, 0));
758 }
759 log.debug("Exiting AssetDB.getSchemaNames()");
760 return v;
761 }
762
763 /**
764 * This method returns a vector of strings containing all of the names of the
765 * assets in the specified slideshow
766 * @return Vector, vector of strings containing all of the names of the
767 * assets in the specified slideshow
768 * @throws SQLException
769 */
770 public Vector getSlideShowAssetFileNames(String slideShowName) throws SQLException {
771 log.debug("Entering AssetDB.getSlideShowAssetFileNames()");
772 Vector v = new Vector();
773
774 Vector resultNames = new Vector();
775 Vector tableNames = new Vector();
776 Vector columnNames = new Vector();
777 Vector columnValues = new Vector();
778 resultNames.add("slideshowassetstable.AssetFileName");
779 tableNames.add("slideshowassetstable");
780 columnNames.add("slideshowassetstable.Name");
781 columnValues.add(slideShowName);
782
783 Table resultTable = midAssetDB.andQuery(resultNames, tableNames, columnNames,
784 columnValues);
785
786 for (int i = 0; i < resultTable.getRowCount(); i++) {
787 v.add(resultTable.getResultsElement(i, 0));
788 }
789 log.debug("Exiting AssetDB.getSchemaNames()");
790 return v;
791 }
792
793
794 /**
795 * A high level method that indicates whether a given Asset exists in the database
796 * or not. Note that only the primary key (the asset's FileName) is used to
797 * perform the search.
798 * @param a the asset to be searched for
799 * @return boolean, true if the asset is in the database, false otherwise
800 * @throws SQLException
801 */
802 public boolean isAssetInDB(Asset a) throws SQLException {
803 log.debug("Entering AssetDB.isAssetInDB(Asset)");
804 String aName = a.getAssetDescriptors().getValue("FileName");
805 Vector v = new Vector();
806
807 Vector resultNames = new Vector();
808 Vector tableNames = new Vector();
809 Vector columnNames = new Vector();
810 Vector columnValues = new Vector();
811 resultNames.add("AssetTable.FileName");
812 tableNames.add("AssetTable");
813 columnNames.add("AssetTable.FileName");
814 columnValues.add(aName);
815
816 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
817 columnNames,
818 columnValues);
819 log.debug("Exiting AssetDB.isAssetInDB(Asset)");
820 return resultTable.getRowCount() > 0;
821 }
822
823 /**
824 * Returns the total number of assets in the Database
825 * @return int, Size of Database
826 */
827 public int sizeDB() {
828 log.debug("Entering/Exiting AssetDB.sizeDB()");
829 try {
830 return this.getAllAssets().size();
831 }
832 catch (SQLException ex) {
833 log.warn("Unexpected SQLException caught in sizeDB() : " + ex.getMessage());
834 return -1;
835 }
836 }
837
838 /**
839 * A high level method for getting a user by specifying the userName and
840 * password. Reflection is used to set the information from the results of the
841 * query as user properties.
842 * @param userName the user's userName
843 * @param password the user's password
844 * @return User, the user with userName and password, and all of its associated
845 * properties. A null user is returned if no user with userName and password
846 * was found.
847 * @throws SQLException
848 */
849 public User getUser(String userName, String password) throws SQLException {
850 // calculate the hash of the password for comparison with the hash in the database
851 log.debug("Entering AssetDB.getUser(String, String)");
852 password = DBUtils.instance().hash(password);
853
854 Vector resultNames = new Vector();
855 Vector tableNames = new Vector();
856 Vector columnNames = new Vector();
857 Vector columnValues = new Vector();
858
859 resultNames.add("*");
860 tableNames.add("UsersTable");
861 columnNames.add("UsersTable.UserName");
862 columnNames.add("UsersTable.Password");
863 columnValues.add(userName);
864 columnValues.add(password);
865
866 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
867 columnNames,
868 columnValues);
869
870 User user = null;
871 if (resultTable.getRowCount() == 0) {
872 return user;
873 }
874 user = new User();
875 String columnName;
876
877 for (int j = 0; j < resultTable.getSizeMetaData(); j++) {
878 columnName = resultTable.getMetaDataElement(j);
879 String firstCharacter = columnName.substring(0, 1);
880 firstCharacter = firstCharacter.toLowerCase();
881 columnName = firstCharacter + columnName.substring(1);
882 Field field;
883 Class userClass = user.getClass();
884 try {
885 field = userClass.getField(columnName);
886 field.set(user, resultTable.getResultsElement(0, j));
887 }
888 catch (Exception ex) {
889 log.warn("Unexpected " + ex.getClass().getName() +
890 " caught in getUser.");
891 }
892 }
893 log.debug("Exiting AssetDB.getUser(String, String)");
894 return user;
895 }
896
897 /**
898 * A high level method for getting a user by specifying the userName. Reflection
899 * is used to set the information from the results of the
900 * query as user properties.
901 * @param userName the user's userName
902 * @return User, the user with userName, and all of its associated
903 * properties. A null user is returned if no user with userName was found.
904 * @throws SQLException
905 */
906 public User getUserFromName(String userName) throws SQLException {
907 log.debug("Entering AssetDB.getUserFromName(String)");
908 Vector resultNames = new Vector();
909 Vector tableNames = new Vector();
910 Vector columnNames = new Vector();
911 Vector columnValues = new Vector();
912
913 resultNames.add("*");
914 tableNames.add("UsersTable");
915 columnNames.add("UsersTable.UserName");
916 columnValues.add(userName);
917
918 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
919 columnNames,
920 columnValues);
921
922 User user = null;
923 if (resultTable.getRowCount() == 0) {
924 return user;
925 }
926 user = new User();
927 String columnName;
928
929 for (int j = 0; j < resultTable.getSizeMetaData(); j++) {
930 columnName = resultTable.getMetaDataElement(j);
931 String firstCharacter = columnName.substring(0, 1);
932 firstCharacter = firstCharacter.toLowerCase();
933 columnName = firstCharacter + columnName.substring(1);
934 Field field;
935 Class userClass = user.getClass();
936 try {
937 field = userClass.getField(columnName);
938 field.set(user, resultTable.getResultsElement(0, j));
939 }
940 catch (Exception ex) {
941 log.warn("Unexpected " + ex.getClass().getName() +
942 " caught in getUser.");
943 }
944 }
945 log.debug("Exiting AssetDB.getUserFromName(String)");
946 return user;
947 }
948
949 /**
950 * @return Table containing all the columns in the UsersTable except for the password
951 * column
952 */
953
954 public Table getUserTable() {
955 log.debug("Entering AssetDB.getUserInfo()");
956 Table resultsTable = new Table();
957 try {
958 Vector resultNames = new Vector();
959 Vector tableNames = new Vector();
960 Vector columnNames = new Vector();
961 Vector columnValues = new Vector();
962
963 resultNames.add("UsersTable.UserName");
964 resultNames.add("UsersTable.LastName");
965 resultNames.add("UsersTable.FirstName");
966 resultNames.add("UsersTable.MiddleInitial");
967 resultNames.add("UsersTable.Organization");
968 tableNames.add("UsersTable");
969
970 resultsTable = midAssetDB.andQuery(resultNames, tableNames,
971 columnNames,
972 columnValues);
973 }
974 catch (SQLException ex) {
975 log.warn("Unexpected SQLException in AssetDB.getUserInfo()");
976 }
977 log.debug("Exiting AssetDB.getUserInfo()");
978 return resultsTable;
979 }
980
981 /**
982 * @return Table containing the entire CollectionTable
983 */
984
985 public Table getCollectionTable() {
986 log.debug("Entering AssetDB.getCollectionTable()");
987 Table resultsTable = new Table();
988 try {
989 Vector resultNames = new Vector();
990 Vector tableNames = new Vector();
991 Vector columnNames = new Vector();
992 Vector columnValues = new Vector();
993
994 /*Select * from CollectionTable! */
995 resultNames.add("*");
996 tableNames.add("CollectionTable");
997
998 resultsTable = midAssetDB.andQuery(resultNames, tableNames,
999 columnNames,
1000 columnValues);
1001 }
1002 catch (SQLException ex) {
1003 log.warn("Unexpected SQLException in AssetDB.getCollectionTable()");
1004 }
1005 log.debug("Exiting AssetDB.getCollectionTable()");
1006 return resultsTable;
1007 }
1008
1009 /**
1010 * A high level method used to add a User to the database.
1011 * Reflection is used to get the properties of the user that will be
1012 * added as column names and the value of each property to be added as
1013 * column values.
1014 * @param user the user to be added to the database
1015 * @return int, the number of rows affected by the operation, 1 or 0
1016 * @throws SQLException
1017 */
1018 public int addUser(User user) throws SQLException {
1019 log.debug("Entering AssetDB.addUser(User)");
1020 // check to make sure the user is not already in the database
1021 if (this.isUserInDB(user)) {
1022 return 0;
1023 }
1024 // hash the password field for storage in the database
1025 String password = user.getPassword();
1026 String hash = DBUtils.instance().hash(password);
1027 user.setPassword(hash);
1028
1029 Vector columnNames = new Vector();
1030 Vector columnValues = new Vector();
1031 Field field;
1032 String fieldName;
1033 Class userClass = user.getClass();
1034 Field[] fields = userClass.getFields();
1035 for (int i = 0; i < fields.length; i++) {
1036 field = fields[i];
1037 fieldName = field.getName();
1038 String firstChar = fieldName.substring(0, 1);
1039 firstChar = firstChar.toUpperCase();
1040 fieldName = firstChar + fieldName.substring(1);
1041 columnNames.add(fieldName);
1042 try {
1043 columnValues.add(field.get(user));
1044 }
1045 catch (IllegalAccessException ex) {
1046 log.warn("Caught unexpected IllegalAccessException in addUser");
1047 }
1048 catch (IllegalArgumentException ex) {
1049 log.warn("Caught unexpected IllegalArgumentException in addUser");
1050 }
1051 }
1052 log.debug("Exiting AssetDB.addUser(User)");
1053 return midAssetDB.insert("UsersTable", columnNames, columnValues);
1054 }
1055
1056 /**
1057 * A high level method used to add a Group to the database.
1058 * Reflection is used to get the properties of the user group that will be
1059 * added as column names and the value of each property to be added as
1060 * column values.
1061 * @param group the Group to be added to the database
1062 * @return int, the number of rows affected by the operation, 1 or 0
1063 * @throws SQLException
1064 */
1065 public int addGroup(Group group) throws SQLException {
1066 log.debug("Entering AssetDB.addGroup(group)");
1067 // check to make sure the group is not already in the database
1068 if (this.isGroupInDB(group)) {
1069 return 0;
1070 }
1071 Vector columnNames = new Vector();
1072 Vector columnValues = new Vector();
1073 Field field;
1074 String fieldName;
1075 Class groupClass = group.getClass();
1076 Field[] fields = groupClass.getFields();
1077 for (int i = 0; i < fields.length; i++) {
1078 field = fields[i];
1079 fieldName = field.getName();
1080 String firstChar = fieldName.substring(0, 1);
1081 firstChar = firstChar.toUpperCase();
1082 fieldName = firstChar + fieldName.substring(1);
1083 columnNames.add(fieldName);
1084 try {
1085 if (field.get(group)instanceof String) {
1086 columnValues.add(field.get(group));
1087 }
1088 else {
1089 Boolean bool = (Boolean) field.get(group);
1090 columnValues.add(bool.toString());
1091 }
1092 }
1093 catch (IllegalAccessException ex) {
1094 log.warn("Caught unexpected IllegalAccessException in addGroup");
1095 }
1096 catch (IllegalArgumentException ex) {
1097 log.warn("Caught unexpected IllegalAccessException in addGroup");
1098 }
1099 }
1100 log.debug("Exiting AssetDB.addGroup(Group)");
1101 return midAssetDB.insert("GroupsTable", columnNames, columnValues);
1102 }
1103
1104 /**
1105 * A high level method to add a User to a Group. Note that this does not
1106 * add a new User to the UsersTable. For that you want addUser.
1107 * @param userName the user to be added to the given group
1108 * @param groupName the name of the group that the User is to be added to
1109 * @return int, the number of rows affected by the operation, 1 or 0
1110 * @throws SQLException
1111 */
1112 public int addUserToGroup(String userName, String groupName) throws SQLException {
1113 log.debug("Entering AssetDB.addUserToGroup(User, String)");
1114 Vector columnNames = new Vector();
1115 Vector columnValues = new Vector();
1116 columnNames.add("UserName");
1117 columnNames.add("GroupName");
1118 columnValues.add(userName);
1119 columnValues.add(groupName);
1120
1121 log.debug("Exiting AssetDB.addUserToGroup(User, String)");
1122 return midAssetDB.insert("UsersGroupsTable", columnNames, columnValues);
1123 }
1124
1125
1126 /**
1127 * Deletes all users in the specified group from the users groups table
1128 * @param groupName the name of the Group to be deleted from
1129 * @return int - number of rows affected
1130 * @throws SQLException
1131 */
1132 public int deleteMultipleUsersFromGroup(String groupName)throws SQLException{
1133 log.debug("Entering AssetDB.deleteMultipleUsersFromGroup");
1134 Vector columnNames = new Vector();
1135 Vector columnValues = new Vector();
1136 columnNames.add("UsersGroupsTable.GroupName");
1137 columnValues.add(groupName);
1138 log.debug("Exiting AssetDB.deleteMultipleUsersFromGroup");
1139 return midAssetDB.delete("UsersGroupsTable",columnNames,columnValues);
1140 }
1141
1142
1143
1144
1145 public Vector getUsersInGroup(String groupName) throws SQLException {
1146 Vector v = new Vector();
1147 log.debug("Entering AssetDB.getUsersInGroup");
1148 Vector resultNames = new Vector();
1149 Vector tableNames = new Vector();
1150 Vector columnNames = new Vector();
1151 Vector columnValues = new Vector();
1152 resultNames.add("UsersGroupsTable.UserName");
1153 tableNames.add("UsersGroupsTable");
1154 columnNames.add("UsersGroupsTable.GroupName");
1155 columnValues.add(groupName);
1156
1157 Table resultTable = midAssetDB.andQuery(resultNames, tableNames, columnNames, columnValues);
1158
1159 for(int i = 0; i < resultTable.getRowCount(); i++){
1160 v.add(resultTable.getResultsElement(i,0));
1161 }
1162 log.debug("Exiting AssetDB.getUsersInGroup");
1163 return v;
1164
1165 }
1166
1167 /**
1168 * This method returns a vector of strings containing all of the groups in the
1169 * GroupsTable.
1170 * @return Vector, vector of all group names in the GroupsTable.
1171 * @throws SQLException
1172 */
1173 public Vector getGroupNames() throws SQLException {
1174 log.debug("Entering AssetDB.getGroupNames()");
1175 Vector v = new Vector();
1176
1177 Vector resultNames = new Vector();
1178 Vector tableNames = new Vector();
1179 resultNames.add("DISTINCT GroupsTable.GroupName");
1180 tableNames.add("GroupsTable");
1181
1182 Table resultTable = midAssetDB.andQuery(resultNames, tableNames, new Vector(),
1183 new Vector());
1184
1185 for (int i = 0; i < resultTable.getRowCount(); i++) {
1186 v.add(resultTable.getResultsElement(i, 0));
1187 }
1188 log.debug("Exiting AssetDB.getGroupNames()");
1189 return v;
1190 }
1191
1192 /**
1193 * This method returns a vector of strings containing all of the slide shows in the
1194 * SlideShowTable for the specified User.
1195 * @param user the user whose slideshows are to be retrieved
1196 * @return Vector, vector of all the slide shows in the
1197 * SlideShowTable for the specified User.
1198 * @throws SQLException
1199 */
1200 public Vector getSlideShowNames(User user) throws SQLException {
1201 log.debug("Entering AssetDB.getSlideShowNames()");
1202 Vector v = new Vector();
1203
1204 Vector resultNames = new Vector();
1205 Vector tableNames = new Vector();
1206 Vector columnNames = new Vector();
1207 Vector columnValues = new Vector();
1208 resultNames.add("DISTINCT SlideShowTable.Name");
1209 tableNames.add("SlideShowTable");
1210 columnNames.add("Owner");
1211 columnValues.add(user.getUserName());
1212
1213 Table resultTable = midAssetDB.andQuery(resultNames, tableNames, columnNames,
1214 columnValues);
1215
1216 for (int i = 0; i < resultTable.getRowCount(); i++) {
1217 v.add(resultTable.getResultsElement(i, 0));
1218 }
1219 log.debug("Exiting AssetDB.getSlideShowNames()");
1220 return v;
1221 }
1222
1223
1224 /**
1225 * A high level method used to delete a User from the database. Note that
1226 * this also deletes the user from any references to it in the
1227 * UsersGroupTable in the database.
1228 * @param user user to be deleted from the database.
1229 * @return int, the number of rows affected by the operation, 1 or 0.
1230 * @throws SQLException
1231 */
1232 public int deleteUser(User user) throws SQLException {
1233 log.debug("Entering AssetDB.deleteUser(User)");
1234 String userName = user.getUserName();
1235 Vector columnNames = new Vector();
1236 Vector columnValues = new Vector();
1237 columnNames.add("UserName");
1238 columnValues.add(userName);
1239 midAssetDB.delete("UsersGroupsTable", columnNames, columnValues);
1240 log.debug("Exiting AssetDB.deleteUser(User)");
1241 return midAssetDB.delete("UsersTable", columnNames, columnValues);
1242 }
1243
1244 /**
1245 * A high level method used to delete a Group from the database.
1246 * @param groupName Name of the group to be deleted from the database
1247 * @return int, the number of rows affected by the operation, 1 or 0.
1248 * @throws SQLException
1249 */
1250 <