Re: [sqlite] Persistent user-defined functions
Daniel Franke wrote: > Layering. Wrap sqlite3_* into your own set of functions. Create another > library, say libyourapp. Most functions will just forward the arguments to > sqlite, but others, e.g. yourapp_open_db() will not only open the database, > but also attach a couple of functions, which are also part of libyourapp. > > This additional layer won't cost too much CPU cycles and is also meaningfull > if you ever decide to switch database backends: just reimplemnt those > functions and you are back in business :) > > This is what I've done. For consistency I've also used simple macros to #define sqlite3_ functions to db_ counterparts, so there are no sqlite3_ APIs called directly from the code. The db_open also registers a couple of small C functions at the same time. Also lets us do a few neat things, like use the same base offset for binding and retrieving data as well as centralizing SQLITE_BUSY returns and error reporting. Would second Daniel's recommendation, all except for the part about switching database backends.. ;-) -P
Re: [sqlite] Persistent user-defined functions
Pam Greene wrote: I use sqlite3_create_function() to attach C++ functions to SQLite triggers. For example, I create the SQL function INSERT_HANDLER(), bound to the C++ function InsertTriggerHandler(). Then I create a trigger: CREATE TRIGGER trig AFTER INSERT ON TableName FOR EACH ROW WHEN (INSERT_HANDLER(new.A, new.B) NOTNULL) BEGIN SELECT RAISE(FAIL, 'Error during insert trigger.'); END The trouble is, although the trigger persists indefinitely, the INSERT_HANDLER() function is only defined for as long as the database connection it was created with exists, and only when the INSERT that causes the trigger uses the same connection. Is there any way to create a more broadly available, persistent custom function, short of building it into sqlite? Or does anyone have another suggestion to solve the implicit problem? Thanks, - Pam I can imagine that a possible solution would be to override the appropriate Sqlite function definition table. I haven't looked deeply, but imagine that it might even be possible with a regular user-defined function so that no Sqlite code is touched. It would be a great addition. JS
Re: [sqlite] Persistent user-defined functions
On Friday 07 April 2006 22:39, Pam Greene wrote: > Is there any way to create a more broadly available, persistent custom > function, short of building it into sqlite? Or does anyone have > another suggestion to solve the implicit problem? Layering. Wrap sqlite3_* into your own set of functions. Create another library, say libyourapp. Most functions will just forward the arguments to sqlite, but others, e.g. yourapp_open_db() will not only open the database, but also attach a couple of functions, which are also part of libyourapp. This additional layer won't cost too much CPU cycles and is also meaningfull if you ever decide to switch database backends: just reimplemnt those functions and you are back in business :) Daniel
Re: [sqlite] Persistent user-defined functions
On 4/7/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > On 4/7/06, Pam Greene <[EMAIL PROTECTED]> wrote: > > I use sqlite3_create_function() to attach C++ functions to SQLite > > triggers. For example, I create the SQL function INSERT_HANDLER(), > > bound to the C++ function InsertTriggerHandler(). Then I create a > > trigger: > > > > CREATE TRIGGER trig AFTER INSERT ON TableName > > FOR EACH ROW > > WHEN (INSERT_HANDLER(new.A, new.B) NOTNULL) > > BEGIN > > SELECT RAISE(FAIL, 'Error during insert trigger.'); > > END > > > > The trouble is, although the trigger persists indefinitely, the > > INSERT_HANDLER() function is only defined for as long as the database > > connection it was created with exists, and only when the INSERT that > > causes the trigger uses the same connection. > > > > Is there any way to create a more broadly available, persistent custom > > function, short of building it into sqlite? Or does anyone have > > another suggestion to solve the implicit problem? > > I don't think there is. What does your insert_handler() do? Lots of things incompatible with doing it all in SQL, unfortunately. This is for a full-text indexing system, so the triggers split the text in a document column into words, optionally process (stem or case-normalize) them, and add them into parallel token tables along with some useful meta-information. (Analogous DELETE and UPDATE triggers keep everything properly in sync.) - Pam
Re: [sqlite] Persistent user-defined functions
On 4/7/06, Pam Greene <[EMAIL PROTECTED]> wrote: > I use sqlite3_create_function() to attach C++ functions to SQLite > triggers. For example, I create the SQL function INSERT_HANDLER(), > bound to the C++ function InsertTriggerHandler(). Then I create a > trigger: > > CREATE TRIGGER trig AFTER INSERT ON TableName > FOR EACH ROW > WHEN (INSERT_HANDLER(new.A, new.B) NOTNULL) > BEGIN > SELECT RAISE(FAIL, 'Error during insert trigger.'); > END > > The trouble is, although the trigger persists indefinitely, the > INSERT_HANDLER() function is only defined for as long as the database > connection it was created with exists, and only when the INSERT that > causes the trigger uses the same connection. > > Is there any way to create a more broadly available, persistent custom > function, short of building it into sqlite? Or does anyone have > another suggestion to solve the implicit problem? I don't think there is. What does your insert_handler() do?
[sqlite] Persistent user-defined functions
I use sqlite3_create_function() to attach C++ functions to SQLite triggers. For example, I create the SQL function INSERT_HANDLER(), bound to the C++ function InsertTriggerHandler(). Then I create a trigger: CREATE TRIGGER trig AFTER INSERT ON TableName FOR EACH ROW WHEN (INSERT_HANDLER(new.A, new.B) NOTNULL) BEGIN SELECT RAISE(FAIL, 'Error during insert trigger.'); END The trouble is, although the trigger persists indefinitely, the INSERT_HANDLER() function is only defined for as long as the database connection it was created with exists, and only when the INSERT that causes the trigger uses the same connection. Is there any way to create a more broadly available, persistent custom function, short of building it into sqlite? Or does anyone have another suggestion to solve the implicit problem? Thanks, - Pam
