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