Developers of Sympa

[Guillaume Rousse <address@concealed>]

Le 09/01/2013 17:26, David Verdin a écrit :
> Hi all,
>
> Le 08/01/13 05:33, IKEDA Soji a écrit :
> > Dear all
> > A happy new year!
> Thanks Soji, a happy new year to you too!
> > On Thu, 27 Dec 2012 19:30:44 +0100
> > Guillaume Rousse<address@concealed>  wrote:
> >
> > > Le 18/12/2012 16:46, IKEDA Soji a écrit :
> > > > Hi developers,
> > > >
> > > > By now, I am debugging Sympa with MySQL (mainly 5.5 and sometimes
> > > > earlier), Pg (only 9.1) and SQLite (3.7.7; current code won't
> > > > support SQLite 2.x).  They are working nice or so.
> > > >
> > > > Oracle, Sybase (and MS-SQL/ODBC and Informix and..., and..., more!)
> > > > has not been checked by me.
> > > >
> > > > What kind of RDBMS(es) you are using for debugging and coding?
> > > I just commited a bunch of tests for those different RDBMS in my branch.
> > >
> > > Only mysql and sqlite do actual database interaction tests, whereas
> > > other just test the statement generation methods.I should add
> > > interactions test with postgresql quite easily soon, and eventually port
> > > the whole stuff to Test::Database rather than rely on environment
> > > vaariables.
> > >
> > > Anyway, those tests quickly show than trying to produce specific SQL
> > > statement isn't enough to ensure complete transparency, as underlying
> > > features seems to differ quite a lot between various RDBMS. For
> > > instance, sqlite has very limited support for table modifications,
> > > meaning deleting fields or adding primary keys after table creation
> > > won't work...
> > You are right.  On current 6.2 branch the SDM component for SQLite
> > exactly do it.
> >
> > > So, rather than the current API granularity, allowing to create an empty
> > > table and populating it in a second time, it would seems safer and more
> > > portable to be able to passe a fields list definition to add_table()
> > > method. Additionaly, it would avoid the ugly 'temporary' field hack :)
> > Current step-by-step construction is ugry.  But anyway, addition and
> > deletion of fields/keys are nessessary for upgrade process.
> Yes, we could support multiple granularities: full table for creation
> and atomic modification for upgrades.
> > > And before Marc jumps on the subject, it may be interesting to look for
> > > already existing perl ORMs, rather than reinventing the wheel. Whereas
> > > Class::DBI or Class::DBIx are definitively overkill, lightweight
> > > alternatives as DBI::Skinny would seem a good compromise between
> > > additional dependencies and maintainance gain.
> > They seem the wheels and the axles we have desired.
> It looks promising indeed. Does anybody know whether this module uses
> precompiled statements for select and update queries? I'd really like to
> generalize this practice as it is supposed to greatly improve database
> usage performances.
'greatly' vastly depends on your usage. Only repeated queries (such as: 
who is member of list X) would offer actual performance improvement. And 
we'd need benchmark anyway to measure them.

I don't think we first need to switch to an ORM to achieve this, tough, 
and the current framework would easily be converted to achieve this. 
That's just a matter of using placeholders instead of sprintf format in 
SQL queries, and caching the result of prepare() with the raw query.

For instance:

Sympa::SDM::do_query->('SELECT count(*) FROM user_table WHERE email_user 
= ?', $who);

sub do_query {
    my ($query, @params) = @_;

   my $sth = $cache->{$query} ||= $dbh->prepare($query);
   return $sth->execute(@params);
}

The additional side-effect is to let DBI handle quoting directly...

--
Guillaume Rousse
INRIA, Direction des systèmes d'information
Domaine de Voluceau
Rocquencourt - BP 105
78153 Le Chesnay
Tel: 01 39 63 58 31

Attachment: smime.p7s
Description: Signature cryptographique S/MIME