Re: [Health-dev] Testing

[Luis Falcon]

Hi Cedric !
On Tue, 22 Dec 2015 23:05:23 +0100
Cédric Krier <address@hidden> wrote:

> On 2015-12-22 20:04, Luis Falcon wrote:
> > On Tue, 22 Dec 2015 18:54:27 +0100
> > Cédric Krier <address@hidden> wrote:  
> > > On 2015-12-22 17:29, Luis Falcon wrote:  
> > > > On Tue, 22 Dec 2015 12:33:00 +0100
> > > > Cédric Krier <address@hidden> wrote:
> > > >     
> > > > > Hi,
> > > > > 
> > > > > I'm trying to make the tests working back on GNU Heath, so I
> > > > > work module per module. So far, I have done almost half of
> > > > > them. But now I have to come back to the first module
> > > > > 'health' because of changeset 8665a21223e2  because the SQL
> > > > > query doesn't work on SQLite. (It is important to use
> > > > > python-sql to write portable SQL queries).    
> > > > Agree. We're going on that direction, and most code has been
> > > > moved to python-sql, but for upgrades "run-once" snippets I've
> > > > been using standard SQL code, that could be used also directly
> > > > from the psql interpreter.    
> > > 
> > > But it was not standard SQL code as it doesn't run on SQLite.  
> > I look at it the other way around. SQLite uses Postgres as their
> > reference platform ("What Would Postgres Do")   
> 
> An implementation is not a standard.
In this case, it "IS TRUE" that is also a SQL standard ;-)
> 
> > > The main issue was the usage of TRUE which is not in the SQL
> > > standard.  
> > Don't agree. The "IS TRUE" predicate is part of the SQL standard
> > (F571 ext), that has been around for quite a while. PostgreSQL is
> > one of the systems that conforms to this SQL standard.   
> 
> Indeed but F571 is supported by very few databases [1].
PostgresSQL and MariaDB being two of them.
> That's exactly why we should never write raw SQL but python-sql
> because with this abstraction we can generate valid SQL for any
> database.
> 
> > Moreover, IMHO, PostgreSQL is one of the most SQL conformant DBs,
> > and we should follow it.  
> 
> I think it is wrong if we can not run test on SQLite because the raw
> SQL query is not supported.
> More over, PostgreSQL has some non-standard behaviour nor non-standard
> function. I don't see the point to stick to one database when we have
> the tool to support others. More over which PostgreSQL version should
> be the standard in this case?
> 
> > > I think the option to copy/paste the query in psql is not so much
> > > important against the portability.  
> > Arguable. As we discussed a while ago, probably the best thing to do
> > in the future is to run sql scripts for code to be "run-once" in
> > upgrades. That would result in cleaner __register__ (s)   
> 
> Tryton has a clean and reliable way to manage upgrade that works
> since 7 years. I don't see the point to not use it.
True. Completely agree and it's a great feature.

That said, I still think that we need a way for a "run once" code for
Tryton upgrades. Otherwise, as time and new Tryton version goes on,
we'll end up with very large __register__ methods, and with an
increased risk of criteria that was not met before at that point in
time, could be met in the future, generating a big problem, and hard to
track.
> 
> > > Also someone who is able to find this query in the code will be
> > > able to convert it from python-sql to plain SQL.  
> > True. But still would have to do the job of conversion.  
> 
> For his database which has we have seen is not trivial.
> Also it is still possible to ask python-sql to generate the query.
Good.
> 
> Any way, for me we should consider as a bug that must be fixed if the
> test doesn't work on any database supported by Tryton.
I like more the concept of if it can be done using python-sql, then we
should use python-sql.

PS : Thanks so much for all the work on unit testing you're doing !!

Bests
> 
> [1]
> https://en.wikipedia.org/wiki/Null_%28SQL%29#Null-specific_and_3VL-specific_comparison_predicates
>

pgpOq4zR7laAC.pgp
Description: OpenPGP digital signature