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
|
/*
* ShowUtils.java
* ------------------
*
* Part of the URY Backend Platform
*
* V0.00 2011/03/21
*
* (C) 2011 URY Computing
*/
package uk.org.ury.common.show;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.ResourceBundle;
import uk.org.ury.backend.database.DatabaseDriver;
import uk.org.ury.backend.database.exceptions.QueryFailureException;
import uk.org.ury.common.show.item.ShowItem;
import uk.org.ury.common.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;
// Resource bundle (for exception reasons)
private static ResourceBundle rb = ResourceBundle.getBundle(ShowUtils.class
.getPackage().getName() + ".ShowUtils");
/**
* 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(
rb.getString("ERR_DB_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(
rb.getString("ERR_DB_HANDLE_IS_NULL"));
if (showID < 0)
throw new IllegalArgumentException(
rb.getString("ERR_NEGATIVE_SHOW_ID"));
if (channel < 0 || channel >= NUM_CHANNELS)
throw new IllegalArgumentException(
rb.getString("ERR_CH_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);
}
}
|
