001: /*
002: * Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
003: *
004: * Project: OpenChronicle
005: *
006: * $Id: HsqlDBBlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:20 bastafidli Exp $
007: *
008: * This program is free software; you can redistribute it and/or modify
009: * it under the terms of the GNU General Public License as published by
010: * the Free Software Foundation; version 2 of the License.
011: *
012: * This program is distributed in the hope that it will be useful,
013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
015: * GNU General Public License for more details.
016: *
017: * You should have received a copy of the GNU General Public License
018: * along with this program; if not, write to the Free Software
019: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
020: */
021:
022: package org.opensubsystems.blog.persist.db.hsqldb;
023:
024: import java.sql.Connection;
025: import java.sql.SQLException;
026: import java.sql.Statement;
027: import java.util.logging.Level;
028: import java.util.logging.Logger;
029:
030: import org.opensubsystems.blog.data.Blog;
031: import org.opensubsystems.blog.data.Entry;
032: import org.opensubsystems.blog.persist.db.BlogDatabaseSchema;
033: import org.opensubsystems.core.error.OSSException;
034: import org.opensubsystems.core.persist.db.DatabaseImpl;
035: import org.opensubsystems.core.util.DatabaseUtils;
036: import org.opensubsystems.core.util.Log;
037:
038: /**
039: * Database specific operations related to persistence of blogs and their
040: * entries. This class encapsulate functionality specific for HSQLDB.
041: *
042: * @version $Id: HsqlDBBlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:20 bastafidli Exp $
043: * @author Miro Halas
044: * @code.reviewer Miro Halas
045: * @code.reviewed 1.4 2006/07/13 23:44:26 jlegeny
046: */
047: public class HsqlDBBlogDatabaseSchema extends BlogDatabaseSchema {
048: /*
049: Use cached tables since these tables can be large
050: Use autogenerated numbers for IDs using IDENTITY column.
051: Identity automatically defines primary key
052: Name all constraints to easily identify them later.
053: For all unique constraint we need to define unique indexes instead of
054: unique constrant otherwise we won't be able to indentify the violation of
055: this constraint by name.
056:
057: CREATE CACHED TABLE BF_BLOG
058: (
059: ID INTEGER IDENTITY,
060: DOMAIN_ID INTEGER NOT NULL,
061: FOLDER VARCHAR(50) NOT NULL,
062: CAPTION VARCHAR(1024) NOT NULL,
063: COMMENTS VARCHAR(32768) NOT NULL,
064: CREATION_DATE TIMESTAMP NOT NULL,
065: MODIFICATION_DATE TIMESTAMP NOT NULL
066: // CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
067: // CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
068: );
069:
070: create unique index BF_BLOG_FLDR_UQ on BF_BLOG(FOLDER);
071:
072: CREATE CACHED TABLE BF_BLOG_ENTRY
073: (
074: ID INTEGER IDENTITY,
075: DOMAIN_ID INTEGER NOT NULL,
076: BLOG_ID INTEGER NOT NULL,
077: CAPTION VARCHAR(1024) NOT NULL,
078: COMMENTS VARCHAR(32768) NOT NULL,
079: IMAGEURL VARCHAR(1024) NOT NULL,
080: TARGETURL VARCHAR(1024) NOT NULL,
081: CREATION_DATE TIMESTAMP NOT NULL,
082: MODIFICATION_DATE TIMESTAMP NOT NULL,
083: // CONSTRAINT BF_WLE_PK PRIMARY KEY (ID),
084: CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)
085: REFERENCES BF_BLOG (ID) ON DELETE CASCADE
086: );
087: */
088:
089: // Constants ////////////////////////////////////////////////////////////////
090: /**
091: * Maximal length of blog comments.
092: */
093: public static final int BLOG_COMMENTS_MAXLENGTH_HSQLDB = 32768;
094:
095: /**
096: * Maximal length of blog entry comments.
097: */
098: public static final int BLOGENTRY_COMMENTS_MAXLENGTH_HSQLDB = 32768;
099:
100: // Cached values ////////////////////////////////////////////////////////////
101:
102: /**
103: * Logger for this class
104: */
105: private static Logger s_logger = Log
106: .getInstance(HsqlDBBlogDatabaseSchema.class);
107:
108: // Constructors /////////////////////////////////////////////////////////////
109:
110: /**
111: * Static initializer
112: */
113: static {
114: // Setup maximal length of individual fields for HSQLDB database
115: Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH_HSQLDB);
116: Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH_HSQLDB);
117: }
118:
119: /**
120: * Default constructor.
121: *
122: * @throws OSSException - error occured.
123: */
124: public HsqlDBBlogDatabaseSchema() throws OSSException {
125: super ();
126: }
127:
128: // Public methods ///////////////////////////////////////////////////////////
129:
130: /**
131: * {@inheritDoc}
132: */
133: public void create(Connection cntDBConnection, String strUserName)
134: throws SQLException {
135: Statement stmQuery = null;
136: try {
137: stmQuery = cntDBConnection.createStatement();
138: if (stmQuery.execute("create cached table BF_BLOG" + NL
139: + "(" + NL + " ID INTEGER IDENTITY," + NL
140: + " DOMAIN_ID INTEGER NOT NULL," + NL
141: + " FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH
142: + ") NOT NULL," + NL + " CAPTION VARCHAR("
143: + BLOG_CAPTION_MAXLENGTH + ") NOT NULL," + NL
144: + " COMMENTS VARCHAR("
145: + BLOG_COMMENTS_MAXLENGTH_HSQLDB + ") NOT NULL,"
146: + NL + " CREATION_DATE TIMESTAMP NOT NULL," + NL
147: + " MODIFICATION_DATE TIMESTAMP NOT NULL" + NL +
148: // Identity automatically defines primary key
149: // "CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID)," +
150: // HSQL has a bug that unique constraint is generates
151: // unique index but with system generated name
152: // To be able to detect violation of this constraint
153: // we need to generate the index ourselves
154: // " CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)" +
155: ")")) {
156: // Close any results
157: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
158: }
159: s_logger.log(Level.FINEST, "Table BF_BLOG created.");
160: if (stmQuery
161: .execute("create unique index BF_BLOG_FLDR_UQ on BF_BLOG(FOLDER)")) {
162: // Close any results
163: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
164: }
165: s_logger
166: .log(Level.FINEST, "Index BF_BLOG_FLDR_UQ created.");
167: if (stmQuery.execute("grant all on BF_BLOG to "
168: + strUserName)) {
169: // Close any results
170: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
171: }
172: s_logger.log(Level.FINEST,
173: "Access for table BF_BLOG set for user "
174: + strUserName);
175:
176: ///////////////////////////////////////////////////////////////////////
177:
178: if (stmQuery.execute("create cached table BF_BLOG_ENTRY"
179: + NL + "(" + NL + " ID INTEGER IDENTITY," + NL
180: + " DOMAIN_ID INTEGER NOT NULL," + NL
181: + " BLOG_ID INTEGER NOT NULL," + NL
182: + " CAPTION VARCHAR("
183: + BLOGENTRY_CAPTION_MAXLENGTH + ") NOT NULL," + NL
184: + " COMMENTS VARCHAR("
185: + BLOGENTRY_COMMENTS_MAXLENGTH_HSQLDB
186: + ") NOT NULL," + NL + " IMAGEURL VARCHAR("
187: + BLOGENTRY_IMAGEURL_MAXLENGTH + ") NOT NULL," + NL
188: + " TARGETURL VARCHAR("
189: + BLOGENTRY_TARGETURL_MAXLENGTH + ") NOT NULL,"
190: + NL
191: + " CREATION_DATE TIMESTAMP NOT NULL,"
192: + NL
193: + " MODIFICATION_DATE TIMESTAMP NOT NULL,"
194: + NL
195: +
196: // Identity automatically defines primary key
197: // " CONSTRAINT BF_WLE_PK PRIMARY KEY (ID)," +
198: " CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID) "
199: + "REFERENCES BF_BLOG (ID) ON DELETE CASCADE" + NL
200: + ")")) {
201: // Close any results
202: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
203: }
204: s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
205: if (stmQuery.execute("grant all on BF_BLOG_ENTRY to "
206: + strUserName)) {
207: // Close any results
208: stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
209: }
210: s_logger.log(Level.FINEST,
211: "Access for table BF_BLOG_ENTRY set for user "
212: + strUserName);
213: } catch (SQLException sqleExc) {
214: s_logger.log(Level.WARNING, "Failed to create schema "
215: + BLOG_SCHEMA_NAME, sqleExc);
216: throw sqleExc;
217: } finally {
218: DatabaseUtils.closeStatement(stmQuery);
219: }
220: }
221:
222: /**
223: * {@inheritDoc}
224: */
225: public String getInsertBlogAndFetchGeneratedValues()
226: throws OSSException {
227: return getInsertBlog();
228: }
229:
230: /**
231: * {@inheritDoc}
232: */
233: public String getUpdateBlogAndFetchGeneratedValues()
234: throws OSSException {
235: StringBuffer buffer = new StringBuffer();
236:
237: buffer
238: .append("update BF_BLOG set FOLDER = ?, CAPTION = ?, COMMENTS = ?,"
239: + " MODIFICATION_DATE = ");
240: buffer.append(DatabaseImpl.getInstance()
241: .getCurrentTimestampFunctionCall());
242: buffer
243: .append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
244:
245: return buffer.toString();
246: }
247:
248: /**
249: * {@inheritDoc}
250: */
251: public String getInsertBlog() throws OSSException {
252: StringBuffer buffer = new StringBuffer();
253:
254: // HSQLDB requires for IDENTITY columns to specify ID and pass null as a value
255: buffer.append("INSERT INTO BF_BLOG(");
256: buffer.append(BLOG_COLUMNS);
257: buffer.append(") VALUES (null, ?, ?, ?, ?, ");
258: buffer.append(DatabaseImpl.getInstance()
259: .getCurrentTimestampFunctionCall());
260: buffer.append(", ");
261: buffer.append(DatabaseImpl.getInstance()
262: .getCurrentTimestampFunctionCall());
263: buffer.append(")");
264:
265: return buffer.toString();
266: }
267:
268: /**
269: * {@inheritDoc}
270: */
271: public String getInsertEntry() throws OSSException {
272: StringBuffer buffer = new StringBuffer();
273:
274: // HSQLDB requires for IDENTITY columns to specify ID and pass null as a value
275: buffer.append("INSERT INTO BF_BLOG_ENTRY(");
276: buffer.append(ENTRY_COLUMNS);
277: buffer.append(") VALUES (null, ?, ?, ?, ?, ?, ?, ");
278: buffer.append(DatabaseImpl.getInstance()
279: .getCurrentTimestampFunctionCall());
280: buffer.append(", ");
281: buffer.append(DatabaseImpl.getInstance()
282: .getCurrentTimestampFunctionCall());
283: buffer.append(")");
284:
285: return buffer.toString();
286: }
287:
288: /**
289: * {@inheritDoc}
290: */
291: public String getInsertEntryAndFetchGeneratedValues()
292: throws OSSException {
293: return getInsertEntry();
294: }
295:
296: /**
297: * {@inheritDoc}
298: */
299: public String getUpdateEntryAndFetchGeneratedValues()
300: throws OSSException {
301: StringBuffer buffer = new StringBuffer();
302:
303: buffer
304: .append("update BF_BLOG_ENTRY set CAPTION = ?, COMMENTS = ?,"
305: + " IMAGEURL = ?, TARGETURL = ?, MODIFICATION_DATE = ");
306: buffer.append(DatabaseImpl.getInstance()
307: .getCurrentTimestampFunctionCall());
308: buffer
309: .append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
310:
311: return buffer.toString();
312: }
313: }
|