diff -r 000000000000 -r f907866f0e4b trunk/com/so/news/storage/Database.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/trunk/com/so/news/storage/Database.java Tue Jan 20 10:21:03 2009 +0100 @@ -0,0 +1,327 @@ +/* + * StarOffice News Server + * see AUTHORS for the list of contributors + * + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program. If not, see . + */ + +package com.so.news.storage; + +import java.sql.Connection; +import java.sql.DriverManager; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; + +import java.util.zip.CRC32; +import com.so.news.Config; +import com.so.news.util.StringTemplate; + +/** + * Database abstraction class. + * @author Christian Lins (christian.lins@web.de) + */ +public class Database +{ + private static Database instance = null; + + /** + * Initializes the Database subsystem, e.g. loading a JDBC driver and + * connection to the Database Managment System. + * This method is called when the daemon starts up or at the first + * call to Database.getInstance(). + * @throws java.lang.Exception + */ + public static void arise() + throws Exception + { + // Tries to load the Database driver and establish a connection. + if(instance == null) + instance = new Database(); + } + + /** + * @return Instance of the current Database backend. Returns null if an error + * has occurred. + */ + public static Database getInstance() + { + try + { + arise(); + return instance; + } + catch(Exception ex) + { + ex.printStackTrace(); + return null; + } + } + + private Connection conn = null; + + /** + * Private constructor. + * @throws java.lang.Exception + */ + private Database() + throws Exception + { + Class.forName( + Config.getInstance().get("n3tpd.storage.dbmsdriver", "")); + this.conn = DriverManager.getConnection( + Config.getInstance().get("n3tpd.storage.database", ""), + Config.getInstance().get("n3tpd.storage.user", "n3tpd_user"), + Config.getInstance().get("n3tpd.storage.password", "")); + this.conn.setAutoCommit(false); + } + + /** + * Adds an article to the database. + * @param article + * @return + * @throws java.sql.SQLException + */ + public boolean addArticle(Article article) + throws SQLException + { + Statement stmt = this.conn.createStatement(); + + String sql0 = "START TRANSACTION"; + String sql1 = "INSERT INTO articles (message_id,header,body)" + + "VALUES('%mid', '%header', '%body')"; + StringTemplate tmpl = new StringTemplate(sql1); + tmpl.set("body", article.getBody()); + tmpl.set("mid", article.getMessageID()); + tmpl.set("header", article.getHeaderSource()); + sql1 = tmpl.toString(); + + String sql2 = "COMMIT"; + + // Add statements as batch + stmt.addBatch(sql0); + stmt.addBatch(sql1); + + // TODO: For each newsgroup add a reference + String sql = "INSERT INTO postings (group_id, article_id, article_index)" + + "VALUES (%gid, (SELECT article_id FROM articles WHERE message_id = '%mid')," + + " %idx)"; + + tmpl = new StringTemplate(sql); + tmpl.set("gid", article.getGroupID()); + tmpl.set("mid", article.getMessageID()); + tmpl.set("idx", getMaxArticleIndex() + 1); + stmt.addBatch(tmpl.toString()); + + // Commit + stmt.addBatch(sql2); + + // And execute the batch + stmt.executeBatch(); + + return true; + } + + /** + * Adds a group to the Database. + * @param name + * @throws java.sql.SQLException + */ + public boolean addGroup(String name) + throws SQLException + { + CRC32 crc = new CRC32(); + crc.update(name.getBytes()); + + long id = crc.getValue(); + + Statement stmt = conn.createStatement(); + return 1 == stmt.executeUpdate("INSERT INTO Groups (ID, Name) VALUES (" + id + ", '" + name + "')"); + } + + public void delete(Article article) + { + + } + + public void delete(Group group) + { + + } + + public Article getArticle(String messageID) + throws SQLException + { + Statement stmt = this.conn.createStatement(); + ResultSet rs = + stmt.executeQuery("SELECT * FROM articles WHERE message_id = '" + messageID + "'"); + + return new Article(rs); + } + + public Article getArticle(long gid, long article_id) + throws SQLException + { + Statement stmt = this.conn.createStatement(); + String sql = "SELECT * FROM articles WHERE article_id = " + + "(SELECT article_id FROM postings WHERE " + + "group_id = " + gid + " AND article_id = " + article_id +")"; + ResultSet rs = + stmt.executeQuery(sql); + + if(rs.next()) + return new Article(rs); + else + return null; + } + + public ResultSet getArticles() + throws SQLException + { + Statement stmt = conn.createStatement(); + return stmt.executeQuery("SELECT * FROM articles"); + } + + /** + * Reads all Groups from the Database. + * @return + * @throws java.sql.SQLException + */ + public ResultSet getGroups() + throws SQLException + { + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery("SELECT * FROM groups"); + + return rs; + } + + /** + * Returns the Group that is identified by the name. + * @param name + * @return + * @throws java.sql.SQLException + */ + public Group getGroup(String name) + throws SQLException + { + Statement stmt = this.conn.createStatement(); + String sql = "SELECT group_id FROM groups WHERE Name = '%name'"; + StringTemplate tmpl = new StringTemplate(sql); + tmpl.set("name", name); + + ResultSet rs = stmt.executeQuery(tmpl.toString()); + + if(!rs.next()) + return null; + else + { + long id = rs.getLong("group_id"); + return new Group(name, id); + } + } + + public int getMaxArticleIndex() + throws SQLException + { + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery( + "SELECT Max(article_index) FROM postings"); + + if(!rs.next()) + return 0; + else + return rs.getInt(1); + } + + public int getLastArticleNumber(Group group) + throws SQLException + { + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery( + "SELECT Max(article_index) FROM postings WHERE group_id = " + group.getID()); + + if(!rs.next()) + return 0; + else + return rs.getInt(1); + } + + public int getFirstArticleNumber(Group group) + throws SQLException + { + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery( + "SELECT Min(article_index) FROM postings WHERE group_id = " + group.getID()); + + if(!rs.next()) + return 0; + else + return rs.getInt(1); + } + + /** + * Returns a group name identified by the given id. + * @param id + * @return + * @throws java.sql.SQLException + */ + public String getGroup(int id) + throws SQLException + { + Statement stmt = conn.createStatement(); + ResultSet rs = stmt.executeQuery( + "SELECT name FROM groups WHERE group_id = '" + id + "'"); + + if(rs.next()) + { + return rs.getString(1); + } + else + return null; + } + + public Article getOldestArticle() + throws SQLException + { + Statement stmt = conn.createStatement(); + ResultSet rs = + stmt.executeQuery("SELECT * FROM Articles WHERE Date = (SELECT Min(Date) FROM Articles)"); + + if(rs.next()) + return new Article(rs); + else + return null; + } + + /** + * Checks if there is a group with the given name in the Database. + * @param name + * @return + * @throws java.sql.SQLException + */ + public boolean isGroupExisting(String name) + throws SQLException + { + Statement stmt = this.conn.createStatement(); + ResultSet rs = stmt.executeQuery("SELECT * FROM Groups WHERE Name = '" + name + "'"); + + return rs.next(); + } + + public void updateArticle(Article article) + { + + } +}