Christian wrote:
"I didn't know modifying the given arrays was supported. Still, dealing
with raw arrays is brittle as I'd have to rely on the DDL order of
columns ... The adapter at least lets me use the ResultSet API and
column names to read the data."
I agree with you completely. And that is why I implemented my own
TriggerAdapter.
Also h2 has a trigger adapter (which you already use)
http://www.h2database.com/javadoc/org/h2/tools/TriggerAdapter.html
But because it does not support all the bells and whistles I want I just
use my own.
The TriggerAdapter of H2 is very easy to understand so have a look at
it's source.
I include it here for you to see how I have implemented the thing I
suggested.
The class refers to some of my own utility classes but I quess it should
be easy to quess what is happening.
I do not rely on column order (and as a sidenote having an order of
columns in the first place is a terrible mistake of sql since the header
of a relation should be a SET of columns).
Below the adapter is a simple trigger that shows how to use it.
package archon.h2;
import java.sql.*;
import java.util.*;
import org.h2.api.Trigger;
import org.h2.tools.*;
import rojares.toolbox.util.*;
/**
* An adapter for the trigger interface that allows to use the ResultSet
* interface instead of a row array.
* Also adds basic info about the trigger as instance variable and some
error reporting
*/
public abstract class MyTriggerAdapter implements Trigger {
private SimpleResultSet oldResultSet, newResultSet;
private TriggerRowSource oldSource, newSource;
protected List<String> primaryKeyColumns = new ArrayList<String>();
protected String triggerCatalog;
protected DbObjectName triggerName;
protected DbObjectName tableName;
protected boolean before;
protected int type;
/**
* This method is called by the database engine once when
initializing the
* trigger. It is called when the trigger is created, as well as
when the
* database is opened. The default implementation initialized the
result
* sets.
*
* @param conn a connection to the database
* @param schemaName the name of the schema
* @param triggerName the name of the trigger used in the CREATE
TRIGGER
* statement
* @param tableName the name of the table
* @param before whether the fire method is called before or after the
* operation is performed
* @param type the operation type: INSERT, UPDATE, or DELETE
*/
public void init(Connection conn, String schemaName, String
triggerName, String tableName, boolean before, int type) throws
SQLException {
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getColumns(null, schemaName, tableName, null);
oldSource = new TriggerRowSource();
newSource = new TriggerRowSource();
oldResultSet = new SimpleResultSet(oldSource);
newResultSet = new SimpleResultSet(newSource);
while (rs.next()) {
String column = rs.getString("COLUMN_NAME");
int dataType = rs.getInt("DATA_TYPE");
int precision = rs.getInt("COLUMN_SIZE");
int scale = rs.getInt("DECIMAL_DIGITS");
oldResultSet.addColumn(column, dataType, precision, scale);
newResultSet.addColumn(column, dataType, precision, scale);
}
rs = dbmd.getPrimaryKeys(null, schemaName, tableName);
while(rs.next())
primaryKeyColumns.add(rs.getString("COLUMN_NAME"));
this.triggerCatalog = conn.getCatalog().toLowerCase();
this.triggerName = new DbObjectName(schemaName, triggerName);
this.tableName = new DbObjectName(schemaName, tableName);
this.before = before;
this.type = type;
}
/**
* A row source that allows to set the next row.
*/
static class TriggerRowSource implements SimpleRowSource {
private Object[] row;
void setRow(Object[] row) {
this.row = row;
}
public Object[] readRow() {
return row;
}
public void close() {
// ignore
}
public void reset() {
// ignore
}
}
private SimpleResultSet wrap(SimpleResultSet rs, TriggerRowSource
source, Object[] row) throws SQLException {
if (row == null) {
return null;
}
source.setRow(row);
rs.next();
return rs;
}
/**
* This method is called for each triggered action. The method is
called
* immediately when the operation occurred (before it is committed). A
* transaction rollback will also rollback the operations that were
done
* within the trigger, if the operations occurred within the same
database.
* If the trigger changes state outside the database, a rollback
trigger
* should be used.
* <p>
* The row arrays contain all columns of the table, in the same order
* as defined in the table.
* </p>
* <p>
* The default implementation calls the fire method with the ResultSet
* parameters.
* </p>
*
* @param conn a connection to the database
* @param oldRow the old row, or null if no old row is available (for
* INSERT)
* @param newRow the new row, or null if no new row is available (for
* DELETE)
* @throws SQLException if the operation must be undone
*/
public void fire(Connection conn, Object[] oldRow, Object[] newRow)
throws SQLException {
try {
/*
System.out.println("Fire trigger " + triggerName + " on
table " + tableName);
System.out.println("OLD: " + Arrays.deepToString(oldRow));
System.out.println("NEW: " + Arrays.deepToString(newRow));
*/
fire(conn, wrap(oldResultSet, oldSource, oldRow),
wrap(newResultSet, newSource, newRow));
}
catch(SQLException sqle) {
sqle.printStackTrace();
throw sqle;
}
catch(RuntimeException rte) {
rte.printStackTrace();
throw rte;
}
}
public void updateColumn(String colname, Object newValue) throws
SQLException {
this.newSource.readRow()[this.newResultSet.findColumn(colname)-1] =
newValue;
}
/**
* This method is called for each triggered action by the default
* fire(Connection conn, Object[] oldRow, Object[] newRow) method.
* ResultSet.next does not need to be called (and calling it has no
effect;
* it will always return true).
*
* @param conn a connection to the database
* @param oldRow the old row, or null if no old row is available (for
* INSERT)
* @param newRow the new row, or null if no new row is available (for
* DELETE)
* @throws SQLException if the operation must be undone
*/
public abstract void fire(Connection conn, ResultSet oldRow,
ResultSet newRow) throws SQLException;
/**
* This method is called when the database is closed.
* If the method throws an exception, it will be logged, but
* closing the database will continue.
* The default implementation does nothing.
*
* @throws SQLException
*/
public void remove() throws SQLException {
// do nothing by default
}
/**
* This method is called when the trigger is dropped.
* The default implementation does nothing.
*
* @throws SQLException
*/
public void close() throws SQLException {
// do nothing by default
}
/*
Helper methods for triggers
Currently supports only INSERT, UPDATE, DELETE and SELECT alone
but no combinations
*/
public static int parseType(String typeName) {
if (typeName.trim().equalsIgnoreCase("INSERT")) return
Trigger.INSERT;
else if (typeName.trim().equalsIgnoreCase("UPDATE")) return
Trigger.UPDATE;
else if (typeName.trim().equalsIgnoreCase("DELETE")) return
Trigger.DELETE;
else if (typeName.trim().equalsIgnoreCase("SELECT")) return
Trigger.SELECT;
else return 0;
}
/*
Currently supports only INSERT, UPDATE, DELETE and SELECT alone
but no combinations
*/
public static String formatType(int type) {
if (type == Trigger.INSERT) return "INSERT";
else if (type == Trigger.UPDATE) return "UPDATE";
else if (type == Trigger.DELETE) return "DELETE";
else if (type == Trigger.SELECT) return "SELECT";
else return "UNKNOWN";
}
/**
Returns true if all values in the rows are the same
If either one is null returns false.
*/
public static boolean noChanges(ResultSet oldRow, ResultSet newRow)
throws SQLException {
if (oldRow == null || newRow == null) return false;
ResultSetMetaData md = newRow.getMetaData();
for(int i=1; i<=md.getColumnCount(); i++) {
Object newval = newRow.getObject(i);
Object oldval = oldRow.getObject(i);
if (newval == null) {
if (oldval != null) return false;
}
else if (!newval.equals(oldval)) return false;
}
return true;
}
/**
Given an array of columns checks if they are changed (different
in the two rows)
If either one is null returns true.
*/
public static boolean needsUpdating(String[] changeCols, ResultSet
oldRow, ResultSet newRow) throws SQLException {
if (oldRow == null || newRow == null) return true;
boolean updateNeeded = false;
for(int i=0; i<changeCols.length; i++) {
String col = changeCols[i];
Object oldVal = oldRow.getObject(col);
Object newVal = newRow.getObject(col);
if (oldVal == null) {
if (newVal != null) { updateNeeded = true; break;}
}
else if (!oldVal.equals(newVal)) { updateNeeded = true;
break;}
}
return updateNeeded;
}
}
package archon.h2.exodus;
import org.h2.api.*;
import java.sql.*;
import rojares.toolbox.util.*;
import archon.h2.*;
public class ModTrigger extends MyTriggerAdapter {
public void fire(Connection conn, ResultSet oldRow, ResultSet
newRow) throws SQLException {
updateColumn("A", new Integer(777));
}
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
