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