Can we execute operating system command from sqlite3

Question

My requirement is: To run a shell script when a new entry is added to a table.

Is there any way to add a trigger to run the shell script on inserting new row?

---

Well, the situation is:

I am using the db of a different program. Say Program X creates and populates xyz.db and I just need to execute a small script whenever program X inserts new row into a table.

Currently my shell script runs infinitely over a loop and check if there are any new rows and process it, since the inserts are not very frequent, running a script continuously is not a good choice.

My question is similar to can we execute unix command from oracle10g procedure but i need to do the same in sqlite3

Answer 1

To begin with I am in total agreement with the comments, this sounds like a poor architecture. If the shell script operation is not independent of the program interacting with the database then this call should be embedded in your code by calling some sort of system command, in the driver used by your program or as part of the main code.

In Java it could be done with ProcessBuilder class, system(<command>) method in ruby and os.system(<command>) in python.

In oracle it is possible to write triggers in java which you could use to make a system call however this functionality doesn't exists in sqlite.

If it is completely necessary I would suggest writing a wrapper round your sqlite db driver and making the system call in there.

Perhaps you could expand on what you actually require to do and we could give alternative approaches

Update:

You could create a callback function in c/c++ using the c/c++ sqlite interface (see Data Change Notification Callbacks in http://www.sqlite.org/capi3ref.html) which you can call in your trigger

CREATE TRIGGER <trigger name>
    INSERT ON <table_name>
    BEGIN
        SELECT callback_function()
    END;

You could then use this callback_function to invoke your shell script, sounds relatively complex, but quite interesting.

Answer 2

I just want to be sure you're asking the right question ...

As you say

check if there are any new rows and process it, ... (infrequent) ... running a script continuously is not a good choice.

so you currently have something like

while true ; do
   $scriptPath/sqlCheckForNewValues.sh
    sleep ${sleepSecs:-60}
done

?

(If you don't have a sleep, then agreed, this not optimal, and note that the sleep value can be tuned on check times to exceed user expectations (so maybe 300 secs is good enough.)

While I agree in principal with the objections on this architecture, only you know the true priorities (which goes to needing more background info on what you're trying to accomplish).

From what you have described, you're going to spend a lot of time figuring out and coding a solution to this issue, when it probably isn't taking up any more than .001% of your overall system processing time.

Is this a nice to have, or can you really quantify that this is worth the expenditure in time? Don't you have other features that this time would be better spent on?

IHTH