001: /*
002: * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
003: *
004: * Project: OpenChronicle
005: *
006: * $Id: PostgreSQLBlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:19 bastafidli Exp $
007: *
008: * This program is free software; you can redistribute it and/or modify
009: * it under the terms of the GNU General Public License as published by
010: * the Free Software Foundation; version 2 of the License.
011: *
012: * This program is distributed in the hope that it will be useful,
013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
015: * GNU General Public License for more details.
016: *
017: * You should have received a copy of the GNU General Public License
018: * along with this program; if not, write to the Free Software
019: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020: */
021:
022: package org.opensubsystems.blog.persist.db.postgresql;
023:
024: import java.sql.Connection;
025: import java.sql.SQLException;
026: import java.sql.Statement;
027: import java.util.logging.Level;
028: import java.util.logging.Logger;
029:
030: import org.opensubsystems.blog.data.Blog;
031: import org.opensubsystems.blog.data.Entry;
032: import org.opensubsystems.blog.persist.db.BlogDatabaseSchema;
033: import org.opensubsystems.core.error.OSSException;
034: import org.opensubsystems.core.util.DatabaseUtils;
035: import org.opensubsystems.core.util.Log;
036:
037: /**
038: * Database specific operations related to persistence of blogs and their
039: * entries. This class encapsulate functionality specific for Postgre SQL.
040: *
041: * @version $Id: PostgreSQLBlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:19 bastafidli Exp $
042: * @author Julian Legeny
043: * @code.reviewer Miro Halas
044: * @code.reviewed 1.3 2006/07/21 00:54:58 jlegeny
045: */
046: public class PostgreSQLBlogDatabaseSchema extends BlogDatabaseSchema {
047: /*
048: Use autogenerated numbers for IDs using sequence
049: Name all constraints to easily identify them later.
050:
051: CREATE TABLE BF_BLOG
052: (
053: ID SERIAL,
054: DOMAIN_ID INTEGER NOT NULL,
055: FOLDER VARCHAR(50) NOT NULL,
056: CAPTION VARCHAR(1024) NOT NULL,
057: COMMENTS VARCHAR(32768) NOT NULL,
058: CREATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
059: MODIFICATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
060: CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
061: CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
062: // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
063: // REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
064: )
065:
066:
067: CREATE OR REPLACE FUNCTION INSERT_BF_BLOG
068: (
069: INTEGER,
070: VARCHAR(50),
071: VARCHAR(1024),
072: VARCHAR(32768)
073: ) RETURNS type_int_timestamp AS '
074: DECLARE
075: IN_DOMAIN_ID ALIAS FOR $1;
076: IN_FOLDER ALIAS FOR $2;
077: IN_CAPTION ALIAS FOR $3;
078: IN_COMMENTS ALIAS FOR $4;
079:
080: out_key INTEGER;
081: out_timestamp TIMESTAMP WITH TIME ZONE;
082: output_result type_int_timestamp;
083: BEGIN
084: SELECT INTO out_timestamp now();
085: INSERT INTO " + strUserName + ".BF_BLOG(DOMAIN_ID, FOLDER,
086: CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE)
087: VALUES (IN_DOMAIN_ID, IN_FOLDER, IN_CAPTION,
088: IN_COMMENTS, out_timestamp, out_timestamp);
089: out_key := CURRVAL(''bf_blog_id_seq'');
090: output_result.intgr := out_key;
091: output_result.tmstp := out_timestamp;
092: RETURN output_result;
093:
094: END
095: ' LANGUAGE 'plpgsql';
096:
097:
098: CREATE OR REPLACE FUNCTION UPDATE_BF_BLOG
099: (
100: VARCHAR(50),
101: VARCHAR(1024),
102: VARCHAR(32768),
103: INTEGER,
104: INTEGER,
105: TIMESTAMP WITH TIME ZONE
106: ) RETURNS type_int_timestamp AS '
107: DECLARE
108: IN_FOLDER ALIAS FOR $1;
109: IN_CAPTION ALIAS FOR $2;
110: IN_COMMENTS ALIAS FOR $3;
111: IN_BLOG_ID ALIAS FOR $4;
112: IN_DOMAIN_ID ALIAS FOR $5;
113: IN_MODIFICATION_DATE ALIAS FOR $6;
114:
115: out_updated_count INTEGER;
116: out_timestamp TIMESTAMP WITH TIME ZONE;
117: output_result type_int_timestamp;
118: BEGIN
119: SELECT INTO out_timestamp now();
120: UPDATE " + strUserName + ".BF_BLOG SET FOLDER = IN_FOLDER, CAPTION = IN_CAPTION,
121: COMMENTS = IN_COMMENTS, MODIFICATION_DATE = out_timestamp
122: WHERE ID = IN_BLOG_ID AND DOMAIN_ID = IN_DOMAIN_ID
123: AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
124: GET DIAGNOSTICS out_updated_count = ROW_COUNT;
125: output_result.intgr := out_updated_count;
126: output_result.tmstp := out_timestamp;
127: RETURN output_result;
128: END;
129: ' LANGUAGE 'plpgsql';
130:
131:
132: CREATE TABLE BF_BLOG_ENTRY
133: (
134: ID SERIAL,
135: DOMAIN_ID INTEGER NOT NULL,
136: BLOG_ID INTEGER NOT NULL,
137: CAPTION VARCHAR(1024) NOT NULL,
138: COMMENTS VARCHAR(32768) NOT NULL,
139: IMAGEURL VARCHAR(1024) NOT NULL,
140: TARGETURL VARCHAR(1024) NOT NULL,
141: CREATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
142: MODIFICATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
143: CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),
144: CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)
145: REFERENCES BF_BLOG (ID) ON DELETE CASCADE
146: // CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)
147: // REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
148: )
149:
150:
151: CREATE OR REPLACE FUNCTION INSERT_BF_BLOGENTR
152: (
153: INTEGER,
154: INTEGER,
155: VARCHAR(1024),
156: VARCHAR(32768),
157: VARCHAR(1024),
158: VARCHAR(1024)
159: ) RETURNS type_int_timestamp AS '
160: DECLARE
161: IN_DOMAIN_ID ALIAS FOR $1;
162: IN_BLOG_ID ALIAS FOR $2;
163: IN_CAPTION ALIAS FOR $3;
164: IN_COMMENTS ALIAS FOR $4;
165: IN_IMAGEURL ALIAS FOR $5;
166: IN_TARGETURL ALIAS FOR $6;
167:
168: out_key INTEGER;
169: out_timestamp TIMESTAMP WITH TIME ZONE;
170: output_result type_int_timestamp;
171: BEGIN
172: SELECT INTO out_timestamp now();
173: INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID,
174: CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)
175: VALUES (IN_DOMAIN_ID, IN_BLOG_ID, IN_CAPTION,
176: IN_COMMENTS, IN_IMAGEURL, IN_TARGETURL, out_timestamp, out_timestamp);
177: out_key := CURRVAL(''bf_blog_entry_id_seq'');
178: output_result.intgr := out_key;
179: output_result.tmstp := out_timestamp;
180: RETURN output_result;
181: END
182: ' LANGUAGE 'plpgsql';
183:
184:
185: CREATE OR REPLACE FUNCTION UPDATE_BF_BLOGENTR
186: (
187: VARCHAR(1024),
188: VARCHAR(32768),
189: VARCHAR(1024),
190: VARCHAR(1024),
191: INTEGER,
192: INTEGER,
193: TIMESTAMP WITH TIME ZONE
194: ) RETURNS type_int_timestamp AS '
195: DECLARE
196: IN_CAPTION ALIAS FOR $1;
197: IN_COMMENTS ALIAS FOR $2;
198: IN_IMAGEURL ALIAS FOR $3;
199: IN_TARGETURL ALIAS FOR $4;
200: IN_ENTRY_ID ALIAS FOR $5;
201: IN_DOMAIN_ID ALIAS FOR $6;
202: IN_MODIFICATION_DATE ALIAS FOR $7;
203:
204: out_updated_count INTEGER;
205: out_timestamp TIMESTAMP WITH TIME ZONE;
206: output_result type_int_timestamp;
207: BEGIN
208: SELECT INTO out_timestamp now();
209: UPDATE " + strUserName + ".BF_BLOG_ENTRY SET CAPTION = IN_CAPTION,
210: COMMENTS = IN_COMMENTS, IMAGEURL = IN_IMAGEURL, TARGETURL= IN_TARGETURL,
211: MODIFICATION_DATE = out_timestamp
212: WHERE ID = IN_ENTRY_ID AND DOMAIN_ID = IN_DOMAIN_ID
213: AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
214: GET DIAGNOSTICS out_updated_count = ROW_COUNT;
215: output_result.intgr := out_updated_count;
216: output_result.tmstp := out_timestamp;
217:
218: RETURN output_result;
219: END;
220: ' LANGUAGE 'plpgsql';
221: */
222:
223: // Constants ////////////////////////////////////////////////////////////////
224: /**
225: * Maximal length of blog comments.
226: * Restriction for VARCHAR type is max. 32768 characters for Postgre SQL database.
227: */
228: public static final int BLOG_COMMENTS_MAXLENGTH_POSTGRES = 32768;
229:
230: /**
231: * Maximal length of blog entry comments.
232: * Restriction for VARCHAR type is max. 32768 characters for Postgre SQL database.
233: */
234: public static final int BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES = 32768;
235:
236: // Cached values ////////////////////////////////////////////////////////////
237:
238: /**
239: * Logger for this class
240: */
241: private static Logger s_logger = Log
242: .getInstance(PostgreSQLBlogDatabaseSchema.class);
243:
244: // Constructors /////////////////////////////////////////////////////////////
245:
246: /**
247: * Static initializer
248: */
249: static {
250: // Setup maximal length of individual fields for Postgre SQL database
251: Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH_POSTGRES);
252: Entry
253: .setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES);
254: }
255:
256: /**
257: * Default constructor.
258: *
259: * @throws OSSException - error occured.
260: */
261: public PostgreSQLBlogDatabaseSchema() throws OSSException {
262: super ();
263: }
264:
265: // Public methods ///////////////////////////////////////////////////////////
266:
267: /**
268: * {@inheritDoc}
269: */
270: public void create(Connection cntDBConnection, String strUserName)
271: throws SQLException {
272: Statement stmQuery = null;
273: try {
274: stmQuery = cntDBConnection.createStatement();
275:
276: if (stmQuery
277: .execute("create table BF_BLOG"
278: + NL
279: + "("
280: + NL
281: + " ID SERIAL,"
282: + NL
283: + " DOMAIN_ID INTEGER NOT NULL,"
284: + NL
285: + " FOLDER VARCHAR("
286: + BLOG_FOLDER_MAXLENGTH
287: + ") NOT NULL,"
288: + NL
289: + " CAPTION VARCHAR("
290: + BLOG_CAPTION_MAXLENGTH
291: + ") NOT NULL,"
292: + NL
293: + " COMMENTS VARCHAR("
294: + BLOG_COMMENTS_MAXLENGTH_POSTGRES
295: + ") NOT NULL,"
296: + NL
297: + " CREATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,"
298: + NL
299: + " MODIFICATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,"
300: + NL
301: + " CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),"
302: + NL
303: +
304: // " CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
305: // " REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE," + NL +
306: " CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)"
307: + NL + ")")) {
308: // Close any results
309: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
310: }
311: s_logger.log(Level.FINEST, "Table BF_BLOG created.");
312:
313: ///////////////////////////////////////////////////////////////////////
314:
315: if (stmQuery
316: .execute("CREATE OR REPLACE FUNCTION INSERT_BF_BLOG "
317: + NL
318: + "( "
319: + NL
320: + " INTEGER, "
321: + NL
322: + " VARCHAR("
323: + BLOG_FOLDER_MAXLENGTH
324: + "), "
325: + NL
326: + " VARCHAR("
327: + BLOG_CAPTION_MAXLENGTH
328: + "), "
329: + NL
330: + " VARCHAR("
331: + BLOG_COMMENTS_MAXLENGTH_POSTGRES
332: + ") "
333: + NL
334: + ") RETURNS type_int_timestamp AS ' "
335: + NL
336: + "DECLARE "
337: + NL
338: + " IN_DOMAIN_ID ALIAS FOR $1; "
339: + NL
340: + " IN_FOLDER ALIAS FOR $2; "
341: + NL
342: + " IN_CAPTION ALIAS FOR $3; "
343: + NL
344: + " IN_COMMENTS ALIAS FOR $4; "
345: + NL
346: + " out_key INTEGER; "
347: + NL
348: + " out_timestamp TIMESTAMP WITH TIME ZONE; "
349: + NL
350: + " output_result type_int_timestamp; "
351: + NL
352: + "BEGIN "
353: + NL
354: + " SELECT INTO out_timestamp now(); "
355: + NL
356: + " INSERT INTO "
357: + strUserName
358: + ".BF_BLOG(DOMAIN_ID, FOLDER, "
359: + NL
360: + " CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE) "
361: + NL
362: + " VALUES (IN_DOMAIN_ID, IN_FOLDER, IN_CAPTION, "
363: + NL
364: + " IN_COMMENTS, out_timestamp, out_timestamp); "
365: + NL
366: + " out_key := CURRVAL(''bf_blog_id_seq''); "
367: + NL
368: + " output_result.intgr := out_key; "
369: + NL
370: + " output_result.tmstp := out_timestamp; "
371: + NL
372: + " RETURN output_result; "
373: + NL
374: + "END " + NL + "' LANGUAGE 'plpgsql';")) {
375: // Close any results
376: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
377: }
378: s_logger.log(Level.FINEST,
379: "Procedure INSERT_BF_BLOG created.");
380:
381: ///////////////////////////////////////////////////////////////////////
382:
383: if (stmQuery
384: .execute("CREATE OR REPLACE FUNCTION UPDATE_BF_BLOG "
385: + NL
386: + "( "
387: + NL
388: + " VARCHAR("
389: + BLOG_FOLDER_MAXLENGTH
390: + "), "
391: + NL
392: + " VARCHAR("
393: + BLOG_CAPTION_MAXLENGTH
394: + "), "
395: + NL
396: + " VARCHAR("
397: + BLOG_COMMENTS_MAXLENGTH_POSTGRES
398: + "), "
399: + NL
400: + " INTEGER, "
401: + NL
402: + " INTEGER, "
403: + NL
404: + " TIMESTAMP WITH TIME ZONE "
405: + NL
406: + ") RETURNS type_int_timestamp AS ' "
407: + NL
408: + "DECLARE "
409: + NL
410: + " IN_FOLDER ALIAS FOR $1; "
411: + NL
412: + " IN_CAPTION ALIAS FOR $2; "
413: + NL
414: + " IN_COMMENTS ALIAS FOR $3; "
415: + NL
416: + " IN_BLOG_ID ALIAS FOR $4; "
417: + NL
418: + " IN_DOMAIN_ID ALIAS FOR $5; "
419: + NL
420: + " IN_MODIFICATION_DATE ALIAS FOR $6; "
421: + NL
422: + " out_updated_count INTEGER; "
423: + NL
424: + " out_timestamp TIMESTAMP WITH TIME ZONE; "
425: + NL
426: + " output_result type_int_timestamp; "
427: + NL
428: + "BEGIN "
429: + NL
430: + " SELECT INTO out_timestamp now(); "
431: + NL
432: + " UPDATE "
433: + strUserName
434: + ".BF_BLOG SET FOLDER = IN_FOLDER, "
435: + NL
436: + " CAPTION = IN_CAPTION, "
437: + NL
438: + " COMMENTS = IN_COMMENTS, MODIFICATION_DATE = out_timestamp "
439: + NL
440: + " WHERE ID = IN_BLOG_ID AND DOMAIN_ID = IN_DOMAIN_ID "
441: + NL
442: + " AND MODIFICATION_DATE = IN_MODIFICATION_DATE; "
443: + NL
444: + " GET DIAGNOSTICS out_updated_count = ROW_COUNT; "
445: + NL
446: + " output_result.intgr := out_updated_count; "
447: + NL
448: + " output_result.tmstp := out_timestamp; "
449: + NL
450: + " RETURN output_result; "
451: + NL
452: + "END; " + NL + "' LANGUAGE 'plpgsql';")) {
453: // Close any results
454: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
455: }
456: s_logger.log(Level.FINEST,
457: "Procedure UPDATE_BF_BLOG created.");
458:
459: ///////////////////////////////////////////////////////////////////////
460:
461: if (stmQuery
462: .execute("create table BF_BLOG_ENTRY"
463: + NL
464: + "("
465: + NL
466: + " ID SERIAL,"
467: + NL
468: + " DOMAIN_ID INTEGER NOT NULL,"
469: + NL
470: + " BLOG_ID INTEGER NOT NULL,"
471: + NL
472: + " CAPTION VARCHAR("
473: + BLOGENTRY_CAPTION_MAXLENGTH
474: + ") NOT NULL,"
475: + NL
476: + " COMMENTS VARCHAR("
477: + BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES
478: + ") NOT NULL,"
479: + NL
480: + " IMAGEURL VARCHAR("
481: + BLOGENTRY_IMAGEURL_MAXLENGTH
482: + ") NOT NULL,"
483: + NL
484: + " TARGETURL VARCHAR("
485: + BLOGENTRY_TARGETURL_MAXLENGTH
486: + ") NOT NULL,"
487: + NL
488: + " CREATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,"
489: + NL
490: + " MODIFICATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,"
491: + NL
492: + " CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),"
493: + NL
494: +
495: // " CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
496: // " REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE," + NL +
497: " CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)"
498: + NL
499: + " REFERENCES BF_BLOG (ID) ON DELETE CASCADE"
500: + NL + ")")) {
501: // Close any results
502: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
503: }
504: s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
505:
506: ///////////////////////////////////////////////////////////////////////
507:
508: if (stmQuery
509: .execute("CREATE OR REPLACE FUNCTION INSERT_BF_BLOGENTR"
510: + NL
511: + "( "
512: + NL
513: + " INTEGER,"
514: + NL
515: + " INTEGER,"
516: + NL
517: + " VARCHAR("
518: + BLOGENTRY_CAPTION_MAXLENGTH
519: + "),"
520: + NL
521: + " VARCHAR("
522: + BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES
523: + "),"
524: + NL
525: + " VARCHAR("
526: + BLOGENTRY_IMAGEURL_MAXLENGTH
527: + "),"
528: + NL
529: + " VARCHAR("
530: + BLOGENTRY_TARGETURL_MAXLENGTH
531: + ")"
532: + NL
533: + ") RETURNS type_int_timestamp AS '"
534: + NL
535: + "DECLARE"
536: + NL
537: + " IN_DOMAIN_ID ALIAS FOR $1;"
538: + NL
539: + " IN_BLOG_ID ALIAS FOR $2;"
540: + NL
541: + " IN_CAPTION ALIAS FOR $3;"
542: + NL
543: + " IN_COMMENTS ALIAS FOR $4;"
544: + NL
545: + " IN_IMAGEURL ALIAS FOR $5;"
546: + NL
547: + " IN_TARGETURL ALIAS FOR $6;"
548: + NL
549: + " out_key INTEGER;"
550: + NL
551: + " out_timestamp TIMESTAMP WITH TIME ZONE;"
552: + NL
553: + " output_result type_int_timestamp;"
554: + NL
555: + "BEGIN"
556: + NL
557: + " SELECT INTO out_timestamp now();"
558: + NL
559: + " INSERT INTO "
560: + strUserName
561: + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID, "
562: + NL
563: + " CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, "
564: + NL
565: + " MODIFICATION_DATE) VALUES (IN_DOMAIN_ID, IN_BLOG_ID, IN_CAPTION, "
566: + NL
567: + " IN_COMMENTS, IN_IMAGEURL, IN_TARGETURL, out_timestamp, out_timestamp);"
568: + NL
569: + " out_key := CURRVAL(''bf_blog_entry_id_seq'');"
570: + NL
571: + " output_result.intgr := out_key;"
572: + NL
573: + " output_result.tmstp := out_timestamp;"
574: + NL
575: + " RETURN output_result;"
576: + NL
577: + "END" + NL + "' LANGUAGE 'plpgsql';")) {
578: // Close any results
579: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
580: }
581: s_logger.log(Level.FINEST,
582: "Table INSERT_BF_BLOGENTR created.");
583:
584: ///////////////////////////////////////////////////////////////////////
585:
586: if (stmQuery
587: .execute("CREATE OR REPLACE FUNCTION UPDATE_BF_BLOGENTR"
588: + NL
589: + "( "
590: + NL
591: + " VARCHAR("
592: + BLOGENTRY_CAPTION_MAXLENGTH
593: + "),"
594: + NL
595: + " VARCHAR("
596: + BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES
597: + "),"
598: + NL
599: + " VARCHAR("
600: + BLOGENTRY_IMAGEURL_MAXLENGTH
601: + "),"
602: + NL
603: + " VARCHAR("
604: + BLOGENTRY_TARGETURL_MAXLENGTH
605: + "),"
606: + NL
607: + " INTEGER, "
608: + NL
609: + " INTEGER, "
610: + NL
611: + " TIMESTAMP WITH TIME ZONE "
612: + NL
613: + ") RETURNS type_int_timestamp AS ' "
614: + NL
615: + "DECLARE "
616: + NL
617: + " IN_CAPTION ALIAS FOR $1; "
618: + NL
619: + " IN_COMMENTS ALIAS FOR $2; "
620: + NL
621: + " IN_IMAGEURL ALIAS FOR $3; "
622: + NL
623: + " IN_TARGETURL ALIAS FOR $4; "
624: + NL
625: + " IN_ENTRY_ID ALIAS FOR $5; "
626: + NL
627: + " IN_DOMAIN_ID ALIAS FOR $6; "
628: + NL
629: + " IN_MODIFICATION_DATE ALIAS FOR $7; "
630: + NL
631: + " out_updated_count INTEGER; "
632: + NL
633: + " out_timestamp TIMESTAMP WITH TIME ZONE; "
634: + NL
635: + " output_result type_int_timestamp; "
636: + NL
637: + "BEGIN "
638: + NL
639: + " SELECT INTO out_timestamp now(); "
640: + NL
641: + " UPDATE "
642: + strUserName
643: + ".BF_BLOG_ENTRY SET CAPTION = IN_CAPTION, "
644: + NL
645: + " COMMENTS = IN_COMMENTS, IMAGEURL = IN_IMAGEURL, "
646: + " TARGETURL= IN_TARGETURL, MODIFICATION_DATE = out_timestamp "
647: + NL
648: + " WHERE ID = IN_ENTRY_ID AND DOMAIN_ID = IN_DOMAIN_ID "
649: + NL
650: + " AND MODIFICATION_DATE = IN_MODIFICATION_DATE; "
651: + NL
652: + " GET DIAGNOSTICS out_updated_count = ROW_COUNT; "
653: + NL
654: + " output_result.intgr := out_updated_count; "
655: + NL
656: + " output_result.tmstp := out_timestamp; "
657: + NL
658: + " RETURN output_result; "
659: + NL
660: + "END; " + NL + "' LANGUAGE 'plpgsql';")) {
661: // Close any results
662: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
663: }
664: s_logger.log(Level.FINEST,
665: "Table UPDATE_BF_BLOGENTR created.");
666: } catch (SQLException sqleExc) {
667: s_logger.log(Level.WARNING, "Failed to create schema "
668: + BLOG_SCHEMA_NAME, sqleExc);
669: throw sqleExc;
670: } finally {
671: DatabaseUtils.closeStatement(stmQuery);
672: }
673: }
674:
675: /**
676: * {@inheritDoc}
677: */
678: public String getInsertBlogAndFetchGeneratedValues()
679: throws OSSException {
680: return "select INTGR, TMSTP from INSERT_BF_BLOG (?, ?, ?, ?)";
681: }
682:
683: /**
684: * {@inheritDoc}
685: */
686: public String getUpdateBlogAndFetchGeneratedValues()
687: throws OSSException {
688: return "select INTGR, TMSTP from UPDATE_BF_BLOG (?, ?, ?, ?, ?, ?)";
689: }
690:
691: /**
692: * {@inheritDoc}
693: */
694: public String getInsertEntryAndFetchGeneratedValues()
695: throws OSSException {
696: return "select INTGR, TMSTP from INSERT_BF_BLOGENTR (?, ?, ?, ?, ?, ?)";
697: }
698:
699: /**
700: * {@inheritDoc}
701: */
702: public String getUpdateEntryAndFetchGeneratedValues()
703: throws OSSException {
704: return "select INTGR, TMSTP from UPDATE_BF_BLOGENTR (?, ?, ?, ?, ?, ?, ?)";
705: }
706: }
|