001: /* Copyright (c) 2001-2005, The HSQL Development Group
002: * All rights reserved.
003: *
004: * Redistribution and use in source and binary forms, with or without
005: * modification, are permitted provided that the following conditions are met:
006: *
007: * Redistributions of source code must retain the above copyright notice, this
008: * list of conditions and the following disclaimer.
009: *
010: * Redistributions in binary form must reproduce the above copyright notice,
011: * this list of conditions and the following disclaimer in the documentation
012: * and/or other materials provided with the distribution.
013: *
014: * Neither the name of the HSQL Development Group nor the names of its
015: * contributors may be used to endorse or promote products derived from this
016: * software without specific prior written permission.
017: *
018: * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
019: * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
020: * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
021: * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
022: * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
023: * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
024: * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
025: * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
026: * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
027: * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
028: * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
029: */
030:
031: // use the org.hsqldb.sample package here; but will be in your own package usually
032: package org.hsqldb.sample;
033:
034: import java.io.PrintWriter;
035: import java.sql.Connection;
036: import java.sql.DriverManager;
037: import java.sql.PreparedStatement;
038: import java.sql.ResultSet;
039: import java.sql.ResultSetMetaData;
040: import java.sql.SQLException;
041: import java.sql.Statement;
042:
043: import org.hsqldb.Trace;
044: import org.hsqldb.Trigger;
045: import org.hsqldb.lib.StringUtil;
046:
047: // peterhudson@users 20020130 - patch 478657 by peterhudson - new class
048: // fredt@users 20030727 - signature altered
049: // boucherb@users 20040315 - sample updated
050:
051: /**
052: * <P>Sample code for use of triggers in hsqldb.
053: *
054: * SQL to invoke is:<p>
055: * CREATE TRIGGER triggerSample BEFORE|AFTER INSERT|UPDATE|DELETE
056: * ON myTable [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL "myPackage.trigClass"<br>
057: *
058: * This will create a thread that will wait for its firing event to occur;
059: * when this happens, the trigger's thread runs the 'trigClass.fire'
060: * Note that this is still in the same Java Virtual Machine as the
061: * database, so make sure the fired method does not hang.<p>
062: *
063: * There is a queue of events waiting to be run by each trigger thread.
064: * This is particularly useful for 'FOR EACH ROW' triggers, when a large
065: * number of trigger events occur in rapid succession, without the trigger
066: * thread getting a chance to run. If the queue becomes full, subsequent
067: * additions to it cause the database engine to suspend awaiting space
068: * in the queue. Take great care to avoid this situation if the trigger
069: * action involves accessing the database, as deadlock will occur.
070: * This can be avoided either by ensuring the QUEUE parameter makes a large
071: * enough queue, or by using the NOWAIT parameter, which causes a new
072: * trigger event to overwrite the most recent event in the queue.
073: * The default queue size is 1024.<p>
074: *
075: * Ensure that "myPackage.trigClass" is present in the classpath which
076: * you use to start hsql.<p>
077: *
078: * If the method wants to access the database, it must establish
079: * a JDBC connection.<p>
080: *
081: * When the 'fire' method is called, it is passed the following arguments: <p>
082: *
083: * fire (int type, String trigName, String tabName, Object oldRow[],
084: * Object[] newRow) <p>
085: *
086: * where 'type' is one of the values enumerated in the Trigger interface and
087: * the 'oldRow'/'newRow' pair represents the rows acted on. The first
088: * length - 1 array slots contain column values and the final slot contains
089: * either null or the value of the internally assigned row identity, if
090: * the concerned table has no primary key. The final slot must _never_ be
091: * modified. <p>
092: *
093: * The mapping of row classes to database types is specified in
094: * /doc/hsqlSyntax.html#Datatypes. <p>
095: *
096: * To be done:<p>
097: *
098: * <ol>
099: * <li> Implement the "jdbc:default:connection: URL to provide transparent
100: * and portable access to internal connections for use in triggers and
101: * stored procedures. <p>
102: *
103: * <li> Implement declaritive column to trigger method argument
104: * mapping, conditional execution (WHEN clause), etc. <p>
105: *
106: * <li> Investigate and refine synchronous and asynchronous trigger models. <p>
107: *
108: * Because certain combinations of trigger create parameters cause the
109: * individual triggered actions of a multirow update to run in different
110: * threads, it is possible for an 'after' trigger to run before its
111: * corresponding 'before' trigger; the acceptability and implications
112: * of this needs to be investigated, documented and the behaviour of
113: * the engine fully specified.
114: *
115: * <li> Investigate and implement the SQL 200n specified execution stack under
116: * arbitrary triggered action and SQL-invoked routine call graphs.
117: * </ol>
118: *
119: * @author Peter Hudson
120: * @author boucherb@users
121: * @version 1.7.2
122: * @since 1.7.0
123: */
124: public class TriggerSample implements Trigger {
125:
126: static final PrintWriter out = new PrintWriter(System.out);
127: static final String drv = "org.hsqldb.jdbcDriver";
128: static final String url = "jdbc:hsqldb:mem:trigger-sample";
129: static final String usr = "sa";
130: static final String pwd = "";
131: static final String impl = TriggerSample.class.getName();
132: static final String tn = "trig_test";
133: static final String drop_test_table_stmt = "DROP TABLE " + tn
134: + " IF EXISTS";
135: static final String create_test_table_stmt = "CREATE TABLE " + tn
136: + "(id INTEGER PRIMARY KEY, value VARCHAR(20))";
137: static final String drop_audit_table_stmt = "DROP TABLE audit IF EXISTS";
138: static final String create_audit_table_stmt = "CREATE TABLE audit("
139: + "id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1), "
140: + "op VARCHAR(6), " + "tn VARCHAR, "
141: + "ors LONGVARCHAR, " + "nrs LONGVARCHAR, "
142: + "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
143: static final String audit_insert_stmt = "INSERT INTO audit(op, tn, ors, nrs) VALUES(?, ?, ?, ?)";
144:
145: /**
146: * A sample HSQLDB Trigger interface implementation. <p>
147: *
148: * This sample prints information about the firing trigger and records
149: * actions in an audit table. <p>
150: *
151: * The techniques used here are simplified dramatically for demonstration
152: * purposes and are in no way recommended as a model upon which to build
153: * actual installations involving triggered actions.
154: *
155: * @param typ trigger type
156: * @param trn trigger name
157: * @param tn table name
158: * @param or old row
159: * @param nr new row
160: */
161: public void fire(int typ, String trn, String tn, Object[] or,
162: Object[] nr) {
163:
164: synchronized (TriggerSample.class) {
165: String ors = or == null ? "null" : StringUtil
166: .arrayToString(or);
167: String nrs = nr == null ? "null" : StringUtil
168: .arrayToString(nr);
169:
170: out.println("----------------------------------------");
171: out.println(getTriggerDescriptor(trn, typ, tn));
172: out.println("old row : " + ors);
173: out.println("new row : " + nrs);
174: out.flush();
175:
176: if ("TRIG_TEST".equals(tn)) {
177: switch (typ) {
178:
179: case INSERT_BEFORE_ROW: {
180:
181: // Business rule: ID shall be less than 11.
182: // (Marti DiBergi, we love you ;-)
183: // You can cast row[i] given your knowledge of what
184: // the table format is:
185: final int ID = ((Number) nr[0]).intValue();
186:
187: doAssert(ID < 11, "ID < 11");
188:
189: break;
190: }
191: case UPDATE_BEFORE_ROW: {
192:
193: // Business rule: ignore update of VALUE 'unchangable'.
194: if ("unchangable".equals(or[1])) {
195: nr[1] = or[1]; // short-circuit the update
196: }
197:
198: // !!!Warning!!!
199: // The engine does not check the class of substituted
200: // values; it's up to you to use the correct class.
201: // For example, this will cause database curruption:
202: // nr[1] = new Integer(5);
203: break;
204: }
205: }
206: }
207:
208: doAuditStep(typ, tn, ors, nrs);
209: }
210: }
211:
212: private static void doAssert(boolean b, String msg)
213: throws RuntimeException {
214:
215: if (b) {
216:
217: // do nothing
218: } else {
219: msg = Trace.getMessage(Trace.ASSERT_FAILED) + ": " + msg;
220:
221: throw new RuntimeException(msg);
222: }
223: }
224:
225: private static void doAuditStep(int typ, String tn, String ors,
226: String nrs) {
227:
228: Connection conn;
229: PreparedStatement stmt;
230:
231: switch (typ) {
232:
233: case INSERT_AFTER_ROW:
234: case UPDATE_AFTER_ROW:
235: case DELETE_AFTER_ROW: {
236: try {
237: conn = getConnection();
238: stmt = conn.prepareStatement(audit_insert_stmt);
239:
240: stmt.setString(1, getOperationSpec(typ));
241: stmt.setString(2, tn);
242: stmt.setString(3, ors);
243: stmt.setString(4, nrs);
244: stmt.executeUpdate();
245: conn.close();
246: } catch (SQLException se) {
247: se.printStackTrace();
248: }
249: }
250: }
251: }
252:
253: public static String getWhenSpec(int type) {
254:
255: switch (type) {
256:
257: case INSERT_BEFORE:
258: case INSERT_BEFORE_ROW:
259: case UPDATE_BEFORE:
260: case UPDATE_BEFORE_ROW:
261: case DELETE_BEFORE:
262: case DELETE_BEFORE_ROW: {
263: return "BEFORE";
264: }
265: case INSERT_AFTER:
266: case INSERT_AFTER_ROW:
267: case UPDATE_AFTER:
268: case UPDATE_AFTER_ROW:
269: case DELETE_AFTER:
270: case DELETE_AFTER_ROW: {
271: return "AFTER";
272: }
273: default: {
274: return "";
275: }
276: }
277: }
278:
279: public static String getOperationSpec(int type) {
280:
281: switch (type) {
282:
283: case INSERT_AFTER:
284: case INSERT_AFTER_ROW:
285: case INSERT_BEFORE:
286: case INSERT_BEFORE_ROW: {
287: return "INSERT";
288: }
289: case UPDATE_AFTER:
290: case UPDATE_AFTER_ROW:
291: case UPDATE_BEFORE:
292: case UPDATE_BEFORE_ROW: {
293: return "UPDATE";
294: }
295: case DELETE_AFTER:
296: case DELETE_AFTER_ROW:
297: case DELETE_BEFORE:
298: case DELETE_BEFORE_ROW: {
299: return "DELETE";
300: }
301: default: {
302: return "";
303: }
304: }
305: }
306:
307: public static String getQueueSpec(int qs) {
308: return (qs < 0) ? "" : ("QUEUE " + qs);
309: }
310:
311: public static String getForEachSpec(int type) {
312:
313: switch (type) {
314:
315: case INSERT_BEFORE_ROW:
316: case INSERT_AFTER_ROW:
317: case UPDATE_BEFORE_ROW:
318: case UPDATE_AFTER_ROW:
319: case DELETE_AFTER_ROW:
320: case DELETE_BEFORE_ROW: {
321: return "FOR EACH ROW";
322: }
323: default: {
324: return "";
325: }
326: }
327: }
328:
329: public static String getTriggerDDL(String trn, int typ, String tab,
330: int qs, String impl) throws SQLException {
331:
332: StringBuffer sb = new StringBuffer();
333:
334: sb.append("CREATE TRIGGER ");
335: sb.append(trn);
336: sb.append(' ');
337: sb.append(getWhenSpec(typ));
338: sb.append(' ');
339: sb.append(getOperationSpec(typ));
340: sb.append(" ON ");
341: sb.append(tab);
342: sb.append(' ');
343: sb.append(getForEachSpec(typ));
344: sb.append(' ');
345: sb.append(getQueueSpec(qs));
346: sb.append(" CALL \"");
347: sb.append(impl);
348: sb.append("\"");
349:
350: return sb.toString();
351: }
352:
353: public static String getTriggerDescriptor(String trn, int typ,
354: String tab) {
355:
356: StringBuffer sb = new StringBuffer();
357:
358: sb.append("TRIGGER : ");
359: sb.append(trn);
360: sb.append(' ');
361: sb.append(getWhenSpec(typ));
362: sb.append(' ');
363: sb.append(getOperationSpec(typ));
364: sb.append(" ON ");
365: sb.append(tab);
366: sb.append(' ');
367: sb.append(getForEachSpec(typ));
368:
369: return sb.toString();
370: }
371:
372: private static Connection getConnection() throws SQLException {
373:
374: try {
375: Class.forName(drv).newInstance();
376:
377: return DriverManager.getConnection(url, usr, pwd);
378: } catch (SQLException se) {
379: throw se;
380: } catch (Exception e) {
381: throw new SQLException(e.toString());
382: }
383: }
384:
385: private static void createTrigger(Statement stmt, String trn,
386: int typ) throws SQLException {
387: stmt.execute(getTriggerDDL(trn, typ, tn, 0, impl));
388: }
389:
390: private static void setup() throws SQLException {
391:
392: Connection conn = getConnection();
393: Statement stmt = conn.createStatement();
394:
395: stmt.execute(drop_test_table_stmt);
396: stmt.execute(create_test_table_stmt);
397: stmt.execute(drop_audit_table_stmt);
398: stmt.execute(create_audit_table_stmt);
399: createTrigger(stmt, "tib_" + tn, INSERT_BEFORE);
400: createTrigger(stmt, "tibr_" + tn, INSERT_BEFORE_ROW);
401: createTrigger(stmt, "tia_" + tn, INSERT_AFTER);
402: createTrigger(stmt, "tiar_" + tn, INSERT_AFTER_ROW);
403: createTrigger(stmt, "tub_" + tn, UPDATE_BEFORE);
404: createTrigger(stmt, "tubr_" + tn, UPDATE_BEFORE_ROW);
405: createTrigger(stmt, "tua_" + tn, UPDATE_AFTER);
406: createTrigger(stmt, "tuar_" + tn, UPDATE_AFTER_ROW);
407: createTrigger(stmt, "tdb_" + tn, DELETE_BEFORE);
408: createTrigger(stmt, "tdbr_" + tn, DELETE_BEFORE_ROW);
409: createTrigger(stmt, "tda_" + tn, DELETE_AFTER);
410: createTrigger(stmt, "tdar_" + tn, DELETE_AFTER_ROW);
411: stmt.close();
412: conn.close();
413: }
414:
415: private static void doSomeWork() throws SQLException {
416:
417: Connection conn = getConnection();
418: Statement stmt = conn.createStatement();
419:
420: conn.setAutoCommit(false);
421: stmt.execute("INSERT INTO trig_test VALUES (1, 'hello')");
422: stmt.execute("INSERT INTO trig_test VALUES (2, 'now what?')");
423: stmt.execute("INSERT INTO trig_test VALUES (3, 'unchangable')");
424: stmt.execute("INSERT INTO trig_test VALUES (4, 'goodbye')");
425: conn.commit();
426: dumpTable("trig_test");
427: stmt.execute("UPDATE trig_test SET value = 'all done'");
428: conn.commit();
429: dumpTable("trig_test");
430: stmt.execute("DELETE FROM trig_test");
431: conn.rollback();
432: dumpTable("trig_test");
433:
434: try {
435: stmt
436: .execute("INSERT INTO trig_test VALUES(11, 'whatever')");
437: } catch (SQLException se) {
438: se.printStackTrace();
439: }
440:
441: stmt.execute("INSERT INTO trig_test VALUES(10, 'whatever')");
442: conn.commit();
443: dumpTable("trig_test");
444: stmt.close();
445: conn.close();
446: }
447:
448: private static void dumpTable(String tn) throws SQLException {
449:
450: Connection conn = getConnection();
451: Statement stmt = conn.createStatement();
452: ResultSet rs = stmt.executeQuery("select * from " + tn);
453: ResultSetMetaData rsmd = rs.getMetaData();
454: int count = rsmd.getColumnCount();
455:
456: out.println();
457: out.println("****************************************");
458: out.println("DUMP FOR TABLE: " + tn);
459: out.println("****************************************");
460: out.flush();
461:
462: while (rs.next()) {
463: out.print("[");
464:
465: for (int i = 1; i <= count; i++) {
466: out.print(rs.getString(i));
467:
468: if (i < count) {
469: out.print(" : ");
470: }
471: }
472:
473: out.println("]");
474: }
475:
476: out.println();
477: out.flush();
478: rs.close();
479: stmt.close();
480: conn.close();
481: }
482:
483: private static void runSample() throws SQLException {
484:
485: setup();
486: doSomeWork();
487: dumpTable("audit");
488: }
489:
490: public static void main(String[] args) throws SQLException {
491: runSample();
492: }
493: }
494: /*
495: test SQL
496: CREATE CACHED TABLE trig_test (int_field integer)
497: CREATE TRIGGER ins_before BEFORE INSERT ON trig_test CALL "org.hsqldb.sample.TriggerSample"
498: CREATE TRIGGER ins_after AFTER INSERT ON trig_test CALL "org.hsqldb.sample.TriggerSample"
499: CREATE TRIGGER upd_before BEFORE UPDATE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
500: CREATE TRIGGER upd_after AFTER UPDATE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
501: CREATE TRIGGER upd_before_row BEFORE UPDATE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
502: CREATE TRIGGER upd_after_row AFTER UPDATE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
503: CREATE TRIGGER del_before BEFORE DELETE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
504: CREATE TRIGGER del_after AFTER DELETE ON trig_test CALL "org.hsqldb.sample.TriggerSample"
505: CREATE TRIGGER del_before_row BEFORE DELETE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
506: CREATE TRIGGER del_after_row AFTER DELETE ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample"
507: INSERT INTO trig_test VALUES (1)
508: INSERT INTO trig_test VALUES (2)
509: INSERT INTO trig_test VALUES (3)
510: UPDATE trig_test SET int_field = int_field + 3
511: DELETE FROM trig_test
512: */
|