Source code: org/acs/damsel/srvr/db/MidAssetDB.java
1 package org.acs.damsel.srvr.db;
2
3 import java.sql.*;
4 import java.util.*;
5
6 import org.acs.damsel.srvr.*;
7 import org.acs.damsel.srvr.search.*;
8 import org.apache.log4j.*;
9
10 /**
11 * <p>Title: Mid-level SQL query and statement generation</p>
12 * <p>Description:
13 * This singleton class contains the mid level methods for the AssetDB class.
14 * Methods include: the basic constructor, query, insert, and delete.
15 * </p>
16 * @version 1.0
17 */
18 public class MidAssetDB {
19 private LowAssetDB lowAssetDB = null;
20 private static MidAssetDB instance = null;
21 private static Logger log = Logger.getLogger(MidAssetDB.class);
22
23 private MidAssetDB() throws SQLException {
24 Config config = Config.instance();
25 BasicConfigurator.resetConfiguration();
26 PropertyConfigurator.configure(config.getLogPropertiesFileName());
27 lowAssetDB = new LowAssetDB();
28 }
29
30 /**
31 * The instance() method is used instead of a constructor to get a handle on
32 * the MidAssetDB object. If the MidAssetDB object has not yet been initialized, then it is initialized
33 * using the default constructor.
34 * @return a handle on the MidAssetDB object
35 * @throws SQLException
36 */
37 public static MidAssetDB instance() throws SQLException {
38 if (instance == null)
39 instance = new MidAssetDB();
40 return instance;
41 }
42
43 /*
44 * A mid-level method for constructing SQL queries.
45 * In general, the parameters are turned into a SQL query of the form:
46 * SELECT resultNames(1), resultNames(2), ..., resultNames(n)
47 * FROM tablesNames(1), tableNames(2), ..., tableNames(n)
48 * WHERE columnNames(1) LIKE columnValues(1)
49 * {AND, OR} columnNames(2) LIKE columnValues(2)
50 * ...
51 * {AND, OR} columnNames(n) LIKE columnValues(n)
52 *
53 * Note that although n's are used in the general form, the Vectors do not
54 * all have to be of the same length, except for columnNames and columnValues.
55 * @param Vector resultNames, the columns to be returned in the result table
56 * (must include the tableName, so AssetTable.FileName, not FileName)
57 * @param Vector tableNames, the names of the tables to be selected from
58 * @param Vector columnNames, the names of the columns to be included in the WHERE clause
59 * (must include the tableName, so UsersTable.UserName, not UserName)
60 * @param Vector columnValues, the values to be checked against in the WHERE clause
61 * (must be in the same order as columnNames in order for them to match up)
62 * @param boolean isOrQuery, if true then the WHERE statements are connected
63 * with ORs. if false, they are connected with ANDs.
64 * @return Table
65 * @throws SQLException
66 */
67 private Table query(Vector resultNames, Vector tableNames, Vector columnNames,
68 Vector columnValues, boolean isOrQuery) throws
69 SQLException {
70
71 // encode quotes and double-quotes to be SQL-safe
72 DBUtils.encode(columnValues);
73
74 String str;
75 String valueStr;
76
77 // add the desired result columns
78 String query = "SELECT ";
79 for (Iterator i = resultNames.iterator(); i.hasNext(); ) {
80 str = (String) i.next();
81 query += str + ", ";
82 }
83 // remove the extra comma
84 query = query.substring(0, query.length() - 2);
85
86 // add the necessary table names
87 query += " FROM ";
88 for (Iterator i = tableNames.iterator(); i.hasNext(); ) {
89 str = (String) i.next();
90 query += str + ",";
91 }
92 // remove the extra comma
93 query = query.substring(0, query.length() - 1);
94
95 // if any were specified, add the WHERE clauses
96 if (columnNames.size() == columnValues.size() && columnNames.size() > 0) {
97 query += " WHERE ";
98 int j = 0;
99 for (Iterator i = columnNames.iterator(); i.hasNext(); j++) {
100 str = (String) i.next();
101 valueStr = (String) columnValues.get(j);
102 if (isOrQuery) {
103 if(valueStr == null)
104 query += str + " LIKE '" + valueStr + "' OR ";
105 else {
106 if (valueStr.indexOf("Table.") != -1)
107 query += str + " LIKE " + valueStr + " OR ";
108 else
109 query += str + " LIKE '" + valueStr + "' OR ";
110 }
111 }
112 else {
113 if(valueStr == null)
114 query += str + " LIKE '" + valueStr + "' AND ";
115 else {
116 if (valueStr.indexOf("Table.") != -1)
117 query += str + " LIKE " + valueStr + " AND ";
118 else
119 query += str + " LIKE '" + valueStr + "' AND ";
120 }
121 }
122 }
123 // remove the extra AND and the space afterward
124 query = query.substring(0, query.length() - 4);
125 }
126 // cap it all off with a semicolon
127 query += ";";
128 return lowAssetDB.executeQuery(query);
129 }
130
131 /**
132 * A wrapper around the generic query method that performs a general query
133 * with the WHERE statements connected with ANDs.
134 * See the documentation for query for more information.
135 * @return Table
136 * @param resultNames Vector the columns to be returned in the result table
137 * (must include the tableName, so AssetTable.FileName, not FileName)
138 * @param tableNames Vector the names of the tables to be selected from
139 * @param columnNames Vector the names of the columns to be included in the WHERE clause
140 * (must include the tableName, so UsersTable.UserName, not UserName)
141 * @param columnValues Vector the values to be checked against in the WHERE clause
142 * (must be in the same order as columnNames in order for them to match up)
143 * @throws SQLException
144 */
145 public Table andQuery(Vector resultNames, Vector tableNames,
146 Vector columnNames,
147 Vector columnValues) throws SQLException {
148 return query(resultNames, tableNames, columnNames, columnValues, false);
149 }
150
151 /**
152 * A wrapper around the generic query method that performs a general query
153 * with the WHERE statements connected with ORs.
154 * See the documentation for query for more information.
155 * @return Table
156 * @param resultNames Vector the columns to be returned in the result table
157 * (must include the tableName, so AssetTable.FileName, not FileName)
158 * @param tableNames Vector the names of the tables to be selected from
159 * @param columnNames Vector the names of the columns to be included in the WHERE clause
160 * (must include the tableName, so UsersTable.UserName, not UserName)
161 * @param columnValues Vector the values to be checked against in the WHERE clause
162 * (must be in the same order as columnNames in order for them to match up)
163 * @throws SQLException
164
165 */
166 public Table orQuery(Vector resultNames, Vector tableNames,
167 Vector columnNames,
168 Vector columnValues) throws SQLException {
169 return query(resultNames, tableNames, columnNames, columnValues, true);
170 }
171
172 /**
173 * browseQuery
174 *
175 * This method is specifically designed to accomodate the browseByKeyField
176 * method. The reason we were not able to use the other queries is because
177 * this query needs both AND and OR statements within the WHERE clause.
178 *
179 * @param browseChar String
180 * @param tag String
181 * @param collectionSelect String
182 * @return Table
183 * @throws SQLException
184 */
185 public Table browseQuery(String browseChar, String tag,
186 String collectionSelect) throws SQLException {
187 Vector tempVec = new Vector();
188 tempVec.add(tag);
189 tempVec.add(collectionSelect);
190 DBUtils.encode(tempVec);
191
192 tag = tempVec.elementAt(0).toString();
193 collectionSelect = tempVec.elementAt(1).toString();
194
195 if (tag.equals("FileName") || tag.equals("PermissionID") ||
196 tag.equals("OwnerName") || tag.equals("GroupName")) {
197 String query = ("SELECT DISTINCT AssetTable.FileName " +
198 "FROM AssetTable,AssetsCollectionsTable " +
199 "WHERE AssetTable.FileName = AssetsCollectionsTable.FileName " +
200 "AND AssetsCollectionsTable.CollectionName = '" +
201 collectionSelect + "' " +
202 "AND UPPER(AssetTable." + tag + ") LIKE '" + browseChar.toUpperCase() +
203 "%' ");
204 return lowAssetDB.executeQuery(query);
205 }
206 else {
207 String query = ("SELECT DISTINCT AssetMetaData.FileName " +
208 "FROM AssetMetaData,AssetsCollectionsTable " +
209 "WHERE AssetMetaData.FileName = AssetsCollectionsTable.FileName " +
210 "AND AssetsCollectionsTable.CollectionName = '" +
211 collectionSelect + "' " +
212 "AND UPPER(AssetMetaData.MetaDataTag) LIKE '"+tag+"' " +
213 "AND UPPER(AssetMetaData.Value) LIKE '" + browseChar.toUpperCase() +
214 "%';");
215 return lowAssetDB.executeQuery(query);
216
217 }
218 }
219
220 /**
221 * A method for performing a simple search.
222 * @todo needs more thorough comment
223 * @param keyword String the keyword to be searched for
224 * @param tag String the tag that should be searched
225 * @param collectionSelect String the collection to be searched
226 * @return Table the result table
227 * @throws SQLException
228 */
229 public Table simpleSearchQuery(String keyword, String tag,
230 String collectionSelect) throws SQLException {
231
232 Vector v = new Vector();
233 v.add(keyword);
234 keyword = DBUtils.encode(keyword);
235 collectionSelect = DBUtils.encode(collectionSelect);
236 keyword = (String) v.get(0);
237 String query = ("SELECT DISTINCT AssetMetaData.FileName " +
238 "FROM AssetMetaData,AssetsCollectionsTable " +
239 "WHERE AssetMetaData.FileName = AssetsCollectionsTable.FileName " +
240 "AND AssetsCollectionsTable.CollectionName = '" +
241 collectionSelect + "' " +
242 "AND UPPER(AssetMetaData.MetaDataTag) LIKE '"+tag.toUpperCase()+"' " +
243 "AND UPPER(AssetMetaData.Value) LIKE '%" + keyword.toUpperCase() +
244 "%';");
245
246 Table resultTable = lowAssetDB.executeQuery(query);
247 if (resultTable.getColCount() == 0)
248 return resultTable;
249
250 query = ("SELECT DISTINCT AssetMetaData.* " +
251 "FROM AssetMetaData " +
252 "WHERE (AssetMetaData.FileName LIKE '"+resultTable.getResultsElement(0,0)+"') ");
253
254 for (int i = 1; i < resultTable.getRowCount(); i++) {
255 query = (query + "OR (AssetMetaData.FileName Like '"+resultTable.getResultsElement(i,0)+"') ");
256 }
257 query = (query + ";");
258
259 return lowAssetDB.executeQuery(query);
260
261 } // end of class simpleSearchQuery
262
263 /**
264 * Method constructs and exectutes a search query for advanced search
265 * based on the contents of the AdvancedSearchParams object passed in.
266 * @param params AdvancedSearchParams
267 * @return Table of results
268 * @throws SQLException
269 */
270 public Table advancedQuery(AdvancedSearchParams params) throws SQLException {
271
272 String allString = null;
273 String anyString = null;
274 String exactString = null;
275 String notString = null;
276 Table allResultsTable = null;
277 Table anyResultsTable = null;
278 Table exactResultsTable = null;
279 Table notResultsTable = null;
280 StringBuffer query = new StringBuffer();
281
282 /* Construct part of the SQL WHERE statement from the allSearchPairs vector
283 of the AdvancedSearchParams object */
284 Vector allVector = params.getAllSearch();
285 if (allVector != null && allVector.size() != 0) {
286 query.append("SELECT DISTINCT AssetMetaData.FileName ");
287 query.append("FROM AssetMetaData,AssetsCollectionsTable WHERE ");
288 query.append("AssetsCollectionsTable.FileName = ");
289 query.append("AssetMetaData.FileName ");
290 String collectionName = params.getCollectionName();
291 query.append("AND AssetsCollectionsTable.CollectionName = '");
292 query.append(collectionName);
293 query.append("' ");
294 query.append("AND AssetMetaData.FileName NOT IN( ");
295 query.append("SELECT FileName FROM AssetMetaData WHERE");
296
297 for (Iterator i = allVector.iterator(); i.hasNext(); ) {
298 SearchPair sp = (SearchPair) i.next();
299 StringTokenizer st = new StringTokenizer(sp.getValue());
300 while (st.hasMoreElements()) {
301 String tempWord = (String) st.nextElement();
302 String searchWord = DBUtils.encode(tempWord);
303 query.append(" (UPPER(AssetMetaData.MetaDataTag) LIKE '");
304 query.append(sp.getField().toUpperCase());
305 query.append("' ");
306 query.append(" AND NOT((UPPER(AssetMetaData.Value) ");
307 query.append("LIKE '% ");
308 query.append(searchWord.toUpperCase());
309 query.append("%')");
310 query.append(" OR (UPPER(AssetMetaData.Value) ");
311 query.append("LIKE '");
312 query.append(searchWord.toUpperCase());
313 query.append("%')))");
314 if (st.hasMoreElements())
315 query.append(" OR ");
316 } // end of while loop
317 if (i.hasNext())
318 query.append(" OR ");
319 } // end of for loop
320 query.append(");");
321
322 allResultsTable = lowAssetDB.executeQuery(query.toString());
323 } // end of if statement
324
325 /* Construct part of the SQL WHERE statement from the anySearchPairs vector
326 of the AdvancedSearchParams object */
327 Vector anyVector = params.getAnySearch();
328 query = new StringBuffer();
329 if (anyVector != null && anyVector.size() != 0) {
330 query.append("SELECT DISTINCT AssetMetaData.FileName " );
331 query.append("FROM AssetMetaData,AssetsCollectionsTable WHERE ");
332 query.append("AssetsCollectionsTable.FileName = " );
333 query.append("AssetMetaData.FileName ");
334 String collectionName = params.getCollectionName();
335 query.append(" AND AssetsCollectionsTable.CollectionName = '");
336 query.append(collectionName);
337 query.append( "' ");
338 boolean firstParam = true;
339 for (Iterator i = anyVector.iterator(); i.hasNext(); ) {
340 if (firstParam) {
341 query.append(" AND (");
342 }
343 else {
344 query.append(" OR ");
345 }
346 firstParam = false;
347 SearchPair sp = (SearchPair) i.next();
348 StringTokenizer st = new StringTokenizer(sp.getValue());
349 String tempWord = (String) st.nextElement();
350 String searchWord = DBUtils.encode(tempWord);
351 query.append(" (UPPER(AssetMetaData.MetaDataTag) LIKE '");
352 query.append(sp.getField().toUpperCase());
353 query.append("' " );
354 query.append("AND UPPER(AssetMetaData.Value) LIKE '%");
355 query.append(searchWord.toUpperCase());
356 query.append("%') ");
357
358 while (st.hasMoreElements()) {
359 tempWord = (String) st.nextElement();
360 searchWord = DBUtils.encode(tempWord);
361 query.append("OR (UPPER(AssetMetaData.MetaDataTag) LIKE '");
362 query.append(sp.getField().toUpperCase());
363 query.append("' " );
364 query.append("AND UPPER(AssetMetaData.Value) LIKE '%");
365 query.append(searchWord.toUpperCase());
366 query.append("%') ");
367 } // end of while loop
368 } // end of for loop
369 query.append(") ");
370
371 anyResultsTable = lowAssetDB.executeQuery(query.toString());
372 } // end of if statement
373
374 /* Construct part of the SQL WHERE statement from the exactSearchPairs
375 vector of the AdvancedSearchParams object */
376 Vector exactVector = params.getExactSearch();
377 query = new StringBuffer();
378 if (exactVector != null && exactVector.size() != 0) {
379 query.append("SELECT DISTINCT AssetMetaData.FileName " );
380 query.append("FROM AssetMetaData,AssetsCollectionsTable WHERE " );
381 query.append("AssetsCollectionsTable.FileName = " );
382 query.append("AssetMetaData.FileName ");
383 String collectionName = params.getCollectionName();
384 query.append("AND AssetsCollectionsTable.CollectionName = '" );
385 query.append(collectionName);
386 query.append( "' ");
387 query.append("AND AssetMetaData.FileName NOT IN(SELECT FileName FROM AssetMetaData ");
388 query.append("WHERE ");
389
390 for (Iterator i = exactVector.iterator(); i.hasNext(); ) {
391 SearchPair sp = (SearchPair) i.next();
392 String tempWord = sp.getValue();
393 String searchWord = DBUtils.encode(tempWord);
394 query.append("(UPPER(AssetMetaData.MetaDataTag) LIKE '");
395 query.append(sp.getField().toUpperCase());
396 query.append( "' " );
397 query.append("AND NOT(UPPER(AssetMetaData.Value) LIKE '%");
398 query.append(searchWord.toUpperCase());
399 query.append("%'))");
400 if (i.hasNext()) {
401 query.append(" OR ");
402 }
403 } // end of for loop
404 query.append(");");
405 exactResultsTable = lowAssetDB.executeQuery(query.toString());
406 } // end of if statement
407
408 /* Construct part of the SQL WHERE statement from the exactSearchPairs
409 vector of the AdvancedSearchParams object */
410 query = new StringBuffer();
411 Vector notVector = params.getNotSearch();
412 if (notVector != null && notVector.size() != 0) {
413 query.append("SELECT DISTINCT AssetMetaData.FileName " );
414 query.append("FROM AssetMetaData,AssetsCollectionsTable WHERE ");
415 query.append("AssetsCollectionsTable.FileName = ");
416 query.append("AssetMetaData.FileName ");
417 String collectionName = params.getCollectionName();
418 query.append("AND AssetsCollectionsTable.CollectionName = '");
419 query.append(collectionName);
420 query.append( "' ");
421 query.append("AND AssetMetaData.FileName NOT IN( ");
422 query.append("SELECT FileName FROM AssetMetaData WHERE ");
423
424 Iterator i = notVector.iterator();
425 while (i.hasNext()) {
426 SearchPair sp = (SearchPair) i.next();
427 String tempWord = sp.getValue();
428 String searchWord = DBUtils.encode(tempWord);
429 boolean firstRun = true;
430 query.append(" (UPPER(AssetMetaData.MetaDataTag) LIKE '");
431 query.append(sp.getField().toUpperCase());
432 query.append("' ");
433 query.append("AND (UPPER(AssetMetaData.Value) LIKE '%");
434 query.append(searchWord.toUpperCase());
435 query.append("%'))");
436
437 if (i.hasNext())
438 query.append(" OR ");
439 } // end of for loop
440 query.append(");");
441 notResultsTable = lowAssetDB.executeQuery(query.toString());
442 }
443
444 Vector everyFileName = new Vector();
445 boolean check = true;
446 int x;
447 //add results from anySearch
448 if (anyResultsTable != null && anyResultsTable.getRowCount() != 0) {
449 for (x = 0; x < anyResultsTable.getRowCount(); x++) {
450 check = true;
451 if (!everyFileName.contains(anyResultsTable.getResultsElement(x,0))) {
452 if (allResultsTable != null) {
453 if (!allResultsTable.isInResults("FileName",anyResultsTable.getResultsElement(x,0))) {
454 check = false;
455 } // end of if statement
456 } // end of if statement
457 if (exactResultsTable != null) {
458 if (!exactResultsTable.isInResults("FileName",anyResultsTable.getResultsElement(x,0))) {
459 check = false;
460 } // end of if statement
461 } // end of if statement
462 if (notResultsTable != null) {
463 if (!notResultsTable.isInResults("FileName",anyResultsTable.getResultsElement(x,0))) {
464 check = false;
465 } // end of if statement
466 } // end of if statement
467
468 if (check) {
469 everyFileName.addElement(anyResultsTable.getResultsElement(x, 0));
470 } // end of if statement
471
472 } // end of if statement
473 } // end of for loop
474 } // end of if statement
475
476 //add results from allSearch
477 if (allResultsTable != null && allResultsTable.getRowCount() != 0) {
478 for (x = 0; x < allResultsTable.getRowCount(); x++) {
479 check = true;
480 if (!everyFileName.contains(allResultsTable.getResultsElement(x,0))) {
481 if (anyResultsTable != null) {
482 if (!anyResultsTable.isInResults("FileName",allResultsTable.getResultsElement(x,0))) {
483 check = false;
484 } // end of if statement
485 } // end of if statement
486 if (exactResultsTable != null) {
487 if (!exactResultsTable.isInResults("FileName",allResultsTable.getResultsElement(x,0))) {
488 check = false;
489 } // end of if statement
490 } // end of if statement
491 if (notResultsTable != null) {
492 if (!notResultsTable.isInResults("FileName",allResultsTable.getResultsElement(x,0))) {
493 check = false;
494 } // end of if statement
495 } // end of if statement
496
497 if (check) {
498 everyFileName.addElement(allResultsTable.getResultsElement(x, 0));
499 } // end of if statement
500
501 } // end of if statement
502 } // end of for loop
503 } // end of if statement
504
505
506
507 //add results from exactSearch
508 if (exactResultsTable != null && exactResultsTable.getRowCount() != 0) {
509 for (x = 0; x < exactResultsTable.getRowCount(); x++) {
510 check = true;
511 if (!everyFileName.contains(exactResultsTable.getResultsElement(x,0))) {
512 if (allResultsTable != null) {
513 if (!allResultsTable.isInResults("FileName",exactResultsTable.getResultsElement(x,0))) {
514 check = false;
515 } // end of if statement
516 } // end of if statement
517 if (anyResultsTable != null) {
518 if (!anyResultsTable.isInResults("FileName",exactResultsTable.getResultsElement(x,0))) {
519 check = false;
520 } // end of if statement
521 } // end of if statement
522 if (notResultsTable != null) {
523 if (!notResultsTable.isInResults("FileName",exactResultsTable.getResultsElement(x,0))) {
524 check = false;
525 } // end of if statement
526 } // end of if statement
527
528 if (check) {
529 everyFileName.addElement(exactResultsTable.getResultsElement(x, 0));
530 } // end of if statement
531
532 } // end of if statement
533 } // end of for loop
534 } // end of if statement
535
536
537
538 //add results from notSearch
539 if (notResultsTable != null && notResultsTable.getRowCount() != 0) {
540 for (x = 0; x < notResultsTable.getRowCount(); x++) {
541 check = true;
542 if (!everyFileName.contains(notResultsTable.getResultsElement(x,0))) {
543 if (allResultsTable != null) {
544 if (!allResultsTable.isInResults("FileName",notResultsTable.getResultsElement(x,0))) {
545 check = false;
546 } // end of if statement
547 } // end of if statement
548 if (exactResultsTable != null) {
549 if (!exactResultsTable.isInResults("FileName",notResultsTable.getResultsElement(x,0))) {
550 check = false;
551 } // end of if statement
552 } // end of if statement
553 if (anyResultsTable != null) {
554 if (!anyResultsTable.isInResults("FileName",notResultsTable.getResultsElement(x,0))) {
555 check = false;
556 } // end of if statement
557 } // end of if statement
558
559 if (check) {
560 everyFileName.addElement(notResultsTable.getResultsElement(x, 0));
561 } // end of if statement
562
563 } // end of if statement
564 } // end of for loop
565 } // end of if statement
566
567 query = new StringBuffer();
568 if (everyFileName != null && everyFileName.size() > 0) {
569 query.append("SELECT DISTINCT AssetMetaData.* ");
570 query.append("FROM AssetMetaData " );
571 query.append("WHERE (AssetMetaData.FileName LIKE '" );
572 query.append(everyFileName.elementAt(0).toString());
573 query.append( "') ");
574
575 for (int i = 1; i < everyFileName.size(); i++) {
576 query.append("OR (AssetMetaData.FileName Like '" );
577 query.append(everyFileName.elementAt(i).toString());
578 query.append("') ");
579 }
580 query.append(";");
581 }
582
583
584
585 return lowAssetDB.executeQuery(query.toString());
586 }
587
588 /**
589 * A middle tier method for peforming generic inserts. Note that one insert
590 * must be performed for each row that is to be inserted. Essentially, the
591 * information is turned into a SQL statement of the form
592 * INSERT INTO tableName(columnNames(1), columnNames(2), ..., columnNames(n))
593 * VALUES(columnValues(1), columnValues(2), ..., columnValues(n));
594 *
595 * @param tableName String the table to be inserted into
596 * @param columnNames Vector strings corresponding to the database fields
597 * @param columnValues Vector strings that are the values (in order!) of the database fields
598 * @return int, the number of affected rows (should be 1 or 0)
599 * @throws SQLException
600 */
601 public int insert(String tableName, Vector columnNames, Vector columnValues) throws
602 SQLException {
603
604 // encode quotes and double-quotes to be SQL-safe
605 DBUtils.encode(columnValues);
606
607 String statement = "INSERT INTO " + tableName + "(";
608 String columnName;
609 String columnValue;
610 for (Iterator i = columnNames.iterator(); i.hasNext(); ) {
611 columnName = (String) i.next();
612 statement += columnName + ",";
613 }
614 statement = statement.substring(0, statement.length() - 1) + ") VALUES(";
615
616 for (Iterator i = columnValues.iterator(); i.hasNext(); ) {
617 columnValue = (String) i.next();
618 statement += "'" + columnValue + "',";
619 }
620
621 statement = statement.substring(0, statement.length() - 1) + ");";
622 return lowAssetDB.executeStatement(statement);
623 }
624
625 /**
626 * A middle tier method for performing generic deletes. Note that until
627 * cascading is implemented, a reference may need to be deleted from multiple
628 * tables in order to completely remove it from the database.
629 * Essentially, the information is turned into a SQL statement of the form
630 * DELETE FROM tableName
631 * WHERE tableName.columnNames(1) = columnValues(1)
632 * AND tableName.columnNames(2) = columnValues(2)
633 * ...
634 * AND tableName.columnNames(n) = columnValues(n);
635 *
636 * @param tableName String the name of the table to be deleted from
637 * @param columnNames Vector strings corresponding to the fields of the specified table in the where clause
638 * @param columnValues Vector
639 * @return int number of rows affected in the table(should be 1 or 0)
640 * @throws SQLException
641 */
642 public int delete(String tableName, Vector columnNames, Vector columnValues) throws
643 SQLException {
644
645 // encode quotes and double-quotes to be SQL-safe
646 DBUtils.encode(columnValues);
647
648 String columnName = null;
649 String columnValue = null;
650 String statement = "DELETE FROM " + tableName + " WHERE ";
651 Iterator j = columnValues.iterator();
652 for (Iterator i = columnNames.iterator(); i.hasNext(); ) {
653 columnName = (String) i.next();
654 columnValue = (String) j.next();
655 statement += columnName + "= '" + columnValue + "' AND ";
656 }
657 statement = statement.substring(0, statement.length() - 4) + ";";
658 return lowAssetDB.executeStatement(statement);
659 }
660
661 /**
662 * A generic method for constructing SQL UPDATE statements. Generally, the
663 * arguments are turned into a statement of the form:
664 * UPDATE tableName
665 * SET updateColumnName(1) = updateColumnValue(1),
666 * updateColumnName(2) = updateColumnValue(2),
667 * ...
668 * updateColumnName(n) = updateColumnValue(n)
669 * WHERE whereColumnName(1) = whereColumnValue(1)
670 * AND whereColumnName(2) = whereColumnValue(2)
671 * ...
672 * AND whereColumnName(m) = whereColumnValue(m);
673 *
674 * @param tableName String the name of the table to be updated
675 * @param updateColumnNames Vector the names of the columns to be updated
676 * @param updateColumnValues Vector the new values of the columns to be updated
677 * @param whereColumnNames Vector the names of columns that restrict the rows that are updated
678 * @param whereColumnValues Vector the values of columns that restrict the rows that are updated
679 * @return int the number of rows affected by the update
680 * @throws SQLException
681 */
682 public int update(String tableName, Vector updateColumnNames,
683 Vector updateColumnValues, Vector whereColumnNames,
684 Vector whereColumnValues) throws SQLException {
685
686 // encode quotes and double-quotes to be SQL-safe
687 DBUtils.encode(updateColumnValues);
688 DBUtils.encode(whereColumnValues);
689
690 String columnName;
691 String columnValue;
692 // start the update statement
693 String statement = "UPDATE " + tableName;
694
695 // add the SET clause
696 statement += " SET ";
697 for (int i = 0; i < updateColumnNames.size(); i++) {
698 columnName = (String) updateColumnNames.get(i);
699 columnValue = (String) updateColumnValues.get(i);
700 statement += columnName + "='" + columnValue + "',";
701 }
702 // trim off the final comma
703 statement = statement.substring(0, statement.length() - 1);
704
705 // add the WHERE clause
706 statement += " WHERE ";
707 for (int i = 0; i < whereColumnNames.size(); i++) {
708 columnName = (String) whereColumnNames.get(i);
709 columnValue = (String) whereColumnValues.get(i);
710 statement += columnName + "='" + columnValue + "' AND ";
711 }
712 // trim off the final AND and space
713 statement = statement.substring(0, statement.length() - 4);
714 // cap off with a semicolon
715 statement += ";";
716
717 return lowAssetDB.executeStatement(statement);
718 }
719
720 /**
721 * This method finds all assets that have been removed from every
722 * collection and so they do not have a collection home thus become orphaned
723 * @return Table of orphaned assets
724 * @throws SQLException
725 */
726 public Table findOrphanedAssets() throws SQLException {
727 String query = ("SELECT AssetTable.* " +
728 "FROM AssetTable " +
729 "WHERE NOT EXISTS" +
730 "(SELECT * " +
731 "FROM AssetsCollectionsTable " +
732 "WHERE AssetTable.FileName = " +
733 "AssetsCollectionsTable.FileName) " +
734 "ORDER BY AssetTable.FileName ");
735 return lowAssetDB.executeQuery(query);
736 }
737
738 /**
739 * This method adds a record to the AssetsCollectionsTable so that an
740 * asset that has previously been deleted is now restored and can be
741 * found using search and browse.
742 *
743 * @param fileName the filename of the asset to be added
744 * @param collectionName the name of the collection to be added to
745 * @throws SQLException
746 */
747 public void addAssetToCollection(String fileName, String collectionName) throws SQLException {
748
749 Vector tempVec = new Vector();
750 tempVec.add(fileName);
751 tempVec.add(collectionName);
752 DBUtils.encode(tempVec);
753 fileName = tempVec.elementAt(0).toString();
754 collectionName = tempVec.elementAt(1).toString();
755
756 String query = ("INSERT INTO AssetsCollectionsTable " +
757 "(FileName,CollectionName) " +
758 "VALUES('"+fileName+"','"+collectionName+"');");
759 lowAssetDB.executeStatement(query);
760
761 String query2 = ("SELECT * " +
762 "FROM AssetsCollectionsTable " +
763 "WHERE AssetsCollectionsTable.CollectionName = 'AllAssets' " +
764 "AND AssetsCollectionsTable.FileName = '"+fileName+"';");
765 Table resultTable = lowAssetDB.executeQuery(query2);
766
767 if (!collectionName.equals("AllAssets") && resultTable.isEmpty()) {
768 String query3 = ("INSERT INTO AssetsCollectionsTable " +
769 "(FileName,CollectionName) " +
770 "VALUES('"+fileName+"','AllAssets');");
771 lowAssetDB.executeStatement(query3);
772 }
773 }
774
775 } // end of class Mid Asset DB