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
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
|
/**
*
*/
package uk.org.ury.show;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import uk.org.ury.database.DatabaseDriver;
import uk.org.ury.database.exceptions.QueryFailureException;
import uk.org.ury.show.item.ShowItem;
import uk.org.ury.show.item.ShowItemProperty;
/**
* A set of common utility routines to facilitate the extraction of
* show items from the show storage areas of the URY database.
*
* @author Matt Windsor
*
*/
public class ShowUtils
{
/**
* The number of channels reserved for show items.
*
* TODO: move this somewhere more appropriate.
*/
public static final int NUM_CHANNELS = 3;
// Maximum number of results to pull from database
private static final int MAX_RESULTS = 50;
/**
* Return the names of the public track folders, or "bins".
*
* @param db The database to query.
*
* @return a list of the public folder names.
* The list may be empty.
*
* @throws IllegalArgumentException if the database is
* null, the show ID is negative or the
* channel index falls out of bounds.
*
* @throws QueryFailureException if the database backend
* yielded an error while executing the search
* query.
*/
public static List<String>
getPublicFolders (DatabaseDriver db)
throws QueryFailureException
{
if (db == null)
throw new IllegalArgumentException ("Database handle is null.");
List<String> results = new ArrayList<String> ();
ResultSet rs = null;
try
{
rs = db.executeQuery ("SELECT share AS name, description"
+ " FROM baps_filefolder"
+ " WHERE baps_filefolder.public"
+ " = TRUE"
+ " ORDER BY filefolderid ASC", MAX_RESULTS);
}
catch (SQLException e1)
{
throw new QueryFailureException (e1.getMessage ());
}
try
{
while (rs.next ())
{
results.add (rs.getString (2));
}
}
catch (SQLException e)
{
throw new QueryFailureException (e.getMessage ());
}
return results;
}
/**
* Given a show and a channel, retrieve a list of all show items
* bound to that channel for the show.
*
* @param db The database to query.
*
* @param showID The unique number that identifies the show.
*
* @param channel The index of the channel to query.
*
* @return a list of ShowItems extracted from the show and
* channel. The list may be empty.
*
* @throws IllegalArgumentException if the database is
* null, the show ID is negative or the
* channel index falls out of bounds.
*
* @throws QueryFailureException if the database backend
* yielded an error while executing the search
* query.
*/
public static List<ShowItem>
getChannelList (DatabaseDriver db, int showID, int channel)
throws QueryFailureException
{
if (db == null)
throw new IllegalArgumentException ("Database handle is null.");
if (showID < 0)
throw new IllegalArgumentException ("Show ID is negative.");
if (channel < 0 || channel >= NUM_CHANNELS)
throw new IllegalArgumentException ("Channel index is out of bounds.");
List<ShowItem> results = new ArrayList<ShowItem> ();
ResultSet rs = null;
Object[] params = {showID, channel};
try
{
rs = db.executeQuery ("SELECT name1, name2, position"
+ " FROM baps_show"
+ " INNER JOIN baps_listing"
+ " ON baps_show.showid"
+ " = baps_listing.showid"
+ " INNER JOIN baps_item"
+ " ON baps_listing.listingid"
+ " = baps_item.listingid"
+ " WHERE baps_show.showid"
+ " = ?"
+ " AND baps_listing.channel"
+ " = ?"
+ " ORDER BY position ASC", params, MAX_RESULTS);
}
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 ShowItem.
*
* @param rs The result-set, or database cursor, pointing to the
* row to translate.
*
* @return A new ShowItem containing the properties extracted
* from the translated row.
*/
private static ShowItem
translateRow (ResultSet rs)
{
// Translate SQL columns into a list of properties.
HashMap<ShowItemProperty, String> properties = new HashMap<ShowItemProperty, String> ();
for (ShowItemProperty p : ShowItemProperty.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 ShowItem (properties);
}
}
|
