List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:March 31 2001 1:10pm
Subject:Hacking triggers into 2.23.34a?
View as plain text  
Hi!

Sorry for the delay in answering this..

I have now read through the current thread and I will try to answer
all relevant questing regarding this in this email.

>>>>> "barries" == barries  <barries@stripped> writes:

barries> I come begging sanity checks and advice, previous experience, existing
barries> code etc.

barries> I am considering adding some modest C++-only trigger support to mysql
barries> 3.23.34a (or whatever's current by the time I finish).  My motivation
barries> is at the bottom of this message.

<cut>

barries> What would this look like in code?  For instance, in mysql_update() down
barries> around sql_update.cc, line 234 in 3.23.34a, I'm thinking of adding
barries> code that looks roughly like the +ed lines here:

barries> + int update_triggers_present = table->update_triggers_list ;
barries>   while (!(error=info.read_record(&info)) && !thd->killed)
barries>   {
barries>     if (!(select && select->skipp_record()))
barries>     {
barries>       store_record(table,1);
barries>       if (fill_record(fields,values))
barries>         break; /* purecov: inspected */
barries>       found++;
barries>       if (compare_record(table, query_id))
barries>       {
barries>         if (!(error=table->file->update_row((byte*)
> table->record[1],
barries>         				    (byte*) table->record[0])))
barries>         {
barries>           updated++;
barries>           if (!--limit && using_limit)
barries>           {
barries>             error= -1;
barries>             break;
barries>           }
barries>         }
barries>         else if (handle_duplicates != DUP_IGNORE ||
barries>         	 error != HA_ERR_FOUND_DUPP_KEY)
barries>         {
table-> file->print_error(error,MYF(0));
barries>           error= 1;
barries>           break;
barries>         }
barries> +       if (update_triggers_present)
barries> +       {
barries> +         UpdateTrigger *ut = table->update_triggers ;
barries> +         while (ut)
barries> +         {
barries> +           List<Items> before_items ; // I'd be happy with
> vector<string>...
barries> +           List<Items> after_items ;
barries> +           // Build lists of Items by unpacking table->record[0] and
barries> +           // table->record[1], don't know how to do this.
barries> +           ut->RowUpdated( before_items, after_items ) ;
barries> +           ut = ut->NextTrigger() ;
barries> +         }
barries> +       }
barries>       }
barries>     }
barries>   }

Instead of creating item for all columns, can't you instead let
the trigger test the columns it's interested in?

If this is ok, you only have to pass the RowUpdated() function the
table in question.  The following things should make it easy for you
to write an efficient trigger:

- In MySQL, it's always ->record[0] that is the original row and
  record[1] that is the modified one.

The sql/sql_update.cc:compare_record() function shows how you can test
if a column has changed.

Bascily the code is:

  for (Field **ptr=table->field ; *ptr ; ptr++)
  {
    Field *f= *ptr;
    if (f->query_id == query_id &&
        (f->cmp_binary_offset(table->rec_buff_length) ||
         (f->null_ptr &&
	  (f->null_ptr[0] & f->null_bit) !=
	  (f->null_ptr[table->rec_buff_length] & f->null_bit))))
    {
	/* Column has changed */
    }
  }

It should be trivial to let the trigger struct contain a list of fields to
check and then call a generic function to check if any of listed
fields have changed.

To access the new value, you can use the field class.
To access the old value, you can create a new field that points at
this as follows:

Field old_field= *field;
old_field.move_field(table->rec_buff_length);


barries> I'm ass_u_ming that mysql_delete() and mysql_insert() can be modified
barries> similarly, though I've not dug in to those quite this far.

Yes, this should be easy.

barries> I also consdered making a shim table handler that would pass through
barries> things and call any triggers that need be called, but that seems like
barries> over-engineering.  In a wild moment of engineering excess, I considered
barries> adding perl to mysqld to make writing the algrithms easier, but wised up
barries> quickly (has anyone done that for writing Procedure callbacks?).

We are have been talking about hiring a new developer, Arnold, to add
a foreign language API to MySQL.  The current plan is to first
construct a good API and then create a Python interface for this.
(People keep telling us that Python is very suited for task like this,
so we decided to start with a Python interface).

Arnold has the last month done some research in how to most
efficiently implement this so and I hope to get some inputs from him
soon.

barries> Ok, now the questions:

barries> - Has/is anyone done/doing anything like this?

no.

barries> - What do I need to look out for?  Is there any documentation of mysqld
barries>   guts hacking?

Only the source.

barries> - Assuming that the above approach is tenable, what would be a good way of
barries>   safely unpacking record[0] and record[1] into reasonable intermediary
barries>   structures, or should it pass things as List<Field>& instead of
barries>   List<Item>& for efficiency / ease?

This would work, but the above approach would be MUCH faster.

barries> - Is anyone interested in this?  Interested enough to contribute / test
barries>   / fund?

We at MySQL AB will of course help by answering any implementation
questions regarding this.

Regards,
Monty
Thread
Hacking triggers into 2.23.34a?barries29 Mar
  • Re: Hacking triggers into 2.23.34a?elble29 Mar
    • Re: Hacking triggers into 2.23.34a?barries29 Mar
      • Re: Hacking triggers into 2.23.34a?elble29 Mar
    • Re: Hacking triggers into 2.23.34a?Jeremy D . Zawodny29 Mar
      • Re: Hacking triggers into 2.23.34a?elble29 Mar
        • Re: Hacking triggers into 2.23.34a?barries29 Mar
          • Re: Hacking triggers into 2.23.34a?Sasha Pachev30 Mar
          • Re: Hacking triggers into 2.23.34a?barries30 Mar
            • Re: Hacking triggers into 2.23.34a?Michael Widenius31 Mar
        • Re: Hacking triggers into 2.23.34a?Sasha Pachev30 Mar
  • Re: Hacking triggers into 2.23.34a?Antony T Curtis30 Mar
  • Hacking triggers into 2.23.34a?Michael Widenius31 Mar
    • Re: Hacking triggers into 2.23.34a?barries31 Mar
      • Re: Hacking triggers into 2.23.34a?Michael Widenius1 Apr
        • Re: Hacking triggers into 2.23.34a?barries1 Apr
          • Re: Hacking triggers into 2.23.34a?barries1 Apr
            • Re: Hacking triggers into 2.23.34a?Michael Widenius1 Apr
              • Re: Hacking triggers into 2.23.34a?barries2 Apr
                • Re: Hacking triggers into 2.23.34a?Michael Widenius2 Apr
          • Re: Hacking triggers into 2.23.34a?Michael Widenius1 Apr
  • SQL92?Antony T Curtis2 Apr