SQLite database routines #database,sqlite

SQLite database routines

--custom properties of stack
--cpAppPath : path to app
--cpDatabaseID : current active database connection number
--cpDataBaseName : name of database to access


on openCard
   mobileOrientation
   getAppPath
   nameDataBase
   connectToDB
end openCard





--cpPath is the app path for testing and documents when live
command getAppPath
   
   local tMyPath, tTestOption
   
   ##------------------SET THIS-----------------------
   put "dev" into tTestOption --development
   --put "live" into tTestOption --live deployment
   ---------------------------------------------------
   
   if tTestOption is "dev" then
      set the itemDelimiter to "/"
      put (item 1 to -2 of the effective fileName of this stack) & "/" into tMyPath
      set the cpAppPath of this stack to tMyPath
   end if
   
   if tTestOption is "live" then
      put specialFolderPath("documents") & "/" into tMyPath
      set the cpAppPath of this stack to tMyPath
   end if
   
end getAppPath



command mobileOrientation
   
   if the platform is "android" then
      set the fullscreenmode of this stack to "showAll" 
      mobileSetAllowedOrientations "portrait,portrait upside down"   
   end if
   
end mobileOrientation





###################--database routines ---- LOCAL -----------------------------------

function nameDb pDbName
   
   if pDbName is empty then
      answer "No DB name assigned!"
      exit nameDb
   end if
   
   set the cpDataBaseName of this stack to empty
   set the cpDataBaseName of this stack to pDbName
   
end nameDb



--connect to the db using the path and database names specified
command connectToDB
   
   local tDatabaseName, tDatabasePath, tDatabaseCheck, tDatabaseID, tErrMsg
   
   put the cpDataBaseName of this stack into tDatabaseName
   put the cpAppPath of this stack & tDatabaseName into tDatabasePath
   -- Open a connection to the database
   -- If the database does not already exist it will be created
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into tDatabaseCheck
   
   --error catching
   if tDatabaseCheck is a number then
      put tDatabaseCheck into tDatabaseID
      -- Store the database id so other handlers can access it
      set the cpDatabaseID of this stack to tDatabaseID
      return empty
   else
      put "Unable to connect to database '" & the dbName of me & "': " & tDatabaseCheck into tErrMsg
      answer error tErrMsg as sheet
      put empty into tDatabaseID
      return tErrMsg
      exit to top
   end if
   
end connectToDB


--close the database and clear the connection
on closeDB pConnID
   
   revCloseDatabase pConnID
   -- this ensures that you'll never have a left-over connection number
   if pConnID = the cpDatabaseID of this stack then
      set the cpDatabaseID of this stack to empty
   end if
   
end closeDB


--all the CRUD operations in here
function callDB pdbCommand, pdbParams, pdbColumns 
   
   connectToDB
   
   local tQuery, tRecords, tConnectionID, tCmd, tResult
   
   put the cpDatabaseID of this stack into tConnectionID
   answer "params passed  " && pdbCommand && pdbParams
   
   
   --SELECT-------------------------------------
   if pdbCommand is "SELECT" then
      
      put "SELECT" && pdbParams into tQuery
      put revDataFromQuery(,,tConnectionID,tQuery) into tRecords
      
      if tRecords begins with "revdberr" then 
         closeDB tConnectionID
         answer error "There was a problem accessing the database: " & tRecords
         
         exit to top
      else 
         closeDB tConnectionID
         return tRecords
      end if
      
   end if 
   
   
   --INSERT---------------------------------------
   if pdbCommand is "INSERT" then
      
      put pdbParams into tCmd
      revExecuteSQL tConnectionID, tCmd
      put the result into tResult
      --answer tResult &&  "tCmd  " && tCmd & return & tConnectionID
      
      if tResult is not a number then-- check for successful execution or error
         answer error "There was a problem saving this record: " & tResult as sheet
      end if
      
      closeDB tConnectionID
   end if
   
   
   --DELETE---------------------------------------
   if pdbCommand is "DELETE" then
      
      put "DELETE " & pdbParams into tCmd
      revExecuteSQL tConnectionID, tCmd
      --answer tResult
      closeDB tConnectionID
      
   end if
   
   
   --UPDATE---------------------------------------
   if pdbCommand is "UPDATE" then
      --exit to top
      put "UPDATE " & pdbParams into tCmd
      revExecuteSQL tConnectionID, tCmd
      --answer tResult
      closeDB tConnectionID
      
   end if
   
end callDB


###################-------------------------------------------------------------------



#####################BUTTONS################################################

----------------Button UPDATE-----------------
on mouseUp
   
   get nameDb ("ASL.db")
   
   local tResult, tTableName, tSetClause
   
   put "items" into tTableName
   --e.g
   --put "item =" & "'carrots'" & " WHERE " & "user=" & "'mick'" into tSetClause
   put "item =" & "'Milch'" & " WHERE " & "user=" & "'mick'" & " AND " & "qtytype=" & "'qty'" into tSetClause
   
   put callDB ("UPDATE", "" & tTableName & " SET " & tSetClause) into tResult
   
   put tResult into fld "RESULT"
   
end mouseUp


----------------Button DELETE-----------------
on mouseUp
   
   get nameDb ("ASL.db")
   
   local tResult, tTableName, tWhereClause
   
   put "items" into tTableName
   --e.g
   --put "item =" & "'Milch'" into tWhereClause
   put "item =" & "'carrots'" & " AND " & "user=" & "'Andy'" into tWhereClause
   
   put callDB ("DELETE", "FROM " & tTableName & " WHERE " & tWhereClause) into tResult
   put tResult into fld "RESULT"
   
end mouseUp


----------------Button INSERT-----------------


on mouseUp
   
   get nameDb ("ASL.db")
   
   local tFields, tTableName, tResult, tuser, titem, tqty, tqtytype, ttimestamp, tstatus, tpicked, tMergeValues
   
   put "items" into tTableName
   
   put "user, item, qty, qtytype, timestamp, status, picked" into tFields
   
   set the itemDel to comma
   replace " " with  empty in tFields
   
   repeat with c = 1 to number of items in tFields
      
      put " '[[t" & item c of tFields & "]]'," after tMergeValues
      
   end repeat
   
   delete char -1 of tMergeValues
   
   put "Greg" into tuser
   put "Ananas" into titem
   put "1" into tqty
   put "qty" into tqtytype
   put the milliseconds into ttimestamp
   put "CLEAR" into tstatus
   put "1" into tpicked
   
   put callDB  ("INSERT", "INSERT INTO " & tTableName & "(" & tFields & ")"  & merge("VALUES ("&tMergeValues&")")) into tResult
      
   put tResult into fld "RESULT"
   
end mouseUp





----------------Button SELECT-----------------
on mouseUp
   
   get nameDb ("ASL.db")
   
   local tResult, tTableName, tWhereClause, tFieldsList, tLimit, tOrderBy
      
   put "items" into tTableName
   put "*" into tFieldsList
   --put "user,item,timestamp" into tFieldsList
   put "picked" into tOrderList
   --put " WHERE item =" & "'Kaffee'" into tWhereClause
   --put " Limit" && "4" into tLimit
   put " ORDER BY" && tOrderList && "DESC" into tOrderBy
   
   
   put callDB ("SELECT", "" & tFieldsList & " FROM " & tTableName & tWhereClause & tOrderBy & tLimit) into tResult
   
   put tResult into fld "RESULT"
   
end mouseUp