I wrote a filterArray() function with a couple dependencies a while ago which
uses a memory sqLite database to convert an array to a database, query that
database, then return the results as an array.
I have a new update to filterArray() which allows for a select clause. I found
myself wanting to find the maximum value of a column, so I update the function.
For an example lets say I wanted the maximum value for the serviceid key in a
numbered array (tRecordsA). I could call it like this:
put empty into tConditions
put "max(serviceid) as serviceid" into tSelectClause
put filterArray(tRecordsA, tConditions, tSelectClause) into tFoundDataA
Since I do not need a where clause here, as tRecordsA only contains the data I
want, but a where clause could also be defined. Here are the handlers if anyone
is interested. If I missed any dependencies ley me know and I will pass those
along as well.
Bob S
function filterArray pArrayDataA, pConditions, pSelectClause
put the defaultFolder & "/" & "tempdatabase.db" into tDBName
put arrayToSQLite(pArrayDataA, tDBName, "arraydata") into tDBID — uses
dependency
if pSelectClause is empty then \
put "*" into pSelectClause
put "select " & pSelectClause & " from arraydata" into tQueryString
if pConditions is not empty then \
put " where" && pConditions after tQueryString
try
put revQueryDatabase(tDBID, tQueryString) into tCursorID
put cursorToArray(tCursorID) into tFilteredDataA — uses dependency
return tFilteredDataA
catch tError
return empty
end try
end filterArray
FUNCTION arrayToSQLite pArrayDataA, pDBFile, pDBName, pDBID, pBinaryColumns
put the keys of pArrayDataA into tArrayKeys
sort tArrayKeys numeric ascending
IF pDBFile is empty THEN put ":memory:" into pDBFile
IF pDBName is empty THEN put "arraydata" into pDBName
TRY
if pDBID is empty then \
put revOpenDatabase("sqlite", pDBFile) into pDBID
IF "Error" is in pDBID THEN
return empty
END IF
-- attempt to set the encoding
put "PRAGMA encoding = 'UTF-16'" into tSQL
revExecuteSQL pDBID, tSQL
-- attempt to drop the table
put "drop table " & pDBName into tDropSQL
revExecuteSQL pDBID, tDropSQL
put the result into tResult
CATCH tError
answer tError
IF the environment is "development" THEN exit to top ELSE quit
END TRY
-- create the table
put "create table" && quote & pDBName & quote \
& cr into tCreateCommand
put "(" & quote & "recordid" & quote && "NUMERIC PRIMARY KEY UNIQUE, " \
& cr AFTER tCreateCommand
put the keys of pArrayDataA [1] into tRecordKeyList
filter lines of tRecordKeyList without "recordid"
REPEAT for each line tRecordKey in tRecordKeyList
if pArrayDataA [1] [tRecordKey] is an array or \
pArrayDataA [1] [tRecordKey] begins with "Salted__" then
put "BLOB" into tColumnType
else
put VARCHAR into tColumnType
end if
put quote & tRecordKey & quote && tColumnType & "," && cr AFTER
tCreateCommand
END REPEAT
delete char -3 to -1 of tCreateCommand
put ")" AFTER tCreateCommand
TRY
revExecuteSQL pDBID, tCreateCommand
put the result into tResult
IF tResult is not 0 THEN breakpoint
CATCH tError
breakpoint
END TRY
put 1 into tRecordCounter
put "recordid" & cr & tRecordKeyList into tColumns
repeat with i = 1 to the number of lines of tColumns
put ":" & i into item i of tColumnList
end repeat
put "(" & tColumnList & ")" into tColumnList
-- insert data
REPEAT for each line tKey in tArrayKeys
put 1 into tColumnCounter
put pArrayDataA [tKey] into tRecordDataA
put tRecordCounter into tQueryDataA [1]
REPEAT for each line tRecordKey in tRecordKeyList
add 1 to tColumnCounter
if tRecordDataA [tRecordKey] is an array then
put arrayEncode(tRecordDataA [tRecordKey]) into tValue
else
put tRecordDataA [tRecordKey] into tValue
end if
put tValue into tQueryDataA [tColumnCounter]
END REPEAT
put "insert into" && pDBName && "VALUES" && tColumnList into tInsertSQL
TRY
revExecuteSQL pDBID, tInsertSQL, "tQueryDataA"
put the result into tResult
if the result is not a number then breakpoint
CATCH tError
breakpoint
END TRY
add 1 to tRecordCounter
END REPEAT
return pDBID
END arrayToSQLite
FUNCTION cursorToArray pCursorID
put revNumberOfRecords(pCursorID) into tNumberOfRecords
if tNumberOfRecords = 0 then \
return empty
put revDatabaseColumnCount(pCursorID) into tColumnCount
put revDatabaseColumnNames(pCursorID) into tColumnNames
REPEAT forever
add 1 to tRecordCount
REPEAT with i = 1 to tColumnCount
put revDatabaseColumnNumbered(pCursorID, i) into tColumnValue
put tColumnValue into aCursorArray [tRecordCount] [item i of
tColumnNames]
END REPEAT
revMoveToNextRecord pCursorID
if revQueryIsAtEnd(pCursorID) then \
exit repeat
END REPEAT
return aCursorArray
END cursorToArray
_______________________________________________
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
