1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
|
/**
*
*/
package uk.org.ury.common.library;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import uk.org.ury.backend.database.DatabaseDriver;
import uk.org.ury.backend.database.exceptions.QueryFailureException;
import uk.org.ury.common.library.exceptions.EmptySearchException;
import uk.org.ury.common.library.item.LibraryItem;
import uk.org.ury.common.library.item.LibraryItemProperty;
/**
* A set of common utility routines to facilitate the extraction of
* library items from the library areas of the URY database.
*
* @author Matt Windsor
*/
public class LibraryUtils
{
/**
* Perform a library search.
*
* @param db The database to query.
*
* @param search The search fragment to include in the search.
* Can be empty or null.
*
* @throws IllegalArgumentException if the search term is
* are null.
*
* @throws QueryFailureException if the database backend
* yielded an error while executing the search
* query.
*
* @throws EmptySearchException if the search term is
* empty (to be handled as a user error).
*
* @return a list of LibraryItems matching the search terms.
*/
public static List<LibraryItem>
search (DatabaseDriver db, String search)
throws QueryFailureException, EmptySearchException
{
if (db == null)
throw new IllegalArgumentException ("Database handle is null.");
if (search == null)
throw new IllegalArgumentException ("Search string is null.");
List<LibraryItem> results = new ArrayList<LibraryItem> ();
// Return empty set if the search term is null.
if (search.equals(""))
throw new EmptySearchException ();
ResultSet rs = null;
Object[] params = {"%" + search + "%", "%" + search + "%", "%" + search + "%"};
try
{
rs = db.executeQuery (
"SELECT r.title AS album, t.title,"
+ " t.artist, recordlabel AS label, status, media AS medium, format,"
+ " datereleased, EXTRACT(EPOCH FROM dateadded) as dateadded,"
+ " EXTRACT(EPOCH FROM datetime_lastedit) AS dateedited,"
+ " shelfletter, shelfnumber, cdid, digitised, clean"
+ " FROM rec_record AS r"
+ " INNER JOIN rec_track AS t ON (r.recordid = t.recordid)"
+ " WHERE t.title ILIKE ?"
+ " OR t.artist ILIKE ?"
+ " OR r.title ILIKE ?"
+ " ORDER BY digitised DESC, medium ASC, r.title ASC,"
+ " t.artist ASC, t.title ASC;", params, 50);
}
catch (SQLException e)
{
throw new QueryFailureException (e.getMessage ());
}
try
{
while (rs.next ())
{
results.add (translateRow (rs));
}
}
catch (SQLException e)
{
throw new QueryFailureException (e.getMessage ());
}
return results;
}
/**
* Translate a row retrieved from the database into a LibraryItem.
*
* @param rs The result-set, or database cursor, pointing to the
* row to translate.
*
* @return A new LibraryItem containing the properties extracted
* from the translated row.
*/
private static LibraryItem
translateRow (ResultSet rs)
{
// Translate SQL columns into a list of properties.
Map<LibraryItemProperty, String> properties = new HashMap<LibraryItemProperty, String> ();
for (LibraryItemProperty p : LibraryItemProperty.values ())
{
try
{
properties.put (p, rs.getString (p.sql));
}
catch (SQLException e)
{
// Ignore this, as it is almost certainly just a non-existent
// property.
}
}
return new LibraryItem (properties);
}
}
|
