BlogDatabaseSchema


1   /*
2    * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3    * 
4    * Project: OpenChronicle
5    * 
6    * $Id: BlogDatabaseSchema.java,v 1.19 2007/02/01 07:34:37 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;
23  
24  import java.sql.Connection  ;
25  import java.sql.SQLException  ;
26  import java.util.HashMap  ;
27  import java.util.Map  ;
28  
29  import org.opensubsystems.blog.data.Blog;
30  import org.opensubsystems.blog.data.Entry;
31  import org.opensubsystems.core.data.DataConstant;
32  import org.opensubsystems.core.error.OSSException;
33  import org.opensubsystems.core.error.OSSInvalidContextException;
34  import org.opensubsystems.core.error.OSSInvalidDataException;
35  import org.opensubsystems.core.persist.db.DatabaseImpl;
36  import org.opensubsystems.core.persist.db.ModifiableDatabaseSchemaImpl;
37  import org.opensubsystems.core.util.Messages;
38  
39  /**
40   * Database specific operations related to persistence of blogs and their entries.
41   * 
42   * @version $Id: BlogDatabaseSchema.java,v 1.19 2007/02/01 07:34:37 bastafidli Exp $
43   * @author Miro Halas
44   * @code.reviewer Miro Halas
45   * @code.reviewed 1.6 2006/07/13 23:43:28 jlegeny
46   */
47  public abstract class BlogDatabaseSchema extends ModifiableDatabaseSchemaImpl
48  {   
49     // Constants ////////////////////////////////////////////////////////////////
50  
51     /**
52      * Name identifies this schema in the database. 
53      */
54     public static final String   BLOG_SCHEMA_NAME = "BLOG";
55     
56     /**
57      * Version of this schema in the database.
58      */
59     public static final int BLOG_SCHEMA_VERSION = 1;
60  
61     // Blog related constants 
62     
63     /**
64      * Table name where blog informations are stored.
65      */
66     public static final String   BLOG_TABLE_NAME = "BF_BLOG";
67     
68     /**
69      * All columns in a blog table in the order in which they will be retrieved
70      */
71     public static final String   BLOG_COLUMNS = "ID, DOMAIN_ID, FOLDER, CAPTION," +
72                                               " COMMENTS, CREATION_DATE," +
73                                               " MODIFICATION_DATE"; 
74  
75     /**
76      * Maximal length of blog folder.
77      */
78     public static final int BLOG_FOLDER_MAXLENGTH = 50;
79  
80     /**
81      * Maximal length of blog caption.
82      */
83     public static final int BLOG_CAPTION_MAXLENGTH = 1024;
84  
85     /**
86      * Maximal length of blog comments.
87      */
88     public static final int BLOG_COMMENTS_MAXLENGTH = 4000;
89     
90     // Blog entry related constants
91     
92     /**
93      * Table name where blog entry informations are stored.
94      */
95     public static final String   BLOGENTRY_TABLE_NAME = "BF_BLOG_ENTRY";
96  
97     /**
98      * All columns in a entry table in the order in which they will be retrieved
99      */
100    public static final String   ENTRY_COLUMNS = "ID, DOMAIN_ID, BLOG_ID, CAPTION," +
101                                               " COMMENTS, IMAGEURL, TARGETURL," +
102                                               " CREATION_DATE, MODIFICATION_DATE"; 
103    /**
104     * Maximal length of entry caption.
105     */
106    public static final int BLOGENTRY_CAPTION_MAXLENGTH = 1024;
107 
108    /**
109     * Maximal length of entry comments.
110     */
111    public static final int BLOGENTRY_COMMENTS_MAXLENGTH = 4000;
112    
113    /**
114     * Maximal length of entry image URL.
115     */
116    public static final int BLOGENTRY_IMAGEURL_MAXLENGTH = 1024;
117 
118    /**
119     * Maximal length of entry target URL.
120     */
121    public static final int BLOGENTRY_TARGETURL_MAXLENGTH = 1024;
122 
123    // Cached values ////////////////////////////////////////////////////////////
124    
125    /**
126     * Map of table names belonging to this schema.
127     */
128    public static final Map   TABLE_NAMES;
129 
130    // Constructors /////////////////////////////////////////////////////////////
131    
132    /**
133     * Static initializer
134     */
135    static
136    {
137       // Create map that stores table names. The key is object data type
138       // and value is table name
139       TABLE_NAMES = new HashMap  ();
140       TABLE_NAMES.put(DataConstant.BLOG_DATA_TYPE_OBJ, "BF_BLOG");
141       TABLE_NAMES.put(DataConstant.BLOGENTRY_DATA_TYPE_OBJ, "BF_BLOG_ENTRY");
142       
143       // Setup maximal length of individual fields for entities
144       Blog.setFolderMaxLength(BLOG_FOLDER_MAXLENGTH);
145       Blog.setCaptionMaxLength(BLOG_CAPTION_MAXLENGTH);
146       Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH);
147       Entry.setCaptionMaxLength(BLOGENTRY_CAPTION_MAXLENGTH);
148       Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH);
149       Entry.setImageURLMaxLength(BLOGENTRY_IMAGEURL_MAXLENGTH);
150       Entry.setTargetURLMaxLength(BLOGENTRY_TARGETURL_MAXLENGTH);
151    }
152 
153    /**
154     * Default constructor.
155     * 
156     * @throws OSSException - error occured.
157     */
158    public BlogDatabaseSchema(
159    ) throws OSSException
160    {
161       super(null, BLOG_SCHEMA_NAME, BLOG_SCHEMA_VERSION, true, TABLE_NAMES);
162    }   
163 
164    // Public methods ///////////////////////////////////////////////////////////
165    
166    /**
167     * Get query to load blog by id 
168     * 
169     * @param strColumns - list of specific columns to retrieve
170     * @return String - query
171     * @throws OSSException - an error has occured
172     */
173    public String   getSelectBlogById(
174       String   strColumns
175    ) throws OSSException
176    {
177       StringBuffer   buffer = new StringBuffer  ();
178       
179       buffer.append("select ");
180       buffer.append(strColumns);
181       buffer.append(" from BF_BLOG where ID = ? and DOMAIN_ID = ?");
182       
183       return buffer.toString();   
184    }
185   
186    /**
187     * Get query to load blog by folder 
188     * 
189     * @param strColumns - list of specific columns to retrieve
190     * @return String - query
191     * @throws OSSException - an error has occured
192     */
193    public String   getSelectBlogByFolder(
194       String   strColumns
195    ) throws OSSException
196    {
197       StringBuffer   buffer = new StringBuffer  (); 
198       
199       buffer.append("select ");
200       buffer.append(strColumns);
201       buffer.append(" from BF_BLOG where FOLDER = ? and DOMAIN_ID = ?");
202 
203       return buffer.toString();
204    }
205       
206    /**
207     * Get query to load all blogs sorted by creation date
208     * 
209     * @param strColumns - list of specific columns to retrieve
210     * @return String - query
211     * @throws OSSException - an error has occured
212     */   
213    public String   getSelectAllBlogs(
214       String   strColumns
215    ) throws OSSException
216    {
217       StringBuffer   buffer = new StringBuffer  ();
218       
219       buffer.append("select ");
220       buffer.append(strColumns);
221       buffer.append(" from BF_BLOG where DOMAIN_ID = ? order by CREATION_DATE");
222 
223       return buffer.toString();   
224    }
225 
226    /**
227     * Get query that inserts a blog to the database and fetches database 
228     * generated values such as the generated id and creation timestamp 
229     * 
230     * @return String - query for simple insert or stored procedure call
231     * @throws OSSException - an error has occured
232     */
233    public abstract String   getInsertBlogAndFetchGeneratedValues(
234    ) throws OSSException;
235    
236    /**
237     * Get query that updates blog in the database and fetches database 
238     * generated values such as the updated modification timestamp 
239     * 
240     * @return String - query for simple update or stored procedure call
241     * @throws OSSException - an error has occured
242     */
243    public abstract String   getUpdateBlogAndFetchGeneratedValues(
244    ) throws OSSException;
245    
246    /**
247     * Method returns simple insert blog query. This method is common for all
248     * databases and can be overwritten for each specific database schema.
249     *
250     * @return String - simple insert blog query
251     * @throws OSSException - an error has occured
252     */
253    public String   getInsertBlog(
254    ) throws OSSException
255    {
256       StringBuffer   buffer = new StringBuffer  ();
257       
258       // No ID column is specified here by default since it is generated by the
259       // database and therefore we do not know value for it
260       buffer.append("insert into BF_BLOG(DOMAIN_ID, FOLDER, CAPTION, COMMENTS," +
261                     " CREATION_DATE, MODIFICATION_DATE)" +
262                     " values (?, ?, ?, ?, ");
263       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
264       buffer.append(",");
265       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
266       buffer.append(")");
267 
268       return buffer.toString();
269    }
270    
271    /**
272     * Get query to delete blog by id
273     * 
274     * @return String - query
275     */
276    public String   getDeleteBlogById(
277    )
278    {
279       return "delete from BF_BLOG where ID = ? and DOMAIN_ID = ?";
280    }
281      
282    /**
283     * Get query to load entry by id 
284     * 
285     * @param strColumns - list of specific columns to retrieve
286     * @return String - query
287     * @throws OSSException - an error has occured
288     */
289    public String   getSelectEntryById(
290       String   strColumns
291    ) throws OSSException
292    {
293       StringBuffer   buffer = new StringBuffer  ();
294       
295       buffer.append("select ");
296       buffer.append(strColumns);
297       buffer.append(" from BF_BLOG_ENTRY where ID = ? and DOMAIN_ID = ?");
298       
299       return buffer.toString();   
300    }
301   
302    /**
303     * Get query to load all entries sorted by date from newest to oldest.
304     * 
305     * @param strColumns - list of specific columns to retrieve
306     * @return String - query
307     * @throws OSSException - an error has occured
308     */   
309    public String   getSelectAllEntries(
310       String   strColumns
311    ) throws OSSException
312    {
313       StringBuffer   buffer = new StringBuffer  ();
314       
315       buffer.append("select ");
316       buffer.append(strColumns);
317       buffer.append(" from BF_BLOG_ENTRY where BLOG_ID = ? and DOMAIN_ID = ?" +
318                     " order by CREATION_DATE desc");
319 
320       return buffer.toString();   
321       
322    }
323    
324    /**
325     * Get query to load last created entry
326     * 
327     * @param strColumns - list of specific columns to retrieve
328     * @return String - query
329     * @throws OSSException - an error has occured
330     */   
331    public String   getSelectLastEntry(
332       String   strColumns
333    ) throws OSSException
334    {
335       StringBuffer   buffer = new StringBuffer  ();
336       
337       buffer.append("select ");
338       buffer.append(strColumns);
339       // Last entry has to have the highest id
340       buffer.append(" from BF_BLOG_ENTRY where ID = (select max(ID)" +
341                     " from BF_BLOG_ENTRY where BLOG_ID = ? and DOMAIN_ID = ?)");
342 
343       return buffer.toString();   
344       
345    }
346 
347    /**
348     * Get query that inserts a entry to the database and fetches database 
349     * generated values such as the generated id and creation timestamp 
350     * 
351     * @return String - query for simple insert or stored procedure call
352     * @throws OSSException - an error has occured
353     */
354    public abstract String   getInsertEntryAndFetchGeneratedValues(
355    ) throws OSSException;
356    
357    /**
358     * Get query that updates entry in the database and fetches database 
359     * generated values such as the updated modification timestamp 
360     * 
361     * @return String - query for simple update or stored procedure call
362     * @throws OSSException - an error has occured
363     */
364    public abstract String   getUpdateEntryAndFetchGeneratedValues(
365    ) throws OSSException;
366    
367    /**
368     * Method returns simple insert entry query. This method is common for all
369     * databases and can be overwritten for each specific database schema.
370     *
371     * @return String - simple insert entry query
372     * @throws OSSException - an error has occured
373     */
374    public String   getInsertEntry(
375    ) throws OSSException
376    {
377       StringBuffer   buffer = new StringBuffer  ();
378       
379       // No ID column is specified here by default since it is generated by the
380       // database and therefore we do not know value for it
381       buffer.append("insert into BF_BLOG_ENTRY(DOMAIN_ID, BLOG_ID, CAPTION," +
382                     " COMMENTS, IMAGEURL, TARGETURL, CREATION_DATE, MODIFICATION_DATE)" +
383                     " values (?, ?, ?, ?, ?, ?, ");
384       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
385       buffer.append(",");
386       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
387       buffer.append(")");
388 
389       return buffer.toString();
390    }
391    
392    /**
393     * Get query to delete entry by id
394     * 
395     * @return String - query
396     */
397    public String   getDeleteEntryById(
398    )
399    {
400       return "delete from BF_BLOG_ENTRY where ID = ? and DOMAIN_ID = ?";
401    }
402 
403    /**
404     * {@inheritDoc}
405     */
406    public void handleSQLException(
407       SQLException   exc, 
408       Connection     dbConnection, 
409       int          iOperationType, 
410       int          iDataType,
411       Object         data
412    ) throws OSSException
413    {
414       OSSInvalidDataException ideException = null;
415       
416       switch(iOperationType)
417       {
418          case DBOP_INSERT:
419          {
420             if (iDataType == DataConstant.BLOG_DATA_TYPE)
421             {
422                if ((exc.getMessage().toUpperCase().indexOf("BF_BLOG_FLDR_UQ") > -1)
423                     // MySQL handles blog folder unique constraint exception as 'KEY 2'
424                     || ((exc.getMessage().toUpperCase()).endsWith("KEY 2"))
425                     // IBM DB2 handles blog folder unique constraint exception as "2"
426                     || ((exc.getMessage().toUpperCase()).indexOf("\"2\"") > -1)
427                    )
428                {
429                   ideException = OSSInvalidDataException.addException(ideException,
430                                     Messages.NONSPECIFIC_ERRORS,
431                                     "Folder has to be unique.",
432                                     exc);
433                }
434             }
435             else if (iDataType == DataConstant.BLOGENTRY_DATA_TYPE)
436             {
437                if (exc.getMessage().toUpperCase().indexOf("BF_BLOGENTR_FK") > -1)
438                {
439                   throw new OSSInvalidContextException(
440                         "Blog to create entry in does not exist.", 
441                         exc);
442                }
443             }
444             break;
445          }
446          case DBOP_UPDATE:
447          {
448             if (iDataType == DataConstant.BLOG_DATA_TYPE)
449             {
450                if ((exc.getMessage().toUpperCase().indexOf("BF_BLOG_FLDR_UQ") > -1)
451                     // MySQL handles blog folder unique constraint exception as 'KEY 2'
452                     || ((exc.getMessage().toUpperCase()).endsWith("KEY 2"))
453                     // IBM DB2 handles blog folder unique constraint exception as "2"
454                     || ((exc.getMessage().toUpperCase()).indexOf("\"2\"") > -1)
455                    )
456                {
457                   ideException = OSSInvalidDataException.addException(ideException,
458                                     Messages.NONSPECIFIC_ERRORS,
459                                     "Folder has to be unique.",
460                                     exc);
461                }
462             }
463             else if (iDataType == DataConstant.BLOGENTRY_DATA_TYPE)
464             {
465                // No special error checking
466             }
467             break;
468          }
469          default:
470          {
471             // Some operations do not need any specific handling
472          }
473       }
474       if (ideException != null)
475       {
476          // If a specific exception was created throw it back to client
477          throw ideException;
478       }
479       else
480       {
481          // No special handling was needed so execute the default exception handling
482          super.handleSQLException(exc, dbConnection, iOperationType, iDataType, data);
483       }   
484    }
485 }
486
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags