Sqlite on cloud base in Livecode for centralized database

Question

I am working on a livecode application.In which i need to use cloud based sqlite which means it should sharable for all users.As right now i am using sqlite for local,but i think it will work only for one device application,Though this every users will have there own database.But what i want is to share a single database to all users.some suggest me to use JSON parsing for remote database. http://lessons.runrev.com/s/lessons/m/4071/l/7003-connecting-to-a-mysql-database .This mysql database is accessible through the url,username and password from a web.There is no any Json parsing over there.Is it possible in the case of Sqlite? Please help

Answer 1

You can use SQLite with several clients, if you write a server for it. Note that it's probably easier to just use a MySQL or PostgreSQL server, even tho their strong typing is annoying.

For networked SQLite, you need a server that receives data from clients via a socket connection. So basically you'd have a single LiveCode program that passes on SQL queries that it gets over a network. A very simplicistic example (untested):

server:

on mouseUp
  accept connections on port 8080 with message "queryMessage"
end mouseUp

on queryMessage theIP
  read from socket theIP until return
  put it into mySQL
  delete char -1 of mySQL --remove trailing return that was added for network protocol
  put revOpenDatabase("SQLite","path/to/myDatabase.sqlite",,,) into connectionID
  put revDataFromQuery(,,connectionID,mySQL) into myResult
  revCloseDatabase connectionID
  write length(myResult) & return & myResult to socket theIP --no return needed, length based
  close socket theIP
end queryMessage

client:

on mouseUp
  -- make sure to know what IP the server is running from!
  -- the number after ":" is the port
  put "localhost:8080" into myIP
  open socket to myIP
  write "SELECT * FROM tableName" & return to socket myIP
  read from socket myIP until return
  put it into returnStringLength
  read from socket myIP for returnStringLength chars
  put it into field "the sql query result"
  close socket myIP
end mouseUp

You need additionally:

• Ways to handle commands like INSERT, PRAGMA, etc. (basically revExecuteSQL)
• SQL error reporting/handling
• Socket error reporting/handling
• If public accessible, you need security!
• Logging of who is querying and what is happening

I've only used this approach with 4-8 clients, so I don't know how well it scales for larger client bases. Note that in this scenario the "server" is not run on a shared webhost (like a webpage), but instead is a "normal" program on a pc in the same local network (example geared to run on same pc as client). doing this over the internet usually needs you to forward ports on a router or firewall, so it's a bit harder to set up, but is also possible.

Make sure to look up commands used in the example in the dictionary.

Answer 2

SQLite is designed to be a single user local file database so it's not suitable for your application. There's lots of options for cloud based data storage out there. You could if you wanted connect directly to a remote database server like MySQL but that means enabling remote access which is a security risk. If you only need to access the database from a restricted set of IPs then it's probably worth considering but use SSL and a user that only has limited privileges.

I don't know how JSON got involved but you could use a web server + server side scripts to create an API that returned JSON objects. That's a more secure approach than remote access. There's other options too... Amazon Web Services SimpleDB would possibly work for you.