Hi Ray, Lots of things for you to think about! As someone else mentioned, I thought you were using a network file system rather than a web server.
Just to set the record straight on a couple of things. mySQL does have transactions, as do all SQL implementations. They're part of the SQL spec. postGresql may well have advantages over mySQL but that isn't one of them. It's quite feasible to implement multi-user sqlite applications. There are several examples on the SQLite web site and in fact their website is driven by an sqlite database. Of course it depends on the needs of the application, as it always does. There are many ways to handle queuing of db access when the db is locked. There's a PRAGMA that controls how long the lock request should wait before reporting it can't get the lock. It's easy to program that yourself in a repeat loop/send in time structure too. I started a thread a few months back about direct calls to server-based SQL databases from Livecode versus middleware which generated a lot of possible approaches. I ended up going the middleware route using php scripts. My Livecode app uses the POST command to send a code that indicates to the server which SQL operation is to be executed along with all the data needed to execute it. No SQL statements ever go over the connection so no sql injection issues. That's just one possible implementation of course and there are many other ways to do it. Good luck! On Wed, Apr 6, 2016 at 8:32 AM Ray <r...@linkit.com> wrote: > 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 > _______________________________________________ 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
