UPDATE: filterArray()

[Bob Sneidar]

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