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