SQLite to table view: is there a quicker way?

I need to display the contents of an SQLite table (+3000 records) in a table view. Does anyone know a quick way to coerse SQLite output to a list of lists so I can put it in a table view?

The only way I know so far is to create the list, record by record (takes about 15 seconds on my MacBookPro, I hope there is a quicker way). Currently I create the list the way:

set DBs to "/Users/bart/Desktop/Phone.db"
set Sql_call to "sqlite3 -list " & DBs & space
set Query_String to "SELECT * FROM Numbers"
set theResult to every paragraph of (do shell script (Sql_call & quoted form of Query_String))
--Result: {"Bart|1234", "Fred|5678"}

set theList to {}
set {ASTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, "|"}
repeat with r in theResult
	set end of theList to (r's every text item)
end repeat
set AppleScript's text item delimiters to ASTID
return theList
-- Result: {{"Bart", "1234"}, {"Fred", "5678"}}

Any help would be greatly appreciated.
Thanks in advance,

Bart

This cuts it down to 5 seconds.

on makeListOfSQLData(theData)
	set {ASTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, "|"}
	script listRef
		property theList : {}
	end script
	repeat with d in theData
		set end of listRef's theList to (d's every text item)
	end repeat
	set AppleScript's text item delimiters to ASTID
	return listRef's theList
end makeListOfSQLData


set DBs to "/Users/bart/Desktop/Phone.db"
set Sql_call to "sqlite3 -list " & DBs & space
set Query_String to "SELECT * FROM Numbers"
set theResult to every paragraph of (do shell script (Sql_call & quoted form of Query_String))
--Result: {"Bart|1234", "Fred|5678"}

set resultAsList to my makeListOfSQLData(theResult)
-- Result: {{"Bart", "1234"}, {"Fred", "5678"}}

But still: is there a quicker way?

Bart

Of course, you’re assuming that your data (ie the value of any cell) doesn’t contain any pipe characters or returns or linefeeds. Your scripts fail if any of these characters are present.

You are also allowing nulls to be treated the same way as empty strings, but that’s fine since you are just displaying the contents in an Interface Builder table view.

Tom
BareFeet

OK, try this modified version of the handlers I use. SQLExecute is for executing any old SQL, returning any result as just text.

SQLExecuteSelect is for executing a select statement and returning a list of lists (rows of field values). It uses a custom separator between the fields, something that should never appear in the actual data. It also adds a separator to the end of each row, so the row end can be distinguished from new lines tat appear in the data.

property sqlite3Path : "/usr/bin/sqlite3"
property fieldSeparator : character id 2
property recordSeparator : character id 3

on SQLExecute(databasePath, sqlCommand)
	set shellCommand to "echo " & (quoted form of sqlCommand) & " | " & sqlite3Path & space & quoted form of databasePath
	set resultText to do shell script shellCommand
	return resultText
end SQLExecute

on SQLExecuteSelect(databasePath, sqlCommand)
	-- 2008 BareFeet http://www.tandb.com.au
	set sqlCommandWithSeparators to "select *, '" & recordSeparator & "' from (" & sqlCommand & ");"
	set shellCommand to "echo " & (quoted form of sqlCommandWithSeparators) & " | " & sqlite3Path & space & "-separator " & (quoted form of fieldSeparator) & space & quoted form of databasePath
	set recordsText to do shell script shellCommand
	script speedyObject
		property recordList : {}
	end script
	if recordsText is not "" then
		set oldDelimiters to AppleScript's text item delimiters
		set AppleScript's text item delimiters to (fieldSeparator & recordSeparator & return)
		set speedyObject's recordList to text items in recordsText
		set last item in speedyObject's recordList to text 1 thru -(1 + (length of (fieldSeparator & recordSeparator))) in last item in speedyObject's recordList
		set AppleScript's text item delimiters to fieldSeparator
		set recordCount to (count speedyObject's recordList)
		repeat with recordN from 1 to recordCount
			set item recordN in speedyObject's recordList to text items in item recordN in speedyObject's recordList
		end repeat
		set AppleScript's text item delimiters to oldDelimiters
	end if
	return speedyObject's recordList
end SQLExecuteSelect

To test it:

on run
	set databasePath to "MyTestDatabase.sqlite"
	set createData to "
create table if not exists Contacts(Id integer primary key, Company text collate nocase, Name text collate nocase, URL text collate nocase);
delete from Contacts;
insert into Contacts(Name, Company, URL) values('Tom', 'BareFeet', 'http://www.tandb.com.au');
insert into Contacts(Name, Company, URL) values('Steve', 'Apple', 'http://www.apple.com');
"
	SQLExecute(databasePath, createData)
	set myQuery to "select Name, Company from Contacts where URL not null"
	SQLExecuteSelect(databasePath, myQuery)
end run

which returns:

{{“Tom”, “BareFeet”}, {“Steve”, “Apple”}}

Or to try it with your database:

on run
	set DBs to "/Users/bart/Desktop/Phone.db"
	set Query_String to "SELECT * FROM Numbers"
	SQLExecuteSelect(DBs, Query_String)
end run

Please let me know how the speed compares to your other methods. At the very least, I think this method will cater for all data, whereas the other methods won’t (eg if the data includes pipes or returns).

This method (and others mentioned here) return all values as strings. That’s fine when you’re dumping the result directly into the contents of a table view. But I should point out that it’s not so good if you are processing the data and need to discern strings from integers from nulls etc.

Tom
BareFeet