src/org/sonews/storage/impl/DrupalDatabase.java
author František Kučera <franta-hg@frantovo.cz>
Tue, 11 Oct 2011 16:34:17 +0200
changeset 71 beb11d70f0eb
parent 70 2177f9b14688
child 72 aae4b4688700
permissions -rw-r--r--
Drupal: správný formát data (RFC 822)
     1 /*
     2  *   SONEWS News Server
     3  *   see AUTHORS for the list of contributors
     4  *
     5  *   This program is free software: you can redistribute it and/or modify
     6  *   it under the terms of the GNU General Public License as published by
     7  *   the Free Software Foundation, either version 3 of the License, or
     8  *   (at your option) any later version.
     9  *
    10  *   This program is distributed in the hope that it will be useful,
    11  *   but WITHOUT ANY WARRANTY; without even the implied warranty of
    12  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    13  *   GNU General Public License for more details.
    14  *
    15  *   You should have received a copy of the GNU General Public License
    16  *   along with this program.  If not, see <http://www.gnu.org/licenses/>.
    17  */
    18 package org.sonews.storage.impl;
    19 
    20 import java.io.UnsupportedEncodingException;
    21 import java.sql.Connection;
    22 import java.sql.DriverManager;
    23 import java.sql.PreparedStatement;
    24 import java.sql.ResultSet;
    25 import java.sql.SQLException;
    26 import java.sql.Statement;
    27 import java.text.SimpleDateFormat;
    28 import java.util.ArrayList;
    29 import java.util.Collections;
    30 import java.util.Date;
    31 import java.util.List;
    32 import java.util.Locale;
    33 import java.util.logging.Level;
    34 import java.util.logging.Logger;
    35 import javax.mail.internet.MailDateFormat;
    36 import javax.mail.internet.MimeUtility;
    37 import org.apache.commons.codec.net.BCodec;
    38 import org.apache.commons.codec.net.QuotedPrintableCodec;
    39 import org.sonews.config.Config;
    40 import org.sonews.feed.Subscription;
    41 import org.sonews.storage.Article;
    42 import org.sonews.storage.ArticleHead;
    43 import org.sonews.storage.Group;
    44 import org.sonews.storage.Storage;
    45 import org.sonews.storage.StorageBackendException;
    46 import org.sonews.util.Pair;
    47 
    48 /**
    49  *
    50  * @author František Kučera (frantovo.cz)
    51  */
    52 public class DrupalDatabase implements Storage {
    53 
    54 	private static final Logger log = Logger.getLogger(DrupalDatabase.class.getName());
    55 	public static final String CHARSET = "UTF-8";
    56 	public static final String CRLF = "\r\n";
    57 	public static final int MAX_RESTARTS = 2;
    58 	/** How many times the database connection was reinitialized */
    59 	protected int restarts = 0;
    60 	protected Connection conn = null;
    61 	private QuotedPrintableCodec qpc = new QuotedPrintableCodec(CHARSET);
    62 	private SimpleDateFormat RFC822_DATE = new SimpleDateFormat("EEE', 'dd' 'MMM' 'yyyy' 'HH:mm:ss' 'Z", Locale.US);
    63 	// TODO: správná doména
    64 	private String myDomain = "kinderporno.cz";
    65 
    66 	public DrupalDatabase() throws StorageBackendException {
    67 		connectDatabase();
    68 	}
    69 
    70 	private void connectDatabase() throws StorageBackendException {
    71 		try {
    72 			// Load database driver
    73 			String driverClass = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object");
    74 			Class.forName(driverClass);
    75 
    76 			// Establish database connection
    77 			String url = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>");
    78 			String username = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root");
    79 			String password = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, "");
    80 			conn = DriverManager.getConnection(url, username, password);
    81 
    82 			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    83 			if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {
    84 				log.warning("Database is NOT fully serializable!");
    85 			}
    86 		} catch (Exception e) {
    87 			throw new StorageBackendException(e);
    88 		}
    89 	}
    90 
    91 	protected static void close(Connection connection, Statement statement, ResultSet resultSet) {
    92 		if (resultSet != null) {
    93 			try {
    94 				resultSet.close();
    95 			} catch (Exception e) {
    96 			}
    97 		}
    98 		if (statement != null) {
    99 			try {
   100 				statement.close();
   101 			} catch (Exception e) {
   102 			}
   103 		}
   104 		if (connection != null) {
   105 			try {
   106 				connection.close();
   107 			} catch (Exception e) {
   108 			}
   109 		}
   110 	}
   111 
   112 	/**
   113 	 * 
   114 	 * @param messageID &lt;{0}-{1}-{2}@domain.tld&gt; where {0} is nntp_id and {1} is group_id and {2} is group_name
   115 	 * @return array where [0] = nntp_id and [1] = group_id and [2] = group_name or returns null if messageID is invalid
   116 	 */
   117 	private static String[] parseMessageID(String messageID) {
   118 		if (messageID.matches("<[0-9]+\\-[0-9]+\\-[a-z0-9\\.]+@.+>")) {
   119 			return messageID.substring(1).split("@")[0].split("\\-");
   120 		} else {
   121 			return null;
   122 		}
   123 	}
   124 
   125 	private static Long parseArticleID(String messageID) {
   126 		String[] localPart = parseMessageID(messageID);
   127 		if (localPart == null) {
   128 			return null;
   129 		} else {
   130 			return Long.parseLong(localPart[0]);
   131 		}
   132 	}
   133 
   134 	private static Long parseGroupID(String messageID) {
   135 		String[] localPart = parseMessageID(messageID);
   136 		if (localPart == null) {
   137 			return null;
   138 		} else {
   139 			return Long.parseLong(localPart[1]);
   140 		}
   141 	}
   142 
   143 	private static String parseGroupName(String messageID) {
   144 		String[] localPart = parseMessageID(messageID);
   145 		if (localPart == null) {
   146 			return null;
   147 		} else {
   148 			return localPart[2];
   149 		}
   150 	}
   151 
   152 	private static String constructMessageId(int articleID, int groupID, String groupName, String domainName) {
   153 		StringBuilder sb = new StringBuilder();
   154 		sb.append("<");
   155 		sb.append(articleID);
   156 		sb.append("-");
   157 		sb.append(groupID);
   158 		sb.append("-");
   159 		sb.append(groupName);
   160 		sb.append("@");
   161 		sb.append(domainName);
   162 		sb.append(">");
   163 		return sb.toString();
   164 	}
   165 
   166 	/**
   167 	 * 
   168 	 * @param sb header list to be appended with new header. List must be terminated by line end.
   169 	 * @param key header name (without : and space)
   170 	 * @param value header value
   171 	 * @param encode true if value should be encoded/escaped before appending
   172 	 * @throws UnsupportedEncodingException 
   173 	 */
   174 	private static void addHeader(StringBuilder sb, String key, String value, boolean encode) throws UnsupportedEncodingException {
   175 		sb.append(key);
   176 		sb.append(": ");
   177 		if (encode) {
   178 			sb.append(MimeUtility.encodeWord(value));
   179 		} else {
   180 			sb.append(value);
   181 		}
   182 		sb.append(CRLF);
   183 	}
   184 
   185 	private String constructHeaders(ResultSet rs) throws SQLException, UnsupportedEncodingException {
   186 		StringBuilder sb = new StringBuilder();
   187 
   188 		addHeader(sb, "Message-id", constructMessageId(rs.getInt("id"), rs.getInt("group_id"), rs.getString("group_name"), myDomain), false);
   189 		addHeader(sb, "From", MimeUtility.encodeWord(rs.getString("sender_name")) + " <>", false);
   190 		addHeader(sb, "Subject", rs.getString("subject"), true);
   191 		/** TODO: správný formát data: */
   192 		addHeader(sb, "Date", RFC822_DATE.format(new Date(rs.getLong("created"))), false);
   193 		addHeader(sb, "Content-Type", "text/html; charset=" + CHARSET, false);
   194 		addHeader(sb, "Content-Transfer-Encoding", "quoted-printable", false);
   195 		//addHeader(sb, "Content-Transfer-Encoding", "base64", false);
   196 
   197 		Integer parentID = rs.getInt("parent_id");
   198 		if (parentID != null && parentID > 0) {
   199 			String parentMessageID = constructMessageId(parentID, rs.getInt("group_id"), rs.getString("group_name"), myDomain);
   200 			addHeader(sb, "In-Reply-To", parentMessageID, false);
   201 			addHeader(sb, "References", parentMessageID, false);
   202 		}
   203 
   204 		return sb.toString();
   205 	}
   206 
   207 	@Override
   208 	public List<Group> getGroups() throws StorageBackendException {
   209 		PreparedStatement ps = null;
   210 		ResultSet rs = null;
   211 		try {
   212 			ps = conn.prepareStatement("SELECT * FROM nntp_group");
   213 			rs = ps.executeQuery();
   214 			List<Group> skupiny = new ArrayList<Group>();
   215 
   216 			while (rs.next()) {
   217 				skupiny.add(new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY));
   218 			}
   219 
   220 			return skupiny;
   221 		} catch (Exception e) {
   222 			throw new StorageBackendException(e);
   223 		} finally {
   224 			close(null, ps, rs);
   225 		}
   226 	}
   227 
   228 	@Override
   229 	public Group getGroup(String name) throws StorageBackendException {
   230 		PreparedStatement ps = null;
   231 		ResultSet rs = null;
   232 		try {
   233 			ps = conn.prepareStatement("SELECT * FROM nntp_group WHERE name = ?");
   234 			ps.setString(1, name);
   235 			rs = ps.executeQuery();
   236 
   237 			while (rs.next()) {
   238 				return new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY);
   239 			}
   240 
   241 			return null;
   242 		} catch (Exception e) {
   243 			throw new StorageBackendException(e);
   244 		} finally {
   245 			close(null, ps, rs);
   246 		}
   247 	}
   248 
   249 	@Override
   250 	public boolean isGroupExisting(String groupname) throws StorageBackendException {
   251 		return getGroup(groupname) != null;
   252 	}
   253 
   254 	@Override
   255 	public Article getArticle(String messageID) throws StorageBackendException {
   256 		Long articleID = parseArticleID(messageID);
   257 		Long groupID = parseGroupID(messageID);
   258 
   259 		if (articleID == null || groupID == null) {
   260 			log.log(Level.SEVERE, "Invalid messageID: {0}", new Object[]{messageID});
   261 			return null;
   262 		} else {
   263 			return getArticle(articleID, groupID);
   264 		}
   265 	}
   266 
   267 	@Override
   268 	public Article getArticle(long articleID, long groupID) throws StorageBackendException {
   269 		PreparedStatement ps = null;
   270 		ResultSet rs = null;
   271 		try {
   272 			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE id = ? AND group_id = ?");
   273 			ps.setLong(1, articleID);
   274 			ps.setLong(2, groupID);
   275 			rs = ps.executeQuery();
   276 
   277 			if (rs.next()) {
   278 				String headers = constructHeaders(rs);
   279 				// TODO: fold?
   280 				BCodec bc = new BCodec(CHARSET);
   281 				byte[] body = qpc.encode(rs.getString("text")).getBytes();
   282 				//byte[] body = bc.encode(rs.getString("text")).getBytes();
   283 
   284 				return new Article(headers, body);
   285 			} else {
   286 				return null;
   287 			}
   288 		} catch (Exception e) {
   289 			throw new StorageBackendException(e);
   290 		} finally {
   291 			close(null, ps, rs);
   292 		}
   293 	}
   294 
   295 	@Override
   296 	public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first, long last) throws StorageBackendException {
   297 		PreparedStatement ps = null;
   298 		ResultSet rs = null;
   299 		try {
   300 			// TODO: je nutné řazení?
   301 			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE group_id = ? AND id >= ? AND id <= ? ORDER BY id");
   302 			ps.setLong(1, group.getInternalID());
   303 			ps.setLong(2, first);
   304 			ps.setLong(3, last);
   305 			rs = ps.executeQuery();
   306 
   307 			List<Pair<Long, ArticleHead>> heads = new ArrayList<Pair<Long, ArticleHead>>();
   308 
   309 			while (rs.next()) {
   310 				String headers = constructHeaders(rs);
   311 				heads.add(new Pair<Long, ArticleHead>(rs.getLong("id"), new ArticleHead(headers)));
   312 			}
   313 
   314 			return heads;
   315 		} catch (Exception e) {
   316 			throw new StorageBackendException(e);
   317 		} finally {
   318 			close(null, ps, rs);
   319 		}
   320 	}
   321 
   322 	@Override
   323 	public List<Pair<Long, String>> getArticleHeaders(Group group, long start, long end, String header, String pattern) throws StorageBackendException {
   324 		log.log(Level.SEVERE, "TODO: getArticleHeaders {0} / {1} / {2} / {3} / {4}", new Object[]{group, start, end, header, pattern});
   325 		/** TODO: */
   326 		return Collections.emptyList();
   327 	}
   328 
   329 	@Override
   330 	public long getArticleIndex(Article article, Group group) throws StorageBackendException {
   331 		Long id = parseArticleID(article.getMessageID());
   332 		if (id == null) {
   333 			throw new StorageBackendException("Invalid messageID: " + article.getMessageID());
   334 		} else {
   335 			return id;
   336 		}
   337 	}
   338 
   339 	@Override
   340 	public List<Long> getArticleNumbers(long groupID) throws StorageBackendException {
   341 		PreparedStatement ps = null;
   342 		ResultSet rs = null;
   343 		try {
   344 			ps = conn.prepareStatement("SELECT id FROM nntp_article WHERE group_id = ?");
   345 			ps.setLong(1, groupID);
   346 			rs = ps.executeQuery();
   347 			List<Long> articleNumbers = new ArrayList<Long>();
   348 			while (rs.next()) {
   349 				articleNumbers.add(rs.getLong(1));
   350 			}
   351 			return articleNumbers;
   352 		} catch (Exception e) {
   353 			throw new StorageBackendException(e);
   354 		} finally {
   355 			close(null, ps, rs);
   356 		}
   357 	}
   358 
   359 	@Override
   360 	public int getFirstArticleNumber(Group group) throws StorageBackendException {
   361 		PreparedStatement ps = null;
   362 		ResultSet rs = null;
   363 		try {
   364 			ps = conn.prepareStatement("SELECT min(id) FROM nntp_article WHERE group_id = ?");
   365 			ps.setLong(1, group.getInternalID());
   366 			rs = ps.executeQuery();
   367 			rs.next();
   368 			return rs.getInt(1);
   369 		} catch (Exception e) {
   370 			throw new StorageBackendException(e);
   371 		} finally {
   372 			close(null, ps, rs);
   373 		}
   374 	}
   375 
   376 	@Override
   377 	public int getLastArticleNumber(Group group) throws StorageBackendException {
   378 		PreparedStatement ps = null;
   379 		ResultSet rs = null;
   380 		try {
   381 			ps = conn.prepareStatement("SELECT max(id) FROM nntp_article WHERE group_id = ?");
   382 			ps.setLong(1, group.getInternalID());
   383 			rs = ps.executeQuery();
   384 			rs.next();
   385 			return rs.getInt(1);
   386 		} catch (Exception e) {
   387 			throw new StorageBackendException(e);
   388 		} finally {
   389 			close(null, ps, rs);
   390 		}
   391 	}
   392 
   393 	@Override
   394 	public boolean isArticleExisting(String messageID) throws StorageBackendException {
   395 		Long articleID = parseArticleID(messageID);
   396 		Long groupID = parseGroupID(messageID);
   397 
   398 		if (articleID == null || groupID == null) {
   399 			return false;
   400 		} else {
   401 			PreparedStatement ps = null;
   402 			ResultSet rs = null;
   403 			try {
   404 				ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE id = ? AND group_id = ?");
   405 				ps.setLong(1, articleID);
   406 				ps.setLong(2, groupID);
   407 				rs = ps.executeQuery();
   408 
   409 				rs.next();
   410 				return rs.getInt(1) == 1;
   411 			} catch (Exception e) {
   412 				throw new StorageBackendException(e);
   413 			} finally {
   414 				close(null, ps, rs);
   415 			}
   416 		}
   417 	}
   418 
   419 	//
   420 	// --- zatím neimplementovat ---
   421 	//
   422 	@Override
   423 	public void addArticle(Article art) throws StorageBackendException {
   424 		log.log(Level.SEVERE, "TODO: addArticle {0}", new Object[]{art});
   425 	}
   426 
   427 	@Override
   428 	public void addEvent(long timestamp, int type, long groupID) throws StorageBackendException {
   429 		log.log(Level.SEVERE, "TODO: addEvent {0} / {1} / {2}", new Object[]{timestamp, type, groupID});
   430 	}
   431 
   432 	@Override
   433 	public void addGroup(String groupname, int flags) throws StorageBackendException {
   434 		log.log(Level.SEVERE, "TODO: addGroup {0} / {1}", new Object[]{groupname, flags});
   435 	}
   436 
   437 	@Override
   438 	public int countArticles() throws StorageBackendException {
   439 		PreparedStatement ps = null;
   440 		ResultSet rs = null;
   441 		try {
   442 			ps = conn.prepareStatement("SELECT count(*) FROM nntp_article");
   443 			rs = ps.executeQuery();
   444 			rs.next();
   445 			return rs.getInt(1);
   446 		} catch (Exception e) {
   447 			throw new StorageBackendException(e);
   448 		} finally {
   449 			close(null, ps, rs);
   450 		}
   451 	}
   452 
   453 	@Override
   454 	public int countGroups() throws StorageBackendException {
   455 		PreparedStatement ps = null;
   456 		ResultSet rs = null;
   457 		try {
   458 			ps = conn.prepareStatement("SELECT count(*) FROM nntp_group");
   459 			rs = ps.executeQuery();
   460 			rs.next();
   461 			return rs.getInt(1);
   462 		} catch (Exception e) {
   463 			throw new StorageBackendException(e);
   464 		} finally {
   465 			close(null, ps, rs);
   466 		}
   467 	}
   468 
   469 	@Override
   470 	public void delete(String messageID) throws StorageBackendException {
   471 		log.log(Level.SEVERE, "TODO: delete {0}", new Object[]{messageID});
   472 	}
   473 
   474 	@Override
   475 	public String getConfigValue(String key) throws StorageBackendException {
   476 		//log.log(Level.SEVERE, "TODO: getConfigValue {0}", new Object[]{key});
   477 		return null;
   478 	}
   479 
   480 	@Override
   481 	public int getEventsCount(int eventType, long startTimestamp, long endTimestamp, Group group) throws StorageBackendException {
   482 		log.log(Level.SEVERE, "TODO: getEventsCount {0} / {1} / {2} / {3}", new Object[]{eventType, startTimestamp, endTimestamp, group});
   483 		return 0;
   484 	}
   485 
   486 	@Override
   487 	public double getEventsPerHour(int key, long gid) throws StorageBackendException {
   488 		log.log(Level.SEVERE, "TODO: getEventsPerHour {0} / {1}", new Object[]{key, gid});
   489 		return 0;
   490 	}
   491 
   492 	@Override
   493 	public List<String> getGroupsForList(String listAddress) throws StorageBackendException {
   494 		log.log(Level.SEVERE, "TODO: getGroupsForList {0}", new Object[]{listAddress});
   495 		return Collections.emptyList();
   496 	}
   497 
   498 	@Override
   499 	public List<String> getListsForGroup(String groupname) throws StorageBackendException {
   500 		log.log(Level.SEVERE, "TODO: getListsForGroup {0}", new Object[]{groupname});
   501 		return Collections.emptyList();
   502 	}
   503 
   504 	@Override
   505 	public String getOldestArticle() throws StorageBackendException {
   506 		log.log(Level.SEVERE, "TODO: getOldestArticle");
   507 		return null;
   508 	}
   509 
   510 	@Override
   511 	public int getPostingsCount(String groupname) throws StorageBackendException {
   512 		PreparedStatement ps = null;
   513 		ResultSet rs = null;
   514 		try {
   515 			ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE group_name = ?");
   516 			ps.setString(1, groupname);
   517 			rs = ps.executeQuery();
   518 			rs.next();
   519 			return rs.getInt(1);
   520 		} catch (Exception e) {
   521 			throw new StorageBackendException(e);
   522 		} finally {
   523 			close(null, ps, rs);
   524 		}
   525 	}
   526 
   527 	@Override
   528 	public List<Subscription> getSubscriptions(int type) throws StorageBackendException {
   529 		log.log(Level.SEVERE, "TODO: getSubscriptions {0}", new Object[]{type});
   530 		return Collections.emptyList();
   531 	}
   532 
   533 	@Override
   534 	public void purgeGroup(Group group) throws StorageBackendException {
   535 		log.log(Level.SEVERE, "TODO: purgeGroup {0}", new Object[]{group});
   536 	}
   537 
   538 	@Override
   539 	public void setConfigValue(String key, String value) throws StorageBackendException {
   540 		log.log(Level.SEVERE, "TODO: setConfigValue {0} = {1}", new Object[]{key, value});
   541 	}
   542 
   543 	@Override
   544 	public boolean update(Article article) throws StorageBackendException {
   545 		log.log(Level.SEVERE, "TODO: update {0}", new Object[]{article});
   546 		throw new StorageBackendException("Not implemented yet.");
   547 	}
   548 
   549 	@Override
   550 	public boolean update(Group group) throws StorageBackendException {
   551 		log.log(Level.SEVERE, "TODO: update {0}", new Object[]{group});
   552 		throw new StorageBackendException("Not implemented yet.");
   553 	}
   554 }