KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > opensubsystems > blog > persist > db > hsqldb > HsqlDBBlogDatabaseSchema


1 /*
2  * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
3  *
4  * Project: OpenChronicle
5  *
6  * $Id: HsqlDBBlogDatabaseSchema.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.hsqldb;
23
24 import java.sql.Connection JavaDoc;
25 import java.sql.SQLException JavaDoc;
26 import java.sql.Statement JavaDoc;
27 import java.util.logging.Level JavaDoc;
28 import java.util.logging.Logger JavaDoc;
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 HSQLDB.
41  *
42  * @version $Id: HsqlDBBlogDatabaseSchema.java,v 1.9 2007/02/20 01:58:05 bastafidli Exp $
43  * @author Miro Halas
44  * @code.reviewer Miro Halas
45  * @code.reviewed 1.4 2006/07/13 23:44:26 jlegeny
46  */
47 public class HsqlDBBlogDatabaseSchema extends BlogDatabaseSchema
48 {
49    /*
50       Use cached tables since these tables can be large
51       Use autogenerated numbers for IDs using IDENTITY column.
52       Identity automatically defines primary key
53       Name all constraints to easily identify them later.
54       For all unique constraint we need to define unique indexes instead of
55       unique constrant otherwise we won't be able to indentify the violation of
56       this constraint by name.
57
58       CREATE CACHED TABLE BF_BLOG
59       (
60          ID INTEGER IDENTITY,
61          DOMAIN_ID INTEGER NOT NULL,
62          FOLDER VARCHAR(50) NOT NULL,
63          CAPTION VARCHAR(1024) NOT NULL,
64          COMMENTS VARCHAR(32768) NOT NULL,
65          CREATION_DATE TIMESTAMP NOT NULL,
66          MODIFICATION_DATE TIMESTAMP NOT NULL
67          // CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
68          // CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
69       );
70
71       create unique index BF_BLOG_FLDR_UQ on BF_BLOG(FOLDER);
72
73       CREATE CACHED TABLE BF_BLOG_ENTRY
74       (
75          ID INTEGER IDENTITY,
76          DOMAIN_ID INTEGER NOT NULL,
77          BLOG_ID INTEGER NOT NULL,
78          CAPTION VARCHAR(1024) NOT NULL,
79          COMMENTS VARCHAR(32768) NOT NULL,
80          IMAGEURL VARCHAR(1024) NOT NULL,
81          TARGETURL VARCHAR(1024) NOT NULL,
82          CREATION_DATE TIMESTAMP NOT NULL,
83          MODIFICATION_DATE TIMESTAMP NOT NULL,
84          // CONSTRAINT BF_WLE_PK PRIMARY KEY (ID),
85          CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)
86          REFERENCES BF_BLOG (ID) ON DELETE CASCADE
87       );
88    */
89    
90    // Constants ////////////////////////////////////////////////////////////////
91
92    /**
93     * Maximal length of blog comments.
94     */
95    public static final int BLOG_COMMENTS_MAXLENGTH_HSQLDB = 32768;
96
97    /**
98     * Maximal length of blog entry comments.
99     */
100    public static final int BLOGENTRY_COMMENTS_MAXLENGTH_HSQLDB = 32768;
101
102    // Cached values ////////////////////////////////////////////////////////////
103
104    /**
105     * Logger for this class
106     */
107    private static Logger JavaDoc s_logger = Log.getInstance(HsqlDBBlogDatabaseSchema.class);
108
109    // Constructors /////////////////////////////////////////////////////////////
110
111    /**
112     * Static initializer
113     */
114    static
115    {
116       // Setup maximal length of individual fields for HSQLDB database
117 Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH_HSQLDB);
118       Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH_HSQLDB);
119    }
120
121    /**
122     * Default constructor.
123     *
124     * @throws OSSException - error occured.
125     */
126    public HsqlDBBlogDatabaseSchema(
127    ) throws OSSException
128    {
129       super();
130    }
131
132    // Public methods ///////////////////////////////////////////////////////////
133
134    /**
135     * {@inheritDoc}
136     */
137    public void create(
138       Connection JavaDoc cntDBConnection,
139       String JavaDoc strUserName
140    ) throws SQLException JavaDoc
141    {
142       Statement JavaDoc stmQuery = null;
143       try
144       {
145          stmQuery = cntDBConnection.createStatement();
146          if (stmQuery.execute(
147             "create cached table BF_BLOG" + NL +
148             "(" + NL +
149             " ID INTEGER IDENTITY," + NL +
150             " DOMAIN_ID INTEGER NOT NULL," + NL +
151             " FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + ") NOT NULL," + NL +
152             " CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
153             " COMMENTS VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_HSQLDB + ") NOT NULL," + NL +
154             " CREATION_DATE TIMESTAMP NOT NULL," + NL +
155             " MODIFICATION_DATE TIMESTAMP NOT NULL" + NL +
156             // Identity automatically defines primary key
157 // "CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID)," +
158 // HSQL has a bug that unique constraint is generates
159 // unique index but with system generated name
160 // To be able to detect violation of this constraint
161 // we need to generate the index ourselves
162 // " CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)" +
163 ")"))
164          {
165             // Close any results
166 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
167          }
168          s_logger.log(Level.FINEST, "Table BF_BLOG created.");
169          if (stmQuery.execute("create unique index BF_BLOG_FLDR_UQ on BF_BLOG(FOLDER)"))
170          {
171             // Close any results
172 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
173          }
174          s_logger.log(Level.FINEST, "Index BF_BLOG_FLDR_UQ created.");
175          if (stmQuery.execute("grant all on BF_BLOG to " + strUserName))
176          {
177             // Close any results
178 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
179          }
180          s_logger.log(Level.FINEST,
181                       "Access for table BF_BLOG set for user " + strUserName);
182
183          ///////////////////////////////////////////////////////////////////////
184
185          if (stmQuery.execute(
186             "create cached table BF_BLOG_ENTRY" + NL +
187             "(" + NL +
188             " ID INTEGER IDENTITY," + NL +
189             " DOMAIN_ID INTEGER NOT NULL," + NL +
190             " BLOG_ID INTEGER NOT NULL," + NL +
191             " CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
192             " COMMENTS VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_HSQLDB + ") NOT NULL," + NL +
193             " IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ") NOT NULL," + NL +
194             " TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ") NOT NULL," + NL +
195             " CREATION_DATE TIMESTAMP NOT NULL," + NL +
196             " MODIFICATION_DATE TIMESTAMP NOT NULL," + NL +
197             // Identity automatically defines primary key
198 // " CONSTRAINT BF_WLE_PK PRIMARY KEY (ID)," +
199 " CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID) "
200             + "REFERENCES BF_BLOG (ID) ON DELETE CASCADE" + NL +
201             ")"))
202          {
203             // Close any results
204 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
205          }
206          s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
207          if (stmQuery.execute("grant all on BF_BLOG_ENTRY to " + strUserName))
208          {
209             // Close any results
210 stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
211          }
212          s_logger.log(Level.FINEST,
213                        "Access for table BF_BLOG_ENTRY set for user " + strUserName);
214       }
215       catch (SQLException JavaDoc sqleExc)
216       {
217          s_logger.log(Level.WARNING,
218                       "Failed to create schema " + BLOG_SCHEMA_NAME, sqleExc);
219          throw sqleExc;
220       }
221       finally
222       {
223          DatabaseUtils.closeStatement(stmQuery);
224       }
225    }
226
227    /**
228     * {@inheritDoc}
229     */
230    public String JavaDoc getInsertBlogAndFetchGeneratedValues(
231    ) throws OSSException
232    {
233       return getInsertBlog();
234    }
235    
236    /**
237     * {@inheritDoc}
238     */
239    public String JavaDoc getUpdateBlogAndFetchGeneratedValues(
240    ) throws OSSException
241    {
242       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
243       
244       buffer.append("update BF_BLOG set FOLDER = ?, CAPTION = ?, COMMENTS = ?," +
245                     " MODIFICATION_DATE = ");
246       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
247       buffer.append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
248
249       return buffer.toString();
250    }
251
252    /**
253     * {@inheritDoc}
254     */
255    public String JavaDoc getInsertBlog(
256    ) throws OSSException
257    {
258       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
259       
260       // HSQLDB requires for IDENTITY columns to specify ID and pass null as a value
261 buffer.append("INSERT INTO BF_BLOG(");
262       buffer.append(BLOG_COLUMNS);
263       buffer.append(") VALUES (null, ?, ?, ?, ?, ");
264       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
265       buffer.append(", ");
266       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
267       buffer.append(")");
268
269       return buffer.toString();
270    }
271
272    /**
273     * {@inheritDoc}
274     */
275    public String JavaDoc getInsertEntry(
276    ) throws OSSException
277    {
278       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
279       
280       // HSQLDB requires for IDENTITY columns to specify ID and pass null as a value
281 buffer.append("INSERT INTO BF_BLOG_ENTRY(");
282       buffer.append(ENTRY_COLUMNS);
283       buffer.append(") VALUES (null, ?, ?, ?, ?, ?, ?, ");
284       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
285       buffer.append(", ");
286       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
287       buffer.append(")");
288
289       return buffer.toString();
290    }
291
292    /**
293     * {@inheritDoc}
294     */
295    public String JavaDoc getInsertEntryAndFetchGeneratedValues(
296    ) throws OSSException
297    {
298       return getInsertEntry();
299    }
300    
301    /**
302     * {@inheritDoc}
303     */
304    public String JavaDoc getUpdateEntryAndFetchGeneratedValues(
305    ) throws OSSException
306    {
307       StringBuffer JavaDoc buffer = new StringBuffer JavaDoc();
308       
309       buffer.append("update BF_BLOG_ENTRY set CAPTION = ?, COMMENTS = ?," +
310                     " IMAGEURL = ?, TARGETURL = ?, MODIFICATION_DATE = ");
311       buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
312       buffer.append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
313
314       return buffer.toString();
315    }
316 }
317
Popular Tags