Re: sqlite question
Re: sqlite question
- Subject: Re: sqlite question
- From: Axel Luttgens <email@hidden>
- Date: Sun, 26 Apr 2015 11:39:23 +0200
> Le 16 avr. 2015 à 18:11, Jim Weisbin a écrit :
>
> I have a script which does the following:
>
> […]
>
> The script is already working, and fairly quick on a gigabit network, but the sqlite query is pretty slow when logged in remotely.
>
> My question is, would it be more efficient to query the sqlite table for each file, instead of grabbing all the text at once?
>
> In other words, I would have to construct a shell script that does this (in this case you can see that the name contains double quotes, and all of them do):
>
> sqlite3 <path.to.db> 'select file_name from player_view where file_name is '\''A&E MV "The Unfolding".m4a'\'';'
Instead of calling sqlite3 several times, once for each file name, I would be tempted to let it do the hard job by making use of the IN operator, especially when accesses to the database file prove quite slow.
The immediate benefit would be that you won’t have to build and search very long lists (thousands of items) anymore.
As far as the SELECT … IN itself is concerned, I guess that in the worst case execution speeds would be comparable to those of a full SELECT.
In the best case (e.g. when column file_name is indexed), this could lead to nice speed increases.
Of course, you would have to slightly change the logics of your code: you’ll have to build a list of file names based on the user’s selection, then query the database.
> Exactly how to escape all those quotes so I can "do shell script" will be a challenge, so, before I attempt it, I’d like to know if the extra effort is worth it.
Hmmm… That’s an interesting one. ;-)
SQLite’s preferred syntax for building string literals makes use of single quotes; additionally, the docs also specifies:
"A string constant is formed by enclosing the string in single
quotes ('). A single quote within the string can be encoded by
putting two single quotes in a row […]".
The only character of concern thus seems to be the single quote.
Provided we manage to generate a valid SQL statement string, escaping it for passing it as a shell argument should be a job for AppleScript’s "quoted form of".
OK, let’s try:
— Escape a string for use in a SQLite literal.
on escapeString(str)
local TIDs
set TIDs to AppleScript's text item delimiters
set AppleScript's text item delimiters to {"'"}
set str to text items of str
set AppleScript's text item delimiters to {"''"}
set str to str as text
set AppleScript's text item delimiters to TIDs
return str
end escapeString
— Build the SELECT statement based on a non empty list of file names.
on buildSelect(fnames)
local sql, k
set sql to "SELECT file_name FROM player_view WHERE file_name IN ("
set sql to sql & "'" & escapeString(item 1 of fnames) & "'"
repeat with k from 2 to count (fnames)
set sql to sql & ", '" & escapeString(item k of fnames) & "'"
end repeat
sql & ")"
end buildSelect
— Query the database, with:
— dbPath: the database file’s posix path
— fnames: a non-empty list of file names to look for
on queryDB(dbPath, fnames)
do shell script ¬
"/usr/bin/sqlite3 " & quoted form of dbPath & ¬
" " & quoted form of buildSelect(fnames)
end queryDB
HTH,
Axel
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden