[Lazarus] WebData

[ABorka]

On 10/15/2010 05:40, Leonardo M. Ramé wrote:
> On 2010-10-15 14:24:48 +0200, Michael Van Canneyt wrote:
>>> After removing the TSqlQuery and TSQLTransaction components from my app,
>>> a new question came to my mind, in the case of complex inserts/updates
>>> that involves more than one database table, is it possible to use
>>> transactions?.
>>
>> Hm. Not if you use the TSQLDBWebDataProvider. To do that, you would
>> have to use TSQLSuery and a regular TWebDataProvider, and hook into
>> the Before/after post events of the TSQLQuery.
>>
>> But I'm certainly open for suggestions for improvements.
>>
>> Michael.
>
> Well, It's not an issue at all, I could create a stored procedure in
> charge of receiveing the params from the dataprovider, then adapt them to
> their respective tables. It's not an issue because I usually work with
> Firebird and PostgreSql, and they have stored procedures.
>
> I ask this because I'm thinking of adding a new layer of abstraction by
> letting the TFPWebProviderDataModule load a configuration from, say, an
> XML file with the list of Providers and its Select, Insert, Update, and
> Delete statements, like this:
>
> <Module name="ClientesProvider">
>    <Provider name="all-customers">
>      <Select>select * from customers</Select>
>    </Provider>
>    <Provider name="one-customer">
>      <Select>
>        select * from customers where IdCustomer=:IdCustomer
>      </Select>
>      <Insert>
>        insert into customers(Name, Age)
>        values(:Name, :Age)
>      </Insert>
>      <Update>
>        update customers set Name=:Name, Age=:Age
>        where IdCustomer=:IdCustomer
>      </Update>
>      <Delete>
>        delete from customers where IdCustomer=:IdCustomer
>      </Delete>
>    </Provider>
> </Module>
>
> With this data, the cgi/fcgi app could dynamically create the
> TPWebProviderDataModule and everything else.
>
> What do you think about this?

But you can do these things already, I believe.
I had multiple tables and some simple access rights to different people 
so my SQL statements had to be different for everyone to get different 
records from the tables.

What I did is, I started from the first demo example found in 
fcl-web/examples/webdata/demo/

and created ONE action called "CRUD" that handles all the crud request 
from the extjs clients. However, I am passing some additional parameters 
that specifies which table is to be used and what needs to be done 
(Create, Read, Update, Delete). From the session I got who is the user, 
so I was able to prepare the proper SQL statements myself.

For example, the extjs data store proxy looks like this in the client 
side extjs:
===========
   var myproxy = new Ext.data.HttpProxy ( {
     api : {
//XML
//      create:  GetCGIURL('/CRUD?format=xml&t=0&d=0'),
//      read:    GetCGIURL('/CRUD?format=xml&t=0&d=1'),
//      update:  GetCGIURL('/CRUD?format=xml&t=0&d=2'),
//      destroy: GetCGIURL('/CRUD?format=xml&t=0&d=3')
//end of XML
//JSON
       create:  GetCGIURL('/CRUD?t=0&d=0'),
       read:    GetCGIURL('/CRUD?t=0&d=1'),
       update:  GetCGIURL('/CRUD?t=0&d=2'),
       destroy: GetCGIURL('/CRUD?t=0&d=3')
//end of JSON
     }
   });
==========
You can see that I am passing the "t" and "d" additional parameters. "t" 
can be the table/database to use and "d" can be the action to be 
performed (create, read, update, delete), etc. In the web module "crud" 
action I just need a case statement for the different SQL statements 
based on these passed parameters.
Of course it is more complicated to build the actually executed SQL 
statements in my app, because I also process the sort order, the number 
of records to be returned, and have some record filtering coming form 
the extjs client, and as mentioned above, based on the user different 
records needs to be returned, etc.

Plus, I created some additional actions in the webmodule for other 
things like a "keepalive" call from the client so the session is not 
closed if the user uses the client for a long time but there are no 
server calls to refresh the session, and so on for other things and 
responses, web pages with templates, like login/logout, etc.

AB