Paging queries with Microsoft SQL Server

[smahood]

Paging queries don't work with Microsoft SQL Server.

Database spec is as follows. Queries are working as well as filtered queries. The same issue should also occur when the classname is "com.microsoft.sqlserver.jdbc.SQLServerDriver", but I haven't tested that.

(def db-spec {:classname "net.sourceforge.jtds.jdbc.Driver"
              :subprotocol "jtds:sqlserver"
              :subname "subname"
              :user "user"
              :password "password"})

Looking at the tests, it looks like the paging queries are in the form

SELECT column_name, another_column_name, third_column 
FROM my_table 
ORDER BY column_name 
ASC LIMIT _pagesize OFFSET _page

Microsoft SQL Server 2005 and 2008 requires the queries in the form

SELECT * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY column_name DESC) AS
rownum, column_name, another_column_name, third_column 
FROM my_table ) AS my_table
WHERE rownum BETWEEN (_page * _pagesize) AND ((_page+1) * _pagesize)
(
ORDER BY rownum DESC

Microsoft SQL Server 2012 requires the queries in the form (taken from http://raresql.com/2012/07/01/sql-paging-in-sql-server-2012-using-order-by-offset-and-fetch-next/ as I don't have a 2012 server handy to test)

SELECT column_name, another_column_name, third_column 
FROM my_table 
ORDER BY column_name 
OFFSET (_page*_pagesize) ROWS
FETCH NEXT (_pagesize) ROWS ONLY

Any idea if this is something that should be fixed in Honey SQL or handled in Sweet-Liberty?

[bpiel]

Sweet-lib has almost no code specific to database types, so I think HoneySQL would be a better place for it. If you follow up on this with HoneySQL, I'd be interested to know what happens.

[smahood]

I've added an issue to HoneySQL (https://github.com/jkk/honeysql/issues/58), and I'll follow up with this one once there's some action there.

I think the SQL Server 2005 and 2008 paging queries are really ugly and probably not worth supporting in Honey SQL, though. This pretty much means that paging in 2005 and 2008 won't ever work with Sweet Liberty.

My thoughts are that it should either be possible to turn off paging in Sweet Liberty manually for database servers that don't support it, or allow the definition of paging queries using something like Yesql when needed. This might be the better option, as it would allow an easy path for developers to drop down into whatever dialect of SQL they need when they run into issues like this, but still allow all the rest of the nice API development and query creation for the standard commands their DB server supports.

I know from my perspective as a user, the 2 things I really want from Sweet Liberty are the ability to create APIs really quickly and easily, with the ability to drop in and easily handle any weird stuff like this as well. Just from working with it for a few days I don't really ever want to go back to my old API development workflow so I have a pretty vested interest in helping to improve things.