001: /*
002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
003: C/Oña, 107 1º2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.forums;
034:
035: import java.sql.SQLException;
036: import java.sql.CallableStatement;
037: import java.sql.ResultSet;
038: import java.sql.ResultSetMetaData;
039: import java.sql.PreparedStatement;
040: import java.sql.Statement;
041:
042: import com.knowgate.debug.DebugFile;
043: import com.knowgate.jdc.JDCConnection;
044: import com.knowgate.dataobjs.DB;
045: import com.knowgate.dataobjs.DBSubset;
046:
047: import com.knowgate.hipergate.Category;
048: import com.knowgate.hipergate.Product;
049:
050: /**
051: * <p>NewsGroup</p>
052: * @author Sergio Montoro Ten
053: * @version 2.0
054: */
055: public class NewsGroup extends Category {
056:
057: /**
058: * Create empty newsgroup
059: */
060: public NewsGroup() {
061: super (DB.k_newsgroups, "NewsGroup");
062: }
063:
064: // ----------------------------------------------------------
065:
066: /**
067: * Create NewsGroup and set its Category GUID
068: * @param sIdNewsGroup GUID of NewsGroup/Category
069: * @throws SQLException
070: */
071: public NewsGroup(String sIdNewsGroup) throws SQLException {
072: super (DB.k_newsgroups, "NewsGroup");
073:
074: put(DB.gu_category, sIdNewsGroup);
075: put(DB.gu_newsgrp, sIdNewsGroup);
076: }
077:
078: // ----------------------------------------------------------
079:
080: /**
081: * <p>Create newsGroup and load properties from Database</p>
082: * Both field sets from k_categories and k_newsgroups are loaded into
083: * internal properties collection upon load.
084: * @param oConn Database Conenction
085: * @param sIdNewsGroup GUID of newsGroup to be loaded
086: * @throws SQLException
087: */
088: public NewsGroup(JDCConnection oConn, String sIdNewsGroup)
089: throws SQLException {
090: super (DB.k_newsgroups, "NewsGroup");
091:
092: load(oConn, new Object[] { sIdNewsGroup });
093: }
094:
095: // ----------------------------------------------------------
096:
097: /**
098: * <p>Count messages for this NewsGroup</p>
099: * @param oConn Database connection
100: * @return Message Count
101: * @throws SQLException If NewsGroup does not exist
102: */
103: public int countMessages(JDCConnection oConn) throws SQLException {
104: int iRetVal;
105:
106: if (DebugFile.trace) {
107: DebugFile
108: .writeln("Begin NewsGroup.countMessages([Connection])");
109: DebugFile.incIdent();
110:
111: }
112:
113: if (DebugFile.trace)
114: DebugFile
115: .writeln("Connection.prepareStatement(SELECT COUNT(*) FROM "
116: + DB.k_x_cat_objs
117: + " WHERE "
118: + DB.gu_category
119: + "='"
120: + getStringNull(DB.gu_newsgrp, "null")
121: + "' AND "
122: + DB.id_class
123: + "="
124: + String.valueOf(NewsMessage.ClassId) + ")");
125:
126: PreparedStatement oStmt = oConn
127: .prepareStatement("SELECT COUNT(*) FROM "
128: + DB.k_x_cat_objs + " WHERE " + DB.gu_category
129: + "=? AND " + DB.id_class + "="
130: + String.valueOf(NewsMessage.ClassId));
131:
132: oStmt.setString(1, getString(DB.gu_newsgrp));
133:
134: ResultSet oRSet = oStmt.executeQuery();
135:
136: if (oRSet.next()) {
137: iRetVal = Integer.parseInt(oRSet.getObject(1).toString());
138: oRSet.close();
139: oStmt.close();
140: } else {
141: oRSet.close();
142: oStmt.close();
143: throw new SQLException("NewsGroup "
144: + getString(DB.gu_newsgrp) + " not found", "42000");
145: }
146:
147: if (DebugFile.trace) {
148: DebugFile.decIdent();
149: DebugFile.writeln("End NewsGroup.countMessages() : "
150: + String.valueOf(iRetVal));
151: }
152:
153: return iRetVal;
154: } // countMessages
155:
156: // ----------------------------------------------------------
157:
158: /**
159: * <p>Count messages for this NewsGroup in a given status</p>
160: * @param oConn Database connection
161: * @param iMsgStatus One of { NewsMessage.STATUS_VALIDATED, NewsMessage.STATUS_PENDING, NewsMessage.STATUS_DISCARDED, NewsMessage.STATUS_EXPIRED }
162: * @return Message Count
163: * @throws SQLException If NewsGroup does not exist
164: */
165: public int countMessages(JDCConnection oConn, short iMsgStatus)
166: throws SQLException {
167: int iRetVal;
168:
169: if (DebugFile.trace) {
170: DebugFile
171: .writeln("Begin NewsGroup.countMessages([Connection], "
172: + String.valueOf(iMsgStatus) + ")");
173: DebugFile.incIdent();
174: }
175:
176: if (DebugFile.trace)
177: DebugFile
178: .writeln("Connection.prepareStatement(SELECT COUNT(x."
179: + DB.gu_object
180: + ") FROM "
181: + DB.k_x_cat_objs
182: + " x, "
183: + DB.k_newsmsgs
184: + " m WHERE m."
185: + DB.gu_msg
186: + "="
187: + "x."
188: + DB.gu_object
189: + " AND m."
190: + DB.id_status
191: + "="
192: + String.valueOf(iMsgStatus)
193: + " AND x."
194: + DB.gu_category
195: + "='"
196: + getStringNull(DB.gu_newsgrp, "null")
197: + "' AND x."
198: + DB.id_class
199: + "="
200: + String.valueOf(NewsMessage.ClassId) + ")");
201:
202: PreparedStatement oStmt = oConn
203: .prepareStatement("SELECT COUNT(x." + DB.gu_object
204: + ") FROM " + DB.k_x_cat_objs + " x, "
205: + DB.k_newsmsgs + " m WHERE m." + DB.gu_msg
206: + "=" + "x." + DB.gu_object + " AND m."
207: + DB.id_status + "=?" + " AND x."
208: + DB.gu_category + "=? AND x." + DB.id_class
209: + "=" + String.valueOf(NewsMessage.ClassId));
210:
211: oStmt.setShort(1, iMsgStatus);
212: oStmt.setString(2, getString(DB.gu_newsgrp));
213:
214: ResultSet oRSet = oStmt.executeQuery();
215:
216: if (oRSet.next()) {
217: iRetVal = Integer.parseInt(oRSet.getObject(1).toString());
218: oRSet.close();
219: oStmt.close();
220: } else {
221: oRSet.close();
222: oStmt.close();
223: throw new SQLException("NewsGroup "
224: + getString(DB.gu_newsgrp) + " not found", "42000");
225: }
226:
227: if (DebugFile.trace) {
228: DebugFile.decIdent();
229: DebugFile.writeln("End NewsGroup.countMessages() : "
230: + String.valueOf(iRetVal));
231: }
232:
233: return iRetVal;
234: } // countMessages
235:
236: // ----------------------------------------------------------
237:
238: /**
239: * <p>Load NewsGroup from database</p>
240: * Both field sets from k_categories and k_newsgroups are loaded into
241: * internal properties collection upon load.
242: * @param oConn Database Conenction
243: * @param PKVals A single element array containing the GUID of NewsGroup to be
244: * loaded. For example: oNewsGrpObj.load(oConnection, new object[]{"123456789012345678901234567890AB"});
245: * @return <b>true</b> if NewsGroup was successfully loaded, <b>false</b> if
246: * Newsgroup GUID was not found at k_newsgropus o k_categories tables.
247: * @throws SQLException
248: */
249: public boolean load(JDCConnection oConn, Object[] PKVals)
250: throws SQLException {
251: boolean bRetVal;
252: PreparedStatement oStmt;
253: ResultSet oRSet;
254: ResultSetMetaData oMDat;
255: int iColCount;
256: String sColName;
257: String sSQL;
258:
259: if (DebugFile.trace) {
260: DebugFile
261: .writeln("Begin NewsGroup.load([Connection], ...)");
262: DebugFile.incIdent();
263: DebugFile.writeln("gu_newsgrp=" + (String) PKVals[0]);
264: }
265:
266: clear();
267:
268: sSQL = "SELECT * FROM " + DB.k_categories + " WHERE "
269: + DB.gu_category + "=?";
270:
271: if (DebugFile.trace)
272: DebugFile.writeln("Connection.prepareStatement(" + sSQL
273: + ")");
274:
275: oStmt = oConn
276: .prepareStatement(sSQL, ResultSet.TYPE_FORWARD_ONLY,
277: ResultSet.CONCUR_READ_ONLY);
278:
279: oStmt.setString(1, (String) PKVals[0]);
280:
281: oRSet = oStmt.executeQuery();
282:
283: bRetVal = oRSet.next();
284:
285: if (bRetVal) {
286: oMDat = oRSet.getMetaData();
287: iColCount = oMDat.getColumnCount();
288:
289: for (int c = 1; c <= iColCount; c++) {
290: sColName = oMDat.getColumnName(c).toLowerCase();
291: if (!sColName.equalsIgnoreCase(DB.dt_created))
292: ;
293: put(sColName, oRSet.getObject(c));
294: } // next
295: oMDat = null;
296: } // fi (bRetVal)
297:
298: oRSet.close();
299: oStmt.close();
300:
301: if (bRetVal) {
302: sSQL = "SELECT * FROM " + DB.k_newsgroups + " WHERE "
303: + DB.gu_newsgrp + "=?";
304: if (DebugFile.trace)
305: DebugFile.writeln("Connection.prepareStatement(" + sSQL
306: + ")");
307: oStmt = oConn.prepareStatement(sSQL,
308: ResultSet.TYPE_FORWARD_ONLY,
309: ResultSet.CONCUR_READ_ONLY);
310: oStmt.setString(1, (String) PKVals[0]);
311: oRSet = oStmt.executeQuery();
312: bRetVal = oRSet.next();
313: if (bRetVal) {
314: oMDat = oRSet.getMetaData();
315: iColCount = oMDat.getColumnCount();
316:
317: for (int c = 1; c <= iColCount; c++) {
318: sColName = oMDat.getColumnName(c).toLowerCase();
319: if (!sColName.equalsIgnoreCase(DB.dt_created))
320: ;
321: put(sColName, oRSet.getObject(c));
322: } // next
323: oMDat = null;
324: }
325: oRSet.close();
326: oStmt.close();
327: }
328:
329: if (DebugFile.trace) {
330: DebugFile.decIdent();
331: DebugFile.writeln("End NewsGroup.load() : "
332: + String.valueOf(bRetVal));
333: }
334:
335: return bRetVal;
336: } // load
337:
338: // ----------------------------------------------------------
339:
340: /**
341: * <p>Get ACLUsers subscribed to this NewsGroup</p>
342: * @param oConn JDBC Database Connection
343: * @return A DBSubset with the following columns:<br>
344: * <table>
345: * <tr><td><b>gu_user</b></td><td><b>tx_email</b></td><td><b>id_msg_type</b></td><td><b>tp_subscrip</b></td></tr>
346: * <tr><td>ACLUser GUID</td><td><b>ACLUser main e-mail</b></td><td>Message Format {TXT | HTM}</td><td>Message Grouping {GROUP_NONE | GROUP_DIGEST}</td></tr>
347: * </table>
348: * @throws SQLException
349: */
350: public DBSubset subscribers(JDCConnection oConn)
351: throws SQLException {
352:
353: if (DebugFile.trace) {
354: DebugFile
355: .writeln("Begin NewsGroup.subscribers ([Connection])");
356: DebugFile.incIdent();
357: DebugFile.writeln("gu_newsgrp="
358: + getStringNull(DB.gu_newsgrp, "null"));
359: }
360:
361: DBSubset oSubs = new DBSubset(DB.k_newsgroup_subscriptions,
362: DB.gu_user + "," + DB.tx_email + "," + DB.id_msg_type
363: + "," + DB.tp_subscrip, DB.gu_newsgrp
364: + "=? AND " + DB.id_status + "="
365: + String.valueOf(Subscription.ACTIVE), 100);
366:
367: oSubs.load(oConn, new Object[] { getString(DB.gu_newsgrp) });
368:
369: if (DebugFile.trace) {
370: DebugFile.decIdent();
371: DebugFile.writeln("End NewsGroup.subscribers() : "
372: + String.valueOf(oSubs.getRowCount()));
373: }
374:
375: return oSubs;
376: } // subscribers
377:
378: // ----------------------------------------------------------
379:
380: /**
381: * <p>Get whether or not a user is subcribed this news group</p>
382: * @param oConn JDBC Database Connection
383: * @param sUserId User GUID
384: * @return <b>true</b> if user is subscribed to this news group and he is active (k_newsgroup_subscriptions.id_status=1),
385: * <b>false</b> if user is not subscribed or if he is subscribed but unactive (k_newsgroup_subscriptions.id_status=0)
386: * @throws SQLException
387: */
388: public boolean isSubscriber(JDCConnection oConn, String sUserId)
389: throws SQLException {
390:
391: PreparedStatement oStmt = oConn
392: .prepareStatement("SELECT " + DB.tx_email + " FROM "
393: + DB.k_newsgroup_subscriptions + " WHERE "
394: + DB.gu_newsgrp + "=? AND " + DB.gu_user
395: + "=? AND " + DB.id_status + "="
396: + String.valueOf(Subscription.ACTIVE),
397: ResultSet.TYPE_FORWARD_ONLY,
398: ResultSet.CONCUR_READ_ONLY);
399:
400: oStmt.setString(1, getString(DB.gu_newsgrp));
401: oStmt.setString(2, sUserId);
402:
403: ResultSet oRSet = oStmt.executeQuery();
404:
405: boolean bSubscriber = oRSet.next();
406:
407: oRSet.close();
408: oStmt.close();
409:
410: return bSubscriber;
411: }
412:
413: // ----------------------------------------------------------
414:
415: /**
416: * <p>Delete this NewsGroup and all its messages</p>
417: * @param oConn JDBC Database Connection
418: * @return
419: * @throws SQLException
420: */
421:
422: public boolean delete(JDCConnection oConn) throws SQLException {
423: return NewsGroup.delete(oConn, getString(DB.gu_newsgrp));
424: }
425:
426: // **********************************************************
427: // Static Methods
428:
429: /**
430: * <p>Store Newsgroup</p>
431: * @param oConn atabase Connection
432: * @param iDomain Identifier of Domain to with the NewsGroup will belong.
433: * @param sWorkArea GUID of WorkArea to with the NewsGroup will belong.
434: * @param sCategoryId Category GUID (newsgroups are subregisters of categories)
435: * @param sParentId GUID of Parent Group (groups, as categories, are hierarchical)
436: * @param sCategoryName Category name (k_categories.nm_category)
437: * @param iIsActive 1 if group is activem, 0 if it is inactive.
438: * @param iDocStatus Initial Document Status. One of { Newsgroup.FREE, Newsgroup.MODERATED }
439: * @param sOwner GUID of User owner of this NewsGroup
440: * @param sIcon1 Closed Folder Icon
441: * @param sIcon2 Opened Folder Icon
442: * @return GUID of newly created NewsGroup
443: * @throws SQLException
444: */
445: public static String store(JDCConnection oConn, int iDomain,
446: String sWorkArea, String sCategoryId, String sParentId,
447: String sCategoryName, short iIsActive, int iDocStatus,
448: String sOwner, String sIcon1, String sIcon2)
449: throws SQLException {
450: String sCatId = Category.store(oConn, sCategoryId, sParentId,
451: sCategoryName, iIsActive, iDocStatus, sOwner, sIcon1,
452: sIcon2);
453:
454: NewsGroup oGrp = new NewsGroup(sCatId);
455: oGrp.put(DB.id_domain, iDomain);
456: oGrp.put(DB.gu_workarea, sWorkArea);
457: oGrp.put(DB.bo_binaries, (short) 0);
458:
459: oGrp.store(oConn);
460:
461: return sCatId;
462: } // store
463:
464: /**
465: * <p>Delete NewsGroup and all its messages.</p>
466: * Delete all files attached to messages contained in group and then call
467: * k_sp_del_newsgroup stored procedure.<br>
468: * @param oConn Database Connection
469: * @param sNewsGroupGUID GUID of NewsGroup to be deleted.
470: * @throws SQLException
471: * @see com.knowgate.hipergate.Product#delete(JDCConnection)
472: */
473: public static boolean delete(JDCConnection oConn,
474: String sNewsGroupGUID) throws SQLException {
475: Statement oStmt;
476: ResultSet oRSet;
477: String sProductId;
478: Product oProd;
479: String sSQL;
480:
481: if (DebugFile.trace) {
482: DebugFile.writeln("Begin NewsGroup.delete([Connection], "
483: + sNewsGroupGUID + ")");
484: DebugFile.incIdent();
485: }
486:
487: // Borrar los archivos adjuntos
488: sSQL = "SELECT " + DB.gu_product + "," + DB.gu_msg + " FROM "
489: + DB.k_newsmsgs + " WHERE " + DB.gu_product
490: + " IS NOT NULL AND " + DB.gu_msg + " IN (SELECT "
491: + DB.gu_object + " FROM " + DB.k_x_cat_objs + " WHERE "
492: + DB.gu_category + "='" + sNewsGroupGUID + "')";
493: oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
494: ResultSet.CONCUR_UPDATABLE);
495: if (DebugFile.trace)
496: DebugFile.writeln("Statement.executeQuery(" + sSQL + ")");
497: oRSet = oStmt.executeQuery(sSQL);
498: while (oRSet.next()) {
499: sProductId = oRSet.getString(1);
500:
501: if (DebugFile.trace)
502: DebugFile
503: .writeln("ResultSet.updateString gu_product = "
504: + sProductId + " to NULL");
505:
506: oRSet.updateString(1, null);
507:
508: if (DebugFile.trace)
509: DebugFile.writeln("ResultSet.updateRow();");
510:
511: oRSet.updateRow();
512:
513: if (DebugFile.trace)
514: DebugFile.writeln("new Product([Connection], "
515: + sProductId + ")");
516:
517: oProd = new Product(oConn, sProductId);
518:
519: oProd.delete(oConn);
520: } // wend
521: oRSet.close();
522: oStmt.close();
523:
524: // Borrar los mensajes y la categoría subyacente
525: CallableStatement oCall;
526:
527: if (DebugFile.trace)
528: DebugFile
529: .writeln("Connection.prepareCall({ call k_sp_del_newsgroup('"
530: + sNewsGroupGUID + "') })");
531:
532: oCall = oConn.prepareCall("{ call k_sp_del_newsgroup('"
533: + sNewsGroupGUID + "') }");
534:
535: if (DebugFile.trace)
536: DebugFile
537: .writeln("CallableStatement.execute({ call k_sp_del_newsgroup('"
538: + sNewsGroupGUID + "') })");
539: oCall.execute();
540: oCall.close();
541:
542: return true;
543: } // delete
544:
545: // **********************************************************
546: // Constantes Publicas
547:
548: public static final short ClassId = 30;
549:
550: public static final short FREE = 0;
551: public static final short MODERATED = 1;
552:
553: } // NewsGroup
|