001: /*
002: * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
003: *
004: * Project: OpenChronicle
005: *
006: * $Id: DB2BlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:20 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.db2;
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.persist.db.DatabaseImpl;
035: import org.opensubsystems.core.util.DatabaseUtils;
036: import org.opensubsystems.core.util.Log;
037:
038: /**
039: * Database specific operations related to persistence of blogs and their
040: * entries. This class encapsulate functionality specific for IBM DB2.
041: *
042: * @version $Id: DB2BlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:20 bastafidli Exp $
043: * @author Julian Legeny
044: * @code.reviewer Miro Halas
045: * @code.reviewed 1.1 2006/07/13 23:43:43 jlegeny
046: */
047: public class DB2BlogDatabaseSchema extends BlogDatabaseSchema {
048: /*
049: Use autogenerated numbers for IDs using sequence
050: Name all constraints to easily identify them later.
051: For stored procedures which provides UPDATE must be defined
052: also output parameter NUMBER OF AFFECTED ROWS. Because IBM DB2
053: driver does not support returning number of processed rows using
054: [ RowCounter = insertStatement.executeUpdate() ].
055:
056: CREATE SEQUENCE BLOG_ID_SEQ INCREMENT BY 1 START WITH 1 NO CYCLE
057:
058: CREATE TABLE BF_BLOG
059: (
060: ID INTEGER NOT NULL,
061: DOMAIN_ID INTEGER NOT NULL,
062: FOLDER VARCHAR(50) NOT NULL,
063: CAPTION VARCHAR(1024) NOT NULL,
064: COMMENTS VARCHAR(32768) NOT NULL,
065: CREATION_DATE TIMESTAMP NOT NULL,
066: MODIFICATION_DATE TIMESTAMP NOT NULL,
067: CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
068: CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
069: // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
070: // REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
071: ) IN OSSTABLESPACE
072:
073:
074: CREATE PROCEDURE INSERT_BF_BLOG
075: (
076: IN IN_DOMAIN_ID INTEGER,
077: IN IN_FOLDER VARCHAR(50),
078: IN IN_CAPTION VARCHAR(1024),
079: IN IN_COMMENTS VARCHAR(32672),
080: OUT OUT_KEY INTEGER,
081: OUT OUT_TIMESTAMP TIMESTAMP
082: ) LANGUAGE SQL SPECIFIC INSERT_BF_BLOG
083: BEGIN
084: DECLARE new_out_key INTEGER DEFAULT -1;
085: DECLARE new_out_timestamp TIMESTAMP;
086: SET new_out_key = NEXT VALUE FOR BLOG_ID_SEQ;
087: SET new_out_timestamp = CURRENT TIMESTAMP;
088: SET OUT_KEY = new_out_key;
089: SET OUT_TIMESTAMP = new_out_timestamp;
090: INSERT INTO " + strUserName + ".BF_BLOG(ID, DOMAIN_ID, FOLDER,
091: CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE)
092: VALUES (OUT_KEY, IN_DOMAIN_ID, IN_FOLDER, IN_CAPTION,
093: IN_COMMENTS, OUT_TIMESTAMP, OUT_TIMESTAMP);
094: END
095:
096:
097: CREATE PROCEDURE UPDATE_BF_BLOG
098: (
099: IN IN_FOLDER VARCHAR(50),
100: IN IN_CAPTION VARCHAR(1024),
101: IN IN_COMMENTS VARCHAR(32672),
102: IN IN_BLOG_ID INTEGER,
103: IN IN_DOMAIN_ID INTEGER,
104: IN IN_MODIFICATION_DATE TIMESTAMP,
105: OUT OUT_TIMESTAMP TIMESTAMP,
106: OUT OUT_ROW_COUNT INTEGER
107: ) LANGUAGE SQL SPECIFIC UPDATE_BF_BLOG
108: BEGIN
109: DECLARE new_out_timestamp TIMESTAMP;
110: DECLARE new_out_row_count INTEGER;
111: SET new_out_timestamp = CURRENT TIMESTAMP;
112: SET OUT_TIMESTAMP = new_out_timestamp;
113: UPDATE BF_BLOG SET FOLDER = IN_FOLDER, CAPTION = IN_CAPTION,
114: COMMENTS = IN_COMMENTS, MODIFICATION_DATE = OUT_TIMESTAMP
115: WHERE ID = IN_BLOG_ID AND DOMAIN_ID = IN_DOMAIN_ID
116: AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
117: GET DIAGNOSTICS new_out_row_count = ROW_COUNT;
118: SET OUT_ROW_COUNT = new_out_row_count;
119: END
120:
121:
122: CREATE SEQUENCE BLOGENTRY_ID_SEQ INCREMENT BY 1 START WITH 1 NO CYCLE
123:
124: CREATE TABLE BF_BLOG_ENTRY
125: (
126: ID INTEGER NOT NULL,
127: DOMAIN_ID INTEGER NOT NULL,
128: BLOG_ID INTEGER NOT NULL,
129: CAPTION VARCHAR(1024) NOT NULL,
130: COMMENTS VARCHAR(32768) NOT NULL,
131: IMAGEURL VARCHAR(1024) NOT NULL,
132: TARGETURL VARCHAR(1024) NOT NULL,
133: CREATION_DATE TIMESTAMP NOT NULL,
134: MODIFICATION_DATE TIMESTAMP NOT NULL,
135: CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),
136: CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)
137: REFERENCES BF_BLOG (ID) ON DELETE CASCADE
138: // CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)
139: // REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
140: ) IN OSSTABLESPACE
141:
142:
143: CREATE PROCEDURE INSERT_BF_BLOGENTR
144: (
145: IN IN_DOMAIN_ID INTEGER,
146: IN IN_BLOG_ID INTEGER,
147: IN IN_CAPTION VARCHAR(1024),
148: IN IN_COMMENTS VARCHAR(32672),
149: IN IN_IMAGEURL VARCHAR(1024),
150: IN IN_TARGETURL VARCHAR(1024),
151: OUT OUT_KEY INTEGER,
152: OUT OUT_TIMESTAMP TIMESTAMP
153: ) LANGUAGE SQL SPECIFIC INSERT_BF_BLOGENTR
154: BEGIN
155: DECLARE new_out_key INTEGER DEFAULT -1;
156: DECLARE new_out_timestamp TIMESTAMP;
157: SET new_out_key = NEXT VALUE FOR BLOGENTRY_ID_SEQ;
158: SET new_out_timestamp = CURRENT TIMESTAMP;
159: SET OUT_KEY = new_out_key;
160: SET OUT_TIMESTAMP = new_out_timestamp;
161: INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(ID, DOMAIN_ID, BLOG_ID,
162: CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)
163: VALUES (OUT_KEY, IN_DOMAIN_ID, IN_BLOG_ID, IN_CAPTION,
164: IN_COMMENTS, IN_IMAGEURL, IN_TARGETURL, OUT_TIMESTAMP, OUT_TIMESTAMP);
165: END
166:
167:
168: CREATE PROCEDURE UPDATE_BF_BLOGENTR
169: (
170: IN IN_CAPTION VARCHAR(1024),
171: IN IN_COMMENTS VARCHAR(32672),
172: IN IN_IMAGEURL VARCHAR(1024),
173: IN IN_TARGETURL VARCHAR(1024),
174: IN IN_ENTRY_ID INTEGER,
175: IN IN_DOMAIN_ID INTEGER,
176: IN IN_MODIFICATION_DATE TIMESTAMP,
177: OUT OUT_TIMESTAMP TIMESTAMP,
178: OUT OUT_ROW_COUNT INTEGER
179: ) LANGUAGE SQL SPECIFIC UPDATE_BF_BLOGENTR
180: BEGIN
181: DECLARE new_out_timestamp TIMESTAMP;
182: DECLARE new_out_row_count INTEGER;
183: SET new_out_timestamp = CURRENT TIMESTAMP;
184: SET OUT_TIMESTAMP = new_out_timestamp;
185: UPDATE BF_BLOG_ENTRY SET CAPTION = IN_CAPTION, COMMENTS = IN_COMMENTS,
186: IMAGEURL = IN_IMAGEURL, TARGETURL= IN_TARGETURL,
187: MODIFICATION_DATE = OUT_TIMESTAMP
188: WHERE ID = IN_ENTRY_ID AND DOMAIN_ID = IN_DOMAIN_ID
189: AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
190: GET DIAGNOSTICS new_out_row_count = ROW_COUNT;
191: SET OUT_ROW_COUNT = new_out_row_count;
192: END
193: */
194:
195: // Constants ////////////////////////////////////////////////////////////////
196: /**
197: * Maximal length of blog comments.
198: * Restriction for VARCHAR type is max. 32672 characters for IBM DB2.
199: */
200: public static final int BLOG_COMMENTS_MAXLENGTH_DB2 = 25000;
201:
202: /**
203: * Maximal length of blog entry comments.
204: * Restriction for VARCHAR type is max. 32672 characters for IBM DB2.
205: */
206: public static final int BLOGENTRY_COMMENTS_MAXLENGTH_DB2 = 25000;
207:
208: // Cached values ////////////////////////////////////////////////////////////
209:
210: /**
211: * Logger for this class
212: */
213: private static Logger s_logger = Log
214: .getInstance(DB2BlogDatabaseSchema.class);
215:
216: // Constructors /////////////////////////////////////////////////////////////
217:
218: /**
219: * Static initializer
220: */
221: static {
222: // Setup maximal length of individual fields specific for DB2 database
223: Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH_DB2);
224: Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH_DB2);
225: }
226:
227: /**
228: * Default constructor.
229: *
230: * @throws OSSException - error occured.
231: */
232: public DB2BlogDatabaseSchema() throws OSSException {
233: super ();
234: }
235:
236: // Public methods ///////////////////////////////////////////////////////////
237:
238: /**
239: * {@inheritDoc}
240: */
241: public void create(Connection cntDBConnection, String strUserName)
242: throws SQLException {
243: Statement stmQuery = null;
244: try {
245: stmQuery = cntDBConnection.createStatement();
246:
247: if (stmQuery.execute("CREATE SEQUENCE BLOG_ID_SEQ "
248: + "INCREMENT BY 1 START WITH 1 NO CYCLE")) {
249: // Close any results
250: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
251: }
252: s_logger.log(Level.FINEST, "Sequence BLOG_ID_SEQ created.");
253:
254: if (stmQuery.execute("create table BF_BLOG" + NL + "(" + NL
255: + " ID INTEGER NOT NULL," + NL
256: + " DOMAIN_ID INTEGER NOT NULL," + NL
257: + " FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH
258: + ") NOT NULL," + NL + " CAPTION VARCHAR("
259: + BLOG_CAPTION_MAXLENGTH + ") NOT NULL," + NL
260: + " COMMENTS VARCHAR("
261: + BLOG_COMMENTS_MAXLENGTH_DB2 + ") NOT NULL," + NL
262: + " CREATION_DATE TIMESTAMP NOT NULL," + NL
263: + " MODIFICATION_DATE TIMESTAMP NOT NULL,"
264: + NL
265: + " CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),"
266: + NL
267: +
268: // " CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
269: // " REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE," + NL +
270: " CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)"
271: + NL + ") IN OSSTABLESPACE")) {
272: // Close any results
273: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
274: }
275: s_logger.log(Level.FINEST, "Table BF_BLOG created.");
276:
277: ///////////////////////////////////////////////////////////////////////
278:
279: if (stmQuery
280: .execute("CREATE PROCEDURE INSERT_BF_BLOG "
281: + NL
282: + "( "
283: + NL
284: + " IN IN_DOMAIN_ID INTEGER, "
285: + NL
286: + " IN IN_FOLDER VARCHAR("
287: + BLOG_FOLDER_MAXLENGTH
288: + "), "
289: + NL
290: + " IN IN_CAPTION VARCHAR("
291: + BLOG_CAPTION_MAXLENGTH
292: + "), "
293: + NL
294: + " IN IN_COMMENTS VARCHAR("
295: + BLOG_COMMENTS_MAXLENGTH_DB2
296: + "), "
297: + NL
298: + " OUT OUT_KEY INTEGER, "
299: + NL
300: + " OUT OUT_TIMESTAMP TIMESTAMP "
301: + NL
302: + ") LANGUAGE SQL SPECIFIC INSERT_BF_BLOG "
303: + NL
304: + "BEGIN "
305: + NL
306: + " DECLARE new_out_key INTEGER DEFAULT -1; "
307: + NL
308: + " DECLARE new_out_timestamp TIMESTAMP; "
309: + NL
310: + " SET new_out_key = NEXT VALUE FOR BLOG_ID_SEQ; "
311: + NL
312: + " SET new_out_timestamp = CURRENT TIMESTAMP; "
313: + NL
314: + " SET OUT_KEY = new_out_key; "
315: + NL
316: + " SET OUT_TIMESTAMP = new_out_timestamp; "
317: + NL
318: + " INSERT INTO "
319: + strUserName
320: + ".BF_BLOG(ID, DOMAIN_ID, FOLDER, "
321: + NL
322: + " CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE) "
323: + NL
324: + " VALUES (OUT_KEY, IN_DOMAIN_ID, IN_FOLDER, IN_CAPTION, "
325: + NL
326: + " IN_COMMENTS, OUT_TIMESTAMP, OUT_TIMESTAMP); "
327: + NL + "END")) {
328: // Close any results
329: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
330: }
331: s_logger.log(Level.FINEST,
332: "Procedure INSERT_BF_BLOG created.");
333:
334: ///////////////////////////////////////////////////////////////////////
335:
336: if (stmQuery
337: .execute("CREATE PROCEDURE UPDATE_BF_BLOG "
338: + NL
339: + "( "
340: + NL
341: + " IN IN_FOLDER VARCHAR("
342: + BLOG_FOLDER_MAXLENGTH
343: + "), "
344: + NL
345: + " IN IN_CAPTION VARCHAR("
346: + BLOG_CAPTION_MAXLENGTH
347: + "), "
348: + NL
349: + " IN IN_COMMENTS VARCHAR("
350: + BLOG_COMMENTS_MAXLENGTH_DB2
351: + "), "
352: + NL
353: + " IN IN_BLOG_ID INTEGER, "
354: + NL
355: + " IN IN_DOMAIN_ID INTEGER, "
356: + NL
357: + " IN IN_MODIFICATION_DATE TIMESTAMP, "
358: + NL
359: + " OUT OUT_TIMESTAMP TIMESTAMP, "
360: + NL
361: + " OUT OUT_ROW_COUNT INTEGER "
362: + NL
363: + ") LANGUAGE SQL SPECIFIC UPDATE_BF_BLOG "
364: + NL
365: + "BEGIN "
366: + NL
367: + " DECLARE new_out_timestamp TIMESTAMP; "
368: + NL
369: + " DECLARE new_out_row_count INTEGER; "
370: + NL
371: + " SET new_out_timestamp = CURRENT TIMESTAMP; "
372: + NL
373: + " SET OUT_TIMESTAMP = new_out_timestamp; "
374: + NL
375: + " UPDATE "
376: + strUserName
377: + ".BF_BLOG SET FOLDER = IN_FOLDER, "
378: + NL
379: + " CAPTION = IN_CAPTION, COMMENTS = IN_COMMENTS, "
380: + NL
381: + " MODIFICATION_DATE = OUT_TIMESTAMP WHERE ID = IN_BLOG_ID "
382: + NL
383: + " AND DOMAIN_ID = IN_DOMAIN_ID "
384: + NL
385: + " AND MODIFICATION_DATE = IN_MODIFICATION_DATE; "
386: + NL
387: + " GET DIAGNOSTICS new_out_row_count = ROW_COUNT; "
388: + NL
389: + " SET OUT_ROW_COUNT = new_out_row_count; "
390: + NL + "END")) {
391: // Close any results
392: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
393: }
394: s_logger.log(Level.FINEST,
395: "Procedure UPDATE_BF_BLOG created.");
396:
397: ///////////////////////////////////////////////////////////////////////
398:
399: if (stmQuery.execute("CREATE SEQUENCE BLOGENTRY_ID_SEQ "
400: + "INCREMENT BY 1 START WITH 1 NO CYCLE")) {
401: // Close any results
402: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
403: }
404: s_logger.log(Level.FINEST,
405: "Sequence BLOGENTRY_ID_SEQ created.");
406:
407: if (stmQuery.execute("create table BF_BLOG_ENTRY" + NL
408: + "(" + NL + " ID INTEGER NOT NULL," + NL
409: + " DOMAIN_ID INTEGER NOT NULL," + NL
410: + " BLOG_ID INTEGER NOT NULL," + NL
411: + " CAPTION VARCHAR("
412: + BLOGENTRY_CAPTION_MAXLENGTH + ") NOT NULL," + NL
413: + " COMMENTS VARCHAR("
414: + BLOGENTRY_COMMENTS_MAXLENGTH_DB2 + ") NOT NULL,"
415: + NL + " IMAGEURL VARCHAR("
416: + BLOGENTRY_IMAGEURL_MAXLENGTH + ") NOT NULL,"
417: + NL
418: + " TARGETURL VARCHAR("
419: + BLOGENTRY_TARGETURL_MAXLENGTH
420: + ") NOT NULL,"
421: + NL
422: + " CREATION_DATE TIMESTAMP NOT NULL,"
423: + NL
424: + " MODIFICATION_DATE TIMESTAMP NOT NULL,"
425: + NL
426: + " CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),"
427: + NL
428: +
429: // " CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
430: // " REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE," + NL +
431: " CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)"
432: + NL
433: + " REFERENCES BF_BLOG (ID) ON DELETE CASCADE"
434: + NL + ") IN OSSTABLESPACE")) {
435: // Close any results
436: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
437: }
438: s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
439:
440: ///////////////////////////////////////////////////////////////////////
441:
442: if (stmQuery
443: .execute("CREATE PROCEDURE INSERT_BF_BLOGENTR"
444: + NL
445: + "("
446: + NL
447: + " IN IN_DOMAIN_ID INTEGER,"
448: + NL
449: + " IN IN_BLOG_ID INTEGER,"
450: + NL
451: + " IN IN_CAPTION VARCHAR("
452: + BLOGENTRY_CAPTION_MAXLENGTH
453: + "),"
454: + NL
455: + " IN IN_COMMENTS VARCHAR("
456: + BLOGENTRY_COMMENTS_MAXLENGTH_DB2
457: + "),"
458: + NL
459: + " IN IN_IMAGEURL VARCHAR("
460: + BLOGENTRY_IMAGEURL_MAXLENGTH
461: + "),"
462: + NL
463: + " IN IN_TARGETURL VARCHAR("
464: + BLOGENTRY_TARGETURL_MAXLENGTH
465: + "),"
466: + NL
467: + " OUT OUT_KEY INTEGER,"
468: + NL
469: + " OUT OUT_TIMESTAMP TIMESTAMP"
470: + NL
471: + ") LANGUAGE SQL SPECIFIC INSERT_BF_BLOGENTR "
472: + NL
473: + "BEGIN"
474: + NL
475: + " DECLARE new_out_key INTEGER DEFAULT -1;"
476: + NL
477: + " DECLARE new_out_timestamp TIMESTAMP;"
478: + NL
479: + " SET new_out_key = NEXT VALUE FOR BLOGENTRY_ID_SEQ;"
480: + NL
481: + " SET new_out_timestamp = CURRENT TIMESTAMP;"
482: + NL
483: + " SET OUT_KEY = new_out_key;"
484: + NL
485: + " SET OUT_TIMESTAMP = new_out_timestamp;"
486: + NL
487: + " INSERT INTO "
488: + strUserName
489: + ".BF_BLOG_ENTRY(ID, DOMAIN_ID, BLOG_ID, "
490: + NL
491: + " CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)"
492: + NL
493: + " VALUES (OUT_KEY, IN_DOMAIN_ID, IN_BLOG_ID, IN_CAPTION, "
494: + NL
495: + " IN_COMMENTS, IN_IMAGEURL, IN_TARGETURL, OUT_TIMESTAMP, OUT_TIMESTAMP);"
496: + NL + "END")) {
497: // Close any results
498: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
499: }
500: s_logger.log(Level.FINEST,
501: "Table INSERT_BF_BLOGENTR created.");
502:
503: ///////////////////////////////////////////////////////////////////////
504:
505: if (stmQuery
506: .execute("CREATE PROCEDURE UPDATE_BF_BLOGENTR"
507: + NL
508: + "("
509: + NL
510: + " IN IN_CAPTION VARCHAR("
511: + BLOGENTRY_CAPTION_MAXLENGTH
512: + "),"
513: + NL
514: + " IN IN_COMMENTS VARCHAR("
515: + BLOGENTRY_COMMENTS_MAXLENGTH_DB2
516: + "),"
517: + NL
518: + " IN IN_IMAGEURL VARCHAR("
519: + BLOGENTRY_IMAGEURL_MAXLENGTH
520: + "),"
521: + NL
522: + " IN IN_TARGETURL VARCHAR("
523: + BLOGENTRY_TARGETURL_MAXLENGTH
524: + "),"
525: + NL
526: + " IN IN_ENTRY_ID INTEGER,"
527: + NL
528: + " IN IN_DOMAIN_ID INTEGER,"
529: + NL
530: + " IN IN_MODIFICATION_DATE TIMESTAMP,"
531: + NL
532: + " OUT OUT_TIMESTAMP TIMESTAMP,"
533: + NL
534: + " OUT OUT_ROW_COUNT INTEGER"
535: + NL
536: + ") LANGUAGE SQL SPECIFIC UPDATE_BF_BLOGENTR "
537: + NL
538: + "BEGIN"
539: + NL
540: + " DECLARE new_out_timestamp TIMESTAMP;"
541: + NL
542: + " DECLARE new_out_row_count INTEGER;"
543: + NL
544: + " SET new_out_timestamp = CURRENT TIMESTAMP;"
545: + NL
546: + " SET OUT_TIMESTAMP = new_out_timestamp;"
547: + NL
548: + " UPDATE "
549: + strUserName
550: + ".BF_BLOG_ENTRY SET CAPTION = IN_CAPTION,"
551: + NL
552: + " COMMENTS = IN_COMMENTS, IMAGEURL = IN_IMAGEURL, TARGETURL= IN_TARGETURL,"
553: + NL
554: + " MODIFICATION_DATE = OUT_TIMESTAMP WHERE ID = IN_ENTRY_ID "
555: + " AND DOMAIN_ID = IN_DOMAIN_ID "
556: + NL
557: + " AND MODIFICATION_DATE = IN_MODIFICATION_DATE;"
558: + NL
559: + " GET DIAGNOSTICS new_out_row_count = ROW_COUNT;"
560: + NL
561: + " SET OUT_ROW_COUNT = new_out_row_count;"
562: + NL + "END")) {
563: // Close any results
564: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
565: }
566: s_logger.log(Level.FINEST,
567: "Table UPDATE_BF_BLOGENTR created.");
568: } catch (SQLException sqleExc) {
569: s_logger.log(Level.WARNING, "Failed to create schema "
570: + BLOG_SCHEMA_NAME, sqleExc);
571: throw sqleExc;
572: } finally {
573: DatabaseUtils.closeStatement(stmQuery);
574: }
575: }
576:
577: /**
578: * {@inheritDoc}
579: */
580: public String getInsertBlogAndFetchGeneratedValues()
581: throws OSSException {
582: return "call INSERT_BF_BLOG (?, ?, ?, ?, ?, ?)";
583: }
584:
585: /**
586: * {@inheritDoc}
587: */
588: public String getUpdateBlogAndFetchGeneratedValues()
589: throws OSSException {
590: return "call UPDATE_BF_BLOG (?, ?, ?, ?, ?, ?, ?, ?)";
591: }
592:
593: /**
594: * {@inheritDoc}
595: */
596: public String getInsertBlog() throws OSSException {
597: StringBuffer buffer = new StringBuffer();
598:
599: // MySQL requires for AUTO_INCREMENT columns to specify ID and pass null as a value
600: buffer.append("INSERT INTO BF_BLOG(");
601: buffer.append(BLOG_COLUMNS);
602: buffer
603: .append(") VALUES (NEXT VALUE FOR BLOG_ID_SEQ, ?, ?, ?, ?, ");
604: buffer.append(DatabaseImpl.getInstance()
605: .getCurrentTimestampFunctionCall());
606: buffer.append(",");
607: buffer.append(DatabaseImpl.getInstance()
608: .getCurrentTimestampFunctionCall());
609: buffer.append(")");
610:
611: return buffer.toString();
612: }
613:
614: /**
615: * {@inheritDoc}
616: */
617: public String getInsertEntryAndFetchGeneratedValues()
618: throws OSSException {
619: return "call INSERT_BF_BLOGENTR (?, ?, ?, ?, ?, ?, ?, ?)";
620: }
621:
622: /**
623: * {@inheritDoc}
624: */
625: public String getUpdateEntryAndFetchGeneratedValues()
626: throws OSSException {
627: return "call UPDATE_BF_BLOGENTR (?, ?, ?, ?, ?, ?, ?, ?, ?)";
628: }
629:
630: /**
631: * {@inheritDoc}
632: */
633: public String getInsertEntry() throws OSSException {
634: StringBuffer buffer = new StringBuffer();
635:
636: // MySQL requires for AUTO_INCREMENT columns to specify ID and pass null as a value
637: buffer.append("INSERT INTO BF_BLOG_ENTRY(");
638: buffer.append(ENTRY_COLUMNS);
639: buffer
640: .append(") VALUES (NEXT VALUE FOR BLOGENTRY_ID_SEQ, ?, ?, ?, ?, ?, ?, ");
641: buffer.append(DatabaseImpl.getInstance()
642: .getCurrentTimestampFunctionCall());
643: buffer.append(",");
644: buffer.append(DatabaseImpl.getInstance()
645: .getCurrentTimestampFunctionCall());
646: buffer.append(")");
647:
648: return buffer.toString();
649: }
650: }
|