Code change in oracle.py to allow joins in pagination.

[tomt]

Hello,

I have patched the select_limitby routine in oracle.py to allow for successful pagination when joins are used.

This is an example of the sql it generates:

SELECT c0 "STATUSPOINT.POINTNUMBER", c1 "STATUSPOINT.POINTNAME", c2 "AOR.REFERENCENAME", c3 "AOR.AOR"
FROM (
  SELECT w_tmp.c0, w_tmp.c1, w_tmp.c2, w_tmp.c3, ROWNUM rn
  FROM (
    SELECT STATUSPOINT.POINTNUMBER c0, STATUSPOINT.POINTNAME c1, AOR.REFERENCENAME c2, AOR.AOR c3
    FROM AOR, STATUSPOINT
     WHERE (STATUSPOINT.POINTACCESSAREA = AOR.AOR)
     ORDER BY STATUSPOINT.POINTNUMBER
  ) w_tmp
  WHERE ROWNUM <= 20
)WHERE rn > 10

The sql I used is based on a suggestion from https://blog.jooq.org/2014/06/09/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework/

It works successfully in my initial tests, but I realize that it's possible that this change may cause some problems that I haven't tested for.

I'm hopeful that this change may be considered for implementation into the official web2py code.  Please let me know if there is anything that I can do to assist in this process.


... gluon/packages/dal/pydal/adapters/oracle.py ...

def select_limitby(self, sql_s, sql_f, sql_t, sql_w, sql_o, limitby):
    if limitby:
        (lmin, lmax) = limitby
        if len(sql_w) > 1:
            sql_w_row = sql_w + ' AND w_row > %i' % lmin
        else:
            sql_w_row = 'WHERE w_row > %i' % lmin

        # start of my code changes

        # remove blanks from sql_f
        mysql_f = sql_f.replace(" ","")
        # split into lists
        myfields = mysql_f.split(",")
        select1 = "SELECT"
        select2 = "SELECT"
        select3 = "SELECT"
        for i in range(len(myfields)):
            select1 += ' c%s "%s",' % (i,myfields[i])
            select2 += ' w_tmp.c%s,' % (i)
            select3 += ' %s c%s,' % (myfields[i],i)
        # remove trailing ','
        select1 = select1.rstrip(",")
        select3 = select3.rstrip(",")           
        mysql = "%s\nFROM (\n  %s ROWNUM rn\n  FROM (\n    %s" % (select1,select2,select3)                 
        mysql += "\n    FROM %s\n    %s\n    %s" % (sql_t,sql_w,sql_o)
        mysql += "\n  ) w_tmp\n  WHERE ROWNUM <= %s\n)WHERE rn > %s\n" % (limitby[1],limitby[0])
        return mysql           
        #return  'SELECT %s %s FROM (SELECT w_tmp.*, ROWNUM w_row FROM (SELECT %s FROM %s%s%s) w_tmp WHERE ROWNUM<=%i) %s %s %s;' % (sql_s, sql_f, sql_f, sql_t, sql_w, sql_o, lmax, sql_t, sql_w_row, sql_o)

        # end of my code changes

    return 'SELECT %s %s FROM %s%s%s;' % (sql_s, sql_f, sql_t, sql_w, sql_o)

.................................................

[Massimo Di Pierro]

Thank you for your work. Can you submit a github pull request? If not we can do it but you may want to get the credit. Let us know.

[tomt]

I'd be happy to give it a try, but I'm not sure what steps to take.  When I go to github and click on create new pull request it appears I have to select a branch to compare to. I'm uncertain if I am supposed to select admin, experimental, or enter a value of my own.  Does web2py have any suggested steps, or a guideline for using github? 

- Tom

[Massimo Di Pierro]

I can take care of it. Can you email me your patch as an attachment?

[tomt]

Hi,  I've attached the oracle.patch file for gluon/packages/dal/pydal/adapters.oracle.py 

- Tom

[Massimo Di Pierro]

Sorry the patch does not work with the latest pydal/adapters/oracle.py. Can I ask you to resubmit it?

[tomt]

I downloaded the latest release, 2.14.6, and downloaded my patch file from this post and applied it against oracle.py:
cd gluon/packages/dal/pydal/adapters
patch < oracle.patch

It created the oracle.py I expected and it runs correctly.
Are you referring to a different version of web2py?

- Tom

[Massimo Di Pierro]

I meat it does not work with the git version. But let me try it again....