1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/trunk/com/so/news/storage/Database.java Tue Jan 20 10:21:03 2009 +0100
1.3 @@ -0,0 +1,327 @@
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 +}