src/org/sonews/storage/impl/DrupalDatabase.java
author František Kučera <franta-hg@frantovo.cz>
Wed Dec 31 12:07:40 2014 +0100 (2014-12-31)
changeset 120 bb1c8a7b774c
parent 106 dc04a3c2c557
permissions -rw-r--r--
XSLT: licence – GNU GPLv3
     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.sql.Connection;
    21 import java.sql.DriverManager;
    22 import java.sql.PreparedStatement;
    23 import java.sql.ResultSet;
    24 import java.sql.Statement;
    25 import java.util.ArrayList;
    26 import java.util.Collections;
    27 import java.util.List;
    28 import java.util.logging.Level;
    29 import java.util.logging.Logger;
    30 import org.sonews.config.Config;
    31 import org.sonews.feed.Subscription;
    32 import org.sonews.storage.Article;
    33 import org.sonews.storage.ArticleHead;
    34 import org.sonews.storage.DrupalArticle;
    35 import org.sonews.storage.DrupalMessage;
    36 import static org.sonews.storage.DrupalMessage.parseArticleID;
    37 import static org.sonews.storage.DrupalMessage.parseGroupID;
    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 CHARSET = "UTF-8";
    51 	public static final String CRLF = "\r\n";
    52 	protected Connection conn = null;
    53 	// TODO: správná doména
    54 	private String myDomain = "nntp.i1984.cz";
    55 
    56 	public DrupalDatabase() throws StorageBackendException {
    57 		connectDatabase();
    58 	}
    59 
    60 	private void connectDatabase() throws StorageBackendException {
    61 		try {
    62 			// Load database driver
    63 			String driverClass = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object");
    64 			Class.forName(driverClass);
    65 
    66 			// Establish database connection
    67 			String url = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "<not specified>");
    68 			String username = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root");
    69 			String password = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, "");
    70 			conn = DriverManager.getConnection(url, username, password);
    71 
    72 			/**
    73 			 * Kódování češtiny:
    74 			 * SET NAMES utf8 → dobrá čeština
    75 			 *		Client characterset:    utf8
    76 			 *		Conn.  characterset:    utf8
    77 			 * SET CHARACTER SET utf8; → dobrá čeština jen pro SLECT, ale při volání funkce se zmrší
    78 			 *		Client characterset:    utf8
    79 			 *		Conn.  characterset:    latin1
    80 			 * 
    81 			 * Správné řešení:
    82 			 *		V JDBC URL musí být: ?useUnicode=true&characterEncoding=UTF-8
    83 			 */
    84 			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    85 			if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {
    86 				log.warning("Database is NOT fully serializable!");
    87 			}
    88 		} catch (Exception e) {
    89 			throw new StorageBackendException(e);
    90 		}
    91 	}
    92 
    93 	protected static void close(Connection connection, Statement statement, ResultSet resultSet) {
    94 		if (resultSet != null) {
    95 			try {
    96 				resultSet.close();
    97 			} catch (Exception e) {
    98 			}
    99 		}
   100 		if (statement != null) {
   101 			try {
   102 				statement.close();
   103 			} catch (Exception e) {
   104 			}
   105 		}
   106 		if (connection != null) {
   107 			try {
   108 				connection.close();
   109 			} catch (Exception e) {
   110 			}
   111 		}
   112 	}
   113 
   114 	@Override
   115 	public List<Group> getGroups() throws StorageBackendException {
   116 		PreparedStatement ps = null;
   117 		ResultSet rs = null;
   118 		try {
   119 			ps = conn.prepareStatement("SELECT * FROM nntp_group");
   120 			rs = ps.executeQuery();
   121 			List<Group> skupiny = new ArrayList<Group>();
   122 
   123 			while (rs.next()) {
   124 				skupiny.add(new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY));
   125 			}
   126 
   127 			return skupiny;
   128 		} catch (Exception e) {
   129 			throw new StorageBackendException(e);
   130 		} finally {
   131 			close(null, ps, rs);
   132 		}
   133 	}
   134 
   135 	@Override
   136 	public Group getGroup(String name) throws StorageBackendException {
   137 		PreparedStatement ps = null;
   138 		ResultSet rs = null;
   139 		try {
   140 			ps = conn.prepareStatement("SELECT * FROM nntp_group WHERE name = ?");
   141 			ps.setString(1, name);
   142 			rs = ps.executeQuery();
   143 
   144 			while (rs.next()) {
   145 				return new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY);
   146 			}
   147 
   148 			return null;
   149 		} catch (Exception e) {
   150 			throw new StorageBackendException(e);
   151 		} finally {
   152 			close(null, ps, rs);
   153 		}
   154 	}
   155 
   156 	@Override
   157 	public boolean isGroupExisting(String groupname) throws StorageBackendException {
   158 		return getGroup(groupname) != null;
   159 	}
   160 
   161 	@Override
   162 	public Article getArticle(String messageID) throws StorageBackendException {
   163 		Long articleID = parseArticleID(messageID);
   164 		Long groupID = parseGroupID(messageID);
   165 
   166 		if (articleID == null || groupID == null) {
   167 			log.log(Level.SEVERE, "Invalid messageID: {0}", new Object[]{messageID});
   168 			return null;
   169 		} else {
   170 			return getArticle(articleID, groupID);
   171 		}
   172 	}
   173 
   174 	@Override
   175 	public Article getArticle(long articleID, long groupID) throws StorageBackendException {
   176 		PreparedStatement ps = null;
   177 		ResultSet rs = null;
   178 		try {
   179 			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE id = ? AND group_id = ?");
   180 			ps.setLong(1, articleID);
   181 			ps.setLong(2, groupID);
   182 			rs = ps.executeQuery();
   183 
   184 			if (rs.next()) {
   185 				DrupalMessage m = new DrupalMessage(rs, myDomain, true);
   186 				return new DrupalArticle(m);
   187 			} else {
   188 				return null;
   189 			}
   190 		} catch (Exception e) {
   191 			throw new StorageBackendException(e);
   192 		} finally {
   193 			close(null, ps, rs);
   194 		}
   195 	}
   196 
   197 	@Override
   198 	public List<Pair<Long, ArticleHead>> getArticleHeads(Group group, long first, long last) throws StorageBackendException {
   199 		PreparedStatement ps = null;
   200 		ResultSet rs = null;
   201 		try {
   202 			ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE group_id = ? AND id >= ? AND id <= ? ORDER BY id");
   203 			ps.setLong(1, group.getInternalID());
   204 			ps.setLong(2, first);
   205 			ps.setLong(3, last);
   206 			rs = ps.executeQuery();
   207 
   208 			List<Pair<Long, ArticleHead>> heads = new ArrayList<Pair<Long, ArticleHead>>();
   209 
   210 			while (rs.next()) {
   211 				DrupalMessage m = new DrupalMessage(rs, myDomain, false);
   212 				String headers = m.getHeaders();
   213 				heads.add(new Pair<Long, ArticleHead>(rs.getLong("id"), new ArticleHead(headers)));
   214 			}
   215 
   216 			return heads;
   217 		} catch (Exception e) {
   218 			throw new StorageBackendException(e);
   219 		} finally {
   220 			close(null, ps, rs);
   221 		}
   222 	}
   223 
   224 	@Override
   225 	public long getArticleIndex(Article article, Group group) throws StorageBackendException {
   226 		Long id = parseArticleID(article.getMessageID());
   227 		if (id == null) {
   228 			throw new StorageBackendException("Invalid messageID: " + article.getMessageID());
   229 		} else {
   230 			return id;
   231 		}
   232 	}
   233 
   234 	@Override
   235 	public List<Long> getArticleNumbers(long groupID) throws StorageBackendException {
   236 		PreparedStatement ps = null;
   237 		ResultSet rs = null;
   238 		try {
   239 			ps = conn.prepareStatement("SELECT id FROM nntp_article WHERE group_id = ?");
   240 			ps.setLong(1, groupID);
   241 			rs = ps.executeQuery();
   242 			List<Long> articleNumbers = new ArrayList<Long>();
   243 			while (rs.next()) {
   244 				articleNumbers.add(rs.getLong(1));
   245 			}
   246 			return articleNumbers;
   247 		} catch (Exception e) {
   248 			throw new StorageBackendException(e);
   249 		} finally {
   250 			close(null, ps, rs);
   251 		}
   252 	}
   253 
   254 	@Override
   255 	public int getFirstArticleNumber(Group group) throws StorageBackendException {
   256 		PreparedStatement ps = null;
   257 		ResultSet rs = null;
   258 		try {
   259 			ps = conn.prepareStatement("SELECT min(id) FROM nntp_article WHERE group_id = ?");
   260 			ps.setLong(1, group.getInternalID());
   261 			rs = ps.executeQuery();
   262 			rs.next();
   263 			return rs.getInt(1);
   264 		} catch (Exception e) {
   265 			throw new StorageBackendException(e);
   266 		} finally {
   267 			close(null, ps, rs);
   268 		}
   269 	}
   270 
   271 	@Override
   272 	public int getLastArticleNumber(Group group) throws StorageBackendException {
   273 		PreparedStatement ps = null;
   274 		ResultSet rs = null;
   275 		try {
   276 			ps = conn.prepareStatement("SELECT max(id) FROM nntp_article WHERE group_id = ?");
   277 			ps.setLong(1, group.getInternalID());
   278 			rs = ps.executeQuery();
   279 			rs.next();
   280 			return rs.getInt(1);
   281 		} catch (Exception e) {
   282 			throw new StorageBackendException(e);
   283 		} finally {
   284 			close(null, ps, rs);
   285 		}
   286 	}
   287 
   288 	@Override
   289 	public boolean isArticleExisting(String messageID) throws StorageBackendException {
   290 		Long articleID = parseArticleID(messageID);
   291 		Long groupID = parseGroupID(messageID);
   292 
   293 		if (articleID == null || groupID == null) {
   294 			return false;
   295 		} else {
   296 			PreparedStatement ps = null;
   297 			ResultSet rs = null;
   298 			try {
   299 				ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE id = ? AND group_id = ?");
   300 				ps.setLong(1, articleID);
   301 				ps.setLong(2, groupID);
   302 				rs = ps.executeQuery();
   303 
   304 				rs.next();
   305 				return rs.getInt(1) == 1;
   306 			} catch (Exception e) {
   307 				throw new StorageBackendException(e);
   308 			} finally {
   309 				close(null, ps, rs);
   310 			}
   311 		}
   312 	}
   313 
   314 	@Override
   315 	public int countArticles() throws StorageBackendException {
   316 		PreparedStatement ps = null;
   317 		ResultSet rs = null;
   318 		try {
   319 			ps = conn.prepareStatement("SELECT count(*) FROM nntp_article");
   320 			rs = ps.executeQuery();
   321 			rs.next();
   322 			return rs.getInt(1);
   323 		} catch (Exception e) {
   324 			throw new StorageBackendException(e);
   325 		} finally {
   326 			close(null, ps, rs);
   327 		}
   328 	}
   329 
   330 	@Override
   331 	public int countGroups() throws StorageBackendException {
   332 		PreparedStatement ps = null;
   333 		ResultSet rs = null;
   334 		try {
   335 			ps = conn.prepareStatement("SELECT count(*) FROM nntp_group");
   336 			rs = ps.executeQuery();
   337 			rs.next();
   338 			return rs.getInt(1);
   339 		} catch (Exception e) {
   340 			throw new StorageBackendException(e);
   341 		} finally {
   342 			close(null, ps, rs);
   343 		}
   344 	}
   345 
   346 	@Override
   347 	public int getPostingsCount(String groupname) throws StorageBackendException {
   348 		PreparedStatement ps = null;
   349 		ResultSet rs = null;
   350 		try {
   351 			ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE group_name = ?");
   352 			ps.setString(1, groupname);
   353 			rs = ps.executeQuery();
   354 			rs.next();
   355 			return rs.getInt(1);
   356 		} catch (Exception e) {
   357 			throw new StorageBackendException(e);
   358 		} finally {
   359 			close(null, ps, rs);
   360 		}
   361 	}
   362 
   363 	@Override
   364 	public List<Pair<Long, String>> getArticleHeaders(Group group, long start, long end, String header, String pattern) throws StorageBackendException {
   365 		log.log(Level.SEVERE, "TODO: getArticleHeaders {0} / {1} / {2} / {3} / {4}", new Object[]{group, start, end, header, pattern});
   366 		/** TODO: */
   367 		return Collections.emptyList();
   368 	}
   369 
   370 	/**
   371 	 * Checks username and password.
   372 	 * @param username
   373 	 * @param password
   374 	 * @return true if credentials are valid | false otherwise
   375 	 * @throws StorageBackendException it there is any error during authentication process 
   376 	 * (but should not be thrown if only bad thing is wrong username or password)
   377 	 */
   378 	@Override
   379 	public boolean authenticateUser(String username,
   380 			char[] password) throws StorageBackendException {
   381 		PreparedStatement ps = null;
   382 		ResultSet rs = null;
   383 		try {
   384 			ps = conn.prepareStatement("SELECT nntp_login(?, ?)");
   385 			ps.setString(1, username);
   386 			ps.setString(2, String.copyValueOf(password));
   387 			rs = ps.executeQuery();
   388 			rs.next();
   389 			return rs.getInt(1) == 1;
   390 		} catch (Exception e) {
   391 			throw new StorageBackendException(e);
   392 		} finally {
   393 			close(null, ps, rs);
   394 		}
   395 	}
   396 
   397 	/**
   398 	 * Validates article and if OK, calls {@link #insertArticle(java.lang.String, java.lang.String, java.lang.String, java.lang.Long, java.lang.Long) }
   399 	 * @param article
   400 	 * @throws StorageBackendException 
   401 	 */
   402 	@Override
   403 	public void addArticle(Article article) throws StorageBackendException {
   404 		if (article.getUser() != null && article.getUser().isAuthenticated()) {
   405 			
   406 			try {
   407 				DrupalMessage m = new DrupalMessage(article);
   408 
   409 				Long parentID = m.getParentID();
   410 				Long groupID = m.getGroupID();
   411 
   412 				if (parentID == null || groupID == null) {
   413 					throw new StorageBackendException("No valid In-Reply-To header was found → rejecting posted message.");
   414 				} else {
   415 
   416 					String subject = m.getSubject();
   417 					String text = m.getBodyXhtmlFragment();
   418 
   419 					if (subject == null || subject.length() < 1) {
   420 						String plainText = m.getBodyPlainText();
   421 						subject = plainText.substring(0, Math.min(32, plainText.length()));
   422 						if (subject.length() < plainText.length()) {
   423 							subject = subject + "…";
   424 						}
   425 					}
   426 
   427 					insertArticle(article.getUser().getUserName(), subject, text, parentID, groupID);
   428 					log.log(Level.INFO, "User ''{0}'' has posted an article", article.getUser().getUserName());
   429 				}
   430 			} catch (Exception e) {
   431 				throw new StorageBackendException(e);
   432 			}
   433 			
   434 		} else {
   435 			log.log(Level.SEVERE, "User was not authenticated, so his article was rejected.");
   436 			throw new StorageBackendException("User must be authenticated to post articles");
   437 		}
   438 	}
   439 
   440 	/**
   441 	 * Physically stores article in database.
   442 	 * @param subject
   443 	 * @param text
   444 	 * @param parentID
   445 	 * @param groupID 
   446 	 */
   447 	private void insertArticle(String sender, String subject, String text, Long parentID, Long groupID) throws StorageBackendException {
   448 		PreparedStatement ps = null;
   449 		ResultSet rs = null;
   450 		try {
   451 			ps = conn.prepareStatement("SELECT nntp_post_article(?, ?, ?, ?, ?)");
   452 
   453 			ps.setString(1, sender);
   454 			ps.setString(2, subject);
   455 			ps.setString(3, text);
   456 			ps.setLong(4, parentID);
   457 			ps.setLong(5, groupID);
   458 
   459 			rs = ps.executeQuery();
   460 			rs.next();
   461 
   462 			Long articleID = rs.getLong(1);
   463 			log.log(Level.INFO, "Article was succesfully stored as {0}", articleID);
   464 		} catch (Exception e) {
   465 			throw new StorageBackendException(e);
   466 		} finally {
   467 			close(null, ps, rs);
   468 		}
   469 	}
   470 
   471 	@Override
   472 	public void addEvent(long timestamp, int type, long groupID) throws StorageBackendException {
   473 		log.log(Level.SEVERE, "TODO: addEvent {0} / {1} / {2}", new Object[]{timestamp, type, groupID});
   474 	}
   475 
   476 	@Override
   477 	public void addGroup(String groupname, int flags) throws StorageBackendException {
   478 		log.log(Level.SEVERE, "TODO: addGroup {0} / {1}", new Object[]{groupname, flags});
   479 	}
   480 
   481 	@Override
   482 	public void delete(String messageID) throws StorageBackendException {
   483 		log.log(Level.SEVERE, "TODO: delete {0}", new Object[]{messageID});
   484 	}
   485 
   486 	@Override
   487 	public String getConfigValue(String key) throws StorageBackendException {
   488 		//log.log(Level.SEVERE, "TODO: getConfigValue {0}", new Object[]{key});
   489 		return null;
   490 	}
   491 
   492 	@Override
   493 	public void setConfigValue(String key, String value) throws StorageBackendException {
   494 		log.log(Level.SEVERE, "TODO: setConfigValue {0} = {1}", new Object[]{key, value});
   495 	}
   496 
   497 	@Override
   498 	public int getEventsCount(int eventType, long startTimestamp, long endTimestamp, Group group) throws StorageBackendException {
   499 		log.log(Level.SEVERE, "TODO: getEventsCount {0} / {1} / {2} / {3}", new Object[]{eventType, startTimestamp, endTimestamp, group});
   500 		return 0;
   501 	}
   502 
   503 	@Override
   504 	public double getEventsPerHour(int key, long gid) throws StorageBackendException {
   505 		log.log(Level.SEVERE, "TODO: getEventsPerHour {0} / {1}", new Object[]{key, gid});
   506 		return 0;
   507 	}
   508 
   509 	@Override
   510 	public List<String> getGroupsForList(String listAddress) throws StorageBackendException {
   511 		log.log(Level.SEVERE, "TODO: getGroupsForList {0}", new Object[]{listAddress});
   512 		return Collections.emptyList();
   513 	}
   514 
   515 	@Override
   516 	public List<String> getListsForGroup(String groupname) throws StorageBackendException {
   517 		log.log(Level.SEVERE, "TODO: getListsForGroup {0}", new Object[]{groupname});
   518 		return Collections.emptyList();
   519 	}
   520 
   521 	@Override
   522 	public String getOldestArticle() throws StorageBackendException {
   523 		log.log(Level.SEVERE, "TODO: getOldestArticle");
   524 		return null;
   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 boolean update(Article article) throws StorageBackendException {
   540 		log.log(Level.SEVERE, "TODO: update {0}", new Object[]{article});
   541 		throw new StorageBackendException("Not implemented yet.");
   542 	}
   543 
   544 	@Override
   545 	public boolean update(Group group) throws StorageBackendException {
   546 		log.log(Level.SEVERE, "TODO: update {0}", new Object[]{group});
   547 		throw new StorageBackendException("Not implemented yet.");
   548 	}
   549 }