# HG changeset patch
# User František Kučera <franta-hg@frantovo.cz>
# Date 1318286321 -7200
# Node ID 6e16e3bee1cad8dd67f89753cd06fd039ea589aa
# Parent  4653fc7609e722fc7d8d9569127527d829dae0b1
Drupal: částečně funkční – jde stáhnout zprávy s předmětem a textem.

diff -r 4653fc7609e7 -r 6e16e3bee1ca src/org/sonews/storage/impl/DrupalDatabase.java
--- a/src/org/sonews/storage/impl/DrupalDatabase.java	Sun Oct 09 01:22:18 2011 +0200
+++ b/src/org/sonews/storage/impl/DrupalDatabase.java	Tue Oct 11 00:38:41 2011 +0200
@@ -17,11 +17,20 @@
  */
 package org.sonews.storage.impl;
 
+import java.io.UnsupportedEncodingException;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
 import java.util.Collections;
 import java.util.List;
 import java.util.logging.Level;
 import java.util.logging.Logger;
+import javax.mail.internet.MimeUtility;
+import org.sonews.config.Config;
 import org.sonews.feed.Subscription;
 import org.sonews.storage.Article;
 import org.sonews.storage.ArticleHead;
@@ -37,54 +46,234 @@
 public class DrupalDatabase implements Storage {
 
 	private static final Logger log = Logger.getLogger(DrupalDatabase.class.getName());
+	public static final String CRLF = "\r\n";
+	public static final int MAX_RESTARTS = 2;
+	/** How many times the database connection was reinitialized */
+	protected int restarts = 0;
+	protected Connection conn = null;
+
+	public DrupalDatabase() throws StorageBackendException {
+		connectDatabase();
+	}
+
+	private void connectDatabase() throws StorageBackendException {
+		try {
+			// Load database driver
+			String driverClass = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object");
+			Class.forName(driverClass);
+
+			// Establish database connection
+			String url = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>");
+			String username = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root");
+			String password = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, "");
+			conn = DriverManager.getConnection(url, username, password);
+
+			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
+			if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {
+				log.warning("Database is NOT fully serializable!");
+			}
+		} catch (Exception e) {
+			throw new StorageBackendException(e);
+		}
+	}
+
+	protected static void close(Connection connection, Statement statement, ResultSet resultSet) {
+		if (resultSet != null) {
+			try {
+				resultSet.close();
+			} catch (Exception e) {
+			}
+		}
+		if (statement != null) {
+			try {
+				statement.close();
+			} catch (Exception e) {
+			}
+		}
+		if (connection != null) {
+			try {
+				connection.close();
+			} catch (Exception e) {
+			}
+		}
+	}
 
 	/**
-	 * Rises the database: reconnect and recreate all prepared statements.
-	 * @throws java.lang.SQLException
+	 * 
+	 * @param messageID {0}-{1}-{2}@domain.tld where {0} is nntp_id and {1} is group_id and {2} is group_name
+	 * @return array where [0] = nntp_id and [1] = group_id and [2] = group_name or returns null if messageID is invalid
 	 */
-	protected void arise() throws SQLException {
+	private static String[] parseMessageID(String messageID) {
+		if (messageID.matches("[0-9]+\\-[0-9]+\\-[a-z0-9\\.]+@.+")) {
+			return messageID.split("@")[0].split("\\-");
+		} else {
+			return null;
+		}
+	}
+
+	private static Long parseArticleID(String messageID) {
+		String[] localPart = parseMessageID(messageID);
+		if (localPart == null) {
+			return null;
+		} else {
+			return Long.parseLong(localPart[0]);
+		}
+	}
+
+	private static Long parseGroupID(String messageID) {
+		String[] localPart = parseMessageID(messageID);
+		if (localPart == null) {
+			return null;
+		} else {
+			return Long.parseLong(localPart[1]);
+		}
+	}
+
+	private static String parseGroupName(String messageID) {
+		String[] localPart = parseMessageID(messageID);
+		if (localPart == null) {
+			return null;
+		} else {
+			return localPart[2];
+		}
+	}
+
+	private static String constructHeaders(ResultSet rs) throws SQLException, UnsupportedEncodingException {
+		StringBuilder sb = new StringBuilder();
+		
+		sb.append("Message-id: ");
+		sb.append(rs.getInt("id"));
+		sb.append("-");
+		sb.append(rs.getInt("group_id"));
+		sb.append("-");
+		sb.append(rs.getString("group_name"));
+		sb.append("@");
+		sb.append("nntp.kinderporno.cz");
+		sb.append(CRLF);
+		
+		sb.append("From: ");
+		sb.append(MimeUtility.encodeWord(rs.getString("sender_name")));
+		sb.append(" <>");
+		sb.append(CRLF);
+		
+		sb.append("Subject: ");
+		sb.append(MimeUtility.encodeWord(rs.getString("subject")));
+		sb.append(CRLF);
+		
+		
+
+		return sb.toString();
 	}
 
 	@Override
 	public List<Group> getGroups() throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: getGroups");
-		/** TODO: */
-		return Collections.emptyList();
+		PreparedStatement ps = null;
+		ResultSet rs = null;
+		try {
+			ps = conn.prepareStatement("SELECT * FROM nntp_group");
+			rs = ps.executeQuery();
+			List<Group> skupiny = new ArrayList<Group>();
+
+			while (rs.next()) {
+				skupiny.add(new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY));
+			}
+
+			return skupiny;
+		} catch (Exception e) {
+			throw new StorageBackendException(e);
+		} finally {
+			close(null, ps, rs);
+		}
 	}
 
 	@Override
 	public Group getGroup(String name) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: getGroup {0}", new Object[]{name});
-		/** TODO: */
-		return null;
+		PreparedStatement ps = null;
+		ResultSet rs = null;
+		try {
+			ps = conn.prepareStatement("SELECT * FROM nntp_group WHERE name = ?");
+			ps.setString(1, name);
+			rs = ps.executeQuery();
+
+			while (rs.next()) {
+				return new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY);
+			}
+
+			return null;
+		} catch (Exception e) {
+			throw new StorageBackendException(e);
+		} finally {
+			close(null, ps, rs);
+		}
 	}
 
 	@Override
 	public boolean isGroupExisting(String groupname) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: isGroupExisting {0}", new Object[]{groupname});
-		/** TODO: */
-		return false;
+		return getGroup(groupname) != null;
 	}
 
 	@Override
 	public Article getArticle(String messageID) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: getArticle {0}", new Object[]{messageID});
-		/** TODO: */
-		return null;
+		Long articleID = parseArticleID(messageID);
+		Long groupID = parseGroupID(messageID);
+
+		if (articleID == null || groupID == null) {
+			log.log(Level.SEVERE, "Invalid messageID: {0}", new Object[]{messageID});
+			return null;
+		} else {
+			return getArticle(articleID, groupID);
+		}
 	}
 
 	@Override
-	public Article getArticle(long articleIndex, long groupID) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: getArticle {0} / {1}", new Object[]{articleIndex, groupID});
-		/** TODO: */
-		return null;
+	public Article getArticle(long articleID, long groupID) throws StorageBackendException {
+		PreparedStatement ps = null;
+		ResultSet rs = null;
+		try {
+			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE id = ? AND group_id = ?");
+			ps.setLong(1, articleID);
+			ps.setLong(2, groupID);
+			rs = ps.executeQuery();
+
+			if (rs.next()) {
+				String headers = constructHeaders(rs);
+				byte[] body = rs.getString("text").getBytes();
+				
+				return new Article(headers, body);
+			} else {
+				return null;
+			}
+		} catch (Exception e) {
+			throw new StorageBackendException(e);
+		} finally {
+			close(null, ps, rs);
+		}
 	}
 
 	@Override
 	public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first, long last) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: getArticleHeads {0} / {1} / {2}", new Object[]{group, first, last});
-		/** TODO: */
-		return Collections.emptyList();
+		PreparedStatement ps = null;
+		ResultSet rs = null;
+		try {
+			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE group_id = ? AND id >= ? AND id <= ?");
+			ps.setLong(1, group.getInternalID());
+			ps.setLong(2, first);
+			ps.setLong(3, last);
+			rs = ps.executeQuery();
+
+			List<Pair<Long, ArticleHead>> heads = new ArrayList<Pair<Long, ArticleHead>>();
+
+			while (rs.next()) {
+				String headers = constructHeaders(rs);
+				heads.add(new Pair<Long, ArticleHead>(rs.getLong("id"), new ArticleHead(headers)));
+			}
+
+			return heads;
+		} catch (Exception e) {
+			throw new StorageBackendException(e);
+		} finally {
+			close(null, ps, rs);
+		}
 	}
 
 	@Override
@@ -95,38 +284,93 @@
 	}
 
 	@Override
-	public long getArticleIndex(Article art, Group group) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: getArticleIndex {0} / {1}", new Object[]{art, group});
-		/** TODO: */
-		return 0;
+	public long getArticleIndex(Article article, Group group) throws StorageBackendException {
+		Long id = parseArticleID(article.getMessageID());
+		if (id == null) {
+			throw new StorageBackendException("Invalid messageID: " + article.getMessageID());
+		} else {
+			return id;
+		}
 	}
 
 	@Override
 	public List<Long> getArticleNumbers(long groupID) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: getArticleNumbers {0}", new Object[]{groupID});
-		/** TODO: */
-		return Collections.emptyList();
+		PreparedStatement ps = null;
+		ResultSet rs = null;
+		try {
+			ps = conn.prepareStatement("SELECT id FROM nntp_article WHERE group_id = ?");
+			ps.setLong(1, groupID);
+			rs = ps.executeQuery();
+			List<Long> articleNumbers = new ArrayList<Long>();
+			while (rs.next()) {
+				articleNumbers.add(rs.getLong(1));
+			}
+			return articleNumbers;
+		} catch (Exception e) {
+			throw new StorageBackendException(e);
+		} finally {
+			close(null, ps, rs);
+		}
 	}
 
 	@Override
 	public int getFirstArticleNumber(Group group) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: getFirstArticleNumber {0}", new Object[]{group});
-		/** TODO: */
-		return 0;
+		PreparedStatement ps = null;
+		ResultSet rs = null;
+		try {
+			ps = conn.prepareStatement("SELECT min(id) FROM nntp_article WHERE group_id = ?");
+			ps.setLong(1, group.getInternalID());
+			rs = ps.executeQuery();
+			rs.next();
+			return rs.getInt(1);
+		} catch (Exception e) {
+			throw new StorageBackendException(e);
+		} finally {
+			close(null, ps, rs);
+		}
 	}
 
 	@Override
 	public int getLastArticleNumber(Group group) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: getLastArticleNumber {0}", new Object[]{group});
-		/** TODO: */
-		return 0;
+		PreparedStatement ps = null;
+		ResultSet rs = null;
+		try {
+			ps = conn.prepareStatement("SELECT max(id) FROM nntp_article WHERE group_id = ?");
+			ps.setLong(1, group.getInternalID());
+			rs = ps.executeQuery();
+			rs.next();
+			return rs.getInt(1);
+		} catch (Exception e) {
+			throw new StorageBackendException(e);
+		} finally {
+			close(null, ps, rs);
+		}
 	}
 
 	@Override
 	public boolean isArticleExisting(String messageID) throws StorageBackendException {
-		log.log(Level.SEVERE, "TODO: isArticleExisting {0}", new Object[]{messageID});
-		/** TODO: */
-		return false;
+		Long articleID = parseArticleID(messageID);
+		Long groupID = parseGroupID(messageID);
+
+		if (articleID == null || groupID == null) {
+			return false;
+		} else {
+			PreparedStatement ps = null;
+			ResultSet rs = null;
+			try {
+				ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE id = ? AND group_id = ?");
+				ps.setLong(1, articleID);
+				ps.setLong(2, groupID);
+				rs = ps.executeQuery();
+
+				rs.next();
+				return rs.getInt(1) == 1;
+			} catch (Exception e) {
+				throw new StorageBackendException(e);
+			} finally {
+				close(null, ps, rs);
+			}
+		}
 	}
 
 	//
diff -r 4653fc7609e7 -r 6e16e3bee1ca src/org/sonews/storage/impl/DrupalDatabaseProvider.java
--- a/src/org/sonews/storage/impl/DrupalDatabaseProvider.java	Sun Oct 09 01:22:18 2011 +0200
+++ b/src/org/sonews/storage/impl/DrupalDatabaseProvider.java	Tue Oct 11 00:38:41 2011 +0200
@@ -17,7 +17,6 @@
  */
 package org.sonews.storage.impl;
 
-import java.sql.SQLException;
 import java.util.Map;
 import java.util.concurrent.ConcurrentHashMap;
 import org.sonews.storage.Storage;
@@ -38,19 +37,14 @@
 	}
 
 	@Override
-	public Storage storage(Thread thread)
-			throws StorageBackendException {
-		try {
-			if (!instances.containsKey(Thread.currentThread())) {
-				DrupalDatabase db = new DrupalDatabase();
-				db.arise();
-				instances.put(Thread.currentThread(), db);
-				return db;
-			} else {
-				return instances.get(Thread.currentThread());
-			}
-		} catch (SQLException ex) {
-			throw new StorageBackendException(ex);
+	public Storage storage(Thread thread) throws StorageBackendException {
+		DrupalDatabase db = instances.get(Thread.currentThread());
+
+		if (db == null) {
+			db = new DrupalDatabase();
+			instances.put(Thread.currentThread(), db);
 		}
+
+		return db;
 	}
-}
+}
\ No newline at end of file