0001: package org.methodize.nntprss.rss.db;
0002:
0003: /* -----------------------------------------------------------
0004: * nntp//rss - a bridge between the RSS world and NNTP clients
0005: * Copyright (c) 2002, 2003 Jason Brome. All Rights Reserved.
0006: *
0007: * email: nntprss@methodize.org
0008: * mail: Methodize Solutions
0009: * PO Box 3865
0010: * Grand Central Station
0011: * New York NY 10163
0012: *
0013: * This file is part of nntp//rss
0014: *
0015: * nntp//rss is free software; you can redistribute it
0016: * and/or modify it under the terms of the GNU General
0017: * Public License as published by the Free Software Foundation;
0018: * either version 2 of the License, or (at your option) any
0019: * later version.
0020: *
0021: * This program is distributed in the hope that it will be
0022: * useful, but WITHOUT ANY WARRANTY; without even the implied
0023: * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
0024: * PURPOSE. See the GNU General Public License for more
0025: * details.
0026: *
0027: * You should have received a copy of the GNU General Public
0028: * License along with this program; if not, write to the
0029: * Free Software Foundation, Inc., 59 Temple Place, Suite 330,
0030: * Boston, MA 02111-1307 USA
0031: * ----------------------------------------------------- */
0032:
0033: import java.net.MalformedURLException;
0034: import java.sql.Connection;
0035: import java.sql.DriverManager;
0036: import java.sql.PreparedStatement;
0037: import java.sql.ResultSet;
0038: import java.sql.ResultSetMetaData;
0039: import java.sql.SQLException;
0040: import java.sql.Statement;
0041: import java.sql.Timestamp;
0042: import java.util.ArrayList;
0043: import java.util.HashSet;
0044: import java.util.Iterator;
0045: import java.util.List;
0046: import java.util.Map;
0047: import java.util.Set;
0048: import java.util.TreeMap;
0049:
0050: import org.apache.log4j.Logger;
0051: import org.apache.log4j.Priority;
0052: import org.methodize.nntprss.db.DBManager;
0053: import org.methodize.nntprss.nntp.NNTPServer;
0054: import org.methodize.nntprss.rss.Channel;
0055: import org.methodize.nntprss.rss.ChannelManager;
0056: import org.methodize.nntprss.rss.Item;
0057: import org.methodize.nntprss.util.AppConstants;
0058: import org.methodize.nntprss.util.XMLHelper;
0059: import org.w3c.dom.Document;
0060: import org.w3c.dom.Element;
0061: import org.w3c.dom.NodeList;
0062:
0063: /**
0064: * @author Jason Brome <jason@methodize.org>
0065: * @version $Id: ChannelManagerDAO.java,v 1.6 2003/03/22 16:31:35 jasonbrome Exp $
0066: */
0067: public class ChannelManagerDAO {
0068:
0069: private static final int DBVERSION = 4;
0070:
0071: private Logger log = Logger.getLogger(ChannelManagerDAO.class);
0072:
0073: private static final ChannelManagerDAO channelManagerDAO = new ChannelManagerDAO();
0074:
0075: private ChannelManagerDAO() {
0076: }
0077:
0078: public static ChannelManagerDAO getChannelManagerDAO() {
0079: return channelManagerDAO;
0080: }
0081:
0082: private void createTables(Document config) {
0083: Connection conn = null;
0084: Statement stmt = null;
0085: PreparedStatement ps = null;
0086: ResultSet rs = null;
0087:
0088: if (log.isInfoEnabled()) {
0089: log.info("Creating application database tables");
0090: }
0091:
0092: try {
0093: conn = DriverManager
0094: .getConnection(DBManager.POOL_CONNECT_STRING);
0095: stmt = conn.createStatement();
0096:
0097: stmt.executeUpdate("CREATE CACHED TABLE channels ("
0098: + "id int not null identity, "
0099: + "url varchar(256) not null, "
0100: + "name varchar(256) not null, "
0101: + "author varchar(256), " + "title varchar(256), "
0102: + "link varchar(500), "
0103: + "description varchar(500), "
0104: + "lastArticle int not null, "
0105: + "lastPolled timestamp, " + "created timestamp, "
0106: + "lastModified bigint, "
0107: + "lastETag varchar(256), "
0108: + "rssVersion varchar(8), " + "historical bit, "
0109: + "enabled bit, " + "postingEnabled bit, "
0110: + "parseAtAllCost bit, "
0111: + "publishAPI varchar(128), "
0112: + "publishConfig varchar(2048), "
0113: + "managingEditor varchar(128), "
0114: + "pollingInterval bigint not null)");
0115:
0116: stmt.executeUpdate("CREATE CACHED TABLE items ("
0117: + "articleNumber int not null, "
0118: + "channel int not null, " + "title varchar, "
0119: + "link varchar, " + "description varchar, "
0120: + "comments varchar(500), " + "dtStamp timestamp, "
0121: + "signature varchar(32))");
0122: stmt.executeUpdate("CREATE CACHED TABLE config ("
0123: + "pollingInterval bigint not null, "
0124: + "proxyServer varchar(256), " + "proxyPort int, "
0125: + "proxyUserID varchar(256), "
0126: + "proxyPassword varchar(256), "
0127: + "contentType int, " + "dbVersion int, "
0128: + "nntpSecure bit)");
0129: stmt
0130: .executeUpdate("INSERT INTO config(pollingInterval, contentType, dbVersion, nntpSecure) VALUES(60*60, "
0131: + AppConstants.CONTENT_TYPE_MIXED
0132: + ", "
0133: + DBVERSION + ", false" + ")");
0134:
0135: NodeList channelsList = config.getDocumentElement()
0136: .getElementsByTagName("channels");
0137:
0138: if (channelsList.getLength() > 0) {
0139: Element channelsElm = (Element) channelsList.item(0);
0140: NodeList channelList = channelsElm
0141: .getElementsByTagName("channel");
0142: if (channelList.getLength() > 0) {
0143:
0144: ps = conn
0145: .prepareStatement("INSERT INTO channels(url, name, created, lastArticle, historical, enabled, postingEnabled, parseAtAllCost, pollingInterval) "
0146: + "values(?, ?, ?, ?, ?, true, false, false, 0)");
0147:
0148: for (int channelCount = 0; channelCount < channelList
0149: .getLength(); channelCount++) {
0150: Element channelElm = (Element) channelList
0151: .item(channelCount);
0152: String url = channelElm.getAttribute("url");
0153: String name = channelElm.getAttribute("name");
0154:
0155: String historicalStr = channelElm
0156: .getAttribute("historical");
0157: boolean historical = true;
0158: if (historicalStr != null) {
0159: historical = historicalStr
0160: .equalsIgnoreCase("true");
0161: }
0162: int paramCount = 1;
0163: ps.setString(paramCount++, url);
0164: ps.setString(paramCount++, name);
0165: ps.setTimestamp(paramCount++, new Timestamp(
0166: System.currentTimeMillis()));
0167: // Last Article
0168: ps.setInt(paramCount++, 0);
0169: ps.setBoolean(paramCount++, historical);
0170: ps.executeUpdate();
0171: }
0172: }
0173: }
0174:
0175: } catch (SQLException se) {
0176:
0177: if (log.isEnabledFor(Priority.ERROR)) {
0178: log.error("Error creating application database tables",
0179: se);
0180: }
0181: throw new RuntimeException(
0182: "Error creating application tables - "
0183: + se.getMessage());
0184:
0185: } finally {
0186: try {
0187: if (rs != null)
0188: rs.close();
0189: } catch (Exception e) {
0190: }
0191: try {
0192: if (ps != null)
0193: ps.close();
0194: } catch (Exception e) {
0195: }
0196: try {
0197: if (stmt != null)
0198: stmt.close();
0199: } catch (Exception e) {
0200: }
0201: try {
0202: if (conn != null)
0203: conn.close();
0204: } catch (Exception e) {
0205: }
0206: }
0207:
0208: if (log.isInfoEnabled()) {
0209: log.info("Finished creating application database tables");
0210: }
0211:
0212: }
0213:
0214: private void upgradeDatabase(int dbVersion) {
0215: Connection conn = null;
0216: Statement stmt = null;
0217:
0218: if (log.isInfoEnabled()) {
0219: log.info("Upgrading database from db v" + dbVersion
0220: + " to db v" + DBVERSION);
0221: }
0222:
0223: try {
0224: conn = DriverManager
0225: .getConnection(DBManager.POOL_CONNECT_STRING);
0226: stmt = conn.createStatement();
0227:
0228: switch (dbVersion) {
0229: // v0.1 updates
0230: case 0:
0231: stmt
0232: .executeUpdate("ALTER TABLE config ADD COLUMN contentType int");
0233: stmt.executeUpdate("UPDATE config SET contentType = "
0234: + AppConstants.CONTENT_TYPE_MIXED);
0235: stmt
0236: .executeUpdate("ALTER TABLE config ADD COLUMN dbVersion int");
0237:
0238: // Channel
0239: stmt
0240: .executeUpdate("ALTER TABLE channels ADD COLUMN title varchar(256)");
0241: stmt
0242: .executeUpdate("ALTER TABLE channels ADD COLUMN link varchar(500)");
0243: stmt
0244: .executeUpdate("ALTER TABLE channels ADD COLUMN description varchar(500)");
0245:
0246: // Items
0247: stmt
0248: .executeUpdate("ALTER TABLE items ADD COLUMN comments varchar(500)");
0249:
0250: case 2:
0251: stmt
0252: .executeUpdate("ALTER TABLE config ADD COLUMN nntpSecure bit");
0253: stmt
0254: .executeUpdate("UPDATE config SET nntpSecure = false");
0255:
0256: stmt
0257: .executeUpdate("ALTER TABLE config ADD COLUMN proxyUserID varchar(256)");
0258: stmt
0259: .executeUpdate("ALTER TABLE config ADD COLUMN proxyPassword varchar(256)");
0260:
0261: stmt
0262: .executeUpdate("ALTER TABLE channels ADD COLUMN enabled bit");
0263: stmt
0264: .executeUpdate("ALTER TABLE channels ADD COLUMN postingEnabled bit");
0265: stmt
0266: .executeUpdate("ALTER TABLE channels ADD COLUMN parseAtAllCost bit");
0267: stmt
0268: .executeUpdate("ALTER TABLE channels ADD COLUMN publishAPI varchar(128)");
0269: stmt
0270: .executeUpdate("ALTER TABLE channels ADD COLUMN publishConfig varchar(2048)");
0271:
0272: stmt
0273: .executeUpdate("UPDATE channels SET enabled = true, postingEnabled = false, parseAtAllCost = false");
0274:
0275: stmt
0276: .executeUpdate("ALTER TABLE channels ADD COLUMN managingEditor varchar(128)");
0277:
0278: case 3:
0279: stmt
0280: .executeUpdate("ALTER TABLE channels ADD COLUMN pollingInterval bigint");
0281: stmt
0282: .executeUpdate("UPDATE channels SET pollingInterval = 0");
0283:
0284: default:
0285: // Force re-poll of all channels after DB upgrade...
0286: stmt.executeUpdate("UPDATE config SET dbVersion = "
0287: + DBVERSION);
0288: stmt
0289: .executeUpdate("UPDATE channels SET lastPolled = null, lastModified = null, lastETag = null");
0290: }
0291:
0292: if (log.isInfoEnabled()) {
0293: log.info("Successfully upgraded database.");
0294: }
0295:
0296: } catch (SQLException se) {
0297: throw new RuntimeException("Problem upgrading database"
0298: + se);
0299: } finally {
0300: try {
0301: if (stmt != null)
0302: stmt.close();
0303: } catch (Exception e) {
0304: }
0305: try {
0306: if (conn != null)
0307: conn.close();
0308: } catch (Exception e) {
0309: }
0310: }
0311: }
0312:
0313: public void initialize(Document config) {
0314: Connection conn = null;
0315: Statement stmt = null;
0316: ResultSet rs = null;
0317: boolean createTables = false;
0318: try {
0319: conn = DriverManager
0320: .getConnection(DBManager.POOL_CONNECT_STRING);
0321: stmt = conn.createStatement();
0322: try {
0323: rs = stmt.executeQuery("SELECT * FROM CONFIG");
0324: if (rs != null) {
0325: if (rs.next()) {
0326: // rssManager.setPollingIntervalSeconds(rs.getLong("pollingInterval"));
0327: // rssManager.setProxyServer(rs.getString("proxyServer"));
0328: // rssManager.setProxyPort(rs.getInt("proxyPort"));
0329: int dbVersion = rs.getInt("dbVersion");
0330: if (dbVersion < DBVERSION) {
0331: upgradeDatabase(dbVersion);
0332: }
0333: }
0334: }
0335: } catch (SQLException e) {
0336: if (e.getErrorCode() == -org.hsqldb.Trace.COLUMN_NOT_FOUND) {
0337: // Pre-version db, upgrade database
0338: upgradeDatabase(0);
0339: } else {
0340: // Our tables don't exist, so let's create them...
0341: createTables = true;
0342: }
0343: }
0344: } catch (SQLException se) {
0345:
0346: throw new RuntimeException(
0347: "Problem initializing application database " + se);
0348:
0349: } finally {
0350: try {
0351: if (rs != null)
0352: rs.close();
0353: } catch (Exception e) {
0354: }
0355: try {
0356: if (stmt != null)
0357: stmt.close();
0358: } catch (Exception e) {
0359: }
0360: try {
0361: if (conn != null)
0362: conn.close();
0363: } catch (Exception e) {
0364: }
0365: }
0366:
0367: if (createTables) {
0368: createTables(config);
0369: }
0370: }
0371:
0372: public void loadConfiguration(ChannelManager channelManager) {
0373: Connection conn = null;
0374: Statement stmt = null;
0375: ResultSet rs = null;
0376: try {
0377: conn = DriverManager
0378: .getConnection(DBManager.POOL_CONNECT_STRING);
0379: stmt = conn.createStatement();
0380: rs = stmt.executeQuery("SELECT * FROM CONFIG");
0381: if (rs != null) {
0382: if (rs.next()) {
0383: channelManager.setPollingIntervalSeconds(rs
0384: .getLong("pollingInterval"));
0385: channelManager.setProxyServer(rs
0386: .getString("proxyServer"));
0387: channelManager.setProxyPort(rs.getInt("proxyPort"));
0388: channelManager.setProxyUserID(rs
0389: .getString("proxyUserID"));
0390: channelManager.setProxyPassword(rs
0391: .getString("proxyPassword"));
0392: }
0393: }
0394: } catch (SQLException se) {
0395: throw new RuntimeException(
0396: "Problem loading Channel manager configuration"
0397: + se);
0398: } finally {
0399: try {
0400: if (rs != null)
0401: rs.close();
0402: } catch (Exception e) {
0403: }
0404: try {
0405: if (stmt != null)
0406: stmt.close();
0407: } catch (Exception e) {
0408: }
0409: try {
0410: if (conn != null)
0411: conn.close();
0412: } catch (Exception e) {
0413: }
0414: }
0415: }
0416:
0417: public void loadConfiguration(NNTPServer nntpServer) {
0418: Connection conn = null;
0419: Statement stmt = null;
0420: ResultSet rs = null;
0421: try {
0422: conn = DriverManager
0423: .getConnection(DBManager.POOL_CONNECT_STRING);
0424: stmt = conn.createStatement();
0425: rs = stmt
0426: .executeQuery("SELECT contentType, nntpSecure FROM CONFIG");
0427: if (rs != null) {
0428: if (rs.next()) {
0429: nntpServer.setContentType(rs.getInt("contentType"));
0430: nntpServer.setSecure(rs.getBoolean("nntpSecure"));
0431: }
0432: }
0433: } catch (SQLException se) {
0434: throw new RuntimeException(
0435: "Problem loading NNTP Server configuration" + se);
0436: } finally {
0437: try {
0438: if (rs != null)
0439: rs.close();
0440: } catch (Exception e) {
0441: }
0442: try {
0443: if (stmt != null)
0444: stmt.close();
0445: } catch (Exception e) {
0446: }
0447: try {
0448: if (conn != null)
0449: conn.close();
0450: } catch (Exception e) {
0451: }
0452: }
0453: }
0454:
0455: public Map loadChannels() {
0456: Map channels = new TreeMap();
0457: Connection conn = null;
0458: Statement stmt = null;
0459: PreparedStatement ps = null;
0460: ResultSet rs = null;
0461: ResultSet rs2 = null;
0462:
0463: if (log.isInfoEnabled()) {
0464: log.info("Loading channel configuration");
0465: }
0466:
0467: try {
0468: conn = DriverManager
0469: .getConnection(DBManager.POOL_CONNECT_STRING);
0470: stmt = conn.createStatement();
0471: rs = stmt.executeQuery("SELECT * FROM channels");
0472: if (rs != null) {
0473: ps = conn
0474: .prepareStatement("SELECT MIN(articleNumber) as firstArticleNumber, COUNT(articleNumber) as totalArticles FROM items WHERE channel = ?");
0475: while (rs.next()) {
0476: String name = rs.getString("name");
0477: String url = rs.getString("url");
0478: Channel channel = null;
0479: try {
0480: channel = new Channel(name, url);
0481: } catch (MalformedURLException me) {
0482: System.out.println(name + " - Bad url: " + url);
0483: // Skip this entry
0484: continue;
0485: }
0486: channel.setId(rs.getInt("id"));
0487: channel.setAuthor(rs.getString("author"));
0488: channel.setLastArticleNumber(rs
0489: .getInt("lastArticle"));
0490: channel.setCreated(rs.getTimestamp("created"));
0491: channel.setTitle(rs.getString("title"));
0492: channel.setLink(rs.getString("link"));
0493: channel.setDescription(rs.getString("description"));
0494:
0495: ps.setInt(1, channel.getId());
0496: rs2 = ps.executeQuery();
0497: if (rs2 != null) {
0498: if (rs2.next()) {
0499: int firstArticleNumber = rs2
0500: .getInt("firstArticleNumber");
0501: if (firstArticleNumber != 0) {
0502: channel
0503: .setFirstArticleNumber(firstArticleNumber);
0504: } else {
0505: channel.setFirstArticleNumber(1);
0506: }
0507:
0508: channel.setTotalArticles(rs2
0509: .getInt("totalArticles"));
0510: }
0511: rs2.close();
0512: }
0513:
0514: channel
0515: .setLastPolled(rs
0516: .getTimestamp("lastPolled"));
0517: channel.setLastModified(rs.getLong("lastModified"));
0518: channel.setLastETag(rs.getString("lastETag"));
0519: channel.setRssVersion(rs.getString("rssVersion"));
0520: channel.setHistorical(rs.getBoolean("historical"));
0521: channel.setEnabled(rs.getBoolean("enabled"));
0522: channel.setPostingEnabled(rs
0523: .getBoolean("postingEnabled"));
0524: channel.setPublishAPI(rs.getString("publishAPI"));
0525: channel.setPublishConfig(XMLHelper
0526: .xmlToStringHashMap(rs
0527: .getString("publishConfig")));
0528:
0529: channel.setParseAtAllCost(rs
0530: .getBoolean("parseAtAllCost"));
0531: channel.setManagingEditor(rs
0532: .getString("managingEditor"));
0533:
0534: channel.setPollingIntervalSeconds(rs
0535: .getLong("pollingInterval"));
0536:
0537: channels.put(channel.getName(), channel);
0538: }
0539: }
0540: } catch (SQLException se) {
0541: throw new RuntimeException(se);
0542: } finally {
0543: try {
0544: if (rs != null)
0545: rs.close();
0546: } catch (SQLException se) {
0547: }
0548: try {
0549: if (rs2 != null)
0550: rs2.close();
0551: } catch (SQLException se) {
0552: }
0553: try {
0554: if (stmt != null)
0555: stmt.close();
0556: } catch (SQLException se) {
0557: }
0558: try {
0559: if (ps != null)
0560: ps.close();
0561: } catch (SQLException se) {
0562: }
0563: try {
0564: if (conn != null)
0565: conn.close();
0566: } catch (SQLException se) {
0567: }
0568: }
0569:
0570: if (log.isInfoEnabled()) {
0571: log.info("Loaded " + channels.size() + " channels");
0572: }
0573:
0574: return channels;
0575: }
0576:
0577: public void addChannel(Channel channel) {
0578: Connection conn = null;
0579: PreparedStatement ps = null;
0580: ResultSet rs = null;
0581:
0582: try {
0583: conn = DriverManager
0584: .getConnection(DBManager.POOL_CONNECT_STRING);
0585: ps = conn
0586: .prepareStatement("INSERT INTO channels(url, name, lastArticle, created, historical, enabled, postingEnabled, publishAPI, publishConfig, parseAtAllCost, pollingInterval) "
0587: + "values(?, ?, 0, ?, ?, ?, ?, ?, ?, ?, ?); CALL IDENTITY()");
0588:
0589: int paramCount = 1;
0590: ps.setString(paramCount++, channel.getUrl());
0591: ps.setString(paramCount++, channel.getName());
0592: ps.setTimestamp(paramCount++, new Timestamp(channel
0593: .getCreated().getTime()));
0594: ps.setBoolean(paramCount++, channel.isHistorical());
0595: ps.setBoolean(paramCount++, channel.isEnabled());
0596: ps.setBoolean(paramCount++, channel.isPostingEnabled());
0597: ps.setString(paramCount++, channel.getPublishAPI());
0598: ps.setString(paramCount++, XMLHelper.stringMapToXML(channel
0599: .getPublishConfig()));
0600: ps.setBoolean(paramCount++, channel.isParseAtAllCost());
0601: ps.setLong(paramCount++, channel
0602: .getPollingIntervalSeconds());
0603: rs = ps.executeQuery();
0604:
0605: if (rs != null) {
0606: if (rs.next()) {
0607: channel.setId(rs.getInt(1));
0608: }
0609: }
0610: } catch (SQLException se) {
0611: throw new RuntimeException(se);
0612: } finally {
0613: try {
0614: if (ps != null)
0615: ps.close();
0616: } catch (SQLException se) {
0617: }
0618: try {
0619: if (conn != null)
0620: conn.close();
0621: } catch (SQLException se) {
0622: }
0623: }
0624:
0625: }
0626:
0627: public void updateChannel(Channel channel) {
0628: Connection conn = null;
0629: PreparedStatement ps = null;
0630:
0631: try {
0632: conn = DriverManager
0633: .getConnection(DBManager.POOL_CONNECT_STRING);
0634: ps = conn
0635: .prepareStatement("UPDATE channels "
0636: + "SET author = ?, name = ?, url = ?, "
0637: + "title = ?, link = ?, description = ?, "
0638: + "lastArticle = ?, "
0639: + "lastPolled = ?, lastModified = ?, lastETag = ?, rssVersion = ?, historical = ?, "
0640: + "enabled = ?, " + "postingEnabled = ?, "
0641: + "publishAPI = ?, "
0642: + "publishConfig = ?, "
0643: + "parseAtAllCost = ?, "
0644: + "managingEditor = ?, "
0645: + "pollingInterval = ? " + "WHERE id = ?");
0646:
0647: int paramCount = 1;
0648: ps.setString(paramCount++, channel.getAuthor());
0649: ps.setString(paramCount++, channel.getName());
0650: ps.setString(paramCount++, channel.getUrl());
0651: ps.setString(paramCount++, channel.getTitle());
0652: ps.setString(paramCount++, channel.getLink());
0653: ps.setString(paramCount++, channel.getDescription());
0654: ps.setInt(paramCount++, channel.getLastArticleNumber());
0655:
0656: if (channel.getLastPolled() != null) {
0657: ps.setTimestamp(paramCount++, new Timestamp(channel
0658: .getLastPolled().getTime()));
0659: } else {
0660: ps.setNull(paramCount++, java.sql.Types.TIMESTAMP);
0661: }
0662:
0663: ps.setLong(paramCount++, channel.getLastModified());
0664: ps.setString(paramCount++, channel.getLastETag());
0665: ps.setString(paramCount++, channel.getRssVersion());
0666: ps.setBoolean(paramCount++, channel.isHistorical());
0667: ps.setBoolean(paramCount++, channel.isEnabled());
0668: ps.setBoolean(paramCount++, channel.isPostingEnabled());
0669: ps.setString(paramCount++, channel.getPublishAPI());
0670: ps.setString(paramCount++, XMLHelper.stringMapToXML(channel
0671: .getPublishConfig()));
0672: ps.setBoolean(paramCount++, channel.isParseAtAllCost());
0673:
0674: ps.setString(paramCount++, channel.getManagingEditor());
0675:
0676: ps.setLong(paramCount++, channel
0677: .getPollingIntervalSeconds());
0678:
0679: ps.setInt(paramCount++, channel.getId());
0680: ps.executeUpdate();
0681:
0682: } catch (SQLException se) {
0683: throw new RuntimeException(se);
0684: } finally {
0685: try {
0686: if (ps != null)
0687: ps.close();
0688: } catch (SQLException se) {
0689: }
0690: try {
0691: if (conn != null)
0692: conn.close();
0693: } catch (SQLException se) {
0694: }
0695: }
0696:
0697: }
0698:
0699: public void deleteItemsBySignature(Channel channel,
0700: Set itemSignatures) {
0701: Connection conn = null;
0702: PreparedStatement ps = null;
0703: ResultSet rs = null;
0704:
0705: try {
0706: conn = DriverManager
0707: .getConnection(DBManager.POOL_CONNECT_STRING);
0708:
0709: ps = conn
0710: .prepareStatement("DELETE FROM items WHERE channel = ? AND signature = ?");
0711:
0712: int paramCount = 1;
0713: ps.setInt(paramCount++, channel.getId());
0714:
0715: Iterator sigIter = itemSignatures.iterator();
0716: while (sigIter.hasNext()) {
0717: ps.setString(paramCount, (String) sigIter.next());
0718: ps.executeUpdate();
0719: }
0720:
0721: // Need to reset first article number...
0722: ps = conn
0723: .prepareStatement("SELECT MIN(articleNumber) as firstArticleNumber FROM items WHERE channel = ?");
0724: paramCount = 1;
0725:
0726: ps.setInt(paramCount++, channel.getId());
0727:
0728: rs = ps.executeQuery();
0729: if (rs != null) {
0730: if (rs.next()) {
0731: int firstArticle = rs.getInt("firstArticleNumber");
0732: if (firstArticle == 0) {
0733: // Have yet to sync any articles, so first article number
0734: // will be 1
0735: if (channel.getLastArticleNumber() == 0) {
0736: channel.setFirstArticleNumber(1);
0737: } else {
0738: channel.setFirstArticleNumber(channel
0739: .getLastArticleNumber());
0740: }
0741: } else {
0742: channel.setFirstArticleNumber(firstArticle);
0743: }
0744: }
0745:
0746: }
0747:
0748: } catch (SQLException se) {
0749: throw new RuntimeException(se);
0750: } finally {
0751: try {
0752: if (rs != null)
0753: rs.close();
0754: } catch (SQLException se) {
0755: }
0756: try {
0757: if (ps != null)
0758: ps.close();
0759: } catch (SQLException se) {
0760: }
0761: try {
0762: if (conn != null)
0763: conn.close();
0764: } catch (SQLException se) {
0765: }
0766: }
0767:
0768: }
0769:
0770: public void deleteChannel(Channel channel) {
0771: Connection conn = null;
0772: PreparedStatement ps = null;
0773:
0774: try {
0775: conn = DriverManager
0776: .getConnection(DBManager.POOL_CONNECT_STRING);
0777:
0778: ps = conn
0779: .prepareStatement("DELETE FROM items WHERE channel = ?");
0780:
0781: int paramCount = 1;
0782: ps.setInt(paramCount++, channel.getId());
0783: ps.executeUpdate();
0784: ps.close();
0785:
0786: ps = conn
0787: .prepareStatement("DELETE FROM channels WHERE id = ?");
0788:
0789: paramCount = 1;
0790: ps.setInt(paramCount++, channel.getId());
0791: ps.executeUpdate();
0792:
0793: } catch (SQLException se) {
0794: throw new RuntimeException(se);
0795: } finally {
0796: try {
0797: if (ps != null)
0798: ps.close();
0799: } catch (SQLException se) {
0800: }
0801: try {
0802: if (conn != null)
0803: conn.close();
0804: } catch (SQLException se) {
0805: }
0806: }
0807:
0808: }
0809:
0810: private Item readItemFromRS(ResultSet rs, Channel channel)
0811: throws SQLException {
0812: Item item = new Item(rs.getInt("articleNumber"), rs
0813: .getString("signature"));
0814: item.setChannel(channel);
0815: item.setDate(rs.getTimestamp("dtStamp"));
0816: item.setTitle(rs.getString("title"));
0817: item.setDescription(rs.getString("description"));
0818: item.setComments(rs.getString("comments"));
0819: item.setLink(rs.getString("link"));
0820:
0821: return item;
0822: }
0823:
0824: public Item loadItem(Channel channel, int articleNumber) {
0825: Item item = null;
0826: Connection conn = null;
0827: PreparedStatement ps = null;
0828: ResultSet rs = null;
0829: try {
0830: conn = DriverManager
0831: .getConnection(DBManager.POOL_CONNECT_STRING);
0832: ps = conn
0833: .prepareStatement("SELECT * FROM items WHERE articleNumber = ? AND channel = ?");
0834: int paramCount = 1;
0835: ps.setInt(paramCount++, articleNumber);
0836: ps.setInt(paramCount++, channel.getId());
0837: rs = ps.executeQuery();
0838:
0839: if (rs != null) {
0840: if (rs.next()) {
0841: item = readItemFromRS(rs, channel);
0842: }
0843: }
0844: } catch (SQLException se) {
0845: throw new RuntimeException(se);
0846: } finally {
0847: try {
0848: if (rs != null)
0849: rs.close();
0850: } catch (SQLException se) {
0851: }
0852: try {
0853: if (ps != null)
0854: ps.close();
0855: } catch (SQLException se) {
0856: }
0857: try {
0858: if (conn != null)
0859: conn.close();
0860: } catch (SQLException se) {
0861: }
0862: }
0863:
0864: return item;
0865: }
0866:
0867: public Item loadNextItem(Channel channel, int relativeArticleNumber) {
0868: return loadRelativeItem(
0869: channel,
0870: relativeArticleNumber,
0871: "SELECT TOP 1 * FROM items WHERE articleNumber > ? AND channel = ? ORDER BY articlenumber");
0872: }
0873:
0874: public Item loadPreviousItem(Channel channel,
0875: int relativeArticleNumber) {
0876: return loadRelativeItem(
0877: channel,
0878: relativeArticleNumber,
0879: "SELECT TOP 1 * FROM items WHERE articleNumber < ? AND channel = ? ORDER BY articlenumber DESC");
0880: }
0881:
0882: private Item loadRelativeItem(Channel channel,
0883: int previousArticleNumber, String sql) {
0884: Item item = null;
0885: Connection conn = null;
0886: PreparedStatement ps = null;
0887: ResultSet rs = null;
0888: try {
0889: conn = DriverManager
0890: .getConnection(DBManager.POOL_CONNECT_STRING);
0891: ps = conn.prepareStatement(sql);
0892: int paramCount = 1;
0893: ps.setInt(paramCount++, previousArticleNumber);
0894: ps.setInt(paramCount++, channel.getId());
0895: rs = ps.executeQuery();
0896:
0897: if (rs != null) {
0898: if (rs.next()) {
0899: item = readItemFromRS(rs, channel);
0900: }
0901: }
0902: } catch (SQLException se) {
0903: throw new RuntimeException(se);
0904: } finally {
0905: try {
0906: if (rs != null)
0907: rs.close();
0908: } catch (SQLException se) {
0909: }
0910: try {
0911: if (ps != null)
0912: ps.close();
0913: } catch (SQLException se) {
0914: }
0915: try {
0916: if (conn != null)
0917: conn.close();
0918: } catch (SQLException se) {
0919: }
0920: }
0921:
0922: return item;
0923: }
0924:
0925: public Item loadItem(Channel channel, String signature) {
0926: Item item = null;
0927: Connection conn = null;
0928: PreparedStatement ps = null;
0929: ResultSet rs = null;
0930: try {
0931: conn = DriverManager
0932: .getConnection(DBManager.POOL_CONNECT_STRING);
0933: ps = conn
0934: .prepareStatement("SELECT * FROM items WHERE signature = ? AND channel = ?");
0935: int paramCount = 1;
0936: ps.setString(paramCount++, signature);
0937: ps.setInt(paramCount++, channel.getId());
0938: rs = ps.executeQuery();
0939:
0940: if (rs != null) {
0941: if (rs.next()) {
0942: item = readItemFromRS(rs, channel);
0943: }
0944: }
0945: } catch (SQLException se) {
0946: throw new RuntimeException(se);
0947: } finally {
0948: try {
0949: if (rs != null)
0950: rs.close();
0951: } catch (SQLException se) {
0952: }
0953: try {
0954: if (ps != null)
0955: ps.close();
0956: } catch (SQLException se) {
0957: }
0958: try {
0959: if (conn != null)
0960: conn.close();
0961: } catch (SQLException se) {
0962: }
0963: }
0964:
0965: return item;
0966: }
0967:
0968: /**
0969: * Method loadItems.
0970: * @param channel
0971: * @param articleRange
0972: * @param onlyHeaders
0973: * @return List
0974: *
0975: * articleRange
0976: * -1 = open ended search (all items from article number,
0977: * all items to article number)
0978: */
0979:
0980: public List loadItems(Channel channel, int[] articleRange,
0981: boolean onlyHeaders) {
0982: List items = new ArrayList();
0983: Connection conn = null;
0984: PreparedStatement ps = null;
0985: ResultSet rs = null;
0986: try {
0987: conn = DriverManager
0988: .getConnection(DBManager.POOL_CONNECT_STRING);
0989: if (articleRange[0] != AppConstants.OPEN_ENDED_RANGE
0990: && articleRange[1] != AppConstants.OPEN_ENDED_RANGE) {
0991: ps = conn
0992: .prepareStatement("SELECT * FROM items WHERE articleNumber >= ? and articleNumber <= ? AND channel = ? ORDER BY articleNumber");
0993: } else if (articleRange[0] == AppConstants.OPEN_ENDED_RANGE
0994: && articleRange[1] != AppConstants.OPEN_ENDED_RANGE) {
0995: ps = conn
0996: .prepareStatement("SELECT * FROM items WHERE articleNumber <= ? AND channel = ? ORDER BY articleNumber");
0997: } else if (articleRange[1] == AppConstants.OPEN_ENDED_RANGE
0998: && articleRange[0] != AppConstants.OPEN_ENDED_RANGE) {
0999: ps = conn
1000: .prepareStatement("SELECT * FROM items WHERE articleNumber >= ? AND channel = ? ORDER BY articleNumber");
1001: } else {
1002: ps = conn
1003: .prepareStatement("SELECT * FROM items WHERE channel = ? ORDER BY articleNumber");
1004: }
1005:
1006: int paramCount = 1;
1007:
1008: if (articleRange[0] != AppConstants.OPEN_ENDED_RANGE) {
1009: ps.setInt(paramCount++, articleRange[0]);
1010: }
1011:
1012: if (articleRange[1] != AppConstants.OPEN_ENDED_RANGE) {
1013: ps.setInt(paramCount++, articleRange[1]);
1014: }
1015:
1016: ps.setInt(paramCount++, channel.getId());
1017: rs = ps.executeQuery();
1018:
1019: if (rs != null) {
1020: while (rs.next()) {
1021: Item item = new Item(rs.getInt("articleNumber"), rs
1022: .getString("signature"));
1023: item.setChannel(channel);
1024: item.setDate(rs.getTimestamp("dtStamp"));
1025: item.setTitle(rs.getString("title"));
1026:
1027: if (!onlyHeaders) {
1028: item
1029: .setDescription(rs
1030: .getString("description"));
1031: item.setLink(rs.getString("link"));
1032: item.setComments(rs.getString("comments"));
1033: }
1034: items.add(item);
1035: }
1036: }
1037: } catch (SQLException se) {
1038: throw new RuntimeException(se);
1039: } finally {
1040: try {
1041: if (rs != null)
1042: rs.close();
1043: } catch (SQLException se) {
1044: }
1045: try {
1046: if (ps != null)
1047: ps.close();
1048: } catch (SQLException se) {
1049: }
1050: try {
1051: if (conn != null)
1052: conn.close();
1053: } catch (SQLException se) {
1054: }
1055: }
1056:
1057: return items;
1058: }
1059:
1060: /**
1061: * Method loadArticleNumbers
1062: * @param channel
1063: * @return List
1064: *
1065: * Supports NNTP listgroup command
1066: */
1067:
1068: public List loadArticleNumbers(Channel channel) {
1069:
1070: List articleNumbers = new ArrayList();
1071: Connection conn = null;
1072: PreparedStatement ps = null;
1073: ResultSet rs = null;
1074: try {
1075: conn = DriverManager
1076: .getConnection(DBManager.POOL_CONNECT_STRING);
1077: ps = conn
1078: .prepareStatement("SELECT articleNumber FROM items WHERE channel = ? ORDER BY articleNumber");
1079:
1080: int paramCount = 1;
1081: ps.setInt(paramCount++, channel.getId());
1082:
1083: rs = ps.executeQuery();
1084:
1085: if (rs != null) {
1086: while (rs.next()) {
1087: articleNumbers.add(new Integer(rs
1088: .getInt("articleNumber")));
1089: }
1090: }
1091: } catch (SQLException se) {
1092: throw new RuntimeException(se);
1093: } finally {
1094: try {
1095: if (rs != null)
1096: rs.close();
1097: } catch (SQLException se) {
1098: }
1099: try {
1100: if (ps != null)
1101: ps.close();
1102: } catch (SQLException se) {
1103: }
1104: try {
1105: if (conn != null)
1106: conn.close();
1107: } catch (SQLException se) {
1108: }
1109: }
1110:
1111: return articleNumbers;
1112: }
1113:
1114: public void saveItem(Item item) {
1115: Connection conn = null;
1116: PreparedStatement ps = null;
1117:
1118: try {
1119: conn = DriverManager
1120: .getConnection(DBManager.POOL_CONNECT_STRING);
1121: ps = conn
1122: .prepareStatement("INSERT INTO items "
1123: + "(articlenumber, channel, title, link, description, comments, dtstamp, signature) "
1124: + "VALUES(?,?,?,?,?,?,?,?)");
1125:
1126: int paramCount = 1;
1127: ps.setInt(paramCount++, item.getArticleNumber());
1128: ps.setInt(paramCount++, item.getChannel().getId());
1129: ps.setString(paramCount++, item.getTitle());
1130: ps.setString(paramCount++, item.getLink());
1131: ps.setString(paramCount++, item.getDescription());
1132: ps.setString(paramCount++, item.getComments());
1133: ps.setTimestamp(paramCount++, new Timestamp(item.getDate()
1134: .getTime()));
1135: ps.setString(paramCount++, item.getSignature());
1136: ps.executeUpdate();
1137:
1138: } catch (SQLException se) {
1139: throw new RuntimeException(se);
1140: } finally {
1141: try {
1142: if (ps != null)
1143: ps.close();
1144: } catch (SQLException se) {
1145: }
1146: try {
1147: if (conn != null)
1148: conn.close();
1149: } catch (SQLException se) {
1150: }
1151: }
1152:
1153: }
1154:
1155: public Set getItemSignatures(int channelId) {
1156: Set signatures = new HashSet();
1157: Connection conn = null;
1158: PreparedStatement ps = null;
1159: ResultSet rs = null;
1160:
1161: try {
1162: conn = DriverManager
1163: .getConnection(DBManager.POOL_CONNECT_STRING);
1164: ps = conn
1165: .prepareStatement("SELECT signature FROM items WHERE channel = ?");
1166:
1167: int paramCount = 1;
1168: ps.setInt(paramCount++, channelId);
1169:
1170: rs = ps.executeQuery();
1171:
1172: if (rs != null) {
1173: while (rs.next()) {
1174: signatures.add(rs.getString("signature"));
1175: }
1176: }
1177: } catch (SQLException se) {
1178: throw new RuntimeException(se);
1179: } finally {
1180: try {
1181: if (rs != null)
1182: rs.close();
1183: } catch (SQLException se) {
1184: }
1185: try {
1186: if (ps != null)
1187: ps.close();
1188: } catch (SQLException se) {
1189: }
1190: try {
1191: if (conn != null)
1192: conn.close();
1193: } catch (SQLException se) {
1194: }
1195: }
1196:
1197: return signatures;
1198: }
1199:
1200: public void saveConfiguration(ChannelManager rssManager) {
1201: Connection conn = null;
1202: PreparedStatement ps = null;
1203:
1204: try {
1205: conn = DriverManager
1206: .getConnection(DBManager.POOL_CONNECT_STRING);
1207: ps = conn.prepareStatement("UPDATE config "
1208: + "SET pollingInterval = ?, " + "proxyServer = ?, "
1209: + "proxyPort = ?, " + "proxyUserID = ?, "
1210: + "proxyPassword = ?");
1211:
1212: int paramCount = 1;
1213: ps.setLong(paramCount++, rssManager
1214: .getPollingIntervalSeconds());
1215: ps.setString(paramCount++, rssManager.getProxyServer());
1216: ps.setInt(paramCount++, rssManager.getProxyPort());
1217: ps.setString(paramCount++, rssManager.getProxyUserID());
1218: ps.setString(paramCount++, rssManager.getProxyPassword());
1219: ps.executeUpdate();
1220:
1221: } catch (SQLException se) {
1222: throw new RuntimeException(se);
1223: } finally {
1224: try {
1225: if (ps != null)
1226: ps.close();
1227: } catch (SQLException se) {
1228: }
1229: try {
1230: if (conn != null)
1231: conn.close();
1232: } catch (SQLException se) {
1233: }
1234: }
1235:
1236: }
1237:
1238: public void saveConfiguration(NNTPServer nntpServer) {
1239: Connection conn = null;
1240: PreparedStatement ps = null;
1241:
1242: try {
1243: conn = DriverManager
1244: .getConnection(DBManager.POOL_CONNECT_STRING);
1245: ps = conn.prepareStatement("UPDATE config "
1246: + "SET contentType = ?, nntpSecure = ?");
1247:
1248: int paramCount = 1;
1249: ps.setInt(paramCount++, nntpServer.getContentType());
1250: ps.setBoolean(paramCount++, nntpServer.isSecure());
1251: ps.executeUpdate();
1252:
1253: } catch (SQLException se) {
1254: throw new RuntimeException(se);
1255: } finally {
1256: try {
1257: if (ps != null)
1258: ps.close();
1259: } catch (SQLException se) {
1260: }
1261: try {
1262: if (conn != null)
1263: conn.close();
1264: } catch (SQLException se) {
1265: }
1266: }
1267:
1268: }
1269:
1270: }
|