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