1.1 --- a/src/org/sonews/storage/impl/JDBCDatabase.java Sun Aug 29 17:28:58 2010 +0200
1.2 +++ b/src/org/sonews/storage/impl/JDBCDatabase.java Sun Aug 29 18:17:37 2010 +0200
1.3 @@ -52,1731 +52,1378 @@
1.4 public class JDBCDatabase implements Storage
1.5 {
1.6
1.7 - public static final int MAX_RESTARTS = 2;
1.8 -
1.9 - private Connection conn = null;
1.10 - private PreparedStatement pstmtAddArticle1 = null;
1.11 - private PreparedStatement pstmtAddArticle2 = null;
1.12 - private PreparedStatement pstmtAddArticle3 = null;
1.13 - private PreparedStatement pstmtAddArticle4 = null;
1.14 - private PreparedStatement pstmtAddGroup0 = null;
1.15 - private PreparedStatement pstmtAddEvent = null;
1.16 - private PreparedStatement pstmtCountArticles = null;
1.17 - private PreparedStatement pstmtCountGroups = null;
1.18 - private PreparedStatement pstmtDeleteArticle0 = null;
1.19 - private PreparedStatement pstmtDeleteArticle1 = null;
1.20 - private PreparedStatement pstmtDeleteArticle2 = null;
1.21 - private PreparedStatement pstmtDeleteArticle3 = null;
1.22 - private PreparedStatement pstmtGetArticle0 = null;
1.23 - private PreparedStatement pstmtGetArticle1 = null;
1.24 - private PreparedStatement pstmtGetArticleHeaders0 = null;
1.25 - private PreparedStatement pstmtGetArticleHeaders1 = null;
1.26 - private PreparedStatement pstmtGetArticleHeads = null;
1.27 - private PreparedStatement pstmtGetArticleIDs = null;
1.28 - private PreparedStatement pstmtGetArticleIndex = null;
1.29 - private PreparedStatement pstmtGetConfigValue = null;
1.30 - private PreparedStatement pstmtGetEventsCount0 = null;
1.31 - private PreparedStatement pstmtGetEventsCount1 = null;
1.32 - private PreparedStatement pstmtGetGroupForList = null;
1.33 - private PreparedStatement pstmtGetGroup0 = null;
1.34 - private PreparedStatement pstmtGetGroup1 = null;
1.35 - private PreparedStatement pstmtGetFirstArticleNumber = null;
1.36 - private PreparedStatement pstmtGetListForGroup = null;
1.37 - private PreparedStatement pstmtGetLastArticleNumber = null;
1.38 - private PreparedStatement pstmtGetMaxArticleID = null;
1.39 - private PreparedStatement pstmtGetMaxArticleIndex = null;
1.40 - private PreparedStatement pstmtGetOldestArticle = null;
1.41 - private PreparedStatement pstmtGetPostingsCount = null;
1.42 - private PreparedStatement pstmtGetSubscriptions = null;
1.43 - private PreparedStatement pstmtIsArticleExisting = null;
1.44 - private PreparedStatement pstmtIsGroupExisting = null;
1.45 - private PreparedStatement pstmtPurgeGroup0 = null;
1.46 - private PreparedStatement pstmtPurgeGroup1 = null;
1.47 - private PreparedStatement pstmtSetConfigValue0 = null;
1.48 - private PreparedStatement pstmtSetConfigValue1 = null;
1.49 - private PreparedStatement pstmtUpdateGroup = null;
1.50 -
1.51 - /** How many times the database connection was reinitialized */
1.52 - private int restarts = 0;
1.53 -
1.54 - /**
1.55 - * Rises the database: reconnect and recreate all prepared statements.
1.56 - * @throws java.lang.SQLException
1.57 - */
1.58 - protected void arise()
1.59 - throws SQLException
1.60 - {
1.61 - try
1.62 - {
1.63 - // Load database driver
1.64 - Class.forName(
1.65 - Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object"));
1.66 + public static final int MAX_RESTARTS = 2;
1.67 + private Connection conn = null;
1.68 + private PreparedStatement pstmtAddArticle1 = null;
1.69 + private PreparedStatement pstmtAddArticle2 = null;
1.70 + private PreparedStatement pstmtAddArticle3 = null;
1.71 + private PreparedStatement pstmtAddArticle4 = null;
1.72 + private PreparedStatement pstmtAddGroup0 = null;
1.73 + private PreparedStatement pstmtAddEvent = null;
1.74 + private PreparedStatement pstmtCountArticles = null;
1.75 + private PreparedStatement pstmtCountGroups = null;
1.76 + private PreparedStatement pstmtDeleteArticle0 = null;
1.77 + private PreparedStatement pstmtDeleteArticle1 = null;
1.78 + private PreparedStatement pstmtDeleteArticle2 = null;
1.79 + private PreparedStatement pstmtDeleteArticle3 = null;
1.80 + private PreparedStatement pstmtGetArticle0 = null;
1.81 + private PreparedStatement pstmtGetArticle1 = null;
1.82 + private PreparedStatement pstmtGetArticleHeaders0 = null;
1.83 + private PreparedStatement pstmtGetArticleHeaders1 = null;
1.84 + private PreparedStatement pstmtGetArticleHeads = null;
1.85 + private PreparedStatement pstmtGetArticleIDs = null;
1.86 + private PreparedStatement pstmtGetArticleIndex = null;
1.87 + private PreparedStatement pstmtGetConfigValue = null;
1.88 + private PreparedStatement pstmtGetEventsCount0 = null;
1.89 + private PreparedStatement pstmtGetEventsCount1 = null;
1.90 + private PreparedStatement pstmtGetGroupForList = null;
1.91 + private PreparedStatement pstmtGetGroup0 = null;
1.92 + private PreparedStatement pstmtGetGroup1 = null;
1.93 + private PreparedStatement pstmtGetFirstArticleNumber = null;
1.94 + private PreparedStatement pstmtGetListForGroup = null;
1.95 + private PreparedStatement pstmtGetLastArticleNumber = null;
1.96 + private PreparedStatement pstmtGetMaxArticleID = null;
1.97 + private PreparedStatement pstmtGetMaxArticleIndex = null;
1.98 + private PreparedStatement pstmtGetOldestArticle = null;
1.99 + private PreparedStatement pstmtGetPostingsCount = null;
1.100 + private PreparedStatement pstmtGetSubscriptions = null;
1.101 + private PreparedStatement pstmtIsArticleExisting = null;
1.102 + private PreparedStatement pstmtIsGroupExisting = null;
1.103 + private PreparedStatement pstmtPurgeGroup0 = null;
1.104 + private PreparedStatement pstmtPurgeGroup1 = null;
1.105 + private PreparedStatement pstmtSetConfigValue0 = null;
1.106 + private PreparedStatement pstmtSetConfigValue1 = null;
1.107 + private PreparedStatement pstmtUpdateGroup = null;
1.108 + /** How many times the database connection was reinitialized */
1.109 + private int restarts = 0;
1.110
1.111 - // Establish database connection
1.112 - this.conn = DriverManager.getConnection(
1.113 - Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>"),
1.114 - Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root"),
1.115 - Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, ""));
1.116 + /**
1.117 + * Rises the database: reconnect and recreate all prepared statements.
1.118 + * @throws java.lang.SQLException
1.119 + */
1.120 + protected void arise()
1.121 + throws SQLException
1.122 + {
1.123 + try {
1.124 + // Load database driver
1.125 + Class.forName(
1.126 + Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object"));
1.127
1.128 - this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1.129 - if(this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE)
1.130 - {
1.131 - Log.get().warning("Database is NOT fully serializable!");
1.132 - }
1.133 + // Establish database connection
1.134 + this.conn = DriverManager.getConnection(
1.135 + Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>"),
1.136 + Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root"),
1.137 + Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, ""));
1.138
1.139 - // Prepare statements for method addArticle()
1.140 - this.pstmtAddArticle1 = conn.prepareStatement(
1.141 - "INSERT INTO articles (article_id, body) VALUES(?, ?)");
1.142 - this.pstmtAddArticle2 = conn.prepareStatement(
1.143 - "INSERT INTO headers (article_id, header_key, header_value, header_index) " +
1.144 - "VALUES (?, ?, ?, ?)");
1.145 - this.pstmtAddArticle3 = conn.prepareStatement(
1.146 - "INSERT INTO postings (group_id, article_id, article_index)" +
1.147 - "VALUES (?, ?, ?)");
1.148 - this.pstmtAddArticle4 = conn.prepareStatement(
1.149 - "INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");
1.150 + this.conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1.151 + if (this.conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {
1.152 + Log.get().warning("Database is NOT fully serializable!");
1.153 + }
1.154
1.155 - // Prepare statement for method addStatValue()
1.156 - this.pstmtAddEvent = conn.prepareStatement(
1.157 - "INSERT INTO events VALUES (?, ?, ?)");
1.158 -
1.159 - // Prepare statement for method addGroup()
1.160 - this.pstmtAddGroup0 = conn.prepareStatement(
1.161 - "INSERT INTO groups (name, flags) VALUES (?, ?)");
1.162 -
1.163 - // Prepare statement for method countArticles()
1.164 - this.pstmtCountArticles = conn.prepareStatement(
1.165 - "SELECT Count(article_id) FROM article_ids");
1.166 -
1.167 - // Prepare statement for method countGroups()
1.168 - this.pstmtCountGroups = conn.prepareStatement(
1.169 - "SELECT Count(group_id) FROM groups WHERE " +
1.170 - "flags & " + Channel.DELETED + " = 0");
1.171 -
1.172 - // Prepare statements for method delete(article)
1.173 - this.pstmtDeleteArticle0 = conn.prepareStatement(
1.174 - "DELETE FROM articles WHERE article_id = " +
1.175 - "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.176 - this.pstmtDeleteArticle1 = conn.prepareStatement(
1.177 - "DELETE FROM headers WHERE article_id = " +
1.178 - "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.179 - this.pstmtDeleteArticle2 = conn.prepareStatement(
1.180 - "DELETE FROM postings WHERE article_id = " +
1.181 - "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.182 - this.pstmtDeleteArticle3 = conn.prepareStatement(
1.183 - "DELETE FROM article_ids WHERE message_id = ?");
1.184 + // Prepare statements for method addArticle()
1.185 + this.pstmtAddArticle1 = conn.prepareStatement(
1.186 + "INSERT INTO articles (article_id, body) VALUES(?, ?)");
1.187 + this.pstmtAddArticle2 = conn.prepareStatement(
1.188 + "INSERT INTO headers (article_id, header_key, header_value, header_index) "
1.189 + + "VALUES (?, ?, ?, ?)");
1.190 + this.pstmtAddArticle3 = conn.prepareStatement(
1.191 + "INSERT INTO postings (group_id, article_id, article_index)"
1.192 + + "VALUES (?, ?, ?)");
1.193 + this.pstmtAddArticle4 = conn.prepareStatement(
1.194 + "INSERT INTO article_ids (article_id, message_id) VALUES (?, ?)");
1.195
1.196 - // Prepare statements for methods getArticle()
1.197 - this.pstmtGetArticle0 = conn.prepareStatement(
1.198 - "SELECT * FROM articles WHERE article_id = " +
1.199 - "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.200 - this.pstmtGetArticle1 = conn.prepareStatement(
1.201 - "SELECT * FROM articles WHERE article_id = " +
1.202 - "(SELECT article_id FROM postings WHERE " +
1.203 - "article_index = ? AND group_id = ?)");
1.204 -
1.205 - // Prepare statement for method getArticleHeaders()
1.206 - this.pstmtGetArticleHeaders0 = conn.prepareStatement(
1.207 - "SELECT header_key, header_value FROM headers WHERE article_id = ? " +
1.208 - "ORDER BY header_index ASC");
1.209 + // Prepare statement for method addStatValue()
1.210 + this.pstmtAddEvent = conn.prepareStatement(
1.211 + "INSERT INTO events VALUES (?, ?, ?)");
1.212
1.213 - // Prepare statement for method getArticleHeaders(regular expr pattern)
1.214 - this.pstmtGetArticleHeaders1 = conn.prepareStatement(
1.215 - "SELECT p.article_index, h.header_value FROM headers h " +
1.216 - "INNER JOIN postings p ON h.article_id = p.article_id " +
1.217 - "INNER JOIN groups g ON p.group_id = g.group_id " +
1.218 - "WHERE g.name = ? AND " +
1.219 - "h.header_key = ? AND " +
1.220 - "p.article_index >= ? " +
1.221 - "ORDER BY p.article_index ASC");
1.222 + // Prepare statement for method addGroup()
1.223 + this.pstmtAddGroup0 = conn.prepareStatement(
1.224 + "INSERT INTO groups (name, flags) VALUES (?, ?)");
1.225
1.226 - this.pstmtGetArticleIDs = conn.prepareStatement(
1.227 - "SELECT article_index FROM postings WHERE group_id = ?");
1.228 -
1.229 - // Prepare statement for method getArticleIndex
1.230 - this.pstmtGetArticleIndex = conn.prepareStatement(
1.231 - "SELECT article_index FROM postings WHERE " +
1.232 - "article_id = (SELECT article_id FROM article_ids " +
1.233 - "WHERE message_id = ?) " +
1.234 - " AND group_id = ?");
1.235 + // Prepare statement for method countArticles()
1.236 + this.pstmtCountArticles = conn.prepareStatement(
1.237 + "SELECT Count(article_id) FROM article_ids");
1.238
1.239 - // Prepare statements for method getArticleHeads()
1.240 - this.pstmtGetArticleHeads = conn.prepareStatement(
1.241 - "SELECT article_id, article_index FROM postings WHERE " +
1.242 - "postings.group_id = ? AND article_index >= ? AND " +
1.243 - "article_index <= ?");
1.244 + // Prepare statement for method countGroups()
1.245 + this.pstmtCountGroups = conn.prepareStatement(
1.246 + "SELECT Count(group_id) FROM groups WHERE "
1.247 + + "flags & " + Channel.DELETED + " = 0");
1.248
1.249 - // Prepare statements for method getConfigValue()
1.250 - this.pstmtGetConfigValue = conn.prepareStatement(
1.251 - "SELECT config_value FROM config WHERE config_key = ?");
1.252 + // Prepare statements for method delete(article)
1.253 + this.pstmtDeleteArticle0 = conn.prepareStatement(
1.254 + "DELETE FROM articles WHERE article_id = "
1.255 + + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.256 + this.pstmtDeleteArticle1 = conn.prepareStatement(
1.257 + "DELETE FROM headers WHERE article_id = "
1.258 + + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.259 + this.pstmtDeleteArticle2 = conn.prepareStatement(
1.260 + "DELETE FROM postings WHERE article_id = "
1.261 + + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.262 + this.pstmtDeleteArticle3 = conn.prepareStatement(
1.263 + "DELETE FROM article_ids WHERE message_id = ?");
1.264
1.265 - // Prepare statements for method getEventsCount()
1.266 - this.pstmtGetEventsCount0 = conn.prepareStatement(
1.267 - "SELECT Count(*) FROM events WHERE event_key = ? AND " +
1.268 - "event_time >= ? AND event_time < ?");
1.269 + // Prepare statements for methods getArticle()
1.270 + this.pstmtGetArticle0 = conn.prepareStatement(
1.271 + "SELECT * FROM articles WHERE article_id = "
1.272 + + "(SELECT article_id FROM article_ids WHERE message_id = ?)");
1.273 + this.pstmtGetArticle1 = conn.prepareStatement(
1.274 + "SELECT * FROM articles WHERE article_id = "
1.275 + + "(SELECT article_id FROM postings WHERE "
1.276 + + "article_index = ? AND group_id = ?)");
1.277
1.278 - this.pstmtGetEventsCount1 = conn.prepareStatement(
1.279 - "SELECT Count(*) FROM events WHERE event_key = ? AND " +
1.280 - "event_time >= ? AND event_time < ? AND group_id = ?");
1.281 -
1.282 - // Prepare statement for method getGroupForList()
1.283 - this.pstmtGetGroupForList = conn.prepareStatement(
1.284 - "SELECT name FROM groups INNER JOIN groups2list " +
1.285 - "ON groups.group_id = groups2list.group_id " +
1.286 - "WHERE groups2list.listaddress = ?");
1.287 + // Prepare statement for method getArticleHeaders()
1.288 + this.pstmtGetArticleHeaders0 = conn.prepareStatement(
1.289 + "SELECT header_key, header_value FROM headers WHERE article_id = ? "
1.290 + + "ORDER BY header_index ASC");
1.291
1.292 - // Prepare statement for method getGroup()
1.293 - this.pstmtGetGroup0 = conn.prepareStatement(
1.294 - "SELECT group_id, flags FROM groups WHERE Name = ?");
1.295 - this.pstmtGetGroup1 = conn.prepareStatement(
1.296 - "SELECT name FROM groups WHERE group_id = ?");
1.297 + // Prepare statement for method getArticleHeaders(regular expr pattern)
1.298 + this.pstmtGetArticleHeaders1 = conn.prepareStatement(
1.299 + "SELECT p.article_index, h.header_value FROM headers h "
1.300 + + "INNER JOIN postings p ON h.article_id = p.article_id "
1.301 + + "INNER JOIN groups g ON p.group_id = g.group_id "
1.302 + + "WHERE g.name = ? AND "
1.303 + + "h.header_key = ? AND "
1.304 + + "p.article_index >= ? "
1.305 + + "ORDER BY p.article_index ASC");
1.306
1.307 - // Prepare statement for method getLastArticleNumber()
1.308 - this.pstmtGetLastArticleNumber = conn.prepareStatement(
1.309 - "SELECT Max(article_index) FROM postings WHERE group_id = ?");
1.310 + this.pstmtGetArticleIDs = conn.prepareStatement(
1.311 + "SELECT article_index FROM postings WHERE group_id = ?");
1.312
1.313 - // Prepare statement for method getListForGroup()
1.314 - this.pstmtGetListForGroup = conn.prepareStatement(
1.315 - "SELECT listaddress FROM groups2list INNER JOIN groups " +
1.316 - "ON groups.group_id = groups2list.group_id WHERE name = ?");
1.317 + // Prepare statement for method getArticleIndex
1.318 + this.pstmtGetArticleIndex = conn.prepareStatement(
1.319 + "SELECT article_index FROM postings WHERE "
1.320 + + "article_id = (SELECT article_id FROM article_ids "
1.321 + + "WHERE message_id = ?) "
1.322 + + " AND group_id = ?");
1.323
1.324 - // Prepare statement for method getMaxArticleID()
1.325 - this.pstmtGetMaxArticleID = conn.prepareStatement(
1.326 - "SELECT Max(article_id) FROM articles");
1.327 -
1.328 - // Prepare statement for method getMaxArticleIndex()
1.329 - this.pstmtGetMaxArticleIndex = conn.prepareStatement(
1.330 - "SELECT Max(article_index) FROM postings WHERE group_id = ?");
1.331 -
1.332 - // Prepare statement for method getOldestArticle()
1.333 - this.pstmtGetOldestArticle = conn.prepareStatement(
1.334 - "SELECT message_id FROM article_ids WHERE article_id = " +
1.335 - "(SELECT Min(article_id) FROM article_ids)");
1.336 + // Prepare statements for method getArticleHeads()
1.337 + this.pstmtGetArticleHeads = conn.prepareStatement(
1.338 + "SELECT article_id, article_index FROM postings WHERE "
1.339 + + "postings.group_id = ? AND article_index >= ? AND "
1.340 + + "article_index <= ?");
1.341
1.342 - // Prepare statement for method getFirstArticleNumber()
1.343 - this.pstmtGetFirstArticleNumber = conn.prepareStatement(
1.344 - "SELECT Min(article_index) FROM postings WHERE group_id = ?");
1.345 -
1.346 - // Prepare statement for method getPostingsCount()
1.347 - this.pstmtGetPostingsCount = conn.prepareStatement(
1.348 - "SELECT Count(*) FROM postings NATURAL JOIN groups " +
1.349 - "WHERE groups.name = ?");
1.350 -
1.351 - // Prepare statement for method getSubscriptions()
1.352 - this.pstmtGetSubscriptions = conn.prepareStatement(
1.353 - "SELECT host, port, name FROM peers NATURAL JOIN " +
1.354 - "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
1.355 -
1.356 - // Prepare statement for method isArticleExisting()
1.357 - this.pstmtIsArticleExisting = conn.prepareStatement(
1.358 - "SELECT Count(article_id) FROM article_ids WHERE message_id = ?");
1.359 -
1.360 - // Prepare statement for method isGroupExisting()
1.361 - this.pstmtIsGroupExisting = conn.prepareStatement(
1.362 - "SELECT * FROM groups WHERE name = ?");
1.363 -
1.364 - // Prepare statement for method setConfigValue()
1.365 - this.pstmtSetConfigValue0 = conn.prepareStatement(
1.366 - "DELETE FROM config WHERE config_key = ?");
1.367 - this.pstmtSetConfigValue1 = conn.prepareStatement(
1.368 - "INSERT INTO config VALUES(?, ?)");
1.369 + // Prepare statements for method getConfigValue()
1.370 + this.pstmtGetConfigValue = conn.prepareStatement(
1.371 + "SELECT config_value FROM config WHERE config_key = ?");
1.372
1.373 - // Prepare statements for method purgeGroup()
1.374 - this.pstmtPurgeGroup0 = conn.prepareStatement(
1.375 - "DELETE FROM peer_subscriptions WHERE group_id = ?");
1.376 - this.pstmtPurgeGroup1 = conn.prepareStatement(
1.377 - "DELETE FROM groups WHERE group_id = ?");
1.378 + // Prepare statements for method getEventsCount()
1.379 + this.pstmtGetEventsCount0 = conn.prepareStatement(
1.380 + "SELECT Count(*) FROM events WHERE event_key = ? AND "
1.381 + + "event_time >= ? AND event_time < ?");
1.382
1.383 - // Prepare statement for method update(Group)
1.384 - this.pstmtUpdateGroup = conn.prepareStatement(
1.385 - "UPDATE groups SET flags = ?, name = ? WHERE group_id = ?");
1.386 - }
1.387 - catch(ClassNotFoundException ex)
1.388 - {
1.389 - throw new Error("JDBC Driver not found!", ex);
1.390 - }
1.391 - }
1.392 -
1.393 - /**
1.394 - * Adds an article to the database.
1.395 - * @param article
1.396 - * @return
1.397 - * @throws java.sql.SQLException
1.398 - */
1.399 - @Override
1.400 - public void addArticle(final Article article)
1.401 - throws StorageBackendException
1.402 - {
1.403 - try
1.404 - {
1.405 - this.conn.setAutoCommit(false);
1.406 + this.pstmtGetEventsCount1 = conn.prepareStatement(
1.407 + "SELECT Count(*) FROM events WHERE event_key = ? AND "
1.408 + + "event_time >= ? AND event_time < ? AND group_id = ?");
1.409
1.410 - int newArticleID = getMaxArticleID() + 1;
1.411 + // Prepare statement for method getGroupForList()
1.412 + this.pstmtGetGroupForList = conn.prepareStatement(
1.413 + "SELECT name FROM groups INNER JOIN groups2list "
1.414 + + "ON groups.group_id = groups2list.group_id "
1.415 + + "WHERE groups2list.listaddress = ?");
1.416
1.417 - // Fill prepared statement with values;
1.418 - // writes body to article table
1.419 - pstmtAddArticle1.setInt(1, newArticleID);
1.420 - pstmtAddArticle1.setBytes(2, article.getBody());
1.421 - pstmtAddArticle1.execute();
1.422 + // Prepare statement for method getGroup()
1.423 + this.pstmtGetGroup0 = conn.prepareStatement(
1.424 + "SELECT group_id, flags FROM groups WHERE Name = ?");
1.425 + this.pstmtGetGroup1 = conn.prepareStatement(
1.426 + "SELECT name FROM groups WHERE group_id = ?");
1.427
1.428 - // Add headers
1.429 - Enumeration headers = article.getAllHeaders();
1.430 - for(int n = 0; headers.hasMoreElements(); n++)
1.431 - {
1.432 - Header header = (Header)headers.nextElement();
1.433 - pstmtAddArticle2.setInt(1, newArticleID);
1.434 - pstmtAddArticle2.setString(2, header.getName().toLowerCase());
1.435 - pstmtAddArticle2.setString(3,
1.436 - header.getValue().replaceAll("[\r\n]", ""));
1.437 - pstmtAddArticle2.setInt(4, n);
1.438 - pstmtAddArticle2.execute();
1.439 - }
1.440 -
1.441 - // For each newsgroup add a reference
1.442 - List<Group> groups = article.getGroups();
1.443 - for(Group group : groups)
1.444 - {
1.445 - pstmtAddArticle3.setLong(1, group.getInternalID());
1.446 - pstmtAddArticle3.setInt(2, newArticleID);
1.447 - pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getInternalID()) + 1);
1.448 - pstmtAddArticle3.execute();
1.449 - }
1.450 -
1.451 - // Write message-id to article_ids table
1.452 - this.pstmtAddArticle4.setInt(1, newArticleID);
1.453 - this.pstmtAddArticle4.setString(2, article.getMessageID());
1.454 - this.pstmtAddArticle4.execute();
1.455 + // Prepare statement for method getLastArticleNumber()
1.456 + this.pstmtGetLastArticleNumber = conn.prepareStatement(
1.457 + "SELECT Max(article_index) FROM postings WHERE group_id = ?");
1.458
1.459 - this.conn.commit();
1.460 - this.conn.setAutoCommit(true);
1.461 + // Prepare statement for method getListForGroup()
1.462 + this.pstmtGetListForGroup = conn.prepareStatement(
1.463 + "SELECT listaddress FROM groups2list INNER JOIN groups "
1.464 + + "ON groups.group_id = groups2list.group_id WHERE name = ?");
1.465
1.466 - this.restarts = 0; // Reset error count
1.467 - }
1.468 - catch(SQLException ex)
1.469 - {
1.470 - try
1.471 - {
1.472 - this.conn.rollback(); // Rollback changes
1.473 - }
1.474 - catch(SQLException ex2)
1.475 - {
1.476 - Log.get().severe("Rollback of addArticle() failed: " + ex2);
1.477 - }
1.478 -
1.479 - try
1.480 - {
1.481 - this.conn.setAutoCommit(true); // and release locks
1.482 - }
1.483 - catch(SQLException ex2)
1.484 - {
1.485 - Log.get().severe("setAutoCommit(true) of addArticle() failed: " + ex2);
1.486 - }
1.487 + // Prepare statement for method getMaxArticleID()
1.488 + this.pstmtGetMaxArticleID = conn.prepareStatement(
1.489 + "SELECT Max(article_id) FROM articles");
1.490
1.491 - restartConnection(ex);
1.492 - addArticle(article);
1.493 - }
1.494 - }
1.495 -
1.496 - /**
1.497 - * Adds a group to the JDBCDatabase. This method is not accessible via NNTP.
1.498 - * @param name
1.499 - * @throws java.sql.SQLException
1.500 - */
1.501 - @Override
1.502 - public void addGroup(String name, int flags)
1.503 - throws StorageBackendException
1.504 - {
1.505 - try
1.506 - {
1.507 - this.conn.setAutoCommit(false);
1.508 - pstmtAddGroup0.setString(1, name);
1.509 - pstmtAddGroup0.setInt(2, flags);
1.510 + // Prepare statement for method getMaxArticleIndex()
1.511 + this.pstmtGetMaxArticleIndex = conn.prepareStatement(
1.512 + "SELECT Max(article_index) FROM postings WHERE group_id = ?");
1.513
1.514 - pstmtAddGroup0.executeUpdate();
1.515 - this.conn.commit();
1.516 - this.conn.setAutoCommit(true);
1.517 - this.restarts = 0; // Reset error count
1.518 - }
1.519 - catch(SQLException ex)
1.520 - {
1.521 - try
1.522 - {
1.523 - this.conn.rollback();
1.524 - this.conn.setAutoCommit(true);
1.525 - }
1.526 - catch(SQLException ex2)
1.527 - {
1.528 - ex2.printStackTrace();
1.529 - }
1.530 + // Prepare statement for method getOldestArticle()
1.531 + this.pstmtGetOldestArticle = conn.prepareStatement(
1.532 + "SELECT message_id FROM article_ids WHERE article_id = "
1.533 + + "(SELECT Min(article_id) FROM article_ids)");
1.534
1.535 - restartConnection(ex);
1.536 - addGroup(name, flags);
1.537 - }
1.538 - }
1.539 + // Prepare statement for method getFirstArticleNumber()
1.540 + this.pstmtGetFirstArticleNumber = conn.prepareStatement(
1.541 + "SELECT Min(article_index) FROM postings WHERE group_id = ?");
1.542
1.543 - @Override
1.544 - public void addEvent(long time, int type, long gid)
1.545 - throws StorageBackendException
1.546 - {
1.547 - try
1.548 - {
1.549 - this.conn.setAutoCommit(false);
1.550 - this.pstmtAddEvent.setLong(1, time);
1.551 - this.pstmtAddEvent.setInt(2, type);
1.552 - this.pstmtAddEvent.setLong(3, gid);
1.553 - this.pstmtAddEvent.executeUpdate();
1.554 - this.conn.commit();
1.555 - this.conn.setAutoCommit(true);
1.556 - this.restarts = 0;
1.557 - }
1.558 - catch(SQLException ex)
1.559 - {
1.560 - try
1.561 - {
1.562 - this.conn.rollback();
1.563 - this.conn.setAutoCommit(true);
1.564 - }
1.565 - catch(SQLException ex2)
1.566 - {
1.567 - ex2.printStackTrace();
1.568 - }
1.569 + // Prepare statement for method getPostingsCount()
1.570 + this.pstmtGetPostingsCount = conn.prepareStatement(
1.571 + "SELECT Count(*) FROM postings NATURAL JOIN groups "
1.572 + + "WHERE groups.name = ?");
1.573
1.574 - restartConnection(ex);
1.575 - addEvent(time, type, gid);
1.576 - }
1.577 - }
1.578 + // Prepare statement for method getSubscriptions()
1.579 + this.pstmtGetSubscriptions = conn.prepareStatement(
1.580 + "SELECT host, port, name FROM peers NATURAL JOIN "
1.581 + + "peer_subscriptions NATURAL JOIN groups WHERE feedtype = ?");
1.582
1.583 - @Override
1.584 - public int countArticles()
1.585 - throws StorageBackendException
1.586 - {
1.587 - ResultSet rs = null;
1.588 + // Prepare statement for method isArticleExisting()
1.589 + this.pstmtIsArticleExisting = conn.prepareStatement(
1.590 + "SELECT Count(article_id) FROM article_ids WHERE message_id = ?");
1.591
1.592 - try
1.593 - {
1.594 - rs = this.pstmtCountArticles.executeQuery();
1.595 - if(rs.next())
1.596 - {
1.597 - return rs.getInt(1);
1.598 - }
1.599 - else
1.600 - {
1.601 - return -1;
1.602 - }
1.603 - }
1.604 - catch(SQLException ex)
1.605 - {
1.606 - restartConnection(ex);
1.607 - return countArticles();
1.608 - }
1.609 - finally
1.610 - {
1.611 - if(rs != null)
1.612 - {
1.613 - try
1.614 - {
1.615 - rs.close();
1.616 - }
1.617 - catch(SQLException ex)
1.618 - {
1.619 - ex.printStackTrace();
1.620 - }
1.621 - restarts = 0;
1.622 - }
1.623 - }
1.624 - }
1.625 + // Prepare statement for method isGroupExisting()
1.626 + this.pstmtIsGroupExisting = conn.prepareStatement(
1.627 + "SELECT * FROM groups WHERE name = ?");
1.628
1.629 - @Override
1.630 - public int countGroups()
1.631 - throws StorageBackendException
1.632 - {
1.633 - ResultSet rs = null;
1.634 + // Prepare statement for method setConfigValue()
1.635 + this.pstmtSetConfigValue0 = conn.prepareStatement(
1.636 + "DELETE FROM config WHERE config_key = ?");
1.637 + this.pstmtSetConfigValue1 = conn.prepareStatement(
1.638 + "INSERT INTO config VALUES(?, ?)");
1.639
1.640 - try
1.641 - {
1.642 - rs = this.pstmtCountGroups.executeQuery();
1.643 - if(rs.next())
1.644 - {
1.645 - return rs.getInt(1);
1.646 - }
1.647 - else
1.648 - {
1.649 - return -1;
1.650 - }
1.651 - }
1.652 - catch(SQLException ex)
1.653 - {
1.654 - restartConnection(ex);
1.655 - return countGroups();
1.656 - }
1.657 - finally
1.658 - {
1.659 - if(rs != null)
1.660 - {
1.661 - try
1.662 - {
1.663 - rs.close();
1.664 - }
1.665 - catch(SQLException ex)
1.666 - {
1.667 - ex.printStackTrace();
1.668 - }
1.669 - restarts = 0;
1.670 - }
1.671 - }
1.672 - }
1.673 + // Prepare statements for method purgeGroup()
1.674 + this.pstmtPurgeGroup0 = conn.prepareStatement(
1.675 + "DELETE FROM peer_subscriptions WHERE group_id = ?");
1.676 + this.pstmtPurgeGroup1 = conn.prepareStatement(
1.677 + "DELETE FROM groups WHERE group_id = ?");
1.678
1.679 - @Override
1.680 - public void delete(final String messageID)
1.681 - throws StorageBackendException
1.682 - {
1.683 - try
1.684 - {
1.685 - this.conn.setAutoCommit(false);
1.686 -
1.687 - this.pstmtDeleteArticle0.setString(1, messageID);
1.688 - int rs = this.pstmtDeleteArticle0.executeUpdate();
1.689 -
1.690 - // We do not trust the ON DELETE CASCADE functionality to delete
1.691 - // orphaned references...
1.692 - this.pstmtDeleteArticle1.setString(1, messageID);
1.693 - rs = this.pstmtDeleteArticle1.executeUpdate();
1.694 + // Prepare statement for method update(Group)
1.695 + this.pstmtUpdateGroup = conn.prepareStatement(
1.696 + "UPDATE groups SET flags = ?, name = ? WHERE group_id = ?");
1.697 + } catch (ClassNotFoundException ex) {
1.698 + throw new Error("JDBC Driver not found!", ex);
1.699 + }
1.700 + }
1.701
1.702 - this.pstmtDeleteArticle2.setString(1, messageID);
1.703 - rs = this.pstmtDeleteArticle2.executeUpdate();
1.704 + /**
1.705 + * Adds an article to the database.
1.706 + * @param article
1.707 + * @return
1.708 + * @throws java.sql.SQLException
1.709 + */
1.710 + @Override
1.711 + public void addArticle(final Article article)
1.712 + throws StorageBackendException
1.713 + {
1.714 + try {
1.715 + this.conn.setAutoCommit(false);
1.716
1.717 - this.pstmtDeleteArticle3.setString(1, messageID);
1.718 - rs = this.pstmtDeleteArticle3.executeUpdate();
1.719 -
1.720 - this.conn.commit();
1.721 - this.conn.setAutoCommit(true);
1.722 - }
1.723 - catch(SQLException ex)
1.724 - {
1.725 - throw new StorageBackendException(ex);
1.726 - }
1.727 - }
1.728 + int newArticleID = getMaxArticleID() + 1;
1.729
1.730 - @Override
1.731 - public Article getArticle(String messageID)
1.732 - throws StorageBackendException
1.733 - {
1.734 - ResultSet rs = null;
1.735 - try
1.736 - {
1.737 - pstmtGetArticle0.setString(1, messageID);
1.738 - rs = pstmtGetArticle0.executeQuery();
1.739 + // Fill prepared statement with values;
1.740 + // writes body to article table
1.741 + pstmtAddArticle1.setInt(1, newArticleID);
1.742 + pstmtAddArticle1.setBytes(2, article.getBody());
1.743 + pstmtAddArticle1.execute();
1.744
1.745 - if(!rs.next())
1.746 - {
1.747 - return null;
1.748 - }
1.749 - else
1.750 - {
1.751 - byte[] body = rs.getBytes("body");
1.752 - String headers = getArticleHeaders(rs.getInt("article_id"));
1.753 - return new Article(headers, body);
1.754 - }
1.755 - }
1.756 - catch(SQLException ex)
1.757 - {
1.758 - restartConnection(ex);
1.759 - return getArticle(messageID);
1.760 - }
1.761 - finally
1.762 - {
1.763 - if(rs != null)
1.764 - {
1.765 - try
1.766 - {
1.767 - rs.close();
1.768 - }
1.769 - catch(SQLException ex)
1.770 - {
1.771 - ex.printStackTrace();
1.772 - }
1.773 - restarts = 0; // Reset error count
1.774 - }
1.775 - }
1.776 - }
1.777 -
1.778 - /**
1.779 - * Retrieves an article by its ID.
1.780 - * @param articleID
1.781 - * @return
1.782 - * @throws StorageBackendException
1.783 - */
1.784 - @Override
1.785 - public Article getArticle(long articleIndex, long gid)
1.786 - throws StorageBackendException
1.787 - {
1.788 - ResultSet rs = null;
1.789 + // Add headers
1.790 + Enumeration headers = article.getAllHeaders();
1.791 + for (int n = 0; headers.hasMoreElements(); n++) {
1.792 + Header header = (Header) headers.nextElement();
1.793 + pstmtAddArticle2.setInt(1, newArticleID);
1.794 + pstmtAddArticle2.setString(2, header.getName().toLowerCase());
1.795 + pstmtAddArticle2.setString(3,
1.796 + header.getValue().replaceAll("[\r\n]", ""));
1.797 + pstmtAddArticle2.setInt(4, n);
1.798 + pstmtAddArticle2.execute();
1.799 + }
1.800
1.801 - try
1.802 - {
1.803 - this.pstmtGetArticle1.setLong(1, articleIndex);
1.804 - this.pstmtGetArticle1.setLong(2, gid);
1.805 + // For each newsgroup add a reference
1.806 + List<Group> groups = article.getGroups();
1.807 + for (Group group : groups) {
1.808 + pstmtAddArticle3.setLong(1, group.getInternalID());
1.809 + pstmtAddArticle3.setInt(2, newArticleID);
1.810 + pstmtAddArticle3.setLong(3, getMaxArticleIndex(group.getInternalID()) + 1);
1.811 + pstmtAddArticle3.execute();
1.812 + }
1.813
1.814 - rs = this.pstmtGetArticle1.executeQuery();
1.815 + // Write message-id to article_ids table
1.816 + this.pstmtAddArticle4.setInt(1, newArticleID);
1.817 + this.pstmtAddArticle4.setString(2, article.getMessageID());
1.818 + this.pstmtAddArticle4.execute();
1.819
1.820 - if(rs.next())
1.821 - {
1.822 - byte[] body = rs.getBytes("body");
1.823 - String headers = getArticleHeaders(rs.getInt("article_id"));
1.824 - return new Article(headers, body);
1.825 - }
1.826 - else
1.827 - {
1.828 - return null;
1.829 - }
1.830 - }
1.831 - catch(SQLException ex)
1.832 - {
1.833 - restartConnection(ex);
1.834 - return getArticle(articleIndex, gid);
1.835 - }
1.836 - finally
1.837 - {
1.838 - if(rs != null)
1.839 - {
1.840 - try
1.841 - {
1.842 - rs.close();
1.843 - }
1.844 - catch(SQLException ex)
1.845 - {
1.846 - ex.printStackTrace();
1.847 - }
1.848 - restarts = 0;
1.849 - }
1.850 - }
1.851 - }
1.852 + this.conn.commit();
1.853 + this.conn.setAutoCommit(true);
1.854
1.855 - /**
1.856 - * Searches for fitting header values using the given regular expression.
1.857 - * @param group
1.858 - * @param start
1.859 - * @param end
1.860 - * @param headerKey
1.861 - * @param pattern
1.862 - * @return
1.863 - * @throws StorageBackendException
1.864 - */
1.865 - @Override
1.866 - public List<Pair<Long, String>> getArticleHeaders(Channel group, long start,
1.867 - long end, String headerKey, String patStr)
1.868 - throws StorageBackendException, PatternSyntaxException
1.869 - {
1.870 - ResultSet rs = null;
1.871 - List<Pair<Long, String>> heads = new ArrayList<Pair<Long, String>>();
1.872 + this.restarts = 0; // Reset error count
1.873 + } catch (SQLException ex) {
1.874 + try {
1.875 + this.conn.rollback(); // Rollback changes
1.876 + } catch (SQLException ex2) {
1.877 + Log.get().severe("Rollback of addArticle() failed: " + ex2);
1.878 + }
1.879
1.880 - try
1.881 - {
1.882 - this.pstmtGetArticleHeaders1.setString(1, group.getName());
1.883 - this.pstmtGetArticleHeaders1.setString(2, headerKey);
1.884 - this.pstmtGetArticleHeaders1.setLong(3, start);
1.885 + try {
1.886 + this.conn.setAutoCommit(true); // and release locks
1.887 + } catch (SQLException ex2) {
1.888 + Log.get().severe("setAutoCommit(true) of addArticle() failed: " + ex2);
1.889 + }
1.890
1.891 - rs = this.pstmtGetArticleHeaders1.executeQuery();
1.892 + restartConnection(ex);
1.893 + addArticle(article);
1.894 + }
1.895 + }
1.896
1.897 - // Convert the "NNTP" regex to Java regex
1.898 - patStr = patStr.replace("*", ".*");
1.899 - Pattern pattern = Pattern.compile(patStr);
1.900 + /**
1.901 + * Adds a group to the JDBCDatabase. This method is not accessible via NNTP.
1.902 + * @param name
1.903 + * @throws java.sql.SQLException
1.904 + */
1.905 + @Override
1.906 + public void addGroup(String name, int flags)
1.907 + throws StorageBackendException
1.908 + {
1.909 + try {
1.910 + this.conn.setAutoCommit(false);
1.911 + pstmtAddGroup0.setString(1, name);
1.912 + pstmtAddGroup0.setInt(2, flags);
1.913
1.914 - while(rs.next())
1.915 - {
1.916 - Long articleIndex = rs.getLong(1);
1.917 - if(end < 0 || articleIndex <= end) // Match start is done via SQL
1.918 - {
1.919 - String headerValue = rs.getString(2);
1.920 - Matcher matcher = pattern.matcher(headerValue);
1.921 - if(matcher.matches())
1.922 - {
1.923 - heads.add(new Pair<Long, String>(articleIndex, headerValue));
1.924 - }
1.925 - }
1.926 - }
1.927 - }
1.928 - catch(SQLException ex)
1.929 - {
1.930 - restartConnection(ex);
1.931 - return getArticleHeaders(group, start, end, headerKey, patStr);
1.932 - }
1.933 - finally
1.934 - {
1.935 - if(rs != null)
1.936 - {
1.937 - try
1.938 - {
1.939 - rs.close();
1.940 - }
1.941 - catch(SQLException ex)
1.942 - {
1.943 - ex.printStackTrace();
1.944 - }
1.945 - }
1.946 - }
1.947 + pstmtAddGroup0.executeUpdate();
1.948 + this.conn.commit();
1.949 + this.conn.setAutoCommit(true);
1.950 + this.restarts = 0; // Reset error count
1.951 + } catch (SQLException ex) {
1.952 + try {
1.953 + this.conn.rollback();
1.954 + this.conn.setAutoCommit(true);
1.955 + } catch (SQLException ex2) {
1.956 + ex2.printStackTrace();
1.957 + }
1.958
1.959 - return heads;
1.960 - }
1.961 + restartConnection(ex);
1.962 + addGroup(name, flags);
1.963 + }
1.964 + }
1.965
1.966 - private String getArticleHeaders(long articleID)
1.967 - throws StorageBackendException
1.968 - {
1.969 - ResultSet rs = null;
1.970 -
1.971 - try
1.972 - {
1.973 - this.pstmtGetArticleHeaders0.setLong(1, articleID);
1.974 - rs = this.pstmtGetArticleHeaders0.executeQuery();
1.975 -
1.976 - StringBuilder buf = new StringBuilder();
1.977 - if(rs.next())
1.978 - {
1.979 - for(;;)
1.980 - {
1.981 - buf.append(rs.getString(1)); // key
1.982 - buf.append(": ");
1.983 - String foldedValue = MimeUtility.fold(0, rs.getString(2));
1.984 - buf.append(foldedValue); // value
1.985 - if(rs.next())
1.986 - {
1.987 - buf.append("\r\n");
1.988 - }
1.989 - else
1.990 - {
1.991 - break;
1.992 - }
1.993 - }
1.994 - }
1.995 -
1.996 - return buf.toString();
1.997 - }
1.998 - catch(SQLException ex)
1.999 - {
1.1000 - restartConnection(ex);
1.1001 - return getArticleHeaders(articleID);
1.1002 - }
1.1003 - finally
1.1004 - {
1.1005 - if(rs != null)
1.1006 - {
1.1007 - try
1.1008 - {
1.1009 - rs.close();
1.1010 - }
1.1011 - catch(SQLException ex)
1.1012 - {
1.1013 - ex.printStackTrace();
1.1014 - }
1.1015 - }
1.1016 - }
1.1017 - }
1.1018 + @Override
1.1019 + public void addEvent(long time, int type, long gid)
1.1020 + throws StorageBackendException
1.1021 + {
1.1022 + try {
1.1023 + this.conn.setAutoCommit(false);
1.1024 + this.pstmtAddEvent.setLong(1, time);
1.1025 + this.pstmtAddEvent.setInt(2, type);
1.1026 + this.pstmtAddEvent.setLong(3, gid);
1.1027 + this.pstmtAddEvent.executeUpdate();
1.1028 + this.conn.commit();
1.1029 + this.conn.setAutoCommit(true);
1.1030 + this.restarts = 0;
1.1031 + } catch (SQLException ex) {
1.1032 + try {
1.1033 + this.conn.rollback();
1.1034 + this.conn.setAutoCommit(true);
1.1035 + } catch (SQLException ex2) {
1.1036 + ex2.printStackTrace();
1.1037 + }
1.1038
1.1039 - @Override
1.1040 - public long getArticleIndex(Article article, Group group)
1.1041 - throws StorageBackendException
1.1042 - {
1.1043 - ResultSet rs = null;
1.1044 + restartConnection(ex);
1.1045 + addEvent(time, type, gid);
1.1046 + }
1.1047 + }
1.1048
1.1049 - try
1.1050 - {
1.1051 - this.pstmtGetArticleIndex.setString(1, article.getMessageID());
1.1052 - this.pstmtGetArticleIndex.setLong(2, group.getInternalID());
1.1053 -
1.1054 - rs = this.pstmtGetArticleIndex.executeQuery();
1.1055 - if(rs.next())
1.1056 - {
1.1057 - return rs.getLong(1);
1.1058 - }
1.1059 - else
1.1060 - {
1.1061 - return -1;
1.1062 - }
1.1063 - }
1.1064 - catch(SQLException ex)
1.1065 - {
1.1066 - restartConnection(ex);
1.1067 - return getArticleIndex(article, group);
1.1068 - }
1.1069 - finally
1.1070 - {
1.1071 - if(rs != null)
1.1072 - {
1.1073 - try
1.1074 - {
1.1075 - rs.close();
1.1076 - }
1.1077 - catch(SQLException ex)
1.1078 - {
1.1079 - ex.printStackTrace();
1.1080 - }
1.1081 - }
1.1082 - }
1.1083 - }
1.1084 -
1.1085 - /**
1.1086 - * Returns a list of Long/Article Pairs.
1.1087 - * @throws java.sql.SQLException
1.1088 - */
1.1089 - @Override
1.1090 - public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first,
1.1091 - long last)
1.1092 - throws StorageBackendException
1.1093 - {
1.1094 - ResultSet rs = null;
1.1095 + @Override
1.1096 + public int countArticles()
1.1097 + throws StorageBackendException
1.1098 + {
1.1099 + ResultSet rs = null;
1.1100
1.1101 - try
1.1102 - {
1.1103 - this.pstmtGetArticleHeads.setLong(1, group.getInternalID());
1.1104 - this.pstmtGetArticleHeads.setLong(2, first);
1.1105 - this.pstmtGetArticleHeads.setLong(3, last);
1.1106 - rs = pstmtGetArticleHeads.executeQuery();
1.1107 + try {
1.1108 + rs = this.pstmtCountArticles.executeQuery();
1.1109 + if (rs.next()) {
1.1110 + return rs.getInt(1);
1.1111 + } else {
1.1112 + return -1;
1.1113 + }
1.1114 + } catch (SQLException ex) {
1.1115 + restartConnection(ex);
1.1116 + return countArticles();
1.1117 + } finally {
1.1118 + if (rs != null) {
1.1119 + try {
1.1120 + rs.close();
1.1121 + } catch (SQLException ex) {
1.1122 + ex.printStackTrace();
1.1123 + }
1.1124 + restarts = 0;
1.1125 + }
1.1126 + }
1.1127 + }
1.1128
1.1129 - List<Pair<Long, ArticleHead>> articles
1.1130 - = new ArrayList<Pair<Long, ArticleHead>>();
1.1131 + @Override
1.1132 + public int countGroups()
1.1133 + throws StorageBackendException
1.1134 + {
1.1135 + ResultSet rs = null;
1.1136
1.1137 - while (rs.next())
1.1138 - {
1.1139 - long aid = rs.getLong("article_id");
1.1140 - long aidx = rs.getLong("article_index");
1.1141 - String headers = getArticleHeaders(aid);
1.1142 - articles.add(new Pair<Long, ArticleHead>(aidx,
1.1143 - new ArticleHead(headers)));
1.1144 - }
1.1145 + try {
1.1146 + rs = this.pstmtCountGroups.executeQuery();
1.1147 + if (rs.next()) {
1.1148 + return rs.getInt(1);
1.1149 + } else {
1.1150 + return -1;
1.1151 + }
1.1152 + } catch (SQLException ex) {
1.1153 + restartConnection(ex);
1.1154 + return countGroups();
1.1155 + } finally {
1.1156 + if (rs != null) {
1.1157 + try {
1.1158 + rs.close();
1.1159 + } catch (SQLException ex) {
1.1160 + ex.printStackTrace();
1.1161 + }
1.1162 + restarts = 0;
1.1163 + }
1.1164 + }
1.1165 + }
1.1166
1.1167 - return articles;
1.1168 - }
1.1169 - catch(SQLException ex)
1.1170 - {
1.1171 - restartConnection(ex);
1.1172 - return getArticleHeads(group, first, last);
1.1173 - }
1.1174 - finally
1.1175 - {
1.1176 - if(rs != null)
1.1177 - {
1.1178 - try
1.1179 - {
1.1180 - rs.close();
1.1181 - }
1.1182 - catch(SQLException ex)
1.1183 - {
1.1184 - ex.printStackTrace();
1.1185 - }
1.1186 - }
1.1187 - }
1.1188 - }
1.1189 + @Override
1.1190 + public void delete(final String messageID)
1.1191 + throws StorageBackendException
1.1192 + {
1.1193 + try {
1.1194 + this.conn.setAutoCommit(false);
1.1195
1.1196 - @Override
1.1197 - public List<Long> getArticleNumbers(long gid)
1.1198 - throws StorageBackendException
1.1199 - {
1.1200 - ResultSet rs = null;
1.1201 - try
1.1202 - {
1.1203 - List<Long> ids = new ArrayList<Long>();
1.1204 - this.pstmtGetArticleIDs.setLong(1, gid);
1.1205 - rs = this.pstmtGetArticleIDs.executeQuery();
1.1206 - while(rs.next())
1.1207 - {
1.1208 - ids.add(rs.getLong(1));
1.1209 - }
1.1210 - return ids;
1.1211 - }
1.1212 - catch(SQLException ex)
1.1213 - {
1.1214 - restartConnection(ex);
1.1215 - return getArticleNumbers(gid);
1.1216 - }
1.1217 - finally
1.1218 - {
1.1219 - if(rs != null)
1.1220 - {
1.1221 - try
1.1222 - {
1.1223 - rs.close();
1.1224 - restarts = 0; // Clear the restart count after successful request
1.1225 - }
1.1226 - catch(SQLException ex)
1.1227 - {
1.1228 - ex.printStackTrace();
1.1229 - }
1.1230 - }
1.1231 - }
1.1232 - }
1.1233 + this.pstmtDeleteArticle0.setString(1, messageID);
1.1234 + int rs = this.pstmtDeleteArticle0.executeUpdate();
1.1235
1.1236 - @Override
1.1237 - public String getConfigValue(String key)
1.1238 - throws StorageBackendException
1.1239 - {
1.1240 - ResultSet rs = null;
1.1241 - try
1.1242 - {
1.1243 - this.pstmtGetConfigValue.setString(1, key);
1.1244 + // We do not trust the ON DELETE CASCADE functionality to delete
1.1245 + // orphaned references...
1.1246 + this.pstmtDeleteArticle1.setString(1, messageID);
1.1247 + rs = this.pstmtDeleteArticle1.executeUpdate();
1.1248
1.1249 - rs = this.pstmtGetConfigValue.executeQuery();
1.1250 - if(rs.next())
1.1251 - {
1.1252 - return rs.getString(1); // First data on index 1 not 0
1.1253 - }
1.1254 - else
1.1255 - {
1.1256 - return null;
1.1257 - }
1.1258 - }
1.1259 - catch(SQLException ex)
1.1260 - {
1.1261 - restartConnection(ex);
1.1262 - return getConfigValue(key);
1.1263 - }
1.1264 - finally
1.1265 - {
1.1266 - if(rs != null)
1.1267 - {
1.1268 - try
1.1269 - {
1.1270 - rs.close();
1.1271 - }
1.1272 - catch(SQLException ex)
1.1273 - {
1.1274 - ex.printStackTrace();
1.1275 - }
1.1276 - restarts = 0; // Clear the restart count after successful request
1.1277 - }
1.1278 - }
1.1279 - }
1.1280 + this.pstmtDeleteArticle2.setString(1, messageID);
1.1281 + rs = this.pstmtDeleteArticle2.executeUpdate();
1.1282
1.1283 - @Override
1.1284 - public int getEventsCount(int type, long start, long end, Channel channel)
1.1285 - throws StorageBackendException
1.1286 - {
1.1287 - ResultSet rs = null;
1.1288 -
1.1289 - try
1.1290 - {
1.1291 - if(channel == null)
1.1292 - {
1.1293 - this.pstmtGetEventsCount0.setInt(1, type);
1.1294 - this.pstmtGetEventsCount0.setLong(2, start);
1.1295 - this.pstmtGetEventsCount0.setLong(3, end);
1.1296 - rs = this.pstmtGetEventsCount0.executeQuery();
1.1297 - }
1.1298 - else
1.1299 - {
1.1300 - this.pstmtGetEventsCount1.setInt(1, type);
1.1301 - this.pstmtGetEventsCount1.setLong(2, start);
1.1302 - this.pstmtGetEventsCount1.setLong(3, end);
1.1303 - this.pstmtGetEventsCount1.setLong(4, channel.getInternalID());
1.1304 - rs = this.pstmtGetEventsCount1.executeQuery();
1.1305 - }
1.1306 -
1.1307 - if(rs.next())
1.1308 - {
1.1309 - return rs.getInt(1);
1.1310 - }
1.1311 - else
1.1312 - {
1.1313 - return -1;
1.1314 - }
1.1315 - }
1.1316 - catch(SQLException ex)
1.1317 - {
1.1318 - restartConnection(ex);
1.1319 - return getEventsCount(type, start, end, channel);
1.1320 - }
1.1321 - finally
1.1322 - {
1.1323 - if(rs != null)
1.1324 - {
1.1325 - try
1.1326 - {
1.1327 - rs.close();
1.1328 - }
1.1329 - catch(SQLException ex)
1.1330 - {
1.1331 - ex.printStackTrace();
1.1332 - }
1.1333 - }
1.1334 - }
1.1335 - }
1.1336 -
1.1337 - /**
1.1338 - * Reads all Groups from the JDBCDatabase.
1.1339 - * @return
1.1340 - * @throws StorageBackendException
1.1341 - */
1.1342 - @Override
1.1343 - public List<Channel> getGroups()
1.1344 - throws StorageBackendException
1.1345 - {
1.1346 - ResultSet rs;
1.1347 - List<Channel> buffer = new ArrayList<Channel>();
1.1348 - Statement stmt = null;
1.1349 + this.pstmtDeleteArticle3.setString(1, messageID);
1.1350 + rs = this.pstmtDeleteArticle3.executeUpdate();
1.1351
1.1352 - try
1.1353 - {
1.1354 - stmt = conn.createStatement();
1.1355 - rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
1.1356 + this.conn.commit();
1.1357 + this.conn.setAutoCommit(true);
1.1358 + } catch (SQLException ex) {
1.1359 + throw new StorageBackendException(ex);
1.1360 + }
1.1361 + }
1.1362
1.1363 - while(rs.next())
1.1364 - {
1.1365 - String name = rs.getString("name");
1.1366 - long id = rs.getLong("group_id");
1.1367 - int flags = rs.getInt("flags");
1.1368 -
1.1369 - Group group = new Group(name, id, flags);
1.1370 - buffer.add(group);
1.1371 - }
1.1372 + @Override
1.1373 + public Article getArticle(String messageID)
1.1374 + throws StorageBackendException
1.1375 + {
1.1376 + ResultSet rs = null;
1.1377 + try {
1.1378 + pstmtGetArticle0.setString(1, messageID);
1.1379 + rs = pstmtGetArticle0.executeQuery();
1.1380
1.1381 - return buffer;
1.1382 - }
1.1383 - catch(SQLException ex)
1.1384 - {
1.1385 - restartConnection(ex);
1.1386 - return getGroups();
1.1387 - }
1.1388 - finally
1.1389 - {
1.1390 - if(stmt != null)
1.1391 - {
1.1392 - try
1.1393 - {
1.1394 - stmt.close(); // Implicitely closes ResultSets
1.1395 - }
1.1396 - catch(SQLException ex)
1.1397 - {
1.1398 - ex.printStackTrace();
1.1399 - }
1.1400 - }
1.1401 - }
1.1402 - }
1.1403 + if (!rs.next()) {
1.1404 + return null;
1.1405 + } else {
1.1406 + byte[] body = rs.getBytes("body");
1.1407 + String headers = getArticleHeaders(rs.getInt("article_id"));
1.1408 + return new Article(headers, body);
1.1409 + }
1.1410 + } catch (SQLException ex) {
1.1411 + restartConnection(ex);
1.1412 + return getArticle(messageID);
1.1413 + } finally {
1.1414 + if (rs != null) {
1.1415 + try {
1.1416 + rs.close();
1.1417 + } catch (SQLException ex) {
1.1418 + ex.printStackTrace();
1.1419 + }
1.1420 + restarts = 0; // Reset error count
1.1421 + }
1.1422 + }
1.1423 + }
1.1424
1.1425 - @Override
1.1426 - public List<String> getGroupsForList(String listAddress)
1.1427 - throws StorageBackendException
1.1428 - {
1.1429 - ResultSet rs = null;
1.1430 -
1.1431 - try
1.1432 - {
1.1433 - this.pstmtGetGroupForList.setString(1, listAddress);
1.1434 + /**
1.1435 + * Retrieves an article by its ID.
1.1436 + * @param articleID
1.1437 + * @return
1.1438 + * @throws StorageBackendException
1.1439 + */
1.1440 + @Override
1.1441 + public Article getArticle(long articleIndex, long gid)
1.1442 + throws StorageBackendException
1.1443 + {
1.1444 + ResultSet rs = null;
1.1445
1.1446 - rs = this.pstmtGetGroupForList.executeQuery();
1.1447 - List<String> groups = new ArrayList<String>();
1.1448 - while(rs.next())
1.1449 - {
1.1450 - String group = rs.getString(1);
1.1451 - groups.add(group);
1.1452 - }
1.1453 - return groups;
1.1454 - }
1.1455 - catch(SQLException ex)
1.1456 - {
1.1457 - restartConnection(ex);
1.1458 - return getGroupsForList(listAddress);
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 - /**
1.1477 - * Returns the Group that is identified by the name.
1.1478 - * @param name
1.1479 - * @return
1.1480 - * @throws StorageBackendException
1.1481 - */
1.1482 - @Override
1.1483 - public Group getGroup(String name)
1.1484 - throws StorageBackendException
1.1485 - {
1.1486 - ResultSet rs = null;
1.1487 -
1.1488 - try
1.1489 - {
1.1490 - this.pstmtGetGroup0.setString(1, name);
1.1491 - rs = this.pstmtGetGroup0.executeQuery();
1.1492 + try {
1.1493 + this.pstmtGetArticle1.setLong(1, articleIndex);
1.1494 + this.pstmtGetArticle1.setLong(2, gid);
1.1495
1.1496 - if (!rs.next())
1.1497 - {
1.1498 - return null;
1.1499 - }
1.1500 - else
1.1501 - {
1.1502 - long id = rs.getLong("group_id");
1.1503 - int flags = rs.getInt("flags");
1.1504 - return new Group(name, id, flags);
1.1505 - }
1.1506 - }
1.1507 - catch(SQLException ex)
1.1508 - {
1.1509 - restartConnection(ex);
1.1510 - return getGroup(name);
1.1511 - }
1.1512 - finally
1.1513 - {
1.1514 - if(rs != null)
1.1515 - {
1.1516 - try
1.1517 - {
1.1518 - rs.close();
1.1519 - }
1.1520 - catch(SQLException ex)
1.1521 - {
1.1522 - ex.printStackTrace();
1.1523 - }
1.1524 - }
1.1525 - }
1.1526 - }
1.1527 + rs = this.pstmtGetArticle1.executeQuery();
1.1528
1.1529 - @Override
1.1530 - public List<String> getListsForGroup(String group)
1.1531 - throws StorageBackendException
1.1532 - {
1.1533 - ResultSet rs = null;
1.1534 - List<String> lists = new ArrayList<String>();
1.1535 + if (rs.next()) {
1.1536 + byte[] body = rs.getBytes("body");
1.1537 + String headers = getArticleHeaders(rs.getInt("article_id"));
1.1538 + return new Article(headers, body);
1.1539 + } else {
1.1540 + return null;
1.1541 + }
1.1542 + } catch (SQLException ex) {
1.1543 + restartConnection(ex);
1.1544 + return getArticle(articleIndex, gid);
1.1545 + } finally {
1.1546 + if (rs != null) {
1.1547 + try {
1.1548 + rs.close();
1.1549 + } catch (SQLException ex) {
1.1550 + ex.printStackTrace();
1.1551 + }
1.1552 + restarts = 0;
1.1553 + }
1.1554 + }
1.1555 + }
1.1556
1.1557 - try
1.1558 - {
1.1559 - this.pstmtGetListForGroup.setString(1, group);
1.1560 - rs = this.pstmtGetListForGroup.executeQuery();
1.1561 + /**
1.1562 + * Searches for fitting header values using the given regular expression.
1.1563 + * @param group
1.1564 + * @param start
1.1565 + * @param end
1.1566 + * @param headerKey
1.1567 + * @param pattern
1.1568 + * @return
1.1569 + * @throws StorageBackendException
1.1570 + */
1.1571 + @Override
1.1572 + public List<Pair<Long, String>> getArticleHeaders(Channel group, long start,
1.1573 + long end, String headerKey, String patStr)
1.1574 + throws StorageBackendException, PatternSyntaxException
1.1575 + {
1.1576 + ResultSet rs = null;
1.1577 + List<Pair<Long, String>> heads = new ArrayList<Pair<Long, String>>();
1.1578
1.1579 - while(rs.next())
1.1580 - {
1.1581 - lists.add(rs.getString(1));
1.1582 - }
1.1583 - return lists;
1.1584 - }
1.1585 - catch(SQLException ex)
1.1586 - {
1.1587 - restartConnection(ex);
1.1588 - return getListsForGroup(group);
1.1589 - }
1.1590 - finally
1.1591 - {
1.1592 - if(rs != null)
1.1593 - {
1.1594 - try
1.1595 - {
1.1596 - rs.close();
1.1597 - }
1.1598 - catch(SQLException ex)
1.1599 - {
1.1600 - ex.printStackTrace();
1.1601 - }
1.1602 - }
1.1603 - }
1.1604 - }
1.1605 -
1.1606 - private int getMaxArticleIndex(long groupID)
1.1607 - throws StorageBackendException
1.1608 - {
1.1609 - ResultSet rs = null;
1.1610 + try {
1.1611 + this.pstmtGetArticleHeaders1.setString(1, group.getName());
1.1612 + this.pstmtGetArticleHeaders1.setString(2, headerKey);
1.1613 + this.pstmtGetArticleHeaders1.setLong(3, start);
1.1614
1.1615 - try
1.1616 - {
1.1617 - this.pstmtGetMaxArticleIndex.setLong(1, groupID);
1.1618 - rs = this.pstmtGetMaxArticleIndex.executeQuery();
1.1619 + rs = this.pstmtGetArticleHeaders1.executeQuery();
1.1620
1.1621 - int maxIndex = 0;
1.1622 - if (rs.next())
1.1623 - {
1.1624 - maxIndex = rs.getInt(1);
1.1625 - }
1.1626 + // Convert the "NNTP" regex to Java regex
1.1627 + patStr = patStr.replace("*", ".*");
1.1628 + Pattern pattern = Pattern.compile(patStr);
1.1629
1.1630 - return maxIndex;
1.1631 - }
1.1632 - catch(SQLException ex)
1.1633 - {
1.1634 - restartConnection(ex);
1.1635 - return getMaxArticleIndex(groupID);
1.1636 - }
1.1637 - finally
1.1638 - {
1.1639 - if(rs != null)
1.1640 - {
1.1641 - try
1.1642 - {
1.1643 - rs.close();
1.1644 - }
1.1645 - catch(SQLException ex)
1.1646 - {
1.1647 - ex.printStackTrace();
1.1648 - }
1.1649 - }
1.1650 - }
1.1651 - }
1.1652 -
1.1653 - private int getMaxArticleID()
1.1654 - throws StorageBackendException
1.1655 - {
1.1656 - ResultSet rs = null;
1.1657 + while (rs.next()) {
1.1658 + Long articleIndex = rs.getLong(1);
1.1659 + if (end < 0 || articleIndex <= end) // Match start is done via SQL
1.1660 + {
1.1661 + String headerValue = rs.getString(2);
1.1662 + Matcher matcher = pattern.matcher(headerValue);
1.1663 + if (matcher.matches()) {
1.1664 + heads.add(new Pair<Long, String>(articleIndex, headerValue));
1.1665 + }
1.1666 + }
1.1667 + }
1.1668 + } catch (SQLException ex) {
1.1669 + restartConnection(ex);
1.1670 + return getArticleHeaders(group, start, end, headerKey, patStr);
1.1671 + } finally {
1.1672 + if (rs != null) {
1.1673 + try {
1.1674 + rs.close();
1.1675 + } catch (SQLException ex) {
1.1676 + ex.printStackTrace();
1.1677 + }
1.1678 + }
1.1679 + }
1.1680
1.1681 - try
1.1682 - {
1.1683 - rs = this.pstmtGetMaxArticleID.executeQuery();
1.1684 + return heads;
1.1685 + }
1.1686
1.1687 - int maxIndex = 0;
1.1688 - if (rs.next())
1.1689 - {
1.1690 - maxIndex = rs.getInt(1);
1.1691 - }
1.1692 + private String getArticleHeaders(long articleID)
1.1693 + throws StorageBackendException
1.1694 + {
1.1695 + ResultSet rs = null;
1.1696
1.1697 - return maxIndex;
1.1698 - }
1.1699 - catch(SQLException ex)
1.1700 - {
1.1701 - restartConnection(ex);
1.1702 - return getMaxArticleID();
1.1703 - }
1.1704 - finally
1.1705 - {
1.1706 - if(rs != null)
1.1707 - {
1.1708 - try
1.1709 - {
1.1710 - rs.close();
1.1711 - }
1.1712 - catch(SQLException ex)
1.1713 - {
1.1714 - ex.printStackTrace();
1.1715 - }
1.1716 - }
1.1717 - }
1.1718 - }
1.1719 + try {
1.1720 + this.pstmtGetArticleHeaders0.setLong(1, articleID);
1.1721 + rs = this.pstmtGetArticleHeaders0.executeQuery();
1.1722
1.1723 - @Override
1.1724 - public int getLastArticleNumber(Group group)
1.1725 - throws StorageBackendException
1.1726 - {
1.1727 - ResultSet rs = null;
1.1728 + StringBuilder buf = new StringBuilder();
1.1729 + if (rs.next()) {
1.1730 + for (;;) {
1.1731 + buf.append(rs.getString(1)); // key
1.1732 + buf.append(": ");
1.1733 + String foldedValue = MimeUtility.fold(0, rs.getString(2));
1.1734 + buf.append(foldedValue); // value
1.1735 + if (rs.next()) {
1.1736 + buf.append("\r\n");
1.1737 + } else {
1.1738 + break;
1.1739 + }
1.1740 + }
1.1741 + }
1.1742
1.1743 - try
1.1744 - {
1.1745 - this.pstmtGetLastArticleNumber.setLong(1, group.getInternalID());
1.1746 - rs = this.pstmtGetLastArticleNumber.executeQuery();
1.1747 - if (rs.next())
1.1748 - {
1.1749 - return rs.getInt(1);
1.1750 - }
1.1751 - else
1.1752 - {
1.1753 - return 0;
1.1754 - }
1.1755 - }
1.1756 - catch(SQLException ex)
1.1757 - {
1.1758 - restartConnection(ex);
1.1759 - return getLastArticleNumber(group);
1.1760 - }
1.1761 - finally
1.1762 - {
1.1763 - if(rs != null)
1.1764 - {
1.1765 - try
1.1766 - {
1.1767 - rs.close();
1.1768 - }
1.1769 - catch(SQLException ex)
1.1770 - {
1.1771 - ex.printStackTrace();
1.1772 - }
1.1773 - }
1.1774 - }
1.1775 - }
1.1776 + return buf.toString();
1.1777 + } catch (SQLException ex) {
1.1778 + restartConnection(ex);
1.1779 + return getArticleHeaders(articleID);
1.1780 + } finally {
1.1781 + if (rs != null) {
1.1782 + try {
1.1783 + rs.close();
1.1784 + } catch (SQLException ex) {
1.1785 + ex.printStackTrace();
1.1786 + }
1.1787 + }
1.1788 + }
1.1789 + }
1.1790
1.1791 - @Override
1.1792 - public int getFirstArticleNumber(Group group)
1.1793 - throws StorageBackendException
1.1794 - {
1.1795 - ResultSet rs = null;
1.1796 - try
1.1797 - {
1.1798 - this.pstmtGetFirstArticleNumber.setLong(1, group.getInternalID());
1.1799 - rs = this.pstmtGetFirstArticleNumber.executeQuery();
1.1800 - if(rs.next())
1.1801 - {
1.1802 - return rs.getInt(1);
1.1803 - }
1.1804 - else
1.1805 - {
1.1806 - return 0;
1.1807 - }
1.1808 - }
1.1809 - catch(SQLException ex)
1.1810 - {
1.1811 - restartConnection(ex);
1.1812 - return getFirstArticleNumber(group);
1.1813 - }
1.1814 - finally
1.1815 - {
1.1816 - if(rs != null)
1.1817 - {
1.1818 - try
1.1819 - {
1.1820 - rs.close();
1.1821 - }
1.1822 - catch(SQLException ex)
1.1823 - {
1.1824 - ex.printStackTrace();
1.1825 - }
1.1826 - }
1.1827 - }
1.1828 - }
1.1829 -
1.1830 - /**
1.1831 - * Returns a group name identified by the given id.
1.1832 - * @param id
1.1833 - * @return
1.1834 - * @throws StorageBackendException
1.1835 - */
1.1836 - public String getGroup(int id)
1.1837 - throws StorageBackendException
1.1838 - {
1.1839 - ResultSet rs = null;
1.1840 + @Override
1.1841 + public long getArticleIndex(Article article, Group group)
1.1842 + throws StorageBackendException
1.1843 + {
1.1844 + ResultSet rs = null;
1.1845
1.1846 - try
1.1847 - {
1.1848 - this.pstmtGetGroup1.setInt(1, id);
1.1849 - rs = this.pstmtGetGroup1.executeQuery();
1.1850 + try {
1.1851 + this.pstmtGetArticleIndex.setString(1, article.getMessageID());
1.1852 + this.pstmtGetArticleIndex.setLong(2, group.getInternalID());
1.1853
1.1854 - if (rs.next())
1.1855 - {
1.1856 - return rs.getString(1);
1.1857 - }
1.1858 - else
1.1859 - {
1.1860 - return null;
1.1861 - }
1.1862 - }
1.1863 - catch(SQLException ex)
1.1864 - {
1.1865 - restartConnection(ex);
1.1866 - return getGroup(id);
1.1867 - }
1.1868 - finally
1.1869 - {
1.1870 - if(rs != null)
1.1871 - {
1.1872 - try
1.1873 - {
1.1874 - rs.close();
1.1875 - }
1.1876 - catch(SQLException ex)
1.1877 - {
1.1878 - ex.printStackTrace();
1.1879 - }
1.1880 - }
1.1881 - }
1.1882 - }
1.1883 + rs = this.pstmtGetArticleIndex.executeQuery();
1.1884 + if (rs.next()) {
1.1885 + return rs.getLong(1);
1.1886 + } else {
1.1887 + return -1;
1.1888 + }
1.1889 + } catch (SQLException ex) {
1.1890 + restartConnection(ex);
1.1891 + return getArticleIndex(article, group);
1.1892 + } finally {
1.1893 + if (rs != null) {
1.1894 + try {
1.1895 + rs.close();
1.1896 + } catch (SQLException ex) {
1.1897 + ex.printStackTrace();
1.1898 + }
1.1899 + }
1.1900 + }
1.1901 + }
1.1902
1.1903 - @Override
1.1904 - public double getEventsPerHour(int key, long gid)
1.1905 - throws StorageBackendException
1.1906 - {
1.1907 - String gidquery = "";
1.1908 - if(gid >= 0)
1.1909 - {
1.1910 - gidquery = " AND group_id = " + gid;
1.1911 - }
1.1912 -
1.1913 - Statement stmt = null;
1.1914 - ResultSet rs = null;
1.1915 -
1.1916 - try
1.1917 - {
1.1918 - stmt = this.conn.createStatement();
1.1919 - rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))" +
1.1920 - " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
1.1921 -
1.1922 - if(rs.next())
1.1923 - {
1.1924 - restarts = 0; // reset error count
1.1925 - return rs.getDouble(1);
1.1926 - }
1.1927 - else
1.1928 - {
1.1929 - return Double.NaN;
1.1930 - }
1.1931 - }
1.1932 - catch(SQLException ex)
1.1933 - {
1.1934 - restartConnection(ex);
1.1935 - return getEventsPerHour(key, gid);
1.1936 - }
1.1937 - finally
1.1938 - {
1.1939 - try
1.1940 - {
1.1941 - if(stmt != null)
1.1942 - {
1.1943 - stmt.close(); // Implicitely closes the result sets
1.1944 - }
1.1945 - }
1.1946 - catch(SQLException ex)
1.1947 - {
1.1948 - ex.printStackTrace();
1.1949 - }
1.1950 - }
1.1951 - }
1.1952 + /**
1.1953 + * Returns a list of Long/Article Pairs.
1.1954 + * @throws java.sql.SQLException
1.1955 + */
1.1956 + @Override
1.1957 + public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first,
1.1958 + long last)
1.1959 + throws StorageBackendException
1.1960 + {
1.1961 + ResultSet rs = null;
1.1962
1.1963 - @Override
1.1964 - public String getOldestArticle()
1.1965 - throws StorageBackendException
1.1966 - {
1.1967 - ResultSet rs = null;
1.1968 + try {
1.1969 + this.pstmtGetArticleHeads.setLong(1, group.getInternalID());
1.1970 + this.pstmtGetArticleHeads.setLong(2, first);
1.1971 + this.pstmtGetArticleHeads.setLong(3, last);
1.1972 + rs = pstmtGetArticleHeads.executeQuery();
1.1973
1.1974 - try
1.1975 - {
1.1976 - rs = this.pstmtGetOldestArticle.executeQuery();
1.1977 - if(rs.next())
1.1978 - {
1.1979 - return rs.getString(1);
1.1980 - }
1.1981 - else
1.1982 - {
1.1983 - return null;
1.1984 - }
1.1985 - }
1.1986 - catch(SQLException ex)
1.1987 - {
1.1988 - restartConnection(ex);
1.1989 - return getOldestArticle();
1.1990 - }
1.1991 - finally
1.1992 - {
1.1993 - if(rs != null)
1.1994 - {
1.1995 - try
1.1996 - {
1.1997 - rs.close();
1.1998 - }
1.1999 - catch(SQLException ex)
1.2000 - {
1.2001 - ex.printStackTrace();
1.2002 - }
1.2003 - }
1.2004 - }
1.2005 - }
1.2006 + List<Pair<Long, ArticleHead>> articles = new ArrayList<Pair<Long, ArticleHead>>();
1.2007
1.2008 - @Override
1.2009 - public int getPostingsCount(String groupname)
1.2010 - throws StorageBackendException
1.2011 - {
1.2012 - ResultSet rs = null;
1.2013 -
1.2014 - try
1.2015 - {
1.2016 - this.pstmtGetPostingsCount.setString(1, groupname);
1.2017 - rs = this.pstmtGetPostingsCount.executeQuery();
1.2018 - if(rs.next())
1.2019 - {
1.2020 - return rs.getInt(1);
1.2021 - }
1.2022 - else
1.2023 - {
1.2024 - Log.get().warning("Count on postings return nothing!");
1.2025 - return 0;
1.2026 - }
1.2027 - }
1.2028 - catch(SQLException ex)
1.2029 - {
1.2030 - restartConnection(ex);
1.2031 - return getPostingsCount(groupname);
1.2032 - }
1.2033 - finally
1.2034 - {
1.2035 - if(rs != null)
1.2036 - {
1.2037 - try
1.2038 - {
1.2039 - rs.close();
1.2040 - }
1.2041 - catch(SQLException ex)
1.2042 - {
1.2043 - ex.printStackTrace();
1.2044 - }
1.2045 - }
1.2046 - }
1.2047 - }
1.2048 + while (rs.next()) {
1.2049 + long aid = rs.getLong("article_id");
1.2050 + long aidx = rs.getLong("article_index");
1.2051 + String headers = getArticleHeaders(aid);
1.2052 + articles.add(new Pair<Long, ArticleHead>(aidx,
1.2053 + new ArticleHead(headers)));
1.2054 + }
1.2055
1.2056 - @Override
1.2057 - public List<Subscription> getSubscriptions(int feedtype)
1.2058 - throws StorageBackendException
1.2059 - {
1.2060 - ResultSet rs = null;
1.2061 -
1.2062 - try
1.2063 - {
1.2064 - List<Subscription> subs = new ArrayList<Subscription>();
1.2065 - this.pstmtGetSubscriptions.setInt(1, feedtype);
1.2066 - rs = this.pstmtGetSubscriptions.executeQuery();
1.2067 -
1.2068 - while(rs.next())
1.2069 - {
1.2070 - String host = rs.getString("host");
1.2071 - String group = rs.getString("name");
1.2072 - int port = rs.getInt("port");
1.2073 - subs.add(new Subscription(host, port, feedtype, group));
1.2074 - }
1.2075 -
1.2076 - return subs;
1.2077 - }
1.2078 - catch(SQLException ex)
1.2079 - {
1.2080 - restartConnection(ex);
1.2081 - return getSubscriptions(feedtype);
1.2082 - }
1.2083 - finally
1.2084 - {
1.2085 - if(rs != null)
1.2086 - {
1.2087 - try
1.2088 - {
1.2089 - rs.close();
1.2090 - }
1.2091 - catch(SQLException ex)
1.2092 - {
1.2093 - ex.printStackTrace();
1.2094 - }
1.2095 - }
1.2096 - }
1.2097 - }
1.2098 + return articles;
1.2099 + } catch (SQLException ex) {
1.2100 + restartConnection(ex);
1.2101 + return getArticleHeads(group, first, last);
1.2102 + } finally {
1.2103 + if (rs != null) {
1.2104 + try {
1.2105 + rs.close();
1.2106 + } catch (SQLException ex) {
1.2107 + ex.printStackTrace();
1.2108 + }
1.2109 + }
1.2110 + }
1.2111 + }
1.2112
1.2113 - /**
1.2114 - * Checks if there is an article with the given messageid in the JDBCDatabase.
1.2115 - * @param name
1.2116 - * @return
1.2117 - * @throws StorageBackendException
1.2118 - */
1.2119 - @Override
1.2120 - public boolean isArticleExisting(String messageID)
1.2121 - throws StorageBackendException
1.2122 - {
1.2123 - ResultSet rs = null;
1.2124 -
1.2125 - try
1.2126 - {
1.2127 - this.pstmtIsArticleExisting.setString(1, messageID);
1.2128 - rs = this.pstmtIsArticleExisting.executeQuery();
1.2129 - return rs.next() && rs.getInt(1) == 1;
1.2130 - }
1.2131 - catch(SQLException ex)
1.2132 - {
1.2133 - restartConnection(ex);
1.2134 - return isArticleExisting(messageID);
1.2135 - }
1.2136 - finally
1.2137 - {
1.2138 - if(rs != null)
1.2139 - {
1.2140 - try
1.2141 - {
1.2142 - rs.close();
1.2143 - }
1.2144 - catch(SQLException ex)
1.2145 - {
1.2146 - ex.printStackTrace();
1.2147 - }
1.2148 - }
1.2149 - }
1.2150 - }
1.2151 -
1.2152 - /**
1.2153 - * Checks if there is a group with the given name in the JDBCDatabase.
1.2154 - * @param name
1.2155 - * @return
1.2156 - * @throws StorageBackendException
1.2157 - */
1.2158 - @Override
1.2159 - public boolean isGroupExisting(String name)
1.2160 - throws StorageBackendException
1.2161 - {
1.2162 - ResultSet rs = null;
1.2163 -
1.2164 - try
1.2165 - {
1.2166 - this.pstmtIsGroupExisting.setString(1, name);
1.2167 - rs = this.pstmtIsGroupExisting.executeQuery();
1.2168 - return rs.next();
1.2169 - }
1.2170 - catch(SQLException ex)
1.2171 - {
1.2172 - restartConnection(ex);
1.2173 - return isGroupExisting(name);
1.2174 - }
1.2175 - finally
1.2176 - {
1.2177 - if(rs != null)
1.2178 - {
1.2179 - try
1.2180 - {
1.2181 - rs.close();
1.2182 - }
1.2183 - catch(SQLException ex)
1.2184 - {
1.2185 - ex.printStackTrace();
1.2186 - }
1.2187 - }
1.2188 - }
1.2189 - }
1.2190 + @Override
1.2191 + public List<Long> getArticleNumbers(long gid)
1.2192 + throws StorageBackendException
1.2193 + {
1.2194 + ResultSet rs = null;
1.2195 + try {
1.2196 + List<Long> ids = new ArrayList<Long>();
1.2197 + this.pstmtGetArticleIDs.setLong(1, gid);
1.2198 + rs = this.pstmtGetArticleIDs.executeQuery();
1.2199 + while (rs.next()) {
1.2200 + ids.add(rs.getLong(1));
1.2201 + }
1.2202 + return ids;
1.2203 + } catch (SQLException ex) {
1.2204 + restartConnection(ex);
1.2205 + return getArticleNumbers(gid);
1.2206 + } finally {
1.2207 + if (rs != null) {
1.2208 + try {
1.2209 + rs.close();
1.2210 + restarts = 0; // Clear the restart count after successful request
1.2211 + } catch (SQLException ex) {
1.2212 + ex.printStackTrace();
1.2213 + }
1.2214 + }
1.2215 + }
1.2216 + }
1.2217
1.2218 - @Override
1.2219 - public void setConfigValue(String key, String value)
1.2220 - throws StorageBackendException
1.2221 - {
1.2222 - try
1.2223 - {
1.2224 - conn.setAutoCommit(false);
1.2225 - this.pstmtSetConfigValue0.setString(1, key);
1.2226 - this.pstmtSetConfigValue0.execute();
1.2227 - this.pstmtSetConfigValue1.setString(1, key);
1.2228 - this.pstmtSetConfigValue1.setString(2, value);
1.2229 - this.pstmtSetConfigValue1.execute();
1.2230 - conn.commit();
1.2231 - conn.setAutoCommit(true);
1.2232 - }
1.2233 - catch(SQLException ex)
1.2234 - {
1.2235 - restartConnection(ex);
1.2236 - setConfigValue(key, value);
1.2237 - }
1.2238 - }
1.2239 -
1.2240 - /**
1.2241 - * Closes the JDBCDatabase connection.
1.2242 - */
1.2243 - public void shutdown()
1.2244 - throws StorageBackendException
1.2245 - {
1.2246 - try
1.2247 - {
1.2248 - if(this.conn != null)
1.2249 - {
1.2250 - this.conn.close();
1.2251 - }
1.2252 - }
1.2253 - catch(SQLException ex)
1.2254 - {
1.2255 - throw new StorageBackendException(ex);
1.2256 - }
1.2257 - }
1.2258 + @Override
1.2259 + public String getConfigValue(String key)
1.2260 + throws StorageBackendException
1.2261 + {
1.2262 + ResultSet rs = null;
1.2263 + try {
1.2264 + this.pstmtGetConfigValue.setString(1, key);
1.2265
1.2266 - @Override
1.2267 - public void purgeGroup(Group group)
1.2268 - throws StorageBackendException
1.2269 - {
1.2270 - try
1.2271 - {
1.2272 - this.pstmtPurgeGroup0.setLong(1, group.getInternalID());
1.2273 - this.pstmtPurgeGroup0.executeUpdate();
1.2274 + rs = this.pstmtGetConfigValue.executeQuery();
1.2275 + if (rs.next()) {
1.2276 + return rs.getString(1); // First data on index 1 not 0
1.2277 + } else {
1.2278 + return null;
1.2279 + }
1.2280 + } catch (SQLException ex) {
1.2281 + restartConnection(ex);
1.2282 + return getConfigValue(key);
1.2283 + } finally {
1.2284 + if (rs != null) {
1.2285 + try {
1.2286 + rs.close();
1.2287 + } catch (SQLException ex) {
1.2288 + ex.printStackTrace();
1.2289 + }
1.2290 + restarts = 0; // Clear the restart count after successful request
1.2291 + }
1.2292 + }
1.2293 + }
1.2294
1.2295 - this.pstmtPurgeGroup1.setLong(1, group.getInternalID());
1.2296 - this.pstmtPurgeGroup1.executeUpdate();
1.2297 - }
1.2298 - catch(SQLException ex)
1.2299 - {
1.2300 - restartConnection(ex);
1.2301 - purgeGroup(group);
1.2302 - }
1.2303 - }
1.2304 -
1.2305 - private void restartConnection(SQLException cause)
1.2306 - throws StorageBackendException
1.2307 - {
1.2308 - restarts++;
1.2309 - Log.get().severe(Thread.currentThread()
1.2310 - + ": Database connection was closed (restart " + restarts + ").");
1.2311 -
1.2312 - if(restarts >= MAX_RESTARTS)
1.2313 - {
1.2314 - // Delete the current, probably broken JDBCDatabase instance.
1.2315 - // So no one can use the instance any more.
1.2316 - JDBCDatabaseProvider.instances.remove(Thread.currentThread());
1.2317 -
1.2318 - // Throw the exception upwards
1.2319 - throw new StorageBackendException(cause);
1.2320 - }
1.2321 -
1.2322 - try
1.2323 - {
1.2324 - Thread.sleep(1500L * restarts);
1.2325 - }
1.2326 - catch(InterruptedException ex)
1.2327 - {
1.2328 - Log.get().warning("Interrupted: " + ex.getMessage());
1.2329 - }
1.2330 -
1.2331 - // Try to properly close the old database connection
1.2332 - try
1.2333 - {
1.2334 - if(this.conn != null)
1.2335 - {
1.2336 - this.conn.close();
1.2337 - }
1.2338 - }
1.2339 - catch(SQLException ex)
1.2340 - {
1.2341 - Log.get().warning(ex.getMessage());
1.2342 - }
1.2343 -
1.2344 - try
1.2345 - {
1.2346 - // Try to reinitialize database connection
1.2347 - arise();
1.2348 - }
1.2349 - catch(SQLException ex)
1.2350 - {
1.2351 - Log.get().warning(ex.getMessage());
1.2352 - restartConnection(ex);
1.2353 - }
1.2354 - }
1.2355 + @Override
1.2356 + public int getEventsCount(int type, long start, long end, Channel channel)
1.2357 + throws StorageBackendException
1.2358 + {
1.2359 + ResultSet rs = null;
1.2360
1.2361 - @Override
1.2362 - public boolean update(Article article)
1.2363 - throws StorageBackendException
1.2364 - {
1.2365 - // DELETE FROM headers WHERE article_id = ?
1.2366 + try {
1.2367 + if (channel == null) {
1.2368 + this.pstmtGetEventsCount0.setInt(1, type);
1.2369 + this.pstmtGetEventsCount0.setLong(2, start);
1.2370 + this.pstmtGetEventsCount0.setLong(3, end);
1.2371 + rs = this.pstmtGetEventsCount0.executeQuery();
1.2372 + } else {
1.2373 + this.pstmtGetEventsCount1.setInt(1, type);
1.2374 + this.pstmtGetEventsCount1.setLong(2, start);
1.2375 + this.pstmtGetEventsCount1.setLong(3, end);
1.2376 + this.pstmtGetEventsCount1.setLong(4, channel.getInternalID());
1.2377 + rs = this.pstmtGetEventsCount1.executeQuery();
1.2378 + }
1.2379
1.2380 - // INSERT INTO headers ...
1.2381 + if (rs.next()) {
1.2382 + return rs.getInt(1);
1.2383 + } else {
1.2384 + return -1;
1.2385 + }
1.2386 + } catch (SQLException ex) {
1.2387 + restartConnection(ex);
1.2388 + return getEventsCount(type, start, end, channel);
1.2389 + } finally {
1.2390 + if (rs != null) {
1.2391 + try {
1.2392 + rs.close();
1.2393 + } catch (SQLException ex) {
1.2394 + ex.printStackTrace();
1.2395 + }
1.2396 + }
1.2397 + }
1.2398 + }
1.2399
1.2400 - // SELECT * FROM postings WHERE article_id = ? AND group_id = ?
1.2401 - return false;
1.2402 - }
1.2403 + /**
1.2404 + * Reads all Groups from the JDBCDatabase.
1.2405 + * @return
1.2406 + * @throws StorageBackendException
1.2407 + */
1.2408 + @Override
1.2409 + public List<Channel> getGroups()
1.2410 + throws StorageBackendException
1.2411 + {
1.2412 + ResultSet rs;
1.2413 + List<Channel> buffer = new ArrayList<Channel>();
1.2414 + Statement stmt = null;
1.2415
1.2416 - /**
1.2417 - * Writes the flags and the name of the given group to the database.
1.2418 - * @param group
1.2419 - * @throws StorageBackendException
1.2420 - */
1.2421 - @Override
1.2422 - public boolean update(Group group)
1.2423 - throws StorageBackendException
1.2424 - {
1.2425 - try
1.2426 - {
1.2427 - this.pstmtUpdateGroup.setInt(1, group.getFlags());
1.2428 - this.pstmtUpdateGroup.setString(2, group.getName());
1.2429 - this.pstmtUpdateGroup.setLong(3, group.getInternalID());
1.2430 - int rs = this.pstmtUpdateGroup.executeUpdate();
1.2431 - return rs == 1;
1.2432 - }
1.2433 - catch(SQLException ex)
1.2434 - {
1.2435 - restartConnection(ex);
1.2436 - return update(group);
1.2437 - }
1.2438 - }
1.2439 + try {
1.2440 + stmt = conn.createStatement();
1.2441 + rs = stmt.executeQuery("SELECT * FROM groups ORDER BY name");
1.2442
1.2443 + while (rs.next()) {
1.2444 + String name = rs.getString("name");
1.2445 + long id = rs.getLong("group_id");
1.2446 + int flags = rs.getInt("flags");
1.2447 +
1.2448 + Group group = new Group(name, id, flags);
1.2449 + buffer.add(group);
1.2450 + }
1.2451 +
1.2452 + return buffer;
1.2453 + } catch (SQLException ex) {
1.2454 + restartConnection(ex);
1.2455 + return getGroups();
1.2456 + } finally {
1.2457 + if (stmt != null) {
1.2458 + try {
1.2459 + stmt.close(); // Implicitely closes ResultSets
1.2460 + } catch (SQLException ex) {
1.2461 + ex.printStackTrace();
1.2462 + }
1.2463 + }
1.2464 + }
1.2465 + }
1.2466 +
1.2467 + @Override
1.2468 + public List<String> getGroupsForList(String listAddress)
1.2469 + throws StorageBackendException
1.2470 + {
1.2471 + ResultSet rs = null;
1.2472 +
1.2473 + try {
1.2474 + this.pstmtGetGroupForList.setString(1, listAddress);
1.2475 +
1.2476 + rs = this.pstmtGetGroupForList.executeQuery();
1.2477 + List<String> groups = new ArrayList<String>();
1.2478 + while (rs.next()) {
1.2479 + String group = rs.getString(1);
1.2480 + groups.add(group);
1.2481 + }
1.2482 + return groups;
1.2483 + } catch (SQLException ex) {
1.2484 + restartConnection(ex);
1.2485 + return getGroupsForList(listAddress);
1.2486 + } finally {
1.2487 + if (rs != null) {
1.2488 + try {
1.2489 + rs.close();
1.2490 + } catch (SQLException ex) {
1.2491 + ex.printStackTrace();
1.2492 + }
1.2493 + }
1.2494 + }
1.2495 + }
1.2496 +
1.2497 + /**
1.2498 + * Returns the Group that is identified by the name.
1.2499 + * @param name
1.2500 + * @return
1.2501 + * @throws StorageBackendException
1.2502 + */
1.2503 + @Override
1.2504 + public Group getGroup(String name)
1.2505 + throws StorageBackendException
1.2506 + {
1.2507 + ResultSet rs = null;
1.2508 +
1.2509 + try {
1.2510 + this.pstmtGetGroup0.setString(1, name);
1.2511 + rs = this.pstmtGetGroup0.executeQuery();
1.2512 +
1.2513 + if (!rs.next()) {
1.2514 + return null;
1.2515 + } else {
1.2516 + long id = rs.getLong("group_id");
1.2517 + int flags = rs.getInt("flags");
1.2518 + return new Group(name, id, flags);
1.2519 + }
1.2520 + } catch (SQLException ex) {
1.2521 + restartConnection(ex);
1.2522 + return getGroup(name);
1.2523 + } finally {
1.2524 + if (rs != null) {
1.2525 + try {
1.2526 + rs.close();
1.2527 + } catch (SQLException ex) {
1.2528 + ex.printStackTrace();
1.2529 + }
1.2530 + }
1.2531 + }
1.2532 + }
1.2533 +
1.2534 + @Override
1.2535 + public List<String> getListsForGroup(String group)
1.2536 + throws StorageBackendException
1.2537 + {
1.2538 + ResultSet rs = null;
1.2539 + List<String> lists = new ArrayList<String>();
1.2540 +
1.2541 + try {
1.2542 + this.pstmtGetListForGroup.setString(1, group);
1.2543 + rs = this.pstmtGetListForGroup.executeQuery();
1.2544 +
1.2545 + while (rs.next()) {
1.2546 + lists.add(rs.getString(1));
1.2547 + }
1.2548 + return lists;
1.2549 + } catch (SQLException ex) {
1.2550 + restartConnection(ex);
1.2551 + return getListsForGroup(group);
1.2552 + } finally {
1.2553 + if (rs != null) {
1.2554 + try {
1.2555 + rs.close();
1.2556 + } catch (SQLException ex) {
1.2557 + ex.printStackTrace();
1.2558 + }
1.2559 + }
1.2560 + }
1.2561 + }
1.2562 +
1.2563 + private int getMaxArticleIndex(long groupID)
1.2564 + throws StorageBackendException
1.2565 + {
1.2566 + ResultSet rs = null;
1.2567 +
1.2568 + try {
1.2569 + this.pstmtGetMaxArticleIndex.setLong(1, groupID);
1.2570 + rs = this.pstmtGetMaxArticleIndex.executeQuery();
1.2571 +
1.2572 + int maxIndex = 0;
1.2573 + if (rs.next()) {
1.2574 + maxIndex = rs.getInt(1);
1.2575 + }
1.2576 +
1.2577 + return maxIndex;
1.2578 + } catch (SQLException ex) {
1.2579 + restartConnection(ex);
1.2580 + return getMaxArticleIndex(groupID);
1.2581 + } finally {
1.2582 + if (rs != null) {
1.2583 + try {
1.2584 + rs.close();
1.2585 + } catch (SQLException ex) {
1.2586 + ex.printStackTrace();
1.2587 + }
1.2588 + }
1.2589 + }
1.2590 + }
1.2591 +
1.2592 + private int getMaxArticleID()
1.2593 + throws StorageBackendException
1.2594 + {
1.2595 + ResultSet rs = null;
1.2596 +
1.2597 + try {
1.2598 + rs = this.pstmtGetMaxArticleID.executeQuery();
1.2599 +
1.2600 + int maxIndex = 0;
1.2601 + if (rs.next()) {
1.2602 + maxIndex = rs.getInt(1);
1.2603 + }
1.2604 +
1.2605 + return maxIndex;
1.2606 + } catch (SQLException ex) {
1.2607 + restartConnection(ex);
1.2608 + return getMaxArticleID();
1.2609 + } finally {
1.2610 + if (rs != null) {
1.2611 + try {
1.2612 + rs.close();
1.2613 + } catch (SQLException ex) {
1.2614 + ex.printStackTrace();
1.2615 + }
1.2616 + }
1.2617 + }
1.2618 + }
1.2619 +
1.2620 + @Override
1.2621 + public int getLastArticleNumber(Group group)
1.2622 + throws StorageBackendException
1.2623 + {
1.2624 + ResultSet rs = null;
1.2625 +
1.2626 + try {
1.2627 + this.pstmtGetLastArticleNumber.setLong(1, group.getInternalID());
1.2628 + rs = this.pstmtGetLastArticleNumber.executeQuery();
1.2629 + if (rs.next()) {
1.2630 + return rs.getInt(1);
1.2631 + } else {
1.2632 + return 0;
1.2633 + }
1.2634 + } catch (SQLException ex) {
1.2635 + restartConnection(ex);
1.2636 + return getLastArticleNumber(group);
1.2637 + } finally {
1.2638 + if (rs != null) {
1.2639 + try {
1.2640 + rs.close();
1.2641 + } catch (SQLException ex) {
1.2642 + ex.printStackTrace();
1.2643 + }
1.2644 + }
1.2645 + }
1.2646 + }
1.2647 +
1.2648 + @Override
1.2649 + public int getFirstArticleNumber(Group group)
1.2650 + throws StorageBackendException
1.2651 + {
1.2652 + ResultSet rs = null;
1.2653 + try {
1.2654 + this.pstmtGetFirstArticleNumber.setLong(1, group.getInternalID());
1.2655 + rs = this.pstmtGetFirstArticleNumber.executeQuery();
1.2656 + if (rs.next()) {
1.2657 + return rs.getInt(1);
1.2658 + } else {
1.2659 + return 0;
1.2660 + }
1.2661 + } catch (SQLException ex) {
1.2662 + restartConnection(ex);
1.2663 + return getFirstArticleNumber(group);
1.2664 + } finally {
1.2665 + if (rs != null) {
1.2666 + try {
1.2667 + rs.close();
1.2668 + } catch (SQLException ex) {
1.2669 + ex.printStackTrace();
1.2670 + }
1.2671 + }
1.2672 + }
1.2673 + }
1.2674 +
1.2675 + /**
1.2676 + * Returns a group name identified by the given id.
1.2677 + * @param id
1.2678 + * @return
1.2679 + * @throws StorageBackendException
1.2680 + */
1.2681 + public String getGroup(int id)
1.2682 + throws StorageBackendException
1.2683 + {
1.2684 + ResultSet rs = null;
1.2685 +
1.2686 + try {
1.2687 + this.pstmtGetGroup1.setInt(1, id);
1.2688 + rs = this.pstmtGetGroup1.executeQuery();
1.2689 +
1.2690 + if (rs.next()) {
1.2691 + return rs.getString(1);
1.2692 + } else {
1.2693 + return null;
1.2694 + }
1.2695 + } catch (SQLException ex) {
1.2696 + restartConnection(ex);
1.2697 + return getGroup(id);
1.2698 + } finally {
1.2699 + if (rs != null) {
1.2700 + try {
1.2701 + rs.close();
1.2702 + } catch (SQLException ex) {
1.2703 + ex.printStackTrace();
1.2704 + }
1.2705 + }
1.2706 + }
1.2707 + }
1.2708 +
1.2709 + @Override
1.2710 + public double getEventsPerHour(int key, long gid)
1.2711 + throws StorageBackendException
1.2712 + {
1.2713 + String gidquery = "";
1.2714 + if (gid >= 0) {
1.2715 + gidquery = " AND group_id = " + gid;
1.2716 + }
1.2717 +
1.2718 + Statement stmt = null;
1.2719 + ResultSet rs = null;
1.2720 +
1.2721 + try {
1.2722 + stmt = this.conn.createStatement();
1.2723 + rs = stmt.executeQuery("SELECT Count(*) / (Max(event_time) - Min(event_time))"
1.2724 + + " * 1000 * 60 * 60 FROM events WHERE event_key = " + key + gidquery);
1.2725 +
1.2726 + if (rs.next()) {
1.2727 + restarts = 0; // reset error count
1.2728 + return rs.getDouble(1);
1.2729 + } else {
1.2730 + return Double.NaN;
1.2731 + }
1.2732 + } catch (SQLException ex) {
1.2733 + restartConnection(ex);
1.2734 + return getEventsPerHour(key, gid);
1.2735 + } finally {
1.2736 + try {
1.2737 + if (stmt != null) {
1.2738 + stmt.close(); // Implicitely closes the result sets
1.2739 + }
1.2740 + } catch (SQLException ex) {
1.2741 + ex.printStackTrace();
1.2742 + }
1.2743 + }
1.2744 + }
1.2745 +
1.2746 + @Override
1.2747 + public String getOldestArticle()
1.2748 + throws StorageBackendException
1.2749 + {
1.2750 + ResultSet rs = null;
1.2751 +
1.2752 + try {
1.2753 + rs = this.pstmtGetOldestArticle.executeQuery();
1.2754 + if (rs.next()) {
1.2755 + return rs.getString(1);
1.2756 + } else {
1.2757 + return null;
1.2758 + }
1.2759 + } catch (SQLException ex) {
1.2760 + restartConnection(ex);
1.2761 + return getOldestArticle();
1.2762 + } finally {
1.2763 + if (rs != null) {
1.2764 + try {
1.2765 + rs.close();
1.2766 + } catch (SQLException ex) {
1.2767 + ex.printStackTrace();
1.2768 + }
1.2769 + }
1.2770 + }
1.2771 + }
1.2772 +
1.2773 + @Override
1.2774 + public int getPostingsCount(String groupname)
1.2775 + throws StorageBackendException
1.2776 + {
1.2777 + ResultSet rs = null;
1.2778 +
1.2779 + try {
1.2780 + this.pstmtGetPostingsCount.setString(1, groupname);
1.2781 + rs = this.pstmtGetPostingsCount.executeQuery();
1.2782 + if (rs.next()) {
1.2783 + return rs.getInt(1);
1.2784 + } else {
1.2785 + Log.get().warning("Count on postings return nothing!");
1.2786 + return 0;
1.2787 + }
1.2788 + } catch (SQLException ex) {
1.2789 + restartConnection(ex);
1.2790 + return getPostingsCount(groupname);
1.2791 + } finally {
1.2792 + if (rs != null) {
1.2793 + try {
1.2794 + rs.close();
1.2795 + } catch (SQLException ex) {
1.2796 + ex.printStackTrace();
1.2797 + }
1.2798 + }
1.2799 + }
1.2800 + }
1.2801 +
1.2802 + @Override
1.2803 + public List<Subscription> getSubscriptions(int feedtype)
1.2804 + throws StorageBackendException
1.2805 + {
1.2806 + ResultSet rs = null;
1.2807 +
1.2808 + try {
1.2809 + List<Subscription> subs = new ArrayList<Subscription>();
1.2810 + this.pstmtGetSubscriptions.setInt(1, feedtype);
1.2811 + rs = this.pstmtGetSubscriptions.executeQuery();
1.2812 +
1.2813 + while (rs.next()) {
1.2814 + String host = rs.getString("host");
1.2815 + String group = rs.getString("name");
1.2816 + int port = rs.getInt("port");
1.2817 + subs.add(new Subscription(host, port, feedtype, group));
1.2818 + }
1.2819 +
1.2820 + return subs;
1.2821 + } catch (SQLException ex) {
1.2822 + restartConnection(ex);
1.2823 + return getSubscriptions(feedtype);
1.2824 + } finally {
1.2825 + if (rs != null) {
1.2826 + try {
1.2827 + rs.close();
1.2828 + } catch (SQLException ex) {
1.2829 + ex.printStackTrace();
1.2830 + }
1.2831 + }
1.2832 + }
1.2833 + }
1.2834 +
1.2835 + /**
1.2836 + * Checks if there is an article with the given messageid in the JDBCDatabase.
1.2837 + * @param name
1.2838 + * @return
1.2839 + * @throws StorageBackendException
1.2840 + */
1.2841 + @Override
1.2842 + public boolean isArticleExisting(String messageID)
1.2843 + throws StorageBackendException
1.2844 + {
1.2845 + ResultSet rs = null;
1.2846 +
1.2847 + try {
1.2848 + this.pstmtIsArticleExisting.setString(1, messageID);
1.2849 + rs = this.pstmtIsArticleExisting.executeQuery();
1.2850 + return rs.next() && rs.getInt(1) == 1;
1.2851 + } catch (SQLException ex) {
1.2852 + restartConnection(ex);
1.2853 + return isArticleExisting(messageID);
1.2854 + } finally {
1.2855 + if (rs != null) {
1.2856 + try {
1.2857 + rs.close();
1.2858 + } catch (SQLException ex) {
1.2859 + ex.printStackTrace();
1.2860 + }
1.2861 + }
1.2862 + }
1.2863 + }
1.2864 +
1.2865 + /**
1.2866 + * Checks if there is a group with the given name in the JDBCDatabase.
1.2867 + * @param name
1.2868 + * @return
1.2869 + * @throws StorageBackendException
1.2870 + */
1.2871 + @Override
1.2872 + public boolean isGroupExisting(String name)
1.2873 + throws StorageBackendException
1.2874 + {
1.2875 + ResultSet rs = null;
1.2876 +
1.2877 + try {
1.2878 + this.pstmtIsGroupExisting.setString(1, name);
1.2879 + rs = this.pstmtIsGroupExisting.executeQuery();
1.2880 + return rs.next();
1.2881 + } catch (SQLException ex) {
1.2882 + restartConnection(ex);
1.2883 + return isGroupExisting(name);
1.2884 + } finally {
1.2885 + if (rs != null) {
1.2886 + try {
1.2887 + rs.close();
1.2888 + } catch (SQLException ex) {
1.2889 + ex.printStackTrace();
1.2890 + }
1.2891 + }
1.2892 + }
1.2893 + }
1.2894 +
1.2895 + @Override
1.2896 + public void setConfigValue(String key, String value)
1.2897 + throws StorageBackendException
1.2898 + {
1.2899 + try {
1.2900 + conn.setAutoCommit(false);
1.2901 + this.pstmtSetConfigValue0.setString(1, key);
1.2902 + this.pstmtSetConfigValue0.execute();
1.2903 + this.pstmtSetConfigValue1.setString(1, key);
1.2904 + this.pstmtSetConfigValue1.setString(2, value);
1.2905 + this.pstmtSetConfigValue1.execute();
1.2906 + conn.commit();
1.2907 + conn.setAutoCommit(true);
1.2908 + } catch (SQLException ex) {
1.2909 + restartConnection(ex);
1.2910 + setConfigValue(key, value);
1.2911 + }
1.2912 + }
1.2913 +
1.2914 + /**
1.2915 + * Closes the JDBCDatabase connection.
1.2916 + */
1.2917 + public void shutdown()
1.2918 + throws StorageBackendException
1.2919 + {
1.2920 + try {
1.2921 + if (this.conn != null) {
1.2922 + this.conn.close();
1.2923 + }
1.2924 + } catch (SQLException ex) {
1.2925 + throw new StorageBackendException(ex);
1.2926 + }
1.2927 + }
1.2928 +
1.2929 + @Override
1.2930 + public void purgeGroup(Group group)
1.2931 + throws StorageBackendException
1.2932 + {
1.2933 + try {
1.2934 + this.pstmtPurgeGroup0.setLong(1, group.getInternalID());
1.2935 + this.pstmtPurgeGroup0.executeUpdate();
1.2936 +
1.2937 + this.pstmtPurgeGroup1.setLong(1, group.getInternalID());
1.2938 + this.pstmtPurgeGroup1.executeUpdate();
1.2939 + } catch (SQLException ex) {
1.2940 + restartConnection(ex);
1.2941 + purgeGroup(group);
1.2942 + }
1.2943 + }
1.2944 +
1.2945 + private void restartConnection(SQLException cause)
1.2946 + throws StorageBackendException
1.2947 + {
1.2948 + restarts++;
1.2949 + Log.get().severe(Thread.currentThread()
1.2950 + + ": Database connection was closed (restart " + restarts + ").");
1.2951 +
1.2952 + if (restarts >= MAX_RESTARTS) {
1.2953 + // Delete the current, probably broken JDBCDatabase instance.
1.2954 + // So no one can use the instance any more.
1.2955 + JDBCDatabaseProvider.instances.remove(Thread.currentThread());
1.2956 +
1.2957 + // Throw the exception upwards
1.2958 + throw new StorageBackendException(cause);
1.2959 + }
1.2960 +
1.2961 + try {
1.2962 + Thread.sleep(1500L * restarts);
1.2963 + } catch (InterruptedException ex) {
1.2964 + Log.get().warning("Interrupted: " + ex.getMessage());
1.2965 + }
1.2966 +
1.2967 + // Try to properly close the old database connection
1.2968 + try {
1.2969 + if (this.conn != null) {
1.2970 + this.conn.close();
1.2971 + }
1.2972 + } catch (SQLException ex) {
1.2973 + Log.get().warning(ex.getMessage());
1.2974 + }
1.2975 +
1.2976 + try {
1.2977 + // Try to reinitialize database connection
1.2978 + arise();
1.2979 + } catch (SQLException ex) {
1.2980 + Log.get().warning(ex.getMessage());
1.2981 + restartConnection(ex);
1.2982 + }
1.2983 + }
1.2984 +
1.2985 + @Override
1.2986 + public boolean update(Article article)
1.2987 + throws StorageBackendException
1.2988 + {
1.2989 + // DELETE FROM headers WHERE article_id = ?
1.2990 +
1.2991 + // INSERT INTO headers ...
1.2992 +
1.2993 + // SELECT * FROM postings WHERE article_id = ? AND group_id = ?
1.2994 + return false;
1.2995 + }
1.2996 +
1.2997 + /**
1.2998 + * Writes the flags and the name of the given group to the database.
1.2999 + * @param group
1.3000 + * @throws StorageBackendException
1.3001 + */
1.3002 + @Override
1.3003 + public boolean update(Group group)
1.3004 + throws StorageBackendException
1.3005 + {
1.3006 + try {
1.3007 + this.pstmtUpdateGroup.setInt(1, group.getFlags());
1.3008 + this.pstmtUpdateGroup.setString(2, group.getName());
1.3009 + this.pstmtUpdateGroup.setLong(3, group.getInternalID());
1.3010 + int rs = this.pstmtUpdateGroup.executeUpdate();
1.3011 + return rs == 1;
1.3012 + } catch (SQLException ex) {
1.3013 + restartConnection(ex);
1.3014 + return update(group);
1.3015 + }
1.3016 + }
1.3017 }