1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 package org.bitrepository.pillar.cache.database;
23
24 import static org.bitrepository.pillar.cache.database.DatabaseConstants.CHECKSUM_TABLE;
25 import static org.bitrepository.pillar.cache.database.DatabaseConstants.CS_CHECKSUM;
26 import static org.bitrepository.pillar.cache.database.DatabaseConstants.CS_DATE;
27 import static org.bitrepository.pillar.cache.database.DatabaseConstants.CS_FILE_ID;
28 import static org.bitrepository.pillar.cache.database.DatabaseConstants.CS_COLLECTION_ID;
29
30 import java.sql.Connection;
31 import java.sql.PreparedStatement;
32 import java.sql.ResultSet;
33 import java.sql.SQLException;
34 import java.util.ArrayList;
35 import java.util.Date;
36 import java.util.List;
37
38 import javax.xml.datatype.XMLGregorianCalendar;
39
40 import org.bitrepository.common.ArgumentValidator;
41 import org.bitrepository.common.utils.CalendarUtils;
42 import org.bitrepository.pillar.cache.ChecksumEntry;
43 import org.bitrepository.service.database.DBConnector;
44 import org.bitrepository.service.database.DatabaseUtils;
45
46
47
48
49 public class ChecksumExtractor {
50
51 private final DBConnector connector;
52
53
54
55
56
57 public ChecksumExtractor(DBConnector connector) {
58 ArgumentValidator.checkNotNull(connector, "DBConnector connector");
59
60 this.connector = connector;
61 }
62
63
64
65
66
67
68
69 public Date extractDateForFile(String fileId, String collectionId) {
70 ArgumentValidator.checkNotNullOrEmpty(fileId, "String fileId");
71
72 String sql = "SELECT " + CS_DATE + " FROM " + CHECKSUM_TABLE + " WHERE " + CS_FILE_ID + " = ? AND "
73 + CS_COLLECTION_ID + " = ?";
74 return DatabaseUtils.selectDateValue(connector, sql, fileId, collectionId);
75 }
76
77
78
79
80
81
82
83 public String extractChecksumForFile(String fileId, String collectionId) {
84 ArgumentValidator.checkNotNullOrEmpty(fileId, "String fileId");
85
86 String sql = "SELECT " + CS_CHECKSUM + " FROM " + CHECKSUM_TABLE + " WHERE " + CS_FILE_ID + " = ? AND "
87 + CS_COLLECTION_ID + " = ?";
88 return DatabaseUtils.selectStringValue(connector, sql, fileId, collectionId);
89 }
90
91
92
93
94
95
96
97 public boolean hasFile(String fileId, String collectionId) {
98 ArgumentValidator.checkNotNullOrEmpty(fileId, "String fileId");
99
100 String sql = "SELECT COUNT(*) FROM " + CHECKSUM_TABLE + " WHERE " + CS_FILE_ID + " = ? AND "
101 + CS_COLLECTION_ID + " = ?";
102 return DatabaseUtils.selectIntValue(connector, sql, fileId, collectionId) != 0;
103 }
104
105
106
107
108
109
110
111 public ChecksumEntry extractSingleEntry(String fileId, String collectionId) {
112 ArgumentValidator.checkNotNullOrEmpty(fileId, "String fileId");
113
114 String sql = "SELECT " + CS_FILE_ID + " , " + CS_CHECKSUM + " , " + CS_DATE + " FROM " + CHECKSUM_TABLE
115 + " WHERE " + CS_FILE_ID + " = ? AND " + CS_COLLECTION_ID + " = ?";
116 try {
117 PreparedStatement ps = null;
118 ResultSet res = null;
119 Connection conn = null;
120 try {
121 conn = connector.getConnection();
122 ps = DatabaseUtils.createPreparedStatement(conn, sql, fileId, collectionId);
123 res = ps.executeQuery();
124 if(!res.next()) {
125 throw new IllegalStateException("No entry for the file '" + fileId + "'.");
126 }
127 return extractChecksumEntry(res);
128 } finally {
129 if(res != null) {
130 res.close();
131 }
132 if(ps != null) {
133 ps.close();
134 }
135 if(conn != null) {
136 conn.close();
137 }
138 }
139 } catch (SQLException e) {
140 throw new IllegalStateException("Cannot extract the ChecksumEntry for '" + fileId + "'", e);
141 }
142 }
143
144
145
146
147
148
149
150
151
152
153 public ExtractedFileIDsResultSet getFileIDs(XMLGregorianCalendar minTimeStamp, XMLGregorianCalendar maxTimeStamp,
154 Long maxNumberOfResults, String collectionId) {
155 List<Object> args = new ArrayList<Object>();
156 StringBuilder sql = new StringBuilder();
157 sql.append("SELECT " + CS_FILE_ID + " , " + CS_DATE + " FROM " + CHECKSUM_TABLE + " WHERE " + CS_COLLECTION_ID
158 + " = ?");
159 args.add(collectionId);
160
161 if(minTimeStamp != null) {
162 sql.append(" AND " + CS_DATE + " > ? ");
163 args.add(CalendarUtils.convertFromXMLGregorianCalendar(minTimeStamp));
164 }
165 if(maxTimeStamp != null) {
166 sql.append(" AND " + CS_DATE + " <= ? ");
167 args.add(CalendarUtils.convertFromXMLGregorianCalendar(maxTimeStamp));
168 }
169 sql.append(" ORDER BY " + CS_DATE + " ASC ");
170
171 ExtractedFileIDsResultSet results = new ExtractedFileIDsResultSet();
172 try {
173 PreparedStatement ps = null;
174 ResultSet res = null;
175 Connection conn = null;
176 try {
177 conn = connector.getConnection();
178 ps = DatabaseUtils.createPreparedStatement(conn, sql.toString(), args.toArray());
179 res = ps.executeQuery();
180
181 int i = 0;
182 while(res.next() && (maxNumberOfResults == null || i < maxNumberOfResults)) {
183 results.insertFileID(res.getString(1), res.getTimestamp(2));
184 i++;
185 }
186
187 if(maxNumberOfResults != null && i >= maxNumberOfResults) {
188 results.reportMoreEntriesFound();
189 }
190 } finally {
191 if(res != null) {
192 res.close();
193 }
194 if(ps != null) {
195 ps.close();
196 }
197 if(conn != null) {
198 conn.close();
199 }
200 }
201 } catch (SQLException e) {
202 throw new IllegalStateException("Cannot extract the file ids with the arguments, minTimestamp = '"
203 + minTimeStamp + "', maxTimestamp = '"+ maxTimeStamp + "', maxNumberOfResults = '"
204 + maxNumberOfResults + "'", e);
205 }
206
207 return results;
208 }
209
210
211
212
213
214
215 public List<String> extractAllFileIDs(String collectionId) {
216 String sql = "SELECT " + CS_FILE_ID + " FROM " + CHECKSUM_TABLE + " WHERE " + CS_COLLECTION_ID + " = ?";
217 return DatabaseUtils.selectStringList(connector, sql, collectionId);
218 }
219
220
221
222
223
224
225
226
227
228
229 public ExtractedChecksumResultSet extractEntries(XMLGregorianCalendar minTimeStamp,
230 XMLGregorianCalendar maxTimeStamp, Long maxNumberOfResults, String collectionId) {
231 List<Object> args = new ArrayList<Object>();
232 StringBuilder sql = new StringBuilder();
233 sql.append("SELECT " + CS_FILE_ID + " , " + CS_CHECKSUM + " , " + CS_DATE + " FROM " + CHECKSUM_TABLE
234 + " WHERE " + CS_COLLECTION_ID + " = ?");
235 args.add(collectionId);
236
237 if(minTimeStamp != null) {
238 sql.append(" AND " + CS_DATE + " >= ? ");
239 args.add(CalendarUtils.convertFromXMLGregorianCalendar(minTimeStamp));
240 }
241 if(maxTimeStamp != null) {
242 sql.append(" AND " + CS_DATE + " <= ? ");
243 args.add(CalendarUtils.convertFromXMLGregorianCalendar(maxTimeStamp));
244 }
245 sql.append(" ORDER BY " + CS_DATE + " ASC ");
246
247 ExtractedChecksumResultSet results = new ExtractedChecksumResultSet();
248 try {
249 PreparedStatement ps = null;
250 ResultSet res = null;
251 Connection conn = null;
252 try {
253 conn = connector.getConnection();
254 ps = DatabaseUtils.createPreparedStatement(conn, sql.toString(), args.toArray());
255 res = ps.executeQuery();
256
257 int i = 0;
258 while(res.next() && (maxNumberOfResults == null || i < maxNumberOfResults)) {
259 results.insertChecksumEntry(extractChecksumEntry(res));
260 i++;
261 }
262
263 if(maxNumberOfResults != null && i >= maxNumberOfResults) {
264 results.reportMoreEntriesFound();
265 }
266 } finally {
267 if(res != null) {
268 res.close();
269 }
270 if(ps != null) {
271 ps.close();
272 }
273 if(conn != null) {
274 conn.close();
275 }
276 }
277 } catch (SQLException e) {
278 throw new IllegalStateException("Cannot extract the checksum entries with the arguments, minTimestamp = '"
279 + minTimeStamp + "', maxTimestamp = '"+ maxTimeStamp + "', maxNumberOfResults = '"
280 + maxNumberOfResults + "'", e);
281 }
282
283 return results;
284 }
285
286
287
288
289
290
291
292
293
294
295
296 private ChecksumEntry extractChecksumEntry(ResultSet resSet) throws SQLException {
297 String fileId = resSet.getString(1);
298 String checksum = resSet.getString(2);
299 Date date = resSet.getTimestamp(3);
300 return new ChecksumEntry(fileId, checksum, date);
301 }
302 }