I am attempting to create a database using Database Events with records that contain a list of numbers. Although Database Events can create a field with a string value, it fails with a list. I would appreciate some ideas on how to insert a list. The Database Events defines a value of a field as anything, so I thought list would be feasible.
--set variable path_to_AMA_Ratings to HFS path to containing folder
set path_to_AMA_Ratings to ((path to documents folder from user domain) as string) & "AMA Impairment Rating"
-- set variable db to HFS path to database file
set db to path_to_AMA_Ratings & ":AMA ROM Ratings.dbev"
--set variable db_posix to posix path of database
set db_posix to POSIX path of db
tell application "Database Events"
--if database does not exist, then create
if (exists of database db_posix) is false then
set db to make new database with properties {name:"AMA ROM Ratings", location:(path_to_AMA_Ratings)}
end if
tell database db_posix
--if record does not exist then create
if (exists of record "Thumb_CMC_Add") is false then
set thisRec to make new record with properties {name:"Thumb_CMC_Add"}
tell thisRec
--the following line fails to create a value containing a list, but returns false
make new field with properties {name:"Impairment Rating", value:{0, 0, 1, 3, 4, 6, 8, 13, 20}}
end tell
end if
close saving yes -- this clears the database out of memory
end tell
end tell
My overall goal is to query a specific item ranging from the first through the last of that field and returns its value.
I am not very surprised that lists, doesn’t work, although I haven’t tested it myself. My guess is that anything that goes for sqllite will go for database events.
However, if you really want to store an array with numbers in a field in a record, then you can build lists of numbers into a string by using some special separator.
This is not a fully worked through solution, but it should provide you with the concept for extracting values.
set mstr to "01,3|24|52,5|199"
tell (a reference to AppleScript's text item delimiters)
set _tids to contents of it
set contents of it to "|"
set nlist to text items of mstr
repeat with anum in nlist
if (anum as number) is 1.3 then
set Bingo to contents of anum
exit repeat
end if
end repeat
set contents of it to _tids
end tell
When you build it the array, then you of course, just concatenate a separator and a value at the end of a string.
I have also discovered, that I have to use a variable, in the “set field value clause”, that is, I first assign what I want to assign to a value of a field, to a variable, and then I set the value to the variable in the field.
Maybe kel’s way work perfectly fine, it would be a bit easier that way, and I see no reason why it shouldn’t work.
Here is the handler for the opposite coercion, which may need some adjustment.
set ml to {0, 0, 1, 3, 4, 6, 8, 13, 20}
set ms to makeString for ml
log ms
--> "{0, 0, 1, 3, 4, 6, 8, 13, 20}"
to makeString for aList
local a
tell aList
try
set a to name of aList
on error e
return (text 19 thru -2 of e)
end try
end tell
end makeString
Come to think of it, it might be better to write the comma delimited text and add the curly braces when reading. Surely, you can parse the text in any style, but that’s not as much fun.
I see that the Database Events field value can assigned a text with numbers separated by commas. The text in this field can be captured by a variable and can be parsed via McUsrII’s loop back to a list when the value is retrieved.
It is not a particularily difficult or complex technique when it comes to make a database in general work for you.
An alternative that may be more feasible, if this list constitutes the most of your data, is to serialize a list of lists, by writing it as a list to file, and reading it back, but then, you must see to that the values of the list are in place, and not any referenced object.
