src/org/sonews/storage/impl/DrupalDatabase.java
author František Kučera <franta-hg@frantovo.cz>
Tue, 11 Oct 2011 01:17:30 +0200
changeset 69 b51612c18a54
parent 68 6e16e3bee1ca
child 70 2177f9b14688
permissions -rw-r--r--
Drupal: správné kódování češtiny.
     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.util.ArrayList;
    28 import java.util.Collections;
    29 import java.util.List;
    30 import java.util.logging.Level;
    31 import java.util.logging.Logger;
    32 import javax.mail.internet.MimeUtility;
    33 import org.apache.commons.codec.net.QuotedPrintableCodec;
    34 import org.sonews.config.Config;
    35 import org.sonews.feed.Subscription;
    36 import org.sonews.storage.Article;
    37 import org.sonews.storage.ArticleHead;
    38 import org.sonews.storage.Group;
    39 import org.sonews.storage.Storage;
    40 import org.sonews.storage.StorageBackendException;
    41 import org.sonews.util.Pair;
    42 
    43 /**
    44  *
    45  * @author František Kučera (frantovo.cz)
    46  */
    47 public class DrupalDatabase implements Storage {
    48 
    49 	private static final Logger log = Logger.getLogger(DrupalDatabase.class.getName());
    50 	public static final String CRLF = "\r\n";
    51 	public static final int MAX_RESTARTS = 2;
    52 	/** How many times the database connection was reinitialized */
    53 	protected int restarts = 0;
    54 	protected Connection conn = null;
    55 	private QuotedPrintableCodec qpc = new QuotedPrintableCodec("UTF-8");
    56 
    57 	public DrupalDatabase() throws StorageBackendException {
    58 		connectDatabase();
    59 	}
    60 
    61 	private void connectDatabase() throws StorageBackendException {
    62 		try {
    63 			// Load database driver
    64 			String driverClass = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object");
    65 			Class.forName(driverClass);
    66 
    67 			// Establish database connection
    68 			String url = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>");
    69 			String username = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root");
    70 			String password = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, "");
    71 			conn = DriverManager.getConnection(url, username, password);
    72 
    73 			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    74 			if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {
    75 				log.warning("Database is NOT fully serializable!");
    76 			}
    77 		} catch (Exception e) {
    78 			throw new StorageBackendException(e);
    79 		}
    80 	}
    81 
    82 	protected static void close(Connection connection, Statement statement, ResultSet resultSet) {
    83 		if (resultSet != null) {
    84 			try {
    85 				resultSet.close();
    86 			} catch (Exception e) {
    87 			}
    88 		}
    89 		if (statement != null) {
    90 			try {
    91 				statement.close();
    92 			} catch (Exception e) {
    93 			}
    94 		}
    95 		if (connection != null) {
    96 			try {
    97 				connection.close();
    98 			} catch (Exception e) {
    99 			}
   100 		}
   101 	}
   102 
   103 	/**
   104 	 * 
   105 	 * @param messageID {0}-{1}-{2}@domain.tld where {0} is nntp_id and {1} is group_id and {2} is group_name
   106 	 * @return array where [0] = nntp_id and [1] = group_id and [2] = group_name or returns null if messageID is invalid
   107 	 */
   108 	private static String[] parseMessageID(String messageID) {
   109 		if (messageID.matches("[0-9]+\\-[0-9]+\\-[a-z0-9\\.]+@.+")) {
   110 			return messageID.split("@")[0].split("\\-");
   111 		} else {
   112 			return null;
   113 		}
   114 	}
   115 
   116 	private static Long parseArticleID(String messageID) {
   117 		String[] localPart = parseMessageID(messageID);
   118 		if (localPart == null) {
   119 			return null;
   120 		} else {
   121 			return Long.parseLong(localPart[0]);
   122 		}
   123 	}
   124 
   125 	private static Long parseGroupID(String messageID) {
   126 		String[] localPart = parseMessageID(messageID);
   127 		if (localPart == null) {
   128 			return null;
   129 		} else {
   130 			return Long.parseLong(localPart[1]);
   131 		}
   132 	}
   133 
   134 	private static String parseGroupName(String messageID) {
   135 		String[] localPart = parseMessageID(messageID);
   136 		if (localPart == null) {
   137 			return null;
   138 		} else {
   139 			return localPart[2];
   140 		}
   141 	}
   142 
   143 	private static String constructHeaders(ResultSet rs) throws SQLException, UnsupportedEncodingException {
   144 		StringBuilder sb = new StringBuilder();
   145 
   146 		sb.append("Message-id: <");
   147 		sb.append(rs.getInt("id"));
   148 		sb.append("-");
   149 		sb.append(rs.getInt("group_id"));
   150 		sb.append("-");
   151 		sb.append(rs.getString("group_name"));
   152 		sb.append("@");
   153 		sb.append("nntp.kinderporno.cz>");
   154 		sb.append(CRLF);
   155 
   156 		sb.append("From: ");
   157 		sb.append(MimeUtility.encodeWord(rs.getString("sender_name")));
   158 		sb.append(" <>");
   159 		sb.append(CRLF);
   160 
   161 		sb.append("Subject: ");
   162 		sb.append(MimeUtility.encodeWord(rs.getString("subject")));
   163 		sb.append(CRLF);
   164 
   165 
   166 		sb.append("Content-Type: text/html; charset=UTF-8");
   167 		sb.append(CRLF);
   168 		sb.append("Content-Transfer-Encoding: quoted-printable");
   169 		sb.append(CRLF);
   170 
   171 		return sb.toString();
   172 	}
   173 
   174 	@Override
   175 	public List<Group> getGroups() throws StorageBackendException {
   176 		PreparedStatement ps = null;
   177 		ResultSet rs = null;
   178 		try {
   179 			ps = conn.prepareStatement("SELECT * FROM nntp_group");
   180 			rs = ps.executeQuery();
   181 			List<Group> skupiny = new ArrayList<Group>();
   182 
   183 			while (rs.next()) {
   184 				skupiny.add(new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY));
   185 			}
   186 
   187 			return skupiny;
   188 		} catch (Exception e) {
   189 			throw new StorageBackendException(e);
   190 		} finally {
   191 			close(null, ps, rs);
   192 		}
   193 	}
   194 
   195 	@Override
   196 	public Group getGroup(String name) throws StorageBackendException {
   197 		PreparedStatement ps = null;
   198 		ResultSet rs = null;
   199 		try {
   200 			ps = conn.prepareStatement("SELECT * FROM nntp_group WHERE name = ?");
   201 			ps.setString(1, name);
   202 			rs = ps.executeQuery();
   203 
   204 			while (rs.next()) {
   205 				return new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY);
   206 			}
   207 
   208 			return null;
   209 		} catch (Exception e) {
   210 			throw new StorageBackendException(e);
   211 		} finally {
   212 			close(null, ps, rs);
   213 		}
   214 	}
   215 
   216 	@Override
   217 	public boolean isGroupExisting(String groupname) throws StorageBackendException {
   218 		return getGroup(groupname) != null;
   219 	}
   220 
   221 	@Override
   222 	public Article getArticle(String messageID) throws StorageBackendException {
   223 		Long articleID = parseArticleID(messageID);
   224 		Long groupID = parseGroupID(messageID);
   225 
   226 		if (articleID == null || groupID == null) {
   227 			log.log(Level.SEVERE, "Invalid messageID: {0}", new Object[]{messageID});
   228 			return null;
   229 		} else {
   230 			return getArticle(articleID, groupID);
   231 		}
   232 	}
   233 
   234 	@Override
   235 	public Article getArticle(long articleID, long groupID) throws StorageBackendException {
   236 		PreparedStatement ps = null;
   237 		ResultSet rs = null;
   238 		try {
   239 			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE id = ? AND group_id = ?");
   240 			ps.setLong(1, articleID);
   241 			ps.setLong(2, groupID);
   242 			rs = ps.executeQuery();
   243 
   244 			if (rs.next()) {
   245 				String headers = constructHeaders(rs);
   246 				byte[] body = qpc.encode(rs.getString("text")).getBytes();
   247 
   248 				return new Article(headers, body);
   249 			} else {
   250 				return null;
   251 			}
   252 		} catch (Exception e) {
   253 			throw new StorageBackendException(e);
   254 		} finally {
   255 			close(null, ps, rs);
   256 		}
   257 	}
   258 
   259 	@Override
   260 	public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first, long last) throws StorageBackendException {
   261 		PreparedStatement ps = null;
   262 		ResultSet rs = null;
   263 		try {
   264 			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE group_id = ? AND id >= ? AND id <= ?");
   265 			ps.setLong(1, group.getInternalID());
   266 			ps.setLong(2, first);
   267 			ps.setLong(3, last);
   268 			rs = ps.executeQuery();
   269 
   270 			List<Pair<Long, ArticleHead>> heads = new ArrayList<Pair<Long, ArticleHead>>();
   271 
   272 			while (rs.next()) {
   273 				String headers = constructHeaders(rs);
   274 				heads.add(new Pair<Long, ArticleHead>(rs.getLong("id"), new ArticleHead(headers)));
   275 			}
   276 
   277 			return heads;
   278 		} catch (Exception e) {
   279 			throw new StorageBackendException(e);
   280 		} finally {
   281 			close(null, ps, rs);
   282 		}
   283 	}
   284 
   285 	@Override
   286 	public List<Pair<Long, String>> getArticleHeaders(Group group, long start, long end, String header, String pattern) throws StorageBackendException {
   287 		log.log(Level.SEVERE, "TODO: getArticleHeaders {0} / {1} / {2} / {3} / {4}", new Object[]{group, start, end, header, pattern});
   288 		/** TODO: */
   289 		return Collections.emptyList();
   290 	}
   291 
   292 	@Override
   293 	public long getArticleIndex(Article article, Group group) throws StorageBackendException {
   294 		Long id = parseArticleID(article.getMessageID());
   295 		if (id == null) {
   296 			throw new StorageBackendException("Invalid messageID: " + article.getMessageID());
   297 		} else {
   298 			return id;
   299 		}
   300 	}
   301 
   302 	@Override
   303 	public List<Long> getArticleNumbers(long groupID) throws StorageBackendException {
   304 		PreparedStatement ps = null;
   305 		ResultSet rs = null;
   306 		try {
   307 			ps = conn.prepareStatement("SELECT id FROM nntp_article WHERE group_id = ?");
   308 			ps.setLong(1, groupID);
   309 			rs = ps.executeQuery();
   310 			List<Long> articleNumbers = new ArrayList<Long>();
   311 			while (rs.next()) {
   312 				articleNumbers.add(rs.getLong(1));
   313 			}
   314 			return articleNumbers;
   315 		} catch (Exception e) {
   316 			throw new StorageBackendException(e);
   317 		} finally {
   318 			close(null, ps, rs);
   319 		}
   320 	}
   321 
   322 	@Override
   323 	public int getFirstArticleNumber(Group group) throws StorageBackendException {
   324 		PreparedStatement ps = null;
   325 		ResultSet rs = null;
   326 		try {
   327 			ps = conn.prepareStatement("SELECT min(id) FROM nntp_article WHERE group_id = ?");
   328 			ps.setLong(1, group.getInternalID());
   329 			rs = ps.executeQuery();
   330 			rs.next();
   331 			return rs.getInt(1);
   332 		} catch (Exception e) {
   333 			throw new StorageBackendException(e);
   334 		} finally {
   335 			close(null, ps, rs);
   336 		}
   337 	}
   338 
   339 	@Override
   340 	public int getLastArticleNumber(Group group) throws StorageBackendException {
   341 		PreparedStatement ps = null;
   342 		ResultSet rs = null;
   343 		try {
   344 			ps = conn.prepareStatement("SELECT max(id) FROM nntp_article WHERE group_id = ?");
   345 			ps.setLong(1, group.getInternalID());
   346 			rs = ps.executeQuery();
   347 			rs.next();
   348 			return rs.getInt(1);
   349 		} catch (Exception e) {
   350 			throw new StorageBackendException(e);
   351 		} finally {
   352 			close(null, ps, rs);
   353 		}
   354 	}
   355 
   356 	@Override
   357 	public boolean isArticleExisting(String messageID) throws StorageBackendException {
   358 		Long articleID = parseArticleID(messageID);
   359 		Long groupID = parseGroupID(messageID);
   360 
   361 		if (articleID == null || groupID == null) {
   362 			return false;
   363 		} else {
   364 			PreparedStatement ps = null;
   365 			ResultSet rs = null;
   366 			try {
   367 				ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE id = ? AND group_id = ?");
   368 				ps.setLong(1, articleID);
   369 				ps.setLong(2, groupID);
   370 				rs = ps.executeQuery();
   371 
   372 				rs.next();
   373 				return rs.getInt(1) == 1;
   374 			} catch (Exception e) {
   375 				throw new StorageBackendException(e);
   376 			} finally {
   377 				close(null, ps, rs);
   378 			}
   379 		}
   380 	}
   381 
   382 	//
   383 	// --- zatím neimplementovat ---
   384 	//
   385 	@Override
   386 	public void addArticle(Article art) throws StorageBackendException {
   387 		log.log(Level.SEVERE, "TODO: addArticle {0}", new Object[]{art});
   388 	}
   389 
   390 	@Override
   391 	public void addEvent(long timestamp, int type, long groupID) throws StorageBackendException {
   392 		log.log(Level.SEVERE, "TODO: addEvent {0} / {1} / {2}", new Object[]{timestamp, type, groupID});
   393 	}
   394 
   395 	@Override
   396 	public void addGroup(String groupname, int flags) throws StorageBackendException {
   397 		log.log(Level.SEVERE, "TODO: addGroup {0} / {1}", new Object[]{groupname, flags});
   398 	}
   399 
   400 	@Override
   401 	public int countArticles() throws StorageBackendException {
   402 		PreparedStatement ps = null;
   403 		ResultSet rs = null;
   404 		try {
   405 			ps = conn.prepareStatement("SELECT count(*) FROM nntp_article");
   406 			rs = ps.executeQuery();
   407 			rs.next();
   408 			return rs.getInt(1);
   409 		} catch (Exception e) {
   410 			throw new StorageBackendException(e);
   411 		} finally {
   412 			close(null, ps, rs);
   413 		}
   414 	}
   415 
   416 	@Override
   417 	public int countGroups() throws StorageBackendException {
   418 		PreparedStatement ps = null;
   419 		ResultSet rs = null;
   420 		try {
   421 			ps = conn.prepareStatement("SELECT count(*) FROM nntp_group");
   422 			rs = ps.executeQuery();
   423 			rs.next();
   424 			return rs.getInt(1);
   425 		} catch (Exception e) {
   426 			throw new StorageBackendException(e);
   427 		} finally {
   428 			close(null, ps, rs);
   429 		}
   430 	}
   431 
   432 	@Override
   433 	public void delete(String messageID) throws StorageBackendException {
   434 		log.log(Level.SEVERE, "TODO: delete {0}", new Object[]{messageID});
   435 	}
   436 
   437 	@Override
   438 	public String getConfigValue(String key) throws StorageBackendException {
   439 		//log.log(Level.SEVERE, "TODO: getConfigValue {0}", new Object[]{key});
   440 		return null;
   441 	}
   442 
   443 	@Override
   444 	public int getEventsCount(int eventType, long startTimestamp, long endTimestamp, Group group) throws StorageBackendException {
   445 		log.log(Level.SEVERE, "TODO: getEventsCount {0} / {1} / {2} / {3}", new Object[]{eventType, startTimestamp, endTimestamp, group});
   446 		return 0;
   447 	}
   448 
   449 	@Override
   450 	public double getEventsPerHour(int key, long gid) throws StorageBackendException {
   451 		log.log(Level.SEVERE, "TODO: getEventsPerHour {0} / {1}", new Object[]{key, gid});
   452 		return 0;
   453 	}
   454 
   455 	@Override
   456 	public List<String> getGroupsForList(String listAddress) throws StorageBackendException {
   457 		log.log(Level.SEVERE, "TODO: getGroupsForList {0}", new Object[]{listAddress});
   458 		return Collections.emptyList();
   459 	}
   460 
   461 	@Override
   462 	public List<String> getListsForGroup(String groupname) throws StorageBackendException {
   463 		log.log(Level.SEVERE, "TODO: getListsForGroup {0}", new Object[]{groupname});
   464 		return Collections.emptyList();
   465 	}
   466 
   467 	@Override
   468 	public String getOldestArticle() throws StorageBackendException {
   469 		log.log(Level.SEVERE, "TODO: getOldestArticle");
   470 		return null;
   471 	}
   472 
   473 	@Override
   474 	public int getPostingsCount(String groupname) throws StorageBackendException {
   475 		PreparedStatement ps = null;
   476 		ResultSet rs = null;
   477 		try {
   478 			ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE group_name = ?");
   479 			ps.setString(1, groupname);
   480 			rs = ps.executeQuery();
   481 			rs.next();
   482 			return rs.getInt(1);
   483 		} catch (Exception e) {
   484 			throw new StorageBackendException(e);
   485 		} finally {
   486 			close(null, ps, rs);
   487 		}
   488 	}
   489 
   490 	@Override
   491 	public List<Subscription> getSubscriptions(int type) throws StorageBackendException {
   492 		log.log(Level.SEVERE, "TODO: getSubscriptions {0}", new Object[]{type});
   493 		return Collections.emptyList();
   494 	}
   495 
   496 	@Override
   497 	public void purgeGroup(Group group) throws StorageBackendException {
   498 		log.log(Level.SEVERE, "TODO: purgeGroup {0}", new Object[]{group});
   499 	}
   500 
   501 	@Override
   502 	public void setConfigValue(String key, String value) throws StorageBackendException {
   503 		log.log(Level.SEVERE, "TODO: setConfigValue {0} = {1}", new Object[]{key, value});
   504 	}
   505 
   506 	@Override
   507 	public boolean update(Article article) throws StorageBackendException {
   508 		log.log(Level.SEVERE, "TODO: update {0}", new Object[]{article});
   509 		throw new StorageBackendException("Not implemented yet.");
   510 	}
   511 
   512 	@Override
   513 	public boolean update(Group group) throws StorageBackendException {
   514 		log.log(Level.SEVERE, "TODO: update {0}", new Object[]{group});
   515 		throw new StorageBackendException("Not implemented yet.");
   516 	}
   517 }