1.1 --- a/org/sonews/storage/impl/JDBCDatabase.java Sun Aug 29 17:04:25 2010 +0200
1.2 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000
1.3 @@ -1,1782 +0,0 @@
1.4 -/*
1.5 - * SONEWS News Server
1.6 - * see AUTHORS for the list of contributors
1.7 - *
1.8 - * This program is free software: you can redistribute it and/or modify
1.9 - * it under the terms of the GNU General Public License as published by
1.10 - * the Free Software Foundation, either version 3 of the License, or
1.11 - * (at your option) any later version.
1.12 - *
1.13 - * This program is distributed in the hope that it will be useful,
1.14 - * but WITHOUT ANY WARRANTY; without even the implied warranty of
1.15 - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
1.16 - * GNU General Public License for more details.
1.17 - *
1.18 - * You should have received a copy of the GNU General Public License
1.19 - * along with this program. If not, see <http://www.gnu.org/licenses/>.
1.20 - */
1.21 -
1.22 -package org.sonews.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 -}