perform remote sqlite command

Question

If this should be moved to the DBA exchange, I apologize. Feels more like linux than DB to me, so here goes:

I've got some machines that run scheduled cron jobs every night and email me the output. I do not want emails for things like this. In general I think the way we use email is broken, but that's another story.

So I started thinking that I could keep a central SQLite database that stored information about when the jobs started, and finished, and maybe even the output. Then I could just build a webpage that queries that and let's me know was going on last night.

So I came up with a simple schema and can run this command at the beginning of a script.

sqlite3 dbname.db "UPDATE data SET LastStart = DATETIME('NOW') WHERE TaskName = 'taskname'"

So now I have a record that states that my job started and at what time. Hooray. Then I can run a similar command to put the time that the job ends.

So. That works great if the database and the tasks are on the same machine. I go to another machine and need to update the sqlite database.... How can I do that efficiently?

I tried this

ssh [email protected] 'sqlite3 /home/aaron/dbname.db "UPDATE data SET LastStart = DATETIME('NOW') WHERE TaskName = 'taskname'"'

But that returns:

Error: no such column: NOW

I tried some variations but didn't get anywhere.

Am I close? Should I be doing something totally different? Am I reinventing the wheel?

Solution

That's all comes from quoting. Try this one:

ssh [email protected] 'sqlite3 /home/aaron/dbname.db "UPDATE data SET \
LastStart = DATETIME('''NOW''') WHERE TaskName = '''taskname'''"'

ps. You need to quote NOW, otherwise sqlite will try to find column with such name. But your quotes ' will be eaten by quotes from ssh. You can't escape ', therefore three quotes ''' are used (the first off ssh quote, second it the quote you need to pass to sqlite, and the last one open ssh quote again).

pps. Furthermore you can inverse quotes like this:

ssh [email protected] "sqlite3 /home/aaron/dbname.db \"UPDATE data SET \
LastStart = DATETIME('NOW') WHERE TaskName = 'taskname'\""

Comments

[LVLAaron]

The first command you gave does not work; same issue.

[LVLAaron]

Second command, however, works great.