Error: Unable to open the database file

Wed Apr 6 15:57:51 EDT 2016

This is where it gets interesting.  My original plan was to directly
connect to mySQL from Livecode using the variableslist parameter to the
various Livecode db functions, which should protect against sql injection.
To be honest, I don't recall why I went away from that plan in favor of
middleware.

Most web hosts will have a cPanel option to set up a mySQL database,
usually using phpMyAdmin and it's pretty straightforward if you are already
familiar with db structures.

The only wrinkle I remember is that you have to specify for each db user
which hosts they can connect from in terms of an ip address or domains.

In fact, I think that's what decided me on using middleware since then all
the db access is from your web hosts domain, thus one entry in the
allowable hosts table.  In my case, my users were scattered across various
domains and all had dynamic ip addresses so it would have a been a pain to
keep all that up to date.

I got a great start on the middleware scripts by downloading Bill's library
(thanks Bill).

Pete

On Wed, Apr 6, 2016 at 10:35 AM Ray <ray at linkit.com> wrote:

> 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
>
>
> _______________________________________________
> 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
>