I come begging sanity checks and advice, previous experience, existing
code etc.
I am considering adding some modest C++-only trigger support to mysql
3.23.34a (or whatever's current by the time I finish). My motivation
is at the bottom of this message.
I'm considering creating a few C++ classes like PostUpdateTrigger,
PostInsertTrigger and PreDeleteTrigger (these are the ones we need,
YMMV). These would be similar in aspect to the Procedure class in that
they would have a few virtual functions that act as callbacks and you'd
have to write everything in C++ and compile them in to mysqld.
Instances of these classes could be pushed on to some linked lists
rooted in new members we'd add to the st_table structure.
Creating the class hierarchy, finding places to hang lists of instances
from, and arranging for these intances' methods to be called all seem
feasible (sanity check, please, I've not hacked in mysqld before :-).
Unpacking the records into Items or perhaps C++ "string" instances
seems like it should be pretty easy for a mysqld internals guru.
What would this look like in code? For instance, in mysql_update() down
around sql_update.cc, line 234 in 3.23.34a, I'm thinking of adding
code that looks roughly like the +ed lines here:
+ int update_triggers_present = table->update_triggers_list ;
while (!(error=info.read_record(&info)) && !thd->killed)
{
if (!(select && select->skipp_record()))
{
store_record(table,1);
if (fill_record(fields,values))
break; /* purecov: inspected */
found++;
if (compare_record(table, query_id))
{
if (!(error=table->file->update_row((byte*) table->record[1],
(byte*) table->record[0])))
{
updated++;
if (!--limit && using_limit)
{
error= -1;
break;
}
}
else if (handle_duplicates != DUP_IGNORE ||
error != HA_ERR_FOUND_DUPP_KEY)
{
table->file->print_error(error,MYF(0));
error= 1;
break;
}
+ if (update_triggers_present)
+ {
+ UpdateTrigger *ut = table->update_triggers ;
+ while (ut)
+ {
+ List<Items> before_items ; // I'd be happy with vector<string>...
+ List<Items> after_items ;
+ // Build lists of Items by unpacking table->record[0] and
+ // table->record[1], don't know how to do this.
+ ut->RowUpdated( before_items, after_items ) ;
+ ut = ut->NextTrigger() ;
+ }
+ }
}
}
}
I'm ass_u_ming that mysql_delete() and mysql_insert() can be modified
similarly, though I've not dug in to those quite this far.
I also consdered making a shim table handler that would pass through
things and call any triggers that need be called, but that seems like
over-engineering. In a wild moment of engineering excess, I considered
adding perl to mysqld to make writing the algrithms easier, but wised up
quickly (has anyone done that for writing Procedure callbacks?).
Ok, now the questions:
- Has/is anyone done/doing anything like this?
- What do I need to look out for? Is there any documentation of mysqld
guts hacking?
- Assuming that the above approach is tenable, what would be a good way of
safely unpacking record[0] and record[1] into reasonable intermediary
structures, or should it pass things as List<Field>& instead of
List<Item>& for efficiency / ease?
- Is anyone interested in this? Interested enough to contribute / test
/ fund?
Many thanks,
Barrie
Motivation:
We have an application with some highly cyclic data in 5 or 6 tables.
We have some iterative algorithms running using lots of SQL statements
and temporary tables that calculate results by traversing this cyclic
data to various depths. This can take some time (5-6 seconds per run of the
algorithm). We want to stick with an SQL managed database, because
that's where our strengths are and where we can hire people and buy /
download supporting software easily (ie going to object databases is
tempting but not temptin enough).
To make things faster, we want to build an in-memory C/C++ data
structure that models the relationships between a few million records of
this very interrelated data.
This means loading the primary keys and forign keys in all records from
the 5 or 6 tables (let's call two of them "foo" and "bar") into C++
containers like vector<Foo>, vector<Bar> inside mysqld and replacing
foreign keys with indexes into the appropriate vectors. let's say foo
has a column "bar_id" and bar has a column "foo_id", and the data set
has many, many foo/bar/foo cycles.
This is all to allow us to subclass Procedure to allow us to add some
calculated fields to rows as they are extracted:
create temporary table tmp_foo select * from foo procedure add_stats() ;
where add_stats() takes the primary key foo.foo_id for each result row,
does some traversal of the vector<Foo> and vector<bar> in our in
memory cache, then adds a few columns of statistics to each record
returned. We have a handful of add_stats()-like Procedures in mind,
some of which involve traversing hundreds of thousands of foo/bar
cycles.
We want to do 100's of queries / second this way. Or more :-). And we
don't want to reload the in-memory structures often or let them grow
more than a few seconds stale. Ideally we want changes made at the
mysql command line to be captured to, as we occasionally have to
hand-tweak the data. Yada, yada, yada.
