HSQLDB backend support completed, but untested.
authorcli
Sat, 10 Sep 2011 18:18:05 +0200
changeset 457e24949b87b0
parent 44 5d7d1adf387f
child 46 28870db3b9fd
HSQLDB backend support completed, but untested.
README
helpers/database_hsqldb_tmpl.sql
helpers/sonews.conf.sample
src/org/sonews/Main.java
src/org/sonews/config/Config.java
src/org/sonews/storage/StorageManager.java
src/org/sonews/storage/impl/HSQLDB.java
src/org/sonews/storage/impl/HSQLDBProvider.java
src/org/sonews/storage/impl/JDBCDatabase.java
src/org/sonews/storage/impl/JDBCDatabaseProvider.java
     1.1 --- a/README	Tue Jun 07 11:55:22 2011 +0200
     1.2 +++ b/README	Sat Sep 10 18:18:05 2011 +0200
     1.3 @@ -1,6 +1,9 @@
     1.4  sonews README
     1.5  =============
     1.6  
     1.7 +sonews is an Usenet News Server written in Java. It uses a relation database as
     1.8 +backend, currently supported is MySQL, PostgreSQL and HSQLDB.
     1.9 +
    1.10  Prerequisites:
    1.11  --------------
    1.12  
    1.13 @@ -23,5 +26,5 @@
    1.14  Bugs and other Issues:
    1.15  ----------------------
    1.16  
    1.17 -Please mail them to christian.lins@fh-osnabrueck.de or better issue them
    1.18 -into the bugtracker at http://bugs.xerxys.info/ .
    1.19 \ No newline at end of file
    1.20 +Please mail them to mail(at)sonews.org or better issue them
    1.21 +into the bugtracker at https://bitbucket.org/cli/sonews/ .
     2.1 --- a/helpers/database_hsqldb_tmpl.sql	Tue Jun 07 11:55:22 2011 +0200
     2.2 +++ b/helpers/database_hsqldb_tmpl.sql	Sat Sep 10 18:18:05 2011 +0200
     2.3 @@ -140,3 +140,4 @@
     2.4  );
     2.5  
     2.6  COMMIT;
     2.7 +SHUTDOWN;
     3.1 --- a/helpers/sonews.conf.sample	Tue Jun 07 11:55:22 2011 +0200
     3.2 +++ b/helpers/sonews.conf.sample	Sat Sep 10 18:18:05 2011 +0200
     3.3 @@ -2,3 +2,4 @@
     3.4  sonews.storage.user=SA
     3.5  sonews.storage.dbmsdriver=org.hsqldb.jdbcDriver
     3.6  sonews.storage.password=
     3.7 +sonews.storage.provider=org.sonews.storage.impl.HSQLDBProvider
     4.1 --- a/src/org/sonews/Main.java	Tue Jun 07 11:55:22 2011 +0200
     4.2 +++ b/src/org/sonews/Main.java	Sat Sep 10 18:18:05 2011 +0200
     4.3 @@ -102,8 +102,9 @@
     4.4  		// Do NOT USE BackendConfig or Log classes before this point because they require
     4.5  		// a working JDBCDatabase connection.
     4.6  		try {
     4.7 -			StorageProvider sprov =
     4.8 -					StorageManager.loadProvider("org.sonews.storage.impl.JDBCDatabaseProvider");
     4.9 +			String provName = Config.inst().get(Config.LEVEL_FILE,
    4.10 +					Config.STORAGE_PROVIDER, "org.sonews.storage.impl.JDBCDatabaseProvider");
    4.11 +			StorageProvider sprov = StorageManager.loadProvider(provName);
    4.12  			StorageManager.enableProvider(sprov);
    4.13  
    4.14  			// Make sure some elementary groups are existing
     5.1 --- a/src/org/sonews/config/Config.java	Tue Jun 07 11:55:22 2011 +0200
     5.2 +++ b/src/org/sonews/config/Config.java	Sat Sep 10 18:18:05 2011 +0200
     5.3 @@ -52,8 +52,7 @@
     5.4  	public static final String MLSEND_PORT = "sonews.mlsend.port";
     5.5  	public static final String MLSEND_USER = "sonews.mlsend.user";
     5.6  	/** Key constant. If value is "true" every I/O is written to logfile
     5.7 -	 * (which is a lot!)
     5.8 -	 */
     5.9 +	 * (which is a lot!) */
    5.10  	public static final String DEBUG = "sonews.debug";
    5.11  	/** Key constant. Value is classname of the JDBC driver */
    5.12  	public static final String STORAGE_DBMSDRIVER = "sonews.storage.dbmsdriver";
    5.13 @@ -63,6 +62,7 @@
    5.14  	public static final String STORAGE_USER = "sonews.storage.user";
    5.15  	/** Key constant. Value is the password for the DBMS. */
    5.16  	public static final String STORAGE_PASSWORD = "sonews.storage.password";
    5.17 +	public static final String STORAGE_PROVIDER = "sonews.storage.provider";
    5.18  	/** Key constant. Value is the name of the host which is allowed to use the
    5.19  	 *  XDAEMON command; default: "localhost" */
    5.20  	public static final String XDAEMON_HOST = "sonews.xdaemon.host";
     6.1 --- a/src/org/sonews/storage/StorageManager.java	Tue Jun 07 11:55:22 2011 +0200
     6.2 +++ b/src/org/sonews/storage/StorageManager.java	Sat Sep 10 18:18:05 2011 +0200
     6.3 @@ -26,8 +26,7 @@
     6.4  
     6.5  	private static StorageProvider provider;
     6.6  
     6.7 -	public static Storage current()
     6.8 -			throws StorageBackendException {
     6.9 +	public static Storage current() throws StorageBackendException {
    6.10  		synchronized (StorageManager.class) {
    6.11  			if (provider == null) {
    6.12  				return null;
    6.13 @@ -41,7 +40,7 @@
    6.14  		try {
    6.15  			Class<?> clazz = Class.forName(pluginClassName);
    6.16  			Object inst = clazz.newInstance();
    6.17 -			return (StorageProvider) inst;
    6.18 +			return (StorageProvider)inst;
    6.19  		} catch (Exception ex) {
    6.20  			System.err.println(ex);
    6.21  			return null;
     7.1 --- a/src/org/sonews/storage/impl/HSQLDB.java	Tue Jun 07 11:55:22 2011 +0200
     7.2 +++ b/src/org/sonews/storage/impl/HSQLDB.java	Sat Sep 10 18:18:05 2011 +0200
     7.3 @@ -17,7 +17,10 @@
     7.4   */
     7.5  package org.sonews.storage.impl;
     7.6  
     7.7 +import java.sql.SQLException;
     7.8 +import org.sonews.storage.Channel;
     7.9  import org.sonews.storage.Storage;
    7.10 +import org.sonews.storage.StorageBackendException;
    7.11  
    7.12  /**
    7.13   * A specialized JDBCDatabase supporting HSQLDB.
    7.14 @@ -26,5 +29,31 @@
    7.15   */
    7.16  public class HSQLDB extends JDBCDatabase implements Storage {
    7.17  
    7.18 +	@Override
    7.19 +	protected void prepareAddGroupStatement() throws SQLException {
    7.20 +		this.pstmtAddGroup0 = conn.prepareStatement(
    7.21 +				"INSERT INTO groups (name, flags, group_id) VALUES (?, ?, IDENTITY())");
    7.22 +	}
    7.23  
    7.24 +	@Override
    7.25 +	protected void prepareCountGroupsStatement() throws SQLException {
    7.26 +		this.pstmtCountGroups = conn.prepareStatement(
    7.27 +				"SELECT Count(group_id) FROM groups WHERE "
    7.28 +				+ "BITAND(flags, " + Channel.DELETED + ") = 0");
    7.29 +	}
    7.30 +
    7.31 +	@Override
    7.32 +	protected void prepareGetPostingsCountStatement() throws SQLException {
    7.33 +		this.pstmtGetPostingsCount = conn.prepareStatement(
    7.34 +				"SELECT Count(*) FROM postings JOIN groups "
    7.35 +				+ "ON groups.name = ? GROUP BY groups.name");
    7.36 +	}
    7.37 +
    7.38 +	@Override
    7.39 +	protected void prepareGetSubscriptionsStatement() throws SQLException {
    7.40 +		this.pstmtGetSubscriptions = conn.prepareStatement(
    7.41 +				"SELECT * FROM (SELECT feedtype, host, port, peer_id FROM peers JOIN "
    7.42 +				+ "peer_subscriptions ON peers.peer_id = peer_subscriptions.peer_id) "
    7.43 +				+ "JOIN groups ON group_id = groups.group_id WHERE feedtype = ?");
    7.44 +	}
    7.45  }
     8.1 --- a/src/org/sonews/storage/impl/HSQLDBProvider.java	Tue Jun 07 11:55:22 2011 +0200
     8.2 +++ b/src/org/sonews/storage/impl/HSQLDBProvider.java	Sat Sep 10 18:18:05 2011 +0200
     8.3 @@ -17,6 +17,9 @@
     8.4   */
     8.5  package org.sonews.storage.impl;
     8.6  
     8.7 +import java.sql.SQLException;
     8.8 +import java.util.Map;
     8.9 +import java.util.concurrent.ConcurrentHashMap;
    8.10  import org.sonews.storage.Storage;
    8.11  import org.sonews.storage.StorageBackendException;
    8.12  import org.sonews.storage.StorageProvider;
    8.13 @@ -27,13 +30,28 @@
    8.14   * @since sonews/1.1
    8.15   */
    8.16  public class HSQLDBProvider implements StorageProvider {
    8.17 +	protected static final Map<Thread, HSQLDB> instances =
    8.18 +			new ConcurrentHashMap<Thread, HSQLDB>();
    8.19  
    8.20 +	@Override
    8.21  	public boolean isSupported(String uri) {
    8.22  		return uri.startsWith("jdbc:hsqldb");
    8.23  	}
    8.24  
    8.25 +	@Override
    8.26  	public Storage storage(Thread thread) throws StorageBackendException {
    8.27 -		throw new UnsupportedOperationException("Not supported yet.");
    8.28 +		try {
    8.29 +			if (!instances.containsKey(Thread.currentThread())) {
    8.30 +				HSQLDB db = new HSQLDB();
    8.31 +				db.arise();
    8.32 +				instances.put(Thread.currentThread(), db);
    8.33 +				return db;
    8.34 +			} else {
    8.35 +				return instances.get(Thread.currentThread());
    8.36 +			}
    8.37 +		} catch (SQLException ex) {
    8.38 +			throw new StorageBackendException(ex);
    8.39 +		}
    8.40  	}
    8.41  
    8.42  }
     9.1 --- a/src/org/sonews/storage/impl/JDBCDatabase.java	Tue Jun 07 11:55:22 2011 +0200
     9.2 +++ b/src/org/sonews/storage/impl/JDBCDatabase.java	Sat Sep 10 18:18:05 2011 +0200
     9.3 @@ -27,6 +27,7 @@
     9.4  import java.util.ArrayList;
     9.5  import java.util.Enumeration;
     9.6  import java.util.List;
     9.7 +import java.util.logging.Level;
     9.8  import java.util.regex.Matcher;
     9.9  import java.util.regex.Pattern;
    9.10  import java.util.regex.PatternSyntaxException;
    9.11 @@ -97,6 +98,29 @@
    9.12  	/** How many times the database connection was reinitialized */
    9.13  	protected int restarts = 0;
    9.14  
    9.15 +	protected void prepareAddGroupStatement() throws SQLException {
    9.16 +		this.pstmtAddGroup0 = conn.prepareStatement(
    9.17 +				"INSERT INTO groups (name, flags) VALUES (?, ?)");
    9.18 +	}
    9.19 +
    9.20 +	protected void prepareCountGroupsStatement() throws SQLException {
    9.21 +		this.pstmtCountGroups = conn.prepareStatement(
    9.22 +				"SELECT Count(group_id) FROM groups WHERE "
    9.23 +				+ "flags & " + Channel.DELETED + " = 0");
    9.24 +	}
    9.25 +
    9.26 +	protected void prepareGetPostingsCountStatement() throws SQLException {
    9.27 +		this.pstmtGetPostingsCount = conn.prepareStatement(
    9.28 +				"SELECT Count(*) FROM postings NATURAL JOIN groups "
    9.29 +				+ "WHERE groups.name = ?");
    9.30 +	}
    9.31 +
    9.32 +	protected void prepareGetSubscriptionsStatement() throws SQLException {
    9.33 +		this.pstmtGetSubscriptions = conn.prepareStatement(
    9.34 +				"SELECT host, port, name FROM peers NATURAL JOIN "
    9.35 +				+ "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
    9.36 +	}
    9.37 +
    9.38  	/**
    9.39  	 * Rises the database: reconnect and recreate all prepared statements.
    9.40  	 * @throws java.lang.SQLException
    9.41 @@ -137,17 +161,14 @@
    9.42  				"INSERT INTO events VALUES (?, ?, ?)");
    9.43  
    9.44  			// Prepare statement for method addGroup()
    9.45 -			this.pstmtAddGroup0 = conn.prepareStatement(
    9.46 -				"INSERT INTO groups (name, flags) VALUES (?, ?)");
    9.47 +			prepareAddGroupStatement();
    9.48  
    9.49  			// Prepare statement for method countArticles()
    9.50  			this.pstmtCountArticles = conn.prepareStatement(
    9.51  				"SELECT Count(article_id) FROM article_ids");
    9.52  
    9.53  			// Prepare statement for method countGroups()
    9.54 -			this.pstmtCountGroups = conn.prepareStatement(
    9.55 -				"SELECT Count(group_id) FROM groups WHERE "
    9.56 -				+ "flags & " + Channel.DELETED + " = 0");
    9.57 +			prepareCountGroupsStatement();
    9.58  
    9.59  			// Prepare statements for method delete(article)
    9.60  			this.pstmtDeleteArticle0 = conn.prepareStatement(
    9.61 @@ -254,14 +275,10 @@
    9.62  				"SELECT Min(article_index) FROM postings WHERE group_id = ?");
    9.63  
    9.64  			// Prepare statement for method getPostingsCount()
    9.65 -			this.pstmtGetPostingsCount = conn.prepareStatement(
    9.66 -				"SELECT Count(*) FROM postings NATURAL JOIN groups "
    9.67 -				+ "WHERE groups.name = ?");
    9.68 +			prepareGetPostingsCountStatement();
    9.69  
    9.70  			// Prepare statement for method getSubscriptions()
    9.71 -			this.pstmtGetSubscriptions = conn.prepareStatement(
    9.72 -				"SELECT host, port, name FROM peers NATURAL JOIN "
    9.73 -				+ "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
    9.74 +			prepareGetSubscriptionsStatement();
    9.75  
    9.76  			// Prepare statement for method isArticleExisting()
    9.77  			this.pstmtIsArticleExisting = conn.prepareStatement(
    9.78 @@ -1364,12 +1381,12 @@
    9.79  		}
    9.80  	}
    9.81  
    9.82 -	private void restartConnection(SQLException cause)
    9.83 +	protected void restartConnection(SQLException cause)
    9.84  		throws StorageBackendException
    9.85  	{
    9.86  		restarts++;
    9.87 -		Log.get().severe(Thread.currentThread()
    9.88 -			+ ": Database connection was closed (restart " + restarts + ").");
    9.89 +		Log.get().log(Level.SEVERE, Thread.currentThread()
    9.90 +			+ ": Database connection was closed (restart " + restarts + ").", cause);
    9.91  
    9.92  		if (restarts >= MAX_RESTARTS) {
    9.93  			// Delete the current, probably broken JDBCDatabase instance.
    10.1 --- a/src/org/sonews/storage/impl/JDBCDatabaseProvider.java	Tue Jun 07 11:55:22 2011 +0200
    10.2 +++ b/src/org/sonews/storage/impl/JDBCDatabaseProvider.java	Sat Sep 10 18:18:05 2011 +0200
    10.3 @@ -31,7 +31,8 @@
    10.4   */
    10.5  public class JDBCDatabaseProvider implements StorageProvider {
    10.6  
    10.7 -	protected static final Map<Thread, JDBCDatabase> instances = new ConcurrentHashMap<Thread, JDBCDatabase>();
    10.8 +	protected static final Map<Thread, JDBCDatabase> instances =
    10.9 +			new ConcurrentHashMap<Thread, JDBCDatabase>();
   10.10  
   10.11  	@Override
   10.12  	public boolean isSupported(String uri) {