Livecode <=> SQL?

[Pierre Sahores]

> Le 23 mai 2015 à 16:44, Dr. Hawkins <dochawk at gmail.com> a écrit :
> 
> On Fri, May 22, 2015 at 6:13 PM, Pierre Sahores <sc at sahores-conseil.com>
> wrote:
>>> 
>>> Oh, I see what you mean.  At the moment, I'm taking the approach of
>>> using a persistent server app, as the time to open a postgres database
>>> is significant, and could happen every couple or few seconds for each
>>> client.
>> 
>> Should’t be at all. PostgreSQL works in a stateless mode. As long as each
> connection to PostgreSQL is :
> 
> Maybe it's livecode then :)
> 
> I think the last timing figures I had were a little under 100ms for
> revOpenDatabase() on localhost (a three or four year old quad-core i5 iMac)
> and around 250ms across town, with complex revDataFromQuery() (many
> insert/updates followed by a single query) taking something like a third as
> long
> 
> e.g.,
> put the milliseconds into strt
> put revDataFromQuery(,,5,dcmd) into theData
> put the milliseconds-strt into stp
> ck "elapsed: " & stp
> 
> (ck is a simple data writing handler)
> 
>> 
>> 1.- opened (by the client, the LC server or the persistant server)
>> 2.- processed (the request)
>> 3.- closed as soon as the expected data are returned
>> 
>> PostgreSQL will be able to respond very smoothly to thousands of
> différent client requests peer second (lost more than Apache can provide
> it…).
> 
> 
> Apache as a bottleneck?

Yes, it went for years and still is ! Have an eye to this, if needed : http://open.litespeedtech.com/mediawiki/
> 
> :)
> 
>> 
>> On the other hand, if each connection is not closed as soon as the job is
> done, the main PostgreSQL controller will
>> become less and less responsive because the unneeded still opened
> connections in its RAM array.
> 
> Right now, what I'm writing is a persistent application running on the
> server, to which the remote client opens a socket and then writes to.  On
> initial open, licensing is verified for the session, and some setup is
> done.  Once initialized and authenticated, client uses write to socket to
> send it a command (inserts & updates & a query) with the changed data, and
> continues on.  When the command is done, the server will write to the
> socket, and the client will make local updates.

A way i used for years but never in the exact same way you do. To secure against interlocking issues the runrev's application’s server running as a process in the background, i used the following way :

Windows/MacOS native or Web browser clients -> HTTP(S) -> Apache -> PHP sockets listener -> TCP socket -> Rev Application’s server -> PostgreSQL

Was 100% reliable in the early 2000 up to 800 connections peer seconds in running on a simple Linux P4 powered sever.

You can have an eye on this at this old paper :  http://www.sahores-conseil.com/insead/index_en.html
> 
> An http solution would be blocking,

Yes (in theory) and no (in practice) as long as each cgi thread opened by the http server (ideally openLiteSpeed instead of Apache 2) acts independently from each other.

> which would be bad in this
> context--these updates are written while the user is working (it actually
> waits until 2 seconds after the last keystroke), but any perceivable delay
> would be bad.
> 
> The server can close the postgres connection when the socket closes.
> 
> Also, I'm thinking that I may be able to drop the query--it's purpose is to
> find data updated by other users in nearly real time.  Instead, with the
> socket staying open, the server could push data to every client using that
> table (not conceivably more than a handful), which would mean significantly
> less transactions (currently, client is checking every couple of seconds
> for updates)
> 
> -- 
> Dr. Richard E. Hawkins, Esq.
> (702) 508-8462
> _______________________________________________
> 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

--
Pierre Sahores
mobile : 06 03 95 77 70
www.sahores-conseil.com