Richard - thanks for this advice. It's really quite helpful. We've
abandoned the sqLite idea but I think mySQL should work fine. The
purpose of this database is to maintain an index of bug reports. The bug
reports themselves are actually Livecode stacks. The database will
serve as an index to all bug reports. The plan is to have just single
table of about four columns; username, bug name, date, and status.
Hopefully it will stay this simple.
Since we'll be updating an entire record at a time I don't think the
lack of dependency will ever be a problem, but let me know what you think.
Thanks,
Ray
On 4/6/2016 11:14 AM, Dr. Hawkins wrote:
On Wed, Apr 6, 2016 at 6:05 AM, Ray <r...@linkit.com> wrote:
I thought of downloading it, updating it, and then putting it back on the
server but that wouldn't work if two users simultaneously did so. Since
I'll have many users using the database simultaneously everything has to be
done on the server. I know only one user can write to an sqLite database
at a time, but that only takes about 20 milliseconds if done on the server
and the other writes get cued, something that wouldn't happen in the
download/re-upload scenario.
You are going past what SQLite is meant to handle, and asking for trouble.
When SQLite writes, it changes a patch of disk (I couldn't tell you how
much).
The other users won't be queued up waiting to write; they'll be getting
failure to open.
You're either going to need a persistent middleware app running on the
server, or to follow the advice of the SQLite team: use postgres for
something like this.
SQLite is wonderful, but it also knows it's limits. I use it in-memory,
and as a convenient way to throw backup files.
And depending upon what you're doing, mySQL may not be an appropriate
choice. In particular, it doesn't handle real transactions.
SQLite and postgres can handle
BEGIN TRANSACTION;
SELECT this from that;
UPDATE that WITH thisstuff;
UPDATE somethingElse WITH that
END TRANSACTION;
whereas mySQL would do this as separate SELECT and two UPDATEs
If you need either all or none of them to happen (e.g., dependencies and
consistency), mySQL is not your choice.
postgres also means a single 20ms transaction for such things, while mySQL
would be three separate 20ms transactions.
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
