/*
* Copyright (c) 2003 - 2007 OpenSubsystems s.r.o. Slovak Republic. All rights reserved.
*
* Project: OpenChronicle
*
* $Id: HsqlDBBlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:20 bastafidli Exp $
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; version 2 of the License.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
package org.opensubsystems.blog.persist.db.hsqldb;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.opensubsystems.blog.data.Blog;
import org.opensubsystems.blog.data.Entry;
import org.opensubsystems.blog.persist.db.BlogDatabaseSchema;
import org.opensubsystems.core.error.OSSException;
import org.opensubsystems.core.persist.db.DatabaseImpl;
import org.opensubsystems.core.util.DatabaseUtils;
import org.opensubsystems.core.util.Log;
/**
* Database specific operations related to persistence of blogs and their
* entries. This class encapsulate functionality specific for HSQLDB.
*
* @version $Id: HsqlDBBlogDatabaseSchema.java,v 1.3 2007/02/20 02:13:20 bastafidli Exp $
* @author Miro Halas
* @code.reviewer Miro Halas
* @code.reviewed 1.4 2006/07/13 23:44:26 jlegeny
*/
public class HsqlDBBlogDatabaseSchema extends BlogDatabaseSchema
{
/*
Use cached tables since these tables can be large
Use autogenerated numbers for IDs using IDENTITY column.
Identity automatically defines primary key
Name all constraints to easily identify them later.
For all unique constraint we need to define unique indexes instead of
unique constrant otherwise we won't be able to indentify the violation of
this constraint by name.
CREATE CACHED TABLE BF_BLOG
(
ID INTEGER IDENTITY,
DOMAIN_ID INTEGER NOT NULL,
FOLDER VARCHAR(50) NOT NULL,
CAPTION VARCHAR(1024) NOT NULL,
COMMENTS VARCHAR(32768) NOT NULL,
CREATION_DATE TIMESTAMP NOT NULL,
MODIFICATION_DATE TIMESTAMP NOT NULL
// CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID),
// CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)
);
create unique index BF_BLOG_FLDR_UQ on BF_BLOG(FOLDER);
CREATE CACHED TABLE BF_BLOG_ENTRY
(
ID INTEGER IDENTITY,
DOMAIN_ID INTEGER NOT NULL,
BLOG_ID INTEGER NOT NULL,
CAPTION VARCHAR(1024) NOT NULL,
COMMENTS VARCHAR(32768) NOT NULL,
IMAGEURL VARCHAR(1024) NOT NULL,
TARGETURL VARCHAR(1024) NOT NULL,
CREATION_DATE TIMESTAMP NOT NULL,
MODIFICATION_DATE TIMESTAMP NOT NULL,
// CONSTRAINT BF_WLE_PK PRIMARY KEY (ID),
CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID)
REFERENCES BF_BLOG (ID) ON DELETE CASCADE
);
*/
// Constants ////////////////////////////////////////////////////////////////
/**
* Maximal length of blog comments.
*/
public static final int BLOG_COMMENTS_MAXLENGTH_HSQLDB = 32768;
/**
* Maximal length of blog entry comments.
*/
public static final int BLOGENTRY_COMMENTS_MAXLENGTH_HSQLDB = 32768;
// Cached values ////////////////////////////////////////////////////////////
/**
* Logger for this class
*/
private static Logger s_logger = Log.getInstance(HsqlDBBlogDatabaseSchema.class);
// Constructors /////////////////////////////////////////////////////////////
/**
* Static initializer
*/
static
{
// Setup maximal length of individual fields for HSQLDB database
Blog.setCommentsMaxLength(BLOG_COMMENTS_MAXLENGTH_HSQLDB);
Entry.setCommentsMaxLength(BLOGENTRY_COMMENTS_MAXLENGTH_HSQLDB);
}
/**
* Default constructor.
*
* @throws OSSException - error occured.
*/
public HsqlDBBlogDatabaseSchema(
) throws OSSException
{
super();
}
// Public methods ///////////////////////////////////////////////////////////
/**
* {@inheritDoc}
*/
public void create(
Connection cntDBConnection,
String strUserName
) throws SQLException
{
Statement stmQuery = null;
try
{
stmQuery = cntDBConnection.createStatement();
if (stmQuery.execute(
"create cached table BF_BLOG" + NL +
"(" + NL +
" ID INTEGER IDENTITY," + NL +
" DOMAIN_ID INTEGER NOT NULL," + NL +
" FOLDER VARCHAR(" + BLOG_FOLDER_MAXLENGTH + ") NOT NULL," + NL +
" CAPTION VARCHAR(" + BLOG_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
" COMMENTS VARCHAR(" + BLOG_COMMENTS_MAXLENGTH_HSQLDB + ") NOT NULL," + NL +
" CREATION_DATE TIMESTAMP NOT NULL," + NL +
" MODIFICATION_DATE TIMESTAMP NOT NULL" + NL +
// Identity automatically defines primary key
// "CONSTRAINT BF_BLOG_PK PRIMARY KEY (ID)," +
// HSQL has a bug that unique constraint is generates
// unique index but with system generated name
// To be able to detect violation of this constraint
// we need to generate the index ourselves
// " CONSTRAINT BF_BLOG_FLDR_UQ UNIQUE (FOLDER)" +
")"))
{
// Close any results
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
}
s_logger.log(Level.FINEST, "Table BF_BLOG created.");
if (stmQuery.execute("create unique index BF_BLOG_FLDR_UQ on BF_BLOG(FOLDER)"))
{
// Close any results
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
}
s_logger.log(Level.FINEST, "Index BF_BLOG_FLDR_UQ created.");
if (stmQuery.execute("grant all on BF_BLOG to " + strUserName))
{
// Close any results
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
}
s_logger.log(Level.FINEST,
"Access for table BF_BLOG set for user " + strUserName);
///////////////////////////////////////////////////////////////////////
if (stmQuery.execute(
"create cached table BF_BLOG_ENTRY" + NL +
"(" + NL +
" ID INTEGER IDENTITY," + NL +
" DOMAIN_ID INTEGER NOT NULL," + NL +
" BLOG_ID INTEGER NOT NULL," + NL +
" CAPTION VARCHAR(" + BLOGENTRY_CAPTION_MAXLENGTH + ") NOT NULL," + NL +
" COMMENTS VARCHAR(" + BLOGENTRY_COMMENTS_MAXLENGTH_HSQLDB + ") NOT NULL," + NL +
" IMAGEURL VARCHAR(" + BLOGENTRY_IMAGEURL_MAXLENGTH + ") NOT NULL," + NL +
" TARGETURL VARCHAR(" + BLOGENTRY_TARGETURL_MAXLENGTH + ") NOT NULL," + NL +
" CREATION_DATE TIMESTAMP NOT NULL," + NL +
" MODIFICATION_DATE TIMESTAMP NOT NULL," + NL +
// Identity automatically defines primary key
// " CONSTRAINT BF_WLE_PK PRIMARY KEY (ID)," +
" CONSTRAINT BF_BLOGENTR_FK FOREIGN KEY (BLOG_ID) "
+ "REFERENCES BF_BLOG (ID) ON DELETE CASCADE" + NL +
")"))
{
// Close any results
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
}
s_logger.log(Level.FINEST, "Table BF_BLOG_ENTRY created.");
if (stmQuery.execute("grant all on BF_BLOG_ENTRY to " + strUserName))
{
// Close any results
stmQuery.getMoreResults(Statement.CLOSE_ALL_RESULTS);
}
s_logger.log(Level.FINEST,
"Access for table BF_BLOG_ENTRY set for user " + strUserName);
}
catch (SQLException sqleExc)
{
s_logger.log(Level.WARNING,
"Failed to create schema " + BLOG_SCHEMA_NAME, sqleExc);
throw sqleExc;
}
finally
{
DatabaseUtils.closeStatement(stmQuery);
}
}
/**
* {@inheritDoc}
*/
public String getInsertBlogAndFetchGeneratedValues(
) throws OSSException
{
return getInsertBlog();
}
/**
* {@inheritDoc}
*/
public String getUpdateBlogAndFetchGeneratedValues(
) throws OSSException
{
StringBuffer buffer = new StringBuffer();
buffer.append("update BF_BLOG set FOLDER = ?, CAPTION = ?, COMMENTS = ?," +
" MODIFICATION_DATE = ");
buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
buffer.append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
return buffer.toString();
}
/**
* {@inheritDoc}
*/
public String getInsertBlog(
) throws OSSException
{
StringBuffer buffer = new StringBuffer();
// HSQLDB requires for IDENTITY columns to specify ID and pass null as a value
buffer.append("INSERT INTO BF_BLOG(");
buffer.append(BLOG_COLUMNS);
buffer.append(") VALUES (null, ?, ?, ?, ?, ");
buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
buffer.append(", ");
buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
buffer.append(")");
return buffer.toString();
}
/**
* {@inheritDoc}
*/
public String getInsertEntry(
) throws OSSException
{
StringBuffer buffer = new StringBuffer();
// HSQLDB requires for IDENTITY columns to specify ID and pass null as a value
buffer.append("INSERT INTO BF_BLOG_ENTRY(");
buffer.append(ENTRY_COLUMNS);
buffer.append(") VALUES (null, ?, ?, ?, ?, ?, ?, ");
buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
buffer.append(", ");
buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
buffer.append(")");
return buffer.toString();
}
/**
* {@inheritDoc}
*/
public String getInsertEntryAndFetchGeneratedValues(
) throws OSSException
{
return getInsertEntry();
}
/**
* {@inheritDoc}
*/
public String getUpdateEntryAndFetchGeneratedValues(
) throws OSSException
{
StringBuffer buffer = new StringBuffer();
buffer.append("update BF_BLOG_ENTRY set CAPTION = ?, COMMENTS = ?," +
" IMAGEURL = ?, TARGETURL = ?, MODIFICATION_DATE = ");
buffer.append(DatabaseImpl.getInstance().getCurrentTimestampFunctionCall());
buffer.append(" where ID = ? and DOMAIN_ID = ? and MODIFICATION_DATE = ?");
return buffer.toString();
}
}
|