Drupal: částečně funkční – jde stáhnout zprávy s předmětem a textem.
authorFrantišek Kučera <franta-hg@frantovo.cz>
Tue, 11 Oct 2011 00:38:41 +0200
changeset 686e16e3bee1ca
parent 67 4653fc7609e7
child 69 b51612c18a54
Drupal: částečně funkční – jde stáhnout zprávy s předmětem a textem.
src/org/sonews/storage/impl/DrupalDatabase.java
src/org/sonews/storage/impl/DrupalDatabaseProvider.java
     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  	//
     2.1 --- a/src/org/sonews/storage/impl/DrupalDatabaseProvider.java	Sun Oct 09 01:22:18 2011 +0200
     2.2 +++ b/src/org/sonews/storage/impl/DrupalDatabaseProvider.java	Tue Oct 11 00:38:41 2011 +0200
     2.3 @@ -17,7 +17,6 @@
     2.4   */
     2.5  package org.sonews.storage.impl;
     2.6  
     2.7 -import java.sql.SQLException;
     2.8  import java.util.Map;
     2.9  import java.util.concurrent.ConcurrentHashMap;
    2.10  import org.sonews.storage.Storage;
    2.11 @@ -38,19 +37,14 @@
    2.12  	}
    2.13  
    2.14  	@Override
    2.15 -	public Storage storage(Thread thread)
    2.16 -			throws StorageBackendException {
    2.17 -		try {
    2.18 -			if (!instances.containsKey(Thread.currentThread())) {
    2.19 -				DrupalDatabase db = new DrupalDatabase();
    2.20 -				db.arise();
    2.21 -				instances.put(Thread.currentThread(), db);
    2.22 -				return db;
    2.23 -			} else {
    2.24 -				return instances.get(Thread.currentThread());
    2.25 -			}
    2.26 -		} catch (SQLException ex) {
    2.27 -			throw new StorageBackendException(ex);
    2.28 +	public Storage storage(Thread thread) throws StorageBackendException {
    2.29 +		DrupalDatabase db = instances.get(Thread.currentThread());
    2.30 +
    2.31 +		if (db == null) {
    2.32 +			db = new DrupalDatabase();
    2.33 +			instances.put(Thread.currentThread(), db);
    2.34  		}
    2.35 +
    2.36 +		return db;
    2.37  	}
    2.38 -}
    2.39 +}
    2.40 \ No newline at end of file