Error: Unable to open the database file

[Ray]

Yeah, my expectation is that we'll avoid injection issues using the PHP 
middleware.

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.
>>
>> Thanks,
>>
>> Ray
>>
>> 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
>> 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 at 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 at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode