Source code: org/acs/damsel/srvr/db/DBUtils.java
1 package org.acs.damsel.srvr.db;
2
3 import java.sql.*;
4 import java.util.Vector;
5 import java.util.Iterator;
6 import java.security.*;
7 import java.math.*;
8
9 import org.acs.damsel.srvr.*;
10 import org.acs.damsel.srvr.asset.*;
11 import org.acs.damsel.srvr.collection.*;
12 import org.acs.damsel.srvr.search.*;
13 import org.acs.damsel.srvr.user.*;
14 import org.acs.damsel.srvr.auth.*;
15 import org.acs.damsel.client.ClientApp;
16 import org.apache.log4j.*;
17 import java.io.*;
18 import org.acs.damsel.srvr.db.*;
19 import org.acs.damsel.srvr.schema.*;
20 import org.acs.damsel.srvr.asset.*;
21 /**
22 * <p>Title: Database utility method package</p>
23 * <p>Description:
24 * This singleton class contains the high level methods for the AssetDB class.
25 * Methods include: the basic constructor, getPermissions, browse, and search,
26 * getCollections, getUsers, etc..
27 * </p>
28 * @version 1.0
29 */
30
31 public class DBUtils {
32 private static DBUtils instance = null;
33 private static Logger log = Logger.getLogger(AssetDB.class);
34 private MidAssetDB midAssetDB = null;
35
36 /**
37 * Constructor for DBUtils
38 * @throws SQLException
39 */
40 private DBUtils() throws SQLException {
41 Config config = Config.instance();
42 BasicConfigurator.resetConfiguration();
43 PropertyConfigurator.configure(config.getLogPropertiesFileName());
44 midAssetDB = MidAssetDB.instance();
45 }
46
47 /**
48 * This is to be used instead of the construction because it is a sigleton
49 * class. Establishes a connection to the database if one does not already exist
50 * and throws a SQLException if an error occurs.
51 * @return DBUtils object
52 * @throws SQLException
53 */
54 public static DBUtils instance() throws SQLException {
55 if (instance == null) {
56 instance = new DBUtils();
57 }
58 return instance;
59 }
60
61 /**
62 * Constructs a SQL query for the given keyword and returns the result. Throws
63 * a SQLException if the SQL is malformed, there is not a database connection,
64 * etc.
65 * @param keyword the keyword to be found in the seach field
66 * @param tag the tag to be searched through
67 * @param collectionSelect the collection to be searched through
68 * @return CollectionView, the results of the search
69 * @throws SQLException
70 */
71 public CollectionView simpleSearch(String keyword, String tag,
72 String collectionSelect) throws
73 SQLException {
74 log.debug("Entering DBUtils.simpleSearch(String, String, String)");
75 try {
76 CollectionView cv = new CollectionView();
77 String tagName = null;
78 String tagValue = null;
79 Asset a = null;
80 AssetDescriptor ad = null;
81 AssetDescriptorCollection adc = null;
82 AssetDB assetDB = AssetDB.instance();
83
84 Table resultTable = midAssetDB.simpleSearchQuery(keyword, tag,
85 collectionSelect);
86
87 if (!resultTable.isEmpty()) {
88 Schema s = ClientApp.instance().getSchemaMgr().getSchemaOfCollection(
89 collectionSelect);
90
91 Vector schemaTags = s.getTags();
92 Vector schemaNames = new Vector();
93
94 for (int x = 0; x < schemaTags.size(); x++) {
95 schemaNames.addElement( ( (MetaDataTag) schemaTags.elementAt(x)).
96 getName());
97 }
98 resultTable.orderBy("FileName");
99
100 String currentAsset = resultTable.getResultsElement(0, 0);
101 ad = new AssetDescriptor();
102 adc = new AssetDescriptorCollection();
103 a = new Asset();
104 for (int i = 0; i < resultTable.getRowCount(); i++) {
105 if (!resultTable.getResultsElement(i, 0).equals(currentAsset)) {
106 ad.setTag("FileName");
107 ad.setValue(currentAsset);
108 adc.addAssetDescriptor(ad);
109 a.setAssetDescriptors(adc);
110
111 cv.addAsset(a);
112 ad = new AssetDescriptor();
113 adc = new AssetDescriptorCollection();
114 a = new Asset();
115 currentAsset = resultTable.getResultsElement(i, 0);
116 } // end of if statement
117 if (schemaNames.contains(resultTable.getResultsElement(i, 1))) {
118 ad.setTag(resultTable.getResultsElement(i, 1));
119 ad.setValue(resultTable.getResultsElement(i, 2));
120 adc.addAssetDescriptor(ad);
121 ad = new AssetDescriptor();
122 } // end of if statement
123 } // end of for loop
124
125 ad.setTag("FileName");
126 ad.setValue(currentAsset);
127 adc.addAssetDescriptor(ad);
128 a.setAssetDescriptors(adc);
129
130 cv.addAsset(a);
131 }
132 return cv;
133 }
134 catch (TagNameNotFoundException ex) {
135 log.error("Caught unexpected TagNameNotFound exception in DBUtils.simpleSearch "+ex.getMessage());
136 return null;
137 }
138 catch (SchemaMgrException ex) {
139 log.error("Caught unexpected SchemaMgr exception in DBUtils.simpleSearch "+ex.getMessage());
140 return null;
141 }
142 catch (SQLException ex) {
143 log.error("Caught unexpected SQL exception in DBUtils.simpleSearch "+ex.getMessage());
144 return null;
145 }
146
147 } // end of method simpleSearch
148
149 /**
150 * This is a method that assists the SearchMgr by accessing the database and
151 * performing an advanced search given parameters from an AdvancedSearch
152 * params object. It returns a CollectionView containing all Assets matching
153 * the specified parameters.
154 * @param params AdvancedSearchParams The parameters to be searched through
155 * @return CollectionView containing all Assets captured by the search
156 * @throws SQLException
157 */
158 public CollectionView advancedSearch(AdvancedSearchParams params) throws
159 SQLException {
160 log.debug("Entering DBUtils.advancedSearch(AdvancedSearchParams)");
161 CollectionView collView = new CollectionView();
162 String tagName = null;
163 String tagValue = null;
164 Asset a = null;
165 AssetDescriptor ad = null;
166 AssetDescriptorCollection adc = null;
167 AssetDB assetDB = AssetDB.instance();
168
169 try {
170 Table resultTable = midAssetDB.advancedQuery(params);
171
172 // iterate through results, creating assets as we go
173
174 if (!resultTable.isEmpty()) {
175 Schema s = ClientApp.instance().getSchemaMgr().getSchemaOfCollection(
176 params.getCollectionName());
177
178 Vector schemaTags = s.getTags();
179 Vector schemaNames = new Vector();
180
181 for (int x = 0; x < schemaTags.size(); x++) {
182 schemaNames.addElement( ( (MetaDataTag) schemaTags.elementAt(x)).
183 getName());
184 }
185 resultTable.orderBy("FileName");
186
187 String currentAsset = resultTable.getResultsElement(0, 0);
188 ad = new AssetDescriptor();
189 adc = new AssetDescriptorCollection();
190 a = new Asset();
191 for (int i = 0; i < resultTable.getRowCount(); i++) {
192 if (!resultTable.getResultsElement(i, 0).equals(currentAsset)) {
193 ad.setTag("FileName");
194 ad.setValue(currentAsset);
195 adc.addAssetDescriptor(ad);
196 a.setAssetDescriptors(adc);
197
198 collView.addAsset(a);
199 ad = new AssetDescriptor();
200 adc = new AssetDescriptorCollection();
201 a = new Asset();
202 currentAsset = resultTable.getResultsElement(i, 0);
203 } // end of if statement
204 if (schemaNames.contains(resultTable.getResultsElement(i, 1))) {
205 ad.setTag(resultTable.getResultsElement(i, 1));
206 ad.setValue(resultTable.getResultsElement(i, 2));
207 adc.addAssetDescriptor(ad);
208 ad = new AssetDescriptor();
209 } // end of if statement
210 } // end of for loop
211
212 ad.setTag("FileName");
213 ad.setValue(currentAsset);
214 adc.addAssetDescriptor(ad);
215 a.setAssetDescriptors(adc);
216
217 collView.addAsset(a);
218 }
219
220 return collView;
221 }
222 catch (TagNameNotFoundException ex) {
223 log.error("Caught unexpected TagNameNotFound Exception in DBUtils.advancedSearch "+ex.getMessage());
224 return null;
225 }
226 catch (SchemaMgrException ex) {
227 log.error("Caught unexpected SchemaMgr Exception in DBUtils.advancedSearch "+ex.getMessage());
228 return null;
229 }
230 catch (SQLException ex) {
231 log.error("Caught unexpected SQL Exception in DBUtils.advancedSearch "+ex.getMessage());
232 return null;
233 }
234
235 }
236
237 /**
238 * A high-level method that determines if a user has a certain low level
239 * permission. Note that keyNames and keyValues hold
240 * keys for the table being checked. Also, the method automatically returns
241 * true if the user is an Administrator.
242 * @param username String name of user that permission is being found for
243 * @param action String what the user wants to do (Read, Write, or Delete).
244 * Note that action MUST be capitalized.
245 * @param table String table user is seeking to perform an action on. Note
246 * that in AuthMgr table will be the EXACT table name.
247 * @param keyName String name of the table's key field.
248 * @param keyValue String value of the table's key field.
249 * @return boolean returns true if user can has permission to perform action.
250 * @throws SQLException
251 */
252 public boolean canDoLowPerm(String username, String action, String table,
253 String keyName, String keyValue) throws
254 SQLException {
255 log.debug("Entering DBUtils.canDoLowPerm("+username+","+action+","+table+","+keyName+","+keyValue+")");
256 AssetDB assetDB = AssetDB.instance();
257 String permID = new String();
258 String type = new String();
259 Vector resultNames = new Vector();
260 Vector tableNames = new Vector();
261 Vector columnNames = new Vector();
262 Vector columnValues = new Vector();
263 Table resultTable;
264
265 //checking if user is administrator, if so return true
266 if (isUserInGroup(username, "Administrators")) {
267 return true;
268 }
269
270 //checking if user is owner of table
271 clearVectors(resultNames, tableNames, columnNames, columnValues);
272 resultNames.add(table + ".PermissionID");
273 tableNames.add(table);
274 columnNames.add(table + "." + keyName);
275 columnNames.add(table + ".OwnerName");
276 columnValues.add(keyValue);
277 columnValues.add(username);
278 resultTable = midAssetDB.andQuery(resultNames, tableNames, columnNames,
279 columnValues);
280 if (resultTable.getResultsElement(0, 0) != null) {
281 type = "Owner";
282 permID = resultTable.getResultsElement(0, 0);
283 }
284
285 //Checking if user is in table's group
286 else {
287 clearVectors(resultNames, tableNames, columnNames, columnValues);
288 resultNames.add(table + ".PermissionID");
289 tableNames.add(table);
290 tableNames.add("UsersGroupsTable");
291 columnNames.add(table + "." + keyName);
292 columnNames.add("UsersGroupsTable.UserName");
293 columnNames.add("UsersGroupsTable.GroupName");
294 columnValues.add(keyValue);
295 columnValues.add(username);
296 columnValues.add(table + ".GroupName");
297 resultTable = midAssetDB.andQuery(resultNames, tableNames, columnNames,
298 columnValues);
299 if (resultTable.getResultsElement(0, 0) != null) {
300 type = "Group";
301 permID = resultTable.getResultsElement(0, 0);
302 }
303 else {
304 type = "Others";
305
306 //getting permissionID from table
307 clearVectors(resultNames, tableNames, columnNames, columnValues);
308 resultNames.add(table + ".PermissionID");
309 tableNames.add(table);
310 columnNames.add(table + "." + keyName);
311 columnValues.add(keyValue);
312 resultTable = midAssetDB.andQuery(resultNames, tableNames, columnNames,
313 columnValues);
314 permID = resultTable.getResultsElement(0, 0);
315 }
316 }
317
318 String actionString = new String();
319 actionString = type + action;
320
321 // type stores the type of user (Owner, Group, Others)
322 // action stores the type of action (Read, Write, Delete)
323 // permID stores the int value of the permissions (0-511)
324 // Check the permID to see if the User has permission for the request
325
326 String perms = this.getPermissions(Integer.parseInt(permID));
327 int out = 0;
328 int in = 0;
329 if (type.equals("Owner")) out = 0;
330 else if (type.equals("Group")) out = 1;
331 else out = 2;
332 if (action.equals("Read")) in = 0;
333 else if (action.equals("Write")) in = 1;
334 else in = 2;
335
336 log.debug("Exiting DBUtils.canDoLowPerm(String, String, String, String, String)");
337 if (perms.charAt((out*3)+in) != '-')
338 return true;
339 return false;
340 }
341
342 /**
343 * Checks if a specified user is allowed to do a specified action. This checks
344 * high level permissions in the GroupsTable.
345 * @param username String user to check permissions for.
346 * @param colName String column name (permission) to be checked
347 * @return boolean - true if action is allowed.
348 * @throws SQLException
349 * michelle s. and christy
350 */
351 public boolean canDoHighPerm(String username, String colName) throws
352 SQLException {
353 log.debug("Entering DBUtils.canDoHighPerm(String, String)");
354
355 if (isUserInGroup(username, "Administrators")) {
356 log.debug("Exiting DBUtils.canDoHighPerm(String, String)");
357 return true;
358 }
359 Vector resultNames = new Vector();
360 Vector tableNames = new Vector();
361 Vector columnNames = new Vector();
362 Vector columnValues = new Vector();
363 resultNames.add("GroupsTable." + colName);
364 tableNames.add("GroupsTable");
365 tableNames.add("UsersGroupsTable");
366 columnNames.add("GroupsTable.GroupName");
367 columnNames.add("UsersGroupsTable.UserName");
368 columnValues.add("UsersGroupsTable.GroupName");
369 columnValues.add(username);
370 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
371 columnNames,
372 columnValues);
373 for (int i=0; i < resultTable.getRowCount(); i++) {
374 if (resultTable.getResultsElement(i, 0).equals("true")) {
375 log.debug("Exiting DBUtils.canDoHighPerm(String, String)");
376 return true;
377 }
378 }
379 log.debug("Exiting DBUtils.canDoHighPerm(String, String)");
380 return false;
381 }
382
383 // A helper method for clearing the contents of a set of vectors. Used to
384 // allow reuse of vectors for queries.
385 private void clearVectors(Vector resultNames, Vector tableNames,
386 Vector columnNames, Vector columnValues) {
387 resultNames.clear();
388 tableNames.clear();
389 columnNames.clear();
390 columnValues.clear();
391 }
392
393 /**
394 * This method browses through the the current collection view and returns all
395 * assets begin with the browseChar and whose tag mathces the tag.
396 * @param browseChar String the character that you want to browse by
397 * @param tag String the tag that you want to browse through
398 * @param collectionSelect String the collection that you want to browse through
399 * @return CollectionView
400 * @throws SQLException
401 */
402 public CollectionView browseDB(String browseChar, String tag,
403 String collectionSelect) throws
404 SQLException {
405 log.debug("Entering DBUtils.browseDB(String, String, String)");
406 Vector metaData = new Vector();
407 CollectionView cv = new CollectionView();
408 AssetDB assetDB = AssetDB.instance();
409
410
411 String tagName = null;
412 String tagValue = null;
413
414 Asset a;
415 AssetDescriptor ad = null;
416 AssetDescriptorCollection adc = null;
417
418 Table resultTable = midAssetDB.browseQuery(browseChar, tag,
419 collectionSelect);
420
421
422 try {
423 for (int i = 0; i < resultTable.getRowCount(); i++) {
424 a = new Asset();
425 a = assetDB.getAssetFromCollection(resultTable.getResultsElement(i, 0),
426 collectionSelect);
427 cv.addAsset(a);
428 }
429 log.debug("Exiting DBUtils.browseDB(String, String, String)");
430 return cv;
431 }
432 catch (SchemaException ex) {
433 log.error("Caught unexpected Schema Exception in BrowseDB.");
434 return null;
435 }
436 catch (SQLException ex) {
437 log.error("Caught unexpected SQL Exception in BrowseDB.");
438 return null;
439 }
440 }
441
442 /**
443 * This method returns all of the orphans in the assets Table
444 * @return Table full of little orphans
445 */
446 public Table getOrphanedAssets() {
447 log.debug("Entering DBUtils.getOrphanedAssets()");
448 try {
449 Table resultsTable = midAssetDB.findOrphanedAssets();
450 log.debug("Exiting DBUtils.getOrphanedAssets()");
451 return resultsTable;
452 }
453 catch (SQLException ex) {
454 log.error("Unexpected SQL Exception in getOrphanedAssets.");
455 log.error(ex.getMessage());
456 return null;
457 }
458 }
459
460 /**
461 * Calculates the SHA-1 hash of the input string and returns the hash as a
462 * string of hexadecimal numbers representing the bytes of the hash.
463 * This is used to calculate the hashes of passwords.
464 * @param input String the string to be hashed
465 * @return String the hash encoded as a string of hexadecimal numbers
466 * returns null if an exception is encountered while computing the hash
467 */
468 public String hash(String input) {
469 log.debug("Entering DBUtils.hash(String)");
470 StringBuffer hexString = new StringBuffer("");
471 try {
472 MessageDigest md = MessageDigest.getInstance("SHA-1");
473 byte[] digest;
474 md.reset();
475 md.update(input.getBytes("UTF-8"));
476 digest = md.digest();
477 for (int i = 0; i < digest.length; i++) {
478 hexString.append(Integer.toHexString(0xFF & digest[i]));
479 }
480 }
481 catch (UnsupportedEncodingException ex) {
482 log.error("UnsupportedEncodingException caught in DBUtils.hash");
483 return null;
484 }
485 catch (NoSuchAlgorithmException ex) {
486 log.error("NoSuchAlgorithmException caught in DBUtils.hash");
487 return null;
488 }
489 log.debug("Exiting DBUtils.hash(String)");
490 return hexString.toString();
491 }
492
493 /**
494 * Method to add indicated existing asset to indicated existing collection
495 * only if asset is not already in that collection
496 * @param fileName of asset to be added
497 * @param collectionName collection to add to
498 */
499 public void addAssetToCollection(String fileName, String collectionName) throws
500 SQLException {
501 log.debug("Entering/Exiting DBUtils.addAssetToCollection(String, String)");
502
503
504 if (!AssetDB.instance().isAssetInCollection(fileName, collectionName)) {
505 try {
506 midAssetDB.addAssetToCollection(fileName, collectionName);
507 }
508 catch (SQLException ex) {
509 log.error("Caught unexpected SQLException in addAssetToCollection.");
510 log.error(ex.getMessage());
511 }
512 }
513
514
515 }
516
517 /**
518 * Searches a Vector of Strings and encodes single quotes as two single quotes.
519 * @param vector Vector a Vector of Strings to be encoded
520 * @return Vector a Vector of Strings with the values encoded
521 */
522 public static Vector encode(Vector vector) {
523 // log.debug("Entering DBUtils.encode(Vector)");
524 String s;
525 StringBuffer sb = new StringBuffer();
526 String doubleQuoteEncodeString = sb.toString();
527 for (int i = 0; i < vector.size(); i++) {
528 Object obj = vector.elementAt(i);
529 if (obj != null) {
530 s = obj.toString();
531 s = new String(s);
532 s = s.replaceAll("''", "'");
533 s = s.replaceAll("'", "''");
534 vector.set(i, s);
535 }
536 }
537 // log.debug("Exiting DBUtils.encode(Vector)");
538 return vector;
539 }
540
541 /**
542 * Searches a String and encodes single quotes as two single quotes.
543 * @param str String a String to be encoded
544 * @return String a String with the values encoded
545 */
546 public static String encode(String str) {
547 // log.debug("Entering DBUtils.encode(Vector)");
548
549 StringBuffer sb = new StringBuffer();
550 String doubleQuoteEncodeString = sb.toString();
551 String s = new String(str);
552 if (str != null) {
553 s = str.replaceAll("''", "'");
554 s = str.replaceAll("'", "''");
555 }
556
557 return s;
558 }
559
560
561 /**
562 * Searches a Vector of Strings and decodes two single quotes as one single quote.
563 * @param vector Vector a Vector of Strings to be encoded
564 * @return Vector a Vector of Strings with the values encoded
565 */
566 public static Vector decode(Vector vector) {
567 // log.debug("Entering DBUtils.decode(Vector)");
568 String s;
569 StringBuffer sb = new StringBuffer();
570 String doubleQuoteEncodeString = sb.toString();
571 for (int i = 0; i < vector.size(); i++) {
572 Object obj = vector.elementAt(i);
573 if (obj != null) {
574 s = obj.toString();
575 s = new String(s);
576 s = s.replaceAll("''", "'");
577 vector.set(i, s);
578 }
579 }
580 // log.debug("Exiting DBUtils.decode(Vector)");
581 return vector;
582 }
583
584 /**
585 * This method returns a vector of strings containing all of the names of the
586 * collections that the user can view.
587 * @param user User trying to view collections
588 * @return Vector Vector of strings containing viewable collections.
589 * @throws SQLException
590 */
591 public Vector getUsersCollections(User user) throws SQLException {
592 return this.getViewableCollections(user);
593 }
594
595 /**
596 * This method returns the permissionID associated with a set of permissions of
597 * the form "rwdrwdrwd" where the first set of "rwd" applies to the owner, the
598 * second set applies to the group, and the third set applies to others. This
599 * method takes in a String of the form "rwdrwdrwd" with any character (dashes
600 * are preferable) in the place of permissions that are not held.
601 * For example, to return the permissionID associated with an object that has
602 * full owner permssions, group read and write, and others read, one would pass
603 * in the String "rwdrw-r--".
604 * The method would then compute the integer value of the permissionID as stored
605 * in the database and return it.
606 * NOTE: This method does not query the database, but simply calculates the
607 * number based on the same method for calculation used in the database.
608 * @param perm String String of the form "rwdrwdrwd" with anything in place of
609 * permissions that are not held.
610 * @return int the permissionID as stored in the database as an int
611 */
612 public int getPermissions (String perm) {
613 log.debug("Entering DBUtils.getPermissions(String)");
614 String[] sArray = new String[3];
615 boolean[] bArray = new boolean[9];
616 int rVal = 0;
617
618 // Check the validity of the permission string and create boolean array
619 if (perm.length() != 9)
620 return -1;
621 sArray[0] = perm.substring(0,3);
622 sArray[1] = perm.substring(3,6);
623 sArray[2] = perm.substring(6,9);
624 for (int out = 0; out < 3; out++) {
625 for (int in = 0; in < 3; in++) {
626 if (in == 0 && (sArray[out].charAt(in) == 'r'))
627 bArray[(out*3)+in] = true;
628 else if (in == 1 && sArray[out].charAt(in) == 'w')
629 bArray[(out*3)+in] = true;
630 else if (in == 2 && sArray[out].charAt(in) == 'd')
631 bArray[(out*3)+in] = true;
632 }
633 }
634
635 for (int i = 0; i < 9; i++){
636 if (bArray[i] == true)
637 rVal = rVal + (int) Math.pow(2, i);
638 }
639 log.debug("Exiting DBUtils.getPermissions(String)");
640 return rVal;
641 }
642
643 /**
644 * This method will take in an integer representing the permissionID of a
645 * group of permissions as stored in the database. The String returned will be
646 * of the form "rwdrwdrwd" with dashes in place of permissions that are not held.
647 * The first group of "rwd" corresponds to the owner, second to the group, third
648 * to others.
649 * For example, a string which has full owner permssions, group read and write,
650 * and others read would be "rwdrw-r--".
651 * Passing in a permissionID integer will return the appropriate permission string
652 * as it would be stored in the database.
653 * NOTE: This function does not access the database but determines the string
654 * based on the math used to store the permissionID in the database.
655 * @param permissionID int representing the numeral permissionID stored in the
656 * database
657 * @return String of permissions of the form "rwdrwdrwd" with dashes (-) in place
658 * of permissions that are not held
659 */
660 public String getPermissions (int permissionID) {
661 log.debug("Entering DBUtils.getPermissions(int)");
662 boolean[] bArray = new boolean[9];
663 String result = new String("");
664
665 if (permissionID < 0 || permissionID > 511)
666 return null;
667
668 for (int i = 9; i >= 0; i--) {
669 if ((permissionID / (int) Math.pow(2, i)) == 1) {
670 permissionID = permissionID % (int) Math.pow(2, i);
671 bArray[i] = true;
672 }
673 }
674
675 for (int out = 0; out < 3; out++) {
676 for (int in = 0; in < 3; in++) {
677 if (in == 0 && bArray[ (out * 3) + in] == true)
678 result = result + "r";
679 else if (in == 1 && bArray[ (out * 3) + in] == true)
680 result = result + "w";
681 else if (in == 2 && bArray[ (out * 3) + in] == true)
682 result = result + "d";
683 else
684 result = result + "-";
685 }
686 }
687 log.debug("Exiting DBUtils.getPermissions(int)");
688 return result;
689 }
690
691 /**
692 * This method returns a vector of collectionNames (Strings) editable by the
693 * specified user.
694 * @param user User
695 * @return Vector of collectionNames
696 * @throws SQLException
697 */
698 public Vector getEditableCollections (User user) {
699 log.debug("Entering DBUtils.getEditableCollections(User)");
700
701 try {
702 // if the user is an administrator, return all of the collections
703 if(this.isUserInGroup(user.getUserName(), "Administrators")) {
704 Vector tableNames = new Vector();
705 Vector resultNames = new Vector();
706 tableNames.add("CollectionTable");
707 resultNames.add("CollectionName");
708
709 MidAssetDB midAssetDB = MidAssetDB.instance();
710 Table table = midAssetDB.andQuery(resultNames, tableNames,
711 new Vector(), new Vector());
712 return table.getCol("CollectionName");
713 }
714
715 Vector editableCollections = new Vector();
716
717 Vector collectionNames = new Vector();
718 Vector collectionPermissionIDs = new Vector();
719
720 Vector tableNames = new Vector();
721 Vector resultNames = new Vector();
722 Vector columnNames = new Vector();
723 Vector columnValues = new Vector();
724
725 tableNames.add("CollectionTable");
726 resultNames.add("*");
727
728 MidAssetDB midAssetDB = MidAssetDB.instance();
729 Table table = midAssetDB.andQuery(resultNames, tableNames,
730 columnNames, columnValues);
731
732 tableNames = new Vector();
733 resultNames = new Vector();
734 columnNames = new Vector();
735 columnValues = new Vector();
736
737 resultNames.add("UsersGroupsTable.GroupName");
738 tableNames.add("UsersGroupsTable");
739 columnNames.add("UsersGroupsTable.UserName");
740 columnValues.add(user.getUserName());
741
742 Table groupTable = midAssetDB.andQuery(resultNames, tableNames,
743 columnNames, columnValues);
744
745 int permissionColNum = table.getMetaData().indexOf("PermissionID");
746 int nameColNum = table.getMetaData().indexOf("CollectionName");
747 int ownerColNum = table.getMetaData().indexOf("OwnerName");
748 int groupColNum = table.getMetaData().indexOf("GroupName");
749 AuthMgr amgr = ClientApp.instance().getAuthMgr();
750 String permissionID;
751 Vector permissionsList;
752 for (int i = 0; i < table.getRowCount(); i++) {
753 permissionID = (String) table.getResultsElement(i, permissionColNum);
754 permissionsList = (Vector) amgr.getPermissionNamesFromID(permissionID);
755 if (permissionsList.contains("ownerWrite") &&
756 table.getResultsElement(i, ownerColNum).equals(user.getUserName())) {
757 editableCollections.addElement(table.getResultsElement(i, nameColNum));
758 }
759 else if (permissionsList.contains("groupWrite") &&
760 groupTable.isInResults("GroupName",
761 table.getResultsElement(i, groupColNum))) {
762 editableCollections.addElement(table.getResultsElement(i, nameColNum));
763 }
764 else if (permissionsList.contains("othersWrite")) {
765 editableCollections.addElement(table.getResultsElement(i, nameColNum));
766 }
767 } // end of for loop
768
769 return editableCollections;
770 } // end of try statement
771 catch (SQLException ex) {
772 log.error("Caught unexpected SQL Exception in DBUtils.getEditableCollections"+ex.getMessage());
773 return null;
774 }
775
776
777 } // end of getEditableCollections
778
779 /**
780 * This method returns a vector of collection that the user has
781 * rights to view.
782 *
783 * @param user User : the user that is currently logged on
784 * @return Vector : collection names that the user has permission to view
785 */
786 public Vector getViewableCollections(User user) {
787 log.debug("Entering DBUtils.getViewableCollections(User)");
788
789 try {
790 // if the user is an administrator, return all of the collections
791 if(this.isUserInGroup(user.getUserName(), "Administrators")) {
792 Vector tableNames = new Vector();
793 Vector resultNames = new Vector();
794 tableNames.add("CollectionTable");
795 resultNames.add("CollectionName");
796
797 MidAssetDB midAssetDB = MidAssetDB.instance();
798 Table table = midAssetDB.andQuery(resultNames, tableNames,
799 new Vector(), new Vector());
800 return table.getCol("CollectionName");
801 }
802
803 Vector editableCollections = new Vector();
804
805 Vector collectionNames = new Vector();
806 Vector collectionPermissionIDs = new Vector();
807
808 Vector tableNames = new Vector();
809 Vector resultNames = new Vector();
810 Vector columnNames = new Vector();
811 Vector columnValues = new Vector();
812
813 tableNames.add("CollectionTable");
814 resultNames.add("*");
815
816 MidAssetDB midAssetDB = MidAssetDB.instance();
817 Table table = midAssetDB.andQuery(resultNames, tableNames,
818 columnNames, columnValues);
819
820 tableNames = new Vector();
821 resultNames = new Vector();
822 columnNames = new Vector();
823 columnValues = new Vector();
824
825 resultNames.add("UsersGroupsTable.GroupName");
826 tableNames.add("UsersGroupsTable");
827 columnNames.add("UsersGroupsTable.UserName");
828 columnValues.add(user.getUserName());
829
830 Table groupTable = midAssetDB.andQuery(resultNames, tableNames,
831 columnNames, columnValues);
832
833 int permissionColNum = table.getMetaData().indexOf("PermissionID");
834 int nameColNum = table.getMetaData().indexOf("CollectionName");
835 int ownerColNum = table.getMetaData().indexOf("OwnerName");
836 int groupColNum = table.getMetaData().indexOf("GroupName");
837 AuthMgr amgr = ClientApp.instance().getAuthMgr();
838 String permissionID;
839 Vector permissionsList;
840 for (int i = 0; i < table.getRowCount(); i++) {
841 permissionID = (String) table.getResultsElement(i, permissionColNum);
842 permissionsList = (Vector) amgr.getPermissionNamesFromID(permissionID);
843 if (permissionsList.contains("othersRead")) {
844 editableCollections.addElement(table.getResultsElement(i, nameColNum));
845 }
846 else if (permissionsList.contains("groupRead") &&
847 groupTable.isInResults("GroupName",
848 table.getResultsElement(i, groupColNum))) {
849 editableCollections.addElement(table.getResultsElement(i, nameColNum));
850 }
851 else if (permissionsList.contains("ownerRead") &&
852 table.getResultsElement(i, ownerColNum).equals(user.getUserName())) {
853 editableCollections.addElement(table.getResultsElement(i, nameColNum));
854 }
855 } // end of for loop
856
857 return editableCollections;
858 } // end of try statement
859 catch (SQLException ex) {
860 log.error("Caught unexpected SQL Exception in DBUtils.getEditableCollections"+ex.getMessage());
861 return null;
862 }
863
864 } // end of get Viewable Collections
865
866 /**
867 * This method returns a vector of strings containing all of the schema names that can
868 * be edited by user.
869 * @param user User, user who can edit schemas.
870 * @return Vector, vector of strings
871 */
872 public Vector getEditableSchemas(User user){
873 log.debug("Entering DBUtils.getEditableSchemas(User)");
874
875 try {
876 // if the user is an administrator, return all of the schemas
877 if(this.isUserInGroup(user.getUserName(), "Administrators")) {
878 Vector tableNames = new Vector();
879 Vector resultNames = new Vector();
880 tableNames.add("SchemaTable");
881 resultNames.add("SchemaName");
882
883 MidAssetDB midAssetDB = MidAssetDB.instance();
884 Table table = midAssetDB.andQuery(resultNames, tableNames,
885 new Vector(), new Vector());
886 return table.getCol("SchemaName");
887 }
888
889 // otherwise, query the database for the schemas that the user can edit
890 Vector editableSchemas = new Vector();
891
892 Vector schemaNames = new Vector();
893 Vector schemaPermissionIDs = new Vector();
894
895 Vector tableNames = new Vector();
896 Vector resultNames = new Vector();
897 Vector columnNames = new Vector();
898 Vector columnValues = new Vector();
899
900 tableNames.add("SchemaTable");
901 resultNames.add("*");
902
903 MidAssetDB midAssetDB = MidAssetDB.instance();
904 Table table = midAssetDB.andQuery(resultNames, tableNames,
905 columnNames, columnValues);
906
907 tableNames = new Vector();
908 resultNames = new Vector();
909 columnNames = new Vector();
910 columnValues = new Vector();
911
912 resultNames.add("UsersGroupsTable.GroupName");
913 tableNames.add("UsersGroupsTable");
914 columnNames.add("UsersGroupsTable.UserName");
915 columnValues.add(user.getUserName());
916
917 Table groupTable = midAssetDB.andQuery(resultNames, tableNames,
918 columnNames, columnValues);
919
920 int permissionColNum = table.getMetaData().indexOf("PermissionID");
921 int nameColNum = table.getMetaData().indexOf("SchemaName");
922 int ownerColNum = table.getMetaData().indexOf("OwnerName");
923 int groupColNum = table.getMetaData().indexOf("GroupName");
924 AuthMgr amgr = ClientApp.instance().getAuthMgr();
925 String permissionID;
926 Vector permissionsList;
927 for (int i = 0; i < table.getRowCount(); i++) {
928 permissionID = (String) table.getResultsElement(i, permissionColNum);
929 permissionsList = (Vector) amgr.getPermissionNamesFromID(permissionID);
930 if (permissionsList.contains("ownerWrite") &&
931 table.getResultsElement(i, ownerColNum).equals(user.getUserName())) {
932 editableSchemas.addElement(table.getResultsElement(i, nameColNum));
933 }
934 else if (permissionsList.contains("groupWrite") &&
935 groupTable.isInResults("GroupName",
936 table.getResultsElement(i, groupColNum))) {
937 editableSchemas.addElement(table.getResultsElement(i, nameColNum));
938 }
939 else if (permissionsList.contains("othersWrite")) {
940 editableSchemas.addElement(table.getResultsElement(i, nameColNum));
941 }
942 } // end of for loop
943
944 return editableSchemas;
945 } // end of try statement
946 catch (SQLException ex) {
947 log.error("Caught unexpected SQL Exception in DBUtils.getEditableSchemas"+ex.getMessage());
948 return null;
949 }
950
951
952 }
953
954 /**
955 * This method determines of the specified collection is private.
956 * @return true if collection is private, false if otherwise.
957 * @param collectionName Collection
958 */
959 public boolean isCollectionPrivate (String collectionName) {
960 log.debug("Entering DBUtils.isCollectionPrivate(String)");
961 if (collectionName.equals("AllAssets"))
962 return false;
963 try {
964 AssetDB assetDB = AssetDB.instance();
965 Collection coll = assetDB.getCollection(collectionName);
966 // Get the collection's permissionID from the database
967 int permID = Integer.parseInt(coll.getPermissionID());
968 String perms = this.getPermissions(permID);
969 // Check if the permissions have any options for the User at all
970 if ((perms.charAt(6) == 'r') || (perms.charAt(7) == 'w') || (perms.charAt(8) == 'd')) {
971 log.debug("Exiting DBUtils.isCollectionPrivate(String)");
972 return false;
973 }
974 }
975 catch (SQLException ex) {
976 log.warn("Unexpected SQLException caught in isCollectionPrivate : " + ex.getMessage());
977 }
978 return true;
979 }
980
981 /**
982 *This method returns all of the permissions that the person can do.
983 * @return Vector of permissions stored in the groupsTable
984 * that the person "Can" do
985 * @throws SQLException
986 */
987 public Vector getPermissionsList() throws SQLException {
988 log.debug("Entering DBUtils.getPermissionsList()");
989 Vector resultNames = new Vector();
990 Vector tableNames = new Vector();
991 Vector columnNames = new Vector();
992 Vector columnValues = new Vector();
993
994 resultNames.add("GroupsTable.*");
995 tableNames.add("GroupsTable");
996
997 Table resultTable = midAssetDB.andQuery(resultNames, tableNames,
998 columnNames, columnValues);
999
1000 Vector metaData = resultTable.getMetaData();
1001 Vector permissions = new Vector();
1002 for (Iterator i = metaData.iterator(); i.hasNext(); ) {
1003 String columnName = (String) i.next();
1004 if (columnName.substring(0,3).equals("Can"))
1005 permissions.add(columnName);
1006 }
1007 log.debug("Exiting DBUtils.getPermissionsList()");
1008 return permissions;
1009 }
1010
1011 /**
1012 * This method takes in the name of a user and a group and checks if the user
1013 * is in the group in the database.
1014 * @param userName name of the desired user
1015 * @param groupName name of the desired group
1016 * @return boolean true if user is in group, false otherwise
1017 * @throws SQLException
1018 */
1019 public boolean isUserInGroup(String userName, String groupName) throws SQLException {
1020 log.debug("Entering DBUtils.isUserInGroup(String, String)");
1021 Vector tableNames = new Vector();
1022 Vector resultNames = new Vector();
1023 Vector columnNames = new Vector();
1024 Vector columnValues = new Vector();
1025
1026 tableNames.add("UsersGroupsTable");
1027 resultNames.add("GroupName");
1028 columnNames.add("UserName");
1029 columnNames.add("GroupName");
1030 columnValues.add(userName);
1031 columnValues.add(groupName);
1032
1033 MidAssetDB midAssetDB = MidAssetDB.instance();
1034 Table table = midAssetDB.andQuery(resultNames, tableNames, columnNames, columnValues);
1035 log.debug("Exiting DBUtils.isUserInGroup(String, String)");
1036 if(table.isEmpty())
1037 return false;
1038 return true;
1039 }
1040
1041 /**
1042 * This method will take in a user object and return the associated assets stored
1043 * in the usersfavorites table in the database as a CollectionView.
1044 * in the usersfavorites table in the database as a CollectionView.
1045 * @return CollectionView containing the user's favorite assets
1046 * @param user User
1047 * @throws SQLException
1048 */
1049 public CollectionView getUsersFavorites(User user) throws SQLException {
1050 log.debug("Entering DBUtils.getUsersFavorites(User)");
1051 AssetDB assetDB = AssetDB.instance();
1052 MidAssetDB midAssetDB = MidAssetDB.instance();
1053 Vector resultNames = new Vector();
1054 Vector tableNames = new Vector();
1055 Vector columnNames = new Vector();
1056 Vector columnValues = new Vector();
1057 resultNames.add("UsersFavorites.FileName");
1058 tableNames.add("UsersFavorites");
1059 columnNames.add("UserName");
1060 columnValues.add(user.getUserName());
1061
1062 Table resultsTable = midAssetDB.andQuery(resultNames, tableNames, columnNames, columnValues);
1063
1064 Vector results = resultsTable.getCol(0);
1065 CollectionView resultsCV = new CollectionView();
1066
1067 for(int i=0; i<results.size();i++){
1068 resultsCV.addAsset(assetDB.getAsset((String)results.elementAt(i)));
1069 }
1070 log.debug("Exiting DBUtils.getUsersFavorites(User)");
1071 return resultsCV;
1072 }
1073
1074}