limited trigger proposal

[Jim McBeath <jimmc@xxxxxxxxxx>]

In the spirit of starting with "baby steps", I propose the following
initial implementation of triggers for 0.94, this being what I consider
pretty much the minimal useful implementation:

* Triggers are created with a CREATE TRIGGER statement:
    CREATE TRIGGER triggerName BEFORE INSERT ON tableName FOR EACH ROW AS
        JAVACLASS package.class 'arg1' 'arg2'
* Triggers are deleted a with DROP TRIGGER statement:
    DROP TRIGGER triggerName
* Triggers are global to a database, and are immediately effective when
  defined (when the containing Transaction is committed).
* Triggers can not be created on system tables.
* Triggers can be created only for INSERT, UPDATE, and DELETE on a table.
* BEFORE and AFTER triggers are supported.
* Errors raised in a BEFORE trigger are reflected back to the client.
* Old and new data are available in special tables called OLD and NEW.
  For a FOR EACH ROW trigger, each will have exactly one row.
* Changing the NEW data in a BEFORE trigger will cause that changed data
  to be written to the new row.
* Triggers run in the same WorkerThread as the original request.
* The current connection and information on the current user are available
  to the Java trigger (easily available from the current WorkerThread as long
  as it is the same as the original request).

I think this will require doing the following:

1. Modify Parser for new syntax.
 1.1 Modify CreateTrigger in sql/SQL.jj to allow specifying BEFORE and AFTER
     keywords, FOR EACH ROW clause, and AS JAVACLASS clause.
 1.2 Modify interpret/CreateTrigger.java to pass the JAVACLASS info (if
     specified) to a new version of database.createTrigger.
 1.3 Modify DatabaseConnection.java to add a new CreateTrigger method that takes
     the JAVACLASS info.  This method would call the new Transaction.addTrigger
     method.
 1.4 Modify DatabaseConnection.java so that dropTrigger is capable of dropping
     either kind of trigger (CALLBACK or JAVACLASS), either by calling
     the existing TriggerManager.dropTrigger, or by calling the new
     Transaction.dropTrigger.
 1.5 Modify TriggerManager to allow checking to see if it knows about a trigger,
     to allow DatabaseConnection to attempt to delete either kind of trigger.

2. Add a new system table, sUSRTriggers.
 2.1 Add TRIGGERS_TABLE to TableDataConglomerate.java; add code to
     initializeSystemTableSchema to set it up; add code to
     updateSystemTableSchema to create the table.
 2.2 Modify setSystemGrants in Database.java.
 2.3 Add methods addTrigger and dropTrigger to Transaction.java, to save the
     trigger data into sUSRTriggers and delete it from there.  Also
     queryTriggers to find the triggers for a specified table (including other
     parms, such as BEFORE/AFTER or INSERT/UPDATE/DELETE).

3. Add code to cache and execute a trigger to
   MasterTableDataSource.MMutableTableDataSource.
 3.1 Define a new interface "JavaTrigger".  Include an init method,
     called when the trigger instance is first created, and which gets passed
     the args from the JAVACLASS clause.  Include an action method which
     gets called to invoke the trigger.  This is the interface the user will
     implement when writing a trigger.
 3.2 When a new MMutableTableDataSource is created,
     call Transaction.queryTriggers to get the data for all triggers for
     this table, save that data in a local cache of trigger data.
     Also set up a local cache of initialized JavaTrigger objects, one
     for each of the six combinations of BEFORE/AFTER x INSERT/UPDATE/DELETE.
 3.3 Add a method getTriggers that takes a BEFORE/AFTER flag and an
     INSERT/UPDATE/DELETE flag and returns JavaTrigger[].  If the cache
     of initialized JavaTrigger[] for that combination is not yet initialized,
     get the matching data from the trigger data cache, instantiate the
     JavaTrigger objects, call their init methods, and save the list in the
     JavaTrigger[] variable for that combination.  Return the JavaTrigger[].
 3.4 Add a flag "inTrigger" to WorkerThread.  Set it when we are executing
     a trigger.  Don't do triggers when already in a trigger.
 3.5 Add an executeTrigger method to MMutableTableDataSource that takes a
     row index for old data, a RowData for new data, and a trigger
     instance to execute.  Set up OLD and NEW tables in the current
     DatabaseConnection (see #6 below), set the inTrigger flag in the
     WorkerThread, and invoke the TriggerHandler (see #5 below): get the
     current triggerHandler from the WorkerThread, call it if it exists.
     If it doesn't, then ignore the trigger (or raise an exception).
 3.6 Add method executeTriggers to MMutableTableDataSource
     that takes a new RowData and an old row index (which it converts to a
     RowData), plus a BEFORE/AFTER flag and an INSERT/UPDATE/DELETE flag.
     Check inTrigger flag, just return without doing anything if set.
     Call getTriggers to get all of the appropriate triggers, then call
     executeTrigger on each one.

4. Add code to invoke the triggers to
   MasterTableDataSource.MMutableTableDataSource (calls to executeTriggers).
 4.1 For Delete: in MMutableTableDataSource.removeRow,
     add a call to check BEFORE triggers just before calling
     table_journal.addEntry; add a call to check AFTER triggers just after
     calling TableDataConglomerate.checkRemoveConstraintViolations.
 4.2 For Insert: In MMutableTableDataSource.addRow,
     add a call to check BEFORE triggers just before calling
     MasterTableDataSource.this.addRow; add a call to check AFTER triggers just
     after calling TableDataConglomerate.checkAddConstraintViolations.
 4.3 For Update: In MMutableTableDataSource.updateRow,
     add a call to check BEFORE triggers just before
     calling table_journal.addEntry to remove the old row;
     add a call to check AFTER triggers just after calling
     TableDataConglomerate.checkAddConstraintViolations.

5. Add code to support creating a java.sql.Connection to pass to the
   JavaTrigger, allowing it to execute SQL commands within the current
   Transaction using the current WorkerThread.
 5.1 Define a new interface com.mckoi.database.TriggerHandler with a method
     that takes a JavaTrigger and executes it.
 5.2 Modify JDBCDatabaseConnection to include a reference to an MConnection.
     Modify MConnection initialization to pass itself to the
     JDBCDatabaseConnection for it to store.
 5.3 Make com.mckoi.database.jdbc.JDBCDatabaseInterface implement
     TriggerHandler.  Make that implementation pass its saved MConnection
     to the JavaTrigger action method.  Modify execQuery to set the
     triggerHandler field in the WorkerThread to itself.
 5.4 Modify LocalDatabaseInterface.execQuery to check to see if the current
     thread is a WorkerThread and inTrigger is set; if so, invoke
     db_interface.execQuery directly in the current thread
     rather than using exec.
 5.5 Modify JDBCDatabaseInterface.execQuery to check the inTrigger flag; if
     set, don't do any locking, unlocking, or auto-commit.

6. Set up OLD and NEW tables for access by triggers.
 6.1 Define a new class RowDataDataSource which extends GTDataSource.
     Make the constructor take a Transaction and a RowData as arguments.
     (And perhaps a pointer to the parent table.)
     Implement getCellContents to access the data in the RowData, and
     implement updateRow to update the original RowData.  Or, add a method
     to get back a new RowData with any changes made to the table (in
     which case the changes will have to be propagated back out to ensure
     they make it to MMutableTableDataSource.addRow etc.).
 6.2 Add a method to Transaction such as setOldNewRowData(RowData old, RowData
     new) to allow setting the old and new RowData for a trigger.
     Call it from MMutableTableDataSource.executeTrigger.
 6.3 Modify Transaction.getTable to check for the special names OLD and NEW,
     and return a RowDataDataSource in those cases, using the data set by
     the call to setOldNewRowData.  Keep a pointer to these tables in
     the Transaction, and update them when setOldNewRowData is called.

Some additional points:
* The above implementation plan does not include any code to ensure that
  triggers added or removed in the middle of a transaction apply to the
  remainder of that transaction.
* I'm assuming that table-level locking will be removed, and all statements
  evaluated in a Transaction will acquire an exclusive lock.  I think this
  is the only reasonable way to lower the complexity of the locking issue
  to a manageable level.
* This plan caches triggers only within a Transaction (in
  MMutableTableDataSource).  We may want to do that caching in
  MasterTableDataSource instead, to decrease the overhead of instantiating
  and initializing the triggers.
* Rather than just defining a JavaTrigger interface, it may be worth defining
  a more generic Trigger interface, then define JavaTrigger (and SqlTrigger
  for later use) as a subclass.
* This plan assumes that the separation of JDBC from the main database
  package is preserved (thus the TriggerHandler interface).  Alternatively,
  we could fold the TriggerHandler into DatabaseInterface and have
  MMutableTableDataSource.executeTrigger directly call a handleTrigger method
  in DatabaseInterface, which requires MasterTableDataSource to import from
  the jdbc subpackage.  It looks like we pretty much need to call back into
  the JDBCDatabaseInterface class anyway, so we only save an interface
  definition by importing the jdbc subpackage into MasterTableDataSource;
  might as well preserve the package separation for now.

Things to implement later:
* INSTEAD OF triggers.
* non FOR EACH ROW triggers.
* Triggers with an SQL body.
* The list of columns being changed by an insert or update command must be
  available; perhaps in a special table with one column, where each row
  contains the name of one column being changed.
* A privilege-controlled per-connection flag to run without triggers.
* Triggers on other actions, such as CREATE/ALTER/DROP TABLE.
* Triggers that execute only after a transaction has successfully committed.
* Control over the sequencing of multiple triggers.
* Nested trigger invocation (defining when a trigger is executed when in the
  middle of another trigger).

I think I understand the code well enough to begin working on this next week.

-Jim