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.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@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 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@72: import org.sonews.storage.DrupalArticle; franta-hg@72: import org.sonews.storage.DrupalMessage; 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@70: public static final String CHARSET = "UTF-8"; franta-hg@68: public static final String CRLF = "\r\n"; franta-hg@68: protected Connection conn = null; franta-hg@70: // TODO: správná doména franta-hg@72: private String myDomain = "nntp.i1984.cz"; 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@70: * @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@70: if (messageID.matches("<[0-9]+\\-[0-9]+\\-[a-z0-9\\.]+@.+>")) { franta-hg@70: return messageID.substring(1).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@72: // If needed: franta-hg@72: // parseGroupName() will be same as this method, just with: franta-hg@72: // return localPart[2]; franta-hg@68: } franta-hg@68: } franta-hg@68: 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@72: DrupalMessage m = new DrupalMessage(rs, myDomain, true); franta-hg@72: return new DrupalArticle(m); 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@70: ps = conn.prepareStatement("SELECT * FROM nntp_article WHERE group_id = ? AND id >= ? AND id <= ? ORDER BY 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@72: DrupalMessage m = new DrupalMessage(rs, myDomain, false); franta-hg@72: String headers = m.getHeaders(); 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@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: @Override franta-hg@67: public int countArticles() throws StorageBackendException { franta-hg@69: PreparedStatement ps = null; franta-hg@69: ResultSet rs = null; franta-hg@69: try { franta-hg@69: ps = conn.prepareStatement("SELECT count(*) FROM nntp_article"); franta-hg@69: rs = ps.executeQuery(); franta-hg@69: rs.next(); franta-hg@69: return rs.getInt(1); franta-hg@69: } catch (Exception e) { franta-hg@69: throw new StorageBackendException(e); franta-hg@69: } finally { franta-hg@69: close(null, ps, rs); franta-hg@69: } franta-hg@67: } franta-hg@67: franta-hg@67: @Override franta-hg@67: public int countGroups() throws StorageBackendException { franta-hg@69: PreparedStatement ps = null; franta-hg@69: ResultSet rs = null; franta-hg@69: try { franta-hg@69: ps = conn.prepareStatement("SELECT count(*) FROM nntp_group"); franta-hg@69: rs = ps.executeQuery(); franta-hg@69: rs.next(); franta-hg@69: return rs.getInt(1); franta-hg@69: } catch (Exception e) { franta-hg@69: throw new StorageBackendException(e); franta-hg@69: } finally { franta-hg@69: close(null, ps, rs); franta-hg@69: } franta-hg@67: } franta-hg@67: franta-hg@67: @Override franta-hg@72: public int getPostingsCount(String groupname) throws StorageBackendException { franta-hg@72: PreparedStatement ps = null; franta-hg@72: ResultSet rs = null; franta-hg@72: try { franta-hg@72: ps = conn.prepareStatement("SELECT count(*) FROM nntp_article WHERE group_name = ?"); franta-hg@72: ps.setString(1, groupname); franta-hg@72: rs = ps.executeQuery(); franta-hg@72: rs.next(); franta-hg@72: return rs.getInt(1); franta-hg@72: } catch (Exception e) { franta-hg@72: throw new StorageBackendException(e); franta-hg@72: } finally { franta-hg@72: close(null, ps, rs); franta-hg@72: } franta-hg@72: } franta-hg@72: franta-hg@72: @Override franta-hg@72: public List> getArticleHeaders(Group group, long start, long end, String header, String pattern) throws StorageBackendException { franta-hg@72: log.log(Level.SEVERE, "TODO: getArticleHeaders {0} / {1} / {2} / {3} / {4}", new Object[]{group, start, end, header, pattern}); franta-hg@72: /** TODO: */ franta-hg@72: return Collections.emptyList(); franta-hg@72: } franta-hg@72: franta-hg@72: @Override franta-hg@72: public void addArticle(Article art) throws StorageBackendException { franta-hg@72: log.log(Level.SEVERE, "TODO: addArticle {0}", new Object[]{art}); franta-hg@72: } franta-hg@72: franta-hg@72: @Override franta-hg@72: public void addEvent(long timestamp, int type, long groupID) throws StorageBackendException { franta-hg@72: log.log(Level.SEVERE, "TODO: addEvent {0} / {1} / {2}", new Object[]{timestamp, type, groupID}); franta-hg@72: } franta-hg@72: franta-hg@72: @Override franta-hg@72: public void addGroup(String groupname, int flags) throws StorageBackendException { franta-hg@72: log.log(Level.SEVERE, "TODO: addGroup {0} / {1}", new Object[]{groupname, flags}); franta-hg@72: } franta-hg@72: franta-hg@72: @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@69: //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@72: public void setConfigValue(String key, String value) throws StorageBackendException { franta-hg@72: log.log(Level.SEVERE, "TODO: setConfigValue {0} = {1}", new Object[]{key, value}); franta-hg@72: } franta-hg@72: franta-hg@72: @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 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 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: }