Multi user sqlite via shared-cache mode?

Björnke von Gierke bvg at mac.com
Wed Mar 12 05:46:27 EDT 2014


I do use sqlite in networked environment, by having written my own server & client in LC. It's pretty trivial, and there's no parallelity, because sqlite only ever 'sees' the server, which is exactly one sql client. Here's an early example that is missing most error checking (this is an older version and for the productive version I added lots of errors, logging, etc.):


-- server
on mouseUp
   if the label of me = "start server" then
      set the label of me to "stop server"
      accept connections on port (field "port") with message "connectionAttempt"
   else
      set the label of me to "start server"
      close socket (field "port")
   end if
end mouseUp

on connectionAttempt theIP
   read from socket theIP until return with message "gotMessage"
end connectionAttempt

on gotMessage theIP theProtocol
   delete char -1 of theProtocol --return used as protocol delimiter
   if theProtocol = "query" then
      read from socket theIP until return with message "queryDB"
   else if theProtocol = "write" then
      read from socket theIP until return with message "writeDB"
   -- else
   -- never happens, unless public accessible
   end if
end gotMessage

on queryDB theIP theQuery
   delete char -1 of theQuery --return from the protocol
   put field "database" into theDB --path to sqlite file
   put revOpenDatabase("sqlite", theDB,,,) into connID
   put unidecode(uniencode(theQuery),"utf8") into theQuery
   put revDataFromQuery(comma,,connID,theQuery) into myResult
   revCloseDatabase connID
   put unidecode(uniencode(myResult,"utf8")) into myResult
      if myresult = "" then --query empty
         write "queryResult" & return & 0 & return to socket theIP
         close socket theIP
      else
         write "queryResult" & return & length(myResult) & return & myResult to socket theIP --return not needed
         close socket theIP
      end if
end queryDB

on writeDB theIP myQuery
   delete char -1 of myQuery --return from protocol
   put field "database" into theDB --path to sqlite file
   put revOpenDatabase("sqlite", theDB,,,) into connID
   put unidecode(uniencode(myQuery),"utf8") into myQuery
   revExecuteSQL connID,myQuery
   put the result into myResult
   revCloseDatabase connID --sets the result to ""
   write "writeOK" & return to socket theIP
   close socket theIP
end writeDB



--client

function queryDB theQuery --theQuery is a sql statement, as understood by LC (no multiline, no ;)
   replace return with "" in theQuery --just to make sure...
   put the milliseconds into theIdent
   put field "IP" & "|" & theIdent into theIP
   open socket to theIP
   write "query" & return & theQuery & return to socket theIP
   read from socket theIP until return
   put it into theMessage
   delete char -1 of theMessage
   if theMessage = "queryResult" then
      read from socket theIP until return
      put it into theAmount 
      delete char -1 of theAmount
      if theAmount = 0 then
         put "sqlempty: No entry found" into theResult
      else
         read from socket theIp for theAmount chars
         put it into theResult --no return at end, do not delete char -1
      end if
   else
      put  "error: Server can't query:" & return & theMessage into theResult
   end if
   close socket theIP
   return theResult
end queryDB

function insertDB theCommand --any sql that's not SELECT
   put the milliseconds into theIdent
   put field "IP" & "|" & theIdent into theIP
   open socket to theIP
   write "write" & return & theCommand & return to socket theIP
   read from socket theIP until return
   put it into myReply
   delete char -1 of myReply
   if myReply = "writeOK" then
      put "" into myReply
   else
      put  "error: Server wrong:" & return & myReply into myReply
   end if
   close socket theIP
   return myReply
end insertDB


>> On Tue, Mar 11, 2014 at 7:41 AM, Michael Gruenthal
>> <mgruenthal at mac.com>wrote:
>> 
>>> I¹m interested in having a small (<20) number of users simultaneously
>>> connected to an sqlite DB on a shared network drive as an alternative to
>>> hosting on a server. 


-- 

Use an alternative Dictionary viewer:
http://bjoernke.com/bvgdocu/

Chat with other RunRev developers:
http://bjoernke.com/chatrev/






More information about the use-livecode mailing list