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.util.Date;
036:
037: import java.io.IOException;
038: import java.io.Reader;
039:
040: import java.sql.SQLException;
041: import java.sql.CallableStatement;
042: import java.sql.Statement;
043: import java.sql.PreparedStatement;
044: import java.sql.ResultSet;
045: import java.sql.ResultSetMetaData;
046: import java.sql.Timestamp;
047: import java.sql.Types;
048:
049: import com.knowgate.debug.DebugFile;
050: import com.knowgate.jdc.JDCConnection;
051: import com.knowgate.misc.Gadgets;
052:
053: import com.knowgate.dataobjs.DB;
054: import com.knowgate.dataobjs.DBBind;
055: import com.knowgate.dataobjs.DBPersist;
056: import com.knowgate.dataobjs.DBSubset;
057:
058: import com.knowgate.hipergate.Product;
059:
060: /**
061: * <p>NewsMessage</p>
062: * @author Sergio Montoro Ten
063: * @version 3.0
064: */
065: public class NewsMessage extends DBPersist {
066:
067: /**
068: * Create empty NewsMessage
069: */
070: public NewsMessage() {
071: super (DB.k_newsmsgs, "NewsMessage");
072: }
073:
074: // ----------------------------------------------------------
075:
076: /**
077: * Post a Plain Text Message
078: * @param oConn Database Conenction
079: * @param sNewsGroupId GUID of NewsGroup for posting
080: * @param sThreadId GUID of message thread (may be <b>null</b>)
081: * @param dtStart Start publishing date (may be <b>null</b>)
082: * @param dtEnd Expiration date (may be <b>null</b>)
083: * @param iStatus STATUS_VALIDATED or STATUS_PENDING
084: * @param sText Message Text
085: * @return GUID of new message
086: * @throws SQLException
087: */
088: public String post(JDCConnection oConn, String sNewsGroupId,
089: String sThreadId, Date dtStart, Date dtEnd, short iStatus,
090: String sText) throws SQLException {
091:
092: if (DebugFile.trace) {
093: DebugFile.writeln("Begin NewsMessage.post([Connection], "
094: + sNewsGroupId + "," + sThreadId + ")");
095: DebugFile.incIdent();
096: }
097:
098: String sRetVal;
099:
100: remove(DB.gu_newsgrp);
101: if (sNewsGroupId != null)
102: put(DB.gu_newsgrp, sNewsGroupId);
103:
104: remove(DB.gu_thread_msg);
105: if (sThreadId != null)
106: put(DB.gu_thread_msg, sThreadId);
107:
108: remove(DB.dt_start);
109: if (dtStart != null)
110: put(DB.dt_start, dtStart);
111:
112: remove(DB.dt_end);
113: if (dtEnd != null)
114: put(DB.dt_end, dtEnd);
115:
116: remove(DB.id_status);
117: put(DB.id_status, iStatus);
118:
119: remove(DB.tx_msg);
120: if (sText != null)
121: put(DB.tx_msg, sText);
122:
123: if (store(oConn))
124: sRetVal = getString(DB.gu_msg);
125: else
126: sRetVal = null;
127:
128: if (DebugFile.trace) {
129: DebugFile.decIdent();
130: DebugFile.writeln("End NewsMessage.post() : " + sRetVal);
131: }
132:
133: return sRetVal;
134: } // post
135:
136: // ----------------------------------------------------------
137:
138: /**
139: * Load message
140: * @param oConn JDCConnection
141: * @param sGuMsg String Message GUID
142: * @return boolean <b>true</b> if message was successfully loaded,
143: * <b>false</b> if no message with such GUID was found
144: * @throws SQLException
145: * @since 3.0
146: */
147: public boolean load(JDCConnection oConn, String sGuMsg)
148: throws SQLException {
149: final int BufferSize = 2048;
150: final String sSQL = "SELECT * FROM " + DB.k_newsmsgs
151: + " WHERE " + DB.gu_msg + "=?";
152: boolean bRetVal;
153: if (DebugFile.trace) {
154: DebugFile.writeln("Begin NewsMessage.load([Connection], "
155: + sGuMsg + ")");
156: DebugFile.incIdent();
157: DebugFile.writeln("Connection.prepareStatement(" + sSQL
158: + ")");
159: }
160: PreparedStatement oStmt = oConn
161: .prepareStatement(sSQL, ResultSet.TYPE_FORWARD_ONLY,
162: ResultSet.CONCUR_READ_ONLY);
163: oStmt.setString(1, sGuMsg);
164: ResultSet oRSet = oStmt.executeQuery();
165: bRetVal = oRSet.next();
166: if (bRetVal) {
167: ResultSetMetaData oMDat = oRSet.getMetaData();
168: int nCols = oMDat.getColumnCount();
169: int iType;
170: int iReaded;
171: for (int c = 1; c <= nCols; c++) {
172: iType = oMDat.getColumnType(c);
173: if ((iType != Types.LONGVARCHAR)
174: && (iType != Types.LONGVARBINARY)
175: && (iType != Types.CLOB)
176: && (iType != Types.BLOB)) {
177: put(oMDat.getColumnName(c).toLowerCase(), oRSet
178: .getObject(c));
179: } else if (iType == Types.LONGVARCHAR) {
180: char Buffer[] = new char[BufferSize];
181: StringBuffer oBody = new StringBuffer();
182: Reader oRead = oRSet.getCharacterStream(c);
183: if (null != oRead) {
184: try {
185: do {
186: iReaded = oRead.read(Buffer, 0,
187: BufferSize);
188: if (iReaded > 0)
189: oBody.append(Buffer, 0, iReaded);
190: } while (BufferSize == iReaded);
191: oRead.close();
192: } catch (IOException ioe) {
193: try {
194: oRSet.close();
195: } catch (Exception ignore) {
196: }
197: try {
198: oStmt.close();
199: } catch (Exception ignore) {
200: }
201: throw new SQLException(ioe.getMessage());
202: }
203: put(oMDat.getColumnName(c).toLowerCase(), oBody
204: .toString());
205: } // fi (oRead!=null)
206: }
207: } // next (c)
208: } // fi (bRetVal)
209: oRSet.close();
210: oStmt.close();
211:
212: if (DebugFile.trace) {
213: DebugFile.decIdent();
214: DebugFile.writeln("End NewsMessage.load() : "
215: + String.valueOf(bRetVal));
216: }
217:
218: return bRetVal;
219: } // load
220:
221: // ----------------------------------------------------------
222:
223: /**
224: * Get attachments
225: * @param oConn JDCConnection
226: * @return DBSubset listing attachments or <b>null</b> if this message has no attachments
227: * @throws SQLException
228: * @since 3.0
229: */
230: public DBSubset getAttachments(JDCConnection oConn)
231: throws SQLException {
232: Product oProd;
233: if (isNull(DB.gu_product))
234: return null;
235: else {
236: oProd = new Product(getString(DB.gu_product));
237: return oProd.getLocations(oConn);
238: }
239: } // getAttachments
240:
241: // ----------------------------------------------------------
242:
243: public boolean load(JDCConnection oConn, Object[] aPK)
244: throws SQLException {
245: return load(oConn, (String) aPK[0]);
246: }
247:
248: // ----------------------------------------------------------
249:
250: /**
251: * <p>Store NewsMessage</p>
252: * Message is posted into a NewsGroup by setting gu_newsgrp property of
253: * NewsMessage to the GUID of newsMessage that will contain it.<br>
254: * If gu_msg is <b>null</b> then a new GUID will be assigned.<br>
255: * If gu_thread_msg is <b>null</b> and gu_parent_msg is <b>null</b> then gu_thread_msg will be assigned the same value as gu_msg.<br>
256: * If gu_thread_msg is <b>null</b> and gu_parent_msg is not <b>null</b> then gu_thread_msg will be assigned the same the parent message thread.<br>
257: * If id_status is <b>null</b> then it will be assigned to NewsMessage.STATUS_PENDING.<br>
258: * If id_msg_type is <b>null</b> then it will be assigned to "TXT" by default.<br>
259: * If dt_start is <b>null</b> then message visibility will start inmediately.<br>
260: * If dt_end is <b>null</b> then message will never expire.<br>
261: * dt_published will always be set to the currents system date no matter with values it is passed as parameter.<br>
262: * nu_thread_msgs will be updated in all messages from this thread by calling k_sp_count_thread_msgs stored procedure.<br>
263: * Column k_newsgroups.dt_last_update is automatically set to the current date each time a new message is stored.
264: * @param oConn Database Connection
265: * @throws SQLException
266: */
267: public boolean store(JDCConnection oConn) throws SQLException {
268: boolean bNewMsg;
269: boolean bRetVal;
270: String sMsgId;
271: int nThreadMsgs;
272: ResultSet oRSet;
273: Statement oStmt;
274: CallableStatement oCall;
275: String sSQL;
276:
277: Timestamp dtNow = new Timestamp(DBBind.getTime());
278:
279: if (DebugFile.trace) {
280: DebugFile.writeln("Begin NewsMessage.store([Connection])");
281: DebugFile.incIdent();
282: }
283:
284: // Si no se especificó un identificador para el mensaje, entonces añadirlo automáticamente
285: if (!AllVals.containsKey(DB.gu_msg)) {
286: bNewMsg = true;
287: sMsgId = Gadgets.generateUUID();
288: put(DB.gu_msg, sMsgId);
289: } else {
290: bNewMsg = false;
291: sMsgId = getString(DB.gu_msg);
292: }
293:
294: if (!AllVals.containsKey(DB.id_status))
295: put(DB.id_status, STATUS_PENDING);
296:
297: if (!AllVals.containsKey(DB.gu_thread_msg))
298: if (AllVals.containsKey(DB.gu_parent_msg)) {
299: oStmt = oConn.createStatement(
300: ResultSet.TYPE_FORWARD_ONLY,
301: ResultSet.CONCUR_READ_ONLY);
302:
303: if (DebugFile.trace)
304: DebugFile.writeln("Statement.executeQuery(SELECT "
305: + DB.gu_thread_msg + " FROM "
306: + DB.k_newsmsgs + " WHERE " + DB.gu_msg
307: + "='"
308: + getStringNull(DB.gu_parent_msg, "null")
309: + "'");
310:
311: oRSet = oStmt.executeQuery("SELECT " + DB.gu_thread_msg
312: + " FROM " + DB.k_newsmsgs + " WHERE "
313: + DB.gu_msg + "='"
314: + getString(DB.gu_parent_msg) + "'");
315: if (oRSet.next())
316: put(DB.gu_thread_msg, oRSet.getString(1));
317: else
318: put(DB.gu_thread_msg, sMsgId);
319: oRSet.close();
320: oStmt.close();
321: } else
322: put(DB.gu_thread_msg, sMsgId);
323:
324: if (oConn.getDataBaseProduct() == JDCConnection.DBMS_POSTGRESQL) {
325: sSQL = "SELECT k_sp_count_thread_msgs ('"
326: + getString(DB.gu_thread_msg) + "')";
327:
328: oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
329: ResultSet.CONCUR_READ_ONLY);
330:
331: if (DebugFile.trace)
332: DebugFile.writeln("Statement.executeQuery(" + sSQL
333: + ")");
334:
335: oRSet = oStmt.executeQuery(sSQL);
336: oRSet.next();
337: nThreadMsgs = oRSet.getInt(1);
338: oRSet.close();
339: oStmt.close();
340: } else {
341: sSQL = "{call k_sp_count_thread_msgs(?,?)}";
342: if (DebugFile.trace)
343: DebugFile.writeln("CallableStatement.prepareCall("
344: + sSQL + ")");
345: oCall = oConn.prepareCall(sSQL);
346: oCall.setString(1, getString(DB.gu_thread_msg));
347: oCall.registerOutParameter(2, Types.INTEGER);
348: oCall.execute();
349: nThreadMsgs = oCall.getInt(2);
350: oCall.close();
351: }
352:
353: replace(DB.nu_thread_msgs, ++nThreadMsgs);
354:
355: if (!AllVals.containsKey(DB.dt_start))
356: put(DB.dt_start, dtNow);
357:
358: if (!AllVals.containsKey(DB.id_msg_type))
359: put(DB.id_msg_type, "TXT");
360:
361: replace(DB.dt_published, dtNow);
362:
363: bRetVal = super .store(oConn);
364:
365: oStmt = oConn.createStatement();
366: sSQL = "UPDATE " + DB.k_newsmsgs + " SET " + DB.nu_thread_msgs
367: + "=" + String.valueOf(nThreadMsgs) + " WHERE "
368: + DB.gu_thread_msg + "='" + getString(DB.gu_thread_msg)
369: + "'";
370: if (DebugFile.trace)
371: DebugFile.writeln("Statement.executeUpdate(" + sSQL + ")");
372: oStmt.executeUpdate(sSQL);
373: oStmt.close();
374:
375: if (!AllVals.containsKey(DB.gu_newsgrp)
376: && !sMsgId.equals(get(DB.gu_thread_msg))) {
377:
378: sSQL = "SELECT " + DB.gu_category + " FROM "
379: + DB.k_x_cat_objs + " WHERE " + DB.gu_object + "='"
380: + getStringNull(DB.gu_thread_msg, "null") + "'";
381:
382: oStmt = oConn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
383: ResultSet.CONCUR_READ_ONLY);
384:
385: if (DebugFile.trace)
386: DebugFile.writeln("Statement.executeQuery(" + sSQL
387: + ")");
388:
389: oRSet = oStmt.executeQuery(sSQL);
390:
391: if (oRSet.next())
392: put(DB.gu_newsgrp, oRSet.getString(1));
393:
394: oRSet.close();
395: oStmt.close();
396: }
397:
398: if (bRetVal && AllVals.containsKey(DB.gu_newsgrp)) {
399: if (DebugFile.trace)
400: DebugFile
401: .writeln("Category.store() && containsKey(DB.gu_newsgrp)");
402:
403: if (bNewMsg) {
404:
405: if (DebugFile.trace)
406: DebugFile.writeln("new message");
407:
408: // Check whether or not dt_last_update column exists for backward compatibility with 1.x versions
409: boolean bHasLastUpdate;
410: try {
411: bHasLastUpdate = (((DBBind) (oConn.getPool()
412: .getDatabaseBinding())).getDBTable(
413: DB.k_newsgroups).getColumnByName(
414: DB.dt_last_update) != null);
415: } catch (NullPointerException npe) {
416: bHasLastUpdate = true;
417: }
418:
419: if (bHasLastUpdate) {
420: PreparedStatement oUpdt = null;
421: sSQL = "UPDATE " + DB.k_newsgroups + " SET "
422: + DB.dt_last_update + "=? WHERE "
423: + DB.gu_newsgrp + "=?";
424:
425: if (DebugFile.trace)
426: DebugFile
427: .writeln("Connection.prepareStatement ("
428: + sSQL + ")");
429:
430: oUpdt = oConn.prepareStatement(sSQL);
431: oUpdt.setTimestamp(1, new Timestamp(new Date()
432: .getTime()));
433: oUpdt.setObject(2, AllVals.get(DB.gu_newsgrp),
434: java.sql.Types.VARCHAR);
435: oUpdt.executeUpdate();
436: oUpdt.close();
437: oUpdt = null;
438: } // fi (bHasLastUpdate)
439: } else {
440: sSQL = "DELETE FROM " + DB.k_x_cat_objs + " WHERE "
441: + DB.gu_category + "='"
442: + getString(DB.gu_newsgrp) + "' AND "
443: + DB.gu_object + "='" + sMsgId + "'";
444: oStmt = oConn.createStatement();
445: if (DebugFile.trace)
446: DebugFile
447: .writeln("Statement.execute(" + sSQL + ")");
448: oStmt.execute(sSQL);
449: oStmt.close();
450: } // fi (!bNewMsg)
451:
452: sSQL = "INSERT INTO " + DB.k_x_cat_objs + "("
453: + DB.gu_category + "," + DB.gu_object + ","
454: + DB.id_class + "," + DB.bi_attribs + ","
455: + DB.od_position + ") VALUES ('"
456: + getString(DB.gu_newsgrp) + "','" + sMsgId + "',"
457: + String.valueOf(NewsMessage.ClassId) + ",0,NULL)";
458: oStmt = oConn.createStatement();
459: if (DebugFile.trace)
460: DebugFile.writeln("Statement.execute(" + sSQL + ")");
461: oStmt.execute(sSQL);
462: oStmt.close();
463: } // fi (bRetVal && containsKey(gu_newsgrp))
464:
465: if (DebugFile.trace) {
466: DebugFile.decIdent();
467: DebugFile.writeln("End NewsMessage.store() : "
468: + String.valueOf(bRetVal));
469: }
470:
471: return bRetVal;
472: } // store
473:
474: // ----------------------------------------------------------
475:
476: /**
477: * <p>Delete NewsMessage.</p>
478: * Files attached to NewsMessage (stored as Products) are delete prior to
479: * the NewsMessage itself. Then k_sp_del_newsmsg stored procedure is called.
480: * @param oConn Database Connection
481: * @throws SQLException
482: */
483: public boolean delete(JDCConnection oConn) throws SQLException {
484: Product oProd;
485: Statement oStmt;
486: CallableStatement oCall;
487: String sSQL;
488:
489: if (DebugFile.trace) {
490: DebugFile.writeln("Begin NewsMessage.delete([Connection])");
491: DebugFile.incIdent();
492: DebugFile.writeln("gu_msg="
493: + getStringNull(DB.gu_msg, "null"));
494: }
495:
496: if (!isNull(DB.gu_product)) {
497: oProd = new Product(oConn, getString(DB.gu_product));
498:
499: oStmt = oConn.createStatement();
500: sSQL = "UPDATE " + DB.k_newsmsgs + " SET " + DB.gu_product
501: + "=NULL WHERE " + DB.gu_msg + "='"
502: + getString(DB.gu_msg) + "'";
503: if (DebugFile.trace)
504: DebugFile.writeln("Statement.executeUpdate(" + sSQL
505: + ")");
506: oStmt.executeUpdate(sSQL);
507: oStmt.close();
508:
509: oProd.delete(oConn);
510:
511: remove(DB.gu_product);
512: } // fi
513:
514: sSQL = "{ call k_sp_del_newsmsg ('" + getString(DB.gu_msg)
515: + "') }";
516: if (DebugFile.trace)
517: DebugFile.writeln("Connection.prepareCall(" + sSQL + ")");
518: oCall = oConn.prepareCall(sSQL);
519: oCall.execute();
520: oCall.close();
521:
522: if (DebugFile.trace) {
523: DebugFile.decIdent();
524: DebugFile.writeln("End NewsMessage.delete() : true");
525: }
526:
527: return true;
528: } // delete
529:
530: // **********************************************************
531: // Static Methods
532:
533: /**
534: * <p>Delete NewsMessage.</p>
535: * Files attached to NewsMessage (stored as Products) are delete prior to
536: * the NewsMessage itself. Then k_sp_del_newsmsg stored procedure is called.
537: * @param oConn Database Connection
538: * @param sNewsMsgGUID GUID of NewsMessage to be deleted
539: * @throws SQLException
540: */
541: public static boolean delete(JDCConnection oConn,
542: String sNewsMsgGUID) throws SQLException {
543: NewsMessage oMsg = new NewsMessage();
544:
545: if (oMsg.load(oConn, new Object[] { sNewsMsgGUID }))
546: return oMsg.delete(oConn);
547: else
548: return false;
549: } // delete
550:
551: // **********************************************************
552: // Constantes Publicas
553:
554: public static final short STATUS_VALIDATED = 0;
555: public static final short STATUS_PENDING = 1;
556: public static final short STATUS_DISCARDED = 2;
557: public static final short STATUS_EXPIRED = 3;
558:
559: public static final short ClassId = 31;
560:
561: }
|