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
Transition from standalone to multiuser with SQLite
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
Re: Transition from standalone to multiuser with SQLite
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.
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
My blog: https://livecode-blogger.blogspot.com
To post code use this: http://tinyurl.com/ogp6d5w
Re: Transition from standalone to multiuser with SQLite
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.
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.
Re: Transition from standalone to multiuser with SQLite
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
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
Re: Transition from standalone to multiuser with SQLite
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
Lynn Fredricks
Paradigma Software - Ultra Fast Database & Reporting Platform for LiveCode
http://www.paradigmasoft.com
Re: Transition from standalone to multiuser with SQLite
Oh this might be fun...