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