org/sonews/storage/impl/JDBCDatabase.java
author cli
Mon, 24 Aug 2009 14:40:37 +0200
changeset 21 4b2c8bedb094
parent 16 5a4a41cfc0a3
child 24 2ff819fa5be1
permissions -rw-r--r--
Refactoring in CommandSelector to allow manually loading of plugins.
     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.storage.impl;
    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.regex.Matcher;
    31 import java.util.regex.Pattern;
    32 import java.util.regex.PatternSyntaxException;
    33 import javax.mail.Header;
    34 import javax.mail.internet.MimeUtility;
    35 import org.sonews.config.Config;
    36 import org.sonews.util.Log;
    37 import org.sonews.feed.Subscription;
    38 import org.sonews.storage.Article;
    39 import org.sonews.storage.ArticleHead;
    40 import org.sonews.storage.Channel;
    41 import org.sonews.storage.Group;
    42 import org.sonews.storage.Storage;
    43 import org.sonews.storage.StorageBackendException;
    44 import org.sonews.util.Pair;
    45 
    46 /**
    47  * JDBCDatabase facade class.
    48  * @author Christian Lins
    49  * @since sonews/0.5.0
    50  */
    51 // TODO: Refactor this class to reduce size (e.g. ArticleDatabase GroupDatabase)
    52 public class JDBCDatabase implements Storage
    53 {
    54 
    55   public static final int MAX_RESTARTS = 3;
    56   
    57   private Connection        conn = null;
    58   private PreparedStatement pstmtAddArticle1 = null;
    59   private PreparedStatement pstmtAddArticle2 = null;
    60   private PreparedStatement pstmtAddArticle3 = null;
    61   private PreparedStatement pstmtAddArticle4 = null;
    62   private PreparedStatement pstmtAddGroup0   = null;
    63   private PreparedStatement pstmtAddEvent = null;
    64   private PreparedStatement pstmtCountArticles = null;
    65   private PreparedStatement pstmtCountGroups   = null;
    66   private PreparedStatement pstmtDeleteArticle0 = null;
    67   private PreparedStatement pstmtDeleteArticle1 = null;
    68   private PreparedStatement pstmtDeleteArticle2 = null;
    69   private PreparedStatement pstmtDeleteArticle3 = null;
    70   private PreparedStatement pstmtGetArticle0 = null;
    71   private PreparedStatement pstmtGetArticle1 = null;
    72   private PreparedStatement pstmtGetArticleHeaders0 = null;
    73   private PreparedStatement pstmtGetArticleHeaders1 = null;
    74   private PreparedStatement pstmtGetArticleHeads = null;
    75   private PreparedStatement pstmtGetArticleIDs   = null;
    76   private PreparedStatement pstmtGetArticleIndex    = null;
    77   private PreparedStatement pstmtGetConfigValue = null;
    78   private PreparedStatement pstmtGetEventsCount0 = null;
    79   private PreparedStatement pstmtGetEventsCount1 = null;
    80   private PreparedStatement pstmtGetGroupForList = null;
    81   private PreparedStatement pstmtGetGroup0     = null;
    82   private PreparedStatement pstmtGetGroup1     = null;
    83   private PreparedStatement pstmtGetFirstArticleNumber = null;
    84   private PreparedStatement pstmtGetListForGroup       = null;
    85   private PreparedStatement pstmtGetLastArticleNumber  = null;
    86   private PreparedStatement pstmtGetMaxArticleID       = null;
    87   private PreparedStatement pstmtGetMaxArticleIndex    = null;
    88   private PreparedStatement pstmtGetOldestArticle      = null;
    89   private PreparedStatement pstmtGetPostingsCount      = null;
    90   private PreparedStatement pstmtGetSubscriptions  = null;
    91   private PreparedStatement pstmtIsArticleExisting = null;
    92   private PreparedStatement pstmtIsGroupExisting = null;
    93   private PreparedStatement pstmtPurgeGroup0     = null;
    94   private PreparedStatement pstmtPurgeGroup1     = null;
    95   private PreparedStatement pstmtSetConfigValue0 = null;
    96   private PreparedStatement pstmtSetConfigValue1 = null;
    97   private PreparedStatement pstmtUpdateGroup     = null;
    98   
    99   /** How many times the database connection was reinitialized */
   100   private int restarts = 0;
   101   
   102   /**
   103    * Rises the database: reconnect and recreate all prepared statements.
   104    * @throws java.lang.SQLException
   105    */
   106   protected void arise()
   107     throws SQLException
   108   {
   109     try
   110     {
   111       // Load database driver
   112       Class.forName(
   113               Config.inst().get(Config.STORAGE_DBMSDRIVER, "java.lang.Object"));
   114 
   115       // Establish database connection
   116       this.conn = DriverManager.getConnection(
   117               Config.inst().get(Config.STORAGE_DATABASE, "<not specified>"),
   118               Config.inst().get(Config.STORAGE_USER, "root"),
   119               Config.inst().get(Config.STORAGE_PASSWORD, ""));
   120 
   121       this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
   122       if(this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE)
   123       {
   124         Log.get().warning("Database is NOT fully serializable!");
   125       }
   126 
   127       // Prepare statements for method addArticle()
   128       this.pstmtAddArticle1 = conn.prepareStatement(
   129         "INSERT INTO articles (article_id, body) VALUES(?, ?)");
   130       this.pstmtAddArticle2 = conn.prepareStatement(
   131         "INSERT INTO headers (article_id, header_key, header_value, header_index) " +
   132         "VALUES (?, ?, ?, ?)");
   133       this.pstmtAddArticle3 = conn.prepareStatement(
   134         "INSERT INTO postings (group_id, article_id, article_index)" +
   135         "VALUES (?, ?, ?)");
   136       this.pstmtAddArticle4 = conn.prepareStatement(
   137         "INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");
   138 
   139       // Prepare statement for method addStatValue()
   140       this.pstmtAddEvent = conn.prepareStatement(
   141         "INSERT INTO events VALUES (?, ?, ?)");
   142      
   143       // Prepare statement for method addGroup()
   144       this.pstmtAddGroup0 = conn.prepareStatement(
   145         "INSERT INTO groups (name, flags) VALUES (?, ?)");
   146       
   147       // Prepare statement for method countArticles()
   148       this.pstmtCountArticles = conn.prepareStatement(
   149         "SELECT Count(article_id) FROM article_ids");
   150       
   151       // Prepare statement for method countGroups()
   152       this.pstmtCountGroups = conn.prepareStatement(
   153         "SELECT Count(group_id) FROM groups WHERE " +
   154         "flags & " + Channel.DELETED + " = 0");
   155       
   156       // Prepare statements for method delete(article)
   157       this.pstmtDeleteArticle0 = conn.prepareStatement(
   158         "DELETE FROM articles WHERE article_id = " +
   159         "(SELECT article_id FROM article_ids WHERE message_id = ?)");
   160       this.pstmtDeleteArticle1 = conn.prepareStatement(
   161         "DELETE FROM headers WHERE article_id = " +
   162         "(SELECT article_id FROM article_ids WHERE message_id = ?)");
   163       this.pstmtDeleteArticle2 = conn.prepareStatement(
   164         "DELETE FROM postings WHERE article_id = " +
   165         "(SELECT article_id FROM article_ids WHERE message_id = ?)");
   166       this.pstmtDeleteArticle3 = conn.prepareStatement(
   167         "DELETE FROM article_ids WHERE message_id = ?");
   168 
   169       // Prepare statements for methods getArticle()
   170       this.pstmtGetArticle0 = conn.prepareStatement(
   171         "SELECT * FROM articles  WHERE article_id = " +
   172         "(SELECT article_id FROM article_ids WHERE message_id = ?)");
   173       this.pstmtGetArticle1 = conn.prepareStatement(
   174         "SELECT * FROM articles WHERE article_id = " +
   175         "(SELECT article_id FROM postings WHERE " +
   176         "article_index = ? AND group_id = ?)");
   177       
   178       // Prepare statement for method getArticleHeaders()
   179       this.pstmtGetArticleHeaders0 = conn.prepareStatement(
   180         "SELECT header_key, header_value FROM headers WHERE article_id = ? " +
   181         "ORDER BY header_index ASC");
   182 
   183       // Prepare statement for method getArticleHeaders(regular expr pattern)
   184       this.pstmtGetArticleHeaders1 = conn.prepareStatement(
   185         "SELECT p.article_index, h.header_value FROM headers h " +
   186           "INNER JOIN postings p ON h.article_id = p.article_id " +
   187           "INNER JOIN groups g ON p.group_id = g.group_id " +
   188             "WHERE g.name          =  ? AND " +
   189                   "h.header_key    =  ? AND " +
   190                   "p.article_index >= ? " +
   191         "ORDER BY p.article_index ASC");
   192 
   193       this.pstmtGetArticleIDs = conn.prepareStatement(
   194         "SELECT article_index FROM postings WHERE group_id = ?");
   195       
   196       // Prepare statement for method getArticleIndex
   197       this.pstmtGetArticleIndex = conn.prepareStatement(
   198               "SELECT article_index FROM postings WHERE " +
   199               "article_id = (SELECT article_id FROM article_ids " +
   200               "WHERE message_id = ?) " +
   201               " AND group_id = ?");
   202 
   203       // Prepare statements for method getArticleHeads()
   204       this.pstmtGetArticleHeads = conn.prepareStatement(
   205         "SELECT article_id, article_index FROM postings WHERE " +
   206         "postings.group_id = ? AND article_index >= ? AND " +
   207         "article_index <= ?");
   208 
   209       // Prepare statements for method getConfigValue()
   210       this.pstmtGetConfigValue = conn.prepareStatement(
   211         "SELECT config_value FROM config WHERE config_key = ?");
   212 
   213       // Prepare statements for method getEventsCount()
   214       this.pstmtGetEventsCount0 = conn.prepareStatement(
   215         "SELECT Count(*) FROM events WHERE event_key = ? AND " +
   216         "event_time >= ? AND event_time < ?");
   217 
   218       this.pstmtGetEventsCount1 = conn.prepareStatement(
   219         "SELECT Count(*) FROM events WHERE event_key = ? AND " +
   220         "event_time >= ? AND event_time < ? AND group_id = ?");
   221       
   222       // Prepare statement for method getGroupForList()
   223       this.pstmtGetGroupForList = conn.prepareStatement(
   224         "SELECT name FROM groups INNER JOIN groups2list " +
   225         "ON groups.group_id = groups2list.group_id " +
   226         "WHERE groups2list.listaddress = ?");
   227 
   228       // Prepare statement for method getGroup()
   229       this.pstmtGetGroup0 = conn.prepareStatement(
   230         "SELECT group_id, flags FROM groups WHERE Name = ?");
   231       this.pstmtGetGroup1 = conn.prepareStatement(
   232         "SELECT name FROM groups WHERE group_id = ?");
   233 
   234       // Prepare statement for method getLastArticleNumber()
   235       this.pstmtGetLastArticleNumber = conn.prepareStatement(
   236         "SELECT Max(article_index) FROM postings WHERE group_id = ?");
   237 
   238       // Prepare statement for method getListForGroup()
   239       this.pstmtGetListForGroup = conn.prepareStatement(
   240         "SELECT listaddress FROM groups2list INNER JOIN groups " +
   241         "ON groups.group_id = groups2list.group_id WHERE name = ?");
   242 
   243       // Prepare statement for method getMaxArticleID()
   244       this.pstmtGetMaxArticleID = conn.prepareStatement(
   245         "SELECT Max(article_id) FROM articles");
   246       
   247       // Prepare statement for method getMaxArticleIndex()
   248       this.pstmtGetMaxArticleIndex = conn.prepareStatement(
   249         "SELECT Max(article_index) FROM postings WHERE group_id = ?");
   250       
   251       // Prepare statement for method getOldestArticle()
   252       this.pstmtGetOldestArticle = conn.prepareStatement(
   253         "SELECT message_id FROM article_ids WHERE article_id = " +
   254         "(SELECT Min(article_id) FROM article_ids)");
   255 
   256       // Prepare statement for method getFirstArticleNumber()
   257       this.pstmtGetFirstArticleNumber = conn.prepareStatement(
   258         "SELECT Min(article_index) FROM postings WHERE group_id = ?");
   259       
   260       // Prepare statement for method getPostingsCount()
   261       this.pstmtGetPostingsCount = conn.prepareStatement(
   262         "SELECT Count(*) FROM postings NATURAL JOIN groups " +
   263         "WHERE groups.name = ?");
   264       
   265       // Prepare statement for method getSubscriptions()
   266       this.pstmtGetSubscriptions = conn.prepareStatement(
   267         "SELECT host, port, name FROM peers NATURAL JOIN " +
   268         "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
   269       
   270       // Prepare statement for method isArticleExisting()
   271       this.pstmtIsArticleExisting = conn.prepareStatement(
   272         "SELECT Count(article_id) FROM article_ids WHERE message_id = ?");
   273       
   274       // Prepare statement for method isGroupExisting()
   275       this.pstmtIsGroupExisting = conn.prepareStatement(
   276         "SELECT * FROM groups WHERE name = ?");
   277       
   278       // Prepare statement for method setConfigValue()
   279       this.pstmtSetConfigValue0 = conn.prepareStatement(
   280         "DELETE FROM config WHERE config_key = ?");
   281       this.pstmtSetConfigValue1 = conn.prepareStatement(
   282         "INSERT INTO config VALUES(?, ?)");
   283 
   284       // Prepare statements for method purgeGroup()
   285       this.pstmtPurgeGroup0 = conn.prepareStatement(
   286         "DELETE FROM peer_subscriptions WHERE group_id = ?");
   287       this.pstmtPurgeGroup1 = conn.prepareStatement(
   288         "DELETE FROM groups WHERE group_id = ?");
   289 
   290       // Prepare statement for method update(Group)
   291       this.pstmtUpdateGroup = conn.prepareStatement(
   292         "UPDATE groups SET flags = ?, name = ? WHERE group_id = ?");
   293     }
   294     catch(ClassNotFoundException ex)
   295     {
   296       throw new Error("JDBC Driver not found!", ex);
   297     }
   298   }
   299   
   300   /**
   301    * Adds an article to the database.
   302    * @param article
   303    * @return
   304    * @throws java.sql.SQLException
   305    */
   306   @Override
   307   public void addArticle(final Article article)
   308     throws StorageBackendException
   309   {
   310     try
   311     {
   312       this.conn.setAutoCommit(false);
   313 
   314       int newArticleID = getMaxArticleID() + 1;
   315 
   316       // Fill prepared statement with values;
   317       // writes body to article table
   318       pstmtAddArticle1.setInt(1, newArticleID);
   319       pstmtAddArticle1.setBytes(2, article.getBody());
   320       pstmtAddArticle1.execute();
   321 
   322       // Add headers
   323       Enumeration headers = article.getAllHeaders();
   324       for(int n = 0; headers.hasMoreElements(); n++)
   325       {
   326         Header header = (Header)headers.nextElement();
   327         pstmtAddArticle2.setInt(1, newArticleID);
   328         pstmtAddArticle2.setString(2, header.getName().toLowerCase());
   329         pstmtAddArticle2.setString(3, 
   330           header.getValue().replaceAll("[\r\n]", ""));
   331         pstmtAddArticle2.setInt(4, n);
   332         pstmtAddArticle2.execute();
   333       }
   334       
   335       // For each newsgroup add a reference
   336       List<Group> groups = article.getGroups();
   337       for(Group group : groups)
   338       {
   339         pstmtAddArticle3.setLong(1, group.getInternalID());
   340         pstmtAddArticle3.setInt(2, newArticleID);
   341         pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getInternalID()) + 1);
   342         pstmtAddArticle3.execute();
   343       }
   344       
   345       // Write message-id to article_ids table
   346       this.pstmtAddArticle4.setInt(1, newArticleID);
   347       this.pstmtAddArticle4.setString(2, article.getMessageID());
   348       this.pstmtAddArticle4.execute();
   349 
   350       this.conn.commit();
   351       this.conn.setAutoCommit(true);
   352 
   353       this.restarts = 0; // Reset error count
   354     }
   355     catch(SQLException ex)
   356     {
   357       try
   358       {
   359         this.conn.rollback();  // Rollback changes
   360       }
   361       catch(SQLException ex2)
   362       {
   363         Log.get().severe("Rollback of addArticle() failed: " + ex2);
   364       }
   365       
   366       try
   367       {
   368         this.conn.setAutoCommit(true); // and release locks
   369       }
   370       catch(SQLException ex2)
   371       {
   372         Log.get().severe("setAutoCommit(true) of addArticle() failed: " + ex2);
   373       }
   374 
   375       restartConnection(ex);
   376       addArticle(article);
   377     }
   378   }
   379   
   380   /**
   381    * Adds a group to the JDBCDatabase. This method is not accessible via NNTP.
   382    * @param name
   383    * @throws java.sql.SQLException
   384    */
   385   @Override
   386   public void addGroup(String name, int flags)
   387     throws StorageBackendException
   388   {
   389     try
   390     {
   391       this.conn.setAutoCommit(false);
   392       pstmtAddGroup0.setString(1, name);
   393       pstmtAddGroup0.setInt(2, flags);
   394 
   395       pstmtAddGroup0.executeUpdate();
   396       this.conn.commit();
   397       this.conn.setAutoCommit(true);
   398       this.restarts = 0; // Reset error count
   399     }
   400     catch(SQLException ex)
   401     {
   402       try
   403       {
   404         this.conn.rollback();
   405         this.conn.setAutoCommit(true);
   406       }
   407       catch(SQLException ex2)
   408       {
   409         ex2.printStackTrace();
   410       }
   411 
   412       restartConnection(ex);
   413       addGroup(name, flags);
   414     }
   415   }
   416 
   417   @Override
   418   public void addEvent(long time, int type, long gid)
   419     throws StorageBackendException
   420   {
   421     try
   422     {
   423       this.conn.setAutoCommit(false);
   424       this.pstmtAddEvent.setLong(1, time);
   425       this.pstmtAddEvent.setInt(2, type);
   426       this.pstmtAddEvent.setLong(3, gid);
   427       this.pstmtAddEvent.executeUpdate();
   428       this.conn.commit();
   429       this.conn.setAutoCommit(true);
   430       this.restarts = 0;
   431     }
   432     catch(SQLException ex)
   433     {
   434       try
   435       {
   436         this.conn.rollback();
   437         this.conn.setAutoCommit(true);
   438       }
   439       catch(SQLException ex2)
   440       {
   441         ex2.printStackTrace();
   442       }
   443 
   444       restartConnection(ex);
   445       addEvent(time, type, gid);
   446     }
   447   }
   448 
   449   @Override
   450   public int countArticles()
   451     throws StorageBackendException
   452   {
   453     ResultSet rs = null;
   454 
   455     try
   456     {
   457       rs = this.pstmtCountArticles.executeQuery();
   458       if(rs.next())
   459       {
   460         return rs.getInt(1);
   461       }
   462       else
   463       {
   464         return -1;
   465       }
   466     }
   467     catch(SQLException ex)
   468     {
   469       restartConnection(ex);
   470       return countArticles();
   471     }
   472     finally
   473     {
   474       if(rs != null)
   475       {
   476         try
   477         {
   478           rs.close();
   479         }
   480         catch(SQLException ex)
   481         {
   482           ex.printStackTrace();
   483         }
   484         restarts = 0;
   485       }
   486     }
   487   }
   488 
   489   @Override
   490   public int countGroups()
   491     throws StorageBackendException
   492   {
   493     ResultSet rs = null;
   494 
   495     try
   496     {
   497       rs = this.pstmtCountGroups.executeQuery();
   498       if(rs.next())
   499       {
   500         return rs.getInt(1);
   501       }
   502       else
   503       {
   504         return -1;
   505       }
   506     }
   507     catch(SQLException ex)
   508     {
   509       restartConnection(ex);
   510       return countGroups();
   511     }
   512     finally
   513     {
   514       if(rs != null)
   515       {
   516         try
   517         {
   518           rs.close();
   519         }
   520         catch(SQLException ex)
   521         {
   522           ex.printStackTrace();
   523         }
   524         restarts = 0;
   525       }
   526     }
   527   }
   528 
   529   @Override
   530   public void delete(final String messageID)
   531     throws StorageBackendException
   532   {
   533     try
   534     {
   535       this.conn.setAutoCommit(false);
   536       
   537       this.pstmtDeleteArticle0.setString(1, messageID);
   538       int rs = this.pstmtDeleteArticle0.executeUpdate();
   539       
   540       // We do not trust the ON DELETE CASCADE functionality to delete
   541       // orphaned references...
   542       this.pstmtDeleteArticle1.setString(1, messageID);
   543       rs = this.pstmtDeleteArticle1.executeUpdate();
   544 
   545       this.pstmtDeleteArticle2.setString(1, messageID);
   546       rs = this.pstmtDeleteArticle2.executeUpdate();
   547 
   548       this.pstmtDeleteArticle3.setString(1, messageID);
   549       rs = this.pstmtDeleteArticle3.executeUpdate();
   550       
   551       this.conn.commit();
   552       this.conn.setAutoCommit(true);
   553     }
   554     catch(SQLException ex)
   555     {
   556       throw new StorageBackendException(ex);
   557     }
   558   }
   559 
   560   @Override
   561   public Article getArticle(String messageID)
   562     throws StorageBackendException
   563   {
   564     ResultSet rs = null;
   565     try
   566     {
   567       pstmtGetArticle0.setString(1, messageID);
   568       rs = pstmtGetArticle0.executeQuery();
   569 
   570       if(!rs.next())
   571       {
   572         return null;
   573       }
   574       else
   575       {
   576         byte[] body     = rs.getBytes("body");
   577         String headers  = getArticleHeaders(rs.getInt("article_id"));
   578         return new Article(headers, body);
   579       }
   580     }
   581     catch(SQLException ex)
   582     {
   583       restartConnection(ex);
   584       return getArticle(messageID);
   585     }
   586     finally
   587     {
   588       if(rs != null)
   589       {
   590         try
   591         {
   592           rs.close();
   593         }
   594         catch(SQLException ex)
   595         {
   596           ex.printStackTrace();
   597         }
   598         restarts = 0; // Reset error count
   599       }
   600     }
   601   }
   602   
   603   /**
   604    * Retrieves an article by its ID.
   605    * @param articleID
   606    * @return
   607    * @throws StorageBackendException
   608    */
   609   @Override
   610   public Article getArticle(long articleIndex, long gid)
   611     throws StorageBackendException
   612   {  
   613     ResultSet rs = null;
   614 
   615     try
   616     {
   617       this.pstmtGetArticle1.setLong(1, articleIndex);
   618       this.pstmtGetArticle1.setLong(2, gid);
   619 
   620       rs = this.pstmtGetArticle1.executeQuery();
   621 
   622       if(rs.next())
   623       {
   624         byte[] body    = rs.getBytes("body");
   625         String headers = getArticleHeaders(rs.getInt("article_id"));
   626         return new Article(headers, body);
   627       }
   628       else
   629       {
   630         return null;
   631       }
   632     }
   633     catch(SQLException ex)
   634     {
   635       restartConnection(ex);
   636       return getArticle(articleIndex, gid);
   637     }
   638     finally
   639     {
   640       if(rs != null)
   641       {
   642         try
   643         {
   644           rs.close();
   645         }
   646         catch(SQLException ex)
   647         {
   648           ex.printStackTrace();
   649         }
   650         restarts = 0;
   651       }
   652     }
   653   }
   654 
   655   /**
   656    * Searches for fitting header values using the given regular expression.
   657    * @param group
   658    * @param start
   659    * @param end
   660    * @param headerKey
   661    * @param pattern
   662    * @return
   663    * @throws StorageBackendException
   664    */
   665   @Override
   666   public List<Pair<Long, String>> getArticleHeaders(Channel group, long start,
   667     long end, String headerKey, String patStr)
   668     throws StorageBackendException, PatternSyntaxException
   669   {
   670     ResultSet rs = null;
   671     List<Pair<Long, String>> heads = new ArrayList<Pair<Long, String>>();
   672 
   673     try
   674     {
   675       this.pstmtGetArticleHeaders1.setString(1, group.getName());
   676       this.pstmtGetArticleHeaders1.setString(2, headerKey);
   677       this.pstmtGetArticleHeaders1.setLong(3, start);
   678 
   679       rs = this.pstmtGetArticleHeaders1.executeQuery();
   680 
   681       // Convert the "NNTP" regex to Java regex
   682       patStr = patStr.replace("*", ".*");
   683       Pattern pattern = Pattern.compile(patStr);
   684 
   685       while(rs.next())
   686       {
   687         Long articleIndex = rs.getLong(1);
   688         if(end < 0 || articleIndex <= end) // Match start is done via SQL
   689         {
   690           String headerValue  = rs.getString(2);
   691           Matcher matcher = pattern.matcher(headerValue);
   692           if(matcher.matches())
   693           {
   694             heads.add(new Pair<Long, String>(articleIndex, headerValue));
   695           }
   696         }
   697       }
   698     }
   699     catch(SQLException ex)
   700     {
   701       restartConnection(ex);
   702       return getArticleHeaders(group, start, end, headerKey, patStr);
   703     }
   704     finally
   705     {
   706       if(rs != null)
   707       {
   708         try
   709         {
   710           rs.close();
   711         }
   712         catch(SQLException ex)
   713         {
   714           ex.printStackTrace();
   715         }
   716       }
   717     }
   718 
   719     return heads;
   720   }
   721 
   722   private String getArticleHeaders(long articleID)
   723     throws StorageBackendException
   724   {
   725     ResultSet rs = null;
   726     
   727     try
   728     {
   729       this.pstmtGetArticleHeaders0.setLong(1, articleID);
   730       rs = this.pstmtGetArticleHeaders0.executeQuery();
   731       
   732       StringBuilder buf = new StringBuilder();
   733       if(rs.next())
   734       {
   735         for(;;)
   736         {
   737           buf.append(rs.getString(1)); // key
   738           buf.append(": ");
   739           String foldedValue = MimeUtility.fold(0, rs.getString(2));
   740           buf.append(foldedValue); // value
   741           if(rs.next())
   742           {
   743             buf.append("\r\n");
   744           }
   745           else
   746           {
   747             break;
   748           }
   749         }
   750       }
   751       
   752       return buf.toString();
   753     }
   754     catch(SQLException ex)
   755     {
   756       restartConnection(ex);
   757       return getArticleHeaders(articleID);
   758     }
   759     finally
   760     {
   761       if(rs != null)
   762       {
   763         try
   764         {
   765           rs.close();
   766         }
   767         catch(SQLException ex)
   768         {
   769           ex.printStackTrace();
   770         }
   771       }
   772     }
   773   }
   774 
   775   @Override
   776   public long getArticleIndex(Article article, Group group)
   777     throws StorageBackendException
   778   {
   779     ResultSet rs = null;
   780 
   781     try
   782     {
   783       this.pstmtGetArticleIndex.setString(1, article.getMessageID());
   784       this.pstmtGetArticleIndex.setLong(2, group.getInternalID());
   785       
   786       rs = this.pstmtGetArticleIndex.executeQuery();
   787       if(rs.next())
   788       {
   789         return rs.getLong(1);
   790       }
   791       else
   792       {
   793         return -1;
   794       }
   795     }
   796     catch(SQLException ex)
   797     {
   798       restartConnection(ex);
   799       return getArticleIndex(article, group);
   800     }
   801     finally
   802     {
   803       if(rs != null)
   804       {
   805         try
   806         {
   807           rs.close();
   808         }
   809         catch(SQLException ex)
   810         {
   811           ex.printStackTrace();
   812         }
   813       }
   814     }
   815   }
   816   
   817   /**
   818    * Returns a list of Long/Article Pairs.
   819    * @throws java.sql.SQLException
   820    */
   821   @Override
   822   public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first,
   823     long last)
   824     throws StorageBackendException
   825   {
   826     ResultSet rs = null;
   827 
   828     try
   829     {
   830       this.pstmtGetArticleHeads.setLong(1, group.getInternalID());
   831       this.pstmtGetArticleHeads.setLong(2, first);
   832       this.pstmtGetArticleHeads.setLong(3, last);
   833       rs = pstmtGetArticleHeads.executeQuery();
   834 
   835       List<Pair<Long, ArticleHead>> articles 
   836         = new ArrayList<Pair<Long, ArticleHead>>();
   837 
   838       while (rs.next())
   839       {
   840         long aid  = rs.getLong("article_id");
   841         long aidx = rs.getLong("article_index");
   842         String headers = getArticleHeaders(aid);
   843         articles.add(new Pair<Long, ArticleHead>(aidx, 
   844                         new ArticleHead(headers)));
   845       }
   846 
   847       return articles;
   848     }
   849     catch(SQLException ex)
   850     {
   851       restartConnection(ex);
   852       return getArticleHeads(group, first, last);
   853     }
   854     finally
   855     {
   856       if(rs != null)
   857       {
   858         try
   859         {
   860           rs.close();
   861         }
   862         catch(SQLException ex)
   863         {
   864           ex.printStackTrace();
   865         }
   866       }
   867     }
   868   }
   869 
   870   @Override
   871   public List<Long> getArticleNumbers(long gid)
   872     throws StorageBackendException
   873   {
   874     ResultSet rs = null;
   875     try
   876     {
   877       List<Long> ids = new ArrayList<Long>();
   878       this.pstmtGetArticleIDs.setLong(1, gid);
   879       rs = this.pstmtGetArticleIDs.executeQuery();
   880       while(rs.next())
   881       {
   882         ids.add(rs.getLong(1));
   883       }
   884       return ids;
   885     }
   886     catch(SQLException ex)
   887     {
   888       restartConnection(ex);
   889       return getArticleNumbers(gid);
   890     }
   891     finally
   892     {
   893       if(rs != null)
   894       {
   895         try
   896         {
   897           rs.close();
   898           restarts = 0; // Clear the restart count after successful request
   899         }
   900         catch(SQLException ex)
   901         {
   902           ex.printStackTrace();
   903         }
   904       }
   905     }
   906   }
   907 
   908   @Override
   909   public String getConfigValue(String key)
   910     throws StorageBackendException
   911   {
   912     ResultSet rs = null;
   913     try
   914     {
   915       this.pstmtGetConfigValue.setString(1, key);
   916 
   917       rs = this.pstmtGetConfigValue.executeQuery();
   918       if(rs.next())
   919       {
   920         return rs.getString(1); // First data on index 1 not 0
   921       }
   922       else
   923       {
   924         return null;
   925       }
   926     }
   927     catch(SQLException ex)
   928     {
   929       restartConnection(ex);
   930       return getConfigValue(key);
   931     }
   932     finally
   933     {
   934       if(rs != null)
   935       {
   936         try
   937         {
   938           rs.close();
   939         }
   940         catch(SQLException ex)
   941         {
   942           ex.printStackTrace();
   943         }
   944         restarts = 0; // Clear the restart count after successful request
   945       }
   946     }
   947   }
   948 
   949   @Override
   950   public int getEventsCount(int type, long start, long end, Channel channel)
   951     throws StorageBackendException
   952   {
   953     ResultSet rs = null;
   954     
   955     try
   956     {
   957       if(channel == null)
   958       {
   959         this.pstmtGetEventsCount0.setInt(1, type);
   960         this.pstmtGetEventsCount0.setLong(2, start);
   961         this.pstmtGetEventsCount0.setLong(3, end);
   962         rs = this.pstmtGetEventsCount0.executeQuery();
   963       }
   964       else
   965       {
   966         this.pstmtGetEventsCount1.setInt(1, type);
   967         this.pstmtGetEventsCount1.setLong(2, start);
   968         this.pstmtGetEventsCount1.setLong(3, end);
   969         this.pstmtGetEventsCount1.setLong(4, channel.getInternalID());
   970         rs = this.pstmtGetEventsCount1.executeQuery();
   971       }
   972       
   973       if(rs.next())
   974       {
   975         return rs.getInt(1);
   976       }
   977       else
   978       {
   979         return -1;
   980       }
   981     }
   982     catch(SQLException ex)
   983     {
   984       restartConnection(ex);
   985       return getEventsCount(type, start, end, channel);
   986     }
   987     finally
   988     {
   989       if(rs != null)
   990       {
   991         try
   992         {
   993           rs.close();
   994         }
   995         catch(SQLException ex)
   996         {
   997           ex.printStackTrace();
   998         }
   999       }
  1000     }
  1001   }
  1002   
  1003   /**
  1004    * Reads all Groups from the JDBCDatabase.
  1005    * @return
  1006    * @throws StorageBackendException
  1007    */
  1008   @Override
  1009   public List<Channel> getGroups()
  1010     throws StorageBackendException
  1011   {
  1012     ResultSet   rs;
  1013     List<Channel> buffer = new ArrayList<Channel>();
  1014     Statement   stmt   = null;
  1015 
  1016     try
  1017     {
  1018       stmt = conn.createStatement();
  1019       rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
  1020 
  1021       while(rs.next())
  1022       {
  1023         String name  = rs.getString("name");
  1024         long   id    = rs.getLong("group_id");
  1025         int    flags = rs.getInt("flags");
  1026         
  1027         Group group = new Group(name, id, flags);
  1028         buffer.add(group);
  1029       }
  1030 
  1031       return buffer;
  1032     }
  1033     catch(SQLException ex)
  1034     {
  1035       restartConnection(ex);
  1036       return getGroups();
  1037     }
  1038     finally
  1039     {
  1040       if(stmt != null)
  1041       {
  1042         try
  1043         {
  1044           stmt.close(); // Implicitely closes ResultSets
  1045         }
  1046         catch(SQLException ex)
  1047         {
  1048           ex.printStackTrace();
  1049         }
  1050       }
  1051     }
  1052   }
  1053 
  1054   @Override
  1055   public List<String> getGroupsForList(String listAddress)
  1056     throws StorageBackendException
  1057   {
  1058     ResultSet rs = null;
  1059     
  1060     try
  1061     {
  1062       this.pstmtGetGroupForList.setString(1, listAddress);
  1063 
  1064       rs = this.pstmtGetGroupForList.executeQuery();
  1065       List<String> groups = new ArrayList<String>();
  1066       while(rs.next())
  1067       {
  1068         String group = rs.getString(1);
  1069         groups.add(group);
  1070       }
  1071       return groups;
  1072     }
  1073     catch(SQLException ex)
  1074     {
  1075       restartConnection(ex);
  1076       return getGroupsForList(listAddress);
  1077     }
  1078     finally
  1079     {
  1080       if(rs != null)
  1081       {
  1082         try
  1083         {
  1084           rs.close();
  1085         }
  1086         catch(SQLException ex)
  1087         {
  1088           ex.printStackTrace();
  1089         }
  1090       }
  1091     }
  1092   }
  1093   
  1094   /**
  1095    * Returns the Group that is identified by the name.
  1096    * @param name
  1097    * @return
  1098    * @throws StorageBackendException
  1099    */
  1100   @Override
  1101   public Group getGroup(String name)
  1102     throws StorageBackendException
  1103   {
  1104     ResultSet rs = null;
  1105     
  1106     try
  1107     {
  1108       this.pstmtGetGroup0.setString(1, name);
  1109       rs = this.pstmtGetGroup0.executeQuery();
  1110 
  1111       if (!rs.next())
  1112       {
  1113         return null;
  1114       }
  1115       else
  1116       {
  1117         long id = rs.getLong("group_id");
  1118         int flags = rs.getInt("flags");
  1119         return new Group(name, id, flags);
  1120       }
  1121     }
  1122     catch(SQLException ex)
  1123     {
  1124       restartConnection(ex);
  1125       return getGroup(name);
  1126     }
  1127     finally
  1128     {
  1129       if(rs != null)
  1130       {
  1131         try
  1132         {
  1133           rs.close();
  1134         }
  1135         catch(SQLException ex)
  1136         {
  1137           ex.printStackTrace();
  1138         }
  1139       }
  1140     }
  1141   }
  1142 
  1143   @Override
  1144   public List<String> getListsForGroup(String group)
  1145     throws StorageBackendException
  1146   {
  1147     ResultSet     rs    = null;
  1148     List<String>  lists = new ArrayList<String>();
  1149 
  1150     try
  1151     {
  1152       this.pstmtGetListForGroup.setString(1, group);
  1153       rs = this.pstmtGetListForGroup.executeQuery();
  1154 
  1155       while(rs.next())
  1156       {
  1157         lists.add(rs.getString(1));
  1158       }
  1159       return lists;
  1160     }
  1161     catch(SQLException ex)
  1162     {
  1163       restartConnection(ex);
  1164       return getListsForGroup(group);
  1165     }
  1166     finally
  1167     {
  1168       if(rs != null)
  1169       {
  1170         try
  1171         {
  1172           rs.close();
  1173         }
  1174         catch(SQLException ex)
  1175         {
  1176           ex.printStackTrace();
  1177         }
  1178       }
  1179     }
  1180   }
  1181   
  1182   private int getMaxArticleIndex(long groupID)
  1183     throws StorageBackendException
  1184   {
  1185     ResultSet rs    = null;
  1186 
  1187     try
  1188     {
  1189       this.pstmtGetMaxArticleIndex.setLong(1, groupID);
  1190       rs = this.pstmtGetMaxArticleIndex.executeQuery();
  1191 
  1192       int maxIndex = 0;
  1193       if (rs.next())
  1194       {
  1195         maxIndex = rs.getInt(1);
  1196       }
  1197 
  1198       return maxIndex;
  1199     }
  1200     catch(SQLException ex)
  1201     {
  1202       restartConnection(ex);
  1203       return getMaxArticleIndex(groupID);
  1204     }
  1205     finally
  1206     {
  1207       if(rs != null)
  1208       {
  1209         try
  1210         {
  1211           rs.close();
  1212         }
  1213         catch(SQLException ex)
  1214         {
  1215           ex.printStackTrace();
  1216         }
  1217       }
  1218     }
  1219   }
  1220   
  1221   private int getMaxArticleID()
  1222     throws StorageBackendException
  1223   {
  1224     ResultSet rs    = null;
  1225 
  1226     try
  1227     {
  1228       rs = this.pstmtGetMaxArticleID.executeQuery();
  1229 
  1230       int maxIndex = 0;
  1231       if (rs.next())
  1232       {
  1233         maxIndex = rs.getInt(1);
  1234       }
  1235 
  1236       return maxIndex;
  1237     }
  1238     catch(SQLException ex)
  1239     {
  1240       restartConnection(ex);
  1241       return getMaxArticleID();
  1242     }
  1243     finally
  1244     {
  1245       if(rs != null)
  1246       {
  1247         try
  1248         {
  1249           rs.close();
  1250         }
  1251         catch(SQLException ex)
  1252         {
  1253           ex.printStackTrace();
  1254         }
  1255       }
  1256     }
  1257   }
  1258 
  1259   @Override
  1260   public int getLastArticleNumber(Group group)
  1261     throws StorageBackendException
  1262   {
  1263     ResultSet rs = null;
  1264 
  1265     try
  1266     {
  1267       this.pstmtGetLastArticleNumber.setLong(1, group.getInternalID());
  1268       rs = this.pstmtGetLastArticleNumber.executeQuery();
  1269       if (rs.next())
  1270       {
  1271         return rs.getInt(1);
  1272       }
  1273       else
  1274       {
  1275         return 0;
  1276       }
  1277     }
  1278     catch(SQLException ex)
  1279     {
  1280       restartConnection(ex);
  1281       return getLastArticleNumber(group);
  1282     }
  1283     finally
  1284     {
  1285       if(rs != null)
  1286       {
  1287         try
  1288         {
  1289           rs.close();
  1290         }
  1291         catch(SQLException ex)
  1292         {
  1293           ex.printStackTrace();
  1294         }
  1295       }
  1296     }
  1297   }
  1298 
  1299   @Override
  1300   public int getFirstArticleNumber(Group group)
  1301     throws StorageBackendException
  1302   {
  1303     ResultSet rs = null;
  1304     try
  1305     {
  1306       this.pstmtGetFirstArticleNumber.setLong(1, group.getInternalID());
  1307       rs = this.pstmtGetFirstArticleNumber.executeQuery();
  1308       if(rs.next())
  1309       {
  1310         return rs.getInt(1);
  1311       }
  1312       else
  1313       {
  1314         return 0;
  1315       }
  1316     }
  1317     catch(SQLException ex)
  1318     {
  1319       restartConnection(ex);
  1320       return getFirstArticleNumber(group);
  1321     }
  1322     finally
  1323     {
  1324       if(rs != null)
  1325       {
  1326         try
  1327         {
  1328           rs.close();
  1329         }
  1330         catch(SQLException ex)
  1331         {
  1332           ex.printStackTrace();
  1333         }
  1334       }
  1335     }
  1336   }
  1337   
  1338   /**
  1339    * Returns a group name identified by the given id.
  1340    * @param id
  1341    * @return
  1342    * @throws StorageBackendException
  1343    */
  1344   public String getGroup(int id)
  1345     throws StorageBackendException
  1346   {
  1347     ResultSet rs = null;
  1348 
  1349     try
  1350     {
  1351       this.pstmtGetGroup1.setInt(1, id);
  1352       rs = this.pstmtGetGroup1.executeQuery();
  1353 
  1354       if (rs.next())
  1355       {
  1356         return rs.getString(1);
  1357       }
  1358       else
  1359       {
  1360         return null;
  1361       }
  1362     }
  1363     catch(SQLException ex)
  1364     {
  1365       restartConnection(ex);
  1366       return getGroup(id);
  1367     }
  1368     finally
  1369     {
  1370       if(rs != null)
  1371       {
  1372         try
  1373         {
  1374           rs.close();
  1375         }
  1376         catch(SQLException ex)
  1377         {
  1378           ex.printStackTrace();
  1379         }
  1380       }
  1381     }
  1382   }
  1383 
  1384   @Override
  1385   public double getEventsPerHour(int key, long gid)
  1386     throws StorageBackendException
  1387   {
  1388     String gidquery = "";
  1389     if(gid >= 0)
  1390     {
  1391       gidquery = " AND group_id = " + gid;
  1392     }
  1393     
  1394     Statement stmt = null;
  1395     ResultSet rs   = null;
  1396     
  1397     try
  1398     {
  1399       stmt = this.conn.createStatement();
  1400       rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))" +
  1401         " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
  1402       
  1403       if(rs.next())
  1404       {
  1405         restarts = 0; // reset error count
  1406         return rs.getDouble(1);
  1407       }
  1408       else
  1409       {
  1410         return Double.NaN;
  1411       }
  1412     }
  1413     catch(SQLException ex)
  1414     {
  1415       restartConnection(ex);
  1416       return getEventsPerHour(key, gid);
  1417     }
  1418     finally
  1419     {
  1420       try
  1421       {
  1422         if(stmt != null)
  1423         {
  1424           stmt.close(); // Implicitely closes the result sets
  1425         }
  1426       }
  1427       catch(SQLException ex)
  1428       {
  1429         ex.printStackTrace();
  1430       }
  1431     }
  1432   }
  1433 
  1434   @Override
  1435   public String getOldestArticle()
  1436     throws StorageBackendException
  1437   {
  1438     ResultSet rs = null;
  1439 
  1440     try
  1441     {
  1442       rs = this.pstmtGetOldestArticle.executeQuery();
  1443       if(rs.next())
  1444       {
  1445         return rs.getString(1);
  1446       }
  1447       else
  1448       {
  1449         return null;
  1450       }
  1451     }
  1452     catch(SQLException ex)
  1453     {
  1454       restartConnection(ex);
  1455       return getOldestArticle();
  1456     }
  1457     finally
  1458     {
  1459       if(rs != null)
  1460       {
  1461         try
  1462         {
  1463           rs.close();
  1464         }
  1465         catch(SQLException ex)
  1466         {
  1467           ex.printStackTrace();
  1468         }
  1469       }
  1470     }
  1471   }
  1472 
  1473   @Override
  1474   public int getPostingsCount(String groupname)
  1475     throws StorageBackendException
  1476   {
  1477     ResultSet rs = null;
  1478     
  1479     try
  1480     {
  1481       this.pstmtGetPostingsCount.setString(1, groupname);
  1482       rs = this.pstmtGetPostingsCount.executeQuery();
  1483       if(rs.next())
  1484       {
  1485         return rs.getInt(1);
  1486       }
  1487       else
  1488       {
  1489         Log.get().warning("Count on postings return nothing!");
  1490         return 0;
  1491       }
  1492     }
  1493     catch(SQLException ex)
  1494     {
  1495       restartConnection(ex);
  1496       return getPostingsCount(groupname);
  1497     }
  1498     finally
  1499     {
  1500       if(rs != null)
  1501       {
  1502         try
  1503         {
  1504           rs.close();
  1505         }
  1506         catch(SQLException ex)
  1507         {
  1508           ex.printStackTrace();
  1509         }
  1510       }
  1511     }
  1512   }
  1513 
  1514   @Override
  1515   public List<Subscription> getSubscriptions(int feedtype)
  1516     throws StorageBackendException
  1517   {
  1518     ResultSet rs = null;
  1519     
  1520     try
  1521     {
  1522       List<Subscription> subs = new ArrayList<Subscription>();
  1523       this.pstmtGetSubscriptions.setInt(1, feedtype);
  1524       rs = this.pstmtGetSubscriptions.executeQuery();
  1525       
  1526       while(rs.next())
  1527       {
  1528         String host  = rs.getString("host");
  1529         String group = rs.getString("name");
  1530         int    port  = rs.getInt("port");
  1531         subs.add(new Subscription(host, port, feedtype, group));
  1532       }
  1533       
  1534       return subs;
  1535     }
  1536     catch(SQLException ex)
  1537     {
  1538       restartConnection(ex);
  1539       return getSubscriptions(feedtype);
  1540     }
  1541     finally
  1542     {
  1543       if(rs != null)
  1544       {
  1545         try
  1546         {
  1547           rs.close();
  1548         }
  1549         catch(SQLException ex)
  1550         {
  1551           ex.printStackTrace();
  1552         }
  1553       }
  1554     }
  1555   }
  1556 
  1557   /**
  1558    * Checks if there is an article with the given messageid in the JDBCDatabase.
  1559    * @param name
  1560    * @return
  1561    * @throws StorageBackendException
  1562    */
  1563   @Override
  1564   public boolean isArticleExisting(String messageID)
  1565     throws StorageBackendException
  1566   {
  1567     ResultSet rs = null;
  1568     
  1569     try
  1570     {
  1571       this.pstmtIsArticleExisting.setString(1, messageID);
  1572       rs = this.pstmtIsArticleExisting.executeQuery();
  1573       return rs.next() && rs.getInt(1) == 1;
  1574     }
  1575     catch(SQLException ex)
  1576     {
  1577       restartConnection(ex);
  1578       return isArticleExisting(messageID);
  1579     }
  1580     finally
  1581     {
  1582       if(rs != null)
  1583       {
  1584         try
  1585         {
  1586           rs.close();
  1587         }
  1588         catch(SQLException ex)
  1589         {
  1590           ex.printStackTrace();
  1591         }
  1592       }
  1593     }
  1594   }
  1595   
  1596   /**
  1597    * Checks if there is a group with the given name in the JDBCDatabase.
  1598    * @param name
  1599    * @return
  1600    * @throws StorageBackendException
  1601    */
  1602   @Override
  1603   public boolean isGroupExisting(String name)
  1604     throws StorageBackendException
  1605   {
  1606     ResultSet rs = null;
  1607     
  1608     try
  1609     {
  1610       this.pstmtIsGroupExisting.setString(1, name);
  1611       rs = this.pstmtIsGroupExisting.executeQuery();
  1612       return rs.next();
  1613     }
  1614     catch(SQLException ex)
  1615     {
  1616       restartConnection(ex);
  1617       return isGroupExisting(name);
  1618     }
  1619     finally
  1620     {
  1621       if(rs != null)
  1622       {
  1623         try
  1624         {
  1625           rs.close();
  1626         }
  1627         catch(SQLException ex)
  1628         {
  1629           ex.printStackTrace();
  1630         }
  1631       }
  1632     }
  1633   }
  1634 
  1635   @Override
  1636   public void setConfigValue(String key, String value)
  1637     throws StorageBackendException
  1638   {
  1639     try
  1640     {
  1641       conn.setAutoCommit(false);
  1642       this.pstmtSetConfigValue0.setString(1, key);
  1643       this.pstmtSetConfigValue0.execute();
  1644       this.pstmtSetConfigValue1.setString(1, key);
  1645       this.pstmtSetConfigValue1.setString(2, value);
  1646       this.pstmtSetConfigValue1.execute();
  1647       conn.commit();
  1648       conn.setAutoCommit(true);
  1649     }
  1650     catch(SQLException ex)
  1651     {
  1652       restartConnection(ex);
  1653       setConfigValue(key, value);
  1654     }
  1655   }
  1656   
  1657   /**
  1658    * Closes the JDBCDatabase connection.
  1659    */
  1660   public void shutdown()
  1661     throws StorageBackendException
  1662   {
  1663     try
  1664     {
  1665       if(this.conn != null)
  1666       {
  1667         this.conn.close();
  1668       }
  1669     }
  1670     catch(SQLException ex)
  1671     {
  1672       throw new StorageBackendException(ex);
  1673     }
  1674   }
  1675 
  1676   @Override
  1677   public void purgeGroup(Group group)
  1678     throws StorageBackendException
  1679   {
  1680     try
  1681     {
  1682       this.pstmtPurgeGroup0.setLong(1, group.getInternalID());
  1683       this.pstmtPurgeGroup0.executeUpdate();
  1684 
  1685       this.pstmtPurgeGroup1.setLong(1, group.getInternalID());
  1686       this.pstmtPurgeGroup1.executeUpdate();
  1687     }
  1688     catch(SQLException ex)
  1689     {
  1690       restartConnection(ex);
  1691       purgeGroup(group);
  1692     }
  1693   }
  1694   
  1695   private void restartConnection(SQLException cause)
  1696     throws StorageBackendException
  1697   {
  1698     restarts++;
  1699     Log.get().severe(Thread.currentThread()
  1700       + ": Database connection was closed (restart " + restarts + ").");
  1701     
  1702     if(restarts >= MAX_RESTARTS)
  1703     {
  1704       // Delete the current, probably broken JDBCDatabase instance.
  1705       // So no one can use the instance any more.
  1706       JDBCDatabaseProvider.instances.remove(Thread.currentThread());
  1707       
  1708       // Throw the exception upwards
  1709       throw new StorageBackendException(cause);
  1710     }
  1711     
  1712     try
  1713     {
  1714       Thread.sleep(1500L * restarts);
  1715     }
  1716     catch(InterruptedException ex)
  1717     {
  1718       Log.get().warning("Interrupted: " + ex.getMessage());
  1719     }
  1720     
  1721     // Try to properly close the old database connection
  1722     try
  1723     {
  1724       if(this.conn != null)
  1725       {
  1726         this.conn.close();
  1727       }
  1728     }
  1729     catch(SQLException ex)
  1730     {
  1731       Log.get().warning(ex.getMessage());
  1732     }
  1733     
  1734     try
  1735     {
  1736       // Try to reinitialize database connection
  1737       arise();
  1738     }
  1739     catch(SQLException ex)
  1740     {
  1741       Log.get().warning(ex.getMessage());
  1742       restartConnection(ex);
  1743     }
  1744   }
  1745 
  1746   /**
  1747    * Writes the flags and the name of the given group to the database.
  1748    * @param group
  1749    * @throws StorageBackendException
  1750    */
  1751   @Override
  1752   public boolean update(Group group)
  1753     throws StorageBackendException
  1754   {
  1755     try
  1756     {
  1757       this.pstmtUpdateGroup.setInt(1, group.getFlags());
  1758       this.pstmtUpdateGroup.setString(2, group.getName());
  1759       this.pstmtUpdateGroup.setLong(3, group.getInternalID());
  1760       int rs = this.pstmtUpdateGroup.executeUpdate();
  1761       return rs == 1;
  1762     }
  1763     catch(SQLException ex)
  1764     {
  1765       restartConnection(ex);
  1766       return update(group);
  1767     }
  1768   }
  1769 
  1770 }