# 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> getArticleHeaders(Channel channel, long start, long end, String header, String pattern) throws StorageBackendException { - throw new UnsupportedOperationException("Not supported yet."); - } - - public List> 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 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 getGroups() throws StorageBackendException { - throw new UnsupportedOperationException("Not supported yet."); - } - - public List 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 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 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 . */ - 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 templateMap = new HashMap(); private static final Map urlMap = new HashMap(); @@ -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();