Multi user sqlite via shared-cache mode?

[Peter Haworth]

Thanks Bjornke, that's very useful.

Pete
lcSQL Software
On Mar 12, 2014 2:46 AM, "Björnke von Gierke" <bvg at mac.com> wrote:

> 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/
>
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode