thomas Dauby wrote:
Hi all,
I recently upgraded to DBIx::Class 0.08123, and paging does not work
anymore.
I get the following error:
query: Error while executing query:DBIx::Class::ResultSet::next():
Generic Subquery Limit order criteria column 'me.start_time' must be
unique (no unique constraint found) at VTOM/Repo2.pm line 322 at
WEB/Action.pm line 119
I modified to order by clause to use a column with a unique contraint,
but the generated SQL is painfully slow:
> SELECT environment_name, start_time, end_time, id, return_status,
application_name, job_name, duration FROM ( SELECT environment.name
<http://environment.name> AS environment_name, me.start_time,
me.end_time, me.id <http://me.id>, me.return_status, application.name
<http://application.name> AS application_name, job.name
<http://job.name> AS job_name, me.duration FROM job_exec me JOIN host
host ON host.id <http://host.id> = me.host_id JOIN users users ON
users.id <http://users.id> = me.users_id JOIN job job ON job.id
<http://job.id> = me.job_id JOIN application application ON
application.id <http://application.id> = job.application_id JOIN
environment environment ON environment.id <http://environment.id> =
application.environment_id WHERE ( return_status NOT IN ( ? ) ) ) me
WHERE ( SELECT COUNT(*) FROM job_exec rownum__emulation WHERE
rownum__emulation.id <http://rownum__emulation.id> > me.id
<http://me.id> ) BETWEEN 690 AND 719 ORDER BY me.id <http://me.id> DESC
: '0'
FYI, here's the generated SQL using the previous version:
> SELECT environment.name <http://environment.name>, me.start_time,
me.end_time, me.id <http://me.id>, me.return_status, application.name
<http://application.name>, job.name <http://job.name>, me.duration FROM
job_exec me JOIN host host ON host.id <http://host.id> = me.host_id JOIN
users users ON users.id <http://users.id> = me.users_id JOIN job job ON
job.id <http://job.id> = me.job_id JOIN application application ON
application.id <http://application.id> = job.application_id JOIN
environment environment ON environment.id <http://environment.id> =
application.environment_id WHERE ( ( environment.name
<http://environment.name> = ? AND return_status NOT IN ( ? ) ) ) ORDER
BY start_time desc: 'paris_divers', '0'
The new SQL comes straight from DBIx::Class::SQLAHacks, but I dont know
where the old sql comes from...
Is there a way to go back to the old method? I'm not familiar enough
with the codebase and the code flow to modify it, but I'm using Sybase
15, so for this operations, I guess scrollable cursors could be used.
What shall I modify to do so?
Before 08122 DBIx::Class would default to software_limit => 1 when used
with Sybase, because the generic subquery method didn't function at all.
While software_limit is not documented (stupid omission, will be fixed in
the next version) it essentially removes *all* limits from the SQL query
and does the "fast-forwarding" on the client side within DBIC. For a usage
example see [1]. So this is how you can switch between the new and old
method, depending on what will be more beneficial for you.
On your suggestion of "scrollable cursors" - we are very interested into
having this kind of functionality baked into DBIC, could you elaborate more
on how this could work? (best show a pure-DBI example, it will be clearer
for us how to incorporate that into the DBIC machinery)
Cheers!
[1]
http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=t/75limit.t;h=686161af223473ebd475d6c563cf71a395dce8ab;hb=refs/heads/master#l29
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
