chris@3: /*
chris@3:  *   SONEWS News Server
chris@3:  *   see AUTHORS for the list of contributors
chris@3:  *
chris@3:  *   This program is free software: you can redistribute it and/or modify
chris@3:  *   it under the terms of the GNU General Public License as published by
chris@3:  *   the Free Software Foundation, either version 3 of the License, or
chris@3:  *   (at your option) any later version.
chris@3:  *
chris@3:  *   This program is distributed in the hope that it will be useful,
chris@3:  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
chris@3:  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
chris@3:  *   GNU General Public License for more details.
chris@3:  *
chris@3:  *   You should have received a copy of the GNU General Public License
chris@3:  *   along with this program.  If not, see <http://www.gnu.org/licenses/>.
chris@3:  */
chris@3: 
chris@3: package org.sonews.storage.impl;
chris@3: 
chris@3: import java.sql.Connection;
chris@3: import java.sql.DriverManager;
chris@3: import java.sql.ResultSet;
chris@3: import java.sql.SQLException;
chris@3: import java.sql.Statement;
chris@3: import java.sql.PreparedStatement;
chris@3: import java.util.ArrayList;
chris@3: import java.util.Enumeration;
chris@3: import java.util.List;
cli@45: import java.util.logging.Level;
chris@3: import java.util.regex.Matcher;
chris@3: import java.util.regex.Pattern;
chris@3: import java.util.regex.PatternSyntaxException;
chris@3: import javax.mail.Header;
chris@3: import javax.mail.internet.MimeUtility;
chris@3: import org.sonews.config.Config;
chris@3: import org.sonews.util.Log;
chris@3: import org.sonews.feed.Subscription;
chris@3: import org.sonews.storage.Article;
chris@3: import org.sonews.storage.ArticleHead;
chris@3: import org.sonews.storage.Channel;
chris@3: import org.sonews.storage.Group;
chris@3: import org.sonews.storage.Storage;
chris@3: import org.sonews.storage.StorageBackendException;
chris@3: import org.sonews.util.Pair;
chris@3: 
chris@3: /**
chris@3:  * JDBCDatabase facade class.
chris@3:  * @author Christian Lins
chris@3:  * @since sonews/0.5.0
chris@3:  */
chris@3: // TODO: Refactor this class to reduce size (e.g. ArticleDatabase GroupDatabase)
chris@3: public class JDBCDatabase implements Storage
chris@3: {
chris@3: 
cli@37: 	public static final int MAX_RESTARTS = 2;
cli@44: 	protected Connection conn = null;
cli@44: 	protected PreparedStatement pstmtAddArticle1 = null;
cli@44: 	protected PreparedStatement pstmtAddArticle2 = null;
cli@44: 	protected PreparedStatement pstmtAddArticle3 = null;
cli@44: 	protected PreparedStatement pstmtAddArticle4 = null;
cli@44: 	protected PreparedStatement pstmtAddGroup0 = null;
cli@44: 	protected PreparedStatement pstmtAddEvent = null;
cli@44: 	protected PreparedStatement pstmtCountArticles = null;
cli@44: 	protected PreparedStatement pstmtCountGroups = null;
cli@44: 	protected PreparedStatement pstmtDeleteArticle0 = null;
cli@44: 	protected PreparedStatement pstmtDeleteArticle1 = null;
cli@44: 	protected PreparedStatement pstmtDeleteArticle2 = null;
cli@44: 	protected PreparedStatement pstmtDeleteArticle3 = null;
cli@44: 	protected PreparedStatement pstmtGetArticle0 = null;
cli@44: 	protected PreparedStatement pstmtGetArticle1 = null;
cli@44: 	protected PreparedStatement pstmtGetArticleHeaders0 = null;
cli@44: 	protected PreparedStatement pstmtGetArticleHeaders1 = null;
cli@44: 	protected PreparedStatement pstmtGetArticleHeads = null;
cli@44: 	protected PreparedStatement pstmtGetArticleIDs = null;
cli@44: 	protected PreparedStatement pstmtGetArticleIndex = null;
cli@44: 	protected PreparedStatement pstmtGetConfigValue = null;
cli@44: 	protected PreparedStatement pstmtGetEventsCount0 = null;
cli@44: 	protected PreparedStatement pstmtGetEventsCount1 = null;
cli@44: 	protected PreparedStatement pstmtGetGroupForList = null;
cli@44: 	protected PreparedStatement pstmtGetGroup0 = null;
cli@44: 	protected PreparedStatement pstmtGetGroup1 = null;
cli@44: 	protected PreparedStatement pstmtGetFirstArticleNumber = null;
cli@44: 	protected PreparedStatement pstmtGetListForGroup = null;
cli@44: 	protected PreparedStatement pstmtGetLastArticleNumber = null;
cli@44: 	protected PreparedStatement pstmtGetMaxArticleID = null;
cli@44: 	protected PreparedStatement pstmtGetMaxArticleIndex = null;
cli@44: 	protected PreparedStatement pstmtGetOldestArticle = null;
cli@44: 	protected PreparedStatement pstmtGetPostingsCount = null;
cli@44: 	protected PreparedStatement pstmtGetSubscriptions = null;
cli@44: 	protected PreparedStatement pstmtIsArticleExisting = null;
cli@44: 	protected PreparedStatement pstmtIsGroupExisting = null;
cli@44: 	protected PreparedStatement pstmtPurgeGroup0 = null;
cli@44: 	protected PreparedStatement pstmtPurgeGroup1 = null;
cli@44: 	protected PreparedStatement pstmtSetConfigValue0 = null;
cli@44: 	protected PreparedStatement pstmtSetConfigValue1 = null;
cli@44: 	protected PreparedStatement pstmtUpdateGroup = null;
cli@37: 	/** How many times the database connection was reinitialized */
cli@44: 	protected int restarts = 0;
chris@3: 
cli@45: 	protected void prepareAddGroupStatement() throws SQLException {
cli@45: 		this.pstmtAddGroup0 = conn.prepareStatement(
cli@45: 				"INSERT INTO groups (name, flags) VALUES (?, ?)");
cli@45: 	}
cli@45: 
cli@45: 	protected void prepareCountGroupsStatement() throws SQLException {
cli@45: 		this.pstmtCountGroups = conn.prepareStatement(
cli@45: 				"SELECT Count(group_id) FROM groups WHERE "
cli@45: 				+ "flags & " + Channel.DELETED + " = 0");
cli@45: 	}
cli@45: 
cli@45: 	protected void prepareGetPostingsCountStatement() throws SQLException {
cli@45: 		this.pstmtGetPostingsCount = conn.prepareStatement(
cli@45: 				"SELECT Count(*) FROM postings NATURAL JOIN groups "
cli@45: 				+ "WHERE groups.name = ?");
cli@45: 	}
cli@45: 
cli@45: 	protected void prepareGetSubscriptionsStatement() throws SQLException {
cli@45: 		this.pstmtGetSubscriptions = conn.prepareStatement(
cli@45: 				"SELECT host, port, name FROM peers NATURAL JOIN "
cli@45: 				+ "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
cli@45: 	}
cli@45: 
cli@37: 	/**
cli@37: 	 * Rises the database: reconnect and recreate all prepared statements.
cli@37: 	 * @throws java.lang.SQLException
cli@37: 	 */
cli@37: 	protected void arise()
cli@37: 		throws SQLException
cli@37: 	{
cli@37: 		try {
cli@37: 			// Load database driver
cli@37: 			Class.forName(
cli@37: 				Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object"));
chris@3: 
cli@37: 			// Establish database connection
cli@37: 			this.conn = DriverManager.getConnection(
cli@37: 				Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>"),
cli@37: 				Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root"),
cli@37: 				Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, ""));
chris@3: 
cli@37: 			this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
cli@37: 			if (this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {
cli@37: 				Log.get().warning("Database is NOT fully serializable!");
cli@37: 			}
chris@3: 
cli@37: 			// Prepare statements for method addArticle()
cli@37: 			this.pstmtAddArticle1 = conn.prepareStatement(
cli@37: 				"INSERT INTO articles (article_id, body) VALUES(?, ?)");
cli@37: 			this.pstmtAddArticle2 = conn.prepareStatement(
cli@37: 				"INSERT INTO headers (article_id, header_key, header_value, header_index) "
cli@37: 				+ "VALUES (?, ?, ?, ?)");
cli@37: 			this.pstmtAddArticle3 = conn.prepareStatement(
cli@37: 				"INSERT INTO postings (group_id, article_id, article_index)"
cli@37: 				+ "VALUES (?, ?, ?)");
cli@37: 			this.pstmtAddArticle4 = conn.prepareStatement(
cli@37: 				"INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");
chris@3: 
cli@37: 			// Prepare statement for method addStatValue()
cli@37: 			this.pstmtAddEvent = conn.prepareStatement(
cli@37: 				"INSERT INTO events VALUES (?, ?, ?)");
chris@3: 
cli@37: 			// Prepare statement for method addGroup()
cli@45: 			prepareAddGroupStatement();
chris@3: 
cli@37: 			// Prepare statement for method countArticles()
cli@37: 			this.pstmtCountArticles = conn.prepareStatement(
cli@37: 				"SELECT Count(article_id) FROM article_ids");
chris@3: 
cli@37: 			// Prepare statement for method countGroups()
cli@45: 			prepareCountGroupsStatement();
chris@3: 
cli@37: 			// Prepare statements for method delete(article)
cli@37: 			this.pstmtDeleteArticle0 = conn.prepareStatement(
cli@37: 				"DELETE FROM articles WHERE article_id = "
cli@37: 				+ "(SELECT article_id FROM article_ids WHERE message_id = ?)");
cli@37: 			this.pstmtDeleteArticle1 = conn.prepareStatement(
cli@37: 				"DELETE FROM headers WHERE article_id = "
cli@37: 				+ "(SELECT article_id FROM article_ids WHERE message_id = ?)");
cli@37: 			this.pstmtDeleteArticle2 = conn.prepareStatement(
cli@37: 				"DELETE FROM postings WHERE article_id = "
cli@37: 				+ "(SELECT article_id FROM article_ids WHERE message_id = ?)");
cli@37: 			this.pstmtDeleteArticle3 = conn.prepareStatement(
cli@37: 				"DELETE FROM article_ids WHERE message_id = ?");
chris@3: 
cli@37: 			// Prepare statements for methods getArticle()
cli@37: 			this.pstmtGetArticle0 = conn.prepareStatement(
cli@37: 				"SELECT * FROM articles  WHERE article_id = "
cli@37: 				+ "(SELECT article_id FROM article_ids WHERE message_id = ?)");
cli@37: 			this.pstmtGetArticle1 = conn.prepareStatement(
cli@37: 				"SELECT * FROM articles WHERE article_id = "
cli@37: 				+ "(SELECT article_id FROM postings WHERE "
cli@37: 				+ "article_index = ? AND group_id = ?)");
chris@3: 
cli@37: 			// Prepare statement for method getArticleHeaders()
cli@37: 			this.pstmtGetArticleHeaders0 = conn.prepareStatement(
cli@37: 				"SELECT header_key, header_value FROM headers WHERE article_id = ? "
cli@37: 				+ "ORDER BY header_index ASC");
chris@3: 
cli@37: 			// Prepare statement for method getArticleHeaders(regular expr pattern)
cli@37: 			this.pstmtGetArticleHeaders1 = conn.prepareStatement(
cli@37: 				"SELECT p.article_index, h.header_value FROM headers h "
cli@37: 				+ "INNER JOIN postings p ON h.article_id = p.article_id "
cli@37: 				+ "INNER JOIN groups g ON p.group_id = g.group_id "
cli@37: 				+ "WHERE g.name          =  ? AND "
cli@37: 				+ "h.header_key    =  ? AND "
cli@37: 				+ "p.article_index >= ? "
cli@37: 				+ "ORDER BY p.article_index ASC");
chris@3: 
cli@37: 			this.pstmtGetArticleIDs = conn.prepareStatement(
cli@37: 				"SELECT article_index FROM postings WHERE group_id = ?");
chris@3: 
cli@37: 			// Prepare statement for method getArticleIndex
cli@37: 			this.pstmtGetArticleIndex = conn.prepareStatement(
cli@37: 				"SELECT article_index FROM postings WHERE "
cli@37: 				+ "article_id = (SELECT article_id FROM article_ids "
cli@37: 				+ "WHERE message_id = ?) "
cli@37: 				+ " AND group_id = ?");
chris@3: 
cli@37: 			// Prepare statements for method getArticleHeads()
cli@37: 			this.pstmtGetArticleHeads = conn.prepareStatement(
cli@37: 				"SELECT article_id, article_index FROM postings WHERE "
cli@37: 				+ "postings.group_id = ? AND article_index >= ? AND "
cli@37: 				+ "article_index <= ?");
chris@3: 
cli@37: 			// Prepare statements for method getConfigValue()
cli@37: 			this.pstmtGetConfigValue = conn.prepareStatement(
cli@37: 				"SELECT config_value FROM config WHERE config_key = ?");
chris@3: 
cli@37: 			// Prepare statements for method getEventsCount()
cli@37: 			this.pstmtGetEventsCount0 = conn.prepareStatement(
cli@37: 				"SELECT Count(*) FROM events WHERE event_key = ? AND "
cli@37: 				+ "event_time >= ? AND event_time < ?");
chris@3: 
cli@37: 			this.pstmtGetEventsCount1 = conn.prepareStatement(
cli@37: 				"SELECT Count(*) FROM events WHERE event_key = ? AND "
cli@37: 				+ "event_time >= ? AND event_time < ? AND group_id = ?");
chris@3: 
cli@37: 			// Prepare statement for method getGroupForList()
cli@37: 			this.pstmtGetGroupForList = conn.prepareStatement(
cli@37: 				"SELECT name FROM groups INNER JOIN groups2list "
cli@37: 				+ "ON groups.group_id = groups2list.group_id "
cli@37: 				+ "WHERE groups2list.listaddress = ?");
chris@3: 
cli@37: 			// Prepare statement for method getGroup()
cli@37: 			this.pstmtGetGroup0 = conn.prepareStatement(
cli@37: 				"SELECT group_id, flags FROM groups WHERE Name = ?");
cli@37: 			this.pstmtGetGroup1 = conn.prepareStatement(
cli@37: 				"SELECT name FROM groups WHERE group_id = ?");
chris@3: 
cli@37: 			// Prepare statement for method getLastArticleNumber()
cli@37: 			this.pstmtGetLastArticleNumber = conn.prepareStatement(
cli@37: 				"SELECT Max(article_index) FROM postings WHERE group_id = ?");
chris@3: 
cli@37: 			// Prepare statement for method getListForGroup()
cli@37: 			this.pstmtGetListForGroup = conn.prepareStatement(
cli@37: 				"SELECT listaddress FROM groups2list INNER JOIN groups "
cli@37: 				+ "ON groups.group_id = groups2list.group_id WHERE name = ?");
chris@3: 
cli@37: 			// Prepare statement for method getMaxArticleID()
cli@37: 			this.pstmtGetMaxArticleID = conn.prepareStatement(
cli@37: 				"SELECT Max(article_id) FROM articles");
chris@3: 
cli@37: 			// Prepare statement for method getMaxArticleIndex()
cli@37: 			this.pstmtGetMaxArticleIndex = conn.prepareStatement(
cli@37: 				"SELECT Max(article_index) FROM postings WHERE group_id = ?");
chris@3: 
cli@37: 			// Prepare statement for method getOldestArticle()
cli@37: 			this.pstmtGetOldestArticle = conn.prepareStatement(
cli@37: 				"SELECT message_id FROM article_ids WHERE article_id = "
cli@37: 				+ "(SELECT Min(article_id) FROM article_ids)");
chris@3: 
cli@37: 			// Prepare statement for method getFirstArticleNumber()
cli@37: 			this.pstmtGetFirstArticleNumber = conn.prepareStatement(
cli@37: 				"SELECT Min(article_index) FROM postings WHERE group_id = ?");
chris@3: 
cli@37: 			// Prepare statement for method getPostingsCount()
cli@45: 			prepareGetPostingsCountStatement();
chris@3: 
cli@37: 			// Prepare statement for method getSubscriptions()
cli@45: 			prepareGetSubscriptionsStatement();
chris@3: 
cli@37: 			// Prepare statement for method isArticleExisting()
cli@37: 			this.pstmtIsArticleExisting = conn.prepareStatement(
cli@37: 				"SELECT Count(article_id) FROM article_ids WHERE message_id = ?");
chris@3: 
cli@37: 			// Prepare statement for method isGroupExisting()
cli@37: 			this.pstmtIsGroupExisting = conn.prepareStatement(
cli@37: 				"SELECT * FROM groups WHERE name = ?");
chris@3: 
cli@37: 			// Prepare statement for method setConfigValue()
cli@37: 			this.pstmtSetConfigValue0 = conn.prepareStatement(
cli@37: 				"DELETE FROM config WHERE config_key = ?");
cli@37: 			this.pstmtSetConfigValue1 = conn.prepareStatement(
cli@37: 				"INSERT INTO config VALUES(?, ?)");
chris@3: 
cli@37: 			// Prepare statements for method purgeGroup()
cli@37: 			this.pstmtPurgeGroup0 = conn.prepareStatement(
cli@37: 				"DELETE FROM peer_subscriptions WHERE group_id = ?");
cli@37: 			this.pstmtPurgeGroup1 = conn.prepareStatement(
cli@37: 				"DELETE FROM groups WHERE group_id = ?");
chris@3: 
cli@37: 			// Prepare statement for method update(Group)
cli@37: 			this.pstmtUpdateGroup = conn.prepareStatement(
cli@37: 				"UPDATE groups SET flags = ?, name = ? WHERE group_id = ?");
cli@37: 		} catch (ClassNotFoundException ex) {
cli@37: 			throw new Error("JDBC Driver not found!", ex);
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	/**
cli@37: 	 * Adds an article to the database.
cli@37: 	 * @param article
cli@37: 	 * @return
cli@37: 	 * @throws java.sql.SQLException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public void addArticle(final Article article)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		try {
cli@37: 			this.conn.setAutoCommit(false);
chris@3: 
cli@37: 			int newArticleID = getMaxArticleID() + 1;
cli@38: 			addArticle(article, newArticleID);
cli@37: 			this.conn.commit();
cli@37: 			this.conn.setAutoCommit(true);
chris@3: 
cli@37: 			this.restarts = 0; // Reset error count
cli@37: 		} catch (SQLException ex) {
cli@37: 			try {
cli@37: 				this.conn.rollback();  // Rollback changes
cli@37: 			} catch (SQLException ex2) {
cli@37: 				Log.get().severe("Rollback of addArticle() failed: " + ex2);
cli@37: 			}
chris@3: 
cli@37: 			try {
cli@37: 				this.conn.setAutoCommit(true); // and release locks
cli@37: 			} catch (SQLException ex2) {
cli@37: 				Log.get().severe("setAutoCommit(true) of addArticle() failed: " + ex2);
cli@37: 			}
chris@3: 
cli@37: 			restartConnection(ex);
cli@37: 			addArticle(article);
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	/**
cli@38: 	 * Adds an article to the database.
cli@38: 	 * @param article
cli@38: 	 * @return
cli@38: 	 * @throws java.sql.SQLException
cli@38: 	 */
cli@38: 	void addArticle(final Article article, final int newArticleID)
cli@38: 		throws SQLException, StorageBackendException
cli@38: 	{
cli@38: 		// Fill prepared statement with values;
cli@38: 		// writes body to article table
cli@38: 		pstmtAddArticle1.setInt(1, newArticleID);
cli@38: 		pstmtAddArticle1.setBytes(2, article.getBody());
cli@38: 		pstmtAddArticle1.execute();
cli@38: 
cli@38: 		// Add headers
cli@38: 		Enumeration headers = article.getAllHeaders();
cli@38: 		for (int n = 0; headers.hasMoreElements(); n++) {
cli@38: 			Header header = (Header) headers.nextElement();
cli@38: 			pstmtAddArticle2.setInt(1, newArticleID);
cli@38: 			pstmtAddArticle2.setString(2, header.getName().toLowerCase());
cli@38: 			pstmtAddArticle2.setString(3,
cli@38: 				header.getValue().replaceAll("[\r\n]", ""));
cli@38: 			pstmtAddArticle2.setInt(4, n);
cli@38: 			pstmtAddArticle2.execute();
cli@38: 		}
cli@38: 
cli@38: 		// For each newsgroup add a reference
cli@38: 		List<Group> groups = article.getGroups();
cli@38: 		for (Group group : groups) {
cli@38: 			pstmtAddArticle3.setLong(1, group.getInternalID());
cli@38: 			pstmtAddArticle3.setInt(2, newArticleID);
cli@38: 			pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getInternalID()) + 1);
cli@38: 			pstmtAddArticle3.execute();
cli@38: 		}
cli@38: 
cli@38: 		// Write message-id to article_ids table
cli@38: 		this.pstmtAddArticle4.setInt(1, newArticleID);
cli@38: 		this.pstmtAddArticle4.setString(2, article.getMessageID());
cli@38: 		this.pstmtAddArticle4.execute();
cli@38: 	}
cli@38: 
cli@38: 	/**
cli@37: 	 * Adds a group to the JDBCDatabase. This method is not accessible via NNTP.
cli@37: 	 * @param name
cli@37: 	 * @throws java.sql.SQLException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public void addGroup(String name, int flags)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		try {
cli@37: 			this.conn.setAutoCommit(false);
cli@37: 			pstmtAddGroup0.setString(1, name);
cli@37: 			pstmtAddGroup0.setInt(2, flags);
chris@3: 
cli@37: 			pstmtAddGroup0.executeUpdate();
cli@37: 			this.conn.commit();
cli@37: 			this.conn.setAutoCommit(true);
cli@37: 			this.restarts = 0; // Reset error count
cli@37: 		} catch (SQLException ex) {
cli@37: 			try {
cli@37: 				this.conn.rollback();
cli@37: 				this.conn.setAutoCommit(true);
cli@37: 			} catch (SQLException ex2) {
cli@37: 				ex2.printStackTrace();
cli@37: 			}
chris@3: 
cli@37: 			restartConnection(ex);
cli@37: 			addGroup(name, flags);
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	@Override
cli@37: 	public void addEvent(long time, int type, long gid)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		try {
cli@37: 			this.conn.setAutoCommit(false);
cli@37: 			this.pstmtAddEvent.setLong(1, time);
cli@37: 			this.pstmtAddEvent.setInt(2, type);
cli@37: 			this.pstmtAddEvent.setLong(3, gid);
cli@37: 			this.pstmtAddEvent.executeUpdate();
cli@37: 			this.conn.commit();
cli@37: 			this.conn.setAutoCommit(true);
cli@37: 			this.restarts = 0;
cli@37: 		} catch (SQLException ex) {
cli@37: 			try {
cli@37: 				this.conn.rollback();
cli@37: 				this.conn.setAutoCommit(true);
cli@37: 			} catch (SQLException ex2) {
cli@37: 				ex2.printStackTrace();
cli@37: 			}
chris@3: 
cli@37: 			restartConnection(ex);
cli@37: 			addEvent(time, type, gid);
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	@Override
cli@37: 	public int countArticles()
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
chris@3: 
cli@37: 		try {
cli@37: 			rs = this.pstmtCountArticles.executeQuery();
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getInt(1);
cli@37: 			} else {
cli@37: 				return -1;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return countArticles();
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 				restarts = 0;
cli@37: 			}
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	@Override
cli@37: 	public int countGroups()
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
chris@3: 
cli@37: 		try {
cli@37: 			rs = this.pstmtCountGroups.executeQuery();
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getInt(1);
cli@37: 			} else {
cli@37: 				return -1;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return countGroups();
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 				restarts = 0;
cli@37: 			}
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	@Override
cli@37: 	public void delete(final String messageID)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		try {
cli@37: 			this.conn.setAutoCommit(false);
chris@3: 
cli@37: 			this.pstmtDeleteArticle0.setString(1, messageID);
cli@37: 			int rs = this.pstmtDeleteArticle0.executeUpdate();
chris@3: 
cli@37: 			// We do not trust the ON DELETE CASCADE functionality to delete
cli@37: 			// orphaned references...
cli@37: 			this.pstmtDeleteArticle1.setString(1, messageID);
cli@37: 			rs = this.pstmtDeleteArticle1.executeUpdate();
chris@3: 
cli@37: 			this.pstmtDeleteArticle2.setString(1, messageID);
cli@37: 			rs = this.pstmtDeleteArticle2.executeUpdate();
chris@3: 
cli@37: 			this.pstmtDeleteArticle3.setString(1, messageID);
cli@37: 			rs = this.pstmtDeleteArticle3.executeUpdate();
chris@3: 
cli@37: 			this.conn.commit();
cli@37: 			this.conn.setAutoCommit(true);
cli@37: 		} catch (SQLException ex) {
cli@37: 			throw new StorageBackendException(ex);
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	@Override
cli@37: 	public Article getArticle(String messageID)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 		try {
cli@37: 			pstmtGetArticle0.setString(1, messageID);
cli@37: 			rs = pstmtGetArticle0.executeQuery();
chris@3: 
cli@37: 			if (!rs.next()) {
cli@37: 				return null;
cli@37: 			} else {
cli@37: 				byte[] body = rs.getBytes("body");
cli@37: 				String headers = getArticleHeaders(rs.getInt("article_id"));
cli@37: 				return new Article(headers, body);
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getArticle(messageID);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 				restarts = 0; // Reset error count
cli@37: 			}
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	/**
cli@37: 	 * Retrieves an article by its ID.
cli@37: 	 * @param articleID
cli@37: 	 * @return
cli@37: 	 * @throws StorageBackendException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public Article getArticle(long articleIndex, long gid)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
chris@3: 
cli@37: 		try {
cli@37: 			this.pstmtGetArticle1.setLong(1, articleIndex);
cli@37: 			this.pstmtGetArticle1.setLong(2, gid);
chris@3: 
cli@37: 			rs = this.pstmtGetArticle1.executeQuery();
chris@3: 
cli@37: 			if (rs.next()) {
cli@37: 				byte[] body = rs.getBytes("body");
cli@37: 				String headers = getArticleHeaders(rs.getInt("article_id"));
cli@37: 				return new Article(headers, body);
cli@37: 			} else {
cli@37: 				return null;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getArticle(articleIndex, gid);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 				restarts = 0;
cli@37: 			}
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	/**
cli@37: 	 * Searches for fitting header values using the given regular expression.
cli@37: 	 * @param group
cli@37: 	 * @param start
cli@37: 	 * @param end
cli@37: 	 * @param headerKey
cli@37: 	 * @param pattern
cli@37: 	 * @return
cli@37: 	 * @throws StorageBackendException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public List<Pair<Long, String>> getArticleHeaders(Channel group, long start,
cli@37: 		long end, String headerKey, String patStr)
cli@37: 		throws StorageBackendException, PatternSyntaxException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 		List<Pair<Long, String>> heads = new ArrayList<Pair<Long, String>>();
cli@12: 
cli@37: 		try {
cli@37: 			this.pstmtGetArticleHeaders1.setString(1, group.getName());
cli@37: 			this.pstmtGetArticleHeaders1.setString(2, headerKey);
cli@37: 			this.pstmtGetArticleHeaders1.setLong(3, start);
chris@3: 
cli@37: 			rs = this.pstmtGetArticleHeaders1.executeQuery();
chris@3: 
cli@37: 			// Convert the "NNTP" regex to Java regex
cli@37: 			patStr = patStr.replace("*", ".*");
cli@37: 			Pattern pattern = Pattern.compile(patStr);
chris@3: 
cli@37: 			while (rs.next()) {
cli@37: 				Long articleIndex = rs.getLong(1);
cli@37: 				if (end < 0 || articleIndex <= end) // Match start is done via SQL
cli@37: 				{
cli@37: 					String headerValue = rs.getString(2);
cli@37: 					Matcher matcher = pattern.matcher(headerValue);
cli@37: 					if (matcher.matches()) {
cli@37: 						heads.add(new Pair<Long, String>(articleIndex, headerValue));
cli@37: 					}
cli@37: 				}
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getArticleHeaders(group, start, end, headerKey, patStr);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
chris@3: 
cli@37: 		return heads;
cli@37: 	}
chris@3: 
cli@37: 	private String getArticleHeaders(long articleID)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
chris@3: 
cli@37: 		try {
cli@37: 			this.pstmtGetArticleHeaders0.setLong(1, articleID);
cli@37: 			rs = this.pstmtGetArticleHeaders0.executeQuery();
chris@3: 
cli@37: 			StringBuilder buf = new StringBuilder();
cli@37: 			if (rs.next()) {
cli@37: 				for (;;) {
cli@37: 					buf.append(rs.getString(1)); // key
cli@37: 					buf.append(": ");
cli@37: 					String foldedValue = MimeUtility.fold(0, rs.getString(2));
cli@37: 					buf.append(foldedValue); // value
cli@37: 					if (rs.next()) {
cli@37: 						buf.append("\r\n");
cli@37: 					} else {
cli@37: 						break;
cli@37: 					}
cli@37: 				}
cli@37: 			}
chris@3: 
cli@37: 			return buf.toString();
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getArticleHeaders(articleID);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	@Override
cli@37: 	public long getArticleIndex(Article article, Group group)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
chris@3: 
cli@37: 		try {
cli@37: 			this.pstmtGetArticleIndex.setString(1, article.getMessageID());
cli@37: 			this.pstmtGetArticleIndex.setLong(2, group.getInternalID());
chris@3: 
cli@37: 			rs = this.pstmtGetArticleIndex.executeQuery();
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getLong(1);
cli@37: 			} else {
cli@37: 				return -1;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getArticleIndex(article, group);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	/**
cli@37: 	 * Returns a list of Long/Article Pairs.
cli@37: 	 * @throws java.sql.SQLException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first,
cli@37: 		long last)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
chris@3: 
cli@37: 		try {
cli@37: 			this.pstmtGetArticleHeads.setLong(1, group.getInternalID());
cli@37: 			this.pstmtGetArticleHeads.setLong(2, first);
cli@37: 			this.pstmtGetArticleHeads.setLong(3, last);
cli@37: 			rs = pstmtGetArticleHeads.executeQuery();
chris@3: 
cli@37: 			List<Pair<Long, ArticleHead>> articles = new ArrayList<Pair<Long, ArticleHead>>();
chris@3: 
cli@37: 			while (rs.next()) {
cli@37: 				long aid = rs.getLong("article_id");
cli@37: 				long aidx = rs.getLong("article_index");
cli@37: 				String headers = getArticleHeaders(aid);
cli@37: 				articles.add(new Pair<Long, ArticleHead>(aidx,
cli@37: 					new ArticleHead(headers)));
cli@37: 			}
chris@3: 
cli@37: 			return articles;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getArticleHeads(group, first, last);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	@Override
cli@37: 	public List<Long> getArticleNumbers(long gid)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 		try {
cli@37: 			List<Long> ids = new ArrayList<Long>();
cli@37: 			this.pstmtGetArticleIDs.setLong(1, gid);
cli@37: 			rs = this.pstmtGetArticleIDs.executeQuery();
cli@37: 			while (rs.next()) {
cli@37: 				ids.add(rs.getLong(1));
cli@37: 			}
cli@37: 			return ids;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getArticleNumbers(gid);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 					restarts = 0; // Clear the restart count after successful request
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	@Override
cli@37: 	public String getConfigValue(String key)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 		try {
cli@37: 			this.pstmtGetConfigValue.setString(1, key);
chris@3: 
cli@37: 			rs = this.pstmtGetConfigValue.executeQuery();
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getString(1); // First data on index 1 not 0
cli@37: 			} else {
cli@37: 				return null;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getConfigValue(key);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 				restarts = 0; // Clear the restart count after successful request
cli@37: 			}
cli@37: 		}
cli@37: 	}
chris@3: 
cli@37: 	@Override
cli@37: 	public int getEventsCount(int type, long start, long end, Channel channel)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
chris@3: 
cli@37: 		try {
cli@37: 			if (channel == null) {
cli@37: 				this.pstmtGetEventsCount0.setInt(1, type);
cli@37: 				this.pstmtGetEventsCount0.setLong(2, start);
cli@37: 				this.pstmtGetEventsCount0.setLong(3, end);
cli@37: 				rs = this.pstmtGetEventsCount0.executeQuery();
cli@37: 			} else {
cli@37: 				this.pstmtGetEventsCount1.setInt(1, type);
cli@37: 				this.pstmtGetEventsCount1.setLong(2, start);
cli@37: 				this.pstmtGetEventsCount1.setLong(3, end);
cli@37: 				this.pstmtGetEventsCount1.setLong(4, channel.getInternalID());
cli@37: 				rs = this.pstmtGetEventsCount1.executeQuery();
cli@37: 			}
cli@24: 
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getInt(1);
cli@37: 			} else {
cli@37: 				return -1;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getEventsCount(type, start, end, channel);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@24: 
cli@37: 	/**
cli@37: 	 * Reads all Groups from the JDBCDatabase.
cli@37: 	 * @return
cli@37: 	 * @throws StorageBackendException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public List<Channel> getGroups()
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs;
cli@37: 		List<Channel> buffer = new ArrayList<Channel>();
cli@37: 		Statement stmt = null;
cli@24: 
cli@37: 		try {
cli@37: 			stmt = conn.createStatement();
cli@37: 			rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
chris@3: 
cli@37: 			while (rs.next()) {
cli@37: 				String name = rs.getString("name");
cli@37: 				long id = rs.getLong("group_id");
cli@37: 				int flags = rs.getInt("flags");
cli@37: 
cli@37: 				Group group = new Group(name, id, flags);
cli@37: 				buffer.add(group);
cli@37: 			}
cli@37: 
cli@37: 			return buffer;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getGroups();
cli@37: 		} finally {
cli@37: 			if (stmt != null) {
cli@37: 				try {
cli@37: 					stmt.close(); // Implicitely closes ResultSets
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public List<String> getGroupsForList(String listAddress)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			this.pstmtGetGroupForList.setString(1, listAddress);
cli@37: 
cli@37: 			rs = this.pstmtGetGroupForList.executeQuery();
cli@37: 			List<String> groups = new ArrayList<String>();
cli@37: 			while (rs.next()) {
cli@37: 				String group = rs.getString(1);
cli@37: 				groups.add(group);
cli@37: 			}
cli@37: 			return groups;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getGroupsForList(listAddress);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	/**
cli@37: 	 * Returns the Group that is identified by the name.
cli@37: 	 * @param name
cli@37: 	 * @return
cli@37: 	 * @throws StorageBackendException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public Group getGroup(String name)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			this.pstmtGetGroup0.setString(1, name);
cli@37: 			rs = this.pstmtGetGroup0.executeQuery();
cli@37: 
cli@37: 			if (!rs.next()) {
cli@37: 				return null;
cli@37: 			} else {
cli@37: 				long id = rs.getLong("group_id");
cli@37: 				int flags = rs.getInt("flags");
cli@37: 				return new Group(name, id, flags);
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getGroup(name);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public List<String> getListsForGroup(String group)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 		List<String> lists = new ArrayList<String>();
cli@37: 
cli@37: 		try {
cli@37: 			this.pstmtGetListForGroup.setString(1, group);
cli@37: 			rs = this.pstmtGetListForGroup.executeQuery();
cli@37: 
cli@37: 			while (rs.next()) {
cli@37: 				lists.add(rs.getString(1));
cli@37: 			}
cli@37: 			return lists;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getListsForGroup(group);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	private int getMaxArticleIndex(long groupID)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			this.pstmtGetMaxArticleIndex.setLong(1, groupID);
cli@37: 			rs = this.pstmtGetMaxArticleIndex.executeQuery();
cli@37: 
cli@37: 			int maxIndex = 0;
cli@37: 			if (rs.next()) {
cli@37: 				maxIndex = rs.getInt(1);
cli@37: 			}
cli@37: 
cli@37: 			return maxIndex;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getMaxArticleIndex(groupID);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	private int getMaxArticleID()
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			rs = this.pstmtGetMaxArticleID.executeQuery();
cli@37: 
cli@37: 			int maxIndex = 0;
cli@37: 			if (rs.next()) {
cli@37: 				maxIndex = rs.getInt(1);
cli@37: 			}
cli@37: 
cli@37: 			return maxIndex;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getMaxArticleID();
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public int getLastArticleNumber(Group group)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			this.pstmtGetLastArticleNumber.setLong(1, group.getInternalID());
cli@37: 			rs = this.pstmtGetLastArticleNumber.executeQuery();
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getInt(1);
cli@37: 			} else {
cli@37: 				return 0;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getLastArticleNumber(group);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public int getFirstArticleNumber(Group group)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 		try {
cli@37: 			this.pstmtGetFirstArticleNumber.setLong(1, group.getInternalID());
cli@37: 			rs = this.pstmtGetFirstArticleNumber.executeQuery();
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getInt(1);
cli@37: 			} else {
cli@37: 				return 0;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getFirstArticleNumber(group);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	/**
cli@37: 	 * Returns a group name identified by the given id.
cli@37: 	 * @param id
cli@37: 	 * @return
cli@37: 	 * @throws StorageBackendException
cli@37: 	 */
cli@37: 	public String getGroup(int id)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			this.pstmtGetGroup1.setInt(1, id);
cli@37: 			rs = this.pstmtGetGroup1.executeQuery();
cli@37: 
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getString(1);
cli@37: 			} else {
cli@37: 				return null;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getGroup(id);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public double getEventsPerHour(int key, long gid)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		String gidquery = "";
cli@37: 		if (gid >= 0) {
cli@37: 			gidquery = " AND group_id = " + gid;
cli@37: 		}
cli@37: 
cli@37: 		Statement stmt = null;
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			stmt = this.conn.createStatement();
cli@37: 			rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))"
cli@37: 				+ " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
cli@37: 
cli@37: 			if (rs.next()) {
cli@37: 				restarts = 0; // reset error count
cli@37: 				return rs.getDouble(1);
cli@37: 			} else {
cli@37: 				return Double.NaN;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getEventsPerHour(key, gid);
cli@37: 		} finally {
cli@37: 			try {
cli@37: 				if (stmt != null) {
cli@37: 					stmt.close(); // Implicitely closes the result sets
cli@37: 				}
cli@37: 			} catch (SQLException ex) {
cli@37: 				ex.printStackTrace();
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public String getOldestArticle()
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			rs = this.pstmtGetOldestArticle.executeQuery();
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getString(1);
cli@37: 			} else {
cli@37: 				return null;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getOldestArticle();
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public int getPostingsCount(String groupname)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			this.pstmtGetPostingsCount.setString(1, groupname);
cli@37: 			rs = this.pstmtGetPostingsCount.executeQuery();
cli@37: 			if (rs.next()) {
cli@37: 				return rs.getInt(1);
cli@37: 			} else {
cli@37: 				Log.get().warning("Count on postings return nothing!");
cli@37: 				return 0;
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getPostingsCount(groupname);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public List<Subscription> getSubscriptions(int feedtype)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			List<Subscription> subs = new ArrayList<Subscription>();
cli@37: 			this.pstmtGetSubscriptions.setInt(1, feedtype);
cli@37: 			rs = this.pstmtGetSubscriptions.executeQuery();
cli@37: 
cli@37: 			while (rs.next()) {
cli@37: 				String host = rs.getString("host");
cli@37: 				String group = rs.getString("name");
cli@37: 				int port = rs.getInt("port");
cli@37: 				subs.add(new Subscription(host, port, feedtype, group));
cli@37: 			}
cli@37: 
cli@37: 			return subs;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return getSubscriptions(feedtype);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	/**
cli@37: 	 * Checks if there is an article with the given messageid in the JDBCDatabase.
cli@37: 	 * @param name
cli@37: 	 * @return
cli@37: 	 * @throws StorageBackendException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public boolean isArticleExisting(String messageID)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			this.pstmtIsArticleExisting.setString(1, messageID);
cli@37: 			rs = this.pstmtIsArticleExisting.executeQuery();
cli@37: 			return rs.next() && rs.getInt(1) == 1;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return isArticleExisting(messageID);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	/**
cli@37: 	 * Checks if there is a group with the given name in the JDBCDatabase.
cli@37: 	 * @param name
cli@37: 	 * @return
cli@37: 	 * @throws StorageBackendException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public boolean isGroupExisting(String name)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		ResultSet rs = null;
cli@37: 
cli@37: 		try {
cli@37: 			this.pstmtIsGroupExisting.setString(1, name);
cli@37: 			rs = this.pstmtIsGroupExisting.executeQuery();
cli@37: 			return rs.next();
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return isGroupExisting(name);
cli@37: 		} finally {
cli@37: 			if (rs != null) {
cli@37: 				try {
cli@37: 					rs.close();
cli@37: 				} catch (SQLException ex) {
cli@37: 					ex.printStackTrace();
cli@37: 				}
cli@37: 			}
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public void setConfigValue(String key, String value)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		try {
cli@37: 			conn.setAutoCommit(false);
cli@37: 			this.pstmtSetConfigValue0.setString(1, key);
cli@37: 			this.pstmtSetConfigValue0.execute();
cli@37: 			this.pstmtSetConfigValue1.setString(1, key);
cli@37: 			this.pstmtSetConfigValue1.setString(2, value);
cli@37: 			this.pstmtSetConfigValue1.execute();
cli@37: 			conn.commit();
cli@37: 			conn.setAutoCommit(true);
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			setConfigValue(key, value);
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	/**
cli@37: 	 * Closes the JDBCDatabase connection.
cli@37: 	 */
cli@37: 	public void shutdown()
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		try {
cli@37: 			if (this.conn != null) {
cli@37: 				this.conn.close();
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			throw new StorageBackendException(ex);
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public void purgeGroup(Group group)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		try {
cli@37: 			this.pstmtPurgeGroup0.setLong(1, group.getInternalID());
cli@37: 			this.pstmtPurgeGroup0.executeUpdate();
cli@37: 
cli@37: 			this.pstmtPurgeGroup1.setLong(1, group.getInternalID());
cli@37: 			this.pstmtPurgeGroup1.executeUpdate();
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			purgeGroup(group);
cli@37: 		}
cli@37: 	}
cli@37: 
cli@45: 	protected void restartConnection(SQLException cause)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		restarts++;
cli@45: 		Log.get().log(Level.SEVERE, Thread.currentThread()
cli@45: 			+ ": Database connection was closed (restart " + restarts + ").", cause);
cli@37: 
cli@37: 		if (restarts >= MAX_RESTARTS) {
cli@37: 			// Delete the current, probably broken JDBCDatabase instance.
cli@37: 			// So no one can use the instance any more.
cli@37: 			JDBCDatabaseProvider.instances.remove(Thread.currentThread());
cli@37: 
cli@37: 			// Throw the exception upwards
cli@37: 			throw new StorageBackendException(cause);
cli@37: 		}
cli@37: 
cli@37: 		try {
cli@37: 			Thread.sleep(1500L * restarts);
cli@37: 		} catch (InterruptedException ex) {
cli@37: 			Log.get().warning("Interrupted: " + ex.getMessage());
cli@37: 		}
cli@37: 
cli@37: 		// Try to properly close the old database connection
cli@37: 		try {
cli@37: 			if (this.conn != null) {
cli@37: 				this.conn.close();
cli@37: 			}
cli@37: 		} catch (SQLException ex) {
cli@37: 			Log.get().warning(ex.getMessage());
cli@37: 		}
cli@37: 
cli@37: 		try {
cli@37: 			// Try to reinitialize database connection
cli@37: 			arise();
cli@37: 		} catch (SQLException ex) {
cli@37: 			Log.get().warning(ex.getMessage());
cli@37: 			restartConnection(ex);
cli@37: 		}
cli@37: 	}
cli@37: 
cli@37: 	@Override
cli@37: 	public boolean update(Article article)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@38: 		ResultSet rs = null;
cli@38: 		try {
cli@38: 			// Retrieve internal article_id
cli@38: 			this.pstmtGetArticle0.setString(1, article.getMessageID());
cli@38: 			rs = this.pstmtGetArticle0.executeQuery();
cli@38: 			int articleID = rs.getInt("article_id");
cli@37: 
cli@38: 			delete(article.getMessageID());
cli@37: 
cli@38: 			this.conn.setAutoCommit(false);
cli@38: 			addArticle(article, articleID);
cli@38: 			this.conn.commit();
cli@38: 			this.conn.setAutoCommit(true);
cli@38: 			return true;
cli@38: 		} catch (SQLException ex) {
cli@38: 			try {
cli@38: 				this.conn.rollback();
cli@38: 			} catch(SQLException ex2) {
cli@38: 				Log.get().severe("Rollback failed: " + ex2.getMessage());
cli@38: 			}
cli@38: 			restartConnection(ex);
cli@38: 			return update(article);
cli@38: 		}
cli@37: 	}
cli@37: 
cli@37: 	/**
cli@37: 	 * Writes the flags and the name of the given group to the database.
cli@37: 	 * @param group
cli@37: 	 * @throws StorageBackendException
cli@37: 	 */
cli@37: 	@Override
cli@37: 	public boolean update(Group group)
cli@37: 		throws StorageBackendException
cli@37: 	{
cli@37: 		try {
cli@37: 			this.pstmtUpdateGroup.setInt(1, group.getFlags());
cli@37: 			this.pstmtUpdateGroup.setString(2, group.getName());
cli@37: 			this.pstmtUpdateGroup.setLong(3, group.getInternalID());
cli@37: 			int rs = this.pstmtUpdateGroup.executeUpdate();
cli@37: 			return rs == 1;
cli@37: 		} catch (SQLException ex) {
cli@37: 			restartConnection(ex);
cli@37: 			return update(group);
cli@37: 		}
cli@37: 	}
chris@3: }