/* Copyright (c) 2001-2010, The HSQL Development Group
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* Redistributions of source code must retain the above copyright notice, this
* list of conditions and the following disclaimer.
*
* Redistributions in binary form must reproduce the above copyright notice,
* this list of conditions and the following disclaimer in the documentation
* and/or other materials provided with the distribution.
*
* Neither the name of the HSQL Development Group nor the names of its
* contributors may be used to endorse or promote products derived from this
* software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
* OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
* EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
package org.hsqldb.sample;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.hsqldb.error.Error;
import org.hsqldb.error.ErrorCode;
import org.hsqldb.Trigger;
import org.hsqldb.lib.StringUtil;
// peterhudson@users 20020130 - patch 478657 by peterhudson - new class
// fredt@users 20030727 - signature altered
// boucherb@users 20040315 - sample updated
/**
* <P>Sample code for use of triggers in hsqldb.
*
* This class org.hsqldb.sample package, but a typical implementation is in
* users's class hierarchy.
*
* SQL to invoke is:<p>
* CREATE TRIGGER triggerSample BEFORE|AFTER INSERT|UPDATE|DELETE
* ON myTable [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL "myPackage.trigClass"<br>
*
* This will create a thread that will wait for its firing event to occur;
* when this happens, the trigger's thread runs the 'trigClass.fire'
* Note that this is still in the same Java Virtual Machine as the
* database, so make sure the fired method does not hang.<p>
*
* There is a queue of events waiting to be run by each trigger thread.
* This is particularly useful for 'FOR EACH ROW' triggers, when a large
* number of trigger events occur in rapid succession, without the trigger
* thread getting a chance to run. If the queue becomes full, subsequent
* additions to it cause the database engine to suspend awaiting space
* in the queue. Take great care to avoid this situation if the trigger
* action involves accessing the database, as deadlock will occur.
* This can be avoided either by ensuring the QUEUE parameter makes a large
* enough queue, or by using the NOWAIT parameter, which causes a new
* trigger event to overwrite the most recent event in the queue.
* The default queue size is 1024.<p>
*
* Ensure that "myPackage.trigClass" is present in the classpath which
* you use to start hsql.<p>
*
* If the method wants to access the database, it must establish
* a JDBC connection.<p>
*
* When the 'fire' method is called, it is passed the following arguments: <p>
*
* fire (int type, String trigName, String tabName, Object oldRow[],
* Object[] newRow) <p>
*
* where 'type' is one of the values enumerated in the Trigger interface and
* the 'oldRow'/'newRow' pair represents the rows acted on. The first
* length - 1 array slots contain column values and the final slot contains
* either null or the value of the internally assigned row identity, if
* the concerned table has no primary key. The final slot must _never_ be
* modified. <p>
*
* The mapping of row classes to database types is specified in
* /doc/hsqlSyntax.html#Datatypes. <p>
*
* To be done:<p>
*
* <ol>
* <li> Implement the "jdbc:default:connection: URL to provide transparent
* and portable access to internal connections for use in triggers and
* stored procedures. <p>
*
* <li> Implement declaritive column to trigger method argument
* mapping, conditional execution (WHEN clause), etc. <p>
*
* <li> Investigate and refine synchronous and asynchronous trigger models. <p>
*
* Because certain combinations of trigger create parameters cause the
* individual triggered actions of a multirow update to run in different
* threads, it is possible for an 'after' trigger to run before its
* corresponding 'before' trigger; the acceptability and implications
* of this needs to be investigated, documented and the behaviour of
* the engine fully specified.
*
* <li> Investigate and implement the SQL 200n specified execution stack under
* arbitrary triggered action and SQL-invoked routine call graphs.
* </ol>
*
* @author Peter Hudson
* @author boucherb@users
* @version 1.7.2
* @since 1.7.0
*/
public class TriggerSample implements Trigger {
static final PrintWriter out = new PrintWriter(System.out);
static final String drv = "org.hsqldb.jdbc.JDBCDriver";
static final String url = "jdbc:hsqldb:mem:trigger-sample";
static final String usr = "SA";
static final String pwd = "";
static final String impl = TriggerSample.class.getName();
static final String tn = "trig_test";
static final String drop_test_table_stmt = "DROP TABLE " + tn
+ " IF EXISTS";
static final String create_test_table_stmt = "CREATE TABLE " + tn
+ "(id INTEGER PRIMARY KEY, value VARCHAR(20))";
static final String drop_audit_table_stmt = "DROP TABLE audit IF EXISTS";
static final String create_audit_table_stmt = "CREATE TABLE audit("
+ "id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1), "
+ "op VARCHAR(6), " + "tn VARCHAR(20), " + "ors LONGVARCHAR, "
+ "nrs LONGVARCHAR, " + "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
static final String audit_insert_stmt =
"INSERT INTO audit(op, tn, ors, nrs) VALUES(?, ?, ?, ?)";
/**
* A sample HSQLDB Trigger interface implementation. <p>
*
* This sample prints information about the firing trigger and records
* actions in an audit table. <p>
*
* The techniques used here are simplified dramatically for demonstration
* purposes and are in no way recommended as a model upon which to build
* actual installations involving triggered actions.
*
* @param typ trigger type
* @param trn trigger name
* @param tn table name
* @param or old row
* @param nr new row
*/
public void fire(int typ, String trn, String tn, Object[] or,
Object[] nr) {
synchronized (TriggerSample.class) {
String ors = or == null ? "null"
: StringUtil.arrayToString(or);
String nrs = nr == null ? "null"
: StringUtil.arrayToString(nr);
out.println("----------------------------------------");
out.println(getTriggerDescriptor(trn, typ, tn));
out.println("old row : " + ors);
out.println("new row : " + nrs);
out.flush();
if ("TRIG_TEST".equals(tn)) {
switch (typ) {
case INSERT_BEFORE_ROW : {
// Business rule: ID shall be less than 11.
// (Marti DiBergi, we love you ;-)
// You can cast row[i] given your knowledge of what
// the table format is:
final int ID = ((Number) nr[0]).intValue();
doAssert(ID < 11, "ID < 11");
break;
}
case UPDATE_BEFORE_ROW : {
// Business rule: ignore update of VALUE 'unchangable'.
if ("unchangable".equals(or[1])) {
nr[1] = or[1]; // short-circuit the update
}
// !!!Warning!!!
// The engine does not check the class of substituted
// values; it's up to you to use the correct class.
// For example, this will cause database curruption:
// nr[1] = new Integer(5);
break;
}
}
}
doAuditStep(typ, tn, ors, nrs);
}
}
private static void doAssert(boolean b, String msg) {
if (b) {
// do nothing
} else {
throw org.hsqldb.error.Error.error(ErrorCode.GENERAL_ERROR,
msg);
}
}
private static void doAuditStep(int typ, String tn, String ors,
String nrs) {
Connection conn;
PreparedStatement stmt;
switch (typ) {
case INSERT_AFTER_ROW :
case UPDATE_AFTER_ROW :
case DELETE_AFTER_ROW : {
try {
conn = getConnection();
stmt = conn.prepareStatement(audit_insert_stmt);
stmt.setString(1, getOperationSpec(typ));
stmt.setString(2, tn);
stmt.setString(3, ors);
stmt.setString(4, nrs);
stmt.executeUpdate();
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
}
}
public static String getWhenSpec(int type) {
switch (type) {
case INSERT_BEFORE_ROW :
case UPDATE_BEFORE_ROW :
case DELETE_BEFORE_ROW : {
return "BEFORE";
}
case INSERT_AFTER :
case INSERT_AFTER_ROW :
case UPDATE_AFTER :
case UPDATE_AFTER_ROW :
case DELETE_AFTER :
case DELETE_AFTER_ROW : {
return "AFTER";
}
default : {
return "";
}
}
}
public static String getOperationSpec(int type) {
switch (type) {
case INSERT_AFTER :
case INSERT_AFTER_ROW :
case INSERT_BEFORE_ROW : {
return "INSERT";
}
case UPDATE_AFTER :
case UPDATE_AFTER_ROW :
case UPDATE_BEFORE_ROW : {
return "UPDATE";
}
case DELETE_AFTER :
case DELETE_AFTER_ROW :
case DELETE_BEFORE_ROW : {
return "DELETE";
}
default : {
return "";
}
}
}
public static String getQueueSpec(int qs) {
return (qs < 0) ? ""
: ("QUEUE " + qs);
}
public static String getForEachSpec(int type) {
switch (type) {
case INSERT_BEFORE_ROW :
case INSERT_AFTER_ROW :
case UPDATE_BEFORE_ROW :
case UPDATE_AFTER_ROW :
case DELETE_AFTER_ROW :
case DELETE_BEFORE_ROW : {
return "FOR EACH ROW";
}
default : {
return "FOR EACH STATEMENT";
}
}
}
public static String getTriggerDDL(String trn, int typ, String tab,
int qs,
String impl) throws SQLException {
StringBuffer sb = new StringBuffer();
sb.append("CREATE TRIGGER ");
sb.append(trn);
sb.append(' ');
sb.append(getWhenSpec(typ));
sb.append(' ');
sb.append(getOperationSpec(typ));
sb.append(" ON ");
sb.append(tab);
sb.append(' ');
sb.append(getForEachSpec(typ));
sb.append(' ');
sb.append(getQueueSpec(qs));
sb.append(" CALL \"");
sb.append(impl);
sb.append("\"");
return sb.toString();
}
public static String getTriggerDescriptor(String trn, int typ,
String tab) {
StringBuffer sb = new StringBuffer();
sb.append("TRIGGER : ");
sb.append(trn);
sb.append(' ');
sb.append(getWhenSpec(typ));
sb.append(' ');
sb.append(getOperationSpec(typ));
sb.append(" ON ");
sb.append(tab);
sb.append(' ');
sb.append(getForEachSpec(typ));
return sb.toString();
}
private static Connection getConnection() throws SQLException {
try {
Class.forName(drv).newInstance();
return DriverManager.getConnection(url, usr, pwd);
} catch (SQLException se) {
throw se;
} catch (Exception e) {
throw new SQLException(e.toString());
}
}
private static void createTrigger(Statement stmt, String trn,
int typ) throws SQLException {
stmt.execute(getTriggerDDL(trn, typ, tn, 0, impl));
}
private static void setup() throws SQLException {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
stmt.execute(drop_test_table_stmt);
stmt.execute(create_test_table_stmt);
stmt.execute(drop_audit_table_stmt);
stmt.execute(create_audit_table_stmt);
createTrigger(stmt, "tibr_" + tn, INSERT_BEFORE_ROW);
createTrigger(stmt, "tia_" + tn, INSERT_AFTER);
createTrigger(stmt, "tiar_" + tn, INSERT_AFTER_ROW);
createTrigger(stmt, "tubr_" + tn, UPDATE_BEFORE_ROW);
createTrigger(stmt, "tua_" + tn, UPDATE_AFTER);
createTrigger(stmt, "tuar_" + tn, UPDATE_AFTER_ROW);
createTrigger(stmt, "tdbr_" + tn, DELETE_BEFORE_ROW);
createTrigger(stmt, "tda_" + tn, DELETE_AFTER);
createTrigger(stmt, "tdar_" + tn, DELETE_AFTER_ROW);
stmt.close();
conn.close();
}
private static void doSomeWork() throws SQLException {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
conn.setAutoCommit(false);
stmt.execute("INSERT INTO trig_test VALUES (1, 'hello')");
stmt.execute("INSERT INTO trig_test VALUES (2, 'now what?')");
stmt.execute("INSERT INTO trig_test VALUES (3, 'unchangable')");
stmt.execute("INSERT INTO trig_test VALUES (4, 'goodbye')");
conn.commit();
dumpTable("trig_test");
stmt.execute("UPDATE trig_test SET value = 'all done'");
conn.commit();
dumpTable("trig_test");
stmt.execute("DELETE FROM trig_test");
conn.rollback();
dumpTable("trig_test");
try {
stmt.execute("INSERT INTO trig_test VALUES(11, 'whatever')");
} catch (SQLException se) {
se.printStackTrace();
}
stmt.execute("INSERT INTO trig_test VALUES(10, 'whatever')");
conn.commit();
dumpTable("trig_test");
stmt.close();
conn.close();
}
private static void dumpTable(String tn) throws SQLException {
Connection conn = getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from " + tn);
ResultSetMetaData rsmd = rs.getMetaData();
int count = rsmd.getColumnCount();
out.println();
out.println("****************************************");
out.println("DUMP FOR TABLE: " + tn);
out.println("****************************************");
out.flush();
while (rs.next()) {
out.print("[");
for (int i = 1; i <= count; i++) {
out.print(rs.getString(i));
if (i < count) {
out.print(" : ");
}
}
out.println("]");
}
out.println();
out.flush();
rs.close();
stmt.close();
conn.close();
}
private static void runSample() throws SQLException {
setup();
doSomeWork();
dumpTable("audit");
}
public static void main(String[] args) throws SQLException {
runSample();
}
}
/*
test SQL
CREATE CACHED TABLE trig_test (int_field integer)
CREATE TRIGGER ins_before BEFORE INSERT ON trig_test CALL "org.hsqldb.sample.TriggerSample"
CREATE TRIGGER ins_after AFTER INSERT ON trig_test CALL "org.hsqldb.sample.TriggerSample"
CREATE TRIGGER upd_before BEFORE UPDATE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
CREATE TRIGGER upd_after AFTER UPDATE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
CREATE TRIGGER upd_before_row BEFORE UPDATE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
CREATE TRIGGER upd_after_row AFTER UPDATE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
CREATE TRIGGER del_before BEFORE DELETE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
CREATE TRIGGER del_after AFTER DELETE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
CREATE TRIGGER del_before_row BEFORE DELETE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
CREATE TRIGGER del_after_row AFTER DELETE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
INSERT INTO trig_test VALUES (1)
INSERT INTO trig_test VALUES (2)
INSERT INTO trig_test VALUES (3)
UPDATE trig_test SET int_field = int_field + 3
DELETE FROM trig_test
*/