package org.apache.slide.store.impl.rdbms;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Enumeration;
import org.apache.slide.common.Service;
import org.apache.slide.common.ServiceAccessException;
import org.apache.slide.common.Uri;
import org.apache.slide.content.NodeProperty;
import org.apache.slide.content.NodeRevisionDescriptor;
import org.apache.slide.content.NodeRevisionDescriptors;
import org.apache.slide.content.NodeRevisionNumber;
import org.apache.slide.structure.ObjectNode;
import org.apache.slide.structure.ObjectNotFoundException;
import org.apache.slide.util.logger.Logger;
/**
* TODO change delete x from X to delete from X where in (...)
* Last modified: $Date: 2006/04/09 11:44:15 $ by $Author: laddi $
*
* @author <a href="mailto:aron@idega.com">aron </a>
* @version $Revision: 1.4 $
*/
public class HsqlRDBMSAdapter extends StandardRDBMSAdapter {
// CommonRDBMSAdapter {
public HsqlRDBMSAdapter(Service service, Logger logger) {
super(service, logger);
}
/*
* (non-Javadoc)
*
* @see org.apache.slide.store.impl.rdbms.StandardRDBMSAdapter#convertRevisionNumberToComparable(java.lang.String)
*/
protected String convertRevisionNumberToComparable(String revisionNumber) {
/*
* CONVERT(SUBSTRING(vh.REVISION_NO,1,(LOCATE('.',vh.REVISION_NO)-1))
* ,INTEGER), CONVERT(SUBSTRING(vh.REVISION_NO, (
* LOCATE('.',vh.REVISION_NO)+1 )),INTEGER)
*/
String s = "convert(SUBSTRING(" + revisionNumber + ",1,(LOCATE('.',"
+ revisionNumber + ")-1)) ,INTEGER), convert( SUBSTRING("
+ revisionNumber + ",(LOCATE('.'," + revisionNumber
+ ")+1)),INTEGER)";
return s;
}
public void removeObject(Connection connection, Uri uri, ObjectNode object)
throws ServiceAccessException, ObjectNotFoundException {
PreparedStatement statement = null;
try {
clearBinding(connection, uri);
// delete links
try {
statement = connection
.prepareStatement("delete from LINKS where URI_ID in (select URI_ID from URI where URI_STRING = ? )");
statement.setString(1, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
// delete version history
// FIXME: Is this true??? Should the version history be removed if
// the object is removed???
try {
statement = connection
.prepareStatement("delete from VERSION_HISTORY where URI_ID in (select URI_ID from URI where URI_STRING = ? )");
statement.setString(1, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
// delete version
try {
statement = connection
.prepareStatement("delete from VERSION where URI_ID in (select URI_ID from URI where URI_STRING = ? )");
statement.setString(1, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
// delete the object itself
try {
statement = connection
.prepareStatement("delete from OBJECT where URI_ID in (select URI_ID from URI where URI_STRING = ?)");
statement.setString(1, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
// finally delete the uri
try {
statement = connection
.prepareStatement("delete from URI where URI_STRING = ?");
statement.setString(1, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
} catch (SQLException e) {
throw createException(e, uri.toString());
}
}
/*
* @see org.apache.slide.store.impl.rdbms.RDBMSAdapter#removeRevisionContent(
* java.sql.Connection, org.apache.slide.common.Uri,
* org.apache.slide.content.NodeRevisionDescriptor)
*/
public void removeRevisionContent(Connection connection, Uri uri,
NodeRevisionDescriptor revisionDescriptor)
throws ServiceAccessException {
try {
PreparedStatement statement = null;
try {
statement = connection
.prepareStatement("delete from VERSION_CONTENT where VERSION_ID in ( SELECT VH.VERSION_ID FROM VERSION_HISTORY VH, URI U WHERE REVISION_NO = ? and U.URI_STRING = ?)");
//"delete vc from VERSION_CONTENT vc, VERSION_HISTORY vh, URI u
// where vc.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = ?
// and vh.URI_ID=u.URI_ID AND u.URI_STRING=?");
statement.setString(1, revisionDescriptor.getRevisionNumber()
.toString());
statement.setString(2, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
} catch (SQLException e) {
throw createException(e, uri.toString());
}
}
/*
* @see org.apache.slide.store.impl.rdbms.RDBMSAdapter#removeRevisionDescriptor(
* java.sql.Connection, org.apache.slide.common.Uri,
* org.apache.slide.content.NodeRevisionNumber)
*/
public void removeRevisionDescriptor(Connection connection, Uri uri,
NodeRevisionNumber revisionNumber) throws ServiceAccessException {
PreparedStatement statement = null;
try {
try {
String sql = "delete from VERSION_LABELS where VERSION_ID in (select vh.VERSION_ID from VERSION_HISTORY vh, URI u where vh.REVISION_NO = ? and vh.URI_ID = u.URI_ID AND u.URI_STRING = ?)";
statement = connection.prepareStatement(sql);
//"delete vl from VERSION_LABELS vl, VERSION_HISTORY vh, URI u where vl.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = ? and vh.URI_ID = u.URI_ID AND u.URI_STRING = ?");
statement.setString(1, revisionNumber.toString());
statement.setString(2, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
try {
String sql = "delete from PROPERTIES where VERSION_ID in (select vh.VERSION_ID from VERSION_HISTORY vh, URI u where vh.REVISION_NO = ? and vh.URI_ID = u.URI_ID AND u.URI_STRING = ?)";
statement = connection.prepareStatement(sql);
//"delete p from PROPERTIES p, VERSION_HISTORY vh, URI u where p.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = ? and vh.URI_ID = u.URI_ID AND u.URI_STRING = ?");
statement.setString(1, revisionNumber.toString());
statement.setString(2, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
} catch (SQLException e) {
throw createException(e, uri.toString());
}
}
/*
* @see org.apache.slide.store.impl.rdbms.RDBMSAdapter#removeRevisionDescriptors(
* java.sql.Connection, org.apache.slide.common.Uri)
*/
public void removeRevisionDescriptors(Connection connection, Uri uri)
throws ServiceAccessException {
PreparedStatement statement = null;
try {
statement = connection
.prepareStatement("delete from VERSION_PREDS where VERSION_ID in ( SELECT VH.VERSION_ID FROM VERSION_HISTORY VH, URI U WHERE REVISION_NO = ? and U.URI_STRING = ?)");
//"delete vp from VERSION_PREDS vp, VERSION_HISTORY vh, URI u where
// vp.VERSION_ID = vh.VERSION_ID and vh.URI_ID = u.URI_ID and
// u.URI_STRING = ?");
statement.setString(1, uri.toString());
statement.executeUpdate();
} catch (SQLException e) {
throw createException(e, uri.toString());
} finally {
close(statement);
}
}
protected long getVersionID(Connection connection, String uriString,
NodeRevisionDescriptor revisionDescriptor) throws SQLException {
PreparedStatement statement = null;
ResultSet rs = null;
long versionID = 0l;
try {
statement = connection
.prepareStatement("select vh.VERSION_ID from VERSION_HISTORY vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = ? and vh.REVISION_NO = ?");
statement.setString(1, uriString);
statement.setString(2, revisionDescriptor.getRevisionNumber()
.toString());
rs = statement.executeQuery();
if (rs.next()) {
versionID = rs.getLong(1);
}
} finally {
close(statement, rs);
}
return versionID;
}
public void createRevisionDescriptor(Connection connection, Uri uri,
NodeRevisionDescriptor revisionDescriptor)
throws ServiceAccessException {
PreparedStatement statement = null;
try {
assureVersionInfo(connection, uri, revisionDescriptor);
for (Enumeration labels = revisionDescriptor.enumerateLabels(); labels
.hasMoreElements();) {
long labelId = assureLabelId(connection, (String) labels
.nextElement());
try {
long versionID = getVersionID(connection, uri.toString(),
revisionDescriptor);
statement = connection
.prepareStatement("insert into VERSION_LABELS (VERSION_ID, LABEL_ID) values (?,?)");
statement.setLong(1, versionID);
statement.setLong(2, labelId);
statement.executeUpdate();
} finally {
close(statement);
}
}
for (Enumeration properties = revisionDescriptor.enumerateProperties(); properties.hasMoreElements();) {
try {
NodeProperty property = (NodeProperty) properties
.nextElement();
long versionID = getVersionID(connection, uri.toString(),
revisionDescriptor);
statement = connection
.prepareStatement("insert into PROPERTIES (VERSION_ID,PROPERTY_NAMESPACE,PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE,IS_PROTECTED) values (?,?,?,?,?,?)");
int protectedProperty = property.isProtected() ? 1 : 0;
statement.setLong(1, versionID);
statement.setString(2, property.getNamespace());
statement.setString(3, property.getName());
statement.setString(4, property.getValue().toString());
statement.setString(5, property.getType());
statement.setInt(6, protectedProperty);
statement.executeUpdate();
} finally {
close(statement);
}
}
} catch (SQLException e) {
throw createException(e, uri.toString());
}
}
public void createRevisionDescriptors(Connection connection, Uri uri,
NodeRevisionDescriptors revisionDescriptors)
throws ServiceAccessException {
PreparedStatement statement = null;
ResultSet res = null;
try {
int isVersioned = 0;
if (revisionDescriptors.isVersioned()) {
isVersioned = 1;
}
boolean revisionExists;
try {
statement = connection
.prepareStatement("SELECT u.URI_ID FROM VERSION v, URI u WHERE v.URI_ID = u.URI_ID and u.URI_STRING = ?");
statement.setString(1, uri.toString());
res = statement.executeQuery();
revisionExists = res.next();
} finally {
close(statement, res);
}
if (!revisionExists) {
try {
long id = getID(connection, uri.toString());
statement = connection
.prepareStatement("insert into VERSION (URI_ID, IS_VERSIONED) values (?,?)");
statement.setLong(1, id);
statement.setInt(2, isVersioned);
statement.executeUpdate();
} finally {
close(statement, res);
}
}
boolean versionHistoryExists = false;
if (revisionDescriptors.getLatestRevision() != null) {
try {
statement = connection
.prepareStatement("SELECT 1 FROM VERSION_HISTORY vh, URI u WHERE vh.URI_ID = u.URI_ID and u.URI_STRING = ? and REVISION_NO = ?");
statement.setString(1, uri.toString());
statement.setString(2, revisionDescriptors
.getLatestRevision().toString());
res = statement.executeQuery();
versionHistoryExists = res.next();
} finally {
close(statement, res);
}
}
if (!versionHistoryExists
&& revisionDescriptors.getLatestRevision() != null) {
try {
long[] ids = getBranchIdAndUriID(connection, uri.toString());
long uriID = ids[0];
long branchID = ids[1];
statement = connection
.prepareStatement("insert into VERSION_HISTORY (URI_ID, BRANCH_ID, REVISION_NO) values(?,?,?)");
statement.setLong(1, uriID);
statement.setLong(2, branchID);
statement.setString(3,
getRevisionNumberAsString(revisionDescriptors
.getLatestRevision()));
// FIXME: Create new revisions on the main branch???
statement.executeUpdate();
} finally {
close(statement, res);
}
}
// Add revision successors
Enumeration revisionNumbers = revisionDescriptors
.enumerateRevisionNumbers();
while (revisionNumbers.hasMoreElements()) {
NodeRevisionNumber nodeRevisionNumber = (NodeRevisionNumber) revisionNumbers
.nextElement();
Enumeration successors = revisionDescriptors
.getSuccessors(nodeRevisionNumber);
while (successors.hasMoreElements()) {
try {
NodeRevisionNumber successor = (NodeRevisionNumber) successors
.nextElement();
statement = connection
.prepareStatement("insert into VERSION_PREDS (VERSION_ID, PREDECESSOR_ID) "
+ " select vr.VERSION_ID, suc.VERSION_ID"
+ " FROM URI uri, VERSION_HISTORY vr, VERSION_HISTORY suc "
+ " where vr.URI_ID = uri.URI_ID "
+ " and suc.URI_ID = uri.URI_ID "
+ " and uri.URI_STRING = ? "
+ " and vr.REVISION_NO = ? "
+ " and suc.REVISION_NO = ? ");
statement.setString(1, uri.toString());
statement.setString(2, nodeRevisionNumber.toString());
statement.setString(3, successor.toString());
statement.executeUpdate();
} finally {
close(statement);
}
}
}
getLogger().log(
revisionDescriptors.getOriginalUri()
+ revisionDescriptors.getInitialRevision(),
LOG_CHANNEL, Logger.INFO);
} catch (SQLException e) {
throw createException(e, uri.toString());
}
}
/*
* @see org.apache.slide.store.impl.rdbms.StandardRDBMSAdapter#clearBinding(java.sql.Connection,
* org.apache.slide.common.Uri)
*/
protected void clearBinding(Connection connection, Uri uri)
throws ServiceAccessException, ObjectNotFoundException,
SQLException {
PreparedStatement statement = null;
// clear this uri from having bindings and being bound
try {
statement = connection
.prepareStatement("delete from BINDING where URI_ID in ( select URI_ID from URI where URI_STRING = ?)");
//"delete c from BINDING c, URI u where c.URI_ID = u.URI_ID and
// u.URI_STRING = ?");
statement.setString(1, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
try {
statement = connection
.prepareStatement("delete from PARENT_BINDING where URI_ID in ( select URI_ID from URI where URI_STRING = ?)");
//"delete c from PARENT_BINDING c, URI u where c.URI_ID = u.URI_ID
// and u.URI_STRING = ?");
statement.setString(1, uri.toString());
statement.executeUpdate();
} finally {
close(statement);
}
}
private long getID(Connection connection, String uriString) throws SQLException
{
PreparedStatement statement = null;
ResultSet rs = null;
long uriID=0l;
try
{
statement =
connection.prepareStatement(
"select URI_ID from URI where URI_STRING = ?");
statement.setString(1,uriString);
rs = statement.executeQuery();
if(rs.next()) {
uriID= rs.getLong(1);
}
}
finally
{
close(statement,rs);
}
return uriID;
}
private long[] getBranchIdAndUriID(Connection connection, String uriString) throws SQLException
{
PreparedStatement statement = null;
ResultSet res = null;
long[] ids = new long[2];
try
{
statement =
connection.prepareStatement(
"select u.URI_ID, b.BRANCH_ID from URI u, BRANCH b where u.URI_STRING = ? and b.BRANCH_STRING = ?");
statement.setString(1, uriString);
statement.setString(2,NodeRevisionDescriptors.MAIN_BRANCH);
res = statement.executeQuery();
if(res.next())
{
ids[0]=res.getLong(1);
ids[1] = res.getLong(2);
}
}
finally
{
close(statement,res);
}
return ids;
}
}