FindIndex question

[Bob Sneidar]

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 at 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.
>