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