mySQL: PHP or direct access?

[Peter Haworth]

Thanks Peter, that is indeed an important consideration.  I believe there
are ways to code around that but nevertheless the single connection
middleware method would certainly be less complicated.

I think I am now realizing the full implications of the direct and indirect
methods of connecting to a database.  It's been a very interesting
conversation and I'm thinking it may not be quite over yet.  Nevertheless I
think I now have a set of best practices to adhere to.

Next decision - php or lc server.  lc server is attractive since I already
know the language but suffers from the drawback that not many web hosts
support it.

Now that I'm seeing the benefits of middleware for db access, my next
thought is why bother with an SQL implementation that supports direct
network access when we're not using direct network access?  For example,
wouldn't SQLite do just as good a job?  I've read that SQLite is used for
many web sites supporting fairly high db access rates, maybe in the range
of several hundred thousand a day.

On Fri, Aug 14, 2015 at 1:21 AM Peter TB Brett <peter.brett at livecode.com>
wrote:

> On 2015-08-13 19:16, Peter Haworth wrote:
> > What are the pluses and minuses you get from using php as a middleman
> > to
> > access a mySQL database on a server versus accessing the mySQL database
> > directly from LC?
>
> There are good reasons other than security that you might want to
> indirect your database operations through some server-side middleware.
>
> Let's say you release version 1.0 of your app.  It talks directly to
> your database.  Everything is great.
>
> A few months later, you realise that in order to add a cool new feature
> to version 2.0 of your app, you want to make some changes to the way the
> database is designed.  But you can't!  There are still lots of copies of
> version 1.0 out there, and they depend on having the database set up the
> way they expect.  So, you have to make version 2.0 and version 1.0 use
> separate databases (or even separate database servers), and users can't
> easily move between app 1.0 and app 2.0.
>
> Let's say that instead you provide a server-side middleware.  App 1.0
> tells the middleware the high-level operation ("change the user profile
> pic to <blah>"), and the middleware converts it into SQL.
>
> If the way that the user database is laid out changes in app 2.0, then
> you can change the middleware to cope with the changes -- and app 1.0
> will still carry on working completely happily.  The high-level
> operations it's doing are still the same, but the way they are performed
> has changed -- but it doesn't need to know about it.
>
> I would say that this is almost as important a consideration as
> security.  Given the regularity with which security flaws are found in
> MySQL, I very strongly recommend *not* exposing MySQL ports to the
> Internet.
>
>                                 Peter
>
> --
> Dr Peter Brett <peter.brett at livecode.com>
> LiveCode Engine Development Team
>
>
> _______________________________________________
> 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
>