Unico,
In trying to determine whether the modified delete statements are "standard"
SQL, I looked at the documentation for several DBMSs:
- MySQL [http://dev.mysql.com/doc/mysql/en/DELETE.html]
- Sybase
[http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=
2919?target=%25N%15_24890_START_RESTART_N%25]
- SQL Server
[http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_de-dz_9lut.asp].
The documentation for MySQL says the following: "In MySQL 4.0, you should
refer to the table names to be deleted with the true table name. In MySQL
4.1, you must use the alias (if one was given) when referring to a table
name.
In MySQL 4.0:
DELETE test FROM test AS t1, test2 WHERE ...
In MySQL 4.1:
DELETE t1 FROM test AS t1, test2 WHERE ..."
Other providers agree on this syntax:
"DELETE table_name FROM table_name [, table_name]* WHERE..."
But they don't specify whether table aliases are allowed before the FROM
keyword. So, I guess the most general form for these statements wouldn't use
table aliases at all:
"delete BINDING from BINDING, URI
where BINDING.URI_ID = URI.URI_ID and URI.URI_STRING = ?"
I'm attaching an updated adapter using this notation. It'd be great if you
reflected these updates in the standard adapter.
-Alejandro
-----Original Message-----
From: Unico Hommes [mailto:[EMAIL PROTECTED]
Sent: Domingo 11 de Julio de 2004 1:14 a
To: Slide Users Mailing List
Subject: Re: JDBCStore with MySQL problem
Hi Alejandro,
This is great. Would you be willing to donate the code to the Slide
project? Btw. do you know if this notation is non-standard SQL or do all
databases understand those modified delete statements? In the latter
case, I better change the standard adapter class.
--
Unico
/*
* Project: wf-data
* File: MySqlAdapter.java
* Created: 29/06/2004
*/
package mx.connecto.repository.sld;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.slide.common.Service;
import org.apache.slide.common.ServiceAccessException;
import org.apache.slide.common.Uri;
import org.apache.slide.content.NodeRevisionDescriptor;
import org.apache.slide.content.NodeRevisionNumber;
import org.apache.slide.store.impl.rdbms.MySqlRDBMSAdapter;
import org.apache.slide.structure.ObjectNode;
import org.apache.slide.structure.ObjectNotFoundException;
import org.apache.slide.util.logger.Logger;
/**
* <br/>Project: wf-data
* <br/>Created: 29/06/2004
* @author Alejandro Gu�zar
* @version $Revision$ $Date$
*/
public class MySqlAdapter extends MySqlRDBMSAdapter {
/**
* @param service
* @param logger
*/
public MySqlAdapter(Service service, Logger logger) {
super(service, logger);
}
/* (non-Javadoc)
* @see org.apache.slide.store.impl.rdbms.RDBMSAdapter#removeObject(
* java.sql.Connection, org.apache.slide.common.Uri,
* org.apache.slide.structure.ObjectNode)
*/
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 LINKS from LINKS, URI " +
"where LINKS.URI_ID = URI.URI_ID and URI.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 VERSION_HISTORY from VERSION_HISTORY, URI " +
"where VERSION_HISTORY.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
statement.setString(1, uri.toString());
statement.executeUpdate();
}
finally {
close(statement);
}
// delete version
try {
statement = connection.prepareStatement(
"delete VERSION from VERSION, URI " +
"where VERSION.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
statement.setString(1, uri.toString());
statement.executeUpdate();
}
finally {
close(statement);
}
// delete the object itself
try {
statement = connection.prepareStatement(
"delete OBJECT from OBJECT, URI " +
"where OBJECT.URI_ID = URI.URI_ID and URI.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());
}
}
/* (non-Javadoc)
* @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 VERSION_CONTENT from VERSION_CONTENT, VERSION_HISTORY, URI " +
"where VERSION_CONTENT.VERSION_ID = VERSION_HISTORY.VERSION_ID " +
"and VERSION_HISTORY.REVISION_NO = ? " +
"and VERSION_HISTORY.URI_ID=URI.URI_ID and URI.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());
}
}
/* (non-Javadoc)
* @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 {
statement = connection.prepareStatement(
"delete VERSION_LABELS from VERSION_LABELS, VERSION_HISTORY, URI " +
"where VERSION_LABELS.VERSION_ID = VERSION_HISTORY.VERSION_ID " +
"and VERSION_HISTORY.REVISION_NO = ? " +
"and VERSION_HISTORY.URI_ID = URI.URI_ID AND URI.URI_STRING = ?");
statement.setString(1, revisionNumber.toString());
statement.setString(2, uri.toString());
statement.executeUpdate();
}
finally {
close(statement);
}
try {
statement = connection.prepareStatement(
"delete PROPERTIES from PROPERTIES, VERSION_HISTORY, URI " +
"where PROPERTIES.VERSION_ID = VERSION_HISTORY.VERSION_ID " +
"and VERSION_HISTORY.REVISION_NO = ? " +
"and VERSION_HISTORY.URI_ID = URI.URI_ID AND URI.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());
}
}
/* (non-Javadoc)
* @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 VERSION_PREDS from VERSION_PREDS, VERSION_HISTORY, URI " +
"where VERSION_PREDS.VERSION_ID = VERSION_HISTORY.VERSION_ID " +
"and VERSION_HISTORY.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
statement.setString(1, uri.toString());
statement.executeUpdate();
} catch (SQLException e) {
throw createException(e, uri.toString());
} finally {
close(statement);
}
}
/* (non-Javadoc)
* @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 BINDING from BINDING, URI " +
"where BINDING.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
statement.setString(1, uri.toString());
statement.executeUpdate();
}
finally {
close(statement);
}
try {
statement = connection.prepareStatement(
"delete PARENT_BINDING from PARENT_BINDING, URI " +
"where PARENT_BINDING.URI_ID = URI.URI_ID and URI.URI_STRING = ?");
statement.setString(1, uri.toString());
statement.executeUpdate();
}
finally {
close(statement);
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
