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