diff -r 1090e2141798 -r 2fdc9cc89502 org/sonews/storage/impl/JDBCDatabase.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/org/sonews/storage/impl/JDBCDatabase.java Wed Jul 22 14:04:05 2009 +0200 @@ -0,0 +1,1772 @@ +/* + * 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.InternetAddress; +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 = 3; + + 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.STORAGE_DBMSDRIVER, "java.lang.Object")); + + // Establish database connection + this.conn = DriverManager.getConnection( + Config.inst().get(Config.STORAGE_DATABASE, ""), + Config.inst().get(Config.STORAGE_USER, "root"), + Config.inst().get(Config.STORAGE_PASSWORD, "")); + + this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); + if(this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) + { + Log.msg("Warning: Database is NOT fully serializable!", false); + } + + // 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.msg("Rollback of addArticle() failed: " + ex2, false); + } + + try + { + this.conn.setAutoCommit(true); // and release locks + } + catch(SQLException ex2) + { + Log.msg("setAutoCommit(true) of addArticle() failed: " + ex2, false); + } + + 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(InternetAddress listAddress) + throws StorageBackendException + { + ResultSet rs = null; + + try + { + this.pstmtGetGroupForList.setString(1, listAddress.getAddress()); + + 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 String getListForGroup(String group) + throws StorageBackendException + { + ResultSet rs = null; + + try + { + this.pstmtGetListForGroup.setString(1, group); + rs = this.pstmtGetListForGroup.executeQuery(); + if (rs.next()) + { + return rs.getString(1); + } + else + { + return null; + } + } + catch(SQLException ex) + { + restartConnection(ex); + return getListForGroup(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.msg("Warning: Count on postings return nothing!", true); + 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.msg(Thread.currentThread() + + ": Database connection was closed (restart " + restarts + ").", false); + + 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.msg("Interrupted: " + ex.getMessage(), false); + } + + // Try to properly close the old database connection + try + { + if(this.conn != null) + { + this.conn.close(); + } + } + catch(SQLException ex) + { + Log.msg(ex.getMessage(), true); + } + + try + { + // Try to reinitialize database connection + arise(); + } + catch(SQLException ex) + { + Log.msg(ex.getMessage(), true); + restartConnection(ex); + } + } + + /** + * 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); + } + } + +}