DB2BlogDatabaseSchema


1   /*
2    * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenChronicle
5    * 
6    * $Id: DB2BlogDatabaseSchema.java,v 1.9 2007/02/20 01:58:05 bastafidli Exp $
7    * 
8    * This program is free software; you can redistribute it and/or modify
9    * it under the terms of the GNU General Public License as published by
10   * the Free Software Foundation; version 2 of the License. 
11   * 
12   * This program is distributed in the hope that it will be useful,
13   * but WITHOUT ANY WARRANTY; without even the implied warranty of
14   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
15   * GNU General Public License for more details.
16   * 
17   * You should have received a copy of the GNU General Public License
18   * along with this program; if not, write to the Free Software
19   * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 
20   */
21  
22  package org.opensubsystems.blog.persist.db.db2;
23  
24  import java.sql.Connection  ;
25  import java.sql.SQLException  ;
26  import java.sql.Statement  ;
27  import java.util.logging.Level  ;
28  import java.util.logging.Logger  ;
29  
30  import org.opensubsystems.blog.data.Blog;
31  import org.opensubsystems.blog.data.Entry;
32  import org.opensubsystems.blog.persist.db.BlogDatabaseSchema;
33  import org.opensubsystems.core.error.OSSException;
34  import org.opensubsystems.core.persist.db.DatabaseImpl;
35  import org.opensubsystems.core.util.DatabaseUtils;
36  import org.opensubsystems.core.util.Log;
37  
38  /**
39   * Database specific operations related to persistence of blogs and their 
40   * entries. This class encapsulate functionality specific for IBM DB2.
41   *
42   * @version $Id: DB2BlogDatabaseSchema.java,v 1.9 2007/02/20 01:58:05 bastafidli Exp $
43   * @author Julian Legeny
44   * @code.reviewer Miro Halas
45   * @code.reviewed 1.1 2006/07/13 23:43:43 jlegeny
46   */
47  public class DB2BlogDatabaseSchema extends BlogDatabaseSchema
48  {   
49     /*
50        Use autogenerated numbers for IDs using sequence
51        Name all constraints to easily identify them later.
52        For stored procedures which provides UPDATE must be defined
53        also output parameter NUMBER OF AFFECTED ROWS. Because IBM DB2
54        driver does not support returning number of processed rows using 
55        [ RowCounter = insertStatement.executeUpdate() ]. 
56  
57        CREATE SEQUENCE BLOG_ID_SEQ INCREMENT BY 1 START WITH 1 NO CYCLE
58  
59        CREATE TABLE BF_BLOG
60        (
61           ID INTEGER NOT NULL,
62           DOMAIN_ID INTEGER NOT NULL,
63           FOLDER VARCHAR(50) NOT NULL,
64           CAPTION VARCHAR(1024) NOT NULL,
65           COMMENTS VARCHAR(32768) NOT NULL,
66           CREATION_DATE TIMESTAMP NOT NULL,
67           MODIFICATION_DATE TIMESTAMP NOT NULL,
68           CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
69           CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
70           // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
71           //    REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
72        ) IN OSSTABLESPACE
73  
74  
75        CREATE PROCEDURE INSERT_BF_BLOG
76        (
77           IN IN_DOMAIN_ID INTEGER,
78           IN IN_FOLDER VARCHAR(50),
79           IN IN_CAPTION VARCHAR(1024),
80           IN IN_COMMENTS VARCHAR(32672),
81           OUT OUT_KEY INTEGER,
82           OUT OUT_TIMESTAMP TIMESTAMP
83        ) LANGUAGE SQL SPECIFIC INSERT_BF_BLOG
84        BEGIN
85           DECLARE new_out_key INTEGER DEFAULT -1;
86           DECLARE new_out_timestamp TIMESTAMP;
87           SET new_out_key = NEXT VALUE FOR BLOG_ID_SEQ;
88           SET new_out_timestamp = CURRENT TIMESTAMP;
89           SET OUT_KEY = new_out_key;
90           SET OUT_TIMESTAMP = new_out_timestamp;
91           INSERT INTO " + strUserName + ".BF_BLOG(ID, DOMAIN_ID, FOLDER, 
92              CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE)
93              VALUES (OUT_KEY, IN_DOMAIN_ID, IN_FOLDER, IN_CAPTION, 
94              IN_COMMENTS, OUT_TIMESTAMP, OUT_TIMESTAMP);
95        END
96  
97  
98        CREATE PROCEDURE UPDATE_BF_BLOG
99        (
100          IN IN_FOLDER VARCHAR(50),
101          IN IN_CAPTION VARCHAR(1024),
102          IN IN_COMMENTS VARCHAR(32672),
103          IN IN_BLOG_ID INTEGER,
104          IN IN_DOMAIN_ID INTEGER,
105          IN IN_MODIFICATION_DATE TIMESTAMP,
106          OUT OUT_TIMESTAMP TIMESTAMP,
107          OUT OUT_ROW_COUNT INTEGER
108       ) LANGUAGE SQL SPECIFIC UPDATE_BF_BLOG
109       BEGIN
110          DECLARE new_out_timestamp TIMESTAMP;
111          DECLARE new_out_row_count INTEGER;
112          SET new_out_timestamp = CURRENT TIMESTAMP;
113          SET OUT_TIMESTAMP = new_out_timestamp;
114          UPDATE BF_BLOG SET FOLDER = IN_FOLDER, CAPTION = IN_CAPTION,
115                 COMMENTS = IN_COMMENTS, MODIFICATION_DATE = OUT_TIMESTAMP 
116                 WHERE ID = IN_BLOG_ID AND DOMAIN_ID = IN_DOMAIN_ID
117                 AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
118          GET DIAGNOSTICS new_out_row_count = ROW_COUNT;
119          SET OUT_ROW_COUNT = new_out_row_count;
120       END
121 
122 
123       CREATE SEQUENCE BLOGENTRY_ID_SEQ INCREMENT BY 1 START WITH 1 NO CYCLE
124 
125       CREATE TABLE BF_BLOG_ENTRY
126       (
127          ID INTEGER NOT NULL,
128          DOMAIN_ID INTEGER NOT NULL,
129          BLOG_ID INTEGER NOT NULL,
130          CAPTION VARCHAR(1024) NOT NULL,
131          COMMENTS VARCHAR(32768) NOT NULL,
132          IMAGEURL VARCHAR(1024) NOT NULL,
133          TARGETURL VARCHAR(1024) NOT NULL,
134          CREATION_DATE TIMESTAMP NOT NULL,
135          MODIFICATION_DATE TIMESTAMP NOT NULL,
136          CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),
137          CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID) 
138             REFERENCES BF_BLOG (ID) ON DELETE CASCADE
139          // CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)
140          //    REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
141       ) IN OSSTABLESPACE
142 
143 
144       CREATE PROCEDURE INSERT_BF_BLOGENTR
145       (
146          IN IN_DOMAIN_ID INTEGER,
147          IN IN_BLOG_ID INTEGER,
148          IN IN_CAPTION VARCHAR(1024),
149          IN IN_COMMENTS VARCHAR(32672),
150          IN IN_IMAGEURL VARCHAR(1024),
151          IN IN_TARGETURL VARCHAR(1024),
152          OUT OUT_KEY INTEGER,
153          OUT OUT_TIMESTAMP TIMESTAMP
154       ) LANGUAGE SQL SPECIFIC INSERT_BF_BLOGENTR
155       BEGIN
156          DECLARE new_out_key INTEGER DEFAULT -1;
157          DECLARE new_out_timestamp TIMESTAMP;
158          SET new_out_key = NEXT VALUE FOR BLOGENTRY_ID_SEQ;
159          SET new_out_timestamp = CURRENT TIMESTAMP;
160          SET OUT_KEY = new_out_key;
161          SET OUT_TIMESTAMP = new_out_timestamp;
162          INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(ID, DOMAIN_ID, BLOG_ID, 
163             CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)
164             VALUES (OUT_KEY, IN_DOMAIN_ID, IN_BLOG_ID, IN_CAPTION, 
165             IN_COMMENTS, IN_IMAGEURL, IN_TARGETURL, OUT_TIMESTAMP, OUT_TIMESTAMP);
166       END
167 
168 
169       CREATE PROCEDURE UPDATE_BF_BLOGENTR
170       (
171          IN IN_CAPTION VARCHAR(1024),
172          IN IN_COMMENTS VARCHAR(32672),
173          IN IN_IMAGEURL VARCHAR(1024),
174          IN IN_TARGETURL VARCHAR(1024),
175          IN IN_ENTRY_ID INTEGER,
176          IN IN_DOMAIN_ID INTEGER,
177          IN IN_MODIFICATION_DATE TIMESTAMP,
178          OUT OUT_TIMESTAMP TIMESTAMP,
179          OUT OUT_ROW_COUNT INTEGER
180       ) LANGUAGE SQL SPECIFIC UPDATE_BF_BLOGENTR
181       BEGIN
182          DECLARE new_out_timestamp TIMESTAMP;
183          DECLARE new_out_row_count INTEGER;
184          SET new_out_timestamp = CURRENT TIMESTAMP;
185          SET OUT_TIMESTAMP = new_out_timestamp;
186          UPDATE BF_BLOG_ENTRY SET CAPTION = IN_CAPTION, COMMENTS = IN_COMMENTS,
187                 IMAGEURL = IN_IMAGEURL, TARGETURL= IN_TARGETURL,
188                 MODIFICATION_DATE = OUT_TIMESTAMP 
189                 WHERE ID = IN_ENTRY_ID AND DOMAIN_ID = IN_DOMAIN_ID
190                 AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
191          GET DIAGNOSTICS new_out_row_count = ROW_COUNT;
192          SET OUT_ROW_COUNT = new_out_row_count;
193       END
194    */
195 
196    // Constants ////////////////////////////////////////////////////////////////
197 
198    /**
199     * Maximal length of blog comments.
200     * Restriction for VARCHAR type is max. 32672 characters for IBM DB2.
201     */
202    public static final int BLOG_COMMENTS_MAXLENGTH_DB2 = 25000;
203 
204    /**
205     * Maximal length of blog entry comments.
206     * Restriction for VARCHAR type is max. 32672 characters for IBM DB2.
207     */
208    public static final int BLOGENTRY_COMMENTS_MAXLENGTH_DB2 = 25000;
209 
210    // Cached values ////////////////////////////////////////////////////////////
211 
212    /**
213     * Logger for this class
214     */
215    private static Logger   s_logger = Log.getInstance(DB2BlogDatabaseSchema.class);
216 
217    // Constructors /////////////////////////////////////////////////////////////
218 
219    /**
220     * Static initializer
221     */
222    static
223    {
224       // Setup maximal length of individual fields specific for DB2  database
225       Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH_DB2);
226       Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH_DB2);
227    }
228 
229    /**
230     * Default constructor.
231     * 
232     * @throws OSSException - error occured.
233     */
234    public DB2BlogDatabaseSchema(
235    ) throws OSSException
236    {
237       super();
238    }   
239 
240    // Public methods ///////////////////////////////////////////////////////////
241 
242    /**
243     * {@inheritDoc}
244     */
245    public void create(
246       Connection   cntDBConnection,
247       String       strUserName
248    ) throws SQLException  
249    {
250       Statement   stmQuery = null;
251       try
252       {        
253          stmQuery = cntDBConnection.createStatement();
254 
255          if (stmQuery.execute("CREATE SEQUENCE BLOG_ID_SEQ " +
256                               "INCREMENT BY 1 START WITH 1 NO CYCLE"))
257          {
258             // Close any results
259             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
260          }
261          s_logger.log(Level.FINEST, "Sequence BLOG_ID_SEQ created.");
262          
263          if (stmQuery.execute(
264             "create table BF_BLOG" + NL +
265             "(" + NL + 
266             "   ID INTEGER NOT NULL," + NL +
267             "   DOMAIN_ID INTEGER NOT NULL," + NL +
268             "   FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + ") NOT NULL," + NL +
269             "   CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
270             "   COMMENTS VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_DB2 + ") NOT NULL," + NL +
271             "   CREATION_DATE TIMESTAMP NOT NULL," + NL +
272             "   MODIFICATION_DATE TIMESTAMP NOT NULL," + NL +
273             "   CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID)," + NL +
274             // "   CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
275             // "      REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE," + NL +
276             "   CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)" + NL +
277             ") IN OSSTABLESPACE"))
278          {
279             // Close any results
280             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
281          }
282          s_logger.log(Level.FINEST, "Table BF_BLOG created.");
283 
284          ///////////////////////////////////////////////////////////////////////
285 
286          if (stmQuery.execute(
287             "CREATE PROCEDURE INSERT_BF_BLOG " + NL +
288             "( " + NL +
289             "   IN IN_DOMAIN_ID INTEGER, " + NL +
290             "   IN IN_FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + "), " + NL +
291             "   IN IN_CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + "), " + NL +
292             "   IN IN_COMMENTS VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_DB2 + "), " + NL +
293             "   OUT OUT_KEY INTEGER, " + NL +
294             "   OUT OUT_TIMESTAMP TIMESTAMP " + NL +
295             ") LANGUAGE SQL SPECIFIC INSERT_BF_BLOG " + NL +
296             "BEGIN " + NL +
297             "   DECLARE new_out_key INTEGER DEFAULT -1; " + NL +
298             "   DECLARE new_out_timestamp TIMESTAMP; " + NL +
299             "   SET new_out_key = NEXT VALUE FOR BLOG_ID_SEQ; " + NL +
300             "   SET new_out_timestamp = CURRENT TIMESTAMP; " + NL +
301             "   SET OUT_KEY = new_out_key; " + NL +
302             "   SET OUT_TIMESTAMP = new_out_timestamp; " + NL +
303             "   INSERT INTO " + strUserName + ".BF_BLOG(ID, DOMAIN_ID, FOLDER,  " + NL +
304             "      CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE) " + NL +
305             "   VALUES (OUT_KEY, IN_DOMAIN_ID, IN_FOLDER, IN_CAPTION, " + NL +
306             "      IN_COMMENTS, OUT_TIMESTAMP, OUT_TIMESTAMP); " + NL +
307             "END"))
308          {
309             // Close any results
310             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
311          }
312          s_logger.log(Level.FINEST, "Procedure INSERT_BF_BLOG created.");
313 
314          ///////////////////////////////////////////////////////////////////////
315 
316          if (stmQuery.execute(
317             "CREATE PROCEDURE UPDATE_BF_BLOG " + NL +
318             "( " + NL +
319             "   IN IN_FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + "), " + NL +
320             "   IN IN_CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + "), " + NL +
321             "   IN IN_COMMENTS VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_DB2 + "), " + NL +
322             "   IN IN_BLOG_ID INTEGER, " + NL +
323             "   IN IN_DOMAIN_ID INTEGER, " + NL +
324             "   IN IN_MODIFICATION_DATE TIMESTAMP, " + NL +
325             "   OUT OUT_TIMESTAMP TIMESTAMP, " + NL +
326             "   OUT OUT_ROW_COUNT INTEGER " + NL +
327             ") LANGUAGE SQL SPECIFIC UPDATE_BF_BLOG " + NL +
328             "BEGIN " + NL +
329             "   DECLARE new_out_timestamp TIMESTAMP; " + NL +
330             "   DECLARE new_out_row_count INTEGER; " + NL +
331             "   SET new_out_timestamp = CURRENT TIMESTAMP; " + NL +
332             "   SET OUT_TIMESTAMP = new_out_timestamp; " + NL +
333             "   UPDATE " + strUserName + ".BF_BLOG SET FOLDER = IN_FOLDER, " + NL +
334             "          CAPTION = IN_CAPTION, COMMENTS = IN_COMMENTS, " + NL +
335             "          MODIFICATION_DATE = OUT_TIMESTAMP WHERE ID = IN_BLOG_ID " + NL +
336             "          AND DOMAIN_ID = IN_DOMAIN_ID " + NL +
337             "          AND MODIFICATION_DATE = IN_MODIFICATION_DATE; " + NL +
338             "   GET DIAGNOSTICS new_out_row_count = ROW_COUNT; " + NL +
339             "   SET OUT_ROW_COUNT = new_out_row_count; " + NL +
340             "END"))
341          {
342             // Close any results
343             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
344          }
345          s_logger.log(Level.FINEST, "Procedure UPDATE_BF_BLOG created.");
346 
347          ///////////////////////////////////////////////////////////////////////
348          
349          if (stmQuery.execute("CREATE SEQUENCE BLOGENTRY_ID_SEQ " +
350                               "INCREMENT BY 1 START WITH 1 NO CYCLE"))
351          {
352             // Close any results
353             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
354          }
355          s_logger.log(Level.FINEST, "Sequence BLOGENTRY_ID_SEQ created.");
356          
357 
358          if (stmQuery.execute(
359             "create table BF_BLOG_ENTRY" + NL +
360             "(" + NL +
361             "  ID INTEGER NOT NULL," + NL +
362             "  DOMAIN_ID INTEGER NOT NULL," + NL +
363             "  BLOG_ID INTEGER NOT NULL," + NL +
364             "  CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
365             "  COMMENTS VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_DB2 + ") NOT NULL," + NL +
366             "  IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ") NOT NULL," + NL +
367             "  TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ") NOT NULL," + NL +
368             "  CREATION_DATE TIMESTAMP NOT NULL," + NL +
369             "  MODIFICATION_DATE TIMESTAMP NOT NULL," + NL +
370             "  CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID)," + NL +
371             // "  CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
372             // "     REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE," + NL +
373             "  CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)" + NL +
374             "     REFERENCES BF_BLOG (ID) ON DELETE CASCADE" + NL +
375             ") IN OSSTABLESPACE"))
376          {
377             // Close any results
378             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
379          }
380          s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
381          
382          ///////////////////////////////////////////////////////////////////////
383 
384          if (stmQuery.execute(
385             "CREATE PROCEDURE INSERT_BF_BLOGENTR" + NL +
386             "(" + NL +
387             "   IN IN_DOMAIN_ID INTEGER," + NL +
388             "   IN IN_BLOG_ID INTEGER," + NL +
389             "   IN IN_CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ")," + NL +
390             "   IN IN_COMMENTS VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_DB2 + ")," + NL +
391             "   IN IN_IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ")," + NL +
392             "   IN IN_TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ")," + NL +
393             "   OUT OUT_KEY INTEGER," + NL +
394             "   OUT OUT_TIMESTAMP TIMESTAMP" + NL +
395             ") LANGUAGE SQL SPECIFIC INSERT_BF_BLOGENTR " + NL +
396             "BEGIN" + NL +
397             "   DECLARE new_out_key INTEGER DEFAULT -1;" + NL +
398             "   DECLARE new_out_timestamp TIMESTAMP;" + NL +
399             "   SET new_out_key = NEXT VALUE FOR BLOGENTRY_ID_SEQ;" + NL +
400             "   SET new_out_timestamp = CURRENT TIMESTAMP;" + NL +
401             "   SET OUT_KEY = new_out_key;" + NL +
402             "   SET OUT_TIMESTAMP = new_out_timestamp;" + NL +
403             "   INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(ID, DOMAIN_ID, BLOG_ID, " + NL +
404             "      CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)" + NL +
405             "      VALUES (OUT_KEY, IN_DOMAIN_ID, IN_BLOG_ID, IN_CAPTION, " + NL +
406             "      IN_COMMENTS, IN_IMAGEURL, IN_TARGETURL, OUT_TIMESTAMP, OUT_TIMESTAMP);" + NL +
407             "END"))
408          {
409             // Close any results
410             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
411          }
412          s_logger.log(Level.FINEST, "Table INSERT_BF_BLOGENTR created.");
413          
414          ///////////////////////////////////////////////////////////////////////
415 
416          if (stmQuery.execute(
417             "CREATE PROCEDURE UPDATE_BF_BLOGENTR" + NL +
418             "(" + NL +
419             "   IN IN_CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ")," + NL +
420             "   IN IN_COMMENTS VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_DB2 + ")," + NL +
421             "   IN IN_IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ")," + NL +
422             "   IN IN_TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ")," + NL +
423             "   IN IN_ENTRY_ID INTEGER," + NL +
424             "   IN IN_DOMAIN_ID INTEGER," + NL +
425             "   IN IN_MODIFICATION_DATE TIMESTAMP," + NL +
426             "   OUT OUT_TIMESTAMP TIMESTAMP," + NL +
427             "   OUT OUT_ROW_COUNT INTEGER" + NL +
428             ") LANGUAGE SQL SPECIFIC UPDATE_BF_BLOGENTR " + NL +
429             "BEGIN" + NL +
430             "   DECLARE new_out_timestamp TIMESTAMP;" + NL +
431             "   DECLARE new_out_row_count INTEGER;" + NL +
432             "   SET new_out_timestamp = CURRENT TIMESTAMP;" + NL +
433             "   SET OUT_TIMESTAMP = new_out_timestamp;" + NL +
434             "   UPDATE  " + strUserName + ".BF_BLOG_ENTRY SET CAPTION = IN_CAPTION," + NL +
435             "      COMMENTS = IN_COMMENTS, IMAGEURL = IN_IMAGEURL, TARGETURL= IN_TARGETURL," + NL +
436             "      MODIFICATION_DATE = OUT_TIMESTAMP WHERE ID = IN_ENTRY_ID " +
437             "      AND DOMAIN_ID = IN_DOMAIN_ID " + NL +
438             "      AND MODIFICATION_DATE = IN_MODIFICATION_DATE;" + NL +
439             "   GET DIAGNOSTICS new_out_row_count = ROW_COUNT;" + NL +
440             "   SET OUT_ROW_COUNT = new_out_row_count;" + NL +
441             "END"))
442          {
443             // Close any results
444             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
445          }
446          s_logger.log(Level.FINEST, "Table UPDATE_BF_BLOGENTR created.");
447       }
448       catch (SQLException   sqleExc)
449       {
450          s_logger.log(Level.WARNING, 
451                       "Failed to create schema " + BLOG_SCHEMA_NAME, sqleExc);
452          throw sqleExc;
453       }
454       finally
455       {
456          DatabaseUtils.closeStatement(stmQuery);
457       }
458    }
459 
460    /**
461     * {@inheritDoc}
462     */
463    public String   getInsertBlogAndFetchGeneratedValues(
464    ) throws OSSException
465    {
466       return  "call INSERT_BF_BLOG (?, ?, ?, ?, ?, ?)";
467    }
468 
469    /**
470     * {@inheritDoc}
471     */
472    public String   getUpdateBlogAndFetchGeneratedValues(
473    ) throws OSSException
474    {
475       return  "call UPDATE_BF_BLOG (?, ?, ?, ?, ?, ?, ?, ?)";
476    }
477 
478    /**
479     * {@inheritDoc}
480     */
481    public String   getInsertBlog(
482    ) throws OSSException
483    {
484       StringBuffer   buffer = new StringBuffer  ();
485       
486       // MySQL requires for AUTO_INCREMENT columns to specify ID and pass null as a value
487       buffer.append("INSERT INTO BF_BLOG(");
488       buffer.append(BLOG_COLUMNS);
489       buffer.append(") VALUES (NEXT VALUE FOR BLOG_ID_SEQ, ?, ?, ?, ?, ");
490       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
491       buffer.append(",");
492       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
493       buffer.append(")");
494 
495       return buffer.toString();
496    }
497 
498    /**
499     * {@inheritDoc}
500     */
501    public String   getInsertEntryAndFetchGeneratedValues(
502    ) throws OSSException
503    {
504       return  "call INSERT_BF_BLOGENTR (?, ?, ?, ?, ?, ?, ?, ?)";
505    }
506 
507    /**
508     * {@inheritDoc}
509     */
510    public String   getUpdateEntryAndFetchGeneratedValues(
511    ) throws OSSException
512    {
513       return  "call UPDATE_BF_BLOGENTR (?, ?, ?, ?, ?, ?, ?, ?, ?)";
514    }
515 
516    /**
517     * {@inheritDoc}
518     */
519    public String   getInsertEntry(
520    ) throws OSSException
521    {
522       StringBuffer   buffer = new StringBuffer  ();
523       
524       // MySQL requires for AUTO_INCREMENT columns to specify ID and pass null as a value
525       buffer.append("INSERT INTO BF_BLOG_ENTRY(");
526       buffer.append(ENTRY_COLUMNS);
527       buffer.append(") VALUES (NEXT VALUE FOR BLOGENTRY_ID_SEQ, ?, ?, ?, ?, ?, ?, ");
528       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
529       buffer.append(",");
530       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
531       buffer.append(")");
532 
533       return buffer.toString();
534    }
535 }
536
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags