1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/org/sonews/daemon/storage/Database.java Fri Jun 26 16:48:50 2009 +0200
1.3 @@ -0,0 +1,1353 @@
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 + ResultSet rs = this.pstmtDeleteArticle0.executeQuery();
1.489 + rs.next();
1.490 +
1.491 + // We trust the ON DELETE CASCADE functionality to delete
1.492 + // orphaned references
1.493 +
1.494 + this.conn.commit();
1.495 + this.conn.setAutoCommit(true);
1.496 + }
1.497 + catch(SQLException ex)
1.498 + {
1.499 + throw ex;
1.500 + }
1.501 + }
1.502 +
1.503 + public Article getArticle(String messageID)
1.504 + throws SQLException
1.505 + {
1.506 + ResultSet rs = null;
1.507 + try
1.508 + {
1.509 + pstmtGetArticle0.setString(1, messageID);
1.510 + rs = pstmtGetArticle0.executeQuery();
1.511 +
1.512 + if(!rs.next())
1.513 + {
1.514 + return null;
1.515 + }
1.516 + else
1.517 + {
1.518 + String body = new String(rs.getBytes("body"));
1.519 + String headers = getArticleHeaders(rs.getInt("article_id"));
1.520 + return new Article(headers, body);
1.521 + }
1.522 + }
1.523 + catch(SQLException ex)
1.524 + {
1.525 + restartConnection(ex);
1.526 + return getArticle(messageID);
1.527 + }
1.528 + finally
1.529 + {
1.530 + if(rs != null)
1.531 + {
1.532 + rs.close();
1.533 + restarts = 0; // Reset error count
1.534 + }
1.535 + }
1.536 + }
1.537 +
1.538 + /**
1.539 + * Retrieves an article by its ID.
1.540 + * @param articleID
1.541 + * @return
1.542 + * @throws java.sql.SQLException
1.543 + */
1.544 + public Article getArticle(long articleIndex, long gid)
1.545 + throws SQLException
1.546 + {
1.547 + ResultSet rs = null;
1.548 +
1.549 + try
1.550 + {
1.551 + this.pstmtGetArticle1.setLong(1, articleIndex);
1.552 + this.pstmtGetArticle1.setLong(2, gid);
1.553 +
1.554 + rs = this.pstmtGetArticle1.executeQuery();
1.555 +
1.556 + if(rs.next())
1.557 + {
1.558 + String body = new String(rs.getBytes("body"));
1.559 + String headers = getArticleHeaders(rs.getInt("article_id"));
1.560 + return new Article(headers, body);
1.561 + }
1.562 + else
1.563 + {
1.564 + return null;
1.565 + }
1.566 + }
1.567 + catch(SQLException ex)
1.568 + {
1.569 + restartConnection(ex);
1.570 + return getArticle(articleIndex, gid);
1.571 + }
1.572 + finally
1.573 + {
1.574 + if(rs != null)
1.575 + {
1.576 + rs.close();
1.577 + restarts = 0;
1.578 + }
1.579 + }
1.580 + }
1.581 +
1.582 + public String getArticleHeaders(long articleID)
1.583 + throws SQLException
1.584 + {
1.585 + ResultSet rs = null;
1.586 +
1.587 + try
1.588 + {
1.589 + this.pstmtGetArticleHeaders.setLong(1, articleID);
1.590 + rs = this.pstmtGetArticleHeaders.executeQuery();
1.591 +
1.592 + StringBuilder buf = new StringBuilder();
1.593 + if(rs.next())
1.594 + {
1.595 + for(;;)
1.596 + {
1.597 + buf.append(rs.getString(1)); // key
1.598 + buf.append(": ");
1.599 + String foldedValue = MimeUtility.fold(0, rs.getString(2));
1.600 + buf.append(foldedValue); // value
1.601 + if(rs.next())
1.602 + {
1.603 + buf.append("\r\n");
1.604 + }
1.605 + else
1.606 + {
1.607 + break;
1.608 + }
1.609 + }
1.610 + }
1.611 +
1.612 + return buf.toString();
1.613 + }
1.614 + catch(SQLException ex)
1.615 + {
1.616 + restartConnection(ex);
1.617 + return getArticleHeaders(articleID);
1.618 + }
1.619 + finally
1.620 + {
1.621 + if(rs != null)
1.622 + rs.close();
1.623 + }
1.624 + }
1.625 +
1.626 + public long getArticleIndex(Article article, Group group)
1.627 + throws SQLException
1.628 + {
1.629 + ResultSet rs = null;
1.630 +
1.631 + try
1.632 + {
1.633 + this.pstmtGetArticleIndex.setString(1, article.getMessageID());
1.634 + this.pstmtGetArticleIndex.setLong(2, group.getID());
1.635 +
1.636 + rs = this.pstmtGetArticleIndex.executeQuery();
1.637 + if(rs.next())
1.638 + {
1.639 + return rs.getLong(1);
1.640 + }
1.641 + else
1.642 + {
1.643 + return -1;
1.644 + }
1.645 + }
1.646 + catch(SQLException ex)
1.647 + {
1.648 + restartConnection(ex);
1.649 + return getArticleIndex(article, group);
1.650 + }
1.651 + finally
1.652 + {
1.653 + if(rs != null)
1.654 + rs.close();
1.655 + }
1.656 + }
1.657 +
1.658 + /**
1.659 + * Returns a list of Long/Article Pairs.
1.660 + * @throws java.sql.SQLException
1.661 + */
1.662 + public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, int first, int last)
1.663 + throws SQLException
1.664 + {
1.665 + ResultSet rs = null;
1.666 +
1.667 + try
1.668 + {
1.669 + this.pstmtGetArticleHeads.setLong(1, group.getID());
1.670 + this.pstmtGetArticleHeads.setInt(2, first);
1.671 + this.pstmtGetArticleHeads.setInt(3, last);
1.672 + rs = pstmtGetArticleHeads.executeQuery();
1.673 +
1.674 + List<Pair<Long, ArticleHead>> articles
1.675 + = new ArrayList<Pair<Long, ArticleHead>>();
1.676 +
1.677 + while (rs.next())
1.678 + {
1.679 + long aid = rs.getLong("article_id");
1.680 + long aidx = rs.getLong("article_index");
1.681 + String headers = getArticleHeaders(aid);
1.682 + articles.add(new Pair<Long, ArticleHead>(aidx,
1.683 + new ArticleHead(headers)));
1.684 + }
1.685 +
1.686 + return articles;
1.687 + }
1.688 + catch(SQLException ex)
1.689 + {
1.690 + restartConnection(ex);
1.691 + return getArticleHeads(group, first, last);
1.692 + }
1.693 + finally
1.694 + {
1.695 + if(rs != null)
1.696 + rs.close();
1.697 + }
1.698 + }
1.699 +
1.700 + public List<Long> getArticleNumbers(long gid)
1.701 + throws SQLException
1.702 + {
1.703 + ResultSet rs = null;
1.704 + try
1.705 + {
1.706 + List<Long> ids = new ArrayList<Long>();
1.707 + this.pstmtGetArticleIDs.setLong(1, gid);
1.708 + rs = this.pstmtGetArticleIDs.executeQuery();
1.709 + while(rs.next())
1.710 + {
1.711 + ids.add(rs.getLong(1));
1.712 + }
1.713 + return ids;
1.714 + }
1.715 + catch(SQLException ex)
1.716 + {
1.717 + restartConnection(ex);
1.718 + return getArticleNumbers(gid);
1.719 + }
1.720 + finally
1.721 + {
1.722 + if(rs != null)
1.723 + {
1.724 + rs.close();
1.725 + restarts = 0; // Clear the restart count after successful request
1.726 + }
1.727 + }
1.728 + }
1.729 +
1.730 + public String getConfigValue(String key)
1.731 + throws SQLException
1.732 + {
1.733 + ResultSet rs = null;
1.734 + try
1.735 + {
1.736 + this.pstmtGetConfigValue.setString(1, key);
1.737 +
1.738 + rs = this.pstmtGetConfigValue.executeQuery();
1.739 + if(rs.next())
1.740 + {
1.741 + return rs.getString(1); // First data on index 1 not 0
1.742 + }
1.743 + else
1.744 + {
1.745 + return null;
1.746 + }
1.747 + }
1.748 + catch(SQLException ex)
1.749 + {
1.750 + restartConnection(ex);
1.751 + return getConfigValue(key);
1.752 + }
1.753 + finally
1.754 + {
1.755 + if(rs != null)
1.756 + {
1.757 + rs.close();
1.758 + restarts = 0; // Clear the restart count after successful request
1.759 + }
1.760 + }
1.761 + }
1.762 +
1.763 + public int getEventsCount(byte type, long start, long end, Group group)
1.764 + throws SQLException
1.765 + {
1.766 + ResultSet rs = null;
1.767 +
1.768 + try
1.769 + {
1.770 + if(group == null)
1.771 + {
1.772 + this.pstmtGetEventsCount0.setInt(1, type);
1.773 + this.pstmtGetEventsCount0.setLong(2, start);
1.774 + this.pstmtGetEventsCount0.setLong(3, end);
1.775 + rs = this.pstmtGetEventsCount0.executeQuery();
1.776 + }
1.777 + else
1.778 + {
1.779 + this.pstmtGetEventsCount1.setInt(1, type);
1.780 + this.pstmtGetEventsCount1.setLong(2, start);
1.781 + this.pstmtGetEventsCount1.setLong(3, end);
1.782 + this.pstmtGetEventsCount1.setLong(4, group.getID());
1.783 + rs = this.pstmtGetEventsCount1.executeQuery();
1.784 + }
1.785 +
1.786 + if(rs.next())
1.787 + {
1.788 + return rs.getInt(1);
1.789 + }
1.790 + else
1.791 + {
1.792 + return -1;
1.793 + }
1.794 + }
1.795 + catch(SQLException ex)
1.796 + {
1.797 + restartConnection(ex);
1.798 + return getEventsCount(type, start, end, group);
1.799 + }
1.800 + finally
1.801 + {
1.802 + if(rs != null)
1.803 + rs.close();
1.804 + }
1.805 + }
1.806 +
1.807 + /**
1.808 + * Reads all Groups from the Database.
1.809 + * @return
1.810 + * @throws java.sql.SQLException
1.811 + */
1.812 + public List<Group> getGroups()
1.813 + throws SQLException
1.814 + {
1.815 + ResultSet rs;
1.816 + List<Group> buffer = new ArrayList<Group>();
1.817 + Statement stmt = null;
1.818 +
1.819 + try
1.820 + {
1.821 + stmt = conn.createStatement();
1.822 + rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
1.823 +
1.824 + while(rs.next())
1.825 + {
1.826 + String name = rs.getString("name");
1.827 + long id = rs.getLong("group_id");
1.828 + int flags = rs.getInt("flags");
1.829 +
1.830 + Group group = new Group(name, id, flags);
1.831 + buffer.add(group);
1.832 + }
1.833 +
1.834 + return buffer;
1.835 + }
1.836 + catch(SQLException ex)
1.837 + {
1.838 + restartConnection(ex);
1.839 + return getGroups();
1.840 + }
1.841 + finally
1.842 + {
1.843 + if(stmt != null)
1.844 + stmt.close(); // Implicitely closes ResultSets
1.845 + }
1.846 + }
1.847 +
1.848 + public String getGroupForList(InternetAddress listAddress)
1.849 + throws SQLException
1.850 + {
1.851 + ResultSet rs = null;
1.852 +
1.853 + try
1.854 + {
1.855 + this.pstmtGetGroupForList.setString(1, listAddress.getAddress());
1.856 +
1.857 + rs = this.pstmtGetGroupForList.executeQuery();
1.858 + if (rs.next())
1.859 + {
1.860 + return rs.getString(1);
1.861 + }
1.862 + else
1.863 + {
1.864 + return null;
1.865 + }
1.866 + }
1.867 + catch(SQLException ex)
1.868 + {
1.869 + restartConnection(ex);
1.870 + return getGroupForList(listAddress);
1.871 + }
1.872 + finally
1.873 + {
1.874 + if(rs != null)
1.875 + rs.close();
1.876 + }
1.877 + }
1.878 +
1.879 + /**
1.880 + * Returns the Group that is identified by the name.
1.881 + * @param name
1.882 + * @return
1.883 + * @throws java.sql.SQLException
1.884 + */
1.885 + public Group getGroup(String name)
1.886 + throws SQLException
1.887 + {
1.888 + ResultSet rs = null;
1.889 +
1.890 + try
1.891 + {
1.892 + this.pstmtGetGroup0.setString(1, name);
1.893 + rs = this.pstmtGetGroup0.executeQuery();
1.894 +
1.895 + if (!rs.next())
1.896 + {
1.897 + return null;
1.898 + }
1.899 + else
1.900 + {
1.901 + long id = rs.getLong("group_id");
1.902 + int flags = rs.getInt("flags");
1.903 + return new Group(name, id, flags);
1.904 + }
1.905 + }
1.906 + catch(SQLException ex)
1.907 + {
1.908 + restartConnection(ex);
1.909 + return getGroup(name);
1.910 + }
1.911 + finally
1.912 + {
1.913 + if(rs != null)
1.914 + rs.close();
1.915 + }
1.916 + }
1.917 +
1.918 + public String getListForGroup(String group)
1.919 + throws SQLException
1.920 + {
1.921 + ResultSet rs = null;
1.922 +
1.923 + try
1.924 + {
1.925 + this.pstmtGetListForGroup.setString(1, group);
1.926 + rs = this.pstmtGetListForGroup.executeQuery();
1.927 + if (rs.next())
1.928 + {
1.929 + return rs.getString(1);
1.930 + }
1.931 + else
1.932 + {
1.933 + return null;
1.934 + }
1.935 + }
1.936 + catch(SQLException ex)
1.937 + {
1.938 + restartConnection(ex);
1.939 + return getListForGroup(group);
1.940 + }
1.941 + finally
1.942 + {
1.943 + if(rs != null)
1.944 + rs.close();
1.945 + }
1.946 + }
1.947 +
1.948 + private int getMaxArticleIndex(long groupID)
1.949 + throws SQLException
1.950 + {
1.951 + ResultSet rs = null;
1.952 +
1.953 + try
1.954 + {
1.955 + this.pstmtGetMaxArticleIndex.setLong(1, groupID);
1.956 + rs = this.pstmtGetMaxArticleIndex.executeQuery();
1.957 +
1.958 + int maxIndex = 0;
1.959 + if (rs.next())
1.960 + {
1.961 + maxIndex = rs.getInt(1);
1.962 + }
1.963 +
1.964 + return maxIndex;
1.965 + }
1.966 + catch(SQLException ex)
1.967 + {
1.968 + restartConnection(ex);
1.969 + return getMaxArticleIndex(groupID);
1.970 + }
1.971 + finally
1.972 + {
1.973 + if(rs != null)
1.974 + rs.close();
1.975 + }
1.976 + }
1.977 +
1.978 + private int getMaxArticleID()
1.979 + throws SQLException
1.980 + {
1.981 + ResultSet rs = null;
1.982 +
1.983 + try
1.984 + {
1.985 + rs = this.pstmtGetMaxArticleID.executeQuery();
1.986 +
1.987 + int maxIndex = 0;
1.988 + if (rs.next())
1.989 + {
1.990 + maxIndex = rs.getInt(1);
1.991 + }
1.992 +
1.993 + return maxIndex;
1.994 + }
1.995 + catch(SQLException ex)
1.996 + {
1.997 + restartConnection(ex);
1.998 + return getMaxArticleID();
1.999 + }
1.1000 + finally
1.1001 + {
1.1002 + if(rs != null)
1.1003 + rs.close();
1.1004 + }
1.1005 + }
1.1006 +
1.1007 + public int getLastArticleNumber(Group group)
1.1008 + throws SQLException
1.1009 + {
1.1010 + ResultSet rs = null;
1.1011 +
1.1012 + try
1.1013 + {
1.1014 + this.pstmtGetLastArticleNumber.setLong(1, group.getID());
1.1015 + rs = this.pstmtGetLastArticleNumber.executeQuery();
1.1016 + if (rs.next())
1.1017 + {
1.1018 + return rs.getInt(1);
1.1019 + }
1.1020 + else
1.1021 + {
1.1022 + return 0;
1.1023 + }
1.1024 + }
1.1025 + catch(SQLException ex)
1.1026 + {
1.1027 + restartConnection(ex);
1.1028 + return getLastArticleNumber(group);
1.1029 + }
1.1030 + finally
1.1031 + {
1.1032 + if(rs != null)
1.1033 + rs.close();
1.1034 + }
1.1035 + }
1.1036 +
1.1037 + public int getFirstArticleNumber(Group group)
1.1038 + throws SQLException
1.1039 + {
1.1040 + ResultSet rs = null;
1.1041 + try
1.1042 + {
1.1043 + this.pstmtGetFirstArticleNumber.setLong(1, group.getID());
1.1044 + rs = this.pstmtGetFirstArticleNumber.executeQuery();
1.1045 + if(rs.next())
1.1046 + {
1.1047 + return rs.getInt(1);
1.1048 + }
1.1049 + else
1.1050 + {
1.1051 + return 0;
1.1052 + }
1.1053 + }
1.1054 + catch(SQLException ex)
1.1055 + {
1.1056 + restartConnection(ex);
1.1057 + return getFirstArticleNumber(group);
1.1058 + }
1.1059 + finally
1.1060 + {
1.1061 + if(rs != null)
1.1062 + rs.close();
1.1063 + }
1.1064 + }
1.1065 +
1.1066 + /**
1.1067 + * Returns a group name identified by the given id.
1.1068 + * @param id
1.1069 + * @return
1.1070 + * @throws java.sql.SQLException
1.1071 + */
1.1072 + public String getGroup(int id)
1.1073 + throws SQLException
1.1074 + {
1.1075 + ResultSet rs = null;
1.1076 +
1.1077 + try
1.1078 + {
1.1079 + this.pstmtGetGroup1.setInt(1, id);
1.1080 + rs = this.pstmtGetGroup1.executeQuery();
1.1081 +
1.1082 + if (rs.next())
1.1083 + {
1.1084 + return rs.getString(1);
1.1085 + }
1.1086 + else
1.1087 + {
1.1088 + return null;
1.1089 + }
1.1090 + }
1.1091 + catch(SQLException ex)
1.1092 + {
1.1093 + restartConnection(ex);
1.1094 + return getGroup(id);
1.1095 + }
1.1096 + finally
1.1097 + {
1.1098 + if(rs != null)
1.1099 + rs.close();
1.1100 + }
1.1101 + }
1.1102 +
1.1103 + public double getNumberOfEventsPerHour(int key, long gid)
1.1104 + throws SQLException
1.1105 + {
1.1106 + String gidquery = "";
1.1107 + if(gid >= 0)
1.1108 + {
1.1109 + gidquery = " AND group_id = " + gid;
1.1110 + }
1.1111 +
1.1112 + Statement stmt = null;
1.1113 + ResultSet rs = null;
1.1114 +
1.1115 + try
1.1116 + {
1.1117 + stmt = this.conn.createStatement();
1.1118 + rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))" +
1.1119 + " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
1.1120 +
1.1121 + if(rs.next())
1.1122 + {
1.1123 + restarts = 0; // reset error count
1.1124 + return rs.getDouble(1);
1.1125 + }
1.1126 + else
1.1127 + {
1.1128 + return Double.NaN;
1.1129 + }
1.1130 + }
1.1131 + catch(SQLException ex)
1.1132 + {
1.1133 + restartConnection(ex);
1.1134 + return getNumberOfEventsPerHour(key, gid);
1.1135 + }
1.1136 + finally
1.1137 + {
1.1138 + if(stmt != null)
1.1139 + {
1.1140 + stmt.close();
1.1141 + }
1.1142 +
1.1143 + if(rs != null)
1.1144 + {
1.1145 + rs.close();
1.1146 + }
1.1147 + }
1.1148 + }
1.1149 +
1.1150 + public int getPostingsCount(String groupname)
1.1151 + throws SQLException
1.1152 + {
1.1153 + ResultSet rs = null;
1.1154 +
1.1155 + try
1.1156 + {
1.1157 + this.pstmtGetPostingsCount.setString(1, groupname);
1.1158 + rs = this.pstmtGetPostingsCount.executeQuery();
1.1159 + if(rs.next())
1.1160 + {
1.1161 + return rs.getInt(1);
1.1162 + }
1.1163 + else
1.1164 + {
1.1165 + Log.msg("Warning: Count on postings return nothing!", true);
1.1166 + return 0;
1.1167 + }
1.1168 + }
1.1169 + catch(SQLException ex)
1.1170 + {
1.1171 + restartConnection(ex);
1.1172 + return getPostingsCount(groupname);
1.1173 + }
1.1174 + finally
1.1175 + {
1.1176 + if(rs != null)
1.1177 + rs.close();
1.1178 + }
1.1179 + }
1.1180 +
1.1181 + public List<Subscription> getSubscriptions(int feedtype)
1.1182 + throws SQLException
1.1183 + {
1.1184 + ResultSet rs = null;
1.1185 +
1.1186 + try
1.1187 + {
1.1188 + List<Subscription> subs = new ArrayList<Subscription>();
1.1189 + this.pstmtGetSubscriptions.setInt(1, feedtype);
1.1190 + rs = this.pstmtGetSubscriptions.executeQuery();
1.1191 +
1.1192 + while(rs.next())
1.1193 + {
1.1194 + String host = rs.getString("host");
1.1195 + String group = rs.getString("name");
1.1196 + int port = rs.getInt("port");
1.1197 + subs.add(new Subscription(host, port, feedtype, group));
1.1198 + }
1.1199 +
1.1200 + return subs;
1.1201 + }
1.1202 + catch(SQLException ex)
1.1203 + {
1.1204 + restartConnection(ex);
1.1205 + return getSubscriptions(feedtype);
1.1206 + }
1.1207 + finally
1.1208 + {
1.1209 + if(rs != null)
1.1210 + rs.close();
1.1211 + }
1.1212 + }
1.1213 +
1.1214 + /**
1.1215 + * Checks if there is an article with the given messageid in the Database.
1.1216 + * @param name
1.1217 + * @return
1.1218 + * @throws java.sql.SQLException
1.1219 + */
1.1220 + public boolean isArticleExisting(String messageID)
1.1221 + throws SQLException
1.1222 + {
1.1223 + ResultSet rs = null;
1.1224 +
1.1225 + try
1.1226 + {
1.1227 + this.pstmtIsArticleExisting.setString(1, messageID);
1.1228 + rs = this.pstmtIsArticleExisting.executeQuery();
1.1229 + return rs.next() && rs.getInt(1) == 1;
1.1230 + }
1.1231 + catch(SQLException ex)
1.1232 + {
1.1233 + restartConnection(ex);
1.1234 + return isArticleExisting(messageID);
1.1235 + }
1.1236 + finally
1.1237 + {
1.1238 + if(rs != null)
1.1239 + rs.close();
1.1240 + }
1.1241 + }
1.1242 +
1.1243 + /**
1.1244 + * Checks if there is a group with the given name in the Database.
1.1245 + * @param name
1.1246 + * @return
1.1247 + * @throws java.sql.SQLException
1.1248 + */
1.1249 + public boolean isGroupExisting(String name)
1.1250 + throws SQLException
1.1251 + {
1.1252 + ResultSet rs = null;
1.1253 +
1.1254 + try
1.1255 + {
1.1256 + this.pstmtIsGroupExisting.setString(1, name);
1.1257 + rs = this.pstmtIsGroupExisting.executeQuery();
1.1258 + return rs.next();
1.1259 + }
1.1260 + catch(SQLException ex)
1.1261 + {
1.1262 + restartConnection(ex);
1.1263 + return isGroupExisting(name);
1.1264 + }
1.1265 + finally
1.1266 + {
1.1267 + if(rs != null)
1.1268 + rs.close();
1.1269 + }
1.1270 + }
1.1271 +
1.1272 + public void setConfigValue(String key, String value)
1.1273 + throws SQLException
1.1274 + {
1.1275 + try
1.1276 + {
1.1277 + conn.setAutoCommit(false);
1.1278 + this.pstmtSetConfigValue0.setString(1, key);
1.1279 + this.pstmtSetConfigValue0.execute();
1.1280 + this.pstmtSetConfigValue1.setString(1, key);
1.1281 + this.pstmtSetConfigValue1.setString(2, value);
1.1282 + this.pstmtSetConfigValue1.execute();
1.1283 + conn.commit();
1.1284 + conn.setAutoCommit(true);
1.1285 + }
1.1286 + catch(SQLException ex)
1.1287 + {
1.1288 + restartConnection(ex);
1.1289 + setConfigValue(key, value);
1.1290 + }
1.1291 + }
1.1292 +
1.1293 + /**
1.1294 + * Closes the Database connection.
1.1295 + */
1.1296 + public void shutdown()
1.1297 + throws SQLException
1.1298 + {
1.1299 + if(this.conn != null)
1.1300 + {
1.1301 + this.conn.close();
1.1302 + }
1.1303 + }
1.1304 +
1.1305 + private void restartConnection(SQLException cause)
1.1306 + throws SQLException
1.1307 + {
1.1308 + restarts++;
1.1309 + Log.msg(Thread.currentThread()
1.1310 + + ": Database connection was closed (restart " + restarts + ").", false);
1.1311 +
1.1312 + if(restarts >= MAX_RESTARTS)
1.1313 + {
1.1314 + // Delete the current, probably broken Database instance.
1.1315 + // So no one can use the instance any more.
1.1316 + Database.instances.remove(Thread.currentThread());
1.1317 +
1.1318 + // Throw the exception upwards
1.1319 + throw cause;
1.1320 + }
1.1321 +
1.1322 + try
1.1323 + {
1.1324 + Thread.sleep(1500L * restarts);
1.1325 + }
1.1326 + catch(InterruptedException ex)
1.1327 + {
1.1328 + Log.msg("Interrupted: " + ex.getMessage(), false);
1.1329 + }
1.1330 +
1.1331 + // Try to properly close the old database connection
1.1332 + try
1.1333 + {
1.1334 + if(this.conn != null)
1.1335 + {
1.1336 + this.conn.close();
1.1337 + }
1.1338 + }
1.1339 + catch(SQLException ex)
1.1340 + {
1.1341 + Log.msg(ex.getMessage(), true);
1.1342 + }
1.1343 +
1.1344 + try
1.1345 + {
1.1346 + // Try to reinitialize database connection
1.1347 + arise();
1.1348 + }
1.1349 + catch(SQLException ex)
1.1350 + {
1.1351 + Log.msg(ex.getMessage(), true);
1.1352 + restartConnection(ex);
1.1353 + }
1.1354 + }
1.1355 +
1.1356 +}