org/sonews/daemon/storage/Database.java
author chris <chris@marvin>
Fri, 26 Jun 2009 16:48:50 +0200
changeset 1 6fceb66e1ad7
child 2 1090e2141798
permissions -rw-r--r--
Hooray... sonews/0.5.0 final

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