SybaseBlogDatabaseSchema


1   /*
2    * Copyright (c) 2006 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenChronicle
5    * 
6    * $Id: SybaseBlogDatabaseSchema.java,v 1.7 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.sybase;
23  
24  import java.sql.Connection  ;
25  import java.sql.PreparedStatement  ;
26  import java.sql.SQLException  ;
27  import java.sql.Statement  ;
28  import java.util.logging.Level  ;
29  import java.util.logging.Logger  ;
30  
31  import org.opensubsystems.blog.data.Blog;
32  import org.opensubsystems.blog.data.Entry;
33  import org.opensubsystems.blog.persist.db.BlogDatabaseSchema;
34  import org.opensubsystems.core.data.DataConstant;
35  import org.opensubsystems.core.error.OSSException;
36  import org.opensubsystems.core.util.CallContext;
37  import org.opensubsystems.core.util.DatabaseUtils;
38  import org.opensubsystems.core.util.Log;
39  
40  /**
41   * Database specific operations related to persistence of blogs and their 
42   * entries. This class encapsulate functionality specific for MS SQL Server.
43   *
44   * @version $Id: SybaseBlogDatabaseSchema.java,v 1.7 2007/02/20 01:58:05 bastafidli Exp $
45   * @author Julian Legeny
46   * @code.reviewer Miro Halas
47   * @code.reviewed 1.3 2006/07/21 00:54:56 jlegeny
48   */
49  public class SybaseBlogDatabaseSchema extends BlogDatabaseSchema
50  {   
51     /*
52        Use autogenerated numbers for IDs using sequence
53        Name all constraints to easily identify them later.
54        ON DELETE CASCADE must be provided by trigger (but there can by just 1 
55        delete trigger for the table). There will be not used triggers and all
56        data will be deleted manually.
57  
58        CREATE TABLE BF_BLOG
59        (
60           ID NUMERIC(10, 0) IDENTITY NOT NULL,
61           DOMAIN_ID NUMERIC(10, 0) NOT NULL,
62           FOLDER VARCHAR(50) NOT NULL,
63           CAPTION VARCHAR(1024) NOT NULL,
64           COMMENTS VARCHAR(8000) NOT NULL,
65           CREATION_DATE DATETIME NOT NULL,
66           MODIFICATION_DATE DATETIME NOT NULL,
67           CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
68           // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
69           //   REFERENCES BF_DOMAIN (ID),
70           CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
71        )
72  
73  
74        CREATE PROCEDURE INSERT_BF_BLOG
75           @IN_DOMAIN_ID NUMERIC(10, 0),
76           @IN_FOLDER VARCHAR(50),
77           @IN_CAPTION VARCHAR(1024),
78           @IN_COMMENTS VARCHAR(8000),
79           @OUT_KEY INTEGER OUTPUT,
80           @OUT_TIMESTAMP DATETIME OUTPUT
81        AS
82           SET @OUT_TIMESTAMP = GETDATE()
83           INSERT INTO " + strUserName + ".BF_BLOG(DOMAIN_ID, FOLDER, 
84              CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE)
85              VALUES (@IN_DOMAIN_ID, @IN_FOLDER, @IN_CAPTION, 
86              @IN_COMMENTS, @OUT_TIMESTAMP, @OUT_TIMESTAMP)
87           SET @OUT_KEY = @@IDENTITY
88        RETURN
89  
90        
91        CREATE PROCEDURE UPDATE_BF_BLOG
92           @IN_FOLDER VARCHAR(50),
93           @IN_CAPTION VARCHAR(1024),
94           @IN_COMMENTS VARCHAR(8000),
95           @IN_BLOG_ID NUMERIC(10, 0),
96           @IN_DOMAIN_ID NUMERIC(10, 0),
97           @IN_MODIFICATION_DATE DATETIME,
98           @OUT_TIMESTAMP DATETIME
99        AS
100          SET @OUT_TIMESTAMP = GETDATE()
101          UPDATE BF_BLOG SET FOLDER = @IN_FOLDER, CAPTION = @IN_CAPTION,
102                 COMMENTS = @IN_COMMENTS, MODIFICATION_DATE = @OUT_TIMESTAMP 
103                 WHERE ID = @IN_BLOG_ID AND DOMAIN_ID = @IN_DOMAIN_ID
104                 AND MODIFICATION_DATE = @IN_MODIFICATION_DATE
105       RETURN
106 
107 
108       CREATE TABLE BF_BLOG_ENTRY
109       (
110          ID NUMERIC(10, 0) IDENTITY NOT NULL,
111          DOMAIN_ID NUMERIC(10, 0) NOT NULL,
112          BLOG_ID NUMERIC(10, 0) NOT NULL,
113          CAPTION VARCHAR(1024) NOT NULL,
114          COMMENTS VARCHAR(8000) NOT NULL,
115          IMAGEURL VARCHAR(1024) NOT NULL,
116          TARGETURL VARCHAR(1024) NOT NULL,
117          CREATION_DATE DATETIME NOT NULL,
118          MODIFICATION_DATE DATETIME NOT NULL,
119          CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID),
120          // CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)
121          //   REFERENCES BF_DOMAIN (ID),
122          CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID) 
123             REFERENCES BF_BLOG (ID)
124       )
125 
126 
127       CREATE PROCEDURE INSERT_BF_BLOGENTR
128          @IN_DOMAIN_ID NUMERIC(10, 0),
129          @IN_BLOG_ID NUMERIC(10, 0),
130          @IN_CAPTION VARCHAR(1024),
131          @IN_COMMENTS VARCHAR(8000),
132          @IN_IMAGEURL VARCHAR(1024) NOT NULL,
133          @IN_TARGETURL VARCHAR(1024) NOT NULL,
134          @OUT_KEY INTEGER OUTPUT,
135          @OUT_TIMESTAMP DATETIME OUTPUT
136       AS
137          SET @OUT_TIMESTAMP = GETDATE()
138          INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID, 
139             CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)
140             VALUES (@IN_DOMAIN_ID, @IN_BLOG_ID, @IN_CAPTION, 
141             @IN_COMMENTS, @IN_IMAGEURL, @IN_TARGETURL, @OUT_TIMESTAMP, @OUT_TIMESTAMP)
142          SET @OUT_KEY = @@IDENTITY
143       RETURN
144 
145 
146       CREATE PROCEDURE UPDATE_BF_BLOGENTR
147          @IN_CAPTION VARCHAR(1024),
148          @IN_COMMENTS VARCHAR(8000),
149          @IN_IMAGEURL VARCHAR(1024) NOT NULL,
150          @IN_TARGETURL VARCHAR(1024) NOT NULL,
151          @IN_ENTRY_ID NUMERIC(10, 0),
152          @IN_DOMAIN_ID NUMERIC(10, 0),
153          @IN_MODIFICATION_DATE DATETIME,
154          @OUT_TIMESTAMP DATETIME OUTPUT
155       AS
156          SET @OUT_TIMESTAMP = GETDATE()
157          UPDATE BF_BLOG_ENTRY SET CAPTION = @IN_CAPTION, COMMENTS = @IN_COMMENTS,
158                 IMAGEURL = @IN_IMAGEURL, TARGETURL= @IN_TARGETURL,
159                 MODIFICATION_DATE = @OUT_TIMESTAMP 
160                 WHERE ID = @IN_ENTRY_ID AND DOMAIN_ID = @IN_DOMAIN_ID
161                 AND MODIFICATION_DATE = @IN_MODIFICATION_DATE
162       RETURN
163    */
164 
165    // Constants ////////////////////////////////////////////////////////////////
166 
167    /**
168     * Maximal length of blog comments.
169     * Restriction for VARCHAR type is max. 8000 characters for Sybase database.
170     */
171    public static final int BLOG_COMMENTS_MAXLENGTH_SYBASE = 8000;
172 
173    /**
174     * Maximal length of blog entry comments.
175     * Restriction for VARCHAR type is max. 8000 characters for Sybase database.
176     */
177    public static final int BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE = 8000;
178 
179    // Cached values ////////////////////////////////////////////////////////////
180 
181    /**
182     * Logger for this class
183     */
184    private static Logger   s_logger = Log.getInstance(SybaseBlogDatabaseSchema.class);
185 
186    // Constructors /////////////////////////////////////////////////////////////
187 
188    /**
189     * Static initializer
190     */
191    static
192    {
193       // Setup maximal length of individual fields for Sybase database.
194       Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH_SYBASE);
195       Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE);
196    }
197 
198    /**
199     * Default constructor.
200     * 
201     * @throws OSSException - error occured.
202     */
203    public SybaseBlogDatabaseSchema(
204    ) throws OSSException
205    {
206       super();
207    }   
208 
209    // Public methods ///////////////////////////////////////////////////////////
210 
211    /**
212     * {@inheritDoc}
213     */
214    public void create(
215       Connection   cntDBConnection,
216       String       strUserName
217    ) throws SQLException  
218    {
219       Statement   stmQuery = null;
220       try
221       {        
222          stmQuery = cntDBConnection.createStatement();
223 
224          if (stmQuery.execute(
225             "create table BF_BLOG" + NL +
226             "(" + NL + 
227             "   ID NUMERIC(10, 0) IDENTITY NOT NULL," + NL +
228             "   DOMAIN_ID NUMERIC(10, 0) NOT NULL," + NL +
229             "   FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + ") NOT NULL," + NL +
230             "   CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
231             "   COMMENTS VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_SYBASE + ") NOT NULL," + NL +
232             "   CREATION_DATE DATETIME NOT NULL," + NL +
233             "   MODIFICATION_DATE DATETIME NOT NULL," + NL +
234             "   CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID)," + NL +
235             // "   CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
236             // "      REFERENCES BF_DOMAIN (ID)," + NL +
237             "   CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)" + NL +
238             ")"))
239          {
240             // Close any results
241             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
242          }
243          s_logger.log(Level.FINEST, "Table BF_BLOG created.");
244 
245          ///////////////////////////////////////////////////////////////////////
246 
247          if (stmQuery.execute(
248             "CREATE PROCEDURE INSERT_BF_BLOG " + NL +
249             "   @IN_DOMAIN_ID NUMERIC(10, 0), " + NL +
250             "   @IN_FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + "), " + NL +
251             "   @IN_CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + "), " + NL +
252             "   @IN_COMMENTS VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_SYBASE + "), " + NL +
253             "   @OUT_KEY INTEGER OUTPUT, " + NL +
254             "   @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
255             "AS " + NL +
256             "   SET @OUT_TIMESTAMP = GETDATE() " + NL +
257             "   INSERT INTO " + strUserName + ".BF_BLOG(DOMAIN_ID, FOLDER,  " + NL +
258             "      CAPTION, COMMENTS, CREATION_DATE, MODIFICATION_DATE) " + NL +
259             "      VALUES (@IN_DOMAIN_ID, @IN_FOLDER, @IN_CAPTION,  " + NL +
260             "      @IN_COMMENTS, @OUT_TIMESTAMP, @OUT_TIMESTAMP) " + NL +
261             "   SET @OUT_KEY = @@IDENTITY" + NL +
262             "RETURN"))
263          {
264             // Close any results
265             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
266          }
267          s_logger.log(Level.FINEST, "Procedure INSERT_BF_BLOG created.");
268 
269          ///////////////////////////////////////////////////////////////////////
270 
271          if (stmQuery.execute(
272             "CREATE PROCEDURE UPDATE_BF_BLOG " + NL +
273             "   @IN_FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + "), " + NL +
274             "   @IN_CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + "), " + NL +
275             "   @IN_COMMENTS VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_SYBASE + "), " + NL +
276             "   @IN_BLOG_ID NUMERIC(10, 0), " + NL +
277             "   @IN_DOMAIN_ID NUMERIC(10, 0), " + NL +
278             "   @IN_MODIFICATION_DATE DATETIME, " + NL +
279             "   @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
280             "AS " + NL +
281             "   SET @OUT_TIMESTAMP = GETDATE()" + NL +
282             "   UPDATE " + strUserName + ".BF_BLOG SET FOLDER = @IN_FOLDER, " + NL +
283             "          CAPTION = @IN_CAPTION, COMMENTS = @IN_COMMENTS, " + NL +
284             "          MODIFICATION_DATE = @OUT_TIMESTAMP WHERE ID = @IN_BLOG_ID " + NL +
285             "          AND DOMAIN_ID = @IN_DOMAIN_ID " + NL +
286             "          AND MODIFICATION_DATE = @IN_MODIFICATION_DATE " + NL +
287             "RETURN"))
288          {
289             // Close any results
290             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
291          }
292          s_logger.log(Level.FINEST, "Procedure UPDATE_BF_BLOG created.");
293 
294          ///////////////////////////////////////////////////////////////////////
295          
296          if (stmQuery.execute(
297             "create table BF_BLOG_ENTRY" + NL +
298             "(" + NL +
299             "  ID NUMERIC(10, 0) IDENTITY NOT NULL," + NL +
300             "  DOMAIN_ID NUMERIC(10, 0) NOT NULL," + NL +
301             "  BLOG_ID NUMERIC(10, 0) NOT NULL," + NL +
302             "  CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
303             "  COMMENTS VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE + ") NOT NULL," + NL +
304             "  IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ") NOT NULL," + NL +
305             "  TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ") NOT NULL," + NL +
306             "  CREATION_DATE DATETIME NOT NULL," + NL +
307             "  MODIFICATION_DATE DATETIME NOT NULL," + NL +
308             "  CONSTRAINT BF_BLOGENTR_PK PRIMARY KEY (ID)," + NL +
309             // "  CONSTRAINT BF_BLOGDOM_FK FOREIGN KEY (DOMAIN_ID)" + NL +
310             // "     REFERENCES BF_DOMAIN (ID)," + NL +
311             "  CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)" + NL +
312             "     REFERENCES BF_BLOG (ID)" + NL +
313             ")"))
314          {
315             // Close any results
316             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
317          }
318          s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
319          
320          ///////////////////////////////////////////////////////////////////////
321 
322          if (stmQuery.execute(
323             "CREATE PROCEDURE INSERT_BF_BLOGENTR" + NL +
324             "   @IN_DOMAIN_ID NUMERIC(10, 0)," + NL +
325             "   @IN_BLOG_ID NUMERIC(10, 0)," + NL +
326             "   @IN_CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ")," + NL +
327             "   @IN_COMMENTS VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE + ")," + NL +
328             "   @IN_IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ")," + NL +
329             "   @IN_TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ")," + NL +
330             "   @OUT_KEY INTEGER OUTPUT," + NL +
331             "   @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
332             "AS" + NL +
333             "   SET @OUT_TIMESTAMP = GETDATE() " + NL +
334             "   INSERT INTO " + strUserName + ".BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID, " + NL +
335             "      CAPTION, COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)" + NL +
336             "      VALUES (@IN_DOMAIN_ID, @IN_BLOG_ID, @IN_CAPTION, @IN_COMMENTS, " + NL +
337             "      @IN_IMAGEURL, @IN_TARGETURL, @OUT_TIMESTAMP, @OUT_TIMESTAMP)" + NL +
338             "   SET @OUT_KEY = @@IDENTITY" + NL +
339          "RETURN"))
340          {
341             // Close any results
342             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
343          }
344          s_logger.log(Level.FINEST, "Table INSERT_BF_BLOGENTR created.");
345          
346          ///////////////////////////////////////////////////////////////////////
347 
348          if (stmQuery.execute(
349             "CREATE PROCEDURE UPDATE_BF_BLOGENTR" + NL +
350             "   @IN_CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ")," + NL +
351             "   @IN_COMMENTS VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_SYBASE + ")," + NL +
352             "   @IN_IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ")," + NL +
353             "   @IN_TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ")," + NL +
354             "   @IN_ENTRY_ID NUMERIC(10, 0)," + NL +
355             "   @IN_DOMAIN_ID NUMERIC(10, 0)," + NL +
356             "   @IN_MODIFICATION_DATE DATETIME," + NL +
357             "   @OUT_TIMESTAMP DATETIME OUTPUT " + NL +
358             "AS " + NL +
359             "   SET @OUT_TIMESTAMP = GETDATE()" + NL +
360             "   UPDATE  " + strUserName + ".BF_BLOG_ENTRY SET CAPTION = @IN_CAPTION," + NL +
361             "      COMMENTS = @IN_COMMENTS, IMAGEURL = @IN_IMAGEURL," + NL +
362             "      TARGETURL= @IN_TARGETURL, MODIFICATION_DATE = @OUT_TIMESTAMP " + NL +
363             "      WHERE ID = @IN_ENTRY_ID AND DOMAIN_ID = @IN_DOMAIN_ID " + NL +
364             "      AND MODIFICATION_DATE = @IN_MODIFICATION_DATE" + NL +
365             "RETURN"))
366          {
367             // Close any results
368             stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
369          }
370          s_logger.log(Level.FINEST, "Table UPDATE_BF_BLOGENTR created.");
371       }
372       catch (SQLException   sqleExc)
373       {
374          s_logger.log(Level.WARNING, 
375                       "Failed to create schema " + BLOG_SCHEMA_NAME, sqleExc);
376          throw sqleExc;
377       }
378       finally
379       {
380          DatabaseUtils.closeStatement(stmQuery);
381       }
382    }
383 
384    /**
385     * {@inheritDoc}
386     */
387    public String   getInsertBlogAndFetchGeneratedValues(
388    ) throws OSSException
389    {
390       return  "{call INSERT_BF_BLOG (?, ?, ?, ?, ?, ?)}";
391    }
392 
393    /**
394     * {@inheritDoc}
395     */
396    public String   getUpdateBlogAndFetchGeneratedValues(
397    ) throws OSSException
398    {
399       return  "{call UPDATE_BF_BLOG ?, ?, ?, ?, ?, ?, ?)}";
400    }
401 
402    /**
403     * {@inheritDoc}
404     */
405    public String   getInsertEntryAndFetchGeneratedValues(
406    ) throws OSSException
407    {
408       return  "{call INSERT_BF_BLOGENTR ?, ?, ?, ?, ?, ?, ?, ?)}";
409    }
410 
411    /**
412     * {@inheritDoc}
413     */
414    public String   getUpdateEntryAndFetchGeneratedValues(
415    ) throws OSSException
416    {
417       return  "{call UPDATE_BF_BLOGENTR ?, ?, ?, ?, ?, ?, ?, ?)}";
418    }
419 
420    /**
421     * {@inheritDoc}
422     */
423    public int deleteRelatedData(
424       Connection   dbConnection,
425       int iDataType,
426       int iId
427    ) throws OSSException, SQLException  
428    {
429       PreparedStatement   pstmDelete = null;
430       int iDeleted = 0;
431 
432       if (iDataType == DataConstant.BLOG_DATA_TYPE)
433       {
434          try
435          {
436             // execute query for delete related data
437             pstmDelete = dbConnection.prepareStatement(
438                             "delete from BF_BLOG_ENTRY where DOMAIN_ID = ? and BLOG_ID = ?");
439             pstmDelete.setInt(1, CallContext.getInstance().getCurrentDomainId());
440             pstmDelete.setInt(2, iId);
441             iDeleted = pstmDelete.executeUpdate();
442          }
443          finally
444          {
445             DatabaseUtils.closeStatement(pstmDelete);
446          }
447       }
448       
449       return iDeleted;
450    }
451 }
452
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags