Hi all.
Here's a new toy for everyone to play with. It takes a numbered array of
key/value pairs and converts it to a MEMORY database. Why you ask? So you can
query against it of course!
In my particular case I am getting an array of every object on a card EXCLUDING
objects that are elements of a datagrid (that would be way too much data for me
to care about) so I can query against it. For instance, given one of the
columns is the name of the object and I want all the images, I can query for
all image records:
select * from arraydata where name LIKE 'image %'
You can see how handy this would be, rather than iterating through every
element of an array to find ONLY the array records you want.
Here's the code:
function arrayToMemoryDB aArrayData
put the keys of aArrayData into tArrayKeys
sort tArrayKeys numeric ascending
put ":MEMORY:" into tDBFile
put revOpenDatabase("sqlite", tDBFile) into tDBID
put "drop table arraydata" into tDropSQL
try
revExecuteSQL tDBID, tDropSQL
put the result into tResult
catch tError
end try
-- create the table
put "create table" && quote & "arraydata" & quote & cr \
into tCreateCommand
put "(" & quote & "recordid" & quote && "NUMERIC PRIMARY KEY NOT NULL
UNIQUE, " & cr \
after tCreateCommand
put the keys of aArrayData [1] into tRecordKeyList
repeat for each line tRecordKey in tRecordKeyList
put quote & tRecordKey & quote && "VARCHAR, " & cr after tCreateCommand
end repeat
delete char -3 to -1 of tCreateCommand
put ")" after tCreateCommand
try
revExecuteSQL tDBID, tCreateCommand
put the result into tResult
if tResult is not 0 then breakpoint
catch tError
breakpoint
end try
-- insert data
repeat for each line tKey in tArrayKeys
put aArrayData [tKey] into aRecordData
put 1 into tCounter
put "recordid" into item tCounter of tColumns
put ":" & tCounter into item tCounter of tColumnData
put tKey into aColumnData [tCounter]
repeat for each line tRecordKey in tRecordKeyList
add 1 to tCounter
put tRecordKey into item tCounter of tColumns
put ":" & tCounter into item tCounter of tColumnData
put aRecordData [tRecordKey] into aColumnData [tCounter]
end repeat
put "(" & tColumns & ")" into tColumns
put "(" & tColumnData & ")" into tColumnData
put "insert into arraydata" && tColumns && "VALUES" && tColumnData
into tInsertSQL
replace quote with "\" & quote in tInsertSQL
replace "'" with quote in tInsertSQL
try
revExecuteSQL tDBID, tInsertSQL, "aColumnData"
put the result into tResult
if tResult is not 1 then breakpoint
catch tError
breakpoint
end try
end repeat
return tDBID
end arrayToMemoryDB
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
