1.1 --- a/src/org/sonews/storage/impl/DrupalDatabase.java Sun Oct 09 01:22:18 2011 +0200
1.2 +++ b/src/org/sonews/storage/impl/DrupalDatabase.java Tue Oct 11 00:38:41 2011 +0200
1.3 @@ -17,11 +17,20 @@
1.4 */
1.5 package org.sonews.storage.impl;
1.6
1.7 +import java.io.UnsupportedEncodingException;
1.8 +import java.sql.Connection;
1.9 +import java.sql.DriverManager;
1.10 +import java.sql.PreparedStatement;
1.11 +import java.sql.ResultSet;
1.12 import java.sql.SQLException;
1.13 +import java.sql.Statement;
1.14 +import java.util.ArrayList;
1.15 import java.util.Collections;
1.16 import java.util.List;
1.17 import java.util.logging.Level;
1.18 import java.util.logging.Logger;
1.19 +import javax.mail.internet.MimeUtility;
1.20 +import org.sonews.config.Config;
1.21 import org.sonews.feed.Subscription;
1.22 import org.sonews.storage.Article;
1.23 import org.sonews.storage.ArticleHead;
1.24 @@ -37,54 +46,234 @@
1.25 public class DrupalDatabase implements Storage {
1.26
1.27 private static final Logger log = Logger.getLogger(DrupalDatabase.class.getName());
1.28 + public static final String CRLF = "\r\n";
1.29 + public static final int MAX_RESTARTS = 2;
1.30 + /** How many times the database connection was reinitialized */
1.31 + protected int restarts = 0;
1.32 + protected Connection conn = null;
1.33 +
1.34 + public DrupalDatabase() throws StorageBackendException {
1.35 + connectDatabase();
1.36 + }
1.37 +
1.38 + private void connectDatabase() throws StorageBackendException {
1.39 + try {
1.40 + // Load database driver
1.41 + String driverClass = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object");
1.42 + Class.forName(driverClass);
1.43 +
1.44 + // Establish database connection
1.45 + String url = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>");
1.46 + String username = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root");
1.47 + String password = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, "");
1.48 + conn = DriverManager.getConnection(url, username, password);
1.49 +
1.50 + conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
1.51 + if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {
1.52 + log.warning("Database is NOT fully serializable!");
1.53 + }
1.54 + } catch (Exception e) {
1.55 + throw new StorageBackendException(e);
1.56 + }
1.57 + }
1.58 +
1.59 + protected static void close(Connection connection, Statement statement, ResultSet resultSet) {
1.60 + if (resultSet != null) {
1.61 + try {
1.62 + resultSet.close();
1.63 + } catch (Exception e) {
1.64 + }
1.65 + }
1.66 + if (statement != null) {
1.67 + try {
1.68 + statement.close();
1.69 + } catch (Exception e) {
1.70 + }
1.71 + }
1.72 + if (connection != null) {
1.73 + try {
1.74 + connection.close();
1.75 + } catch (Exception e) {
1.76 + }
1.77 + }
1.78 + }
1.79
1.80 /**
1.81 - * Rises the database: reconnect and recreate all prepared statements.
1.82 - * @throws java.lang.SQLException
1.83 + *
1.84 + * @param messageID {0}-{1}-{2}@domain.tld where {0} is nntp_id and {1} is group_id and {2} is group_name
1.85 + * @return array where [0] = nntp_id and [1] = group_id and [2] = group_name or returns null if messageID is invalid
1.86 */
1.87 - protected void arise() throws SQLException {
1.88 + private static String[] parseMessageID(String messageID) {
1.89 + if (messageID.matches("[0-9]+\\-[0-9]+\\-[a-z0-9\\.]+@.+")) {
1.90 + return messageID.split("@")[0].split("\\-");
1.91 + } else {
1.92 + return null;
1.93 + }
1.94 + }
1.95 +
1.96 + private static Long parseArticleID(String messageID) {
1.97 + String[] localPart = parseMessageID(messageID);
1.98 + if (localPart == null) {
1.99 + return null;
1.100 + } else {
1.101 + return Long.parseLong(localPart[0]);
1.102 + }
1.103 + }
1.104 +
1.105 + private static Long parseGroupID(String messageID) {
1.106 + String[] localPart = parseMessageID(messageID);
1.107 + if (localPart == null) {
1.108 + return null;
1.109 + } else {
1.110 + return Long.parseLong(localPart[1]);
1.111 + }
1.112 + }
1.113 +
1.114 + private static String parseGroupName(String messageID) {
1.115 + String[] localPart = parseMessageID(messageID);
1.116 + if (localPart == null) {
1.117 + return null;
1.118 + } else {
1.119 + return localPart[2];
1.120 + }
1.121 + }
1.122 +
1.123 + private static String constructHeaders(ResultSet rs) throws SQLException, UnsupportedEncodingException {
1.124 + StringBuilder sb = new StringBuilder();
1.125 +
1.126 + sb.append("Message-id: ");
1.127 + sb.append(rs.getInt("id"));
1.128 + sb.append("-");
1.129 + sb.append(rs.getInt("group_id"));
1.130 + sb.append("-");
1.131 + sb.append(rs.getString("group_name"));
1.132 + sb.append("@");
1.133 + sb.append("nntp.kinderporno.cz");
1.134 + sb.append(CRLF);
1.135 +
1.136 + sb.append("From: ");
1.137 + sb.append(MimeUtility.encodeWord(rs.getString("sender_name")));
1.138 + sb.append(" <>");
1.139 + sb.append(CRLF);
1.140 +
1.141 + sb.append("Subject: ");
1.142 + sb.append(MimeUtility.encodeWord(rs.getString("subject")));
1.143 + sb.append(CRLF);
1.144 +
1.145 +
1.146 +
1.147 + return sb.toString();
1.148 }
1.149
1.150 @Override
1.151 public List<Group> getGroups() throws StorageBackendException {
1.152 - log.log(Level.SEVERE, "TODO: getGroups");
1.153 - /** TODO: */
1.154 - return Collections.emptyList();
1.155 + PreparedStatement ps = null;
1.156 + ResultSet rs = null;
1.157 + try {
1.158 + ps = conn.prepareStatement("SELECT * FROM nntp_group");
1.159 + rs = ps.executeQuery();
1.160 + List<Group> skupiny = new ArrayList<Group>();
1.161 +
1.162 + while (rs.next()) {
1.163 + skupiny.add(new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY));
1.164 + }
1.165 +
1.166 + return skupiny;
1.167 + } catch (Exception e) {
1.168 + throw new StorageBackendException(e);
1.169 + } finally {
1.170 + close(null, ps, rs);
1.171 + }
1.172 }
1.173
1.174 @Override
1.175 public Group getGroup(String name) throws StorageBackendException {
1.176 - log.log(Level.SEVERE, "TODO: getGroup {0}", new Object[]{name});
1.177 - /** TODO: */
1.178 - return null;
1.179 + PreparedStatement ps = null;
1.180 + ResultSet rs = null;
1.181 + try {
1.182 + ps = conn.prepareStatement("SELECT * FROM nntp_group WHERE name = ?");
1.183 + ps.setString(1, name);
1.184 + rs = ps.executeQuery();
1.185 +
1.186 + while (rs.next()) {
1.187 + return new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY);
1.188 + }
1.189 +
1.190 + return null;
1.191 + } catch (Exception e) {
1.192 + throw new StorageBackendException(e);
1.193 + } finally {
1.194 + close(null, ps, rs);
1.195 + }
1.196 }
1.197
1.198 @Override
1.199 public boolean isGroupExisting(String groupname) throws StorageBackendException {
1.200 - log.log(Level.SEVERE, "TODO: isGroupExisting {0}", new Object[]{groupname});
1.201 - /** TODO: */
1.202 - return false;
1.203 + return getGroup(groupname) != null;
1.204 }
1.205
1.206 @Override
1.207 public Article getArticle(String messageID) throws StorageBackendException {
1.208 - log.log(Level.SEVERE, "TODO: getArticle {0}", new Object[]{messageID});
1.209 - /** TODO: */
1.210 - return null;
1.211 + Long articleID = parseArticleID(messageID);
1.212 + Long groupID = parseGroupID(messageID);
1.213 +
1.214 + if (articleID == null || groupID == null) {
1.215 + log.log(Level.SEVERE, "Invalid messageID: {0}", new Object[]{messageID});
1.216 + return null;
1.217 + } else {
1.218 + return getArticle(articleID, groupID);
1.219 + }
1.220 }
1.221
1.222 @Override
1.223 - public Article getArticle(long articleIndex, long groupID) throws StorageBackendException {
1.224 - log.log(Level.SEVERE, "TODO: getArticle {0} / {1}", new Object[]{articleIndex, groupID});
1.225 - /** TODO: */
1.226 - return null;
1.227 + public Article getArticle(long articleID, long groupID) throws StorageBackendException {
1.228 + PreparedStatement ps = null;
1.229 + ResultSet rs = null;
1.230 + try {
1.231 + ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE id = ? AND group_id = ?");
1.232 + ps.setLong(1, articleID);
1.233 + ps.setLong(2, groupID);
1.234 + rs = ps.executeQuery();
1.235 +
1.236 + if (rs.next()) {
1.237 + String headers = constructHeaders(rs);
1.238 + byte[] body = rs.getString("text").getBytes();
1.239 +
1.240 + return new Article(headers, body);
1.241 + } else {
1.242 + return null;
1.243 + }
1.244 + } catch (Exception e) {
1.245 + throw new StorageBackendException(e);
1.246 + } finally {
1.247 + close(null, ps, rs);
1.248 + }
1.249 }
1.250
1.251 @Override
1.252 public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first, long last) throws StorageBackendException {
1.253 - log.log(Level.SEVERE, "TODO: getArticleHeads {0} / {1} / {2}", new Object[]{group, first, last});
1.254 - /** TODO: */
1.255 - return Collections.emptyList();
1.256 + PreparedStatement ps = null;
1.257 + ResultSet rs = null;
1.258 + try {
1.259 + ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE group_id = ? AND id >= ? AND id <= ?");
1.260 + ps.setLong(1, group.getInternalID());
1.261 + ps.setLong(2, first);
1.262 + ps.setLong(3, last);
1.263 + rs = ps.executeQuery();
1.264 +
1.265 + List<Pair<Long, ArticleHead>> heads = new ArrayList<Pair<Long, ArticleHead>>();
1.266 +
1.267 + while (rs.next()) {
1.268 + String headers = constructHeaders(rs);
1.269 + heads.add(new Pair<Long, ArticleHead>(rs.getLong("id"), new ArticleHead(headers)));
1.270 + }
1.271 +
1.272 + return heads;
1.273 + } catch (Exception e) {
1.274 + throw new StorageBackendException(e);
1.275 + } finally {
1.276 + close(null, ps, rs);
1.277 + }
1.278 }
1.279
1.280 @Override
1.281 @@ -95,38 +284,93 @@
1.282 }
1.283
1.284 @Override
1.285 - public long getArticleIndex(Article art, Group group) throws StorageBackendException {
1.286 - log.log(Level.SEVERE, "TODO: getArticleIndex {0} / {1}", new Object[]{art, group});
1.287 - /** TODO: */
1.288 - return 0;
1.289 + public long getArticleIndex(Article article, Group group) throws StorageBackendException {
1.290 + Long id = parseArticleID(article.getMessageID());
1.291 + if (id == null) {
1.292 + throw new StorageBackendException("Invalid messageID: " + article.getMessageID());
1.293 + } else {
1.294 + return id;
1.295 + }
1.296 }
1.297
1.298 @Override
1.299 public List<Long> getArticleNumbers(long groupID) throws StorageBackendException {
1.300 - log.log(Level.SEVERE, "TODO: getArticleNumbers {0}", new Object[]{groupID});
1.301 - /** TODO: */
1.302 - return Collections.emptyList();
1.303 + PreparedStatement ps = null;
1.304 + ResultSet rs = null;
1.305 + try {
1.306 + ps = conn.prepareStatement("SELECT id FROM nntp_article WHERE group_id = ?");
1.307 + ps.setLong(1, groupID);
1.308 + rs = ps.executeQuery();
1.309 + List<Long> articleNumbers = new ArrayList<Long>();
1.310 + while (rs.next()) {
1.311 + articleNumbers.add(rs.getLong(1));
1.312 + }
1.313 + return articleNumbers;
1.314 + } catch (Exception e) {
1.315 + throw new StorageBackendException(e);
1.316 + } finally {
1.317 + close(null, ps, rs);
1.318 + }
1.319 }
1.320
1.321 @Override
1.322 public int getFirstArticleNumber(Group group) throws StorageBackendException {
1.323 - log.log(Level.SEVERE, "TODO: getFirstArticleNumber {0}", new Object[]{group});
1.324 - /** TODO: */
1.325 - return 0;
1.326 + PreparedStatement ps = null;
1.327 + ResultSet rs = null;
1.328 + try {
1.329 + ps = conn.prepareStatement("SELECT min(id) FROM nntp_article WHERE group_id = ?");
1.330 + ps.setLong(1, group.getInternalID());
1.331 + rs = ps.executeQuery();
1.332 + rs.next();
1.333 + return rs.getInt(1);
1.334 + } catch (Exception e) {
1.335 + throw new StorageBackendException(e);
1.336 + } finally {
1.337 + close(null, ps, rs);
1.338 + }
1.339 }
1.340
1.341 @Override
1.342 public int getLastArticleNumber(Group group) throws StorageBackendException {
1.343 - log.log(Level.SEVERE, "TODO: getLastArticleNumber {0}", new Object[]{group});
1.344 - /** TODO: */
1.345 - return 0;
1.346 + PreparedStatement ps = null;
1.347 + ResultSet rs = null;
1.348 + try {
1.349 + ps = conn.prepareStatement("SELECT max(id) FROM nntp_article WHERE group_id = ?");
1.350 + ps.setLong(1, group.getInternalID());
1.351 + rs = ps.executeQuery();
1.352 + rs.next();
1.353 + return rs.getInt(1);
1.354 + } catch (Exception e) {
1.355 + throw new StorageBackendException(e);
1.356 + } finally {
1.357 + close(null, ps, rs);
1.358 + }
1.359 }
1.360
1.361 @Override
1.362 public boolean isArticleExisting(String messageID) throws StorageBackendException {
1.363 - log.log(Level.SEVERE, "TODO: isArticleExisting {0}", new Object[]{messageID});
1.364 - /** TODO: */
1.365 - return false;
1.366 + Long articleID = parseArticleID(messageID);
1.367 + Long groupID = parseGroupID(messageID);
1.368 +
1.369 + if (articleID == null || groupID == null) {
1.370 + return false;
1.371 + } else {
1.372 + PreparedStatement ps = null;
1.373 + ResultSet rs = null;
1.374 + try {
1.375 + ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE id = ? AND group_id = ?");
1.376 + ps.setLong(1, articleID);
1.377 + ps.setLong(2, groupID);
1.378 + rs = ps.executeQuery();
1.379 +
1.380 + rs.next();
1.381 + return rs.getInt(1) == 1;
1.382 + } catch (Exception e) {
1.383 + throw new StorageBackendException(e);
1.384 + } finally {
1.385 + close(null, ps, rs);
1.386 + }
1.387 + }
1.388 }
1.389
1.390 //