franta-hg@63: /*
franta-hg@63: * SONEWS News Server
franta-hg@63: * see AUTHORS for the list of contributors
franta-hg@63: *
franta-hg@63: * This program is free software: you can redistribute it and/or modify
franta-hg@63: * it under the terms of the GNU General Public License as published by
franta-hg@63: * the Free Software Foundation, either version 3 of the License, or
franta-hg@63: * (at your option) any later version.
franta-hg@63: *
franta-hg@63: * This program is distributed in the hope that it will be useful,
franta-hg@63: * but WITHOUT ANY WARRANTY; without even the implied warranty of
franta-hg@63: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
franta-hg@63: * GNU General Public License for more details.
franta-hg@63: *
franta-hg@63: * You should have received a copy of the GNU General Public License
franta-hg@63: * along with this program. If not, see .
franta-hg@63: */
franta-hg@63: package org.sonews.storage.impl;
franta-hg@63:
franta-hg@68: import java.io.UnsupportedEncodingException;
franta-hg@68: import java.sql.Connection;
franta-hg@68: import java.sql.DriverManager;
franta-hg@68: import java.sql.PreparedStatement;
franta-hg@68: import java.sql.ResultSet;
franta-hg@64: import java.sql.SQLException;
franta-hg@68: import java.sql.Statement;
franta-hg@68: import java.util.ArrayList;
franta-hg@65: import java.util.Collections;
franta-hg@64: import java.util.List;
franta-hg@65: import java.util.logging.Level;
franta-hg@66: import java.util.logging.Logger;
franta-hg@68: import javax.mail.internet.MimeUtility;
franta-hg@68: import org.sonews.config.Config;
franta-hg@64: import org.sonews.feed.Subscription;
franta-hg@64: import org.sonews.storage.Article;
franta-hg@64: import org.sonews.storage.ArticleHead;
franta-hg@64: import org.sonews.storage.Group;
franta-hg@64: import org.sonews.storage.Storage;
franta-hg@64: import org.sonews.storage.StorageBackendException;
franta-hg@64: import org.sonews.util.Pair;
franta-hg@64:
franta-hg@63: /**
franta-hg@63: *
franta-hg@63: * @author František Kučera (frantovo.cz)
franta-hg@63: */
franta-hg@64: public class DrupalDatabase implements Storage {
franta-hg@67:
franta-hg@66: private static final Logger log = Logger.getLogger(DrupalDatabase.class.getName());
franta-hg@68: public static final String CRLF = "\r\n";
franta-hg@68: public static final int MAX_RESTARTS = 2;
franta-hg@68: /** How many times the database connection was reinitialized */
franta-hg@68: protected int restarts = 0;
franta-hg@68: protected Connection conn = null;
franta-hg@68:
franta-hg@68: public DrupalDatabase() throws StorageBackendException {
franta-hg@68: connectDatabase();
franta-hg@68: }
franta-hg@68:
franta-hg@68: private void connectDatabase() throws StorageBackendException {
franta-hg@68: try {
franta-hg@68: // Load database driver
franta-hg@68: String driverClass = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DBMSDRIVER, "java.lang.Object");
franta-hg@68: Class.forName(driverClass);
franta-hg@68:
franta-hg@68: // Establish database connection
franta-hg@68: String url = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_DATABASE, "");
franta-hg@68: String username = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_USER, "root");
franta-hg@68: String password = Config.inst().get(Config.LEVEL_FILE, Config.STORAGE_PASSWORD, "");
franta-hg@68: conn = DriverManager.getConnection(url, username, password);
franta-hg@68:
franta-hg@68: conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
franta-hg@68: if (conn.getTransactionIsolation() != Connection.TRANSACTION_SERIALIZABLE) {
franta-hg@68: log.warning("Database is NOT fully serializable!");
franta-hg@68: }
franta-hg@68: } catch (Exception e) {
franta-hg@68: throw new StorageBackendException(e);
franta-hg@68: }
franta-hg@68: }
franta-hg@68:
franta-hg@68: protected static void close(Connection connection, Statement statement, ResultSet resultSet) {
franta-hg@68: if (resultSet != null) {
franta-hg@68: try {
franta-hg@68: resultSet.close();
franta-hg@68: } catch (Exception e) {
franta-hg@68: }
franta-hg@68: }
franta-hg@68: if (statement != null) {
franta-hg@68: try {
franta-hg@68: statement.close();
franta-hg@68: } catch (Exception e) {
franta-hg@68: }
franta-hg@68: }
franta-hg@68: if (connection != null) {
franta-hg@68: try {
franta-hg@68: connection.close();
franta-hg@68: } catch (Exception e) {
franta-hg@68: }
franta-hg@68: }
franta-hg@68: }
franta-hg@64:
franta-hg@64: /**
franta-hg@68: *
franta-hg@68: * @param messageID {0}-{1}-{2}@domain.tld where {0} is nntp_id and {1} is group_id and {2} is group_name
franta-hg@68: * @return array where [0] = nntp_id and [1] = group_id and [2] = group_name or returns null if messageID is invalid
franta-hg@64: */
franta-hg@68: private static String[] parseMessageID(String messageID) {
franta-hg@68: if (messageID.matches("[0-9]+\\-[0-9]+\\-[a-z0-9\\.]+@.+")) {
franta-hg@68: return messageID.split("@")[0].split("\\-");
franta-hg@68: } else {
franta-hg@68: return null;
franta-hg@68: }
franta-hg@68: }
franta-hg@68:
franta-hg@68: private static Long parseArticleID(String messageID) {
franta-hg@68: String[] localPart = parseMessageID(messageID);
franta-hg@68: if (localPart == null) {
franta-hg@68: return null;
franta-hg@68: } else {
franta-hg@68: return Long.parseLong(localPart[0]);
franta-hg@68: }
franta-hg@68: }
franta-hg@68:
franta-hg@68: private static Long parseGroupID(String messageID) {
franta-hg@68: String[] localPart = parseMessageID(messageID);
franta-hg@68: if (localPart == null) {
franta-hg@68: return null;
franta-hg@68: } else {
franta-hg@68: return Long.parseLong(localPart[1]);
franta-hg@68: }
franta-hg@68: }
franta-hg@68:
franta-hg@68: private static String parseGroupName(String messageID) {
franta-hg@68: String[] localPart = parseMessageID(messageID);
franta-hg@68: if (localPart == null) {
franta-hg@68: return null;
franta-hg@68: } else {
franta-hg@68: return localPart[2];
franta-hg@68: }
franta-hg@68: }
franta-hg@68:
franta-hg@68: private static String constructHeaders(ResultSet rs) throws SQLException, UnsupportedEncodingException {
franta-hg@68: StringBuilder sb = new StringBuilder();
franta-hg@68:
franta-hg@68: sb.append("Message-id: ");
franta-hg@68: sb.append(rs.getInt("id"));
franta-hg@68: sb.append("-");
franta-hg@68: sb.append(rs.getInt("group_id"));
franta-hg@68: sb.append("-");
franta-hg@68: sb.append(rs.getString("group_name"));
franta-hg@68: sb.append("@");
franta-hg@68: sb.append("nntp.kinderporno.cz");
franta-hg@68: sb.append(CRLF);
franta-hg@68:
franta-hg@68: sb.append("From: ");
franta-hg@68: sb.append(MimeUtility.encodeWord(rs.getString("sender_name")));
franta-hg@68: sb.append(" <>");
franta-hg@68: sb.append(CRLF);
franta-hg@68:
franta-hg@68: sb.append("Subject: ");
franta-hg@68: sb.append(MimeUtility.encodeWord(rs.getString("subject")));
franta-hg@68: sb.append(CRLF);
franta-hg@68:
franta-hg@68:
franta-hg@68:
franta-hg@68: return sb.toString();
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@67: public List getGroups() throws StorageBackendException {
franta-hg@68: PreparedStatement ps = null;
franta-hg@68: ResultSet rs = null;
franta-hg@68: try {
franta-hg@68: ps = conn.prepareStatement("SELECT * FROM nntp_group");
franta-hg@68: rs = ps.executeQuery();
franta-hg@68: List skupiny = new ArrayList();
franta-hg@68:
franta-hg@68: while (rs.next()) {
franta-hg@68: skupiny.add(new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY));
franta-hg@68: }
franta-hg@68:
franta-hg@68: return skupiny;
franta-hg@68: } catch (Exception e) {
franta-hg@68: throw new StorageBackendException(e);
franta-hg@68: } finally {
franta-hg@68: close(null, ps, rs);
franta-hg@68: }
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@67: public Group getGroup(String name) throws StorageBackendException {
franta-hg@68: PreparedStatement ps = null;
franta-hg@68: ResultSet rs = null;
franta-hg@68: try {
franta-hg@68: ps = conn.prepareStatement("SELECT * FROM nntp_group WHERE name = ?");
franta-hg@68: ps.setString(1, name);
franta-hg@68: rs = ps.executeQuery();
franta-hg@68:
franta-hg@68: while (rs.next()) {
franta-hg@68: return new Group(rs.getString("name"), rs.getInt("id"), Group.READONLY);
franta-hg@68: }
franta-hg@68:
franta-hg@68: return null;
franta-hg@68: } catch (Exception e) {
franta-hg@68: throw new StorageBackendException(e);
franta-hg@68: } finally {
franta-hg@68: close(null, ps, rs);
franta-hg@68: }
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@67: public boolean isGroupExisting(String groupname) throws StorageBackendException {
franta-hg@68: return getGroup(groupname) != null;
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public Article getArticle(String messageID) throws StorageBackendException {
franta-hg@68: Long articleID = parseArticleID(messageID);
franta-hg@68: Long groupID = parseGroupID(messageID);
franta-hg@68:
franta-hg@68: if (articleID == null || groupID == null) {
franta-hg@68: log.log(Level.SEVERE, "Invalid messageID: {0}", new Object[]{messageID});
franta-hg@68: return null;
franta-hg@68: } else {
franta-hg@68: return getArticle(articleID, groupID);
franta-hg@68: }
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@68: public Article getArticle(long articleID, long groupID) throws StorageBackendException {
franta-hg@68: PreparedStatement ps = null;
franta-hg@68: ResultSet rs = null;
franta-hg@68: try {
franta-hg@68: ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE id = ? AND group_id = ?");
franta-hg@68: ps.setLong(1, articleID);
franta-hg@68: ps.setLong(2, groupID);
franta-hg@68: rs = ps.executeQuery();
franta-hg@68:
franta-hg@68: if (rs.next()) {
franta-hg@68: String headers = constructHeaders(rs);
franta-hg@68: byte[] body = rs.getString("text").getBytes();
franta-hg@68:
franta-hg@68: return new Article(headers, body);
franta-hg@68: } else {
franta-hg@68: return null;
franta-hg@68: }
franta-hg@68: } catch (Exception e) {
franta-hg@68: throw new StorageBackendException(e);
franta-hg@68: } finally {
franta-hg@68: close(null, ps, rs);
franta-hg@68: }
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public List> getArticleHeads(Group group, long first, long last) throws StorageBackendException {
franta-hg@68: PreparedStatement ps = null;
franta-hg@68: ResultSet rs = null;
franta-hg@68: try {
franta-hg@68: ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE group_id = ? AND id >= ? AND id <= ?");
franta-hg@68: ps.setLong(1, group.getInternalID());
franta-hg@68: ps.setLong(2, first);
franta-hg@68: ps.setLong(3, last);
franta-hg@68: rs = ps.executeQuery();
franta-hg@68:
franta-hg@68: List> heads = new ArrayList>();
franta-hg@68:
franta-hg@68: while (rs.next()) {
franta-hg@68: String headers = constructHeaders(rs);
franta-hg@68: heads.add(new Pair(rs.getLong("id"), new ArticleHead(headers)));
franta-hg@68: }
franta-hg@68:
franta-hg@68: return heads;
franta-hg@68: } catch (Exception e) {
franta-hg@68: throw new StorageBackendException(e);
franta-hg@68: } finally {
franta-hg@68: close(null, ps, rs);
franta-hg@68: }
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public List> getArticleHeaders(Group group, long start, long end, String header, String pattern) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: getArticleHeaders {0} / {1} / {2} / {3} / {4}", new Object[]{group, start, end, header, pattern});
franta-hg@65: /** TODO: */
franta-hg@65: return Collections.emptyList();
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@68: public long getArticleIndex(Article article, Group group) throws StorageBackendException {
franta-hg@68: Long id = parseArticleID(article.getMessageID());
franta-hg@68: if (id == null) {
franta-hg@68: throw new StorageBackendException("Invalid messageID: " + article.getMessageID());
franta-hg@68: } else {
franta-hg@68: return id;
franta-hg@68: }
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public List getArticleNumbers(long groupID) throws StorageBackendException {
franta-hg@68: PreparedStatement ps = null;
franta-hg@68: ResultSet rs = null;
franta-hg@68: try {
franta-hg@68: ps = conn.prepareStatement("SELECT id FROM nntp_article WHERE group_id = ?");
franta-hg@68: ps.setLong(1, groupID);
franta-hg@68: rs = ps.executeQuery();
franta-hg@68: List articleNumbers = new ArrayList();
franta-hg@68: while (rs.next()) {
franta-hg@68: articleNumbers.add(rs.getLong(1));
franta-hg@68: }
franta-hg@68: return articleNumbers;
franta-hg@68: } catch (Exception e) {
franta-hg@68: throw new StorageBackendException(e);
franta-hg@68: } finally {
franta-hg@68: close(null, ps, rs);
franta-hg@68: }
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@67: public int getFirstArticleNumber(Group group) throws StorageBackendException {
franta-hg@68: PreparedStatement ps = null;
franta-hg@68: ResultSet rs = null;
franta-hg@68: try {
franta-hg@68: ps = conn.prepareStatement("SELECT min(id) FROM nntp_article WHERE group_id = ?");
franta-hg@68: ps.setLong(1, group.getInternalID());
franta-hg@68: rs = ps.executeQuery();
franta-hg@68: rs.next();
franta-hg@68: return rs.getInt(1);
franta-hg@68: } catch (Exception e) {
franta-hg@68: throw new StorageBackendException(e);
franta-hg@68: } finally {
franta-hg@68: close(null, ps, rs);
franta-hg@68: }
franta-hg@67: }
franta-hg@67:
franta-hg@67: @Override
franta-hg@67: public int getLastArticleNumber(Group group) throws StorageBackendException {
franta-hg@68: PreparedStatement ps = null;
franta-hg@68: ResultSet rs = null;
franta-hg@68: try {
franta-hg@68: ps = conn.prepareStatement("SELECT max(id) FROM nntp_article WHERE group_id = ?");
franta-hg@68: ps.setLong(1, group.getInternalID());
franta-hg@68: rs = ps.executeQuery();
franta-hg@68: rs.next();
franta-hg@68: return rs.getInt(1);
franta-hg@68: } catch (Exception e) {
franta-hg@68: throw new StorageBackendException(e);
franta-hg@68: } finally {
franta-hg@68: close(null, ps, rs);
franta-hg@68: }
franta-hg@67: }
franta-hg@67:
franta-hg@67: @Override
franta-hg@67: public boolean isArticleExisting(String messageID) throws StorageBackendException {
franta-hg@68: Long articleID = parseArticleID(messageID);
franta-hg@68: Long groupID = parseGroupID(messageID);
franta-hg@68:
franta-hg@68: if (articleID == null || groupID == null) {
franta-hg@68: return false;
franta-hg@68: } else {
franta-hg@68: PreparedStatement ps = null;
franta-hg@68: ResultSet rs = null;
franta-hg@68: try {
franta-hg@68: ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE id = ? AND group_id = ?");
franta-hg@68: ps.setLong(1, articleID);
franta-hg@68: ps.setLong(2, groupID);
franta-hg@68: rs = ps.executeQuery();
franta-hg@68:
franta-hg@68: rs.next();
franta-hg@68: return rs.getInt(1) == 1;
franta-hg@68: } catch (Exception e) {
franta-hg@68: throw new StorageBackendException(e);
franta-hg@68: } finally {
franta-hg@68: close(null, ps, rs);
franta-hg@68: }
franta-hg@68: }
franta-hg@67: }
franta-hg@67:
franta-hg@67: //
franta-hg@67: // --- zatím neimplementovat ---
franta-hg@67: //
franta-hg@67: @Override
franta-hg@67: public void addArticle(Article art) throws StorageBackendException {
franta-hg@67: log.log(Level.SEVERE, "TODO: addArticle {0}", new Object[]{art});
franta-hg@67: }
franta-hg@67:
franta-hg@67: @Override
franta-hg@67: public void addEvent(long timestamp, int type, long groupID) throws StorageBackendException {
franta-hg@67: log.log(Level.SEVERE, "TODO: addEvent {0} / {1} / {2}", new Object[]{timestamp, type, groupID});
franta-hg@67: }
franta-hg@67:
franta-hg@67: @Override
franta-hg@67: public void addGroup(String groupname, int flags) throws StorageBackendException {
franta-hg@67: log.log(Level.SEVERE, "TODO: addGroup {0} / {1}", new Object[]{groupname, flags});
franta-hg@67: }
franta-hg@67:
franta-hg@67: @Override
franta-hg@67: public int countArticles() throws StorageBackendException {
franta-hg@67: log.log(Level.SEVERE, "TODO: countArticles");
franta-hg@67: return 0;
franta-hg@67: }
franta-hg@67:
franta-hg@67: @Override
franta-hg@67: public int countGroups() throws StorageBackendException {
franta-hg@67: log.log(Level.SEVERE, "TODO: countGroups");
franta-hg@67: return 0;
franta-hg@67: }
franta-hg@67:
franta-hg@67: @Override
franta-hg@67: public void delete(String messageID) throws StorageBackendException {
franta-hg@67: log.log(Level.SEVERE, "TODO: delete {0}", new Object[]{messageID});
franta-hg@67: }
franta-hg@67:
franta-hg@67: @Override
franta-hg@64: public String getConfigValue(String key) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: getConfigValue {0}", new Object[]{key});
franta-hg@65: return null;
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public int getEventsCount(int eventType, long startTimestamp, long endTimestamp, Group group) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: getEventsCount {0} / {1} / {2} / {3}", new Object[]{eventType, startTimestamp, endTimestamp, group});
franta-hg@65: return 0;
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public double getEventsPerHour(int key, long gid) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: getEventsPerHour {0} / {1}", new Object[]{key, gid});
franta-hg@65: return 0;
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public List getGroupsForList(String listAddress) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: getGroupsForList {0}", new Object[]{listAddress});
franta-hg@65: return Collections.emptyList();
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public List getListsForGroup(String groupname) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: getListsForGroup {0}", new Object[]{groupname});
franta-hg@65: return Collections.emptyList();
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public String getOldestArticle() throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: getOldestArticle");
franta-hg@65: return null;
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public int getPostingsCount(String groupname) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: getPostingsCount {0}", new Object[]{groupname});
franta-hg@65: return 0;
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public List getSubscriptions(int type) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: getSubscriptions {0}", new Object[]{type});
franta-hg@65: return Collections.emptyList();
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public void purgeGroup(Group group) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: purgeGroup {0}", new Object[]{group});
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public void setConfigValue(String key, String value) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: setConfigValue {0} = {1}", new Object[]{key, value});
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public boolean update(Article article) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: update {0}", new Object[]{article});
franta-hg@65: throw new StorageBackendException("Not implemented yet.");
franta-hg@64: }
franta-hg@64:
franta-hg@64: @Override
franta-hg@64: public boolean update(Group group) throws StorageBackendException {
franta-hg@66: log.log(Level.SEVERE, "TODO: update {0}", new Object[]{group});
franta-hg@65: throw new StorageBackendException("Not implemented yet.");
franta-hg@64: }
franta-hg@63: }