Improve DSL

[oyvindberg]

1) allow mixing orderBy and seek.

2) snapshot tests for generated sql in tests

3) Revamp SQL generation

The previous method for SQL generation was not powerful enough

Create scopes for all levels

The previous method failed to create "scopes" for all joins, which meant that predicates, sorting, limits and so on wouldn't apply where usage of the DSL indicated they would.

Now we create these scopes with CTEs at all levels of the query. CTEs should be fine, performance-wise, as they were fixed for PG12

Better compositionality

The previous method failed when performing (joined query).join(joined query), it only worked with relations on the left side. This is also fixed

Refactoring

The old SQL generation code was neither understandable nor correct. Now I hope it's both

An example of SQL before and after:

before:

select product0.productid, product0.name, product0.productnumber, product0.makeflag, product0.finishedgoodsflag, product0.color, product0.safetystocklevel, product0.reorderpoint, product0.standardcost, product0.listprice, product0.size, product0.sizeunitmeasurecode, product0.weightunitmeasurecode, product0.weight, product0.daystomanufacture, product0.productline, product0.class, product0.style, product0.productsubcategoryid, product0.productmodelid, product0.sellstartdate, product0.sellenddate, product0.discontinueddate, product0.rowguid, product0.modifieddate, productmodel0.productmodelid, productmodel0.name, productmodel0.catalogdescription, productmodel0.instructions, productmodel0.rowguid, productmodel0.modifieddate
from (
  select "productid","name","productnumber","makeflag","finishedgoodsflag","color","safetystocklevel","reorderpoint","standardcost","listprice","size","sizeunitmeasurecode","weightunitmeasurecode","weight","daystomanufacture","productline","class","style","productsubcategoryid","productmodelid","sellstartdate"::text,"sellenddate"::text,"discontinueddate"::text,"rowguid","modifieddate"::text from production.product product0
) product0
left join (
  select "productmodelid","name","catalogdescription","instructions","rowguid","modifieddate"::text from production.productmodel productmodel0
) productmodel0 on (product0.productmodelid = productmodel0.productmodelid)

after:

with 
productmodel0 as (
  (select productmodel0 from production.productmodel productmodel0 )
),
product0 as (
  (select product0 from production.product product0 )
),
left_join_cte0 as (
  select product0, productmodel0
  from product0
  left join productmodel0
  on ((product0).productmodelid = (productmodel0).productmodelid)

)
select (product0)."productid",(product0)."name",(product0)."productnumber",(product0)."makeflag",(product0)."finishedgoodsflag",(product0)."color",(product0)."safetystocklevel",(product0)."reorderpoint",(product0)."standardcost",(product0)."listprice",(product0)."size",(product0)."sizeunitmeasurecode",(product0)."weightunitmeasurecode",(product0)."weight",(product0)."daystomanufacture",(product0)."productline",(product0)."class",(product0)."style",(product0)."productsubcategoryid",(product0)."productmodelid",(product0)."sellstartdate"::text,(product0)."sellenddate"::text,(product0)."discontinueddate"::text,(product0)."rowguid",(product0)."modifieddate"::text,(productmodel0)."productmodelid",(productmodel0)."name",(productmodel0)."catalogdescription",(productmodel0)."instructions",(productmodel0)."rowguid",(productmodel0)."modifieddate"::text from left_join_cte0

4) Add count to DSL