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.dataxslt.db;
034:
035: import java.util.Date;
036:
037: import java.io.File;
038:
039: import java.sql.SQLException;
040: import java.sql.Statement;
041: import java.sql.PreparedStatement;
042: import java.sql.CallableStatement;
043: import java.sql.ResultSet;
044: import java.sql.Types;
045:
046: import com.knowgate.debug.DebugFile;
047: import com.knowgate.jdc.JDCConnection;
048: import com.knowgate.dataobjs.DB;
049: import com.knowgate.dataobjs.DBBind;
050: import com.knowgate.dataobjs.DBPersist;
051: import com.knowgate.misc.Gadgets;
052:
053: /**
054: *
055: * <p>PageSet database index</p>
056: * @author Sergio Montoro Ten
057: * @version 1.1
058: */
059:
060: public class PageSetDB extends DBPersist {
061:
062: public PageSetDB() {
063: super (DB.k_pagesets, "PageSetDB");
064: }
065:
066: public PageSetDB(JDCConnection oConn, String sPageSetGUID)
067: throws SQLException {
068: super (DB.k_pagesets, "PageSetDB");
069: Object aPageSet[] = { sPageSetGUID };
070: if (!load(oConn, aPageSet))
071: throw new SQLException("Could not find PageSet "
072: + sPageSetGUID + " at " + DB.k_pagesets);
073: }
074:
075: // ----------------------------------------------------------
076:
077: public boolean load(JDCConnection oConn, Object aPK[])
078: throws SQLException {
079: // Rutina especial de carga con procedimiento almacenado para maxima velocidad en el rendering
080:
081: ResultSet oRSet;
082: PreparedStatement oStmt;
083: CallableStatement oCall;
084: String sNmPageSet;
085: Object sField;
086: boolean bRetVal;
087:
088: if (DebugFile.trace) {
089: DebugFile.writeln("Begin PageSetDB.load([Connection], {"
090: + aPK[0] + "}");
091: DebugFile.incIdent();
092: }
093:
094: if (oConn.getDataBaseProduct() == JDCConnection.DBMS_ORACLE
095: || oConn.getDataBaseProduct() == JDCConnection.DBMS_MSSQL
096: || oConn.getDataBaseProduct() == JDCConnection.DBMS_MYSQL) {
097:
098: if (DebugFile.trace)
099: DebugFile
100: .writeln("Connection.prepareCall({ call k_sp_read_pageset ('"
101: + aPK[0]
102: + "',?,?,?,?,?,?,?,?,?,?,?,?,?) }");
103:
104: oCall = oConn
105: .prepareCall("{ call k_sp_read_pageset (?,?,?,?,?,?,?,?,?,?,?,?,?,?) }");
106:
107: clear();
108:
109: oCall.setObject(1, aPK[0], Types.CHAR); // gu_pageset
110: oCall.registerOutParameter(2, Types.CHAR); // gu_microsite
111: oCall.registerOutParameter(3, Types.VARCHAR); // nm_microsite
112: oCall.registerOutParameter(4, Types.CHAR); // gu_workarea
113: oCall.registerOutParameter(5, Types.VARCHAR); // nm_pageset
114: oCall.registerOutParameter(6, Types.VARCHAR); // vs_stamp
115: oCall.registerOutParameter(7, Types.CHAR); // id_language
116: oCall.registerOutParameter(8, Types.TIMESTAMP); // dt_modified
117: oCall.registerOutParameter(9, Types.VARCHAR); // path_data
118: oCall.registerOutParameter(10, Types.VARCHAR); // id_status
119: oCall.registerOutParameter(11, Types.VARCHAR); // path_metadata
120: oCall.registerOutParameter(12, Types.VARCHAR); // tx_comments
121: oCall.registerOutParameter(13, Types.CHAR); // gu_company
122: oCall.registerOutParameter(14, Types.CHAR); // gu_project
123:
124: if (DebugFile.trace)
125: DebugFile.writeln("CallableStatement.execute()");
126:
127: oCall.execute();
128:
129: sNmPageSet = oCall.getString(5);
130: bRetVal = (null != sNmPageSet);
131:
132: put(DB.gu_pageset, aPK[0]);
133:
134: if (bRetVal) {
135: put(DB.gu_microsite, oCall.getString(2).trim());
136: put(DB.nm_microsite, oCall.getString(3));
137: put(DB.gu_workarea, oCall.getString(4).trim());
138: put(DB.nm_pageset, oCall.getString(5));
139:
140: sField = oCall.getObject(6);
141: if (!oCall.wasNull())
142: put(DB.vs_stamp, (String) sField);
143:
144: sField = oCall.getObject(7);
145: if (!oCall.wasNull())
146: put(DB.id_language, ((String) sField).trim());
147:
148: sField = oCall.getObject(8);
149: if (!oCall.wasNull())
150: put(DB.dt_modified, oCall.getDate(8));
151:
152: sField = oCall.getObject(9);
153: if (!oCall.wasNull())
154: put(DB.path_data, (String) sField);
155:
156: sField = oCall.getObject(10);
157: if (!oCall.wasNull())
158: put(DB.id_status, (String) sField);
159:
160: sField = oCall.getObject(11);
161: if (!oCall.wasNull())
162: put(DB.path_metadata, (String) sField);
163:
164: sField = oCall.getObject(12);
165: if (!oCall.wasNull())
166: put(DB.tx_comments, (String) sField);
167:
168: sField = oCall.getObject(13);
169: if (!oCall.wasNull())
170: put(DB.gu_company, sField.toString().trim());
171:
172: sField = oCall.getObject(14);
173: if (!oCall.wasNull())
174: put(DB.gu_project, sField.toString().trim());
175: } // fi (bRetVal)
176:
177: oCall.close();
178: } else {
179:
180: if (DebugFile.trace)
181: DebugFile
182: .writeln("Connection.prepareStatement(SELECT m."
183: + DB.gu_microsite
184: + ",m."
185: + DB.nm_microsite
186: + ",p."
187: + DB.gu_workarea
188: + ",p."
189: + DB.nm_pageset
190: + ",p."
191: + DB.vs_stamp
192: + ",p."
193: + DB.id_language
194: + ",p."
195: + DB.dt_modified
196: + ",p."
197: + DB.path_data
198: + ",p."
199: + DB.id_status
200: + ",m."
201: + DB.path_metadata
202: + ",p."
203: + DB.tx_comments
204: + ",p."
205: + DB.gu_company
206: + ",p."
207: + DB.gu_project
208: + " FROM "
209: + DB.k_pagesets
210: + " p LEFT OUTER JOIN "
211: + DB.k_microsites
212: + " m ON p."
213: + DB.gu_microsite
214: + "=m."
215: + DB.gu_microsite
216: + " WHERE p."
217: + DB.gu_pageset + "=" + aPK[0] + ")");
218:
219: oStmt = oConn.prepareStatement("SELECT m."
220: + DB.gu_microsite + ",m." + DB.nm_microsite + ",p."
221: + DB.gu_workarea + ",p." + DB.nm_pageset + ",p."
222: + DB.vs_stamp + ",p." + DB.id_language + ",p."
223: + DB.dt_modified + ",p." + DB.path_data + ",p."
224: + DB.id_status + ",m." + DB.path_metadata + ",p."
225: + DB.tx_comments + ",p." + DB.gu_company + ",p."
226: + DB.gu_project + " FROM " + DB.k_pagesets
227: + " p LEFT OUTER JOIN " + DB.k_microsites
228: + " m ON p." + DB.gu_microsite + "=m."
229: + DB.gu_microsite + " WHERE p." + DB.gu_pageset
230: + "=?", ResultSet.TYPE_FORWARD_ONLY,
231: ResultSet.CONCUR_READ_ONLY);
232:
233: oStmt.setObject(1, aPK[0], Types.CHAR);
234: oRSet = oStmt.executeQuery();
235:
236: bRetVal = oRSet.next();
237:
238: put(DB.gu_pageset, aPK[0]);
239:
240: if (bRetVal) {
241: put(DB.gu_microsite, oRSet.getString(1));
242: put(DB.nm_microsite, oRSet.getString(2));
243: put(DB.gu_workarea, oRSet.getString(3));
244: put(DB.nm_pageset, oRSet.getString(4));
245:
246: sField = oRSet.getObject(5);
247: if (!oRSet.wasNull())
248: put(DB.vs_stamp, (String) sField);
249:
250: sField = oRSet.getObject(6);
251: if (!oRSet.wasNull())
252: put(DB.id_language, (String) sField);
253:
254: sField = oRSet.getObject(7);
255: if (!oRSet.wasNull())
256: put(DB.dt_modified, oRSet.getTimestamp(7));
257:
258: sField = oRSet.getObject(8);
259: if (!oRSet.wasNull())
260: put(DB.path_data, (String) sField);
261:
262: sField = oRSet.getObject(9);
263: if (!oRSet.wasNull())
264: put(DB.id_status, (String) sField);
265:
266: sField = oRSet.getObject(10);
267: if (!oRSet.wasNull())
268: put(DB.path_metadata, (String) sField);
269:
270: sField = oRSet.getObject(11);
271: if (!oRSet.wasNull())
272: put(DB.tx_comments, (String) sField);
273:
274: sField = oRSet.getObject(12);
275: if (!oRSet.wasNull())
276: put(DB.gu_company, (String) sField);
277:
278: sField = oRSet.getObject(13);
279: if (!oRSet.wasNull())
280: put(DB.gu_project, (String) sField);
281: }
282: oRSet.close();
283: oStmt.close();
284: }
285:
286: if (DebugFile.trace) {
287: DebugFile.decIdent();
288: DebugFile.writeln("End PageSetDB.load() : "
289: + String.valueOf(bRetVal));
290: }
291:
292: return bRetVal;
293: } // load
294:
295: // ----------------------------------------------------------
296:
297: public boolean store(JDCConnection oConn) throws SQLException {
298: java.sql.Timestamp dtNow = new java.sql.Timestamp(DBBind
299: .getTime());
300:
301: if (!AllVals.containsKey(DB.gu_pageset))
302: put(DB.gu_pageset, Gadgets.generateUUID());
303:
304: // Forzar la fecha de modificación del registro
305: replace(DB.dt_modified, dtNow);
306:
307: return super .store(oConn);
308: } // store
309:
310: // ----------------------------------------------------------
311:
312: public boolean delete(JDCConnection oConn) throws SQLException {
313: boolean bRetVal;
314: File oXFil;
315: Statement oStmt;
316:
317: if (DebugFile.trace) {
318: DebugFile.writeln("Begin PageSetDB.delete([Connection])");
319: DebugFile.incIdent();
320: }
321:
322: if (exists(oConn)) {
323: if (DebugFile.trace)
324: DebugFile.writeln("PageSet " + getString(DB.gu_pageset)
325: + " exists");
326:
327: if (!isNull(DB.path_data)) {
328: oXFil = new File(getString(DB.path_data));
329: if (oXFil.exists()) {
330: if (DebugFile.trace)
331: DebugFile.writeln("File.delete("
332: + getString(DB.path_data) + ")");
333: oXFil.delete();
334: }
335: oXFil = null;
336: } // fi (path_data)
337:
338: if (DBBind.exists(oConn, DB.k_list_jobs, "U")) {
339: oStmt = oConn.createStatement();
340: if (DebugFile.trace)
341: DebugFile.writeln("Connection.execute("
342: + "DELETE FROM " + DB.k_list_jobs
343: + " WHERE " + DB.gu_pageset + "='"
344: + getString(DB.gu_pageset) + "'" + ")");
345: oStmt.execute("DELETE FROM " + DB.k_list_jobs
346: + " WHERE " + DB.gu_pageset + "='"
347: + getString(DB.gu_pageset) + "'");
348: oStmt.close();
349: }
350:
351: if (DBBind.exists(oConn, DB.k_pageset_answers, "U")) {
352: oStmt = oConn.createStatement();
353: oStmt.execute("DELETE FROM " + DB.k_pageset_answers
354: + " WHERE " + DB.gu_pageset + "='"
355: + getString(DB.gu_pageset) + "'");
356: oStmt.execute("DELETE FROM " + DB.k_pageset_datasheets
357: + " WHERE " + DB.gu_pageset + "='"
358: + getString(DB.gu_pageset) + "'");
359: oStmt.execute("DELETE FROM " + DB.k_datasheets_lookup
360: + " WHERE " + DB.gu_owner + "='"
361: + getString(DB.gu_pageset) + "'");
362: oStmt.close();
363: }
364:
365: oStmt = oConn.createStatement();
366: oStmt.execute("DELETE FROM " + DB.k_pageset_pages
367: + " WHERE " + DB.gu_pageset + "='"
368: + getString(DB.gu_pageset) + "'");
369: oStmt.close();
370:
371: bRetVal = super .delete(oConn);
372: } else
373: bRetVal = false;
374:
375: if (DebugFile.trace) {
376: DebugFile.decIdent();
377: DebugFile.writeln("End PageSetDB.delete()"
378: + String.valueOf(bRetVal));
379: }
380:
381: return bRetVal;
382: } // delete
383:
384: // ----------------------------------------------------------
385:
386: public boolean existsFile() {
387: if (!isNull(DB.path_data))
388: return new File(getString(DB.path_data)).exists();
389: else
390: return false;
391: } // existsFile
392:
393: // ----------------------------------------------------------
394:
395: public void setPage(JDCConnection oConn, String sIdPage,
396: int iPgPage, String sTlPage, String sPathPage)
397: throws SQLException {
398: PreparedStatement oStmt;
399: ResultSet oRSet;
400: String sGuPage;
401: String sSQL;
402:
403: if (DebugFile.trace) {
404: DebugFile.writeln("Begin PageSetDB.setPage([Connection], "
405: + sIdPage + "," + String.valueOf(iPgPage) + ","
406: + sTlPage + "," + sPathPage + ")");
407: DebugFile.incIdent();
408: }
409:
410: sSQL = "SELECT " + DB.gu_page + " FROM " + DB.k_pageset_pages
411: + " WHERE " + DB.gu_pageset + "=? AND " + DB.pg_page
412: + "=?";
413:
414: if (DebugFile.trace)
415: DebugFile.writeln("Connection.prepareStatement(" + sSQL
416: + ")");
417:
418: oStmt = oConn.prepareStatement(sSQL);
419: oStmt.setString(1, getString(DB.gu_pageset));
420: oStmt.setInt(2, iPgPage);
421: oRSet = oStmt.executeQuery();
422: if (oRSet.next())
423: sGuPage = oRSet.getString(1);
424: else
425: sGuPage = null;
426: oRSet.close();
427: oStmt.close();
428:
429: if (null == sGuPage) {
430:
431: if (DebugFile.trace)
432: DebugFile.writeln("Connection.prepareStatement("
433: + "INSERT INTO "
434: + DB.k_pageset_pages
435: + " ("
436: + DB.gu_page
437: + ","
438: + DB.pg_page
439: + ","
440: + DB.gu_pageset
441: + ","
442: + DB.dt_modified
443: + ","
444: + DB.tl_page
445: + ","
446: + DB.path_page
447: + ") VALUES ('"
448: + sIdPage
449: + "',"
450: + String.valueOf(iPgPage)
451: + ",'"
452: + getString(DB.gu_pageset)
453: + "',"
454: + DBBind.escape(new Date(), "ts")
455: + ",'"
456: + sTlPage + "','" + sPathPage + "')" + ")");
457:
458: sSQL = "INSERT INTO " + DB.k_pageset_pages + " ("
459: + DB.gu_page + "," + DB.pg_page + ","
460: + DB.gu_pageset + "," + DB.dt_modified + ","
461: + DB.tl_page + "," + DB.path_page
462: + ") VALUES (?,?,?,"
463: + DBBind.escape(new Date(), "ts") + ",?,?)";
464:
465: oStmt = oConn.prepareStatement(sSQL);
466: oStmt.setString(1, sIdPage);
467: oStmt.setInt(2, iPgPage);
468: oStmt.setString(3, getString(DB.gu_pageset));
469: oStmt.setString(4, sTlPage);
470: oStmt.setString(5, sPathPage);
471: if (DebugFile.trace)
472: DebugFile.writeln("PreparedStatement.execute()");
473: oStmt.execute();
474: oStmt.close();
475: } else {
476: sSQL = "UPDATE " + DB.k_pageset_pages + " SET "
477: + DB.dt_modified + "="
478: + DBBind.escape(new Date(), "ts") + ","
479: + DB.tl_page + "=?," + DB.path_page + "=? WHERE "
480: + DB.gu_page + "=?";
481: if (DebugFile.trace)
482: DebugFile.writeln("Connection.prepareStatement)" + sSQL
483: + ")");
484:
485: oStmt = oConn.prepareStatement(sSQL);
486: oStmt.setString(1, sTlPage);
487: oStmt.setString(2, sPathPage);
488: oStmt.setString(3, sGuPage);
489: if (DebugFile.trace)
490: DebugFile.writeln("PreparedStatement.executeUpdate()");
491: oStmt.executeUpdate();
492: oStmt.close();
493: }
494:
495: if (DebugFile.trace) {
496: DebugFile.decIdent();
497: DebugFile.writeln("End PageSetDB.setPage()");
498: }
499: } // setPage
500:
501: // **********************************************************
502: // Metodos Estáticos
503:
504: /**
505: * <p>Get relative path to XML data file</p>
506: * Relative path is readed from field path_data of table k_pagesets.<br>
507: * For example: domains/2049/workareas/c0a801ccf90ee54e40100000ffd3af4f/apps/Mailwire/data/Basic (Dec 8 2003 11.00.01 PM).xml<br>
508: * @param oConn Database Connection
509: * @param sPageSetGUID GUID of PageSet witch data file relative path is to be retrieved
510: * @return Relative path to XML metadata file or <b>null</b> if no Microsite with such GUID was found at k_microsites table.
511: * @throws SQLException
512: */
513: public static String filePath(JDCConnection oConn,
514: String sPageSetGUID) throws SQLException {
515:
516: if (DebugFile.trace) {
517: DebugFile.writeln("Begin PageSetDB.filePath([Connection], "
518: + sPageSetGUID + ")");
519: DebugFile.incIdent();
520: }
521: PreparedStatement oStmt;
522: ResultSet oRSet;
523: String sFilePath;
524:
525: oStmt = oConn.prepareStatement("SELECT " + DB.path_data
526: + " FROM " + DB.k_pagesets + " WHERE " + DB.gu_pageset
527: + "=?", ResultSet.TYPE_FORWARD_ONLY,
528: ResultSet.CONCUR_READ_ONLY);
529: oStmt.setString(1, sPageSetGUID);
530: oRSet = oStmt.executeQuery();
531: if (oRSet.next())
532: sFilePath = oRSet.getString(1);
533: else
534: sFilePath = null;
535: oRSet.close();
536: oStmt.close();
537:
538: if (DebugFile.trace) {
539: DebugFile.decIdent();
540: DebugFile.writeln("End PageSetDB.filePath() : "
541: + (sFilePath != null ? sFilePath : "null"));
542: }
543: return sFilePath;
544: } // filePath
545:
546: // ----------------------------------------------------------
547:
548: public static boolean delete(JDCConnection oConn,
549: String sPageSetGUID) throws SQLException {
550: PageSetDB oPGDB = new PageSetDB(oConn, sPageSetGUID);
551: return oPGDB.delete(oConn);
552: } // delete
553:
554: // **********************************************************
555: // * Variables estáticas
556:
557: public static final short ClassId = 71;
558: }
|