diff -r f907866f0e4b -r 6fceb66e1ad7 trunk/com/so/news/storage/Database.java --- a/trunk/com/so/news/storage/Database.java Tue Jan 20 10:21:03 2009 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,327 +0,0 @@ -/* - * 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) - { - - } -}