On 20 Oct 09:57, Vincent (CloudSuite) wrote: > I assume one of the main reasons Python-SQL was built, was to make it > easier > to generate dynamic SQL queries (i.e. exact columns/table joins etc. are not > known before-hand). This is IMO where Python-SQL shines and I appreciate > Tryton's ORM is using Python-SQL internally. > > I completely fail to see any advantages of why all module 'static' SQL > queries > have been replaced with Python-SQL code though. Am I missing something > here?
Yes, the point is you need much less often to write SQL queries in modules because the ORM become much more powerfull and will continue. > If I was required to work with Python-SQL, my work-flow would change into: > 1) Write my SQL queries in a dedicated database tool > 2) Test it runs and check the results. Why? I always write it directly with python-sql it is much more efficient for many things because I can use loop, condition and range to create the query and often shorter on big query. > 3) Convert the query to Python-SQL, resulting in more lines of code, various > additional python imports, creating lots of object instances, requiring me > to do > things manually that SQL apparently takes care of automatically (at least, > I've > seen production code like this: invoice_join.left.left.left.left.right... > to add some joins > to a specific table). I don't know what you are talking about but such usage is because of wrong design and it should be avoided. > All this for the same end result as the previous > steps. Yes so no need of the two first steps. > 4) Test the Python-SQL code runs and double-check the result is the same as > SQL. Idem. > If I'd directly write in Python-SQL I'd miss important database tools like > EXPLAIN and trouble-shooting would be (IMO) much harder and more > time-consuming. You can still use EXPLAIN because you can get your query with python-sql. But you should not care about EXPLAIN when writing a query because it is the purpose of the database to find the best solution. You must focus on expressing correctly the expected result. You must care about optimisation on database production by logging long query and then you can learn them with the EXPLAIN. > To summarize: Why convert an industry standard 4GL language to a > proprietary > 3GL-alike construction, I don't get the comparison because with python-sql you are writing SQL not any other language. > when the very same query itself can be written in > an > ANSI/ISO-standard way that should work on any DB engine without > modification? This is a false assumption they are a lot of small differences between database and python-sql allow to hide them. > I could live with this if field selection, joins, aggregate functions, etc. > could be performed directly on ORM objects+fields, like other ORMs offer, > but as it is > now we are required to use SQL for this and in that case I prefer to write > SQL. select and join are already supported. aggregate is a tricky part but I guess it should be possible to implement something reusing the ModelSQL.search_domain but this is no more an ORM method. Finaly, you are still free to construct your SQL query manually by joining strings but you will be warned that your SQL will probably not be compatible with all databases, you have the risk to introduce SQL-injection thread and you will risk to have alias collisions. -- Cédric Krier - B2CK SPRL Email/Jabber: cedric.kr...@b2ck.com Tel: +32 472 54 46 59 Website: http://www.b2ck.com/
pgpUXE1GfdHA0.pgp
Description: PGP signature
