diff -r 146b3275b792 -r c404a87db5b7 src/org/sonews/storage/impl/JDBCDatabase.java
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/src/org/sonews/storage/impl/JDBCDatabase.java Sun Aug 29 17:43:58 2010 +0200
@@ -0,0 +1,1782 @@
+/*
+ * SONEWS News Server
+ * see AUTHORS for the list of contributors
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation, either version 3 of the License, or
+ * (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program. If not, see .
+ */
+
+package org.sonews.storage.impl;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.PreparedStatement;
+import java.util.ArrayList;
+import java.util.Enumeration;
+import java.util.List;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import java.util.regex.PatternSyntaxException;
+import javax.mail.Header;
+import javax.mail.internet.MimeUtility;
+import org.sonews.config.Config;
+import org.sonews.util.Log;
+import org.sonews.feed.Subscription;
+import org.sonews.storage.Article;
+import org.sonews.storage.ArticleHead;
+import org.sonews.storage.Channel;
+import org.sonews.storage.Group;
+import org.sonews.storage.Storage;
+import org.sonews.storage.StorageBackendException;
+import org.sonews.util.Pair;
+
+/**
+ * JDBCDatabase facade class.
+ * @author Christian Lins
+ * @since sonews/0.5.0
+ */
+// TODO: Refactor this class to reduce size (e.g. ArticleDatabase GroupDatabase)
+public class JDBCDatabase implements Storage
+{
+
+ public static final int MAX_RESTARTS = 2;
+
+ private Connection conn = null;
+ private PreparedStatement pstmtAddArticle1 = null;
+ private PreparedStatement pstmtAddArticle2 = null;
+ private PreparedStatement pstmtAddArticle3 = null;
+ private PreparedStatement pstmtAddArticle4 = null;
+ private PreparedStatement pstmtAddGroup0 = null;
+ private PreparedStatement pstmtAddEvent = null;
+ private PreparedStatement pstmtCountArticles = null;
+ private PreparedStatement pstmtCountGroups = null;
+ private PreparedStatement pstmtDeleteArticle0 = null;
+ private PreparedStatement pstmtDeleteArticle1 = null;
+ private PreparedStatement pstmtDeleteArticle2 = null;
+ private PreparedStatement pstmtDeleteArticle3 = null;
+ private PreparedStatement pstmtGetArticle0 = null;
+ private PreparedStatement pstmtGetArticle1 = null;
+ private PreparedStatement pstmtGetArticleHeaders0 = null;
+ private PreparedStatement pstmtGetArticleHeaders1 = null;
+ private PreparedStatement pstmtGetArticleHeads = null;
+ private PreparedStatement pstmtGetArticleIDs = null;
+ private PreparedStatement pstmtGetArticleIndex = null;
+ private PreparedStatement pstmtGetConfigValue = null;
+ private PreparedStatement pstmtGetEventsCount0 = null;
+ private PreparedStatement pstmtGetEventsCount1 = null;
+ private PreparedStatement pstmtGetGroupForList = null;
+ private PreparedStatement pstmtGetGroup0 = null;
+ private PreparedStatement pstmtGetGroup1 = null;
+ private PreparedStatement pstmtGetFirstArticleNumber = null;
+ private PreparedStatement pstmtGetListForGroup = null;
+ private PreparedStatement pstmtGetLastArticleNumber = null;
+ private PreparedStatement pstmtGetMaxArticleID = null;
+ private PreparedStatement pstmtGetMaxArticleIndex = null;
+ private PreparedStatement pstmtGetOldestArticle = null;
+ private PreparedStatement pstmtGetPostingsCount = null;
+ private PreparedStatement pstmtGetSubscriptions = null;
+ private PreparedStatement pstmtIsArticleExisting = null;
+ private PreparedStatement pstmtIsGroupExisting = null;
+ private PreparedStatement pstmtPurgeGroup0 = null;
+ private PreparedStatement pstmtPurgeGroup1 = null;
+ private PreparedStatement pstmtSetConfigValue0 = null;
+ private PreparedStatement pstmtSetConfigValue1 = null;
+ private PreparedStatement pstmtUpdateGroup = null;
+
+ /** How many times the database connection was reinitialized */
+ private int restarts = 0;
+
+ /**
+ * Rises the database: reconnect and recreate all prepared statements.
+ * @throws java.lang.SQLException
+ */
+ protected void arise()
+ throws SQLException
+ {
+ try
+ {
+ // Load database driver
+ Class.forName(
+ Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object"));
+
+ // Establish database connection
+ this.conn = DriverManager.getConnection(
+ Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, ""),
+ Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root"),
+ Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, ""));
+
+ this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
+ if(this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE)
+ {
+ Log.get().warning("Database is NOT fully serializable!");
+ }
+
+ // Prepare statements for method addArticle()
+ this.pstmtAddArticle1 = conn.prepareStatement(
+ "INSERT INTO articles (article_id, body) VALUES(?, ?)");
+ this.pstmtAddArticle2 = conn.prepareStatement(
+ "INSERT INTO headers (article_id, header_key, header_value, header_index) " +
+ "VALUES (?, ?, ?, ?)");
+ this.pstmtAddArticle3 = conn.prepareStatement(
+ "INSERT INTO postings (group_id, article_id, article_index)" +
+ "VALUES (?, ?, ?)");
+ this.pstmtAddArticle4 = conn.prepareStatement(
+ "INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");
+
+ // Prepare statement for method addStatValue()
+ this.pstmtAddEvent = conn.prepareStatement(
+ "INSERT INTO events VALUES (?, ?, ?)");
+
+ // Prepare statement for method addGroup()
+ this.pstmtAddGroup0 = conn.prepareStatement(
+ "INSERT INTO groups (name, flags) VALUES (?, ?)");
+
+ // Prepare statement for method countArticles()
+ this.pstmtCountArticles = conn.prepareStatement(
+ "SELECT Count(article_id) FROM article_ids");
+
+ // Prepare statement for method countGroups()
+ this.pstmtCountGroups = conn.prepareStatement(
+ "SELECT Count(group_id) FROM groups WHERE " +
+ "flags & " + Channel.DELETED + " = 0");
+
+ // Prepare statements for method delete(article)
+ this.pstmtDeleteArticle0 = conn.prepareStatement(
+ "DELETE FROM articles WHERE article_id = " +
+ "(SELECT article_id FROM article_ids WHERE message_id = ?)");
+ this.pstmtDeleteArticle1 = conn.prepareStatement(
+ "DELETE FROM headers WHERE article_id = " +
+ "(SELECT article_id FROM article_ids WHERE message_id = ?)");
+ this.pstmtDeleteArticle2 = conn.prepareStatement(
+ "DELETE FROM postings WHERE article_id = " +
+ "(SELECT article_id FROM article_ids WHERE message_id = ?)");
+ this.pstmtDeleteArticle3 = conn.prepareStatement(
+ "DELETE FROM article_ids WHERE message_id = ?");
+
+ // Prepare statements for methods getArticle()
+ this.pstmtGetArticle0 = conn.prepareStatement(
+ "SELECT * FROM articles WHERE article_id = " +
+ "(SELECT article_id FROM article_ids WHERE message_id = ?)");
+ this.pstmtGetArticle1 = conn.prepareStatement(
+ "SELECT * FROM articles WHERE article_id = " +
+ "(SELECT article_id FROM postings WHERE " +
+ "article_index = ? AND group_id = ?)");
+
+ // Prepare statement for method getArticleHeaders()
+ this.pstmtGetArticleHeaders0 = conn.prepareStatement(
+ "SELECT header_key, header_value FROM headers WHERE article_id = ? " +
+ "ORDER BY header_index ASC");
+
+ // Prepare statement for method getArticleHeaders(regular expr pattern)
+ this.pstmtGetArticleHeaders1 = conn.prepareStatement(
+ "SELECT p.article_index, h.header_value FROM headers h " +
+ "INNER JOIN postings p ON h.article_id = p.article_id " +
+ "INNER JOIN groups g ON p.group_id = g.group_id " +
+ "WHERE g.name = ? AND " +
+ "h.header_key = ? AND " +
+ "p.article_index >= ? " +
+ "ORDER BY p.article_index ASC");
+
+ this.pstmtGetArticleIDs = conn.prepareStatement(
+ "SELECT article_index FROM postings WHERE group_id = ?");
+
+ // Prepare statement for method getArticleIndex
+ this.pstmtGetArticleIndex = conn.prepareStatement(
+ "SELECT article_index FROM postings WHERE " +
+ "article_id = (SELECT article_id FROM article_ids " +
+ "WHERE message_id = ?) " +
+ " AND group_id = ?");
+
+ // Prepare statements for method getArticleHeads()
+ this.pstmtGetArticleHeads = conn.prepareStatement(
+ "SELECT article_id, article_index FROM postings WHERE " +
+ "postings.group_id = ? AND article_index >= ? AND " +
+ "article_index <= ?");
+
+ // Prepare statements for method getConfigValue()
+ this.pstmtGetConfigValue = conn.prepareStatement(
+ "SELECT config_value FROM config WHERE config_key = ?");
+
+ // Prepare statements for method getEventsCount()
+ this.pstmtGetEventsCount0 = conn.prepareStatement(
+ "SELECT Count(*) FROM events WHERE event_key = ? AND " +
+ "event_time >= ? AND event_time < ?");
+
+ this.pstmtGetEventsCount1 = conn.prepareStatement(
+ "SELECT Count(*) FROM events WHERE event_key = ? AND " +
+ "event_time >= ? AND event_time < ? AND group_id = ?");
+
+ // Prepare statement for method getGroupForList()
+ this.pstmtGetGroupForList = conn.prepareStatement(
+ "SELECT name FROM groups INNER JOIN groups2list " +
+ "ON groups.group_id = groups2list.group_id " +
+ "WHERE groups2list.listaddress = ?");
+
+ // Prepare statement for method getGroup()
+ this.pstmtGetGroup0 = conn.prepareStatement(
+ "SELECT group_id, flags FROM groups WHERE Name = ?");
+ this.pstmtGetGroup1 = conn.prepareStatement(
+ "SELECT name FROM groups WHERE group_id = ?");
+
+ // Prepare statement for method getLastArticleNumber()
+ this.pstmtGetLastArticleNumber = conn.prepareStatement(
+ "SELECT Max(article_index) FROM postings WHERE group_id = ?");
+
+ // Prepare statement for method getListForGroup()
+ this.pstmtGetListForGroup = conn.prepareStatement(
+ "SELECT listaddress FROM groups2list INNER JOIN groups " +
+ "ON groups.group_id = groups2list.group_id WHERE name = ?");
+
+ // Prepare statement for method getMaxArticleID()
+ this.pstmtGetMaxArticleID = conn.prepareStatement(
+ "SELECT Max(article_id) FROM articles");
+
+ // Prepare statement for method getMaxArticleIndex()
+ this.pstmtGetMaxArticleIndex = conn.prepareStatement(
+ "SELECT Max(article_index) FROM postings WHERE group_id = ?");
+
+ // Prepare statement for method getOldestArticle()
+ this.pstmtGetOldestArticle = conn.prepareStatement(
+ "SELECT message_id FROM article_ids WHERE article_id = " +
+ "(SELECT Min(article_id) FROM article_ids)");
+
+ // Prepare statement for method getFirstArticleNumber()
+ this.pstmtGetFirstArticleNumber = conn.prepareStatement(
+ "SELECT Min(article_index) FROM postings WHERE group_id = ?");
+
+ // Prepare statement for method getPostingsCount()
+ this.pstmtGetPostingsCount = conn.prepareStatement(
+ "SELECT Count(*) FROM postings NATURAL JOIN groups " +
+ "WHERE groups.name = ?");
+
+ // Prepare statement for method getSubscriptions()
+ this.pstmtGetSubscriptions = conn.prepareStatement(
+ "SELECT host, port, name FROM peers NATURAL JOIN " +
+ "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
+
+ // Prepare statement for method isArticleExisting()
+ this.pstmtIsArticleExisting = conn.prepareStatement(
+ "SELECT Count(article_id) FROM article_ids WHERE message_id = ?");
+
+ // Prepare statement for method isGroupExisting()
+ this.pstmtIsGroupExisting = conn.prepareStatement(
+ "SELECT * FROM groups WHERE name = ?");
+
+ // Prepare statement for method setConfigValue()
+ this.pstmtSetConfigValue0 = conn.prepareStatement(
+ "DELETE FROM config WHERE config_key = ?");
+ this.pstmtSetConfigValue1 = conn.prepareStatement(
+ "INSERT INTO config VALUES(?, ?)");
+
+ // Prepare statements for method purgeGroup()
+ this.pstmtPurgeGroup0 = conn.prepareStatement(
+ "DELETE FROM peer_subscriptions WHERE group_id = ?");
+ this.pstmtPurgeGroup1 = conn.prepareStatement(
+ "DELETE FROM groups WHERE group_id = ?");
+
+ // Prepare statement for method update(Group)
+ this.pstmtUpdateGroup = conn.prepareStatement(
+ "UPDATE groups SET flags = ?, name = ? WHERE group_id = ?");
+ }
+ catch(ClassNotFoundException ex)
+ {
+ throw new Error("JDBC Driver not found!", ex);
+ }
+ }
+
+ /**
+ * Adds an article to the database.
+ * @param article
+ * @return
+ * @throws java.sql.SQLException
+ */
+ @Override
+ public void addArticle(final Article article)
+ throws StorageBackendException
+ {
+ try
+ {
+ this.conn.setAutoCommit(false);
+
+ int newArticleID = getMaxArticleID() + 1;
+
+ // Fill prepared statement with values;
+ // writes body to article table
+ pstmtAddArticle1.setInt(1, newArticleID);
+ pstmtAddArticle1.setBytes(2, article.getBody());
+ pstmtAddArticle1.execute();
+
+ // Add headers
+ Enumeration headers = article.getAllHeaders();
+ for(int n = 0; headers.hasMoreElements(); n++)
+ {
+ Header header = (Header)headers.nextElement();
+ pstmtAddArticle2.setInt(1, newArticleID);
+ pstmtAddArticle2.setString(2, header.getName().toLowerCase());
+ pstmtAddArticle2.setString(3,
+ header.getValue().replaceAll("[\r\n]", ""));
+ pstmtAddArticle2.setInt(4, n);
+ pstmtAddArticle2.execute();
+ }
+
+ // For each newsgroup add a reference
+ List groups = article.getGroups();
+ for(Group group : groups)
+ {
+ pstmtAddArticle3.setLong(1, group.getInternalID());
+ pstmtAddArticle3.setInt(2, newArticleID);
+ pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getInternalID()) + 1);
+ pstmtAddArticle3.execute();
+ }
+
+ // Write message-id to article_ids table
+ this.pstmtAddArticle4.setInt(1, newArticleID);
+ this.pstmtAddArticle4.setString(2, article.getMessageID());
+ this.pstmtAddArticle4.execute();
+
+ this.conn.commit();
+ this.conn.setAutoCommit(true);
+
+ this.restarts = 0; // Reset error count
+ }
+ catch(SQLException ex)
+ {
+ try
+ {
+ this.conn.rollback(); // Rollback changes
+ }
+ catch(SQLException ex2)
+ {
+ Log.get().severe("Rollback of addArticle() failed: " + ex2);
+ }
+
+ try
+ {
+ this.conn.setAutoCommit(true); // and release locks
+ }
+ catch(SQLException ex2)
+ {
+ Log.get().severe("setAutoCommit(true) of addArticle() failed: " + ex2);
+ }
+
+ restartConnection(ex);
+ addArticle(article);
+ }
+ }
+
+ /**
+ * Adds a group to the JDBCDatabase. This method is not accessible via NNTP.
+ * @param name
+ * @throws java.sql.SQLException
+ */
+ @Override
+ public void addGroup(String name, int flags)
+ throws StorageBackendException
+ {
+ try
+ {
+ this.conn.setAutoCommit(false);
+ pstmtAddGroup0.setString(1, name);
+ pstmtAddGroup0.setInt(2, flags);
+
+ pstmtAddGroup0.executeUpdate();
+ this.conn.commit();
+ this.conn.setAutoCommit(true);
+ this.restarts = 0; // Reset error count
+ }
+ catch(SQLException ex)
+ {
+ try
+ {
+ this.conn.rollback();
+ this.conn.setAutoCommit(true);
+ }
+ catch(SQLException ex2)
+ {
+ ex2.printStackTrace();
+ }
+
+ restartConnection(ex);
+ addGroup(name, flags);
+ }
+ }
+
+ @Override
+ public void addEvent(long time, int type, long gid)
+ throws StorageBackendException
+ {
+ try
+ {
+ this.conn.setAutoCommit(false);
+ this.pstmtAddEvent.setLong(1, time);
+ this.pstmtAddEvent.setInt(2, type);
+ this.pstmtAddEvent.setLong(3, gid);
+ this.pstmtAddEvent.executeUpdate();
+ this.conn.commit();
+ this.conn.setAutoCommit(true);
+ this.restarts = 0;
+ }
+ catch(SQLException ex)
+ {
+ try
+ {
+ this.conn.rollback();
+ this.conn.setAutoCommit(true);
+ }
+ catch(SQLException ex2)
+ {
+ ex2.printStackTrace();
+ }
+
+ restartConnection(ex);
+ addEvent(time, type, gid);
+ }
+ }
+
+ @Override
+ public int countArticles()
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ rs = this.pstmtCountArticles.executeQuery();
+ if(rs.next())
+ {
+ return rs.getInt(1);
+ }
+ else
+ {
+ return -1;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return countArticles();
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ restarts = 0;
+ }
+ }
+ }
+
+ @Override
+ public int countGroups()
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ rs = this.pstmtCountGroups.executeQuery();
+ if(rs.next())
+ {
+ return rs.getInt(1);
+ }
+ else
+ {
+ return -1;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return countGroups();
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ restarts = 0;
+ }
+ }
+ }
+
+ @Override
+ public void delete(final String messageID)
+ throws StorageBackendException
+ {
+ try
+ {
+ this.conn.setAutoCommit(false);
+
+ this.pstmtDeleteArticle0.setString(1, messageID);
+ int rs = this.pstmtDeleteArticle0.executeUpdate();
+
+ // We do not trust the ON DELETE CASCADE functionality to delete
+ // orphaned references...
+ this.pstmtDeleteArticle1.setString(1, messageID);
+ rs = this.pstmtDeleteArticle1.executeUpdate();
+
+ this.pstmtDeleteArticle2.setString(1, messageID);
+ rs = this.pstmtDeleteArticle2.executeUpdate();
+
+ this.pstmtDeleteArticle3.setString(1, messageID);
+ rs = this.pstmtDeleteArticle3.executeUpdate();
+
+ this.conn.commit();
+ this.conn.setAutoCommit(true);
+ }
+ catch(SQLException ex)
+ {
+ throw new StorageBackendException(ex);
+ }
+ }
+
+ @Override
+ public Article getArticle(String messageID)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+ try
+ {
+ pstmtGetArticle0.setString(1, messageID);
+ rs = pstmtGetArticle0.executeQuery();
+
+ if(!rs.next())
+ {
+ return null;
+ }
+ else
+ {
+ byte[] body = rs.getBytes("body");
+ String headers = getArticleHeaders(rs.getInt("article_id"));
+ return new Article(headers, body);
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getArticle(messageID);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ restarts = 0; // Reset error count
+ }
+ }
+ }
+
+ /**
+ * Retrieves an article by its ID.
+ * @param articleID
+ * @return
+ * @throws StorageBackendException
+ */
+ @Override
+ public Article getArticle(long articleIndex, long gid)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetArticle1.setLong(1, articleIndex);
+ this.pstmtGetArticle1.setLong(2, gid);
+
+ rs = this.pstmtGetArticle1.executeQuery();
+
+ if(rs.next())
+ {
+ byte[] body = rs.getBytes("body");
+ String headers = getArticleHeaders(rs.getInt("article_id"));
+ return new Article(headers, body);
+ }
+ else
+ {
+ return null;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getArticle(articleIndex, gid);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ restarts = 0;
+ }
+ }
+ }
+
+ /**
+ * Searches for fitting header values using the given regular expression.
+ * @param group
+ * @param start
+ * @param end
+ * @param headerKey
+ * @param pattern
+ * @return
+ * @throws StorageBackendException
+ */
+ @Override
+ public List> getArticleHeaders(Channel group, long start,
+ long end, String headerKey, String patStr)
+ throws StorageBackendException, PatternSyntaxException
+ {
+ ResultSet rs = null;
+ List> heads = new ArrayList>();
+
+ try
+ {
+ this.pstmtGetArticleHeaders1.setString(1, group.getName());
+ this.pstmtGetArticleHeaders1.setString(2, headerKey);
+ this.pstmtGetArticleHeaders1.setLong(3, start);
+
+ rs = this.pstmtGetArticleHeaders1.executeQuery();
+
+ // Convert the "NNTP" regex to Java regex
+ patStr = patStr.replace("*", ".*");
+ Pattern pattern = Pattern.compile(patStr);
+
+ while(rs.next())
+ {
+ Long articleIndex = rs.getLong(1);
+ if(end < 0 || articleIndex <= end) // Match start is done via SQL
+ {
+ String headerValue = rs.getString(2);
+ Matcher matcher = pattern.matcher(headerValue);
+ if(matcher.matches())
+ {
+ heads.add(new Pair(articleIndex, headerValue));
+ }
+ }
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getArticleHeaders(group, start, end, headerKey, patStr);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+
+ return heads;
+ }
+
+ private String getArticleHeaders(long articleID)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetArticleHeaders0.setLong(1, articleID);
+ rs = this.pstmtGetArticleHeaders0.executeQuery();
+
+ StringBuilder buf = new StringBuilder();
+ if(rs.next())
+ {
+ for(;;)
+ {
+ buf.append(rs.getString(1)); // key
+ buf.append(": ");
+ String foldedValue = MimeUtility.fold(0, rs.getString(2));
+ buf.append(foldedValue); // value
+ if(rs.next())
+ {
+ buf.append("\r\n");
+ }
+ else
+ {
+ break;
+ }
+ }
+ }
+
+ return buf.toString();
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getArticleHeaders(articleID);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public long getArticleIndex(Article article, Group group)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetArticleIndex.setString(1, article.getMessageID());
+ this.pstmtGetArticleIndex.setLong(2, group.getInternalID());
+
+ rs = this.pstmtGetArticleIndex.executeQuery();
+ if(rs.next())
+ {
+ return rs.getLong(1);
+ }
+ else
+ {
+ return -1;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getArticleIndex(article, group);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ /**
+ * Returns a list of Long/Article Pairs.
+ * @throws java.sql.SQLException
+ */
+ @Override
+ public List> getArticleHeads(Group group, long first,
+ long last)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetArticleHeads.setLong(1, group.getInternalID());
+ this.pstmtGetArticleHeads.setLong(2, first);
+ this.pstmtGetArticleHeads.setLong(3, last);
+ rs = pstmtGetArticleHeads.executeQuery();
+
+ List> articles
+ = new ArrayList>();
+
+ while (rs.next())
+ {
+ long aid = rs.getLong("article_id");
+ long aidx = rs.getLong("article_index");
+ String headers = getArticleHeaders(aid);
+ articles.add(new Pair(aidx,
+ new ArticleHead(headers)));
+ }
+
+ return articles;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getArticleHeads(group, first, last);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public List getArticleNumbers(long gid)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+ try
+ {
+ List ids = new ArrayList();
+ this.pstmtGetArticleIDs.setLong(1, gid);
+ rs = this.pstmtGetArticleIDs.executeQuery();
+ while(rs.next())
+ {
+ ids.add(rs.getLong(1));
+ }
+ return ids;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getArticleNumbers(gid);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ restarts = 0; // Clear the restart count after successful request
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public String getConfigValue(String key)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+ try
+ {
+ this.pstmtGetConfigValue.setString(1, key);
+
+ rs = this.pstmtGetConfigValue.executeQuery();
+ if(rs.next())
+ {
+ return rs.getString(1); // First data on index 1 not 0
+ }
+ else
+ {
+ return null;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getConfigValue(key);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ restarts = 0; // Clear the restart count after successful request
+ }
+ }
+ }
+
+ @Override
+ public int getEventsCount(int type, long start, long end, Channel channel)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ if(channel == null)
+ {
+ this.pstmtGetEventsCount0.setInt(1, type);
+ this.pstmtGetEventsCount0.setLong(2, start);
+ this.pstmtGetEventsCount0.setLong(3, end);
+ rs = this.pstmtGetEventsCount0.executeQuery();
+ }
+ else
+ {
+ this.pstmtGetEventsCount1.setInt(1, type);
+ this.pstmtGetEventsCount1.setLong(2, start);
+ this.pstmtGetEventsCount1.setLong(3, end);
+ this.pstmtGetEventsCount1.setLong(4, channel.getInternalID());
+ rs = this.pstmtGetEventsCount1.executeQuery();
+ }
+
+ if(rs.next())
+ {
+ return rs.getInt(1);
+ }
+ else
+ {
+ return -1;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getEventsCount(type, start, end, channel);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ /**
+ * Reads all Groups from the JDBCDatabase.
+ * @return
+ * @throws StorageBackendException
+ */
+ @Override
+ public List getGroups()
+ throws StorageBackendException
+ {
+ ResultSet rs;
+ List buffer = new ArrayList();
+ Statement stmt = null;
+
+ try
+ {
+ stmt = conn.createStatement();
+ rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
+
+ while(rs.next())
+ {
+ String name = rs.getString("name");
+ long id = rs.getLong("group_id");
+ int flags = rs.getInt("flags");
+
+ Group group = new Group(name, id, flags);
+ buffer.add(group);
+ }
+
+ return buffer;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getGroups();
+ }
+ finally
+ {
+ if(stmt != null)
+ {
+ try
+ {
+ stmt.close(); // Implicitely closes ResultSets
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public List getGroupsForList(String listAddress)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetGroupForList.setString(1, listAddress);
+
+ rs = this.pstmtGetGroupForList.executeQuery();
+ List groups = new ArrayList();
+ while(rs.next())
+ {
+ String group = rs.getString(1);
+ groups.add(group);
+ }
+ return groups;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getGroupsForList(listAddress);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ /**
+ * Returns the Group that is identified by the name.
+ * @param name
+ * @return
+ * @throws StorageBackendException
+ */
+ @Override
+ public Group getGroup(String name)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetGroup0.setString(1, name);
+ rs = this.pstmtGetGroup0.executeQuery();
+
+ if (!rs.next())
+ {
+ return null;
+ }
+ else
+ {
+ long id = rs.getLong("group_id");
+ int flags = rs.getInt("flags");
+ return new Group(name, id, flags);
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getGroup(name);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public List getListsForGroup(String group)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+ List lists = new ArrayList();
+
+ try
+ {
+ this.pstmtGetListForGroup.setString(1, group);
+ rs = this.pstmtGetListForGroup.executeQuery();
+
+ while(rs.next())
+ {
+ lists.add(rs.getString(1));
+ }
+ return lists;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getListsForGroup(group);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ private int getMaxArticleIndex(long groupID)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetMaxArticleIndex.setLong(1, groupID);
+ rs = this.pstmtGetMaxArticleIndex.executeQuery();
+
+ int maxIndex = 0;
+ if (rs.next())
+ {
+ maxIndex = rs.getInt(1);
+ }
+
+ return maxIndex;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getMaxArticleIndex(groupID);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ private int getMaxArticleID()
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ rs = this.pstmtGetMaxArticleID.executeQuery();
+
+ int maxIndex = 0;
+ if (rs.next())
+ {
+ maxIndex = rs.getInt(1);
+ }
+
+ return maxIndex;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getMaxArticleID();
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public int getLastArticleNumber(Group group)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetLastArticleNumber.setLong(1, group.getInternalID());
+ rs = this.pstmtGetLastArticleNumber.executeQuery();
+ if (rs.next())
+ {
+ return rs.getInt(1);
+ }
+ else
+ {
+ return 0;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getLastArticleNumber(group);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public int getFirstArticleNumber(Group group)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+ try
+ {
+ this.pstmtGetFirstArticleNumber.setLong(1, group.getInternalID());
+ rs = this.pstmtGetFirstArticleNumber.executeQuery();
+ if(rs.next())
+ {
+ return rs.getInt(1);
+ }
+ else
+ {
+ return 0;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getFirstArticleNumber(group);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ /**
+ * Returns a group name identified by the given id.
+ * @param id
+ * @return
+ * @throws StorageBackendException
+ */
+ public String getGroup(int id)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetGroup1.setInt(1, id);
+ rs = this.pstmtGetGroup1.executeQuery();
+
+ if (rs.next())
+ {
+ return rs.getString(1);
+ }
+ else
+ {
+ return null;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getGroup(id);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public double getEventsPerHour(int key, long gid)
+ throws StorageBackendException
+ {
+ String gidquery = "";
+ if(gid >= 0)
+ {
+ gidquery = " AND group_id = " + gid;
+ }
+
+ Statement stmt = null;
+ ResultSet rs = null;
+
+ try
+ {
+ stmt = this.conn.createStatement();
+ rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))" +
+ " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
+
+ if(rs.next())
+ {
+ restarts = 0; // reset error count
+ return rs.getDouble(1);
+ }
+ else
+ {
+ return Double.NaN;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getEventsPerHour(key, gid);
+ }
+ finally
+ {
+ try
+ {
+ if(stmt != null)
+ {
+ stmt.close(); // Implicitely closes the result sets
+ }
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+
+ @Override
+ public String getOldestArticle()
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ rs = this.pstmtGetOldestArticle.executeQuery();
+ if(rs.next())
+ {
+ return rs.getString(1);
+ }
+ else
+ {
+ return null;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getOldestArticle();
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public int getPostingsCount(String groupname)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtGetPostingsCount.setString(1, groupname);
+ rs = this.pstmtGetPostingsCount.executeQuery();
+ if(rs.next())
+ {
+ return rs.getInt(1);
+ }
+ else
+ {
+ Log.get().warning("Count on postings return nothing!");
+ return 0;
+ }
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getPostingsCount(groupname);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public List getSubscriptions(int feedtype)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ List subs = new ArrayList();
+ this.pstmtGetSubscriptions.setInt(1, feedtype);
+ rs = this.pstmtGetSubscriptions.executeQuery();
+
+ while(rs.next())
+ {
+ String host = rs.getString("host");
+ String group = rs.getString("name");
+ int port = rs.getInt("port");
+ subs.add(new Subscription(host, port, feedtype, group));
+ }
+
+ return subs;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return getSubscriptions(feedtype);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ /**
+ * Checks if there is an article with the given messageid in the JDBCDatabase.
+ * @param name
+ * @return
+ * @throws StorageBackendException
+ */
+ @Override
+ public boolean isArticleExisting(String messageID)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtIsArticleExisting.setString(1, messageID);
+ rs = this.pstmtIsArticleExisting.executeQuery();
+ return rs.next() && rs.getInt(1) == 1;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return isArticleExisting(messageID);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ /**
+ * Checks if there is a group with the given name in the JDBCDatabase.
+ * @param name
+ * @return
+ * @throws StorageBackendException
+ */
+ @Override
+ public boolean isGroupExisting(String name)
+ throws StorageBackendException
+ {
+ ResultSet rs = null;
+
+ try
+ {
+ this.pstmtIsGroupExisting.setString(1, name);
+ rs = this.pstmtIsGroupExisting.executeQuery();
+ return rs.next();
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return isGroupExisting(name);
+ }
+ finally
+ {
+ if(rs != null)
+ {
+ try
+ {
+ rs.close();
+ }
+ catch(SQLException ex)
+ {
+ ex.printStackTrace();
+ }
+ }
+ }
+ }
+
+ @Override
+ public void setConfigValue(String key, String value)
+ throws StorageBackendException
+ {
+ try
+ {
+ conn.setAutoCommit(false);
+ this.pstmtSetConfigValue0.setString(1, key);
+ this.pstmtSetConfigValue0.execute();
+ this.pstmtSetConfigValue1.setString(1, key);
+ this.pstmtSetConfigValue1.setString(2, value);
+ this.pstmtSetConfigValue1.execute();
+ conn.commit();
+ conn.setAutoCommit(true);
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ setConfigValue(key, value);
+ }
+ }
+
+ /**
+ * Closes the JDBCDatabase connection.
+ */
+ public void shutdown()
+ throws StorageBackendException
+ {
+ try
+ {
+ if(this.conn != null)
+ {
+ this.conn.close();
+ }
+ }
+ catch(SQLException ex)
+ {
+ throw new StorageBackendException(ex);
+ }
+ }
+
+ @Override
+ public void purgeGroup(Group group)
+ throws StorageBackendException
+ {
+ try
+ {
+ this.pstmtPurgeGroup0.setLong(1, group.getInternalID());
+ this.pstmtPurgeGroup0.executeUpdate();
+
+ this.pstmtPurgeGroup1.setLong(1, group.getInternalID());
+ this.pstmtPurgeGroup1.executeUpdate();
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ purgeGroup(group);
+ }
+ }
+
+ private void restartConnection(SQLException cause)
+ throws StorageBackendException
+ {
+ restarts++;
+ Log.get().severe(Thread.currentThread()
+ + ": Database connection was closed (restart " + restarts + ").");
+
+ if(restarts >= MAX_RESTARTS)
+ {
+ // Delete the current, probably broken JDBCDatabase instance.
+ // So no one can use the instance any more.
+ JDBCDatabaseProvider.instances.remove(Thread.currentThread());
+
+ // Throw the exception upwards
+ throw new StorageBackendException(cause);
+ }
+
+ try
+ {
+ Thread.sleep(1500L * restarts);
+ }
+ catch(InterruptedException ex)
+ {
+ Log.get().warning("Interrupted: " + ex.getMessage());
+ }
+
+ // Try to properly close the old database connection
+ try
+ {
+ if(this.conn != null)
+ {
+ this.conn.close();
+ }
+ }
+ catch(SQLException ex)
+ {
+ Log.get().warning(ex.getMessage());
+ }
+
+ try
+ {
+ // Try to reinitialize database connection
+ arise();
+ }
+ catch(SQLException ex)
+ {
+ Log.get().warning(ex.getMessage());
+ restartConnection(ex);
+ }
+ }
+
+ @Override
+ public boolean update(Article article)
+ throws StorageBackendException
+ {
+ // DELETE FROM headers WHERE article_id = ?
+
+ // INSERT INTO headers ...
+
+ // SELECT * FROM postings WHERE article_id = ? AND group_id = ?
+ return false;
+ }
+
+ /**
+ * Writes the flags and the name of the given group to the database.
+ * @param group
+ * @throws StorageBackendException
+ */
+ @Override
+ public boolean update(Group group)
+ throws StorageBackendException
+ {
+ try
+ {
+ this.pstmtUpdateGroup.setInt(1, group.getFlags());
+ this.pstmtUpdateGroup.setString(2, group.getName());
+ this.pstmtUpdateGroup.setLong(3, group.getInternalID());
+ int rs = this.pstmtUpdateGroup.executeUpdate();
+ return rs == 1;
+ }
+ catch(SQLException ex)
+ {
+ restartConnection(ex);
+ return update(group);
+ }
+ }
+
+}