1.1 --- a/trunk/com/so/news/storage/Database.java Tue Jan 20 10:21:03 2009 +0100
1.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000
1.3 @@ -1,327 +0,0 @@
1.4 -/*
1.5 - * StarOffice News Server
1.6 - * see AUTHORS for the list of contributors
1.7 - *
1.8 - * This program is free software: you can redistribute it and/or modify
1.9 - * it under the terms of the GNU General Public License as published by
1.10 - * the Free Software Foundation, either version 3 of the License, or
1.11 - * (at your option) any later version.
1.12 - *
1.13 - * This program is distributed in the hope that it will be useful,
1.14 - * but WITHOUT ANY WARRANTY; without even the implied warranty of
1.15 - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
1.16 - * GNU General Public License for more details.
1.17 - *
1.18 - * You should have received a copy of the GNU General Public License
1.19 - * along with this program. If not, see <http://www.gnu.org/licenses/>.
1.20 - */
1.21 -
1.22 -package com.so.news.storage;
1.23 -
1.24 -import java.sql.Connection;
1.25 -import java.sql.DriverManager;
1.26 -import java.sql.ResultSet;
1.27 -import java.sql.SQLException;
1.28 -import java.sql.Statement;
1.29 -
1.30 -import java.util.zip.CRC32;
1.31 -import com.so.news.Config;
1.32 -import com.so.news.util.StringTemplate;
1.33 -
1.34 -/**
1.35 - * Database abstraction class.
1.36 - * @author Christian Lins (christian.lins@web.de)
1.37 - */
1.38 -public class Database
1.39 -{
1.40 - private static Database instance = null;
1.41 -
1.42 - /**
1.43 - * Initializes the Database subsystem, e.g. loading a JDBC driver and
1.44 - * connection to the Database Managment System.
1.45 - * This method is called when the daemon starts up or at the first
1.46 - * call to Database.getInstance().
1.47 - * @throws java.lang.Exception
1.48 - */
1.49 - public static void arise()
1.50 - throws Exception
1.51 - {
1.52 - // Tries to load the Database driver and establish a connection.
1.53 - if(instance == null)
1.54 - instance = new Database();
1.55 - }
1.56 -
1.57 - /**
1.58 - * @return Instance of the current Database backend. Returns null if an error
1.59 - * has occurred.
1.60 - */
1.61 - public static Database getInstance()
1.62 - {
1.63 - try
1.64 - {
1.65 - arise();
1.66 - return instance;
1.67 - }
1.68 - catch(Exception ex)
1.69 - {
1.70 - ex.printStackTrace();
1.71 - return null;
1.72 - }
1.73 - }
1.74 -
1.75 - private Connection conn = null;
1.76 -
1.77 - /**
1.78 - * Private constructor.
1.79 - * @throws java.lang.Exception
1.80 - */
1.81 - private Database()
1.82 - throws Exception
1.83 - {
1.84 - Class.forName(
1.85 - Config.getInstance().get("n3tpd.storage.dbmsdriver", ""));
1.86 - this.conn = DriverManager.getConnection(
1.87 - Config.getInstance().get("n3tpd.storage.database", ""),
1.88 - Config.getInstance().get("n3tpd.storage.user", "n3tpd_user"),
1.89 - Config.getInstance().get("n3tpd.storage.password", ""));
1.90 - this.conn.setAutoCommit(false);
1.91 - }
1.92 -
1.93 - /**
1.94 - * Adds an article to the database.
1.95 - * @param article
1.96 - * @return
1.97 - * @throws java.sql.SQLException
1.98 - */
1.99 - public boolean addArticle(Article article)
1.100 - throws SQLException
1.101 - {
1.102 - Statement stmt = this.conn.createStatement();
1.103 -
1.104 - String sql0 = "START TRANSACTION";
1.105 - String sql1 = "INSERT INTO articles (message_id,header,body)" +
1.106 - "VALUES('%mid', '%header', '%body')";
1.107 - StringTemplate tmpl = new StringTemplate(sql1);
1.108 - tmpl.set("body", article.getBody());
1.109 - tmpl.set("mid", article.getMessageID());
1.110 - tmpl.set("header", article.getHeaderSource());
1.111 - sql1 = tmpl.toString();
1.112 -
1.113 - String sql2 = "COMMIT";
1.114 -
1.115 - // Add statements as batch
1.116 - stmt.addBatch(sql0);
1.117 - stmt.addBatch(sql1);
1.118 -
1.119 - // TODO: For each newsgroup add a reference
1.120 - String sql = "INSERT INTO postings (group_id, article_id, article_index)" +
1.121 - "VALUES (%gid, (SELECT article_id FROM articles WHERE message_id = '%mid')," +
1.122 - " %idx)";
1.123 -
1.124 - tmpl = new StringTemplate(sql);
1.125 - tmpl.set("gid", article.getGroupID());
1.126 - tmpl.set("mid", article.getMessageID());
1.127 - tmpl.set("idx", getMaxArticleIndex() + 1);
1.128 - stmt.addBatch(tmpl.toString());
1.129 -
1.130 - // Commit
1.131 - stmt.addBatch(sql2);
1.132 -
1.133 - // And execute the batch
1.134 - stmt.executeBatch();
1.135 -
1.136 - return true;
1.137 - }
1.138 -
1.139 - /**
1.140 - * Adds a group to the Database.
1.141 - * @param name
1.142 - * @throws java.sql.SQLException
1.143 - */
1.144 - public boolean addGroup(String name)
1.145 - throws SQLException
1.146 - {
1.147 - CRC32 crc = new CRC32();
1.148 - crc.update(name.getBytes());
1.149 -
1.150 - long id = crc.getValue();
1.151 -
1.152 - Statement stmt = conn.createStatement();
1.153 - return 1 == stmt.executeUpdate("INSERT INTO Groups (ID, Name) VALUES (" + id + ", '" + name + "')");
1.154 - }
1.155 -
1.156 - public void delete(Article article)
1.157 - {
1.158 -
1.159 - }
1.160 -
1.161 - public void delete(Group group)
1.162 - {
1.163 -
1.164 - }
1.165 -
1.166 - public Article getArticle(String messageID)
1.167 - throws SQLException
1.168 - {
1.169 - Statement stmt = this.conn.createStatement();
1.170 - ResultSet rs =
1.171 - stmt.executeQuery("SELECT * FROM articles WHERE message_id = '" + messageID + "'");
1.172 -
1.173 - return new Article(rs);
1.174 - }
1.175 -
1.176 - public Article getArticle(long gid, long article_id)
1.177 - throws SQLException
1.178 - {
1.179 - Statement stmt = this.conn.createStatement();
1.180 - String sql = "SELECT * FROM articles WHERE article_id = " +
1.181 - "(SELECT article_id FROM postings WHERE " +
1.182 - "group_id = " + gid + " AND article_id = " + article_id +")";
1.183 - ResultSet rs =
1.184 - stmt.executeQuery(sql);
1.185 -
1.186 - if(rs.next())
1.187 - return new Article(rs);
1.188 - else
1.189 - return null;
1.190 - }
1.191 -
1.192 - public ResultSet getArticles()
1.193 - throws SQLException
1.194 - {
1.195 - Statement stmt = conn.createStatement();
1.196 - return stmt.executeQuery("SELECT * FROM articles");
1.197 - }
1.198 -
1.199 - /**
1.200 - * Reads all Groups from the Database.
1.201 - * @return
1.202 - * @throws java.sql.SQLException
1.203 - */
1.204 - public ResultSet getGroups()
1.205 - throws SQLException
1.206 - {
1.207 - Statement stmt = conn.createStatement();
1.208 - ResultSet rs = stmt.executeQuery("SELECT * FROM groups");
1.209 -
1.210 - return rs;
1.211 - }
1.212 -
1.213 - /**
1.214 - * Returns the Group that is identified by the name.
1.215 - * @param name
1.216 - * @return
1.217 - * @throws java.sql.SQLException
1.218 - */
1.219 - public Group getGroup(String name)
1.220 - throws SQLException
1.221 - {
1.222 - Statement stmt = this.conn.createStatement();
1.223 - String sql = "SELECT group_id FROM groups WHERE Name = '%name'";
1.224 - StringTemplate tmpl = new StringTemplate(sql);
1.225 - tmpl.set("name", name);
1.226 -
1.227 - ResultSet rs = stmt.executeQuery(tmpl.toString());
1.228 -
1.229 - if(!rs.next())
1.230 - return null;
1.231 - else
1.232 - {
1.233 - long id = rs.getLong("group_id");
1.234 - return new Group(name, id);
1.235 - }
1.236 - }
1.237 -
1.238 - public int getMaxArticleIndex()
1.239 - throws SQLException
1.240 - {
1.241 - Statement stmt = conn.createStatement();
1.242 - ResultSet rs = stmt.executeQuery(
1.243 - "SELECT Max(article_index) FROM postings");
1.244 -
1.245 - if(!rs.next())
1.246 - return 0;
1.247 - else
1.248 - return rs.getInt(1);
1.249 - }
1.250 -
1.251 - public int getLastArticleNumber(Group group)
1.252 - throws SQLException
1.253 - {
1.254 - Statement stmt = conn.createStatement();
1.255 - ResultSet rs = stmt.executeQuery(
1.256 - "SELECT Max(article_index) FROM postings WHERE group_id = " + group.getID());
1.257 -
1.258 - if(!rs.next())
1.259 - return 0;
1.260 - else
1.261 - return rs.getInt(1);
1.262 - }
1.263 -
1.264 - public int getFirstArticleNumber(Group group)
1.265 - throws SQLException
1.266 - {
1.267 - Statement stmt = conn.createStatement();
1.268 - ResultSet rs = stmt.executeQuery(
1.269 - "SELECT Min(article_index) FROM postings WHERE group_id = " + group.getID());
1.270 -
1.271 - if(!rs.next())
1.272 - return 0;
1.273 - else
1.274 - return rs.getInt(1);
1.275 - }
1.276 -
1.277 - /**
1.278 - * Returns a group name identified by the given id.
1.279 - * @param id
1.280 - * @return
1.281 - * @throws java.sql.SQLException
1.282 - */
1.283 - public String getGroup(int id)
1.284 - throws SQLException
1.285 - {
1.286 - Statement stmt = conn.createStatement();
1.287 - ResultSet rs = stmt.executeQuery(
1.288 - "SELECT name FROM groups WHERE group_id = '" + id + "'");
1.289 -
1.290 - if(rs.next())
1.291 - {
1.292 - return rs.getString(1);
1.293 - }
1.294 - else
1.295 - return null;
1.296 - }
1.297 -
1.298 - public Article getOldestArticle()
1.299 - throws SQLException
1.300 - {
1.301 - Statement stmt = conn.createStatement();
1.302 - ResultSet rs =
1.303 - stmt.executeQuery("SELECT * FROM Articles WHERE Date = (SELECT Min(Date) FROM Articles)");
1.304 -
1.305 - if(rs.next())
1.306 - return new Article(rs);
1.307 - else
1.308 - return null;
1.309 - }
1.310 -
1.311 - /**
1.312 - * Checks if there is a group with the given name in the Database.
1.313 - * @param name
1.314 - * @return
1.315 - * @throws java.sql.SQLException
1.316 - */
1.317 - public boolean isGroupExisting(String name)
1.318 - throws SQLException
1.319 - {
1.320 - Statement stmt = this.conn.createStatement();
1.321 - ResultSet rs = stmt.executeQuery("SELECT * FROM Groups WHERE Name = '" + name + "'");
1.322 -
1.323 - return rs.next();
1.324 - }
1.325 -
1.326 - public void updateArticle(Article article)
1.327 - {
1.328 -
1.329 - }
1.330 -}