# HG changeset patch
# User cli
# Date 1307440522 -7200
# Node ID 5d7d1adf387f39bbd3f489e80383614936630d4f
# Parent 7d0e65712a9560e9a054bae20b39154311f98b21
Work on hsqldb support
diff -r 7d0e65712a95 -r 5d7d1adf387f helpers/database_hsqldb_tmpl.sql
--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/helpers/database_hsqldb_tmpl.sql Tue Jun 07 11:55:22 2011 +0200
@@ -0,0 +1,142 @@
+/*
+ flags:
+ If bit 0 is set, groups is a mirrorred mailing list.
+ If not set default newsgroup.
+
+ Normalization: 1NF, 2NF, 3NF
+*/
+CREATE CACHED TABLE groups
+(
+ group_id INT,
+ name VARCHAR(80) NOT NULL,
+ flags TINYINT DEFAULT 0,
+
+ PRIMARY KEY(group_id),
+ UNIQUE(name)
+);
+
+CREATE CACHED TABLE articles
+(
+ article_id INT,
+ body VARBINARY,
+
+ PRIMARY KEY(article_id)
+);
+
+CREATE CACHED TABLE article_ids
+(
+ article_id INT,
+ message_id VARCHAR(255),
+
+ PRIMARY KEY(article_id),
+ UNIQUE(message_id),
+ FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE
+);
+
+CREATE CACHED TABLE headers
+(
+ article_id INT,
+ header_key VARCHAR(255),
+ header_value LONGVARCHAR,
+ header_index INT,
+
+ PRIMARY KEY(article_id, header_key, header_index),
+ FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE
+);
+
+/*
+ Normalization: 1NF, 2NF
+*/
+CREATE CACHED TABLE postings
+(
+ group_id INTEGER,
+ article_id INTEGER,
+ article_index INTEGER NOT NULL,
+
+ PRIMARY KEY(group_id, article_id),
+ FOREIGN KEY(article_id) REFERENCES articles(article_id) ON DELETE CASCADE
+);
+
+/*
+ Table for association of newsgroups and mailing-lists
+
+ Normalization: 1NF, 2NF, 3NF
+*/
+CREATE CACHED TABLE groups2list
+(
+ group_id INTEGER,
+ listaddress VARCHAR(255),
+
+ PRIMARY KEY(group_id, listaddress),
+ UNIQUE(listaddress),
+ FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
+);
+
+/*
+ Configuration table, containing key/value pairs
+
+ Normalization: 1NF, 2NF, 3NF
+*/
+CREATE CACHED TABLE config
+(
+ config_key VARCHAR(255),
+ config_value LONGVARCHAR,
+
+ PRIMARY KEY(config_key)
+);
+
+/*
+ Newsserver peers
+ feedtype: 0: pullfeed 1: pushfeed
+ Normalization: 1NF (atomic values), 2NF
+*/
+CREATE CACHED TABLE peers
+(
+ peer_id INT,
+ host VARCHAR(255),
+ port INT,
+
+ PRIMARY KEY(peer_id),
+ UNIQUE(host, port)
+);
+
+/*
+ List of newsgroups to feed into sonews
+
+ Normalization: 1NF, 2NF, 3NF
+*/
+CREATE CACHED TABLE peer_subscriptions
+(
+ peer_id INTEGER,
+ group_id INTEGER,
+ feedtype TINYINT DEFAULT 0,
+
+ PRIMARY KEY(peer_id, group_id, feedtype),
+ FOREIGN KEY(peer_id) REFERENCES peers(peer_id) ON DELETE CASCADE,
+ FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
+);
+
+/*
+ Tables for server event statistics
+
+ Possible statistic keys:
+ 1=CONNECTIONS (active connections)
+ 2=POSTED_NEWS (directly to the server posted unique messages)
+ 3=GATEWAYED_NEWS (posted unique message gateways through the ML-gateway)
+ 4=FEEDED_NEWS (unique messages feed via NNTP)
+
+ The server will create snapshots of the above data.
+
+ Normalization: 1NF, 2NF
+*/
+CREATE CACHED TABLE events
+(
+ event_time BIGINT, /* time of this snapshot */
+ event_key TINYINT, /* which data */
+ group_id INT ,
+
+ PRIMARY KEY(event_time, event_key),
+ FOREIGN KEY(group_id) REFERENCES groups(group_id) ON DELETE CASCADE
+);
+
+COMMIT;
diff -r 7d0e65712a95 -r 5d7d1adf387f helpers/sonews.conf.sample
--- a/helpers/sonews.conf.sample Tue Jun 07 09:23:34 2011 +0200
+++ b/helpers/sonews.conf.sample Tue Jun 07 11:55:22 2011 +0200
@@ -1,4 +1,4 @@
sonews.storage.database=jdbc:hsqldb:file:sonewsdb
-sonews.storage.user=
+sonews.storage.user=SA
sonews.storage.dbmsdriver=org.hsqldb.jdbcDriver
sonews.storage.password=
diff -r 7d0e65712a95 -r 5d7d1adf387f src/org/sonews/storage/impl/HSQLDB.java
--- a/src/org/sonews/storage/impl/HSQLDB.java Tue Jun 07 09:23:34 2011 +0200
+++ b/src/org/sonews/storage/impl/HSQLDB.java Tue Jun 07 11:55:22 2011 +0200
@@ -17,141 +17,14 @@
*/
package org.sonews.storage.impl;
-import java.util.List;
-import org.sonews.feed.Subscription;
-import org.sonews.storage.Article;
-import org.sonews.storage.ArticleHead;
-import org.sonews.storage.Channel;
-import org.sonews.storage.Group;
import org.sonews.storage.Storage;
-import org.sonews.storage.StorageBackendException;
-import org.sonews.util.Pair;
/**
- *
+ * A specialized JDBCDatabase supporting HSQLDB.
* @author Christian Lins
* @since sonews/1.1
*/
-public class HSQLDB implements Storage {
+public class HSQLDB extends JDBCDatabase implements Storage {
- public void addArticle(Article art) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public void addEvent(long timestamp, int type, long groupID) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public void addGroup(String groupname, int flags) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public int countArticles() throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public int countGroups() throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public void delete(String messageID) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public Article getArticle(String messageID) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public Article getArticle(long articleIndex, long groupID) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public List<Pair<Long, String>> getArticleHeaders(Channel channel, long start, long end, String header, String pattern) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first, long last) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public long getArticleIndex(Article art, Group group) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public List<Long> getArticleNumbers(long groupID) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public String getConfigValue(String key) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public int getEventsCount(int eventType, long startTimestamp, long endTimestamp, Channel channel) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public double getEventsPerHour(int key, long gid) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public int getFirstArticleNumber(Group group) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public Group getGroup(String name) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public List<Channel> getGroups() throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public List<String> getGroupsForList(String listAddress) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public int getLastArticleNumber(Group group) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public List<String> getListsForGroup(String groupname) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public String getOldestArticle() throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public int getPostingsCount(String groupname) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public List<Subscription> getSubscriptions(int type) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public boolean isArticleExisting(String messageID) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public boolean isGroupExisting(String groupname) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public void purgeGroup(Group group) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public void setConfigValue(String key, String value) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public boolean update(Article article) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
-
- public boolean update(Group group) throws StorageBackendException {
- throw new UnsupportedOperationException("Not supported yet.");
- }
}
diff -r 7d0e65712a95 -r 5d7d1adf387f src/org/sonews/storage/impl/HSQLDBProvider.java
--- a/src/org/sonews/storage/impl/HSQLDBProvider.java Tue Jun 07 09:23:34 2011 +0200
+++ b/src/org/sonews/storage/impl/HSQLDBProvider.java Tue Jun 07 11:55:22 2011 +0200
@@ -29,7 +29,7 @@
public class HSQLDBProvider implements StorageProvider {
public boolean isSupported(String uri) {
- throw new UnsupportedOperationException("Not supported yet.");
+ return uri.startsWith("jdbc:hsqldb");
}
public Storage storage(Thread thread) throws StorageBackendException {
diff -r 7d0e65712a95 -r 5d7d1adf387f src/org/sonews/storage/impl/JDBCDatabase.java
--- a/src/org/sonews/storage/impl/JDBCDatabase.java Tue Jun 07 09:23:34 2011 +0200
+++ b/src/org/sonews/storage/impl/JDBCDatabase.java Tue Jun 07 11:55:22 2011 +0200
@@ -53,49 +53,49 @@
{
public static final int MAX_RESTARTS = 2;
- private Connection conn = null;
- private PreparedStatement pstmtAddArticle1 = null;
- private PreparedStatement pstmtAddArticle2 = null;
- private PreparedStatement pstmtAddArticle3 = null;
- private PreparedStatement pstmtAddArticle4 = null;
- private PreparedStatement pstmtAddGroup0 = null;
- private PreparedStatement pstmtAddEvent = null;
- private PreparedStatement pstmtCountArticles = null;
- private PreparedStatement pstmtCountGroups = null;
- private PreparedStatement pstmtDeleteArticle0 = null;
- private PreparedStatement pstmtDeleteArticle1 = null;
- private PreparedStatement pstmtDeleteArticle2 = null;
- private PreparedStatement pstmtDeleteArticle3 = null;
- private PreparedStatement pstmtGetArticle0 = null;
- private PreparedStatement pstmtGetArticle1 = null;
- private PreparedStatement pstmtGetArticleHeaders0 = null;
- private PreparedStatement pstmtGetArticleHeaders1 = null;
- private PreparedStatement pstmtGetArticleHeads = null;
- private PreparedStatement pstmtGetArticleIDs = null;
- private PreparedStatement pstmtGetArticleIndex = null;
- private PreparedStatement pstmtGetConfigValue = null;
- private PreparedStatement pstmtGetEventsCount0 = null;
- private PreparedStatement pstmtGetEventsCount1 = null;
- private PreparedStatement pstmtGetGroupForList = null;
- private PreparedStatement pstmtGetGroup0 = null;
- private PreparedStatement pstmtGetGroup1 = null;
- private PreparedStatement pstmtGetFirstArticleNumber = null;
- private PreparedStatement pstmtGetListForGroup = null;
- private PreparedStatement pstmtGetLastArticleNumber = null;
- private PreparedStatement pstmtGetMaxArticleID = null;
- private PreparedStatement pstmtGetMaxArticleIndex = null;
- private PreparedStatement pstmtGetOldestArticle = null;
- private PreparedStatement pstmtGetPostingsCount = null;
- private PreparedStatement pstmtGetSubscriptions = null;
- private PreparedStatement pstmtIsArticleExisting = null;
- private PreparedStatement pstmtIsGroupExisting = null;
- private PreparedStatement pstmtPurgeGroup0 = null;
- private PreparedStatement pstmtPurgeGroup1 = null;
- private PreparedStatement pstmtSetConfigValue0 = null;
- private PreparedStatement pstmtSetConfigValue1 = null;
- private PreparedStatement pstmtUpdateGroup = null;
+ protected Connection conn = null;
+ protected PreparedStatement pstmtAddArticle1 = null;
+ protected PreparedStatement pstmtAddArticle2 = null;
+ protected PreparedStatement pstmtAddArticle3 = null;
+ protected PreparedStatement pstmtAddArticle4 = null;
+ protected PreparedStatement pstmtAddGroup0 = null;
+ protected PreparedStatement pstmtAddEvent = null;
+ protected PreparedStatement pstmtCountArticles = null;
+ protected PreparedStatement pstmtCountGroups = null;
+ protected PreparedStatement pstmtDeleteArticle0 = null;
+ protected PreparedStatement pstmtDeleteArticle1 = null;
+ protected PreparedStatement pstmtDeleteArticle2 = null;
+ protected PreparedStatement pstmtDeleteArticle3 = null;
+ protected PreparedStatement pstmtGetArticle0 = null;
+ protected PreparedStatement pstmtGetArticle1 = null;
+ protected PreparedStatement pstmtGetArticleHeaders0 = null;
+ protected PreparedStatement pstmtGetArticleHeaders1 = null;
+ protected PreparedStatement pstmtGetArticleHeads = null;
+ protected PreparedStatement pstmtGetArticleIDs = null;
+ protected PreparedStatement pstmtGetArticleIndex = null;
+ protected PreparedStatement pstmtGetConfigValue = null;
+ protected PreparedStatement pstmtGetEventsCount0 = null;
+ protected PreparedStatement pstmtGetEventsCount1 = null;
+ protected PreparedStatement pstmtGetGroupForList = null;
+ protected PreparedStatement pstmtGetGroup0 = null;
+ protected PreparedStatement pstmtGetGroup1 = null;
+ protected PreparedStatement pstmtGetFirstArticleNumber = null;
+ protected PreparedStatement pstmtGetListForGroup = null;
+ protected PreparedStatement pstmtGetLastArticleNumber = null;
+ protected PreparedStatement pstmtGetMaxArticleID = null;
+ protected PreparedStatement pstmtGetMaxArticleIndex = null;
+ protected PreparedStatement pstmtGetOldestArticle = null;
+ protected PreparedStatement pstmtGetPostingsCount = null;
+ protected PreparedStatement pstmtGetSubscriptions = null;
+ protected PreparedStatement pstmtIsArticleExisting = null;
+ protected PreparedStatement pstmtIsGroupExisting = null;
+ protected PreparedStatement pstmtPurgeGroup0 = null;
+ protected PreparedStatement pstmtPurgeGroup1 = null;
+ protected PreparedStatement pstmtSetConfigValue0 = null;
+ protected PreparedStatement pstmtSetConfigValue1 = null;
+ protected PreparedStatement pstmtUpdateGroup = null;
/** How many times the database connection was reinitialized */
- private int restarts = 0;
+ protected int restarts = 0;
/**
* Rises the database: reconnect and recreate all prepared statements.
diff -r 7d0e65712a95 -r 5d7d1adf387f src/org/sonews/storage/impl/JDBCDatabaseProvider.java
--- a/src/org/sonews/storage/impl/JDBCDatabaseProvider.java Tue Jun 07 09:23:34 2011 +0200
+++ b/src/org/sonews/storage/impl/JDBCDatabaseProvider.java Tue Jun 07 11:55:22 2011 +0200
@@ -35,7 +35,7 @@
@Override
public boolean isSupported(String uri) {
- throw new UnsupportedOperationException("Not supported yet.");
+ return uri.startsWith("jdbc:mysql") || uri.startsWith("jdbc:postgresql");
}
@Override
diff -r 7d0e65712a95 -r 5d7d1adf387f src/org/sonews/util/DatabaseSetup.java
--- a/src/org/sonews/util/DatabaseSetup.java Tue Jun 07 09:23:34 2011 +0200
+++ b/src/org/sonews/util/DatabaseSetup.java Tue Jun 07 11:55:22 2011 +0200
@@ -15,13 +15,13 @@
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
-
package org.sonews.util;
import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
+import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
@@ -32,8 +32,7 @@
* @author Christian Lins
* @since sonews/0.5.0
*/
-public final class DatabaseSetup
-{
+public final class DatabaseSetup {
private static final Map<String, String> templateMap = new HashMap<String, String>();
private static final Map<String, StringTemplate> urlMap = new HashMap<String, StringTemplate>();
@@ -42,58 +41,79 @@
static {
templateMap.put("1", "helpers/database_mysql5_tmpl.sql");
templateMap.put("2", "helpers/database_postgresql8_tmpl.sql");
+ templateMap.put("3", "helpers/database_hsqldb_tmpl.sql");
urlMap.put("1", new StringTemplate("jdbc:mysql://%HOSTNAME/%DB"));
urlMap.put("2", new StringTemplate("jdbc:postgresql://%HOSTNAME/%DB"));
driverMap.put("1", "com.mysql.jdbc.Driver");
driverMap.put("2", "org.postgresql.Driver");
+ driverMap.put("3", "org.hsqldb.jdbcDriver");
}
public static void main(String[] args)
- throws Exception
- {
- System.out.println("sonews Database setup helper");
- System.out.println("This program will create a initial database table structure");
- System.out.println("for the sonews Newsserver.");
- System.out.println("You need to create a database and a db user manually before!");
+ throws Exception {
+
+ loadJDBCDriver();
- System.out.println("Select DBMS type:");
- System.out.println("[1] MySQL 5.x or higher");
- System.out.println("[2] PostgreSQL 8.x or higher");
- System.out.print("Your choice: ");
+ if (args.length == 0) {
+ System.out.println("sonews Database setup helper");
+ System.out.println("This program will create a initial database table structure");
+ System.out.println("for the sonews Newsserver.");
+ System.out.println("You need to create a database and a db user manually before!");
- BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
- String dbmsType = in.readLine();
- String tmplName = templateMap.get(dbmsType);
- if (tmplName == null) {
- System.err.println("Invalid choice. Try again you fool!");
- main(args);
- return;
+ System.out.println("Select DBMS type:");
+ System.out.println("[1] MySQL 5.x or higher");
+ System.out.println("[2] PostgreSQL 8.x or higher");
+ System.out.print("Your choice: ");
+
+ BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
+ String dbmsType = in.readLine();
+ String tmplName = templateMap.get(dbmsType);
+ if (tmplName == null) {
+ System.err.println("Invalid choice. Try again you fool!");
+ main(args);
+ return;
+ }
+
+ String tmpl = Resource.getAsString(tmplName, true);
+
+ System.out.print("Database server hostname (e.g. localhost): ");
+ String dbHostname = in.readLine();
+
+ System.out.print("Database name: ");
+ String dbName = in.readLine();
+
+ System.out.print("Give name of DB user that can create tables: ");
+ String dbUser = in.readLine();
+
+ System.out.print("Password: ");
+ String dbPassword = in.readLine();
+
+ String url = urlMap.get(dbmsType).set("HOSTNAME", dbHostname).set("DB", dbName).toString();
+ createTables(tmpl, url, dbUser, dbPassword);
+
+ // TODO: Create config file
+
+ } else if(args.length == 4) {
+ String tmplName = args[0];
+ String url = args[1];
+ String dbUser = args[2];
+ String dbPassword = args[3];
+
+ String tmpl = Resource.getAsString(tmplName, true);
+ createTables(tmpl, url, dbUser, dbPassword);
+ } else {
+ System.out.println("Wrong number of parameters!");
}
- // Load JDBC Driver class
- Class.forName(driverMap.get(dbmsType));
+ System.out.println("Ok");
+ }
- String tmpl = Resource.getAsString(tmplName, true);
-
- System.out.print("Database server hostname (e.g. localhost): ");
- String dbHostname = in.readLine();
-
- System.out.print("Database name: ");
- String dbName = in.readLine();
-
- System.out.print("Give name of DB user that can create tables: ");
- String dbUser = in.readLine();
-
- System.out.print("Password: ");
- String dbPassword = in.readLine();
-
- String url = urlMap.get(dbmsType).set("HOSTNAME", dbHostname).set("DB", dbName).toString();
-
+ public static void createTables(String tmpl, String url, String dbUser, String dbPassword)
+ throws SQLException {
Connection conn =
- DriverManager.getConnection(url, dbUser, dbPassword);
- conn.setAutoCommit(false);
+ DriverManager.getConnection(url, dbUser, dbPassword);
String[] tmplChunks = tmpl.split(";");
@@ -107,10 +127,16 @@
}
conn.commit();
- conn.setAutoCommit(true);
+ conn.close();
+ }
- // Create config file
-
- System.out.println("Ok");
+ public static void loadJDBCDriver() {
+ for(String className : driverMap.values()) {
+ try {
+ Class.forName(className);
+ } catch (ClassNotFoundException ex) {
+ System.out.println("Could not load JDBC driver: " + className);
+ }
+ }
}
}
diff -r 7d0e65712a95 -r 5d7d1adf387f src/org/sonews/util/io/Resource.java
--- a/src/org/sonews/util/io/Resource.java Tue Jun 07 09:23:34 2011 +0200
+++ b/src/org/sonews/util/io/Resource.java Tue Jun 07 11:55:22 2011 +0200
@@ -19,6 +19,8 @@
package org.sonews.util.io;
import java.io.BufferedReader;
+import java.io.File;
+import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
@@ -59,6 +61,10 @@
try {
URL url = getAsURL(name);
if (url == null) {
+ File file = new File(name);
+ if(file.exists()) {
+ return new FileInputStream(file);
+ }
return null;
} else {
return url.openStream();