1.1 --- a/org/sonews/daemon/storage/Database.java Wed Jul 01 10:48:22 2009 +0200
1.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000
1.3 @@ -1,1352 +0,0 @@
1.4 -/*
1.5 - * SONEWS 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 org.sonews.daemon.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 -import java.sql.PreparedStatement;
1.30 -import java.util.ArrayList;
1.31 -import java.util.Enumeration;
1.32 -import java.util.List;
1.33 -import java.util.Map;
1.34 -import java.util.concurrent.ConcurrentHashMap;
1.35 -import javax.mail.Header;
1.36 -import javax.mail.internet.InternetAddress;
1.37 -import javax.mail.internet.MimeUtility;
1.38 -import org.sonews.daemon.BootstrapConfig;
1.39 -import org.sonews.util.Log;
1.40 -import org.sonews.feed.Subscription;
1.41 -import org.sonews.util.Pair;
1.42 -
1.43 -/**
1.44 - * Database facade class.
1.45 - * @author Christian Lins
1.46 - * @since sonews/0.5.0
1.47 - */
1.48 -// TODO: Refactor this class to reduce size (e.g. ArticleDatabase GroupDatabase)
1.49 -public class Database
1.50 -{
1.51 -
1.52 - public static final int MAX_RESTARTS = 3;
1.53 -
1.54 - private static final Map<Thread, Database> instances
1.55 - = new ConcurrentHashMap<Thread, Database>();
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(boolean create)
1.62 - throws SQLException
1.63 - {
1.64 - if(!instances.containsKey(Thread.currentThread()) && create)
1.65 - {
1.66 - Database db = new Database();
1.67 - db.arise();
1.68 - instances.put(Thread.currentThread(), db);
1.69 - return db;
1.70 - }
1.71 - else
1.72 - {
1.73 - return instances.get(Thread.currentThread());
1.74 - }
1.75 - }
1.76 -
1.77 - public static Database getInstance()
1.78 - throws SQLException
1.79 - {
1.80 - return getInstance(true);
1.81 - }
1.82 -
1.83 - private Connection conn = null;
1.84 - private PreparedStatement pstmtAddArticle1 = null;
1.85 - private PreparedStatement pstmtAddArticle2 = null;
1.86 - private PreparedStatement pstmtAddArticle3 = null;
1.87 - private PreparedStatement pstmtAddArticle4 = null;
1.88 - private PreparedStatement pstmtAddGroup0 = null;
1.89 - private PreparedStatement pstmtAddEvent = null;
1.90 - private PreparedStatement pstmtCountArticles = null;
1.91 - private PreparedStatement pstmtCountGroups = null;
1.92 - private PreparedStatement pstmtDeleteArticle0 = null;
1.93 - private PreparedStatement pstmtGetArticle0 = null;
1.94 - private PreparedStatement pstmtGetArticle1 = null;
1.95 - private PreparedStatement pstmtGetArticleHeaders = null;
1.96 - private PreparedStatement pstmtGetArticleHeads = null;
1.97 - private PreparedStatement pstmtGetArticleIDs = null;
1.98 - private PreparedStatement pstmtGetArticleIndex = null;
1.99 - private PreparedStatement pstmtGetConfigValue = null;
1.100 - private PreparedStatement pstmtGetEventsCount0 = null;
1.101 - private PreparedStatement pstmtGetEventsCount1 = null;
1.102 - private PreparedStatement pstmtGetGroupForList = null;
1.103 - private PreparedStatement pstmtGetGroup0 = null;
1.104 - private PreparedStatement pstmtGetGroup1 = null;
1.105 - private PreparedStatement pstmtGetFirstArticleNumber = null;
1.106 - private PreparedStatement pstmtGetListForGroup = null;
1.107 - private PreparedStatement pstmtGetLastArticleNumber = null;
1.108 - private PreparedStatement pstmtGetMaxArticleID = null;
1.109 - private PreparedStatement pstmtGetMaxArticleIndex = null;
1.110 - private PreparedStatement pstmtGetPostingsCount = null;
1.111 - private PreparedStatement pstmtGetSubscriptions = null;
1.112 - private PreparedStatement pstmtIsArticleExisting = null;
1.113 - private PreparedStatement pstmtIsGroupExisting = null;
1.114 - private PreparedStatement pstmtSetConfigValue0 = null;
1.115 - private PreparedStatement pstmtSetConfigValue1 = null;
1.116 -
1.117 - /** How many times the database connection was reinitialized */
1.118 - private int restarts = 0;
1.119 -
1.120 - /**
1.121 - * Rises the database: reconnect and recreate all prepared statements.
1.122 - * @throws java.lang.SQLException
1.123 - */
1.124 - private void arise()
1.125 - throws SQLException
1.126 - {
1.127 - try
1.128 - {
1.129 - // Load database driver
1.130 - Class.forName(
1.131 - BootstrapConfig.getInstance().get(BootstrapConfig.STORAGE_DBMSDRIVER, "java.lang.Object"));
1.132 -
1.133 - // Establish database connection
1.134 - this.conn = DriverManager.getConnection(
1.135 - BootstrapConfig.getInstance().get(BootstrapConfig.STORAGE_DATABASE, "<not specified>"),
1.136 - BootstrapConfig.getInstance().get(BootstrapConfig.STORAGE_USER, "root"),
1.137 - BootstrapConfig.getInstance().get(BootstrapConfig.STORAGE_PASSWORD, ""));
1.138 -
1.139 - this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1.140 - if(this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE)
1.141 - {
1.142 - Log.msg("Warning: Database is NOT fully serializable!", false);
1.143 - }
1.144 -
1.145 - // Prepare statements for method addArticle()
1.146 - this.pstmtAddArticle1 = conn.prepareStatement(
1.147 - "INSERT INTO articles (article_id, body) VALUES(?, ?)");
1.148 - this.pstmtAddArticle2 = conn.prepareStatement(
1.149 - "INSERT INTO headers (article_id, header_key, header_value, header_index) " +
1.150 - "VALUES (?, ?, ?, ?)");
1.151 - this.pstmtAddArticle3 = conn.prepareStatement(
1.152 - "INSERT INTO postings (group_id, article_id, article_index)" +
1.153 - "VALUES (?, ?, ?)");
1.154 - this.pstmtAddArticle4 = conn.prepareStatement(
1.155 - "INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");
1.156 -
1.157 - // Prepare statement for method addStatValue()
1.158 - this.pstmtAddEvent = conn.prepareStatement(
1.159 - "INSERT INTO events VALUES (?, ?, ?)");
1.160 -
1.161 - // Prepare statement for method addGroup()
1.162 - this.pstmtAddGroup0 = conn.prepareStatement(
1.163 - "INSERT INTO groups (name, flags) VALUES (?, ?)");
1.164 -
1.165 - // Prepare statement for method countArticles()
1.166 - this.pstmtCountArticles = conn.prepareStatement(
1.167 - "SELECT Count(article_id) FROM article_ids");
1.168 -
1.169 - // Prepare statement for method countGroups()
1.170 - this.pstmtCountGroups = conn.prepareStatement(
1.171 - "SELECT Count(group_id) FROM groups WHERE " +
1.172 - "flags & " + Group.DELETED + " = 0");
1.173 -
1.174 - // Prepare statements for method delete(article)
1.175 - this.pstmtDeleteArticle0 = conn.prepareStatement(
1.176 - "DELETE FROM articles WHERE article_id = " +
1.177 - "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.178 -
1.179 - // Prepare statements for methods getArticle()
1.180 - this.pstmtGetArticle0 = conn.prepareStatement(
1.181 - "SELECT * FROM articles WHERE article_id = " +
1.182 - "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.183 - this.pstmtGetArticle1 = conn.prepareStatement(
1.184 - "SELECT * FROM articles WHERE article_id = " +
1.185 - "(SELECT article_id FROM postings WHERE " +
1.186 - "article_index = ? AND group_id = ?)");
1.187 -
1.188 - // Prepare statement for method getArticleHeaders()
1.189 - this.pstmtGetArticleHeaders = conn.prepareStatement(
1.190 - "SELECT header_key, header_value FROM headers WHERE article_id = ? " +
1.191 - "ORDER BY header_index ASC");
1.192 -
1.193 - this.pstmtGetArticleIDs = conn.prepareStatement(
1.194 - "SELECT article_index FROM postings WHERE group_id = ?");
1.195 -
1.196 - // Prepare statement for method getArticleIndex
1.197 - this.pstmtGetArticleIndex = conn.prepareStatement(
1.198 - "SELECT article_index FROM postings WHERE " +
1.199 - "article_id = (SELECT article_id FROM article_ids " +
1.200 - "WHERE message_id = ?) " +
1.201 - " AND group_id = ?");
1.202 -
1.203 - // Prepare statements for method getArticleHeads()
1.204 - this.pstmtGetArticleHeads = conn.prepareStatement(
1.205 - "SELECT article_id, article_index FROM postings WHERE " +
1.206 - "postings.group_id = ? AND article_index >= ? AND " +
1.207 - "article_index <= ?");
1.208 -
1.209 - // Prepare statements for method getConfigValue()
1.210 - this.pstmtGetConfigValue = conn.prepareStatement(
1.211 - "SELECT config_value FROM config WHERE config_key = ?");
1.212 -
1.213 - // Prepare statements for method getEventsCount()
1.214 - this.pstmtGetEventsCount0 = conn.prepareStatement(
1.215 - "SELECT Count(*) FROM events WHERE event_key = ? AND " +
1.216 - "event_time >= ? AND event_time < ?");
1.217 -
1.218 - this.pstmtGetEventsCount1 = conn.prepareStatement(
1.219 - "SELECT Count(*) FROM events WHERE event_key = ? AND " +
1.220 - "event_time >= ? AND event_time < ? AND group_id = ?");
1.221 -
1.222 - // Prepare statement for method getGroupForList()
1.223 - this.pstmtGetGroupForList = conn.prepareStatement(
1.224 - "SELECT name FROM groups INNER JOIN groups2list " +
1.225 - "ON groups.group_id = groups2list.group_id " +
1.226 - "WHERE groups2list.listaddress = ?");
1.227 -
1.228 - // Prepare statement for method getGroup()
1.229 - this.pstmtGetGroup0 = conn.prepareStatement(
1.230 - "SELECT group_id, flags FROM groups WHERE Name = ?");
1.231 - this.pstmtGetGroup1 = conn.prepareStatement(
1.232 - "SELECT name FROM groups WHERE group_id = ?");
1.233 -
1.234 - // Prepare statement for method getLastArticleNumber()
1.235 - this.pstmtGetLastArticleNumber = conn.prepareStatement(
1.236 - "SELECT Max(article_index) FROM postings WHERE group_id = ?");
1.237 -
1.238 - // Prepare statement for method getListForGroup()
1.239 - this.pstmtGetListForGroup = conn.prepareStatement(
1.240 - "SELECT listaddress FROM groups2list INNER JOIN groups " +
1.241 - "ON groups.group_id = groups2list.group_id WHERE name = ?");
1.242 -
1.243 - // Prepare statement for method getMaxArticleID()
1.244 - this.pstmtGetMaxArticleID = conn.prepareStatement(
1.245 - "SELECT Max(article_id) FROM articles");
1.246 -
1.247 - // Prepare statement for method getMaxArticleIndex()
1.248 - this.pstmtGetMaxArticleIndex = conn.prepareStatement(
1.249 - "SELECT Max(article_index) FROM postings WHERE group_id = ?");
1.250 -
1.251 - // Prepare statement for method getFirstArticleNumber()
1.252 - this.pstmtGetFirstArticleNumber = conn.prepareStatement(
1.253 - "SELECT Min(article_index) FROM postings WHERE group_id = ?");
1.254 -
1.255 - // Prepare statement for method getPostingsCount()
1.256 - this.pstmtGetPostingsCount = conn.prepareStatement(
1.257 - "SELECT Count(*) FROM postings NATURAL JOIN groups " +
1.258 - "WHERE groups.name = ?");
1.259 -
1.260 - // Prepare statement for method getSubscriptions()
1.261 - this.pstmtGetSubscriptions = conn.prepareStatement(
1.262 - "SELECT host, port, name FROM peers NATURAL JOIN " +
1.263 - "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
1.264 -
1.265 - // Prepare statement for method isArticleExisting()
1.266 - this.pstmtIsArticleExisting = conn.prepareStatement(
1.267 - "SELECT Count(article_id) FROM article_ids WHERE message_id = ?");
1.268 -
1.269 - // Prepare statement for method isGroupExisting()
1.270 - this.pstmtIsGroupExisting = conn.prepareStatement(
1.271 - "SELECT * FROM groups WHERE name = ?");
1.272 -
1.273 - // Prepare statement for method setConfigValue()
1.274 - this.pstmtSetConfigValue0 = conn.prepareStatement(
1.275 - "DELETE FROM config WHERE config_key = ?");
1.276 - this.pstmtSetConfigValue1 = conn.prepareStatement(
1.277 - "INSERT INTO config VALUES(?, ?)");
1.278 - }
1.279 - catch(ClassNotFoundException ex)
1.280 - {
1.281 - throw new Error("JDBC Driver not found!", ex);
1.282 - }
1.283 - }
1.284 -
1.285 - /**
1.286 - * Adds an article to the database.
1.287 - * @param article
1.288 - * @return
1.289 - * @throws java.sql.SQLException
1.290 - */
1.291 - public void addArticle(final Article article)
1.292 - throws SQLException
1.293 - {
1.294 - try
1.295 - {
1.296 - this.conn.setAutoCommit(false);
1.297 -
1.298 - int newArticleID = getMaxArticleID() + 1;
1.299 -
1.300 - // Fill prepared statement with values;
1.301 - // writes body to article table
1.302 - pstmtAddArticle1.setInt(1, newArticleID);
1.303 - pstmtAddArticle1.setBytes(2, article.getBody().getBytes());
1.304 - pstmtAddArticle1.execute();
1.305 -
1.306 - // Add headers
1.307 - Enumeration headers = article.getAllHeaders();
1.308 - for(int n = 0; headers.hasMoreElements(); n++)
1.309 - {
1.310 - Header header = (Header)headers.nextElement();
1.311 - pstmtAddArticle2.setInt(1, newArticleID);
1.312 - pstmtAddArticle2.setString(2, header.getName().toLowerCase());
1.313 - pstmtAddArticle2.setString(3,
1.314 - header.getValue().replaceAll("[\r\n]", ""));
1.315 - pstmtAddArticle2.setInt(4, n);
1.316 - pstmtAddArticle2.execute();
1.317 - }
1.318 -
1.319 - // For each newsgroup add a reference
1.320 - List<Group> groups = article.getGroups();
1.321 - for(Group group : groups)
1.322 - {
1.323 - pstmtAddArticle3.setLong(1, group.getID());
1.324 - pstmtAddArticle3.setInt(2, newArticleID);
1.325 - pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getID()) + 1);
1.326 - pstmtAddArticle3.execute();
1.327 - }
1.328 -
1.329 - // Write message-id to article_ids table
1.330 - this.pstmtAddArticle4.setInt(1, newArticleID);
1.331 - this.pstmtAddArticle4.setString(2, article.getMessageID());
1.332 - this.pstmtAddArticle4.execute();
1.333 -
1.334 - this.conn.commit();
1.335 - this.conn.setAutoCommit(true);
1.336 -
1.337 - this.restarts = 0; // Reset error count
1.338 - }
1.339 - catch(SQLException ex)
1.340 - {
1.341 - try
1.342 - {
1.343 - this.conn.rollback(); // Rollback changes
1.344 - }
1.345 - catch(SQLException ex2)
1.346 - {
1.347 - Log.msg("Rollback of addArticle() failed: " + ex2, false);
1.348 - }
1.349 -
1.350 - try
1.351 - {
1.352 - this.conn.setAutoCommit(true); // and release locks
1.353 - }
1.354 - catch(SQLException ex2)
1.355 - {
1.356 - Log.msg("setAutoCommit(true) of addArticle() failed: " + ex2, false);
1.357 - }
1.358 -
1.359 - restartConnection(ex);
1.360 - addArticle(article);
1.361 - }
1.362 - }
1.363 -
1.364 - /**
1.365 - * Adds a group to the Database. This method is not accessible via NNTP.
1.366 - * @param name
1.367 - * @throws java.sql.SQLException
1.368 - */
1.369 - public void addGroup(String name, int flags)
1.370 - throws SQLException
1.371 - {
1.372 - try
1.373 - {
1.374 - this.conn.setAutoCommit(false);
1.375 - pstmtAddGroup0.setString(1, name);
1.376 - pstmtAddGroup0.setInt(2, flags);
1.377 -
1.378 - pstmtAddGroup0.executeUpdate();
1.379 - this.conn.commit();
1.380 - this.conn.setAutoCommit(true);
1.381 - this.restarts = 0; // Reset error count
1.382 - }
1.383 - catch(SQLException ex)
1.384 - {
1.385 - this.conn.rollback();
1.386 - this.conn.setAutoCommit(true);
1.387 - restartConnection(ex);
1.388 - addGroup(name, flags);
1.389 - }
1.390 - }
1.391 -
1.392 - public void addEvent(long time, byte type, long gid)
1.393 - throws SQLException
1.394 - {
1.395 - try
1.396 - {
1.397 - this.conn.setAutoCommit(false);
1.398 - this.pstmtAddEvent.setLong(1, time);
1.399 - this.pstmtAddEvent.setInt(2, type);
1.400 - this.pstmtAddEvent.setLong(3, gid);
1.401 - this.pstmtAddEvent.executeUpdate();
1.402 - this.conn.commit();
1.403 - this.conn.setAutoCommit(true);
1.404 - this.restarts = 0;
1.405 - }
1.406 - catch(SQLException ex)
1.407 - {
1.408 - this.conn.rollback();
1.409 - this.conn.setAutoCommit(true);
1.410 -
1.411 - restartConnection(ex);
1.412 - addEvent(time, type, gid);
1.413 - }
1.414 - }
1.415 -
1.416 - public int countArticles()
1.417 - throws SQLException
1.418 - {
1.419 - ResultSet rs = null;
1.420 -
1.421 - try
1.422 - {
1.423 - rs = this.pstmtCountArticles.executeQuery();
1.424 - if(rs.next())
1.425 - {
1.426 - return rs.getInt(1);
1.427 - }
1.428 - else
1.429 - {
1.430 - return -1;
1.431 - }
1.432 - }
1.433 - catch(SQLException ex)
1.434 - {
1.435 - restartConnection(ex);
1.436 - return countArticles();
1.437 - }
1.438 - finally
1.439 - {
1.440 - if(rs != null)
1.441 - {
1.442 - rs.close();
1.443 - restarts = 0;
1.444 - }
1.445 - }
1.446 - }
1.447 -
1.448 - public int countGroups()
1.449 - throws SQLException
1.450 - {
1.451 - ResultSet rs = null;
1.452 -
1.453 - try
1.454 - {
1.455 - rs = this.pstmtCountGroups.executeQuery();
1.456 - if(rs.next())
1.457 - {
1.458 - return rs.getInt(1);
1.459 - }
1.460 - else
1.461 - {
1.462 - return -1;
1.463 - }
1.464 - }
1.465 - catch(SQLException ex)
1.466 - {
1.467 - restartConnection(ex);
1.468 - return countGroups();
1.469 - }
1.470 - finally
1.471 - {
1.472 - if(rs != null)
1.473 - {
1.474 - rs.close();
1.475 - restarts = 0;
1.476 - }
1.477 - }
1.478 - }
1.479 -
1.480 - public void delete(final String messageID)
1.481 - throws SQLException
1.482 - {
1.483 - try
1.484 - {
1.485 - this.conn.setAutoCommit(false);
1.486 -
1.487 - this.pstmtDeleteArticle0.setString(1, messageID);
1.488 - int rs = this.pstmtDeleteArticle0.executeUpdate();
1.489 -
1.490 - // We trust the ON DELETE CASCADE functionality to delete
1.491 - // orphaned references
1.492 -
1.493 - this.conn.commit();
1.494 - this.conn.setAutoCommit(true);
1.495 - }
1.496 - catch(SQLException ex)
1.497 - {
1.498 - throw ex;
1.499 - }
1.500 - }
1.501 -
1.502 - public Article getArticle(String messageID)
1.503 - throws SQLException
1.504 - {
1.505 - ResultSet rs = null;
1.506 - try
1.507 - {
1.508 - pstmtGetArticle0.setString(1, messageID);
1.509 - rs = pstmtGetArticle0.executeQuery();
1.510 -
1.511 - if(!rs.next())
1.512 - {
1.513 - return null;
1.514 - }
1.515 - else
1.516 - {
1.517 - String body = new String(rs.getBytes("body"));
1.518 - String headers = getArticleHeaders(rs.getInt("article_id"));
1.519 - return new Article(headers, body);
1.520 - }
1.521 - }
1.522 - catch(SQLException ex)
1.523 - {
1.524 - restartConnection(ex);
1.525 - return getArticle(messageID);
1.526 - }
1.527 - finally
1.528 - {
1.529 - if(rs != null)
1.530 - {
1.531 - rs.close();
1.532 - restarts = 0; // Reset error count
1.533 - }
1.534 - }
1.535 - }
1.536 -
1.537 - /**
1.538 - * Retrieves an article by its ID.
1.539 - * @param articleID
1.540 - * @return
1.541 - * @throws java.sql.SQLException
1.542 - */
1.543 - public Article getArticle(long articleIndex, long gid)
1.544 - throws SQLException
1.545 - {
1.546 - ResultSet rs = null;
1.547 -
1.548 - try
1.549 - {
1.550 - this.pstmtGetArticle1.setLong(1, articleIndex);
1.551 - this.pstmtGetArticle1.setLong(2, gid);
1.552 -
1.553 - rs = this.pstmtGetArticle1.executeQuery();
1.554 -
1.555 - if(rs.next())
1.556 - {
1.557 - String body = new String(rs.getBytes("body"));
1.558 - String headers = getArticleHeaders(rs.getInt("article_id"));
1.559 - return new Article(headers, body);
1.560 - }
1.561 - else
1.562 - {
1.563 - return null;
1.564 - }
1.565 - }
1.566 - catch(SQLException ex)
1.567 - {
1.568 - restartConnection(ex);
1.569 - return getArticle(articleIndex, gid);
1.570 - }
1.571 - finally
1.572 - {
1.573 - if(rs != null)
1.574 - {
1.575 - rs.close();
1.576 - restarts = 0;
1.577 - }
1.578 - }
1.579 - }
1.580 -
1.581 - public String getArticleHeaders(long articleID)
1.582 - throws SQLException
1.583 - {
1.584 - ResultSet rs = null;
1.585 -
1.586 - try
1.587 - {
1.588 - this.pstmtGetArticleHeaders.setLong(1, articleID);
1.589 - rs = this.pstmtGetArticleHeaders.executeQuery();
1.590 -
1.591 - StringBuilder buf = new StringBuilder();
1.592 - if(rs.next())
1.593 - {
1.594 - for(;;)
1.595 - {
1.596 - buf.append(rs.getString(1)); // key
1.597 - buf.append(": ");
1.598 - String foldedValue = MimeUtility.fold(0, rs.getString(2));
1.599 - buf.append(foldedValue); // value
1.600 - if(rs.next())
1.601 - {
1.602 - buf.append("\r\n");
1.603 - }
1.604 - else
1.605 - {
1.606 - break;
1.607 - }
1.608 - }
1.609 - }
1.610 -
1.611 - return buf.toString();
1.612 - }
1.613 - catch(SQLException ex)
1.614 - {
1.615 - restartConnection(ex);
1.616 - return getArticleHeaders(articleID);
1.617 - }
1.618 - finally
1.619 - {
1.620 - if(rs != null)
1.621 - rs.close();
1.622 - }
1.623 - }
1.624 -
1.625 - public long getArticleIndex(Article article, Group group)
1.626 - throws SQLException
1.627 - {
1.628 - ResultSet rs = null;
1.629 -
1.630 - try
1.631 - {
1.632 - this.pstmtGetArticleIndex.setString(1, article.getMessageID());
1.633 - this.pstmtGetArticleIndex.setLong(2, group.getID());
1.634 -
1.635 - rs = this.pstmtGetArticleIndex.executeQuery();
1.636 - if(rs.next())
1.637 - {
1.638 - return rs.getLong(1);
1.639 - }
1.640 - else
1.641 - {
1.642 - return -1;
1.643 - }
1.644 - }
1.645 - catch(SQLException ex)
1.646 - {
1.647 - restartConnection(ex);
1.648 - return getArticleIndex(article, group);
1.649 - }
1.650 - finally
1.651 - {
1.652 - if(rs != null)
1.653 - rs.close();
1.654 - }
1.655 - }
1.656 -
1.657 - /**
1.658 - * Returns a list of Long/Article Pairs.
1.659 - * @throws java.sql.SQLException
1.660 - */
1.661 - public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, int first, int last)
1.662 - throws SQLException
1.663 - {
1.664 - ResultSet rs = null;
1.665 -
1.666 - try
1.667 - {
1.668 - this.pstmtGetArticleHeads.setLong(1, group.getID());
1.669 - this.pstmtGetArticleHeads.setInt(2, first);
1.670 - this.pstmtGetArticleHeads.setInt(3, last);
1.671 - rs = pstmtGetArticleHeads.executeQuery();
1.672 -
1.673 - List<Pair<Long, ArticleHead>> articles
1.674 - = new ArrayList<Pair<Long, ArticleHead>>();
1.675 -
1.676 - while (rs.next())
1.677 - {
1.678 - long aid = rs.getLong("article_id");
1.679 - long aidx = rs.getLong("article_index");
1.680 - String headers = getArticleHeaders(aid);
1.681 - articles.add(new Pair<Long, ArticleHead>(aidx,
1.682 - new ArticleHead(headers)));
1.683 - }
1.684 -
1.685 - return articles;
1.686 - }
1.687 - catch(SQLException ex)
1.688 - {
1.689 - restartConnection(ex);
1.690 - return getArticleHeads(group, first, last);
1.691 - }
1.692 - finally
1.693 - {
1.694 - if(rs != null)
1.695 - rs.close();
1.696 - }
1.697 - }
1.698 -
1.699 - public List<Long> getArticleNumbers(long gid)
1.700 - throws SQLException
1.701 - {
1.702 - ResultSet rs = null;
1.703 - try
1.704 - {
1.705 - List<Long> ids = new ArrayList<Long>();
1.706 - this.pstmtGetArticleIDs.setLong(1, gid);
1.707 - rs = this.pstmtGetArticleIDs.executeQuery();
1.708 - while(rs.next())
1.709 - {
1.710 - ids.add(rs.getLong(1));
1.711 - }
1.712 - return ids;
1.713 - }
1.714 - catch(SQLException ex)
1.715 - {
1.716 - restartConnection(ex);
1.717 - return getArticleNumbers(gid);
1.718 - }
1.719 - finally
1.720 - {
1.721 - if(rs != null)
1.722 - {
1.723 - rs.close();
1.724 - restarts = 0; // Clear the restart count after successful request
1.725 - }
1.726 - }
1.727 - }
1.728 -
1.729 - public String getConfigValue(String key)
1.730 - throws SQLException
1.731 - {
1.732 - ResultSet rs = null;
1.733 - try
1.734 - {
1.735 - this.pstmtGetConfigValue.setString(1, key);
1.736 -
1.737 - rs = this.pstmtGetConfigValue.executeQuery();
1.738 - if(rs.next())
1.739 - {
1.740 - return rs.getString(1); // First data on index 1 not 0
1.741 - }
1.742 - else
1.743 - {
1.744 - return null;
1.745 - }
1.746 - }
1.747 - catch(SQLException ex)
1.748 - {
1.749 - restartConnection(ex);
1.750 - return getConfigValue(key);
1.751 - }
1.752 - finally
1.753 - {
1.754 - if(rs != null)
1.755 - {
1.756 - rs.close();
1.757 - restarts = 0; // Clear the restart count after successful request
1.758 - }
1.759 - }
1.760 - }
1.761 -
1.762 - public int getEventsCount(byte type, long start, long end, Group group)
1.763 - throws SQLException
1.764 - {
1.765 - ResultSet rs = null;
1.766 -
1.767 - try
1.768 - {
1.769 - if(group == null)
1.770 - {
1.771 - this.pstmtGetEventsCount0.setInt(1, type);
1.772 - this.pstmtGetEventsCount0.setLong(2, start);
1.773 - this.pstmtGetEventsCount0.setLong(3, end);
1.774 - rs = this.pstmtGetEventsCount0.executeQuery();
1.775 - }
1.776 - else
1.777 - {
1.778 - this.pstmtGetEventsCount1.setInt(1, type);
1.779 - this.pstmtGetEventsCount1.setLong(2, start);
1.780 - this.pstmtGetEventsCount1.setLong(3, end);
1.781 - this.pstmtGetEventsCount1.setLong(4, group.getID());
1.782 - rs = this.pstmtGetEventsCount1.executeQuery();
1.783 - }
1.784 -
1.785 - if(rs.next())
1.786 - {
1.787 - return rs.getInt(1);
1.788 - }
1.789 - else
1.790 - {
1.791 - return -1;
1.792 - }
1.793 - }
1.794 - catch(SQLException ex)
1.795 - {
1.796 - restartConnection(ex);
1.797 - return getEventsCount(type, start, end, group);
1.798 - }
1.799 - finally
1.800 - {
1.801 - if(rs != null)
1.802 - rs.close();
1.803 - }
1.804 - }
1.805 -
1.806 - /**
1.807 - * Reads all Groups from the Database.
1.808 - * @return
1.809 - * @throws java.sql.SQLException
1.810 - */
1.811 - public List<Group> getGroups()
1.812 - throws SQLException
1.813 - {
1.814 - ResultSet rs;
1.815 - List<Group> buffer = new ArrayList<Group>();
1.816 - Statement stmt = null;
1.817 -
1.818 - try
1.819 - {
1.820 - stmt = conn.createStatement();
1.821 - rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
1.822 -
1.823 - while(rs.next())
1.824 - {
1.825 - String name = rs.getString("name");
1.826 - long id = rs.getLong("group_id");
1.827 - int flags = rs.getInt("flags");
1.828 -
1.829 - Group group = new Group(name, id, flags);
1.830 - buffer.add(group);
1.831 - }
1.832 -
1.833 - return buffer;
1.834 - }
1.835 - catch(SQLException ex)
1.836 - {
1.837 - restartConnection(ex);
1.838 - return getGroups();
1.839 - }
1.840 - finally
1.841 - {
1.842 - if(stmt != null)
1.843 - stmt.close(); // Implicitely closes ResultSets
1.844 - }
1.845 - }
1.846 -
1.847 - public String getGroupForList(InternetAddress listAddress)
1.848 - throws SQLException
1.849 - {
1.850 - ResultSet rs = null;
1.851 -
1.852 - try
1.853 - {
1.854 - this.pstmtGetGroupForList.setString(1, listAddress.getAddress());
1.855 -
1.856 - rs = this.pstmtGetGroupForList.executeQuery();
1.857 - if (rs.next())
1.858 - {
1.859 - return rs.getString(1);
1.860 - }
1.861 - else
1.862 - {
1.863 - return null;
1.864 - }
1.865 - }
1.866 - catch(SQLException ex)
1.867 - {
1.868 - restartConnection(ex);
1.869 - return getGroupForList(listAddress);
1.870 - }
1.871 - finally
1.872 - {
1.873 - if(rs != null)
1.874 - rs.close();
1.875 - }
1.876 - }
1.877 -
1.878 - /**
1.879 - * Returns the Group that is identified by the name.
1.880 - * @param name
1.881 - * @return
1.882 - * @throws java.sql.SQLException
1.883 - */
1.884 - public Group getGroup(String name)
1.885 - throws SQLException
1.886 - {
1.887 - ResultSet rs = null;
1.888 -
1.889 - try
1.890 - {
1.891 - this.pstmtGetGroup0.setString(1, name);
1.892 - rs = this.pstmtGetGroup0.executeQuery();
1.893 -
1.894 - if (!rs.next())
1.895 - {
1.896 - return null;
1.897 - }
1.898 - else
1.899 - {
1.900 - long id = rs.getLong("group_id");
1.901 - int flags = rs.getInt("flags");
1.902 - return new Group(name, id, flags);
1.903 - }
1.904 - }
1.905 - catch(SQLException ex)
1.906 - {
1.907 - restartConnection(ex);
1.908 - return getGroup(name);
1.909 - }
1.910 - finally
1.911 - {
1.912 - if(rs != null)
1.913 - rs.close();
1.914 - }
1.915 - }
1.916 -
1.917 - public String getListForGroup(String group)
1.918 - throws SQLException
1.919 - {
1.920 - ResultSet rs = null;
1.921 -
1.922 - try
1.923 - {
1.924 - this.pstmtGetListForGroup.setString(1, group);
1.925 - rs = this.pstmtGetListForGroup.executeQuery();
1.926 - if (rs.next())
1.927 - {
1.928 - return rs.getString(1);
1.929 - }
1.930 - else
1.931 - {
1.932 - return null;
1.933 - }
1.934 - }
1.935 - catch(SQLException ex)
1.936 - {
1.937 - restartConnection(ex);
1.938 - return getListForGroup(group);
1.939 - }
1.940 - finally
1.941 - {
1.942 - if(rs != null)
1.943 - rs.close();
1.944 - }
1.945 - }
1.946 -
1.947 - private int getMaxArticleIndex(long groupID)
1.948 - throws SQLException
1.949 - {
1.950 - ResultSet rs = null;
1.951 -
1.952 - try
1.953 - {
1.954 - this.pstmtGetMaxArticleIndex.setLong(1, groupID);
1.955 - rs = this.pstmtGetMaxArticleIndex.executeQuery();
1.956 -
1.957 - int maxIndex = 0;
1.958 - if (rs.next())
1.959 - {
1.960 - maxIndex = rs.getInt(1);
1.961 - }
1.962 -
1.963 - return maxIndex;
1.964 - }
1.965 - catch(SQLException ex)
1.966 - {
1.967 - restartConnection(ex);
1.968 - return getMaxArticleIndex(groupID);
1.969 - }
1.970 - finally
1.971 - {
1.972 - if(rs != null)
1.973 - rs.close();
1.974 - }
1.975 - }
1.976 -
1.977 - private int getMaxArticleID()
1.978 - throws SQLException
1.979 - {
1.980 - ResultSet rs = null;
1.981 -
1.982 - try
1.983 - {
1.984 - rs = this.pstmtGetMaxArticleID.executeQuery();
1.985 -
1.986 - int maxIndex = 0;
1.987 - if (rs.next())
1.988 - {
1.989 - maxIndex = rs.getInt(1);
1.990 - }
1.991 -
1.992 - return maxIndex;
1.993 - }
1.994 - catch(SQLException ex)
1.995 - {
1.996 - restartConnection(ex);
1.997 - return getMaxArticleID();
1.998 - }
1.999 - finally
1.1000 - {
1.1001 - if(rs != null)
1.1002 - rs.close();
1.1003 - }
1.1004 - }
1.1005 -
1.1006 - public int getLastArticleNumber(Group group)
1.1007 - throws SQLException
1.1008 - {
1.1009 - ResultSet rs = null;
1.1010 -
1.1011 - try
1.1012 - {
1.1013 - this.pstmtGetLastArticleNumber.setLong(1, group.getID());
1.1014 - rs = this.pstmtGetLastArticleNumber.executeQuery();
1.1015 - if (rs.next())
1.1016 - {
1.1017 - return rs.getInt(1);
1.1018 - }
1.1019 - else
1.1020 - {
1.1021 - return 0;
1.1022 - }
1.1023 - }
1.1024 - catch(SQLException ex)
1.1025 - {
1.1026 - restartConnection(ex);
1.1027 - return getLastArticleNumber(group);
1.1028 - }
1.1029 - finally
1.1030 - {
1.1031 - if(rs != null)
1.1032 - rs.close();
1.1033 - }
1.1034 - }
1.1035 -
1.1036 - public int getFirstArticleNumber(Group group)
1.1037 - throws SQLException
1.1038 - {
1.1039 - ResultSet rs = null;
1.1040 - try
1.1041 - {
1.1042 - this.pstmtGetFirstArticleNumber.setLong(1, group.getID());
1.1043 - rs = this.pstmtGetFirstArticleNumber.executeQuery();
1.1044 - if(rs.next())
1.1045 - {
1.1046 - return rs.getInt(1);
1.1047 - }
1.1048 - else
1.1049 - {
1.1050 - return 0;
1.1051 - }
1.1052 - }
1.1053 - catch(SQLException ex)
1.1054 - {
1.1055 - restartConnection(ex);
1.1056 - return getFirstArticleNumber(group);
1.1057 - }
1.1058 - finally
1.1059 - {
1.1060 - if(rs != null)
1.1061 - rs.close();
1.1062 - }
1.1063 - }
1.1064 -
1.1065 - /**
1.1066 - * Returns a group name identified by the given id.
1.1067 - * @param id
1.1068 - * @return
1.1069 - * @throws java.sql.SQLException
1.1070 - */
1.1071 - public String getGroup(int id)
1.1072 - throws SQLException
1.1073 - {
1.1074 - ResultSet rs = null;
1.1075 -
1.1076 - try
1.1077 - {
1.1078 - this.pstmtGetGroup1.setInt(1, id);
1.1079 - rs = this.pstmtGetGroup1.executeQuery();
1.1080 -
1.1081 - if (rs.next())
1.1082 - {
1.1083 - return rs.getString(1);
1.1084 - }
1.1085 - else
1.1086 - {
1.1087 - return null;
1.1088 - }
1.1089 - }
1.1090 - catch(SQLException ex)
1.1091 - {
1.1092 - restartConnection(ex);
1.1093 - return getGroup(id);
1.1094 - }
1.1095 - finally
1.1096 - {
1.1097 - if(rs != null)
1.1098 - rs.close();
1.1099 - }
1.1100 - }
1.1101 -
1.1102 - public double getNumberOfEventsPerHour(int key, long gid)
1.1103 - throws SQLException
1.1104 - {
1.1105 - String gidquery = "";
1.1106 - if(gid >= 0)
1.1107 - {
1.1108 - gidquery = " AND group_id = " + gid;
1.1109 - }
1.1110 -
1.1111 - Statement stmt = null;
1.1112 - ResultSet rs = null;
1.1113 -
1.1114 - try
1.1115 - {
1.1116 - stmt = this.conn.createStatement();
1.1117 - rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))" +
1.1118 - " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
1.1119 -
1.1120 - if(rs.next())
1.1121 - {
1.1122 - restarts = 0; // reset error count
1.1123 - return rs.getDouble(1);
1.1124 - }
1.1125 - else
1.1126 - {
1.1127 - return Double.NaN;
1.1128 - }
1.1129 - }
1.1130 - catch(SQLException ex)
1.1131 - {
1.1132 - restartConnection(ex);
1.1133 - return getNumberOfEventsPerHour(key, gid);
1.1134 - }
1.1135 - finally
1.1136 - {
1.1137 - if(stmt != null)
1.1138 - {
1.1139 - stmt.close();
1.1140 - }
1.1141 -
1.1142 - if(rs != null)
1.1143 - {
1.1144 - rs.close();
1.1145 - }
1.1146 - }
1.1147 - }
1.1148 -
1.1149 - public int getPostingsCount(String groupname)
1.1150 - throws SQLException
1.1151 - {
1.1152 - ResultSet rs = null;
1.1153 -
1.1154 - try
1.1155 - {
1.1156 - this.pstmtGetPostingsCount.setString(1, groupname);
1.1157 - rs = this.pstmtGetPostingsCount.executeQuery();
1.1158 - if(rs.next())
1.1159 - {
1.1160 - return rs.getInt(1);
1.1161 - }
1.1162 - else
1.1163 - {
1.1164 - Log.msg("Warning: Count on postings return nothing!", true);
1.1165 - return 0;
1.1166 - }
1.1167 - }
1.1168 - catch(SQLException ex)
1.1169 - {
1.1170 - restartConnection(ex);
1.1171 - return getPostingsCount(groupname);
1.1172 - }
1.1173 - finally
1.1174 - {
1.1175 - if(rs != null)
1.1176 - rs.close();
1.1177 - }
1.1178 - }
1.1179 -
1.1180 - public List<Subscription> getSubscriptions(int feedtype)
1.1181 - throws SQLException
1.1182 - {
1.1183 - ResultSet rs = null;
1.1184 -
1.1185 - try
1.1186 - {
1.1187 - List<Subscription> subs = new ArrayList<Subscription>();
1.1188 - this.pstmtGetSubscriptions.setInt(1, feedtype);
1.1189 - rs = this.pstmtGetSubscriptions.executeQuery();
1.1190 -
1.1191 - while(rs.next())
1.1192 - {
1.1193 - String host = rs.getString("host");
1.1194 - String group = rs.getString("name");
1.1195 - int port = rs.getInt("port");
1.1196 - subs.add(new Subscription(host, port, feedtype, group));
1.1197 - }
1.1198 -
1.1199 - return subs;
1.1200 - }
1.1201 - catch(SQLException ex)
1.1202 - {
1.1203 - restartConnection(ex);
1.1204 - return getSubscriptions(feedtype);
1.1205 - }
1.1206 - finally
1.1207 - {
1.1208 - if(rs != null)
1.1209 - rs.close();
1.1210 - }
1.1211 - }
1.1212 -
1.1213 - /**
1.1214 - * Checks if there is an article with the given messageid in the Database.
1.1215 - * @param name
1.1216 - * @return
1.1217 - * @throws java.sql.SQLException
1.1218 - */
1.1219 - public boolean isArticleExisting(String messageID)
1.1220 - throws SQLException
1.1221 - {
1.1222 - ResultSet rs = null;
1.1223 -
1.1224 - try
1.1225 - {
1.1226 - this.pstmtIsArticleExisting.setString(1, messageID);
1.1227 - rs = this.pstmtIsArticleExisting.executeQuery();
1.1228 - return rs.next() && rs.getInt(1) == 1;
1.1229 - }
1.1230 - catch(SQLException ex)
1.1231 - {
1.1232 - restartConnection(ex);
1.1233 - return isArticleExisting(messageID);
1.1234 - }
1.1235 - finally
1.1236 - {
1.1237 - if(rs != null)
1.1238 - rs.close();
1.1239 - }
1.1240 - }
1.1241 -
1.1242 - /**
1.1243 - * Checks if there is a group with the given name in the Database.
1.1244 - * @param name
1.1245 - * @return
1.1246 - * @throws java.sql.SQLException
1.1247 - */
1.1248 - public boolean isGroupExisting(String name)
1.1249 - throws SQLException
1.1250 - {
1.1251 - ResultSet rs = null;
1.1252 -
1.1253 - try
1.1254 - {
1.1255 - this.pstmtIsGroupExisting.setString(1, name);
1.1256 - rs = this.pstmtIsGroupExisting.executeQuery();
1.1257 - return rs.next();
1.1258 - }
1.1259 - catch(SQLException ex)
1.1260 - {
1.1261 - restartConnection(ex);
1.1262 - return isGroupExisting(name);
1.1263 - }
1.1264 - finally
1.1265 - {
1.1266 - if(rs != null)
1.1267 - rs.close();
1.1268 - }
1.1269 - }
1.1270 -
1.1271 - public void setConfigValue(String key, String value)
1.1272 - throws SQLException
1.1273 - {
1.1274 - try
1.1275 - {
1.1276 - conn.setAutoCommit(false);
1.1277 - this.pstmtSetConfigValue0.setString(1, key);
1.1278 - this.pstmtSetConfigValue0.execute();
1.1279 - this.pstmtSetConfigValue1.setString(1, key);
1.1280 - this.pstmtSetConfigValue1.setString(2, value);
1.1281 - this.pstmtSetConfigValue1.execute();
1.1282 - conn.commit();
1.1283 - conn.setAutoCommit(true);
1.1284 - }
1.1285 - catch(SQLException ex)
1.1286 - {
1.1287 - restartConnection(ex);
1.1288 - setConfigValue(key, value);
1.1289 - }
1.1290 - }
1.1291 -
1.1292 - /**
1.1293 - * Closes the Database connection.
1.1294 - */
1.1295 - public void shutdown()
1.1296 - throws SQLException
1.1297 - {
1.1298 - if(this.conn != null)
1.1299 - {
1.1300 - this.conn.close();
1.1301 - }
1.1302 - }
1.1303 -
1.1304 - private void restartConnection(SQLException cause)
1.1305 - throws SQLException
1.1306 - {
1.1307 - restarts++;
1.1308 - Log.msg(Thread.currentThread()
1.1309 - + ": Database connection was closed (restart " + restarts + ").", false);
1.1310 -
1.1311 - if(restarts >= MAX_RESTARTS)
1.1312 - {
1.1313 - // Delete the current, probably broken Database instance.
1.1314 - // So no one can use the instance any more.
1.1315 - Database.instances.remove(Thread.currentThread());
1.1316 -
1.1317 - // Throw the exception upwards
1.1318 - throw cause;
1.1319 - }
1.1320 -
1.1321 - try
1.1322 - {
1.1323 - Thread.sleep(1500L * restarts);
1.1324 - }
1.1325 - catch(InterruptedException ex)
1.1326 - {
1.1327 - Log.msg("Interrupted: " + ex.getMessage(), false);
1.1328 - }
1.1329 -
1.1330 - // Try to properly close the old database connection
1.1331 - try
1.1332 - {
1.1333 - if(this.conn != null)
1.1334 - {
1.1335 - this.conn.close();
1.1336 - }
1.1337 - }
1.1338 - catch(SQLException ex)
1.1339 - {
1.1340 - Log.msg(ex.getMessage(), true);
1.1341 - }
1.1342 -
1.1343 - try
1.1344 - {
1.1345 - // Try to reinitialize database connection
1.1346 - arise();
1.1347 - }
1.1348 - catch(SQLException ex)
1.1349 - {
1.1350 - Log.msg(ex.getMessage(), true);
1.1351 - restartConnection(ex);
1.1352 - }
1.1353 - }
1.1354 -
1.1355 -}