0

I've have two event queues(table mapped queues) based on two different states of same data stored in two different tables. The events are generated on create/update/delete on both the tables.

Constrain is that both the tables have to be in sync. The create/update/delete in one table has to be reflected into other table.

So the question is that should i use trigger to queue events in table or application/object to queue events in table? and Why?

Note:

  1. Update on either is capable of generating 3 different types of events. So on application layer extra diff logic would be required to generate correct event.
  2. The Negative thing in trigger is that it will introduce duplicate events from both sides. i.e. if some event is processed on one table it will create an event for processing on other table.
3

I wrote this stuff a long time ago for a C++ application and made a post on my blog. Here's a quick summary (since the original article is quite long and contains file attachments):

In SQLite you write callbacks in raw C++:

Header file (file.h)

#include <iostream>
#include <sqlite3.h>
#include <cstdlib>
#include <assert.h>

void memberfunction(const unsigned char* text1, 
                    const unsigned char* text2, 
                    const unsigned char* text3);
void mcallback(sqlite3_context* context, int argc, sqlite3_value** argv);

It defines the two additional functions we'll need for the next step (actually our C++ side function and a SQLite stored procedure).

Implementation file (file.cpp)

This function will be called from our trigger from the DB.

void memeberfunction(const unsigned char* text1, 
                     const unsigned char* text2, 
                     const unsigned char* text3)
{
    cout << "inserted: " << text1 << "  " << text2 << "  " << text3 << "n";
}

And now a SQLite stored procedure to call our function; a SQLite trigger will invoke this.

void mcallback(sqlite3_context* context, int argc, sqlite3_value** argv)
{
    assert(argc == 3);
    //SQLite types must be converted to C++ types
    const unsigned char* text1 = sqlite3_value_text(argv[0]);
    const unsigned char* text2 = sqlite3_value_text(argv[1]);
    const unsigned char* text3 = sqlite3_value_text(argv[2]);
    memeberfunction( text1, text2, text3);
}

We don't have to limit the function to just a simple print; this function could have (for example) moved data to another table.

Register the stored procedure to the DB

error = sqlite3_create_function(conn, "mcallback", 3, SQLITE_UTF8, NULL, 
                                      &mcallback, NULL, NULL);

Create the SQLite trigger

We need the stored procedure since triggers are built on top of them.

error = sqlite3_exec(conn, "create temp trigger callbacktrigger "
                           "after insert on hello for each row "
                           "begin "
                             "select mcallback(new.first_column, "
                             "new.second_column, new.exclamation_pt); "
                           "end;", 0, 0, 0);

The trigger is only temporary. This way it won't stick around in the database after execution terminates (of our whole program). Our callback won't be around after then anyway and the trigger could (and will, given it's nature) cause major problems if we use the database outside this application.

  • I think you misunderstood the question. The Question is that the events should be added from application layer or sqlite layer. I've already thought of having callbacks like your blog's example is having. Currently I'm using a 3rd party wrapper over sqlite and adding a function is not supported in that. I have to enhance the 3rd party code to support that(tight on dead line...). – bikram990 Feb 21 '14 at 19:50
  • Events should live in SQLite as triggers. That way they can execute table changes from any vector (various ways to update data). The Trigger links to stored procedures to mutate other tables or achieve other results. Finally if you need to let you application know of events you can call the application code from inside the DB. If the deadline is tight application level code is much easier to write, but more error prone. If you wrap the access to SQLite you can code some logic in there to make the necessary changes in other parts of the DB. Triggers are easy to maintain as one point of control. – Dan Sabin Feb 21 '14 at 20:06
  • Thankx for elaborating it here. Sure will discuss more on this with my team and update. My Original idea was this only but as i've to modify 3rd party code for this, so dropped the idea. – bikram990 Feb 22 '14 at 6:25
  • Yea prolly not a good idea to pull this off unless you have sqlite around. The software approach should work fine. Just harder to maintain – Dan Sabin Feb 22 '14 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.