Error: Unable to open the database file
ray at linkit.com
Wed Apr 6 19:35:48 CEST 2016
Yeah, my expectation is that we'll avoid injection issues using the PHP
All in all, it seems like a pretty common need; to store on a server a
database that multiple users are going to be updating. I wish there
were some sort of a Livecode lesson which detailed several approaches to
this including the necessary steps to set up the database on the remote
server. Do you know of any?
On 4/6/2016 12:42 PM, Peter Haworth wrote:
> 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 <ray at 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.
>> On 4/6/2016 11:14 AM, Dr. Hawkins wrote:
>>> On Wed, Apr 6, 2016 at 6:05 AM, Ray <ray at linkit.com> wrote:
>>>> I thought of downloading it, updating it, and then putting it back on
>>>> 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
>>>> at a time, but that only takes about 20 milliseconds if done on the
>>>> 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
>>> When SQLite writes, it changes a patch of disk (I couldn't tell you how
>>> 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
>>> would be three separate 20ms transactions.
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
More information about the use-livecode