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