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