1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/org/sonews/storage/impl/JDBCDatabase.java Wed Jul 22 14:04:05 2009 +0200
1.3 @@ -0,0 +1,1772 @@
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.storage.impl;
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.regex.Matcher;
1.34 +import java.util.regex.Pattern;
1.35 +import java.util.regex.PatternSyntaxException;
1.36 +import javax.mail.Header;
1.37 +import javax.mail.internet.InternetAddress;
1.38 +import javax.mail.internet.MimeUtility;
1.39 +import org.sonews.config.Config;
1.40 +import org.sonews.util.Log;
1.41 +import org.sonews.feed.Subscription;
1.42 +import org.sonews.storage.Article;
1.43 +import org.sonews.storage.ArticleHead;
1.44 +import org.sonews.storage.Channel;
1.45 +import org.sonews.storage.Group;
1.46 +import org.sonews.storage.Storage;
1.47 +import org.sonews.storage.StorageBackendException;
1.48 +import org.sonews.util.Pair;
1.49 +
1.50 +/**
1.51 + * JDBCDatabase facade class.
1.52 + * @author Christian Lins
1.53 + * @since sonews/0.5.0
1.54 + */
1.55 +// TODO: Refactor this class to reduce size (e.g. ArticleDatabase GroupDatabase)
1.56 +public class JDBCDatabase implements Storage
1.57 +{
1.58 +
1.59 + public static final int MAX_RESTARTS = 3;
1.60 +
1.61 + private Connection conn = null;
1.62 + private PreparedStatement pstmtAddArticle1 = null;
1.63 + private PreparedStatement pstmtAddArticle2 = null;
1.64 + private PreparedStatement pstmtAddArticle3 = null;
1.65 + private PreparedStatement pstmtAddArticle4 = null;
1.66 + private PreparedStatement pstmtAddGroup0 = null;
1.67 + private PreparedStatement pstmtAddEvent = null;
1.68 + private PreparedStatement pstmtCountArticles = null;
1.69 + private PreparedStatement pstmtCountGroups = null;
1.70 + private PreparedStatement pstmtDeleteArticle0 = null;
1.71 + private PreparedStatement pstmtDeleteArticle1 = null;
1.72 + private PreparedStatement pstmtDeleteArticle2 = null;
1.73 + private PreparedStatement pstmtDeleteArticle3 = null;
1.74 + private PreparedStatement pstmtGetArticle0 = null;
1.75 + private PreparedStatement pstmtGetArticle1 = null;
1.76 + private PreparedStatement pstmtGetArticleHeaders0 = null;
1.77 + private PreparedStatement pstmtGetArticleHeaders1 = null;
1.78 + private PreparedStatement pstmtGetArticleHeads = null;
1.79 + private PreparedStatement pstmtGetArticleIDs = null;
1.80 + private PreparedStatement pstmtGetArticleIndex = null;
1.81 + private PreparedStatement pstmtGetConfigValue = null;
1.82 + private PreparedStatement pstmtGetEventsCount0 = null;
1.83 + private PreparedStatement pstmtGetEventsCount1 = null;
1.84 + private PreparedStatement pstmtGetGroupForList = null;
1.85 + private PreparedStatement pstmtGetGroup0 = null;
1.86 + private PreparedStatement pstmtGetGroup1 = null;
1.87 + private PreparedStatement pstmtGetFirstArticleNumber = null;
1.88 + private PreparedStatement pstmtGetListForGroup = null;
1.89 + private PreparedStatement pstmtGetLastArticleNumber = null;
1.90 + private PreparedStatement pstmtGetMaxArticleID = null;
1.91 + private PreparedStatement pstmtGetMaxArticleIndex = null;
1.92 + private PreparedStatement pstmtGetOldestArticle = null;
1.93 + private PreparedStatement pstmtGetPostingsCount = null;
1.94 + private PreparedStatement pstmtGetSubscriptions = null;
1.95 + private PreparedStatement pstmtIsArticleExisting = null;
1.96 + private PreparedStatement pstmtIsGroupExisting = null;
1.97 + private PreparedStatement pstmtPurgeGroup0 = null;
1.98 + private PreparedStatement pstmtPurgeGroup1 = null;
1.99 + private PreparedStatement pstmtSetConfigValue0 = null;
1.100 + private PreparedStatement pstmtSetConfigValue1 = null;
1.101 + private PreparedStatement pstmtUpdateGroup = null;
1.102 +
1.103 + /** How many times the database connection was reinitialized */
1.104 + private int restarts = 0;
1.105 +
1.106 + /**
1.107 + * Rises the database: reconnect and recreate all prepared statements.
1.108 + * @throws java.lang.SQLException
1.109 + */
1.110 + protected void arise()
1.111 + throws SQLException
1.112 + {
1.113 + try
1.114 + {
1.115 + // Load database driver
1.116 + Class.forName(
1.117 + Config.inst().get(Config.STORAGE_DBMSDRIVER, "java.lang.Object"));
1.118 +
1.119 + // Establish database connection
1.120 + this.conn = DriverManager.getConnection(
1.121 + Config.inst().get(Config.STORAGE_DATABASE, "<not specified>"),
1.122 + Config.inst().get(Config.STORAGE_USER, "root"),
1.123 + Config.inst().get(Config.STORAGE_PASSWORD, ""));
1.124 +
1.125 + this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1.126 + if(this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE)
1.127 + {
1.128 + Log.msg("Warning: Database is NOT fully serializable!", false);
1.129 + }
1.130 +
1.131 + // Prepare statements for method addArticle()
1.132 + this.pstmtAddArticle1 = conn.prepareStatement(
1.133 + "INSERT INTO articles (article_id, body) VALUES(?, ?)");
1.134 + this.pstmtAddArticle2 = conn.prepareStatement(
1.135 + "INSERT INTO headers (article_id, header_key, header_value, header_index) " +
1.136 + "VALUES (?, ?, ?, ?)");
1.137 + this.pstmtAddArticle3 = conn.prepareStatement(
1.138 + "INSERT INTO postings (group_id, article_id, article_index)" +
1.139 + "VALUES (?, ?, ?)");
1.140 + this.pstmtAddArticle4 = conn.prepareStatement(
1.141 + "INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");
1.142 +
1.143 + // Prepare statement for method addStatValue()
1.144 + this.pstmtAddEvent = conn.prepareStatement(
1.145 + "INSERT INTO events VALUES (?, ?, ?)");
1.146 +
1.147 + // Prepare statement for method addGroup()
1.148 + this.pstmtAddGroup0 = conn.prepareStatement(
1.149 + "INSERT INTO groups (name, flags) VALUES (?, ?)");
1.150 +
1.151 + // Prepare statement for method countArticles()
1.152 + this.pstmtCountArticles = conn.prepareStatement(
1.153 + "SELECT Count(article_id) FROM article_ids");
1.154 +
1.155 + // Prepare statement for method countGroups()
1.156 + this.pstmtCountGroups = conn.prepareStatement(
1.157 + "SELECT Count(group_id) FROM groups WHERE " +
1.158 + "flags & " + Channel.DELETED + " = 0");
1.159 +
1.160 + // Prepare statements for method delete(article)
1.161 + this.pstmtDeleteArticle0 = conn.prepareStatement(
1.162 + "DELETE FROM articles WHERE article_id = " +
1.163 + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.164 + this.pstmtDeleteArticle1 = conn.prepareStatement(
1.165 + "DELETE FROM headers WHERE article_id = " +
1.166 + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.167 + this.pstmtDeleteArticle2 = conn.prepareStatement(
1.168 + "DELETE FROM postings WHERE article_id = " +
1.169 + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.170 + this.pstmtDeleteArticle3 = conn.prepareStatement(
1.171 + "DELETE FROM article_ids WHERE message_id = ?");
1.172 +
1.173 + // Prepare statements for methods getArticle()
1.174 + this.pstmtGetArticle0 = conn.prepareStatement(
1.175 + "SELECT * FROM articles WHERE article_id = " +
1.176 + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.177 + this.pstmtGetArticle1 = conn.prepareStatement(
1.178 + "SELECT * FROM articles WHERE article_id = " +
1.179 + "(SELECT article_id FROM postings WHERE " +
1.180 + "article_index = ? AND group_id = ?)");
1.181 +
1.182 + // Prepare statement for method getArticleHeaders()
1.183 + this.pstmtGetArticleHeaders0 = conn.prepareStatement(
1.184 + "SELECT header_key, header_value FROM headers WHERE article_id = ? " +
1.185 + "ORDER BY header_index ASC");
1.186 +
1.187 + // Prepare statement for method getArticleHeaders(regular expr pattern)
1.188 + this.pstmtGetArticleHeaders1 = conn.prepareStatement(
1.189 + "SELECT p.article_index, h.header_value FROM headers h " +
1.190 + "INNER JOIN postings p ON h.article_id = p.article_id " +
1.191 + "INNER JOIN groups g ON p.group_id = g.group_id " +
1.192 + "WHERE g.name = ? AND " +
1.193 + "h.header_key = ? AND " +
1.194 + "p.article_index >= ? " +
1.195 + "ORDER BY p.article_index ASC");
1.196 +
1.197 + this.pstmtGetArticleIDs = conn.prepareStatement(
1.198 + "SELECT article_index FROM postings WHERE group_id = ?");
1.199 +
1.200 + // Prepare statement for method getArticleIndex
1.201 + this.pstmtGetArticleIndex = conn.prepareStatement(
1.202 + "SELECT article_index FROM postings WHERE " +
1.203 + "article_id = (SELECT article_id FROM article_ids " +
1.204 + "WHERE message_id = ?) " +
1.205 + " AND group_id = ?");
1.206 +
1.207 + // Prepare statements for method getArticleHeads()
1.208 + this.pstmtGetArticleHeads = conn.prepareStatement(
1.209 + "SELECT article_id, article_index FROM postings WHERE " +
1.210 + "postings.group_id = ? AND article_index >= ? AND " +
1.211 + "article_index <= ?");
1.212 +
1.213 + // Prepare statements for method getConfigValue()
1.214 + this.pstmtGetConfigValue = conn.prepareStatement(
1.215 + "SELECT config_value FROM config WHERE config_key = ?");
1.216 +
1.217 + // Prepare statements for method getEventsCount()
1.218 + this.pstmtGetEventsCount0 = conn.prepareStatement(
1.219 + "SELECT Count(*) FROM events WHERE event_key = ? AND " +
1.220 + "event_time >= ? AND event_time < ?");
1.221 +
1.222 + this.pstmtGetEventsCount1 = conn.prepareStatement(
1.223 + "SELECT Count(*) FROM events WHERE event_key = ? AND " +
1.224 + "event_time >= ? AND event_time < ? AND group_id = ?");
1.225 +
1.226 + // Prepare statement for method getGroupForList()
1.227 + this.pstmtGetGroupForList = conn.prepareStatement(
1.228 + "SELECT name FROM groups INNER JOIN groups2list " +
1.229 + "ON groups.group_id = groups2list.group_id " +
1.230 + "WHERE groups2list.listaddress = ?");
1.231 +
1.232 + // Prepare statement for method getGroup()
1.233 + this.pstmtGetGroup0 = conn.prepareStatement(
1.234 + "SELECT group_id, flags FROM groups WHERE Name = ?");
1.235 + this.pstmtGetGroup1 = conn.prepareStatement(
1.236 + "SELECT name FROM groups WHERE group_id = ?");
1.237 +
1.238 + // Prepare statement for method getLastArticleNumber()
1.239 + this.pstmtGetLastArticleNumber = conn.prepareStatement(
1.240 + "SELECT Max(article_index) FROM postings WHERE group_id = ?");
1.241 +
1.242 + // Prepare statement for method getListForGroup()
1.243 + this.pstmtGetListForGroup = conn.prepareStatement(
1.244 + "SELECT listaddress FROM groups2list INNER JOIN groups " +
1.245 + "ON groups.group_id = groups2list.group_id WHERE name = ?");
1.246 +
1.247 + // Prepare statement for method getMaxArticleID()
1.248 + this.pstmtGetMaxArticleID = conn.prepareStatement(
1.249 + "SELECT Max(article_id) FROM articles");
1.250 +
1.251 + // Prepare statement for method getMaxArticleIndex()
1.252 + this.pstmtGetMaxArticleIndex = conn.prepareStatement(
1.253 + "SELECT Max(article_index) FROM postings WHERE group_id = ?");
1.254 +
1.255 + // Prepare statement for method getOldestArticle()
1.256 + this.pstmtGetOldestArticle = conn.prepareStatement(
1.257 + "SELECT message_id FROM article_ids WHERE article_id = " +
1.258 + "(SELECT Min(article_id) FROM article_ids)");
1.259 +
1.260 + // Prepare statement for method getFirstArticleNumber()
1.261 + this.pstmtGetFirstArticleNumber = conn.prepareStatement(
1.262 + "SELECT Min(article_index) FROM postings WHERE group_id = ?");
1.263 +
1.264 + // Prepare statement for method getPostingsCount()
1.265 + this.pstmtGetPostingsCount = conn.prepareStatement(
1.266 + "SELECT Count(*) FROM postings NATURAL JOIN groups " +
1.267 + "WHERE groups.name = ?");
1.268 +
1.269 + // Prepare statement for method getSubscriptions()
1.270 + this.pstmtGetSubscriptions = conn.prepareStatement(
1.271 + "SELECT host, port, name FROM peers NATURAL JOIN " +
1.272 + "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
1.273 +
1.274 + // Prepare statement for method isArticleExisting()
1.275 + this.pstmtIsArticleExisting = conn.prepareStatement(
1.276 + "SELECT Count(article_id) FROM article_ids WHERE message_id = ?");
1.277 +
1.278 + // Prepare statement for method isGroupExisting()
1.279 + this.pstmtIsGroupExisting = conn.prepareStatement(
1.280 + "SELECT * FROM groups WHERE name = ?");
1.281 +
1.282 + // Prepare statement for method setConfigValue()
1.283 + this.pstmtSetConfigValue0 = conn.prepareStatement(
1.284 + "DELETE FROM config WHERE config_key = ?");
1.285 + this.pstmtSetConfigValue1 = conn.prepareStatement(
1.286 + "INSERT INTO config VALUES(?, ?)");
1.287 +
1.288 + // Prepare statements for method purgeGroup()
1.289 + this.pstmtPurgeGroup0 = conn.prepareStatement(
1.290 + "DELETE FROM peer_subscriptions WHERE group_id = ?");
1.291 + this.pstmtPurgeGroup1 = conn.prepareStatement(
1.292 + "DELETE FROM groups WHERE group_id = ?");
1.293 +
1.294 + // Prepare statement for method update(Group)
1.295 + this.pstmtUpdateGroup = conn.prepareStatement(
1.296 + "UPDATE groups SET flags = ?, name = ? WHERE group_id = ?");
1.297 + }
1.298 + catch(ClassNotFoundException ex)
1.299 + {
1.300 + throw new Error("JDBC Driver not found!", ex);
1.301 + }
1.302 + }
1.303 +
1.304 + /**
1.305 + * Adds an article to the database.
1.306 + * @param article
1.307 + * @return
1.308 + * @throws java.sql.SQLException
1.309 + */
1.310 + @Override
1.311 + public void addArticle(final Article article)
1.312 + throws StorageBackendException
1.313 + {
1.314 + try
1.315 + {
1.316 + this.conn.setAutoCommit(false);
1.317 +
1.318 + int newArticleID = getMaxArticleID() + 1;
1.319 +
1.320 + // Fill prepared statement with values;
1.321 + // writes body to article table
1.322 + pstmtAddArticle1.setInt(1, newArticleID);
1.323 + pstmtAddArticle1.setBytes(2, article.getBody());
1.324 + pstmtAddArticle1.execute();
1.325 +
1.326 + // Add headers
1.327 + Enumeration headers = article.getAllHeaders();
1.328 + for(int n = 0; headers.hasMoreElements(); n++)
1.329 + {
1.330 + Header header = (Header)headers.nextElement();
1.331 + pstmtAddArticle2.setInt(1, newArticleID);
1.332 + pstmtAddArticle2.setString(2, header.getName().toLowerCase());
1.333 + pstmtAddArticle2.setString(3,
1.334 + header.getValue().replaceAll("[\r\n]", ""));
1.335 + pstmtAddArticle2.setInt(4, n);
1.336 + pstmtAddArticle2.execute();
1.337 + }
1.338 +
1.339 + // For each newsgroup add a reference
1.340 + List<Group> groups = article.getGroups();
1.341 + for(Group group : groups)
1.342 + {
1.343 + pstmtAddArticle3.setLong(1, group.getInternalID());
1.344 + pstmtAddArticle3.setInt(2, newArticleID);
1.345 + pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getInternalID()) + 1);
1.346 + pstmtAddArticle3.execute();
1.347 + }
1.348 +
1.349 + // Write message-id to article_ids table
1.350 + this.pstmtAddArticle4.setInt(1, newArticleID);
1.351 + this.pstmtAddArticle4.setString(2, article.getMessageID());
1.352 + this.pstmtAddArticle4.execute();
1.353 +
1.354 + this.conn.commit();
1.355 + this.conn.setAutoCommit(true);
1.356 +
1.357 + this.restarts = 0; // Reset error count
1.358 + }
1.359 + catch(SQLException ex)
1.360 + {
1.361 + try
1.362 + {
1.363 + this.conn.rollback(); // Rollback changes
1.364 + }
1.365 + catch(SQLException ex2)
1.366 + {
1.367 + Log.msg("Rollback of addArticle() failed: " + ex2, false);
1.368 + }
1.369 +
1.370 + try
1.371 + {
1.372 + this.conn.setAutoCommit(true); // and release locks
1.373 + }
1.374 + catch(SQLException ex2)
1.375 + {
1.376 + Log.msg("setAutoCommit(true) of addArticle() failed: " + ex2, false);
1.377 + }
1.378 +
1.379 + restartConnection(ex);
1.380 + addArticle(article);
1.381 + }
1.382 + }
1.383 +
1.384 + /**
1.385 + * Adds a group to the JDBCDatabase. This method is not accessible via NNTP.
1.386 + * @param name
1.387 + * @throws java.sql.SQLException
1.388 + */
1.389 + @Override
1.390 + public void addGroup(String name, int flags)
1.391 + throws StorageBackendException
1.392 + {
1.393 + try
1.394 + {
1.395 + this.conn.setAutoCommit(false);
1.396 + pstmtAddGroup0.setString(1, name);
1.397 + pstmtAddGroup0.setInt(2, flags);
1.398 +
1.399 + pstmtAddGroup0.executeUpdate();
1.400 + this.conn.commit();
1.401 + this.conn.setAutoCommit(true);
1.402 + this.restarts = 0; // Reset error count
1.403 + }
1.404 + catch(SQLException ex)
1.405 + {
1.406 + try
1.407 + {
1.408 + this.conn.rollback();
1.409 + this.conn.setAutoCommit(true);
1.410 + }
1.411 + catch(SQLException ex2)
1.412 + {
1.413 + ex2.printStackTrace();
1.414 + }
1.415 +
1.416 + restartConnection(ex);
1.417 + addGroup(name, flags);
1.418 + }
1.419 + }
1.420 +
1.421 + @Override
1.422 + public void addEvent(long time, int type, long gid)
1.423 + throws StorageBackendException
1.424 + {
1.425 + try
1.426 + {
1.427 + this.conn.setAutoCommit(false);
1.428 + this.pstmtAddEvent.setLong(1, time);
1.429 + this.pstmtAddEvent.setInt(2, type);
1.430 + this.pstmtAddEvent.setLong(3, gid);
1.431 + this.pstmtAddEvent.executeUpdate();
1.432 + this.conn.commit();
1.433 + this.conn.setAutoCommit(true);
1.434 + this.restarts = 0;
1.435 + }
1.436 + catch(SQLException ex)
1.437 + {
1.438 + try
1.439 + {
1.440 + this.conn.rollback();
1.441 + this.conn.setAutoCommit(true);
1.442 + }
1.443 + catch(SQLException ex2)
1.444 + {
1.445 + ex2.printStackTrace();
1.446 + }
1.447 +
1.448 + restartConnection(ex);
1.449 + addEvent(time, type, gid);
1.450 + }
1.451 + }
1.452 +
1.453 + @Override
1.454 + public int countArticles()
1.455 + throws StorageBackendException
1.456 + {
1.457 + ResultSet rs = null;
1.458 +
1.459 + try
1.460 + {
1.461 + rs = this.pstmtCountArticles.executeQuery();
1.462 + if(rs.next())
1.463 + {
1.464 + return rs.getInt(1);
1.465 + }
1.466 + else
1.467 + {
1.468 + return -1;
1.469 + }
1.470 + }
1.471 + catch(SQLException ex)
1.472 + {
1.473 + restartConnection(ex);
1.474 + return countArticles();
1.475 + }
1.476 + finally
1.477 + {
1.478 + if(rs != null)
1.479 + {
1.480 + try
1.481 + {
1.482 + rs.close();
1.483 + }
1.484 + catch(SQLException ex)
1.485 + {
1.486 + ex.printStackTrace();
1.487 + }
1.488 + restarts = 0;
1.489 + }
1.490 + }
1.491 + }
1.492 +
1.493 + @Override
1.494 + public int countGroups()
1.495 + throws StorageBackendException
1.496 + {
1.497 + ResultSet rs = null;
1.498 +
1.499 + try
1.500 + {
1.501 + rs = this.pstmtCountGroups.executeQuery();
1.502 + if(rs.next())
1.503 + {
1.504 + return rs.getInt(1);
1.505 + }
1.506 + else
1.507 + {
1.508 + return -1;
1.509 + }
1.510 + }
1.511 + catch(SQLException ex)
1.512 + {
1.513 + restartConnection(ex);
1.514 + return countGroups();
1.515 + }
1.516 + finally
1.517 + {
1.518 + if(rs != null)
1.519 + {
1.520 + try
1.521 + {
1.522 + rs.close();
1.523 + }
1.524 + catch(SQLException ex)
1.525 + {
1.526 + ex.printStackTrace();
1.527 + }
1.528 + restarts = 0;
1.529 + }
1.530 + }
1.531 + }
1.532 +
1.533 + @Override
1.534 + public void delete(final String messageID)
1.535 + throws StorageBackendException
1.536 + {
1.537 + try
1.538 + {
1.539 + this.conn.setAutoCommit(false);
1.540 +
1.541 + this.pstmtDeleteArticle0.setString(1, messageID);
1.542 + int rs = this.pstmtDeleteArticle0.executeUpdate();
1.543 +
1.544 + // We do not trust the ON DELETE CASCADE functionality to delete
1.545 + // orphaned references...
1.546 + this.pstmtDeleteArticle1.setString(1, messageID);
1.547 + rs = this.pstmtDeleteArticle1.executeUpdate();
1.548 +
1.549 + this.pstmtDeleteArticle2.setString(1, messageID);
1.550 + rs = this.pstmtDeleteArticle2.executeUpdate();
1.551 +
1.552 + this.pstmtDeleteArticle3.setString(1, messageID);
1.553 + rs = this.pstmtDeleteArticle3.executeUpdate();
1.554 +
1.555 + this.conn.commit();
1.556 + this.conn.setAutoCommit(true);
1.557 + }
1.558 + catch(SQLException ex)
1.559 + {
1.560 + throw new StorageBackendException(ex);
1.561 + }
1.562 + }
1.563 +
1.564 + @Override
1.565 + public Article getArticle(String messageID)
1.566 + throws StorageBackendException
1.567 + {
1.568 + ResultSet rs = null;
1.569 + try
1.570 + {
1.571 + pstmtGetArticle0.setString(1, messageID);
1.572 + rs = pstmtGetArticle0.executeQuery();
1.573 +
1.574 + if(!rs.next())
1.575 + {
1.576 + return null;
1.577 + }
1.578 + else
1.579 + {
1.580 + byte[] body = rs.getBytes("body");
1.581 + String headers = getArticleHeaders(rs.getInt("article_id"));
1.582 + return new Article(headers, body);
1.583 + }
1.584 + }
1.585 + catch(SQLException ex)
1.586 + {
1.587 + restartConnection(ex);
1.588 + return getArticle(messageID);
1.589 + }
1.590 + finally
1.591 + {
1.592 + if(rs != null)
1.593 + {
1.594 + try
1.595 + {
1.596 + rs.close();
1.597 + }
1.598 + catch(SQLException ex)
1.599 + {
1.600 + ex.printStackTrace();
1.601 + }
1.602 + restarts = 0; // Reset error count
1.603 + }
1.604 + }
1.605 + }
1.606 +
1.607 + /**
1.608 + * Retrieves an article by its ID.
1.609 + * @param articleID
1.610 + * @return
1.611 + * @throws StorageBackendException
1.612 + */
1.613 + @Override
1.614 + public Article getArticle(long articleIndex, long gid)
1.615 + throws StorageBackendException
1.616 + {
1.617 + ResultSet rs = null;
1.618 +
1.619 + try
1.620 + {
1.621 + this.pstmtGetArticle1.setLong(1, articleIndex);
1.622 + this.pstmtGetArticle1.setLong(2, gid);
1.623 +
1.624 + rs = this.pstmtGetArticle1.executeQuery();
1.625 +
1.626 + if(rs.next())
1.627 + {
1.628 + byte[] body = rs.getBytes("body");
1.629 + String headers = getArticleHeaders(rs.getInt("article_id"));
1.630 + return new Article(headers, body);
1.631 + }
1.632 + else
1.633 + {
1.634 + return null;
1.635 + }
1.636 + }
1.637 + catch(SQLException ex)
1.638 + {
1.639 + restartConnection(ex);
1.640 + return getArticle(articleIndex, gid);
1.641 + }
1.642 + finally
1.643 + {
1.644 + if(rs != null)
1.645 + {
1.646 + try
1.647 + {
1.648 + rs.close();
1.649 + }
1.650 + catch(SQLException ex)
1.651 + {
1.652 + ex.printStackTrace();
1.653 + }
1.654 + restarts = 0;
1.655 + }
1.656 + }
1.657 + }
1.658 +
1.659 + /**
1.660 + * Searches for fitting header values using the given regular expression.
1.661 + * @param group
1.662 + * @param start
1.663 + * @param end
1.664 + * @param headerKey
1.665 + * @param pattern
1.666 + * @return
1.667 + * @throws StorageBackendException
1.668 + */
1.669 + @Override
1.670 + public List<Pair<Long, String>> getArticleHeaders(Channel group, long start,
1.671 + long end, String headerKey, String patStr)
1.672 + throws StorageBackendException, PatternSyntaxException
1.673 + {
1.674 + ResultSet rs = null;
1.675 + List<Pair<Long, String>> heads = new ArrayList<Pair<Long, String>>();
1.676 +
1.677 + try
1.678 + {
1.679 + this.pstmtGetArticleHeaders1.setString(1, group.getName());
1.680 + this.pstmtGetArticleHeaders1.setString(2, headerKey);
1.681 + this.pstmtGetArticleHeaders1.setLong(3, start);
1.682 +
1.683 + rs = this.pstmtGetArticleHeaders1.executeQuery();
1.684 +
1.685 + // Convert the "NNTP" regex to Java regex
1.686 + patStr = patStr.replace("*", ".*");
1.687 + Pattern pattern = Pattern.compile(patStr);
1.688 +
1.689 + while(rs.next())
1.690 + {
1.691 + Long articleIndex = rs.getLong(1);
1.692 + if(end < 0 || articleIndex <= end) // Match start is done via SQL
1.693 + {
1.694 + String headerValue = rs.getString(2);
1.695 + Matcher matcher = pattern.matcher(headerValue);
1.696 + if(matcher.matches())
1.697 + {
1.698 + heads.add(new Pair<Long, String>(articleIndex, headerValue));
1.699 + }
1.700 + }
1.701 + }
1.702 + }
1.703 + catch(SQLException ex)
1.704 + {
1.705 + restartConnection(ex);
1.706 + return getArticleHeaders(group, start, end, headerKey, patStr);
1.707 + }
1.708 + finally
1.709 + {
1.710 + if(rs != null)
1.711 + {
1.712 + try
1.713 + {
1.714 + rs.close();
1.715 + }
1.716 + catch(SQLException ex)
1.717 + {
1.718 + ex.printStackTrace();
1.719 + }
1.720 + }
1.721 + }
1.722 +
1.723 + return heads;
1.724 + }
1.725 +
1.726 + private String getArticleHeaders(long articleID)
1.727 + throws StorageBackendException
1.728 + {
1.729 + ResultSet rs = null;
1.730 +
1.731 + try
1.732 + {
1.733 + this.pstmtGetArticleHeaders0.setLong(1, articleID);
1.734 + rs = this.pstmtGetArticleHeaders0.executeQuery();
1.735 +
1.736 + StringBuilder buf = new StringBuilder();
1.737 + if(rs.next())
1.738 + {
1.739 + for(;;)
1.740 + {
1.741 + buf.append(rs.getString(1)); // key
1.742 + buf.append(": ");
1.743 + String foldedValue = MimeUtility.fold(0, rs.getString(2));
1.744 + buf.append(foldedValue); // value
1.745 + if(rs.next())
1.746 + {
1.747 + buf.append("\r\n");
1.748 + }
1.749 + else
1.750 + {
1.751 + break;
1.752 + }
1.753 + }
1.754 + }
1.755 +
1.756 + return buf.toString();
1.757 + }
1.758 + catch(SQLException ex)
1.759 + {
1.760 + restartConnection(ex);
1.761 + return getArticleHeaders(articleID);
1.762 + }
1.763 + finally
1.764 + {
1.765 + if(rs != null)
1.766 + {
1.767 + try
1.768 + {
1.769 + rs.close();
1.770 + }
1.771 + catch(SQLException ex)
1.772 + {
1.773 + ex.printStackTrace();
1.774 + }
1.775 + }
1.776 + }
1.777 + }
1.778 +
1.779 + @Override
1.780 + public long getArticleIndex(Article article, Group group)
1.781 + throws StorageBackendException
1.782 + {
1.783 + ResultSet rs = null;
1.784 +
1.785 + try
1.786 + {
1.787 + this.pstmtGetArticleIndex.setString(1, article.getMessageID());
1.788 + this.pstmtGetArticleIndex.setLong(2, group.getInternalID());
1.789 +
1.790 + rs = this.pstmtGetArticleIndex.executeQuery();
1.791 + if(rs.next())
1.792 + {
1.793 + return rs.getLong(1);
1.794 + }
1.795 + else
1.796 + {
1.797 + return -1;
1.798 + }
1.799 + }
1.800 + catch(SQLException ex)
1.801 + {
1.802 + restartConnection(ex);
1.803 + return getArticleIndex(article, group);
1.804 + }
1.805 + finally
1.806 + {
1.807 + if(rs != null)
1.808 + {
1.809 + try
1.810 + {
1.811 + rs.close();
1.812 + }
1.813 + catch(SQLException ex)
1.814 + {
1.815 + ex.printStackTrace();
1.816 + }
1.817 + }
1.818 + }
1.819 + }
1.820 +
1.821 + /**
1.822 + * Returns a list of Long/Article Pairs.
1.823 + * @throws java.sql.SQLException
1.824 + */
1.825 + @Override
1.826 + public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first,
1.827 + long last)
1.828 + throws StorageBackendException
1.829 + {
1.830 + ResultSet rs = null;
1.831 +
1.832 + try
1.833 + {
1.834 + this.pstmtGetArticleHeads.setLong(1, group.getInternalID());
1.835 + this.pstmtGetArticleHeads.setLong(2, first);
1.836 + this.pstmtGetArticleHeads.setLong(3, last);
1.837 + rs = pstmtGetArticleHeads.executeQuery();
1.838 +
1.839 + List<Pair<Long, ArticleHead>> articles
1.840 + = new ArrayList<Pair<Long, ArticleHead>>();
1.841 +
1.842 + while (rs.next())
1.843 + {
1.844 + long aid = rs.getLong("article_id");
1.845 + long aidx = rs.getLong("article_index");
1.846 + String headers = getArticleHeaders(aid);
1.847 + articles.add(new Pair<Long, ArticleHead>(aidx,
1.848 + new ArticleHead(headers)));
1.849 + }
1.850 +
1.851 + return articles;
1.852 + }
1.853 + catch(SQLException ex)
1.854 + {
1.855 + restartConnection(ex);
1.856 + return getArticleHeads(group, first, last);
1.857 + }
1.858 + finally
1.859 + {
1.860 + if(rs != null)
1.861 + {
1.862 + try
1.863 + {
1.864 + rs.close();
1.865 + }
1.866 + catch(SQLException ex)
1.867 + {
1.868 + ex.printStackTrace();
1.869 + }
1.870 + }
1.871 + }
1.872 + }
1.873 +
1.874 + @Override
1.875 + public List<Long> getArticleNumbers(long gid)
1.876 + throws StorageBackendException
1.877 + {
1.878 + ResultSet rs = null;
1.879 + try
1.880 + {
1.881 + List<Long> ids = new ArrayList<Long>();
1.882 + this.pstmtGetArticleIDs.setLong(1, gid);
1.883 + rs = this.pstmtGetArticleIDs.executeQuery();
1.884 + while(rs.next())
1.885 + {
1.886 + ids.add(rs.getLong(1));
1.887 + }
1.888 + return ids;
1.889 + }
1.890 + catch(SQLException ex)
1.891 + {
1.892 + restartConnection(ex);
1.893 + return getArticleNumbers(gid);
1.894 + }
1.895 + finally
1.896 + {
1.897 + if(rs != null)
1.898 + {
1.899 + try
1.900 + {
1.901 + rs.close();
1.902 + restarts = 0; // Clear the restart count after successful request
1.903 + }
1.904 + catch(SQLException ex)
1.905 + {
1.906 + ex.printStackTrace();
1.907 + }
1.908 + }
1.909 + }
1.910 + }
1.911 +
1.912 + @Override
1.913 + public String getConfigValue(String key)
1.914 + throws StorageBackendException
1.915 + {
1.916 + ResultSet rs = null;
1.917 + try
1.918 + {
1.919 + this.pstmtGetConfigValue.setString(1, key);
1.920 +
1.921 + rs = this.pstmtGetConfigValue.executeQuery();
1.922 + if(rs.next())
1.923 + {
1.924 + return rs.getString(1); // First data on index 1 not 0
1.925 + }
1.926 + else
1.927 + {
1.928 + return null;
1.929 + }
1.930 + }
1.931 + catch(SQLException ex)
1.932 + {
1.933 + restartConnection(ex);
1.934 + return getConfigValue(key);
1.935 + }
1.936 + finally
1.937 + {
1.938 + if(rs != null)
1.939 + {
1.940 + try
1.941 + {
1.942 + rs.close();
1.943 + }
1.944 + catch(SQLException ex)
1.945 + {
1.946 + ex.printStackTrace();
1.947 + }
1.948 + restarts = 0; // Clear the restart count after successful request
1.949 + }
1.950 + }
1.951 + }
1.952 +
1.953 + @Override
1.954 + public int getEventsCount(int type, long start, long end, Channel channel)
1.955 + throws StorageBackendException
1.956 + {
1.957 + ResultSet rs = null;
1.958 +
1.959 + try
1.960 + {
1.961 + if(channel == null)
1.962 + {
1.963 + this.pstmtGetEventsCount0.setInt(1, type);
1.964 + this.pstmtGetEventsCount0.setLong(2, start);
1.965 + this.pstmtGetEventsCount0.setLong(3, end);
1.966 + rs = this.pstmtGetEventsCount0.executeQuery();
1.967 + }
1.968 + else
1.969 + {
1.970 + this.pstmtGetEventsCount1.setInt(1, type);
1.971 + this.pstmtGetEventsCount1.setLong(2, start);
1.972 + this.pstmtGetEventsCount1.setLong(3, end);
1.973 + this.pstmtGetEventsCount1.setLong(4, channel.getInternalID());
1.974 + rs = this.pstmtGetEventsCount1.executeQuery();
1.975 + }
1.976 +
1.977 + if(rs.next())
1.978 + {
1.979 + return rs.getInt(1);
1.980 + }
1.981 + else
1.982 + {
1.983 + return -1;
1.984 + }
1.985 + }
1.986 + catch(SQLException ex)
1.987 + {
1.988 + restartConnection(ex);
1.989 + return getEventsCount(type, start, end, channel);
1.990 + }
1.991 + finally
1.992 + {
1.993 + if(rs != null)
1.994 + {
1.995 + try
1.996 + {
1.997 + rs.close();
1.998 + }
1.999 + catch(SQLException ex)
1.1000 + {
1.1001 + ex.printStackTrace();
1.1002 + }
1.1003 + }
1.1004 + }
1.1005 + }
1.1006 +
1.1007 + /**
1.1008 + * Reads all Groups from the JDBCDatabase.
1.1009 + * @return
1.1010 + * @throws StorageBackendException
1.1011 + */
1.1012 + @Override
1.1013 + public List<Channel> getGroups()
1.1014 + throws StorageBackendException
1.1015 + {
1.1016 + ResultSet rs;
1.1017 + List<Channel> buffer = new ArrayList<Channel>();
1.1018 + Statement stmt = null;
1.1019 +
1.1020 + try
1.1021 + {
1.1022 + stmt = conn.createStatement();
1.1023 + rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
1.1024 +
1.1025 + while(rs.next())
1.1026 + {
1.1027 + String name = rs.getString("name");
1.1028 + long id = rs.getLong("group_id");
1.1029 + int flags = rs.getInt("flags");
1.1030 +
1.1031 + Group group = new Group(name, id, flags);
1.1032 + buffer.add(group);
1.1033 + }
1.1034 +
1.1035 + return buffer;
1.1036 + }
1.1037 + catch(SQLException ex)
1.1038 + {
1.1039 + restartConnection(ex);
1.1040 + return getGroups();
1.1041 + }
1.1042 + finally
1.1043 + {
1.1044 + if(stmt != null)
1.1045 + {
1.1046 + try
1.1047 + {
1.1048 + stmt.close(); // Implicitely closes ResultSets
1.1049 + }
1.1050 + catch(SQLException ex)
1.1051 + {
1.1052 + ex.printStackTrace();
1.1053 + }
1.1054 + }
1.1055 + }
1.1056 + }
1.1057 +
1.1058 + @Override
1.1059 + public List<String> getGroupsForList(InternetAddress listAddress)
1.1060 + throws StorageBackendException
1.1061 + {
1.1062 + ResultSet rs = null;
1.1063 +
1.1064 + try
1.1065 + {
1.1066 + this.pstmtGetGroupForList.setString(1, listAddress.getAddress());
1.1067 +
1.1068 + rs = this.pstmtGetGroupForList.executeQuery();
1.1069 + List<String> groups = new ArrayList<String>();
1.1070 + while(rs.next())
1.1071 + {
1.1072 + String group = rs.getString(1);
1.1073 + groups.add(group);
1.1074 + }
1.1075 + return groups;
1.1076 + }
1.1077 + catch(SQLException ex)
1.1078 + {
1.1079 + restartConnection(ex);
1.1080 + return getGroupsForList(listAddress);
1.1081 + }
1.1082 + finally
1.1083 + {
1.1084 + if(rs != null)
1.1085 + {
1.1086 + try
1.1087 + {
1.1088 + rs.close();
1.1089 + }
1.1090 + catch(SQLException ex)
1.1091 + {
1.1092 + ex.printStackTrace();
1.1093 + }
1.1094 + }
1.1095 + }
1.1096 + }
1.1097 +
1.1098 + /**
1.1099 + * Returns the Group that is identified by the name.
1.1100 + * @param name
1.1101 + * @return
1.1102 + * @throws StorageBackendException
1.1103 + */
1.1104 + @Override
1.1105 + public Group getGroup(String name)
1.1106 + throws StorageBackendException
1.1107 + {
1.1108 + ResultSet rs = null;
1.1109 +
1.1110 + try
1.1111 + {
1.1112 + this.pstmtGetGroup0.setString(1, name);
1.1113 + rs = this.pstmtGetGroup0.executeQuery();
1.1114 +
1.1115 + if (!rs.next())
1.1116 + {
1.1117 + return null;
1.1118 + }
1.1119 + else
1.1120 + {
1.1121 + long id = rs.getLong("group_id");
1.1122 + int flags = rs.getInt("flags");
1.1123 + return new Group(name, id, flags);
1.1124 + }
1.1125 + }
1.1126 + catch(SQLException ex)
1.1127 + {
1.1128 + restartConnection(ex);
1.1129 + return getGroup(name);
1.1130 + }
1.1131 + finally
1.1132 + {
1.1133 + if(rs != null)
1.1134 + {
1.1135 + try
1.1136 + {
1.1137 + rs.close();
1.1138 + }
1.1139 + catch(SQLException ex)
1.1140 + {
1.1141 + ex.printStackTrace();
1.1142 + }
1.1143 + }
1.1144 + }
1.1145 + }
1.1146 +
1.1147 + @Override
1.1148 + public String getListForGroup(String group)
1.1149 + throws StorageBackendException
1.1150 + {
1.1151 + ResultSet rs = null;
1.1152 +
1.1153 + try
1.1154 + {
1.1155 + this.pstmtGetListForGroup.setString(1, group);
1.1156 + rs = this.pstmtGetListForGroup.executeQuery();
1.1157 + if (rs.next())
1.1158 + {
1.1159 + return rs.getString(1);
1.1160 + }
1.1161 + else
1.1162 + {
1.1163 + return null;
1.1164 + }
1.1165 + }
1.1166 + catch(SQLException ex)
1.1167 + {
1.1168 + restartConnection(ex);
1.1169 + return getListForGroup(group);
1.1170 + }
1.1171 + finally
1.1172 + {
1.1173 + if(rs != null)
1.1174 + {
1.1175 + try
1.1176 + {
1.1177 + rs.close();
1.1178 + }
1.1179 + catch(SQLException ex)
1.1180 + {
1.1181 + ex.printStackTrace();
1.1182 + }
1.1183 + }
1.1184 + }
1.1185 + }
1.1186 +
1.1187 + private int getMaxArticleIndex(long groupID)
1.1188 + throws StorageBackendException
1.1189 + {
1.1190 + ResultSet rs = null;
1.1191 +
1.1192 + try
1.1193 + {
1.1194 + this.pstmtGetMaxArticleIndex.setLong(1, groupID);
1.1195 + rs = this.pstmtGetMaxArticleIndex.executeQuery();
1.1196 +
1.1197 + int maxIndex = 0;
1.1198 + if (rs.next())
1.1199 + {
1.1200 + maxIndex = rs.getInt(1);
1.1201 + }
1.1202 +
1.1203 + return maxIndex;
1.1204 + }
1.1205 + catch(SQLException ex)
1.1206 + {
1.1207 + restartConnection(ex);
1.1208 + return getMaxArticleIndex(groupID);
1.1209 + }
1.1210 + finally
1.1211 + {
1.1212 + if(rs != null)
1.1213 + {
1.1214 + try
1.1215 + {
1.1216 + rs.close();
1.1217 + }
1.1218 + catch(SQLException ex)
1.1219 + {
1.1220 + ex.printStackTrace();
1.1221 + }
1.1222 + }
1.1223 + }
1.1224 + }
1.1225 +
1.1226 + private int getMaxArticleID()
1.1227 + throws StorageBackendException
1.1228 + {
1.1229 + ResultSet rs = null;
1.1230 +
1.1231 + try
1.1232 + {
1.1233 + rs = this.pstmtGetMaxArticleID.executeQuery();
1.1234 +
1.1235 + int maxIndex = 0;
1.1236 + if (rs.next())
1.1237 + {
1.1238 + maxIndex = rs.getInt(1);
1.1239 + }
1.1240 +
1.1241 + return maxIndex;
1.1242 + }
1.1243 + catch(SQLException ex)
1.1244 + {
1.1245 + restartConnection(ex);
1.1246 + return getMaxArticleID();
1.1247 + }
1.1248 + finally
1.1249 + {
1.1250 + if(rs != null)
1.1251 + {
1.1252 + try
1.1253 + {
1.1254 + rs.close();
1.1255 + }
1.1256 + catch(SQLException ex)
1.1257 + {
1.1258 + ex.printStackTrace();
1.1259 + }
1.1260 + }
1.1261 + }
1.1262 + }
1.1263 +
1.1264 + @Override
1.1265 + public int getLastArticleNumber(Group group)
1.1266 + throws StorageBackendException
1.1267 + {
1.1268 + ResultSet rs = null;
1.1269 +
1.1270 + try
1.1271 + {
1.1272 + this.pstmtGetLastArticleNumber.setLong(1, group.getInternalID());
1.1273 + rs = this.pstmtGetLastArticleNumber.executeQuery();
1.1274 + if (rs.next())
1.1275 + {
1.1276 + return rs.getInt(1);
1.1277 + }
1.1278 + else
1.1279 + {
1.1280 + return 0;
1.1281 + }
1.1282 + }
1.1283 + catch(SQLException ex)
1.1284 + {
1.1285 + restartConnection(ex);
1.1286 + return getLastArticleNumber(group);
1.1287 + }
1.1288 + finally
1.1289 + {
1.1290 + if(rs != null)
1.1291 + {
1.1292 + try
1.1293 + {
1.1294 + rs.close();
1.1295 + }
1.1296 + catch(SQLException ex)
1.1297 + {
1.1298 + ex.printStackTrace();
1.1299 + }
1.1300 + }
1.1301 + }
1.1302 + }
1.1303 +
1.1304 + @Override
1.1305 + public int getFirstArticleNumber(Group group)
1.1306 + throws StorageBackendException
1.1307 + {
1.1308 + ResultSet rs = null;
1.1309 + try
1.1310 + {
1.1311 + this.pstmtGetFirstArticleNumber.setLong(1, group.getInternalID());
1.1312 + rs = this.pstmtGetFirstArticleNumber.executeQuery();
1.1313 + if(rs.next())
1.1314 + {
1.1315 + return rs.getInt(1);
1.1316 + }
1.1317 + else
1.1318 + {
1.1319 + return 0;
1.1320 + }
1.1321 + }
1.1322 + catch(SQLException ex)
1.1323 + {
1.1324 + restartConnection(ex);
1.1325 + return getFirstArticleNumber(group);
1.1326 + }
1.1327 + finally
1.1328 + {
1.1329 + if(rs != null)
1.1330 + {
1.1331 + try
1.1332 + {
1.1333 + rs.close();
1.1334 + }
1.1335 + catch(SQLException ex)
1.1336 + {
1.1337 + ex.printStackTrace();
1.1338 + }
1.1339 + }
1.1340 + }
1.1341 + }
1.1342 +
1.1343 + /**
1.1344 + * Returns a group name identified by the given id.
1.1345 + * @param id
1.1346 + * @return
1.1347 + * @throws StorageBackendException
1.1348 + */
1.1349 + public String getGroup(int id)
1.1350 + throws StorageBackendException
1.1351 + {
1.1352 + ResultSet rs = null;
1.1353 +
1.1354 + try
1.1355 + {
1.1356 + this.pstmtGetGroup1.setInt(1, id);
1.1357 + rs = this.pstmtGetGroup1.executeQuery();
1.1358 +
1.1359 + if (rs.next())
1.1360 + {
1.1361 + return rs.getString(1);
1.1362 + }
1.1363 + else
1.1364 + {
1.1365 + return null;
1.1366 + }
1.1367 + }
1.1368 + catch(SQLException ex)
1.1369 + {
1.1370 + restartConnection(ex);
1.1371 + return getGroup(id);
1.1372 + }
1.1373 + finally
1.1374 + {
1.1375 + if(rs != null)
1.1376 + {
1.1377 + try
1.1378 + {
1.1379 + rs.close();
1.1380 + }
1.1381 + catch(SQLException ex)
1.1382 + {
1.1383 + ex.printStackTrace();
1.1384 + }
1.1385 + }
1.1386 + }
1.1387 + }
1.1388 +
1.1389 + @Override
1.1390 + public double getEventsPerHour(int key, long gid)
1.1391 + throws StorageBackendException
1.1392 + {
1.1393 + String gidquery = "";
1.1394 + if(gid >= 0)
1.1395 + {
1.1396 + gidquery = " AND group_id = " + gid;
1.1397 + }
1.1398 +
1.1399 + Statement stmt = null;
1.1400 + ResultSet rs = null;
1.1401 +
1.1402 + try
1.1403 + {
1.1404 + stmt = this.conn.createStatement();
1.1405 + rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))" +
1.1406 + " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
1.1407 +
1.1408 + if(rs.next())
1.1409 + {
1.1410 + restarts = 0; // reset error count
1.1411 + return rs.getDouble(1);
1.1412 + }
1.1413 + else
1.1414 + {
1.1415 + return Double.NaN;
1.1416 + }
1.1417 + }
1.1418 + catch(SQLException ex)
1.1419 + {
1.1420 + restartConnection(ex);
1.1421 + return getEventsPerHour(key, gid);
1.1422 + }
1.1423 + finally
1.1424 + {
1.1425 + try
1.1426 + {
1.1427 + if(stmt != null)
1.1428 + {
1.1429 + stmt.close(); // Implicitely closes the result sets
1.1430 + }
1.1431 + }
1.1432 + catch(SQLException ex)
1.1433 + {
1.1434 + ex.printStackTrace();
1.1435 + }
1.1436 + }
1.1437 + }
1.1438 +
1.1439 + @Override
1.1440 + public String getOldestArticle()
1.1441 + throws StorageBackendException
1.1442 + {
1.1443 + ResultSet rs = null;
1.1444 +
1.1445 + try
1.1446 + {
1.1447 + rs = this.pstmtGetOldestArticle.executeQuery();
1.1448 + if(rs.next())
1.1449 + {
1.1450 + return rs.getString(1);
1.1451 + }
1.1452 + else
1.1453 + {
1.1454 + return null;
1.1455 + }
1.1456 + }
1.1457 + catch(SQLException ex)
1.1458 + {
1.1459 + restartConnection(ex);
1.1460 + return getOldestArticle();
1.1461 + }
1.1462 + finally
1.1463 + {
1.1464 + if(rs != null)
1.1465 + {
1.1466 + try
1.1467 + {
1.1468 + rs.close();
1.1469 + }
1.1470 + catch(SQLException ex)
1.1471 + {
1.1472 + ex.printStackTrace();
1.1473 + }
1.1474 + }
1.1475 + }
1.1476 + }
1.1477 +
1.1478 + @Override
1.1479 + public int getPostingsCount(String groupname)
1.1480 + throws StorageBackendException
1.1481 + {
1.1482 + ResultSet rs = null;
1.1483 +
1.1484 + try
1.1485 + {
1.1486 + this.pstmtGetPostingsCount.setString(1, groupname);
1.1487 + rs = this.pstmtGetPostingsCount.executeQuery();
1.1488 + if(rs.next())
1.1489 + {
1.1490 + return rs.getInt(1);
1.1491 + }
1.1492 + else
1.1493 + {
1.1494 + Log.msg("Warning: Count on postings return nothing!", true);
1.1495 + return 0;
1.1496 + }
1.1497 + }
1.1498 + catch(SQLException ex)
1.1499 + {
1.1500 + restartConnection(ex);
1.1501 + return getPostingsCount(groupname);
1.1502 + }
1.1503 + finally
1.1504 + {
1.1505 + if(rs != null)
1.1506 + {
1.1507 + try
1.1508 + {
1.1509 + rs.close();
1.1510 + }
1.1511 + catch(SQLException ex)
1.1512 + {
1.1513 + ex.printStackTrace();
1.1514 + }
1.1515 + }
1.1516 + }
1.1517 + }
1.1518 +
1.1519 + @Override
1.1520 + public List<Subscription> getSubscriptions(int feedtype)
1.1521 + throws StorageBackendException
1.1522 + {
1.1523 + ResultSet rs = null;
1.1524 +
1.1525 + try
1.1526 + {
1.1527 + List<Subscription> subs = new ArrayList<Subscription>();
1.1528 + this.pstmtGetSubscriptions.setInt(1, feedtype);
1.1529 + rs = this.pstmtGetSubscriptions.executeQuery();
1.1530 +
1.1531 + while(rs.next())
1.1532 + {
1.1533 + String host = rs.getString("host");
1.1534 + String group = rs.getString("name");
1.1535 + int port = rs.getInt("port");
1.1536 + subs.add(new Subscription(host, port, feedtype, group));
1.1537 + }
1.1538 +
1.1539 + return subs;
1.1540 + }
1.1541 + catch(SQLException ex)
1.1542 + {
1.1543 + restartConnection(ex);
1.1544 + return getSubscriptions(feedtype);
1.1545 + }
1.1546 + finally
1.1547 + {
1.1548 + if(rs != null)
1.1549 + {
1.1550 + try
1.1551 + {
1.1552 + rs.close();
1.1553 + }
1.1554 + catch(SQLException ex)
1.1555 + {
1.1556 + ex.printStackTrace();
1.1557 + }
1.1558 + }
1.1559 + }
1.1560 + }
1.1561 +
1.1562 + /**
1.1563 + * Checks if there is an article with the given messageid in the JDBCDatabase.
1.1564 + * @param name
1.1565 + * @return
1.1566 + * @throws StorageBackendException
1.1567 + */
1.1568 + @Override
1.1569 + public boolean isArticleExisting(String messageID)
1.1570 + throws StorageBackendException
1.1571 + {
1.1572 + ResultSet rs = null;
1.1573 +
1.1574 + try
1.1575 + {
1.1576 + this.pstmtIsArticleExisting.setString(1, messageID);
1.1577 + rs = this.pstmtIsArticleExisting.executeQuery();
1.1578 + return rs.next() && rs.getInt(1) == 1;
1.1579 + }
1.1580 + catch(SQLException ex)
1.1581 + {
1.1582 + restartConnection(ex);
1.1583 + return isArticleExisting(messageID);
1.1584 + }
1.1585 + finally
1.1586 + {
1.1587 + if(rs != null)
1.1588 + {
1.1589 + try
1.1590 + {
1.1591 + rs.close();
1.1592 + }
1.1593 + catch(SQLException ex)
1.1594 + {
1.1595 + ex.printStackTrace();
1.1596 + }
1.1597 + }
1.1598 + }
1.1599 + }
1.1600 +
1.1601 + /**
1.1602 + * Checks if there is a group with the given name in the JDBCDatabase.
1.1603 + * @param name
1.1604 + * @return
1.1605 + * @throws StorageBackendException
1.1606 + */
1.1607 + @Override
1.1608 + public boolean isGroupExisting(String name)
1.1609 + throws StorageBackendException
1.1610 + {
1.1611 + ResultSet rs = null;
1.1612 +
1.1613 + try
1.1614 + {
1.1615 + this.pstmtIsGroupExisting.setString(1, name);
1.1616 + rs = this.pstmtIsGroupExisting.executeQuery();
1.1617 + return rs.next();
1.1618 + }
1.1619 + catch(SQLException ex)
1.1620 + {
1.1621 + restartConnection(ex);
1.1622 + return isGroupExisting(name);
1.1623 + }
1.1624 + finally
1.1625 + {
1.1626 + if(rs != null)
1.1627 + {
1.1628 + try
1.1629 + {
1.1630 + rs.close();
1.1631 + }
1.1632 + catch(SQLException ex)
1.1633 + {
1.1634 + ex.printStackTrace();
1.1635 + }
1.1636 + }
1.1637 + }
1.1638 + }
1.1639 +
1.1640 + @Override
1.1641 + public void setConfigValue(String key, String value)
1.1642 + throws StorageBackendException
1.1643 + {
1.1644 + try
1.1645 + {
1.1646 + conn.setAutoCommit(false);
1.1647 + this.pstmtSetConfigValue0.setString(1, key);
1.1648 + this.pstmtSetConfigValue0.execute();
1.1649 + this.pstmtSetConfigValue1.setString(1, key);
1.1650 + this.pstmtSetConfigValue1.setString(2, value);
1.1651 + this.pstmtSetConfigValue1.execute();
1.1652 + conn.commit();
1.1653 + conn.setAutoCommit(true);
1.1654 + }
1.1655 + catch(SQLException ex)
1.1656 + {
1.1657 + restartConnection(ex);
1.1658 + setConfigValue(key, value);
1.1659 + }
1.1660 + }
1.1661 +
1.1662 + /**
1.1663 + * Closes the JDBCDatabase connection.
1.1664 + */
1.1665 + public void shutdown()
1.1666 + throws StorageBackendException
1.1667 + {
1.1668 + try
1.1669 + {
1.1670 + if(this.conn != null)
1.1671 + {
1.1672 + this.conn.close();
1.1673 + }
1.1674 + }
1.1675 + catch(SQLException ex)
1.1676 + {
1.1677 + throw new StorageBackendException(ex);
1.1678 + }
1.1679 + }
1.1680 +
1.1681 + @Override
1.1682 + public void purgeGroup(Group group)
1.1683 + throws StorageBackendException
1.1684 + {
1.1685 + try
1.1686 + {
1.1687 + this.pstmtPurgeGroup0.setLong(1, group.getInternalID());
1.1688 + this.pstmtPurgeGroup0.executeUpdate();
1.1689 +
1.1690 + this.pstmtPurgeGroup1.setLong(1, group.getInternalID());
1.1691 + this.pstmtPurgeGroup1.executeUpdate();
1.1692 + }
1.1693 + catch(SQLException ex)
1.1694 + {
1.1695 + restartConnection(ex);
1.1696 + purgeGroup(group);
1.1697 + }
1.1698 + }
1.1699 +
1.1700 + private void restartConnection(SQLException cause)
1.1701 + throws StorageBackendException
1.1702 + {
1.1703 + restarts++;
1.1704 + Log.msg(Thread.currentThread()
1.1705 + + ": Database connection was closed (restart " + restarts + ").", false);
1.1706 +
1.1707 + if(restarts >= MAX_RESTARTS)
1.1708 + {
1.1709 + // Delete the current, probably broken JDBCDatabase instance.
1.1710 + // So no one can use the instance any more.
1.1711 + JDBCDatabaseProvider.instances.remove(Thread.currentThread());
1.1712 +
1.1713 + // Throw the exception upwards
1.1714 + throw new StorageBackendException(cause);
1.1715 + }
1.1716 +
1.1717 + try
1.1718 + {
1.1719 + Thread.sleep(1500L * restarts);
1.1720 + }
1.1721 + catch(InterruptedException ex)
1.1722 + {
1.1723 + Log.msg("Interrupted: " + ex.getMessage(), false);
1.1724 + }
1.1725 +
1.1726 + // Try to properly close the old database connection
1.1727 + try
1.1728 + {
1.1729 + if(this.conn != null)
1.1730 + {
1.1731 + this.conn.close();
1.1732 + }
1.1733 + }
1.1734 + catch(SQLException ex)
1.1735 + {
1.1736 + Log.msg(ex.getMessage(), true);
1.1737 + }
1.1738 +
1.1739 + try
1.1740 + {
1.1741 + // Try to reinitialize database connection
1.1742 + arise();
1.1743 + }
1.1744 + catch(SQLException ex)
1.1745 + {
1.1746 + Log.msg(ex.getMessage(), true);
1.1747 + restartConnection(ex);
1.1748 + }
1.1749 + }
1.1750 +
1.1751 + /**
1.1752 + * Writes the flags and the name of the given group to the database.
1.1753 + * @param group
1.1754 + * @throws StorageBackendException
1.1755 + */
1.1756 + @Override
1.1757 + public boolean update(Group group)
1.1758 + throws StorageBackendException
1.1759 + {
1.1760 + try
1.1761 + {
1.1762 + this.pstmtUpdateGroup.setInt(1, group.getFlags());
1.1763 + this.pstmtUpdateGroup.setString(2, group.getName());
1.1764 + this.pstmtUpdateGroup.setLong(3, group.getInternalID());
1.1765 + int rs = this.pstmtUpdateGroup.executeUpdate();
1.1766 + return rs == 1;
1.1767 + }
1.1768 + catch(SQLException ex)
1.1769 + {
1.1770 + restartConnection(ex);
1.1771 + return update(group);
1.1772 + }
1.1773 + }
1.1774 +
1.1775 +}