This is the code with a couple of dependencies. I think that’s all the
fiddlybits. If I missed something let me know.
Bob S
function filterArray pArrayDataA, pConditions
put the defaultFolder & "/" & "tempdatabase.db" into tDBName
put arrayToSQLite(pArrayDataA, tDBName, "arraydata") into tDBID
put "select * from arraydata where" && pConditions into tQueryString
try
put revQueryDatabase(tDBID, tQueryString) into tCursorID
put cursorToArray(tCursorID) into tFilteredDataA
return tFilteredDataA
catch tError
return empty
end try
end filterArray
FUNCTION arrayToSQLite pArrayDataA, pDBFile, pDBName, pDBID, pBinaryColumns
— The only argument required is pArrayData. Defaults will be used for the
rest
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
> On Mar 25, 2024, at 3:06 PM, Alex Tweedly via use-livecode
> <use-livecode@lists.runrev.com> wrote:
>
> Bob - I think you've mentioned these functions (and posted code, or a pointer
> to code, for them) before (but I couldn't find it). Any chance you could
> re-post (or just send to me, or ...)
>
> Mike - I couldn't see in the thread *why* you want to use a dg ather than a
> pg ? Is there a missing capability you need ? Or some non-obvious (to me)
> reason to avoid pg?
>
> Thanks,
>
> Alex.
>
_______________________________________________
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
