1
2 package org.galagosearch.core.store;
3
4 import java.io.IOException;
5 import java.sql.Connection;
6 import java.sql.DriverManager;
7 import java.sql.PreparedStatement;
8 import java.sql.ResultSet;
9 import java.sql.SQLException;
10 import java.sql.Statement;
11 import java.util.ArrayList;
12 import java.util.Collection;
13 import java.util.HashMap;
14 import java.util.Iterator;
15 import java.util.Map.Entry;
16 import org.galagosearch.core.parse.Document;
17 import org.galagosearch.tupleflow.Parameters;
18 import org.galagosearch.tupleflow.TupleFlowParameters;
19
20 /***
21 *
22 * @author trevor
23 */
24 public class SQLDocumentStore implements Collection<Document>, DocumentStore {
25 Connection connection;
26 PreparedStatement insertDocument;
27 PreparedStatement insertMetadata;
28 PreparedStatement selectDocument;
29 PreparedStatement selectDocumentByID;
30 PreparedStatement selectMetadata;
31 PreparedStatement insertByMetadata;
32
33 public SQLDocumentStore(TupleFlowParameters parameters) throws SQLException, ClassNotFoundException {
34 this(parameters.getXML());
35 }
36
37 public SQLDocumentStore(Parameters parameters) throws SQLException, ClassNotFoundException {
38 this(parameters.get("driverName"), parameters.get("databaseUrl"));
39 }
40
41 public SQLDocumentStore(String driverName, String databaseUrl) throws SQLException, ClassNotFoundException {
42 connection = connect(driverName, databaseUrl);
43 createStatements();
44 }
45
46 private void createStatements() throws SQLException {
47 String sql;
48 sql = "insert into documents(documentText)" +
49 " values(?)";
50 insertDocument = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
51
52 sql = "insert into documentMetadata(documentID, metadataKey, metadataValue)" +
53 " values(?, ?, ?)";
54 insertMetadata = connection.prepareStatement(sql);
55
56 sql = "select documents.documentID, documents.documentText " +
57 " from documents " +
58 " join documentMetadata on (documents.documentID = documentMetadata.documentID) " +
59 " where documentMetadata.metadataKey = ? " +
60 " and documentMetadata.metadataValue = ? ";
61 selectDocument = connection.prepareStatement(sql);
62
63 sql = "select documents.documentText " +
64 " from documents " +
65 " where documentID = ? ";
66 selectDocumentByID = connection.prepareStatement(sql);
67
68 sql = "select metadataKey, metadataValue " +
69 " from documentMetadata " +
70 " where documentID = ? ";
71
72 selectMetadata = connection.prepareStatement(sql);
73
74 sql = "insert into documentMetadata(documentID, metadataKey, metadataValue) " +
75 "select documentID, ?, ? " +
76 " from documentMetadata " +
77 " where metadataKey = ? and metadataValue = ?";
78
79 insertByMetadata = connection.prepareStatement(sql);
80 }
81
82 private static void unconditionalUpdate(Connection c, String sql) {
83 try {
84 Statement s = c.createStatement();
85 s.executeUpdate(sql);
86 s.close();
87 } catch (Exception e) {
88 }
89 }
90
91 public static Connection connect(String driverName, String databaseUrl) throws SQLException, ClassNotFoundException {
92 Class.forName(driverName);
93 return DriverManager.getConnection(databaseUrl);
94 }
95
96 public static void dropDatabase(String driverName, String databaseUrl) throws SQLException, ClassNotFoundException {
97 Connection connection = connect(driverName, databaseUrl);
98
99 unconditionalUpdate(connection, "drop table documents");
100 unconditionalUpdate(connection, "drop table documentMetadata");
101 }
102
103 public static void createDatabase(String driverName, String databaseUrl) throws SQLException, ClassNotFoundException {
104 Connection connection = connect(driverName, databaseUrl);
105
106 String documentsSql = "create table documents (" +
107 " documentID int PRIMARY KEY AUTO_INCREMENT, " +
108 " documentText LONGTEXT " +
109 ") ";
110
111 String metadataSql = "create table documentMetadata ( " +
112 " documentID int, " +
113 " metadataKey varchar(50), " +
114 " metadataValue varchar(1000) " +
115 ") ";
116
117 unconditionalUpdate(connection, documentsSql);
118 unconditionalUpdate(connection, metadataSql);
119 }
120
121 public void close() throws IOException {
122 try {
123 insertMetadata.close();
124 insertDocument.close();
125 connection.close();
126 } catch (SQLException e) {
127 throw new IOException("Caught exception while closing stuff.");
128 }
129 }
130
131 public synchronized boolean add(Document document) {
132 try {
133 insertDocument.setString(1, document.text);
134 insertDocument.executeUpdate();
135
136
137 ResultSet keySet = insertDocument.getGeneratedKeys();
138 if (!keySet.next()) {
139 return false;
140 }
141 long documentID = keySet.getInt(1);
142 keySet.close();
143
144
145 for (Entry<String, String> entry : document.metadata.entrySet()) {
146 String key = entry.getKey();
147 String value = entry.getValue();
148
149 insertMetadata.setLong(1, documentID);
150 insertMetadata.setString(2, key);
151 insertMetadata.setString(3, value);
152 insertMetadata.executeUpdate();
153 insertMetadata.clearParameters();
154 }
155 } catch (SQLException e) {
156 throw new RuntimeException("Failed to add a document to the DocumentStore", e);
157 }
158
159 return true;
160 }
161
162 public synchronized void addMetadata(
163 String oldKey, String oldValue,
164 String newKey, String newValue) throws SQLException {
165 insertByMetadata.setString(1, newKey);
166 insertByMetadata.setString(2, newValue);
167
168 insertByMetadata.setString(3, oldKey);
169 insertByMetadata.setString(4, oldValue);
170
171 insertByMetadata.execute();
172 }
173
174 public Iterator<Document> iterator() {
175 final ResultSet rs;
176 final boolean hn;
177 final Statement statement;
178
179 try {
180 String sql = "select documents.documentID " +
181 " from documents ";
182 statement = connection.createStatement();
183 rs = statement.executeQuery(sql);
184 hn = rs.next();
185
186 if (hn == false) {
187 rs.close();
188 }
189 } catch (SQLException e) {
190 throw new RuntimeException("Couldn't create an iterator for the DocumentStore", e);
191 }
192
193 return new Iterator<Document>() {
194 ResultSet resultSet = rs;
195 boolean hasNext = hn;
196
197 public boolean hasNext() {
198 return hasNext;
199 }
200
201 public Document next() {
202 if (hasNext == false) {
203 return null;
204 }
205 Document document;
206
207 try {
208 long documentID = resultSet.getLong(1);
209 document = get(documentID);
210 hasNext = resultSet.next();
211
212 if (hasNext == false) {
213 resultSet.close();
214 }
215 } catch (SQLException e) {
216 throw new RuntimeException("Couldn't get next Document from the database", e);
217 }
218
219 return document;
220 }
221
222 public void remove() {
223 throw new UnsupportedOperationException(
224 "Removing documents is not supported by the DocumentStore iterator.");
225 }
226 };
227 }
228
229 public synchronized void addMetadata(long documentID, Document document) throws SQLException {
230 selectMetadata.setLong(1, documentID);
231 document.metadata = new HashMap();
232 ResultSet metadata = selectMetadata.executeQuery();
233
234 while (metadata.next()) {
235 String key = metadata.getString(1);
236 String value = metadata.getString(2);
237 document.metadata.put(key, value);
238 }
239
240 metadata.close();
241 }
242
243 public synchronized Document get(String identifier) throws IOException {
244 try {
245 return get("identifier", identifier);
246 } catch(SQLException e) {
247 IOException exception = new IOException("Caught a SQLException");
248 exception.initCause(e);
249 throw exception;
250 }
251 }
252
253 public synchronized Document get(long documentID) throws SQLException {
254 Document result = new Document();
255 selectDocumentByID.setLong(1, documentID);
256 ResultSet document = selectDocumentByID.executeQuery();
257 String text;
258
259 if (document.next()) {
260 text = document.getString(1);
261
262 result.text = text;
263 document.close();
264 } else {
265 document.close();
266 return null;
267 }
268
269 addMetadata(documentID, result);
270 return result;
271 }
272
273 public synchronized Document get(String metadataKey, String metadataValue) throws SQLException {
274 Document result = new Document();
275
276 selectDocument.setString(1, metadataKey);
277 selectDocument.setString(2, metadataValue);
278 ResultSet document = selectDocument.executeQuery();
279
280 long documentID;
281 String text;
282
283 if (document.next()) {
284 documentID = document.getLong(1);
285 text = document.getString(2);
286
287 result.text = text;
288 document.close();
289 } else {
290 document.close();
291 return null;
292 }
293
294 addMetadata(documentID, result);
295 return result;
296 }
297
298 public int size() {
299 ResultSet resultSet = null;
300 Statement statement = null;
301 int size = 0;
302
303 try {
304 statement = connection.createStatement();
305 String sql = "select count(*) from documents";
306 resultSet = statement.executeQuery(sql);
307
308 if (resultSet.next()) {
309 size = resultSet.getInt(1);
310 resultSet.close();
311 }
312
313 } catch (SQLException e) {
314 throw new RuntimeException("Size operation failed", e);
315 } finally {
316 try {
317 if (resultSet != null) {
318 resultSet.close();
319 }
320 if (statement != null) {
321 statement.close();
322 }
323 } catch (SQLException e) {
324
325 }
326 }
327
328 return size;
329 }
330
331 public boolean isEmpty() {
332 return size() != 0;
333 }
334
335 public boolean contains(Document d) {
336 throw new RuntimeException("contains() is not supported by SQLDocumentStore");
337 }
338
339 public Document[] toArray() {
340 return toArray(new Document[0]);
341 }
342
343 public <T> T[] toArray(T[] example) {
344 ArrayList<Document> list = new ArrayList();
345
346 for (Document d : this) {
347 list.add(d);
348 }
349
350 return list.toArray(example);
351 }
352
353 public boolean remove(Object o) {
354 throw new RuntimeException("remove() is not supported by SQLDocumentStore");
355 }
356
357 public boolean contains(Object o) {
358 throw new RuntimeException("contains() is not supported by SQLDocumentStore");
359 }
360
361 public boolean containsAll(Collection<?> documents) {
362 boolean doesContain = true;
363 for (Object d : documents) {
364 if (!contains(d)) {
365 return false;
366 }
367 }
368 return true;
369 }
370
371 public boolean addAll(Collection<? extends Document> documents) {
372 for (Object d : documents) {
373 if (d instanceof Document) {
374 add((Document) d);
375 } else {
376 return false;
377 }
378 }
379 return true;
380 }
381
382 public boolean removeAll(Collection<?> objects) {
383 for (Object o : objects) {
384 remove(o);
385 }
386 return true;
387 }
388
389 public boolean retainAll(Collection<?> objects) {
390 throw new RuntimeException("retainAll() is not supported by SQLDocumentStore");
391 }
392
393 public void clear() {
394 throw new RuntimeException("clear() is not supported by SQLDocumentStore");
395 }
396
397 public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
398 String driver = "com.mysql.jdbc.Driver";
399 String url = "jdbc:mysql:///document_store?user=root";
400
401 SQLDocumentStore.dropDatabase(driver, url);
402 SQLDocumentStore.createDatabase(driver, url);
403
404 SQLDocumentStore s = new SQLDocumentStore(driver, url);
405
406 Document document = new Document();
407 document.identifier = "WTX000-000-00";
408 document.metadata.put("identifier", document.identifier);
409 document.metadata.put("hi", "mom");
410 document.text = "hello! hello!";
411 s.add(document);
412
413 s.addMetadata("hi", "mom", "initial", "test");
414 Document d = s.get("hi", "mom");
415
416 for (Document e : s) {
417 System.out.println(e.text);
418 }
419
420 s.close();
421 }
422 }