PostgreSQLBlogDatabaseSchema


1   /*
2    * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenChronicle
5    * 
6    * $Id: PostgreSQLBlogDatabaseSchema.java,v 1.8 2007/02/20 01:58:06 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.postgresql;
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.util.DatabaseUtils;
35  import org.opensubsystems.core.util.Log;
36  
37  /**
38   * Database specific operations related to persistence of blogs and their 
39   * entries. This class encapsulate functionality specific for Postgre SQL.
40   *
41   * @version $Id: PostgreSQLBlogDatabaseSchema.java,v 1.8 2007/02/20 01:58:06 bastafidli Exp $
42   * @author Julian Legeny
43   * @code.reviewer Miro Halas
44   * @code.reviewed 1.3 2006/07/21 00:54:58 jlegeny
45   */
46  public class PostgreSQLBlogDatabaseSchema extends BlogDatabaseSchema
47  {   
48     /*
49        Use autogenerated numbers for IDs using sequence
50        Name all constraints to easily identify them later.
51  
52        CREATE TABLE BF_BLOG
53        (
54           ID SERIAL,
55           DOMAIN_ID INTEGER NOT NULL,
56           FOLDER VARCHAR(50) NOT NULL,
57           CAPTION VARCHAR(1024) NOT NULL,
58           COMMENTS VARCHAR(32768) NOT NULL,
59           CREATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
60           MODIFICATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
61           CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
62           CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
63           // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
64           //    REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
65        )
66  
67  
68        CREATE OR REPLACE FUNCTION INSERT_BF_BLOG
69        (
70           INTEGER,
71           VARCHAR(50),
72           VARCHAR(1024),
73           VARCHAR(32768)
74        ) RETURNS type_int_timestamp AS '
75        DECLARE
76           IN_DOMAIN_ID ALIAS FOR $1;
77           IN_FOLDER ALIAS FOR $2;
78           IN_CAPTION ALIAS FOR $3;
79           IN_COMMENTS ALIAS FOR $4;
80  
81           out_key INTEGER;
82           out_timestamp TIMESTAMP WITH TIME ZONE;
83           output_result type_int_timestamp;
84        BEGIN
85           SELECT INTO out_timestamp now();
86           INSERT INTO " + strUserName + ".BF_BLOG(DOMAIN_ID, FOLDER, 
87              CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE)
88              VALUES (IN_DOMAIN_ID, IN_FOLDER, IN_CAPTION, 
89              IN_COMMENTS, out_timestamp, out_timestamp);
90           out_key := CURRVAL(''bf_blog_id_seq'');
91           output_result.intgr := out_key;
92           output_result.tmstp := out_timestamp;
93           RETURN output_result;
94  
95        END
96        ' LANGUAGE 'plpgsql';
97  
98  
99        CREATE OR REPLACE FUNCTION UPDATE_BF_BLOG
100       (
101          VARCHAR(50),
102          VARCHAR(1024),
103          VARCHAR(32768),
104          INTEGER,
105          INTEGER,
106          TIMESTAMP WITH TIME ZONE
107       ) RETURNS type_int_timestamp AS '
108       DECLARE
109          IN_FOLDER ALIAS FOR $1;
110          IN_CAPTION ALIAS FOR $2;
111          IN_COMMENTS ALIAS FOR $3;
112          IN_BLOG_ID ALIAS FOR $4;
113          IN_DOMAIN_ID ALIAS FOR $5;
114          IN_MODIFICATION_DATE ALIAS FOR $6;
115 
116          out_updated_count INTEGER;
117          out_timestamp TIMESTAMP WITH TIME ZONE;
118          output_result type_int_timestamp;
119       BEGIN
120          SELECT INTO out_timestamp now();
121          UPDATE " + strUserName + ".BF_BLOG SET FOLDER = IN_FOLDER, CAPTION = IN_CAPTION,
122                 COMMENTS = IN_COMMENTS, MODIFICATION_DATE = out_timestamp 
123                 WHERE ID = IN_BLOG_ID AND DOMAIN_ID = IN_DOMAIN_ID
124                 AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
125          GET DIAGNOSTICS out_updated_count = ROW_COUNT;
126          output_result.intgr := out_updated_count;
127          output_result.tmstp := out_timestamp;
128          RETURN output_result;
129       END;
130       ' LANGUAGE 'plpgsql';
131 
132 
133       CREATE TABLE BF_BLOG_ENTRY
134       (
135          ID SERIAL,
136          DOMAIN_ID INTEGER NOT NULL,
137          BLOG_ID INTEGER NOT NULL,
138          CAPTION VARCHAR(1024) NOT NULL,
139          COMMENTS VARCHAR(32768) NOT NULL,
140          IMAGEURL VARCHAR(1024) NOT NULL,
141          TARGETURL VARCHAR(1024) NOT NULL,
142          CREATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
143          MODIFICATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL,
144          CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),
145          CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID) 
146             REFERENCES BF_BLOG (ID) ON DELETE CASCADE
147          // CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)
148          //    REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
149       )
150 
151 
152       CREATE OR REPLACE FUNCTION INSERT_BF_BLOGENTR
153       (
154          INTEGER,
155          INTEGER,
156          VARCHAR(1024),
157          VARCHAR(32768),
158          VARCHAR(1024),
159          VARCHAR(1024)
160       ) RETURNS type_int_timestamp AS '
161       DECLARE
162          IN_DOMAIN_ID ALIAS FOR $1;
163          IN_BLOG_ID ALIAS FOR $2;
164          IN_CAPTION ALIAS FOR $3;
165          IN_COMMENTS ALIAS FOR $4;
166          IN_IMAGEURL ALIAS FOR $5;
167          IN_TARGETURL ALIAS FOR $6;
168 
169          out_key INTEGER;
170          out_timestamp TIMESTAMP WITH TIME ZONE;
171          output_result type_int_timestamp;
172       BEGIN
173          SELECT INTO out_timestamp now();
174          INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID, 
175             CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)
176             VALUES (IN_DOMAIN_ID, IN_BLOG_ID, IN_CAPTION, 
177             IN_COMMENTS, IN_IMAGEURL, IN_TARGETURL, out_timestamp, out_timestamp);
178          out_key := CURRVAL(''bf_blog_entry_id_seq'');
179          output_result.intgr := out_key;
180          output_result.tmstp := out_timestamp;
181          RETURN output_result;
182       END
183       ' LANGUAGE 'plpgsql';
184 
185 
186       CREATE OR REPLACE FUNCTION UPDATE_BF_BLOGENTR
187       (
188          VARCHAR(1024),
189          VARCHAR(32768),
190          VARCHAR(1024),
191          VARCHAR(1024),
192          INTEGER,
193          INTEGER,
194          TIMESTAMP WITH TIME ZONE
195       ) RETURNS type_int_timestamp AS '
196       DECLARE
197          IN_CAPTION ALIAS FOR $1;
198          IN_COMMENTS ALIAS FOR $2;
199          IN_IMAGEURL ALIAS FOR $3;
200          IN_TARGETURL ALIAS FOR $4;
201          IN_ENTRY_ID ALIAS FOR $5;
202          IN_DOMAIN_ID ALIAS FOR $6;
203          IN_MODIFICATION_DATE ALIAS FOR $7;
204 
205          out_updated_count INTEGER;
206          out_timestamp TIMESTAMP WITH TIME ZONE;
207          output_result type_int_timestamp;
208       BEGIN
209          SELECT INTO out_timestamp now();
210          UPDATE " + strUserName + ".BF_BLOG_ENTRY SET CAPTION = IN_CAPTION, 
211                 COMMENTS = IN_COMMENTS, IMAGEURL = IN_IMAGEURL, TARGETURL= IN_TARGETURL,
212                 MODIFICATION_DATE = out_timestamp 
213                 WHERE ID = IN_ENTRY_ID AND DOMAIN_ID = IN_DOMAIN_ID
214                 AND MODIFICATION_DATE = IN_MODIFICATION_DATE;
215          GET DIAGNOSTICS out_updated_count = ROW_COUNT;
216          output_result.intgr := out_updated_count;
217          output_result.tmstp := out_timestamp;
218 
219          RETURN output_result;
220       END;
221       ' LANGUAGE 'plpgsql';
222    */
223 
224    // Constants ////////////////////////////////////////////////////////////////
225 
226    /**
227     * Maximal length of blog comments.
228     * Restriction for VARCHAR type is max. 32768 characters for Postgre SQL database.
229     */
230    public static final int BLOG_COMMENTS_MAXLENGTH_POSTGRES = 32768;
231 
232    /**
233     * Maximal length of blog entry comments.
234     * Restriction for VARCHAR type is max. 32768 characters for Postgre SQL database.
235     */
236    public static final int BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES = 32768;
237 
238    // Cached values ////////////////////////////////////////////////////////////
239 
240    /**
241     * Logger for this class
242     */
243    private static Logger   s_logger = Log.getInstance(PostgreSQLBlogDatabaseSchema.class);
244 
245    // Constructors /////////////////////////////////////////////////////////////
246 
247    /**
248     * Static initializer
249     */
250    static
251    {
252       // Setup maximal length of individual fields for Postgre SQL database
253       Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH_POSTGRES);
254       Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES);
255    }
256 
257    /**
258     * Default constructor.
259     * 
260     * @throws OSSException - error occured.
261     */
262    public PostgreSQLBlogDatabaseSchema(
263    ) throws OSSException
264    {
265       super();
266    }   
267 
268    // Public methods ///////////////////////////////////////////////////////////
269 
270    /**
271     * {@inheritDoc}
272     */
273    public void create(
274       Connection   cntDBConnection,
275       String       strUserName
276    ) throws SQLException  
277    {
278       Statement   stmQuery = null;
279       try
280       {        
281          stmQuery = cntDBConnection.createStatement();
282 
283          if (stmQuery.execute(
284             "create table BF_BLOG" + NL +
285             "(" + NL + 
286             "   ID SERIAL," + NL +
287             "   DOMAIN_ID INTEGER NOT NULL," + NL +
288             "   FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + ") NOT NULL," + NL +
289             "   CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
290             "   COMMENTS VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_POSTGRES + ") NOT NULL," + NL +
291             "   CREATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL," + NL +
292             "   MODIFICATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL," + NL +
293             "   CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID)," + NL +
294             // "   CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
295             // "      REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE," + NL +
296             "   CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)" + NL +
297             ")"))
298          {
299             // Close any results
300             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
301          }
302          s_logger.log(Level.FINEST, "Table BF_BLOG created.");
303 
304          ///////////////////////////////////////////////////////////////////////
305 
306          if (stmQuery.execute(
307             "CREATE OR REPLACE FUNCTION INSERT_BF_BLOG " + NL +
308             "( " + NL +
309             "   INTEGER, " + NL +
310             "   VARCHAR(" + BLOG_FOLDER_MAXLENGTH + "), " + NL +
311             "   VARCHAR(" + BLOG_CAPTION_MAXLENGTH + "), " + NL +
312             "   VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_POSTGRES + ") " + NL +
313             ") RETURNS type_int_timestamp AS ' " + NL +
314             "DECLARE " + NL +
315             "   IN_DOMAIN_ID ALIAS FOR $1; " + NL +
316             "   IN_FOLDER ALIAS FOR $2; " + NL +
317             "   IN_CAPTION ALIAS FOR $3; " + NL +
318             "   IN_COMMENTS ALIAS FOR $4; " + NL +
319             "   out_key INTEGER; " + NL +
320             "   out_timestamp TIMESTAMP WITH TIME ZONE; " + NL +
321             "   output_result type_int_timestamp; " + NL +
322             "BEGIN " + NL +
323             "   SELECT INTO out_timestamp now(); " + NL +
324             "   INSERT INTO " + strUserName + ".BF_BLOG(DOMAIN_ID, FOLDER,  " + NL +
325             "      CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE) " + NL +
326             "      VALUES (IN_DOMAIN_ID, IN_FOLDER, IN_CAPTION,  " + NL +
327             "      IN_COMMENTS, out_timestamp, out_timestamp); " + NL +
328             "   out_key := CURRVAL(''bf_blog_id_seq''); " + NL +
329             "   output_result.intgr := out_key; " + NL +
330             "   output_result.tmstp := out_timestamp; " + NL +
331             "   RETURN output_result; " + NL +
332             "END " + NL +
333             "' LANGUAGE 'plpgsql';"))
334          {
335             // Close any results
336             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
337          }
338          s_logger.log(Level.FINEST, "Procedure INSERT_BF_BLOG created.");
339 
340          ///////////////////////////////////////////////////////////////////////
341 
342          if (stmQuery.execute(
343             "CREATE OR REPLACE FUNCTION UPDATE_BF_BLOG " + NL +
344             "( " + NL +
345             "   VARCHAR(" + BLOG_FOLDER_MAXLENGTH + "), " + NL +
346             "   VARCHAR(" + BLOG_CAPTION_MAXLENGTH + "), " + NL +
347             "   VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_POSTGRES + "), " + NL +
348             "   INTEGER, " + NL +
349             "   INTEGER, " + NL +
350             "   TIMESTAMP WITH TIME ZONE " + NL +
351             ") RETURNS type_int_timestamp AS ' " + NL +
352             "DECLARE " + NL +
353             "   IN_FOLDER ALIAS FOR $1; " + NL +
354             "   IN_CAPTION ALIAS FOR $2; " + NL +
355             "   IN_COMMENTS ALIAS FOR $3; " + NL +
356             "   IN_BLOG_ID ALIAS FOR $4; " + NL +
357             "   IN_DOMAIN_ID ALIAS FOR $5; " + NL +
358             "   IN_MODIFICATION_DATE ALIAS FOR $6; " + NL +
359             "   out_updated_count INTEGER; " + NL +
360             "   out_timestamp TIMESTAMP WITH TIME ZONE; " + NL +
361             "   output_result type_int_timestamp; " + NL +
362             "BEGIN " + NL +
363             "   SELECT INTO out_timestamp now(); " + NL +
364             "   UPDATE " + strUserName + ".BF_BLOG SET FOLDER = IN_FOLDER, " + NL +
365             "          CAPTION = IN_CAPTION, " + NL +
366             "          COMMENTS = IN_COMMENTS, MODIFICATION_DATE = out_timestamp " + NL + 
367             "          WHERE ID = IN_BLOG_ID AND DOMAIN_ID = IN_DOMAIN_ID " + NL +
368             "          AND MODIFICATION_DATE = IN_MODIFICATION_DATE; " + NL +
369             "   GET DIAGNOSTICS out_updated_count = ROW_COUNT; " + NL +
370             "   output_result.intgr := out_updated_count; " + NL +
371             "   output_result.tmstp := out_timestamp; " + NL +
372             "   RETURN output_result; " + NL +
373             "END; " + NL +
374             "' LANGUAGE 'plpgsql';"))
375          {
376             // Close any results
377             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
378          }
379          s_logger.log(Level.FINEST, "Procedure UPDATE_BF_BLOG created.");
380 
381          ///////////////////////////////////////////////////////////////////////
382          
383          if (stmQuery.execute(
384             "create table BF_BLOG_ENTRY" + NL +
385             "(" + NL +
386             "  ID SERIAL," + NL +
387             "  DOMAIN_ID INTEGER NOT NULL," + NL +
388             "  BLOG_ID INTEGER NOT NULL," + NL +
389             "  CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
390             "  COMMENTS VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES + ") NOT NULL," + NL +
391             "  IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ") NOT NULL," + NL +
392             "  TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ") NOT NULL," + NL +
393             "  CREATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL," + NL +
394             "  MODIFICATION_DATE TIMESTAMP WITH TIME ZONE NOT NULL," + NL +
395             "  CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID)," + NL +
396             // "  CONSTRAINT BF_BLOGENTRDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
397             // "     REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE," + NL +
398             "  CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)" + NL +
399             "     REFERENCES BF_BLOG (ID) ON DELETE CASCADE" + NL +
400             ")"))
401          {
402             // Close any results
403             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
404          }
405          s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
406          
407          ///////////////////////////////////////////////////////////////////////
408 
409          if (stmQuery.execute(
410             "CREATE OR REPLACE FUNCTION INSERT_BF_BLOGENTR" + NL +
411             "( " + NL +
412             "   INTEGER," + NL +
413             "   INTEGER," + NL +
414             "   VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ")," + NL +
415             "   VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES + ")," + NL +
416             "   VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ")," + NL +
417             "   VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ")" + NL +
418             ") RETURNS type_int_timestamp AS '" + NL +
419             "DECLARE" + NL +
420             "   IN_DOMAIN_ID ALIAS FOR $1;" + NL +
421             "   IN_BLOG_ID ALIAS FOR $2;" + NL +
422             "   IN_CAPTION ALIAS FOR $3;" + NL +
423             "   IN_COMMENTS ALIAS FOR $4;" + NL +
424             "   IN_IMAGEURL ALIAS FOR $5;" + NL +
425             "   IN_TARGETURL ALIAS FOR $6;" + NL +
426             "   out_key INTEGER;" + NL +
427             "   out_timestamp TIMESTAMP WITH TIME ZONE;" + NL +
428             "   output_result type_int_timestamp;" + NL +
429             "BEGIN" + NL +
430             "   SELECT INTO out_timestamp now();" + NL +
431             "   INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID, " + NL +
432             "      CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, " + NL +
433             "      MODIFICATION_DATE) VALUES (IN_DOMAIN_ID, IN_BLOG_ID, IN_CAPTION, " + NL +
434             "      IN_COMMENTS, IN_IMAGEURL, IN_TARGETURL, out_timestamp, out_timestamp);" + NL +
435             "   out_key := CURRVAL(''bf_blog_entry_id_seq'');" + NL +
436             "   output_result.intgr := out_key;" + NL +
437             "   output_result.tmstp := out_timestamp;" + NL +
438             "   RETURN output_result;" + NL +
439             "END" + NL +
440             "' LANGUAGE 'plpgsql';"))
441          {
442             // Close any results
443             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
444          }
445          s_logger.log(Level.FINEST, "Table INSERT_BF_BLOGENTR created.");
446          
447          ///////////////////////////////////////////////////////////////////////
448 
449          if (stmQuery.execute(
450             "CREATE OR REPLACE FUNCTION UPDATE_BF_BLOGENTR" + NL +
451             "( " + NL +
452             "   VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ")," + NL +
453             "   VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_POSTGRES + ")," + NL +
454             "   VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ")," + NL +
455             "   VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ")," + NL +
456             "   INTEGER, " + NL +
457             "   INTEGER, " + NL +
458             "   TIMESTAMP WITH TIME ZONE " + NL +
459             ") RETURNS type_int_timestamp AS ' " + NL +
460             "DECLARE " + NL +
461             "   IN_CAPTION ALIAS FOR $1; " + NL +
462             "   IN_COMMENTS ALIAS FOR $2; " + NL +
463             "   IN_IMAGEURL ALIAS FOR $3; " + NL +
464             "   IN_TARGETURL ALIAS FOR $4; " + NL +
465             "   IN_ENTRY_ID ALIAS FOR $5; " + NL +
466             "   IN_DOMAIN_ID ALIAS FOR $6; " + NL +
467             "   IN_MODIFICATION_DATE ALIAS FOR $7; " + NL +
468             "   out_updated_count INTEGER; " + NL +
469             "   out_timestamp TIMESTAMP WITH TIME ZONE; " + NL +
470             "   output_result type_int_timestamp; " + NL +
471             "BEGIN " + NL +
472             "   SELECT INTO out_timestamp now(); " + NL +
473             "   UPDATE " + strUserName + ".BF_BLOG_ENTRY SET CAPTION = IN_CAPTION, " + NL + 
474             "          COMMENTS = IN_COMMENTS, IMAGEURL = IN_IMAGEURL, " +
475             "          TARGETURL= IN_TARGETURL, MODIFICATION_DATE = out_timestamp " + NL +
476             "          WHERE ID = IN_ENTRY_ID AND DOMAIN_ID = IN_DOMAIN_ID " + NL +
477             "          AND MODIFICATION_DATE = IN_MODIFICATION_DATE; " + NL +
478             "   GET DIAGNOSTICS out_updated_count = ROW_COUNT; " + NL +
479             "   output_result.intgr := out_updated_count; " + NL +
480             "   output_result.tmstp := out_timestamp; " + NL +
481             "   RETURN output_result; " + NL +
482             "END; " + NL +
483             "' LANGUAGE 'plpgsql';"))
484          {
485             // Close any results
486             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
487          }
488          s_logger.log(Level.FINEST, "Table UPDATE_BF_BLOGENTR created.");
489       }
490       catch (SQLException   sqleExc)
491       {
492          s_logger.log(Level.WARNING, 
493                       "Failed to create schema " + BLOG_SCHEMA_NAME, sqleExc);
494          throw sqleExc;
495       }
496       finally
497       {
498          DatabaseUtils.closeStatement(stmQuery);
499       }
500    }
501 
502    /**
503     * {@inheritDoc}
504     */
505    public String   getInsertBlogAndFetchGeneratedValues(
506    ) throws OSSException
507    {
508       return  "select INTGR, TMSTP from INSERT_BF_BLOG (?, ?, ?, ?)";
509    }
510 
511    /**
512     * {@inheritDoc}
513     */
514    public String   getUpdateBlogAndFetchGeneratedValues(
515    ) throws OSSException
516    {
517       return  "select INTGR, TMSTP from UPDATE_BF_BLOG (?, ?, ?, ?, ?, ?)";
518    }
519 
520    /**
521     * {@inheritDoc}
522     */
523    public String   getInsertEntryAndFetchGeneratedValues(
524    ) throws OSSException
525    {
526       return  "select INTGR, TMSTP from INSERT_BF_BLOGENTR (?, ?, ?, ?, ?, ?)";
527    }
528 
529    /**
530     * {@inheritDoc}
531     */
532    public String   getUpdateEntryAndFetchGeneratedValues(
533    ) throws OSSException
534    {
535       return  "select INTGR, TMSTP from UPDATE_BF_BLOGENTR (?, ?, ?, ?, ?, ?, ?)";
536    }
537 }
538
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags