org/sonews/daemon/storage/Database.java
author chris <chris@marvin>
Wed, 01 Jul 2009 10:48:22 +0200
changeset 2 1090e2141798
parent 1 6fceb66e1ad7
permissions -rw-r--r--
sonews/0.5.1 fixes merged
     1 /*
     2  *   SONEWS News Server
     3  *   see AUTHORS for the list of contributors
     4  *
     5  *   This program is free software: you can redistribute it and/or modify
     6  *   it under the terms of the GNU General Public License as published by
     7  *   the Free Software Foundation, either version 3 of the License, or
     8  *   (at your option) any later version.
     9  *
    10  *   This program is distributed in the hope that it will be useful,
    11  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
    12  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    13  *   GNU General Public License for more details.
    14  *
    15  *   You should have received a copy of the GNU General Public License
    16  *   along with this program.  If not, see <http://www.gnu.org/licenses/>.
    17  */
    18 
    19 package org.sonews.daemon.storage;
    20 
    21 import java.sql.Connection;
    22 import java.sql.DriverManager;
    23 import java.sql.ResultSet;
    24 import java.sql.SQLException;
    25 import java.sql.Statement;
    26 import java.sql.PreparedStatement;
    27 import java.util.ArrayList;
    28 import java.util.Enumeration;
    29 import java.util.List;
    30 import java.util.Map;
    31 import java.util.concurrent.ConcurrentHashMap;
    32 import javax.mail.Header;
    33 import javax.mail.internet.InternetAddress;
    34 import javax.mail.internet.MimeUtility;
    35 import org.sonews.daemon.BootstrapConfig;
    36 import org.sonews.util.Log;
    37 import org.sonews.feed.Subscription;
    38 import org.sonews.util.Pair;
    39 
    40 /**
    41  * Database facade class.
    42  * @author Christian Lins
    43  * @since sonews/0.5.0
    44  */
    45 // TODO: Refactor this class to reduce size (e.g. ArticleDatabase GroupDatabase)
    46 public class Database
    47 {
    48 
    49   public static final int MAX_RESTARTS = 3;
    50   
    51   private static final Map<Thread, Database> instances 
    52     = new ConcurrentHashMap<Thread, Database>();
    53   
    54   /**
    55    * @return Instance of the current Database backend. Returns null if an error
    56    * has occurred.
    57    */
    58   public static Database getInstance(boolean create)
    59     throws SQLException
    60   {
    61     if(!instances.containsKey(Thread.currentThread()) && create)
    62     {
    63       Database db = new Database();
    64       db.arise();
    65       instances.put(Thread.currentThread(), db);
    66       return db;
    67     }
    68     else
    69     {
    70       return instances.get(Thread.currentThread());
    71     }
    72   }
    73   
    74   public static Database getInstance()
    75     throws SQLException
    76   {
    77     return getInstance(true);
    78   }
    79   
    80   private Connection        conn = null;
    81   private PreparedStatement pstmtAddArticle1 = null;
    82   private PreparedStatement pstmtAddArticle2 = null;
    83   private PreparedStatement pstmtAddArticle3 = null;
    84   private PreparedStatement pstmtAddArticle4 = null;
    85   private PreparedStatement pstmtAddGroup0   = null;
    86   private PreparedStatement pstmtAddEvent = null;
    87   private PreparedStatement pstmtCountArticles = null;
    88   private PreparedStatement pstmtCountGroups   = null;
    89   private PreparedStatement pstmtDeleteArticle0 = null;
    90   private PreparedStatement pstmtGetArticle0 = null;
    91   private PreparedStatement pstmtGetArticle1 = null;
    92   private PreparedStatement pstmtGetArticleHeaders  = null;
    93   private PreparedStatement pstmtGetArticleHeads = null;
    94   private PreparedStatement pstmtGetArticleIDs   = null;
    95   private PreparedStatement pstmtGetArticleIndex    = null;
    96   private PreparedStatement pstmtGetConfigValue = null;
    97   private PreparedStatement pstmtGetEventsCount0 = null;
    98   private PreparedStatement pstmtGetEventsCount1 = null;
    99   private PreparedStatement pstmtGetGroupForList = null;
   100   private PreparedStatement pstmtGetGroup0     = null;
   101   private PreparedStatement pstmtGetGroup1     = null;
   102   private PreparedStatement pstmtGetFirstArticleNumber = null;
   103   private PreparedStatement pstmtGetListForGroup       = null;
   104   private PreparedStatement pstmtGetLastArticleNumber  = null;
   105   private PreparedStatement pstmtGetMaxArticleID       = null;
   106   private PreparedStatement pstmtGetMaxArticleIndex    = null;
   107   private PreparedStatement pstmtGetPostingsCount      = null;
   108   private PreparedStatement pstmtGetSubscriptions  = null;
   109   private PreparedStatement pstmtIsArticleExisting = null;
   110   private PreparedStatement pstmtIsGroupExisting = null;
   111   private PreparedStatement pstmtSetConfigValue0 = null;
   112   private PreparedStatement pstmtSetConfigValue1 = null;
   113   
   114   /** How many times the database connection was reinitialized */
   115   private int restarts = 0;
   116   
   117   /**
   118    * Rises the database: reconnect and recreate all prepared statements.
   119    * @throws java.lang.SQLException
   120    */
   121   private void arise()
   122     throws SQLException
   123   {
   124     try
   125     {
   126       // Load database driver
   127       Class.forName(
   128               BootstrapConfig.getInstance().get(BootstrapConfig.STORAGE_DBMSDRIVER, "java.lang.Object"));
   129 
   130       // Establish database connection
   131       this.conn = DriverManager.getConnection(
   132               BootstrapConfig.getInstance().get(BootstrapConfig.STORAGE_DATABASE, "<not specified>"),
   133               BootstrapConfig.getInstance().get(BootstrapConfig.STORAGE_USER, "root"),
   134               BootstrapConfig.getInstance().get(BootstrapConfig.STORAGE_PASSWORD, ""));
   135 
   136       this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
   137       if(this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE)
   138       {
   139         Log.msg("Warning: Database is NOT fully serializable!", false);
   140       }
   141 
   142       // Prepare statements for method addArticle()
   143       this.pstmtAddArticle1 = conn.prepareStatement(
   144         "INSERT INTO articles (article_id, body) VALUES(?, ?)");
   145       this.pstmtAddArticle2 = conn.prepareStatement(
   146         "INSERT INTO headers (article_id, header_key, header_value, header_index) " +
   147         "VALUES (?, ?, ?, ?)");
   148       this.pstmtAddArticle3 = conn.prepareStatement(
   149         "INSERT INTO postings (group_id, article_id, article_index)" +
   150         "VALUES (?, ?, ?)");
   151       this.pstmtAddArticle4 = conn.prepareStatement(
   152         "INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");
   153 
   154       // Prepare statement for method addStatValue()
   155       this.pstmtAddEvent = conn.prepareStatement(
   156         "INSERT INTO events VALUES (?, ?, ?)");
   157      
   158       // Prepare statement for method addGroup()
   159       this.pstmtAddGroup0 = conn.prepareStatement(
   160         "INSERT INTO groups (name, flags) VALUES (?, ?)");
   161       
   162       // Prepare statement for method countArticles()
   163       this.pstmtCountArticles = conn.prepareStatement(
   164         "SELECT Count(article_id) FROM article_ids");
   165       
   166       // Prepare statement for method countGroups()
   167       this.pstmtCountGroups = conn.prepareStatement(
   168         "SELECT Count(group_id) FROM groups WHERE " +
   169         "flags & " + Group.DELETED + " = 0");
   170       
   171       // Prepare statements for method delete(article)
   172       this.pstmtDeleteArticle0 = conn.prepareStatement(
   173         "DELETE FROM articles WHERE article_id = " +
   174         "(SELECT article_id FROM article_ids WHERE message_id = ?)");
   175 
   176       // Prepare statements for methods getArticle()
   177       this.pstmtGetArticle0 = conn.prepareStatement(
   178         "SELECT * FROM articles  WHERE article_id = " +
   179         "(SELECT article_id FROM article_ids WHERE message_id = ?)");
   180       this.pstmtGetArticle1 = conn.prepareStatement(
   181         "SELECT * FROM articles WHERE article_id = " +
   182         "(SELECT article_id FROM postings WHERE " +
   183         "article_index = ? AND group_id = ?)");
   184       
   185       // Prepare statement for method getArticleHeaders()
   186       this.pstmtGetArticleHeaders = conn.prepareStatement(
   187         "SELECT header_key, header_value FROM headers WHERE article_id = ? " +
   188         "ORDER BY header_index ASC");
   189       
   190       this.pstmtGetArticleIDs = conn.prepareStatement(
   191         "SELECT article_index FROM postings WHERE group_id = ?");
   192       
   193       // Prepare statement for method getArticleIndex
   194       this.pstmtGetArticleIndex = conn.prepareStatement(
   195               "SELECT article_index FROM postings WHERE " +
   196               "article_id = (SELECT article_id FROM article_ids " +
   197               "WHERE message_id = ?) " +
   198               " AND group_id = ?");
   199 
   200       // Prepare statements for method getArticleHeads()
   201       this.pstmtGetArticleHeads = conn.prepareStatement(
   202         "SELECT article_id, article_index FROM postings WHERE " +
   203         "postings.group_id = ? AND article_index >= ? AND " +
   204         "article_index <= ?");
   205 
   206       // Prepare statements for method getConfigValue()
   207       this.pstmtGetConfigValue = conn.prepareStatement(
   208         "SELECT config_value FROM config WHERE config_key = ?");
   209 
   210       // Prepare statements for method getEventsCount()
   211       this.pstmtGetEventsCount0 = conn.prepareStatement(
   212         "SELECT Count(*) FROM events WHERE event_key = ? AND " +
   213         "event_time >= ? AND event_time < ?");
   214 
   215       this.pstmtGetEventsCount1 = conn.prepareStatement(
   216         "SELECT Count(*) FROM events WHERE event_key = ? AND " +
   217         "event_time >= ? AND event_time < ? AND group_id = ?");
   218       
   219       // Prepare statement for method getGroupForList()
   220       this.pstmtGetGroupForList = conn.prepareStatement(
   221         "SELECT name FROM groups INNER JOIN groups2list " +
   222         "ON groups.group_id = groups2list.group_id " +
   223         "WHERE groups2list.listaddress = ?");
   224 
   225       // Prepare statement for method getGroup()
   226       this.pstmtGetGroup0 = conn.prepareStatement(
   227         "SELECT group_id, flags FROM groups WHERE Name = ?");
   228       this.pstmtGetGroup1 = conn.prepareStatement(
   229         "SELECT name FROM groups WHERE group_id = ?");
   230 
   231       // Prepare statement for method getLastArticleNumber()
   232       this.pstmtGetLastArticleNumber = conn.prepareStatement(
   233         "SELECT Max(article_index) FROM postings WHERE group_id = ?");
   234 
   235       // Prepare statement for method getListForGroup()
   236       this.pstmtGetListForGroup = conn.prepareStatement(
   237         "SELECT listaddress FROM groups2list INNER JOIN groups " +
   238         "ON groups.group_id = groups2list.group_id WHERE name = ?");
   239 
   240       // Prepare statement for method getMaxArticleID()
   241       this.pstmtGetMaxArticleID = conn.prepareStatement(
   242         "SELECT Max(article_id) FROM articles");
   243       
   244       // Prepare statement for method getMaxArticleIndex()
   245       this.pstmtGetMaxArticleIndex = conn.prepareStatement(
   246         "SELECT Max(article_index) FROM postings WHERE group_id = ?");
   247       
   248       // Prepare statement for method getFirstArticleNumber()
   249       this.pstmtGetFirstArticleNumber = conn.prepareStatement(
   250         "SELECT Min(article_index) FROM postings WHERE group_id = ?");
   251       
   252       // Prepare statement for method getPostingsCount()
   253       this.pstmtGetPostingsCount = conn.prepareStatement(
   254         "SELECT Count(*) FROM postings NATURAL JOIN groups " +
   255         "WHERE groups.name = ?");
   256       
   257       // Prepare statement for method getSubscriptions()
   258       this.pstmtGetSubscriptions = conn.prepareStatement(
   259         "SELECT host, port, name FROM peers NATURAL JOIN " +
   260         "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
   261       
   262       // Prepare statement for method isArticleExisting()
   263       this.pstmtIsArticleExisting = conn.prepareStatement(
   264         "SELECT Count(article_id) FROM article_ids WHERE message_id = ?");
   265       
   266       // Prepare statement for method isGroupExisting()
   267       this.pstmtIsGroupExisting = conn.prepareStatement(
   268         "SELECT * FROM groups WHERE name = ?");
   269       
   270       // Prepare statement for method setConfigValue()
   271       this.pstmtSetConfigValue0 = conn.prepareStatement(
   272         "DELETE FROM config WHERE config_key = ?");
   273       this.pstmtSetConfigValue1 = conn.prepareStatement(
   274         "INSERT INTO config VALUES(?, ?)");
   275     }
   276     catch(ClassNotFoundException ex)
   277     {
   278       throw new Error("JDBC Driver not found!", ex);
   279     }
   280   }
   281   
   282   /**
   283    * Adds an article to the database.
   284    * @param article
   285    * @return
   286    * @throws java.sql.SQLException
   287    */
   288   public void addArticle(final Article article)
   289     throws SQLException
   290   {
   291     try
   292     {
   293       this.conn.setAutoCommit(false);
   294 
   295       int newArticleID = getMaxArticleID() + 1;
   296 
   297       // Fill prepared statement with values;
   298       // writes body to article table
   299       pstmtAddArticle1.setInt(1, newArticleID);
   300       pstmtAddArticle1.setBytes(2, article.getBody().getBytes());
   301       pstmtAddArticle1.execute();
   302 
   303       // Add headers
   304       Enumeration headers = article.getAllHeaders();
   305       for(int n = 0; headers.hasMoreElements(); n++)
   306       {
   307         Header header = (Header)headers.nextElement();
   308         pstmtAddArticle2.setInt(1, newArticleID);
   309         pstmtAddArticle2.setString(2, header.getName().toLowerCase());
   310         pstmtAddArticle2.setString(3, 
   311           header.getValue().replaceAll("[\r\n]", ""));
   312         pstmtAddArticle2.setInt(4, n);
   313         pstmtAddArticle2.execute();
   314       }
   315       
   316       // For each newsgroup add a reference
   317       List<Group> groups = article.getGroups();
   318       for(Group group : groups)
   319       {
   320         pstmtAddArticle3.setLong(1, group.getID());
   321         pstmtAddArticle3.setInt(2, newArticleID);
   322         pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getID()) + 1);
   323         pstmtAddArticle3.execute();
   324       }
   325       
   326       // Write message-id to article_ids table
   327       this.pstmtAddArticle4.setInt(1, newArticleID);
   328       this.pstmtAddArticle4.setString(2, article.getMessageID());
   329       this.pstmtAddArticle4.execute();
   330 
   331       this.conn.commit();
   332       this.conn.setAutoCommit(true);
   333 
   334       this.restarts = 0; // Reset error count
   335     }
   336     catch(SQLException ex)
   337     {
   338       try
   339       {
   340         this.conn.rollback();  // Rollback changes
   341       }
   342       catch(SQLException ex2)
   343       {
   344         Log.msg("Rollback of addArticle() failed: " + ex2, false);
   345       }
   346       
   347       try
   348       {
   349         this.conn.setAutoCommit(true); // and release locks
   350       }
   351       catch(SQLException ex2)
   352       {
   353         Log.msg("setAutoCommit(true) of addArticle() failed: " + ex2, false);
   354       }
   355 
   356       restartConnection(ex);
   357       addArticle(article);
   358     }
   359   }
   360   
   361   /**
   362    * Adds a group to the Database. This method is not accessible via NNTP.
   363    * @param name
   364    * @throws java.sql.SQLException
   365    */
   366   public void addGroup(String name, int flags)
   367     throws SQLException
   368   {
   369     try
   370     {
   371       this.conn.setAutoCommit(false);
   372       pstmtAddGroup0.setString(1, name);
   373       pstmtAddGroup0.setInt(2, flags);
   374 
   375       pstmtAddGroup0.executeUpdate();
   376       this.conn.commit();
   377       this.conn.setAutoCommit(true);
   378       this.restarts = 0; // Reset error count
   379     }
   380     catch(SQLException ex)
   381     {
   382       this.conn.rollback();
   383       this.conn.setAutoCommit(true);
   384       restartConnection(ex);
   385       addGroup(name, flags);
   386     }
   387   }
   388   
   389   public void addEvent(long time, byte type, long gid)
   390     throws SQLException
   391   {
   392     try
   393     {
   394       this.conn.setAutoCommit(false);
   395       this.pstmtAddEvent.setLong(1, time);
   396       this.pstmtAddEvent.setInt(2, type);
   397       this.pstmtAddEvent.setLong(3, gid);
   398       this.pstmtAddEvent.executeUpdate();
   399       this.conn.commit();
   400       this.conn.setAutoCommit(true);
   401       this.restarts = 0;
   402     }
   403     catch(SQLException ex)
   404     {
   405       this.conn.rollback();
   406       this.conn.setAutoCommit(true);
   407 
   408       restartConnection(ex);
   409       addEvent(time, type, gid);
   410     }
   411   }
   412   
   413   public int countArticles()
   414     throws SQLException
   415   {
   416     ResultSet rs = null;
   417 
   418     try
   419     {
   420       rs = this.pstmtCountArticles.executeQuery();
   421       if(rs.next())
   422       {
   423         return rs.getInt(1);
   424       }
   425       else
   426       {
   427         return -1;
   428       }
   429     }
   430     catch(SQLException ex)
   431     {
   432       restartConnection(ex);
   433       return countArticles();
   434     }
   435     finally
   436     {
   437       if(rs != null)
   438       {
   439         rs.close();
   440         restarts = 0;
   441       }
   442     }
   443   }
   444   
   445   public int countGroups()
   446     throws SQLException
   447   {
   448     ResultSet rs = null;
   449 
   450     try
   451     {
   452       rs = this.pstmtCountGroups.executeQuery();
   453       if(rs.next())
   454       {
   455         return rs.getInt(1);
   456       }
   457       else
   458       {
   459         return -1;
   460       }
   461     }
   462     catch(SQLException ex)
   463     {
   464       restartConnection(ex);
   465       return countGroups();
   466     }
   467     finally
   468     {
   469       if(rs != null)
   470       {
   471         rs.close();
   472         restarts = 0;
   473       }
   474     }
   475   }
   476   
   477   public void delete(final String messageID)
   478     throws SQLException
   479   {
   480     try
   481     {
   482       this.conn.setAutoCommit(false);
   483       
   484       this.pstmtDeleteArticle0.setString(1, messageID);
   485       int rs = this.pstmtDeleteArticle0.executeUpdate();
   486       
   487       // We trust the ON DELETE CASCADE functionality to delete
   488       // orphaned references
   489       
   490       this.conn.commit();
   491       this.conn.setAutoCommit(true);
   492     }
   493     catch(SQLException ex)
   494     {
   495       throw ex;
   496     }
   497   }
   498   
   499   public Article getArticle(String messageID)
   500     throws SQLException
   501   {
   502     ResultSet rs = null;
   503     try
   504     {
   505       pstmtGetArticle0.setString(1, messageID);
   506       rs = pstmtGetArticle0.executeQuery();
   507 
   508       if(!rs.next())
   509       {
   510         return null;
   511       }
   512       else
   513       {
   514         String body     = new String(rs.getBytes("body"));
   515         String headers  = getArticleHeaders(rs.getInt("article_id"));
   516         return new Article(headers, body);
   517       }
   518     }
   519     catch(SQLException ex)
   520     {
   521       restartConnection(ex);
   522       return getArticle(messageID);
   523     }
   524     finally
   525     {
   526       if(rs != null)
   527       {
   528         rs.close();
   529         restarts = 0; // Reset error count
   530       }
   531     }
   532   }
   533   
   534   /**
   535    * Retrieves an article by its ID.
   536    * @param articleID
   537    * @return
   538    * @throws java.sql.SQLException
   539    */
   540   public Article getArticle(long articleIndex, long gid)
   541     throws SQLException
   542   {  
   543     ResultSet rs = null;
   544 
   545     try
   546     {
   547       this.pstmtGetArticle1.setLong(1, articleIndex);
   548       this.pstmtGetArticle1.setLong(2, gid);
   549 
   550       rs = this.pstmtGetArticle1.executeQuery();
   551 
   552       if(rs.next())
   553       {
   554         String body    = new String(rs.getBytes("body"));
   555         String headers = getArticleHeaders(rs.getInt("article_id"));
   556         return new Article(headers, body);
   557       }
   558       else
   559       {
   560         return null;
   561       }
   562     }
   563     catch(SQLException ex)
   564     {
   565       restartConnection(ex);
   566       return getArticle(articleIndex, gid);
   567     }
   568     finally
   569     {
   570       if(rs != null)
   571       {
   572         rs.close();
   573         restarts = 0;
   574       }
   575     }
   576   }
   577   
   578   public String getArticleHeaders(long articleID)
   579     throws SQLException
   580   {
   581     ResultSet rs = null;
   582     
   583     try
   584     {
   585       this.pstmtGetArticleHeaders.setLong(1, articleID);
   586       rs = this.pstmtGetArticleHeaders.executeQuery();
   587       
   588       StringBuilder buf = new StringBuilder();
   589       if(rs.next())
   590       {
   591         for(;;)
   592         {
   593           buf.append(rs.getString(1)); // key
   594           buf.append(": ");
   595           String foldedValue = MimeUtility.fold(0, rs.getString(2));
   596           buf.append(foldedValue); // value
   597           if(rs.next())
   598           {
   599             buf.append("\r\n");
   600           }
   601           else
   602           {
   603             break;
   604           }
   605         }
   606       }
   607       
   608       return buf.toString();
   609     }
   610     catch(SQLException ex)
   611     {
   612       restartConnection(ex);
   613       return getArticleHeaders(articleID);
   614     }
   615     finally
   616     {
   617       if(rs != null)
   618         rs.close();
   619     }
   620   }
   621   
   622   public long getArticleIndex(Article article, Group group)
   623     throws SQLException
   624   {
   625     ResultSet rs = null;
   626 
   627     try
   628     {
   629       this.pstmtGetArticleIndex.setString(1, article.getMessageID());
   630       this.pstmtGetArticleIndex.setLong(2, group.getID());
   631       
   632       rs = this.pstmtGetArticleIndex.executeQuery();
   633       if(rs.next())
   634       {
   635         return rs.getLong(1);
   636       }
   637       else
   638       {
   639         return -1;
   640       }
   641     }
   642     catch(SQLException ex)
   643     {
   644       restartConnection(ex);
   645       return getArticleIndex(article, group);
   646     }
   647     finally
   648     {
   649       if(rs != null)
   650         rs.close();
   651     }
   652   }
   653   
   654   /**
   655    * Returns a list of Long/Article Pairs.
   656    * @throws java.sql.SQLException
   657    */
   658   public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, int first, int last)
   659     throws SQLException
   660   {
   661     ResultSet rs = null;
   662 
   663     try
   664     {
   665       this.pstmtGetArticleHeads.setLong(1, group.getID());
   666       this.pstmtGetArticleHeads.setInt(2, first);
   667       this.pstmtGetArticleHeads.setInt(3, last);
   668       rs = pstmtGetArticleHeads.executeQuery();
   669 
   670       List<Pair<Long, ArticleHead>> articles 
   671         = new ArrayList<Pair<Long, ArticleHead>>();
   672 
   673       while (rs.next())
   674       {
   675         long aid  = rs.getLong("article_id");
   676         long aidx = rs.getLong("article_index");
   677         String headers = getArticleHeaders(aid);
   678         articles.add(new Pair<Long, ArticleHead>(aidx, 
   679                         new ArticleHead(headers)));
   680       }
   681 
   682       return articles;
   683     }
   684     catch(SQLException ex)
   685     {
   686       restartConnection(ex);
   687       return getArticleHeads(group, first, last);
   688     }
   689     finally
   690     {
   691       if(rs != null)
   692         rs.close();
   693     }
   694   }
   695   
   696   public List<Long> getArticleNumbers(long gid)
   697     throws SQLException
   698   {
   699     ResultSet rs = null;
   700     try
   701     {
   702       List<Long> ids = new ArrayList<Long>();
   703       this.pstmtGetArticleIDs.setLong(1, gid);
   704       rs = this.pstmtGetArticleIDs.executeQuery();
   705       while(rs.next())
   706       {
   707         ids.add(rs.getLong(1));
   708       }
   709       return ids;
   710     }
   711     catch(SQLException ex)
   712     {
   713       restartConnection(ex);
   714       return getArticleNumbers(gid);
   715     }
   716     finally
   717     {
   718       if(rs != null)
   719       {
   720         rs.close();
   721         restarts = 0; // Clear the restart count after successful request
   722       }
   723     }
   724   }
   725   
   726   public String getConfigValue(String key)
   727     throws SQLException
   728   {
   729     ResultSet rs = null;
   730     try
   731     {
   732       this.pstmtGetConfigValue.setString(1, key);
   733 
   734       rs = this.pstmtGetConfigValue.executeQuery();
   735       if(rs.next())
   736       {
   737         return rs.getString(1); // First data on index 1 not 0
   738       }
   739       else
   740       {
   741         return null;
   742       }
   743     }
   744     catch(SQLException ex)
   745     {
   746       restartConnection(ex);
   747       return getConfigValue(key);
   748     }
   749     finally
   750     {
   751       if(rs != null)
   752       {
   753         rs.close();
   754         restarts = 0; // Clear the restart count after successful request
   755       }
   756     }
   757   }
   758   
   759   public int getEventsCount(byte type, long start, long end, Group group)
   760     throws SQLException
   761   {
   762     ResultSet rs = null;
   763     
   764     try
   765     {
   766       if(group == null)
   767       {
   768         this.pstmtGetEventsCount0.setInt(1, type);
   769         this.pstmtGetEventsCount0.setLong(2, start);
   770         this.pstmtGetEventsCount0.setLong(3, end);
   771         rs = this.pstmtGetEventsCount0.executeQuery();
   772       }
   773       else
   774       {
   775         this.pstmtGetEventsCount1.setInt(1, type);
   776         this.pstmtGetEventsCount1.setLong(2, start);
   777         this.pstmtGetEventsCount1.setLong(3, end);
   778         this.pstmtGetEventsCount1.setLong(4, group.getID());
   779         rs = this.pstmtGetEventsCount1.executeQuery();
   780       }
   781       
   782       if(rs.next())
   783       {
   784         return rs.getInt(1);
   785       }
   786       else
   787       {
   788         return -1;
   789       }
   790     }
   791     catch(SQLException ex)
   792     {
   793       restartConnection(ex);
   794       return getEventsCount(type, start, end, group);
   795     }
   796     finally
   797     {
   798       if(rs != null)
   799         rs.close();
   800     }
   801   }
   802   
   803   /**
   804    * Reads all Groups from the Database.
   805    * @return
   806    * @throws java.sql.SQLException
   807    */
   808   public List<Group> getGroups()
   809     throws SQLException
   810   {
   811     ResultSet   rs;
   812     List<Group> buffer = new ArrayList<Group>();
   813     Statement   stmt   = null;
   814 
   815     try
   816     {
   817       stmt = conn.createStatement();
   818       rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
   819 
   820       while(rs.next())
   821       {
   822         String name  = rs.getString("name");
   823         long   id    = rs.getLong("group_id");
   824         int    flags = rs.getInt("flags");
   825         
   826         Group group = new Group(name, id, flags);
   827         buffer.add(group);
   828       }
   829 
   830       return buffer;
   831     }
   832     catch(SQLException ex)
   833     {
   834       restartConnection(ex);
   835       return getGroups();
   836     }
   837     finally
   838     {
   839       if(stmt != null)
   840         stmt.close(); // Implicitely closes ResultSets
   841     }
   842   }
   843   
   844   public String getGroupForList(InternetAddress listAddress)
   845     throws SQLException
   846   {
   847     ResultSet rs = null;
   848     
   849     try
   850     {
   851       this.pstmtGetGroupForList.setString(1, listAddress.getAddress());
   852 
   853       rs = this.pstmtGetGroupForList.executeQuery();
   854       if (rs.next())
   855       {
   856         return rs.getString(1);
   857       }
   858       else
   859       {
   860         return null;
   861       }
   862     }
   863     catch(SQLException ex)
   864     {
   865       restartConnection(ex);
   866       return getGroupForList(listAddress);
   867     }
   868     finally
   869     {
   870       if(rs != null)
   871         rs.close();
   872     }
   873   }
   874   
   875   /**
   876    * Returns the Group that is identified by the name.
   877    * @param name
   878    * @return
   879    * @throws java.sql.SQLException
   880    */
   881   public Group getGroup(String name)
   882     throws SQLException
   883   {
   884     ResultSet rs = null;
   885     
   886     try
   887     {
   888       this.pstmtGetGroup0.setString(1, name);
   889       rs = this.pstmtGetGroup0.executeQuery();
   890 
   891       if (!rs.next())
   892       {
   893         return null;
   894       }
   895       else
   896       {
   897         long id = rs.getLong("group_id");
   898         int flags = rs.getInt("flags");
   899         return new Group(name, id, flags);
   900       }
   901     }
   902     catch(SQLException ex)
   903     {
   904       restartConnection(ex);
   905       return getGroup(name);
   906     }
   907     finally
   908     {
   909       if(rs != null)
   910         rs.close();
   911     }
   912   }
   913   
   914   public String getListForGroup(String group)
   915     throws SQLException
   916   {
   917     ResultSet rs = null;
   918 
   919     try
   920     {
   921       this.pstmtGetListForGroup.setString(1, group);
   922       rs = this.pstmtGetListForGroup.executeQuery();
   923       if (rs.next())
   924       {
   925         return rs.getString(1);
   926       }
   927       else
   928       {
   929         return null;
   930       }
   931     }
   932     catch(SQLException ex)
   933     {
   934       restartConnection(ex);
   935       return getListForGroup(group);
   936     }
   937     finally
   938     {
   939       if(rs != null)
   940         rs.close();
   941     }
   942   }
   943   
   944   private int getMaxArticleIndex(long groupID)
   945     throws SQLException
   946   {
   947     ResultSet rs    = null;
   948 
   949     try
   950     {
   951       this.pstmtGetMaxArticleIndex.setLong(1, groupID);
   952       rs = this.pstmtGetMaxArticleIndex.executeQuery();
   953 
   954       int maxIndex = 0;
   955       if (rs.next())
   956       {
   957         maxIndex = rs.getInt(1);
   958       }
   959 
   960       return maxIndex;
   961     }
   962     catch(SQLException ex)
   963     {
   964       restartConnection(ex);
   965       return getMaxArticleIndex(groupID);
   966     }
   967     finally
   968     {
   969       if(rs != null)
   970         rs.close();
   971     }
   972   }
   973   
   974   private int getMaxArticleID()
   975     throws SQLException
   976   {
   977     ResultSet rs    = null;
   978 
   979     try
   980     {
   981       rs = this.pstmtGetMaxArticleID.executeQuery();
   982 
   983       int maxIndex = 0;
   984       if (rs.next())
   985       {
   986         maxIndex = rs.getInt(1);
   987       }
   988 
   989       return maxIndex;
   990     }
   991     catch(SQLException ex)
   992     {
   993       restartConnection(ex);
   994       return getMaxArticleID();
   995     }
   996     finally
   997     {
   998       if(rs != null)
   999         rs.close();
  1000     }
  1001   }
  1002   
  1003   public int getLastArticleNumber(Group group)
  1004     throws SQLException
  1005   {
  1006     ResultSet rs = null;
  1007 
  1008     try
  1009     {
  1010       this.pstmtGetLastArticleNumber.setLong(1, group.getID());
  1011       rs = this.pstmtGetLastArticleNumber.executeQuery();
  1012       if (rs.next())
  1013       {
  1014         return rs.getInt(1);
  1015       }
  1016       else
  1017       {
  1018         return 0;
  1019       }
  1020     }
  1021     catch(SQLException ex)
  1022     {
  1023       restartConnection(ex);
  1024       return getLastArticleNumber(group);
  1025     }
  1026     finally
  1027     {
  1028       if(rs != null)
  1029         rs.close();
  1030     }
  1031   }
  1032   
  1033   public int getFirstArticleNumber(Group group)
  1034     throws SQLException
  1035   {
  1036     ResultSet rs = null;
  1037     try
  1038     {
  1039       this.pstmtGetFirstArticleNumber.setLong(1, group.getID());
  1040       rs = this.pstmtGetFirstArticleNumber.executeQuery();
  1041       if(rs.next())
  1042       {
  1043         return rs.getInt(1);
  1044       }
  1045       else
  1046       {
  1047         return 0;
  1048       }
  1049     }
  1050     catch(SQLException ex)
  1051     {
  1052       restartConnection(ex);
  1053       return getFirstArticleNumber(group);
  1054     }
  1055     finally
  1056     {
  1057       if(rs != null)
  1058         rs.close();
  1059     }
  1060   }
  1061   
  1062   /**
  1063    * Returns a group name identified by the given id.
  1064    * @param id
  1065    * @return
  1066    * @throws java.sql.SQLException
  1067    */
  1068   public String getGroup(int id)
  1069     throws SQLException
  1070   {
  1071     ResultSet rs = null;
  1072 
  1073     try
  1074     {
  1075       this.pstmtGetGroup1.setInt(1, id);
  1076       rs = this.pstmtGetGroup1.executeQuery();
  1077 
  1078       if (rs.next())
  1079       {
  1080         return rs.getString(1);
  1081       }
  1082       else
  1083       {
  1084         return null;
  1085       }
  1086     }
  1087     catch(SQLException ex)
  1088     {
  1089       restartConnection(ex);
  1090       return getGroup(id);
  1091     }
  1092     finally
  1093     {
  1094       if(rs != null)
  1095         rs.close();
  1096     }
  1097   }
  1098   
  1099   public double getNumberOfEventsPerHour(int key, long gid)
  1100     throws SQLException
  1101   {
  1102     String gidquery = "";
  1103     if(gid >= 0)
  1104     {
  1105       gidquery = " AND group_id = " + gid;
  1106     }
  1107     
  1108     Statement stmt = null;
  1109     ResultSet rs   = null;
  1110     
  1111     try
  1112     {
  1113       stmt = this.conn.createStatement();
  1114       rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))" +
  1115         " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
  1116       
  1117       if(rs.next())
  1118       {
  1119         restarts = 0; // reset error count
  1120         return rs.getDouble(1);
  1121       }
  1122       else
  1123       {
  1124         return Double.NaN;
  1125       }
  1126     }
  1127     catch(SQLException ex)
  1128     {
  1129       restartConnection(ex);
  1130       return getNumberOfEventsPerHour(key, gid);
  1131     }
  1132     finally
  1133     {
  1134       if(stmt != null)
  1135       {
  1136         stmt.close();
  1137       }
  1138       
  1139       if(rs != null)
  1140       {
  1141         rs.close();
  1142       }
  1143     }
  1144   }
  1145   
  1146   public int getPostingsCount(String groupname)
  1147     throws SQLException
  1148   {
  1149     ResultSet rs = null;
  1150     
  1151     try
  1152     {
  1153       this.pstmtGetPostingsCount.setString(1, groupname);
  1154       rs = this.pstmtGetPostingsCount.executeQuery();
  1155       if(rs.next())
  1156       {
  1157         return rs.getInt(1);
  1158       }
  1159       else
  1160       {
  1161         Log.msg("Warning: Count on postings return nothing!", true);
  1162         return 0;
  1163       }
  1164     }
  1165     catch(SQLException ex)
  1166     {
  1167       restartConnection(ex);
  1168       return getPostingsCount(groupname);
  1169     }
  1170     finally
  1171     {
  1172       if(rs != null)
  1173         rs.close();
  1174     }
  1175   }
  1176   
  1177   public List<Subscription> getSubscriptions(int feedtype)
  1178     throws SQLException
  1179   {
  1180     ResultSet rs = null;
  1181     
  1182     try
  1183     {
  1184       List<Subscription> subs = new ArrayList<Subscription>();
  1185       this.pstmtGetSubscriptions.setInt(1, feedtype);
  1186       rs = this.pstmtGetSubscriptions.executeQuery();
  1187       
  1188       while(rs.next())
  1189       {
  1190         String host  = rs.getString("host");
  1191         String group = rs.getString("name");
  1192         int    port  = rs.getInt("port");
  1193         subs.add(new Subscription(host, port, feedtype, group));
  1194       }
  1195       
  1196       return subs;
  1197     }
  1198     catch(SQLException ex)
  1199     {
  1200       restartConnection(ex);
  1201       return getSubscriptions(feedtype);
  1202     }
  1203     finally
  1204     {
  1205       if(rs != null)
  1206         rs.close();
  1207     }
  1208   }
  1209 
  1210   /**
  1211    * Checks if there is an article with the given messageid in the Database.
  1212    * @param name
  1213    * @return
  1214    * @throws java.sql.SQLException
  1215    */
  1216   public boolean isArticleExisting(String messageID)
  1217     throws SQLException
  1218   {
  1219     ResultSet rs = null;
  1220     
  1221     try
  1222     {
  1223       this.pstmtIsArticleExisting.setString(1, messageID);
  1224       rs = this.pstmtIsArticleExisting.executeQuery();
  1225       return rs.next() && rs.getInt(1) == 1;
  1226     }
  1227     catch(SQLException ex)
  1228     {
  1229       restartConnection(ex);
  1230       return isArticleExisting(messageID);
  1231     }
  1232     finally
  1233     {
  1234       if(rs != null)
  1235         rs.close();
  1236     }
  1237   }
  1238   
  1239   /**
  1240    * Checks if there is a group with the given name in the Database.
  1241    * @param name
  1242    * @return
  1243    * @throws java.sql.SQLException
  1244    */
  1245   public boolean isGroupExisting(String name)
  1246     throws SQLException
  1247   {
  1248     ResultSet rs = null;
  1249     
  1250     try
  1251     {
  1252       this.pstmtIsGroupExisting.setString(1, name);
  1253       rs = this.pstmtIsGroupExisting.executeQuery();
  1254       return rs.next();
  1255     }
  1256     catch(SQLException ex)
  1257     {
  1258       restartConnection(ex);
  1259       return isGroupExisting(name);
  1260     }
  1261     finally
  1262     {
  1263       if(rs != null)
  1264         rs.close();
  1265     }
  1266   }
  1267   
  1268   public void setConfigValue(String key, String value)
  1269     throws SQLException
  1270   {
  1271     try
  1272     {
  1273       conn.setAutoCommit(false);
  1274       this.pstmtSetConfigValue0.setString(1, key);
  1275       this.pstmtSetConfigValue0.execute();
  1276       this.pstmtSetConfigValue1.setString(1, key);
  1277       this.pstmtSetConfigValue1.setString(2, value);
  1278       this.pstmtSetConfigValue1.execute();
  1279       conn.commit();
  1280       conn.setAutoCommit(true);
  1281     }
  1282     catch(SQLException ex)
  1283     {
  1284       restartConnection(ex);
  1285       setConfigValue(key, value);
  1286     }
  1287   }
  1288   
  1289   /**
  1290    * Closes the Database connection.
  1291    */
  1292   public void shutdown()
  1293     throws SQLException
  1294   {
  1295     if(this.conn != null)
  1296     {
  1297       this.conn.close();
  1298     }
  1299   }
  1300   
  1301   private void restartConnection(SQLException cause)
  1302     throws SQLException
  1303   {
  1304     restarts++;
  1305     Log.msg(Thread.currentThread() 
  1306       + ": Database connection was closed (restart " + restarts + ").", false);
  1307     
  1308     if(restarts >= MAX_RESTARTS)
  1309     {
  1310       // Delete the current, probably broken Database instance.
  1311       // So no one can use the instance any more.
  1312       Database.instances.remove(Thread.currentThread());
  1313       
  1314       // Throw the exception upwards
  1315       throw cause;
  1316     }
  1317     
  1318     try
  1319     {
  1320       Thread.sleep(1500L * restarts);
  1321     }
  1322     catch(InterruptedException ex)
  1323     {
  1324       Log.msg("Interrupted: " + ex.getMessage(), false);
  1325     }
  1326     
  1327     // Try to properly close the old database connection
  1328     try
  1329     {
  1330       if(this.conn != null)
  1331       {
  1332         this.conn.close();
  1333       }
  1334     }
  1335     catch(SQLException ex)
  1336     {
  1337       Log.msg(ex.getMessage(), true);
  1338     }
  1339     
  1340     try
  1341     {
  1342       // Try to reinitialize database connection
  1343       arise();
  1344     }
  1345     catch(SQLException ex)
  1346     {
  1347       Log.msg(ex.getMessage(), true);
  1348       restartConnection(ex);
  1349     }
  1350   }
  1351 
  1352 }