• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag
 

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: sqlite question
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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


  • Follow-Ups:
    • Re: sqlite question
      • From: Jim Weisbin <email@hidden>
References: 
 >sqlite question (From: Jim Weisbin <email@hidden>)

  • Prev by Date: Re: Can't get static text
  • Next by Date: Re: sqlite question
  • Previous by thread: Re: sqlite question
  • Next by thread: Re: sqlite question
  • Index(es):
    • Date
    • Thread