Let me rephrase in a new thread, for clarity:
We can get results from an SQLite database, which by default looks like this:
Name|Company|URL
In order to make that an AppleScript list, we have to slowly parse through the column separators and row separators to result in something like:
{
{"Name", "Company", "URL"}
}
It would be much faster and easier if we could somehow create or manipulate the output from SQLite in a way that could be simply read into a list by AppleScript's "read" OSAX command:
read POSIX file "Database Search Results" as list
The question is, how can we change the output of the SQLite select statement to be in the format needed for AppleScript to read it in as a native list?
My preliminary tests show that an AppleScript list in a file contains something like:
list....
list....utxt....Nameutxt....Companyutxt....URL
where the tabs and newlines are just shown for clarity. The .... four byte sequences are the length of the subsequent list (number of items) or text (number of characters). But I'm a bit hazy on the actual format of a list in a file.
You might think it's just a simple matter of a search and replace of the "|" column separators or the newline row separators after the SQLite output but the data itself may contain those characters. We can replace the column separator using the "-separator" option in the sqlite3 command line tool. I don't know of any way to replace the row separator other than to add a column to the output.
Here is a fully self contained script that demonstrates this:
property sqlite3Path : "/usr/bin/sqlite3"
property columnSeparator : "<cSep>"
property rowSeparator : "<rSep>"
on run
set databasePath to "MyTestDatabase.sqlite"
set createData to "
create table if not exists Contacts(Id integer primary key, Name text, Company text, URL text);
delete from Contacts;
"
SQLExecute(databasePath, createData)
set myQuery to "select Name, Company from Contacts where URL not null"
SQLExecuteSelect(databasePath, myQuery)
on SQLExecuteSelect(databasePath, sqlCommand)
set sqlCommandWithSeparators to "select *, '" & rowSeparator & "' as rowSeparator from (" & sqlCommand & ");"
set shellCommand to "echo " & (quoted form of sqlCommandWithSeparators) & " | " & sqlite3Path & space & "-header -separator " & (quoted form of columnSeparator) & space & quoted form of databasePath
set recordsText to do shell script shellCommand
return recordsText
end SQLExecuteSelect
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
The output appears as:
"Name<cSep>Company<cSep>rowSeparator
Tom<cSep>BareFeet<cSep><rSep>
Steve<cSep>Apple<cSep><rSep>"
So, how could we change each <cSep> and <rSep> to be the appropriate 8 byte code (utxt.... and list.... respectively, but with the right values of ....) that the AppleScript "read file as list" OSAX expects?