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