Transition from standalone to multiuser with SQLite

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
bergsy
Posts: 45
Joined: Mon Oct 28, 2013 10:51 pm

Transition from standalone to multiuser with SQLite

Post by bergsy » Sun Jul 17, 2016 11:27 am

Hi,

I have developed a Livecode application (Windows and Mac) that currently runs standalone using SQLite. I need to progress this to a multi-user system with the database hosted on either the internet/intranet/local network using our app and also the web.

In order to do this I need to have the database hosted in a networked location. I was thinking of using MySQL, however it is problematic given that composite keys that contain text fields need to be represented with MD5 checksums or equivalent - because MySQL doesn't support composite keys the same as SQLite when they contain large text strings. Or I can go for SQLite first up and then transition if the database gets bigger.

I need some advice on this. Not sure how to set up SQLite on a server and access it over the web. Having the database as a shared file is the solution for a local network, but not for something web based (unless I am missing something).

Any advice appreciated.

Cheers

Greg

MaxV
Posts: 1579
Joined: Tue May 28, 2013 2:20 pm
Location: Italy
Contact:

Re: Transition from standalone to multiuser with SQLite

Post by MaxV » Wed Jul 20, 2016 10:05 am

Why do you use composite keys? (INTEGER) PRIMARY KEY is better to me.
A composite key it's just a complex way to generate a primary key, and quite impossible to debug. My suggestion is to switch PRIMARY KEYS as soon as possible.
Livecode Wiki: http://livecode.wikia.com
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Transition from standalone to multiuser with SQLite

Post by Mikey » Wed Jul 20, 2016 2:38 pm

Greg,
I'm also not a fan of composite keys. We can aruge about whether a composite search item is a good idea, or not.

Depending on how complicated your setup is, here are some things to think about:
1) SQLite is not multi-user friendly - it will tolerate multiple users, until you have some collision between queries and updates, or transactions. If you are going to use SQLite in those situations, then you should either have SOMETHING on the server acting as a DB server, OR, you should have each of the clients download a current version of the data locally, and send updates to the server.

bergsy
Posts: 45
Joined: Mon Oct 28, 2013 10:51 pm

Re: Transition from standalone to multiuser with SQLite

Post by bergsy » Thu Jul 28, 2016 4:33 pm

Thanks for the feedback.

I am using composite keys to ensure that records do not get created twice. For example, if I have a customer, a product and sub products, I want to ensure only one record exists for any combination of customer/product/subproduct. I guess I can check to see if the record exists first and if so, update it rather than create it.

The suggestion of creating a server that handles SQLite calls seems like an interim solution, however if I want a huge database then MySQL may be a better way to go as it will scale better.

Thanks

Greg

Lynn
Posts: 109
Joined: Thu Feb 23, 2006 7:43 pm

Re: Transition from standalone to multiuser with SQLite

Post by Lynn » Mon Nov 28, 2016 9:01 pm

Valentina Server now has a SQLite based, multi-user solution in it. Check it out for free here. It also has its own Valentina DB Database Server, and Valentina Reports Server - all in one package.
Best regards,


Lynn Fredricks
Paradigma Software - Ultra Fast Database & Reporting Platform for LiveCode
http://www.paradigmasoft.com

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Transition from standalone to multiuser with SQLite

Post by Mikey » Mon Nov 28, 2016 9:03 pm

Oh this might be fun...

Post Reply

Return to “Databases”