Remove troubling line from debian/control.
3 * see AUTHORS for the list of contributors
5 * This program is free software: you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation, either version 3 of the License, or
8 * (at your option) any later version.
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
15 * You should have received a copy of the GNU General Public License
16 * along with this program. If not, see <http://www.gnu.org/licenses/>.
19 package org.sonews.storage.impl;
21 import java.sql.Connection;
22 import java.sql.DriverManager;
23 import java.sql.ResultSet;
24 import java.sql.SQLException;
25 import java.sql.Statement;
26 import java.sql.PreparedStatement;
27 import java.util.ArrayList;
28 import java.util.Enumeration;
29 import java.util.List;
30 import java.util.logging.Level;
31 import java.util.regex.Matcher;
32 import java.util.regex.Pattern;
33 import java.util.regex.PatternSyntaxException;
34 import javax.mail.Header;
35 import javax.mail.internet.MimeUtility;
36 import org.sonews.config.Config;
37 import org.sonews.util.Log;
38 import org.sonews.feed.Subscription;
39 import org.sonews.storage.Article;
40 import org.sonews.storage.ArticleHead;
41 import org.sonews.storage.Group;
42 import org.sonews.storage.Storage;
43 import org.sonews.storage.StorageBackendException;
44 import org.sonews.util.Pair;
47 * JDBCDatabase facade class.
48 * @author Christian Lins
51 // TODO: Refactor this class to reduce size (e.g. ArticleDatabase GroupDatabase)
52 public class JDBCDatabase implements Storage {
53 public static final int MAX_RESTARTS = 2;
55 protected Connection conn = null;
56 protected PreparedStatement pstmtAddArticle1 = null;
57 protected PreparedStatement pstmtAddArticle2 = null;
58 protected PreparedStatement pstmtAddArticle3 = null;
59 protected PreparedStatement pstmtAddArticle4 = null;
60 protected PreparedStatement pstmtAddGroup0 = null;
61 protected PreparedStatement pstmtAddEvent = null;
62 protected PreparedStatement pstmtCountArticles = null;
63 protected PreparedStatement pstmtCountGroups = null;
64 protected PreparedStatement pstmtDeleteArticle0 = null;
65 protected PreparedStatement pstmtDeleteArticle1 = null;
66 protected PreparedStatement pstmtDeleteArticle2 = null;
67 protected PreparedStatement pstmtDeleteArticle3 = null;
68 protected PreparedStatement pstmtGetArticle0 = null;
69 protected PreparedStatement pstmtGetArticle1 = null;
70 protected PreparedStatement pstmtGetArticleHeaders0 = null;
71 protected PreparedStatement pstmtGetArticleHeaders1 = null;
72 protected PreparedStatement pstmtGetArticleHeads = null;
73 protected PreparedStatement pstmtGetArticleIDs = null;
74 protected PreparedStatement pstmtGetArticleIndex = null;
75 protected PreparedStatement pstmtGetConfigValue = null;
76 protected PreparedStatement pstmtGetEventsCount0 = null;
77 protected PreparedStatement pstmtGetEventsCount1 = null;
78 protected PreparedStatement pstmtGetGroupForList = null;
79 protected PreparedStatement pstmtGetGroup0 = null;
80 protected PreparedStatement pstmtGetGroup1 = null;
81 protected PreparedStatement pstmtGetFirstArticleNumber = null;
82 protected PreparedStatement pstmtGetListForGroup = null;
83 protected PreparedStatement pstmtGetLastArticleNumber = null;
84 protected PreparedStatement pstmtGetMaxArticleID = null;
85 protected PreparedStatement pstmtGetMaxArticleIndex = null;
86 protected PreparedStatement pstmtGetOldestArticle = null;
87 protected PreparedStatement pstmtGetPostingsCount = null;
88 protected PreparedStatement pstmtGetSubscriptions = null;
89 protected PreparedStatement pstmtIsArticleExisting = null;
90 protected PreparedStatement pstmtIsGroupExisting = null;
91 protected PreparedStatement pstmtPurgeGroup0 = null;
92 protected PreparedStatement pstmtPurgeGroup1 = null;
93 protected PreparedStatement pstmtSetConfigValue0 = null;
94 protected PreparedStatement pstmtSetConfigValue1 = null;
95 protected PreparedStatement pstmtUpdateGroup = null;
96 /** How many times the database connection was reinitialized */
97 protected int restarts = 0;
99 protected void prepareAddGroupStatement() throws SQLException {
100 this.pstmtAddGroup0 = conn.prepareStatement(
101 "INSERT INTO groups (name, flags) VALUES (?, ?)");
104 protected void prepareCountGroupsStatement() throws SQLException {
105 this.pstmtCountGroups = conn.prepareStatement(
106 "SELECT Count(group_id) FROM groups WHERE "
107 + "flags & " + Group.DELETED + " = 0");
110 protected void prepareGetPostingsCountStatement() throws SQLException {
111 this.pstmtGetPostingsCount = conn.prepareStatement(
112 "SELECT Count(*) FROM postings NATURAL JOIN groups "
113 + "WHERE groups.name = ?");
116 protected void prepareGetSubscriptionsStatement() throws SQLException {
117 this.pstmtGetSubscriptions = conn.prepareStatement(
118 "SELECT host, port, name FROM peers NATURAL JOIN "
119 + "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
123 * Rises the database: reconnect and recreate all prepared statements.
124 * @throws java.lang.SQLException
126 protected void arise()
130 // Load database driver
132 Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object"));
134 // Establish database connection
135 this.conn = DriverManager.getConnection(
136 Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>"),
137 Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root"),
138 Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, ""));
140 this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
141 if (this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {
142 Log.get().warning("Database is NOT fully serializable!");
145 // Prepare statements for method addArticle()
146 this.pstmtAddArticle1 = conn.prepareStatement(
147 "INSERT INTO articles (article_id, body) VALUES(?, ?)");
148 this.pstmtAddArticle2 = conn.prepareStatement(
149 "INSERT INTO headers (article_id, header_key, header_value, header_index) "
150 + "VALUES (?, ?, ?, ?)");
151 this.pstmtAddArticle3 = conn.prepareStatement(
152 "INSERT INTO postings (group_id, article_id, article_index)"
153 + "VALUES (?, ?, ?)");
154 this.pstmtAddArticle4 = conn.prepareStatement(
155 "INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");
157 // Prepare statement for method addStatValue()
158 this.pstmtAddEvent = conn.prepareStatement(
159 "INSERT INTO events VALUES (?, ?, ?)");
161 // Prepare statement for method addGroup()
162 prepareAddGroupStatement();
164 // Prepare statement for method countArticles()
165 this.pstmtCountArticles = conn.prepareStatement(
166 "SELECT Count(article_id) FROM article_ids");
168 // Prepare statement for method countGroups()
169 prepareCountGroupsStatement();
171 // Prepare statements for method delete(article)
172 this.pstmtDeleteArticle0 = conn.prepareStatement(
173 "DELETE FROM articles WHERE article_id = "
174 + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
175 this.pstmtDeleteArticle1 = conn.prepareStatement(
176 "DELETE FROM headers WHERE article_id = "
177 + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
178 this.pstmtDeleteArticle2 = conn.prepareStatement(
179 "DELETE FROM postings WHERE article_id = "
180 + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
181 this.pstmtDeleteArticle3 = conn.prepareStatement(
182 "DELETE FROM article_ids WHERE message_id = ?");
184 // Prepare statements for methods getArticle()
185 this.pstmtGetArticle0 = conn.prepareStatement(
186 "SELECT * FROM articles WHERE article_id = "
187 + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
188 this.pstmtGetArticle1 = conn.prepareStatement(
189 "SELECT * FROM articles WHERE article_id = "
190 + "(SELECT article_id FROM postings WHERE "
191 + "article_index = ? AND group_id = ?)");
193 // Prepare statement for method getArticleHeaders()
194 this.pstmtGetArticleHeaders0 = conn.prepareStatement(
195 "SELECT header_key, header_value FROM headers WHERE article_id = ? "
196 + "ORDER BY header_index ASC");
198 // Prepare statement for method getArticleHeaders(regular expr pattern)
199 this.pstmtGetArticleHeaders1 = conn.prepareStatement(
200 "SELECT p.article_index, h.header_value FROM headers h "
201 + "INNER JOIN postings p ON h.article_id = p.article_id "
202 + "INNER JOIN groups g ON p.group_id = g.group_id "
203 + "WHERE g.name = ? AND "
204 + "h.header_key = ? AND "
205 + "p.article_index >= ? "
206 + "ORDER BY p.article_index ASC");
208 this.pstmtGetArticleIDs = conn.prepareStatement(
209 "SELECT article_index FROM postings WHERE group_id = ?");
211 // Prepare statement for method getArticleIndex
212 this.pstmtGetArticleIndex = conn.prepareStatement(
213 "SELECT article_index FROM postings WHERE "
214 + "article_id = (SELECT article_id FROM article_ids "
215 + "WHERE message_id = ?) "
216 + " AND group_id = ?");
218 // Prepare statements for method getArticleHeads()
219 this.pstmtGetArticleHeads = conn.prepareStatement(
220 "SELECT article_id, article_index FROM postings WHERE "
221 + "postings.group_id = ? AND article_index >= ? AND "
222 + "article_index <= ?");
224 // Prepare statements for method getConfigValue()
225 this.pstmtGetConfigValue = conn.prepareStatement(
226 "SELECT config_value FROM config WHERE config_key = ?");
228 // Prepare statements for method getEventsCount()
229 this.pstmtGetEventsCount0 = conn.prepareStatement(
230 "SELECT Count(*) FROM events WHERE event_key = ? AND "
231 + "event_time >= ? AND event_time < ?");
233 this.pstmtGetEventsCount1 = conn.prepareStatement(
234 "SELECT Count(*) FROM events WHERE event_key = ? AND "
235 + "event_time >= ? AND event_time < ? AND group_id = ?");
237 // Prepare statement for method getGroupForList()
238 this.pstmtGetGroupForList = conn.prepareStatement(
239 "SELECT name FROM groups INNER JOIN groups2list "
240 + "ON groups.group_id = groups2list.group_id "
241 + "WHERE groups2list.listaddress = ?");
243 // Prepare statement for method getGroup()
244 this.pstmtGetGroup0 = conn.prepareStatement(
245 "SELECT group_id, flags FROM groups WHERE Name = ?");
246 this.pstmtGetGroup1 = conn.prepareStatement(
247 "SELECT name FROM groups WHERE group_id = ?");
249 // Prepare statement for method getLastArticleNumber()
250 this.pstmtGetLastArticleNumber = conn.prepareStatement(
251 "SELECT Max(article_index) FROM postings WHERE group_id = ?");
253 // Prepare statement for method getListForGroup()
254 this.pstmtGetListForGroup = conn.prepareStatement(
255 "SELECT listaddress FROM groups2list INNER JOIN groups "
256 + "ON groups.group_id = groups2list.group_id WHERE name = ?");
258 // Prepare statement for method getMaxArticleID()
259 this.pstmtGetMaxArticleID = conn.prepareStatement(
260 "SELECT Max(article_id) FROM articles");
262 // Prepare statement for method getMaxArticleIndex()
263 this.pstmtGetMaxArticleIndex = conn.prepareStatement(
264 "SELECT Max(article_index) FROM postings WHERE group_id = ?");
266 // Prepare statement for method getOldestArticle()
267 this.pstmtGetOldestArticle = conn.prepareStatement(
268 "SELECT message_id FROM article_ids WHERE article_id = "
269 + "(SELECT Min(article_id) FROM article_ids)");
271 // Prepare statement for method getFirstArticleNumber()
272 this.pstmtGetFirstArticleNumber = conn.prepareStatement(
273 "SELECT Min(article_index) FROM postings WHERE group_id = ?");
275 // Prepare statement for method getPostingsCount()
276 prepareGetPostingsCountStatement();
278 // Prepare statement for method getSubscriptions()
279 prepareGetSubscriptionsStatement();
281 // Prepare statement for method isArticleExisting()
282 this.pstmtIsArticleExisting = conn.prepareStatement(
283 "SELECT Count(article_id) FROM article_ids WHERE message_id = ?");
285 // Prepare statement for method isGroupExisting()
286 this.pstmtIsGroupExisting = conn.prepareStatement(
287 "SELECT * FROM groups WHERE name = ?");
289 // Prepare statement for method setConfigValue()
290 this.pstmtSetConfigValue0 = conn.prepareStatement(
291 "DELETE FROM config WHERE config_key = ?");
292 this.pstmtSetConfigValue1 = conn.prepareStatement(
293 "INSERT INTO config VALUES(?, ?)");
295 // Prepare statements for method purgeGroup()
296 this.pstmtPurgeGroup0 = conn.prepareStatement(
297 "DELETE FROM peer_subscriptions WHERE group_id = ?");
298 this.pstmtPurgeGroup1 = conn.prepareStatement(
299 "DELETE FROM groups WHERE group_id = ?");
301 // Prepare statement for method update(Group)
302 this.pstmtUpdateGroup = conn.prepareStatement(
303 "UPDATE groups SET flags = ?, name = ? WHERE group_id = ?");
304 } catch (ClassNotFoundException ex) {
305 throw new Error("JDBC Driver not found!", ex);
310 * Adds an article to the database.
313 * @throws java.sql.SQLException
316 public void addArticle(final Article article)
317 throws StorageBackendException
320 this.conn.setAutoCommit(false);
322 int newArticleID = getMaxArticleID() + 1;
323 addArticle(article, newArticleID);
325 this.conn.setAutoCommit(true);
327 this.restarts = 0; // Reset error count
328 } catch (SQLException ex) {
330 this.conn.rollback(); // Rollback changes
331 } catch (SQLException ex2) {
332 Log.get().severe("Rollback of addArticle() failed: " + ex2);
336 this.conn.setAutoCommit(true); // and release locks
337 } catch (SQLException ex2) {
338 Log.get().severe("setAutoCommit(true) of addArticle() failed: " + ex2);
341 restartConnection(ex);
347 * Adds an article to the database.
350 * @throws java.sql.SQLException
352 void addArticle(final Article article, final int newArticleID)
353 throws SQLException, StorageBackendException
355 // Fill prepared statement with values;
356 // writes body to article table
357 pstmtAddArticle1.setInt(1, newArticleID);
358 pstmtAddArticle1.setBytes(2, article.getBody());
359 pstmtAddArticle1.execute();
362 Enumeration headers = article.getAllHeaders();
363 for (int n = 0; headers.hasMoreElements(); n++) {
364 Header header = (Header) headers.nextElement();
365 pstmtAddArticle2.setInt(1, newArticleID);
366 pstmtAddArticle2.setString(2, header.getName().toLowerCase());
367 pstmtAddArticle2.setString(3,
368 header.getValue().replaceAll("[\r\n]", ""));
369 pstmtAddArticle2.setInt(4, n);
370 pstmtAddArticle2.execute();
373 // For each newsgroup add a reference
374 List<Group> groups = article.getGroups();
375 for (Group group : groups) {
376 pstmtAddArticle3.setLong(1, group.getInternalID());
377 pstmtAddArticle3.setInt(2, newArticleID);
378 pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getInternalID()) + 1);
379 pstmtAddArticle3.execute();
382 // Write message-id to article_ids table
383 this.pstmtAddArticle4.setInt(1, newArticleID);
384 this.pstmtAddArticle4.setString(2, article.getMessageID());
385 this.pstmtAddArticle4.execute();
389 * Adds a group to the JDBCDatabase. This method is not accessible via NNTP.
391 * @throws java.sql.SQLException
394 public void addGroup(String name, int flags)
395 throws StorageBackendException
398 this.conn.setAutoCommit(false);
399 pstmtAddGroup0.setString(1, name);
400 pstmtAddGroup0.setInt(2, flags);
402 pstmtAddGroup0.executeUpdate();
404 this.conn.setAutoCommit(true);
405 this.restarts = 0; // Reset error count
406 } catch (SQLException ex) {
408 this.conn.rollback();
409 this.conn.setAutoCommit(true);
410 } catch (SQLException ex2) {
411 ex2.printStackTrace();
414 restartConnection(ex);
415 addGroup(name, flags);
420 public void addEvent(long time, int type, long gid)
421 throws StorageBackendException
424 this.conn.setAutoCommit(false);
425 this.pstmtAddEvent.setLong(1, time);
426 this.pstmtAddEvent.setInt(2, type);
427 this.pstmtAddEvent.setLong(3, gid);
428 this.pstmtAddEvent.executeUpdate();
430 this.conn.setAutoCommit(true);
432 } catch (SQLException ex) {
434 this.conn.rollback();
435 this.conn.setAutoCommit(true);
436 } catch (SQLException ex2) {
437 ex2.printStackTrace();
440 restartConnection(ex);
441 addEvent(time, type, gid);
446 public int countArticles()
447 throws StorageBackendException
452 rs = this.pstmtCountArticles.executeQuery();
458 } catch (SQLException ex) {
459 restartConnection(ex);
460 return countArticles();
465 } catch (SQLException ex) {
466 ex.printStackTrace();
474 public int countGroups()
475 throws StorageBackendException
480 rs = this.pstmtCountGroups.executeQuery();
486 } catch (SQLException ex) {
487 restartConnection(ex);
488 return countGroups();
493 } catch (SQLException ex) {
494 ex.printStackTrace();
502 public void delete(final String messageID)
503 throws StorageBackendException
506 this.conn.setAutoCommit(false);
508 this.pstmtDeleteArticle0.setString(1, messageID);
509 int rs = this.pstmtDeleteArticle0.executeUpdate();
511 // We do not trust the ON DELETE CASCADE functionality to delete
512 // orphaned references...
513 this.pstmtDeleteArticle1.setString(1, messageID);
514 rs = this.pstmtDeleteArticle1.executeUpdate();
516 this.pstmtDeleteArticle2.setString(1, messageID);
517 rs = this.pstmtDeleteArticle2.executeUpdate();
519 this.pstmtDeleteArticle3.setString(1, messageID);
520 rs = this.pstmtDeleteArticle3.executeUpdate();
523 this.conn.setAutoCommit(true);
524 } catch (SQLException ex) {
525 throw new StorageBackendException(ex);
530 public Article getArticle(String messageID)
531 throws StorageBackendException
535 pstmtGetArticle0.setString(1, messageID);
536 rs = pstmtGetArticle0.executeQuery();
541 byte[] body = rs.getBytes("body");
542 String headers = getArticleHeaders(rs.getInt("article_id"));
543 return new Article(headers, body);
545 } catch (SQLException ex) {
546 restartConnection(ex);
547 return getArticle(messageID);
552 } catch (SQLException ex) {
553 ex.printStackTrace();
555 restarts = 0; // Reset error count
561 * Retrieves an article by its ID.
564 * @throws StorageBackendException
567 public Article getArticle(long articleIndex, long gid)
568 throws StorageBackendException
573 this.pstmtGetArticle1.setLong(1, articleIndex);
574 this.pstmtGetArticle1.setLong(2, gid);
576 rs = this.pstmtGetArticle1.executeQuery();
579 byte[] body = rs.getBytes("body");
580 String headers = getArticleHeaders(rs.getInt("article_id"));
581 return new Article(headers, body);
585 } catch (SQLException ex) {
586 restartConnection(ex);
587 return getArticle(articleIndex, gid);
592 } catch (SQLException ex) {
593 ex.printStackTrace();
601 * Searches for fitting header values using the given regular expression.
608 * @throws StorageBackendException
611 public List<Pair<Long, String>> getArticleHeaders(Group group, long start,
612 long end, String headerKey, String patStr)
613 throws StorageBackendException, PatternSyntaxException
616 List<Pair<Long, String>> heads = new ArrayList<Pair<Long, String>>();
619 this.pstmtGetArticleHeaders1.setString(1, group.getName());
620 this.pstmtGetArticleHeaders1.setString(2, headerKey);
621 this.pstmtGetArticleHeaders1.setLong(3, start);
623 rs = this.pstmtGetArticleHeaders1.executeQuery();
625 // Convert the "NNTP" regex to Java regex
626 patStr = patStr.replace("*", ".*");
627 Pattern pattern = Pattern.compile(patStr);
630 Long articleIndex = rs.getLong(1);
631 if (end < 0 || articleIndex <= end) // Match start is done via SQL
633 String headerValue = rs.getString(2);
634 Matcher matcher = pattern.matcher(headerValue);
635 if (matcher.matches()) {
636 heads.add(new Pair<Long, String>(articleIndex, headerValue));
640 } catch (SQLException ex) {
641 restartConnection(ex);
642 return getArticleHeaders(group, start, end, headerKey, patStr);
647 } catch (SQLException ex) {
648 ex.printStackTrace();
656 private String getArticleHeaders(long articleID)
657 throws StorageBackendException
662 this.pstmtGetArticleHeaders0.setLong(1, articleID);
663 rs = this.pstmtGetArticleHeaders0.executeQuery();
665 StringBuilder buf = new StringBuilder();
668 buf.append(rs.getString(1)); // key
670 String foldedValue = MimeUtility.fold(0, rs.getString(2));
671 buf.append(foldedValue); // value
680 return buf.toString();
681 } catch (SQLException ex) {
682 restartConnection(ex);
683 return getArticleHeaders(articleID);
688 } catch (SQLException ex) {
689 ex.printStackTrace();
696 public long getArticleIndex(Article article, Group group)
697 throws StorageBackendException
702 this.pstmtGetArticleIndex.setString(1, article.getMessageID());
703 this.pstmtGetArticleIndex.setLong(2, group.getInternalID());
705 rs = this.pstmtGetArticleIndex.executeQuery();
707 return rs.getLong(1);
711 } catch (SQLException ex) {
712 restartConnection(ex);
713 return getArticleIndex(article, group);
718 } catch (SQLException ex) {
719 ex.printStackTrace();
726 * Returns a list of Long/Article Pairs.
727 * @throws java.sql.SQLException
730 public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first,
732 throws StorageBackendException
737 this.pstmtGetArticleHeads.setLong(1, group.getInternalID());
738 this.pstmtGetArticleHeads.setLong(2, first);
739 this.pstmtGetArticleHeads.setLong(3, last);
740 rs = pstmtGetArticleHeads.executeQuery();
742 List<Pair<Long, ArticleHead>> articles = new ArrayList<Pair<Long, ArticleHead>>();
745 long aid = rs.getLong("article_id");
746 long aidx = rs.getLong("article_index");
747 String headers = getArticleHeaders(aid);
748 articles.add(new Pair<Long, ArticleHead>(aidx,
749 new ArticleHead(headers)));
753 } catch (SQLException ex) {
754 restartConnection(ex);
755 return getArticleHeads(group, first, last);
760 } catch (SQLException ex) {
761 ex.printStackTrace();
768 public List<Long> getArticleNumbers(long gid)
769 throws StorageBackendException
773 List<Long> ids = new ArrayList<Long>();
774 this.pstmtGetArticleIDs.setLong(1, gid);
775 rs = this.pstmtGetArticleIDs.executeQuery();
777 ids.add(rs.getLong(1));
780 } catch (SQLException ex) {
781 restartConnection(ex);
782 return getArticleNumbers(gid);
787 restarts = 0; // Clear the restart count after successful request
788 } catch (SQLException ex) {
789 ex.printStackTrace();
796 public String getConfigValue(String key)
797 throws StorageBackendException
801 this.pstmtGetConfigValue.setString(1, key);
803 rs = this.pstmtGetConfigValue.executeQuery();
805 return rs.getString(1); // First data on index 1 not 0
809 } catch (SQLException ex) {
810 restartConnection(ex);
811 return getConfigValue(key);
816 } catch (SQLException ex) {
817 ex.printStackTrace();
819 restarts = 0; // Clear the restart count after successful request
825 public int getEventsCount(int type, long start, long end, Group channel)
826 throws StorageBackendException
831 if (channel == null) {
832 this.pstmtGetEventsCount0.setInt(1, type);
833 this.pstmtGetEventsCount0.setLong(2, start);
834 this.pstmtGetEventsCount0.setLong(3, end);
835 rs = this.pstmtGetEventsCount0.executeQuery();
837 this.pstmtGetEventsCount1.setInt(1, type);
838 this.pstmtGetEventsCount1.setLong(2, start);
839 this.pstmtGetEventsCount1.setLong(3, end);
840 this.pstmtGetEventsCount1.setLong(4, channel.getInternalID());
841 rs = this.pstmtGetEventsCount1.executeQuery();
849 } catch (SQLException ex) {
850 restartConnection(ex);
851 return getEventsCount(type, start, end, channel);
856 } catch (SQLException ex) {
857 ex.printStackTrace();
864 * Reads all Groups from the JDBCDatabase.
866 * @throws StorageBackendException
869 public List<Group> getGroups()
870 throws StorageBackendException
873 List<Group> buffer = new ArrayList<Group>();
874 Statement stmt = null;
877 stmt = conn.createStatement();
878 rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
881 String name = rs.getString("name");
882 long id = rs.getLong("group_id");
883 int flags = rs.getInt("flags");
885 Group group = new Group(name, id, flags);
890 } catch (SQLException ex) {
891 restartConnection(ex);
896 stmt.close(); // Implicitely closes ResultSets
897 } catch (SQLException ex) {
898 ex.printStackTrace();
905 public List<String> getGroupsForList(String listAddress)
906 throws StorageBackendException
911 this.pstmtGetGroupForList.setString(1, listAddress);
913 rs = this.pstmtGetGroupForList.executeQuery();
914 List<String> groups = new ArrayList<String>();
916 String group = rs.getString(1);
920 } catch (SQLException ex) {
921 restartConnection(ex);
922 return getGroupsForList(listAddress);
927 } catch (SQLException ex) {
928 ex.printStackTrace();
935 * Returns the Group that is identified by the name.
938 * @throws StorageBackendException
941 public Group getGroup(String name)
942 throws StorageBackendException
947 this.pstmtGetGroup0.setString(1, name);
948 rs = this.pstmtGetGroup0.executeQuery();
953 long id = rs.getLong("group_id");
954 int flags = rs.getInt("flags");
955 return new Group(name, id, flags);
957 } catch (SQLException ex) {
958 restartConnection(ex);
959 return getGroup(name);
964 } catch (SQLException ex) {
965 ex.printStackTrace();
972 public List<String> getListsForGroup(String group)
973 throws StorageBackendException
976 List<String> lists = new ArrayList<String>();
979 this.pstmtGetListForGroup.setString(1, group);
980 rs = this.pstmtGetListForGroup.executeQuery();
983 lists.add(rs.getString(1));
986 } catch (SQLException ex) {
987 restartConnection(ex);
988 return getListsForGroup(group);
993 } catch (SQLException ex) {
994 ex.printStackTrace();
1000 private int getMaxArticleIndex(long groupID)
1001 throws StorageBackendException
1003 ResultSet rs = null;
1006 this.pstmtGetMaxArticleIndex.setLong(1, groupID);
1007 rs = this.pstmtGetMaxArticleIndex.executeQuery();
1011 maxIndex = rs.getInt(1);
1015 } catch (SQLException ex) {
1016 restartConnection(ex);
1017 return getMaxArticleIndex(groupID);
1022 } catch (SQLException ex) {
1023 ex.printStackTrace();
1029 private int getMaxArticleID()
1030 throws StorageBackendException
1032 ResultSet rs = null;
1035 rs = this.pstmtGetMaxArticleID.executeQuery();
1039 maxIndex = rs.getInt(1);
1043 } catch (SQLException ex) {
1044 restartConnection(ex);
1045 return getMaxArticleID();
1050 } catch (SQLException ex) {
1051 ex.printStackTrace();
1058 public int getLastArticleNumber(Group group)
1059 throws StorageBackendException
1061 ResultSet rs = null;
1064 this.pstmtGetLastArticleNumber.setLong(1, group.getInternalID());
1065 rs = this.pstmtGetLastArticleNumber.executeQuery();
1067 return rs.getInt(1);
1071 } catch (SQLException ex) {
1072 restartConnection(ex);
1073 return getLastArticleNumber(group);
1078 } catch (SQLException ex) {
1079 ex.printStackTrace();
1086 public int getFirstArticleNumber(Group group)
1087 throws StorageBackendException
1089 ResultSet rs = null;
1091 this.pstmtGetFirstArticleNumber.setLong(1, group.getInternalID());
1092 rs = this.pstmtGetFirstArticleNumber.executeQuery();
1094 return rs.getInt(1);
1098 } catch (SQLException ex) {
1099 restartConnection(ex);
1100 return getFirstArticleNumber(group);
1105 } catch (SQLException ex) {
1106 ex.printStackTrace();
1113 * Returns a group name identified by the given id.
1116 * @throws StorageBackendException
1118 public String getGroup(int id)
1119 throws StorageBackendException
1121 ResultSet rs = null;
1124 this.pstmtGetGroup1.setInt(1, id);
1125 rs = this.pstmtGetGroup1.executeQuery();
1128 return rs.getString(1);
1132 } catch (SQLException ex) {
1133 restartConnection(ex);
1134 return getGroup(id);
1139 } catch (SQLException ex) {
1140 ex.printStackTrace();
1147 public double getEventsPerHour(int key, long gid)
1148 throws StorageBackendException
1150 String gidquery = "";
1152 gidquery = " AND group_id = " + gid;
1155 Statement stmt = null;
1156 ResultSet rs = null;
1159 stmt = this.conn.createStatement();
1160 rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))"
1161 + " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
1164 restarts = 0; // reset error count
1165 return rs.getDouble(1);
1169 } catch (SQLException ex) {
1170 restartConnection(ex);
1171 return getEventsPerHour(key, gid);
1175 stmt.close(); // Implicitely closes the result sets
1177 } catch (SQLException ex) {
1178 ex.printStackTrace();
1184 public String getOldestArticle()
1185 throws StorageBackendException
1187 ResultSet rs = null;
1190 rs = this.pstmtGetOldestArticle.executeQuery();
1192 return rs.getString(1);
1196 } catch (SQLException ex) {
1197 restartConnection(ex);
1198 return getOldestArticle();
1203 } catch (SQLException ex) {
1204 ex.printStackTrace();
1211 public int getPostingsCount(String groupname)
1212 throws StorageBackendException
1214 ResultSet rs = null;
1217 this.pstmtGetPostingsCount.setString(1, groupname);
1218 rs = this.pstmtGetPostingsCount.executeQuery();
1220 return rs.getInt(1);
1222 Log.get().warning("Count on postings return nothing!");
1225 } catch (SQLException ex) {
1226 restartConnection(ex);
1227 return getPostingsCount(groupname);
1232 } catch (SQLException ex) {
1233 ex.printStackTrace();
1240 public List<Subscription> getSubscriptions(int feedtype)
1241 throws StorageBackendException
1243 ResultSet rs = null;
1246 List<Subscription> subs = new ArrayList<Subscription>();
1247 this.pstmtGetSubscriptions.setInt(1, feedtype);
1248 rs = this.pstmtGetSubscriptions.executeQuery();
1251 String host = rs.getString("host");
1252 String group = rs.getString("name");
1253 int port = rs.getInt("port");
1254 subs.add(new Subscription(host, port, feedtype, group));
1258 } catch (SQLException ex) {
1259 restartConnection(ex);
1260 return getSubscriptions(feedtype);
1265 } catch (SQLException ex) {
1266 ex.printStackTrace();
1273 * Checks if there is an article with the given messageid in the JDBCDatabase.
1276 * @throws StorageBackendException
1279 public boolean isArticleExisting(String messageID)
1280 throws StorageBackendException
1282 ResultSet rs = null;
1285 this.pstmtIsArticleExisting.setString(1, messageID);
1286 rs = this.pstmtIsArticleExisting.executeQuery();
1287 return rs.next() && rs.getInt(1) == 1;
1288 } catch (SQLException ex) {
1289 restartConnection(ex);
1290 return isArticleExisting(messageID);
1295 } catch (SQLException ex) {
1296 ex.printStackTrace();
1303 * Checks if there is a group with the given name in the JDBCDatabase.
1306 * @throws StorageBackendException
1309 public boolean isGroupExisting(String name)
1310 throws StorageBackendException
1312 ResultSet rs = null;
1315 this.pstmtIsGroupExisting.setString(1, name);
1316 rs = this.pstmtIsGroupExisting.executeQuery();
1318 } catch (SQLException ex) {
1319 restartConnection(ex);
1320 return isGroupExisting(name);
1325 } catch (SQLException ex) {
1326 ex.printStackTrace();
1333 public void setConfigValue(String key, String value)
1334 throws StorageBackendException
1337 conn.setAutoCommit(false);
1338 this.pstmtSetConfigValue0.setString(1, key);
1339 this.pstmtSetConfigValue0.execute();
1340 this.pstmtSetConfigValue1.setString(1, key);
1341 this.pstmtSetConfigValue1.setString(2, value);
1342 this.pstmtSetConfigValue1.execute();
1344 conn.setAutoCommit(true);
1345 } catch (SQLException ex) {
1346 restartConnection(ex);
1347 setConfigValue(key, value);
1352 * Closes the JDBCDatabase connection.
1354 public void shutdown()
1355 throws StorageBackendException
1358 if (this.conn != null) {
1361 } catch (SQLException ex) {
1362 throw new StorageBackendException(ex);
1367 public void purgeGroup(Group group)
1368 throws StorageBackendException
1371 this.pstmtPurgeGroup0.setLong(1, group.getInternalID());
1372 this.pstmtPurgeGroup0.executeUpdate();
1374 this.pstmtPurgeGroup1.setLong(1, group.getInternalID());
1375 this.pstmtPurgeGroup1.executeUpdate();
1376 } catch (SQLException ex) {
1377 restartConnection(ex);
1382 protected void restartConnection(SQLException cause)
1383 throws StorageBackendException
1386 Log.get().log(Level.SEVERE, Thread.currentThread()
1387 + ": Database connection was closed (restart " + restarts + ").", cause);
1389 if (restarts >= MAX_RESTARTS) {
1390 // Delete the current, probably broken JDBCDatabase instance.
1391 // So no one can use the instance any more.
1392 JDBCDatabaseProvider.instances.remove(Thread.currentThread());
1394 // Throw the exception upwards
1395 throw new StorageBackendException(cause);
1399 Thread.sleep(1500L * restarts);
1400 } catch (InterruptedException ex) {
1401 Log.get().warning("Interrupted: " + ex.getMessage());
1404 // Try to properly close the old database connection
1406 if (this.conn != null) {
1409 } catch (SQLException ex) {
1410 Log.get().warning(ex.getMessage());
1414 // Try to reinitialize database connection
1416 } catch (SQLException ex) {
1417 Log.get().warning(ex.getMessage());
1418 restartConnection(ex);
1423 public boolean update(Article article)
1424 throws StorageBackendException
1426 ResultSet rs = null;
1428 // Retrieve internal article_id
1429 this.pstmtGetArticle0.setString(1, article.getMessageID());
1430 rs = this.pstmtGetArticle0.executeQuery();
1431 int articleID = rs.getInt("article_id");
1433 delete(article.getMessageID());
1435 this.conn.setAutoCommit(false);
1436 addArticle(article, articleID);
1438 this.conn.setAutoCommit(true);
1440 } catch (SQLException ex) {
1442 this.conn.rollback();
1443 } catch(SQLException ex2) {
1444 Log.get().severe("Rollback failed: " + ex2.getMessage());
1446 restartConnection(ex);
1447 return update(article);
1452 * Writes the flags and the name of the given group to the database.
1454 * @throws StorageBackendException
1457 public boolean update(Group group)
1458 throws StorageBackendException
1461 this.pstmtUpdateGroup.setInt(1, group.getFlags());
1462 this.pstmtUpdateGroup.setString(2, group.getName());
1463 this.pstmtUpdateGroup.setLong(3, group.getInternalID());
1464 int rs = this.pstmtUpdateGroup.executeUpdate();
1466 } catch (SQLException ex) {
1467 restartConnection(ex);
1468 return update(group);