Emitters for transform pagination SQL

[kucerm2]

It's need it for pagination support in Oracle DB.

-- 12c Pagination SQL with OFFSET/FETCH
-- Bind variables:
-- PAGE_SIZE: Count of rows to be returned = 5
-- PAGE_NUM: Page number = 2
select object_id, object_name
from dba_objects 
order by object_id
offset (:PAGE_NUM - 1) * :PAGE_SIZE rows 
fetch next :PAGE_SIZE rows only

[myguidingstar]

can you explain it? I don't have any knowledge of OracleDB

[kucerm2]

• Oracle 12c

fallback	Actual wrapper	Oracle wrapper
limit-fallback	LIMIT :page-size | FETCH FIRST :page-size ROWS ONLY
offset-fallback	OFFSET :offset|OFFSET :offset ROWS

• Oracle pre 12c It's more complicated, because need wrap whole select statement as subselect. For example:

     select  object_id, object_name  
     from (select  a.object_id, a.object_name, row_number() over (order by a.object_id) row_index from dba_objects a)
     where row_index between ((:PAGE_NUM – 1) * :PAGE_SIZE) + 1 and (:PAGE_NUM * :PAGE_SIZE);

[kucerm2]

Oracle pre 12c is only for sample, not for implementation. Major task is newest version of Oracle => 12c.

[myguidingstar]

so I've just looked at this OracleDB guide http://www.oracletutorial.com/oracle-basics/oracle-fetch/.

So Emitter should be extended to support differences in limit syntax. It also has WITH TIES and PERCENT.

I'm considering this syntax to cover Oracle

;; queries
`[{(:some/table {:offset 10
                 :limit  2})
   [:table/column-1 :table/column-2]}]
;; or
`[{(:some/table {:limit  [2 :percent :with-ties]})
   [:table/column-1 :table/column-2]}]
;; or
`[{(:some/table {:limit  [2 :with-ties]})
   [:table/column-1 :table/column-2]}]

What do you think?

[kucerm2]

I don't think this is solution of "Oracle driver". Main reason of the task was support standard paging for Oracle driver. If you extend query syntax then query will be depend on DB driver.

[myguidingstar]

so a higher priority is to allow overriding

limit with #(str " FETCH FIRST " % " ROWS ONLY ") offset with #(str " OFFSET " % " ROWS ")

The extended query syntax proposed above is specific to OracleDB. Old syntax just works everywhere, and new syntax can only work if provided Oracle DB.

It's not the solution, but it's part of supporting OracleDB specific features. And with lower priority.

[myguidingstar]

@kucerm2 can you help test the :percent and :with-ties keywords mentioned above? I've just implemented them in this PR https://github.com/walkable-server/walkable/pull/144 Use {:sha "dec853a5c11ed8b1f7cd1daacf42f9381f495e0b"} in your deps.edn.

[myguidingstar]

@kucerm2 latest version is {:sha "509fdd08096985b2e1b621defb4eee6757c97147"}

[kucerm2]

query	result
:limit 3	ok
:limit [3 :percent :with-ties]	failed - limit clause was not generated

[myguidingstar]

@kucerm2 that's strange. Do you have a :validate function in your floor-plan's pagination fallback for that key? Can you try {:throw? true} eg:

;; floor-plan
:pagination-fallbacks
{:users/all
 {:limit {:default  10
          :validate #(<= 0 % 20)
          :throw? true}}}

It will throw a "Malformed" exception if the provided limit fails the spec, or a "Invalid" exception if it fails the validation.

[myguidingstar]

@kucerm2 you may have an idea of its behavior by looking at: https://github.com/walkable-server/walkable/blob/f28a14c4bccd39abc457eb38f96dc7ca219d8a32/test/walkable/sql_query_builder/pagination_test.cljc#L112

{:sha "f28a14c4bccd39abc457eb38f96dc7ca219d8a32"}