Bug in using nhibbench paging in SQLite

Today, due to SQLite paging, it took a few hours!

SQLite starts from the third page and only displays data on the second page!

When checking that your code is correct, start to suspect that there is a problem with nhib.pdf processing SQLite processing pagination!

Replace the database with ms SQL, and there is no problem with paging!

Fortunately, I found an article at this time to answer my questions!

The difference is: I use the NHibernate-2.0.1.GA official version!

The path to exploration is really difficult! For this reason! Try nhibernate2.1ga. spring.net 1.3 is supported.

 

Http://3rgb.com/entry/bug_of_NHibernate_with_SQLite

The original article is as follows:

First, I used the nhibernate2.0.1-ga version and the test version, and the following problems have been recorded and corrected by the bug tracker, but the bin or source on the NH station is also good, are not updated yet.

Details: n3c uses sqlite3, and then uses the preceding nhib.pdf for database operations. During paging, only the first and second pages can be correctly divided, starting from the third, only the content on the second page is displayed.

In SQLite, when querying data by page, the SQL statement is followed by limit startrow and offset. If it is the first page, it is the limit offset. For example, if I display 10 entries per page, normally, the first page is limit 10, the second page is limit 10, the third page is limit 20, 10, and the fourth page is limit 30, 10... However, in the SQL statements generated by the preceding versions of Nhibernate, the number of limit statements is changed from the third page to the limit 10, 20, and the fourth page to the limit 10, 30, and so on. The reason is that when NH assembles the SQL statement, reverse start and offset. In the output data, because the offset is set to 10, the latter case is changed to 20, 30, 40, and 50 starting from 10th records .... The record, and then only the first 10 records are returned. As a result, the paging efficiency becomes worse, and the database pressure increases. From the third page, we can only see 10 records on the second page.

So either wait for the official NH to fix this bug, or change the source to compile a new DLL.

Specific modification method:

Index: sqlitedialect. CS
========================================================== ======================================
--- Sqlitedialect. CS (Revision 3862)
+++ Sqlitedialect. CS (working copy)
@-179,14 + 179,15 @@
Sqlstringbuilder pagingbuilder = new sqlstringbuilder ();
Pagingbuilder. Add (querysqlstring );
Pagingbuilder. Add ("Limit ");
-Pagingbuilder. Add (limit. tostring ());
-
+
If (Offset> 0)
{
+ Pagingbuilder. Add (offset. tostring ());
Pagingbuilder. Add (",");
-Pagingbuilder. Add (offset. tostring ());
}

+ Pagingbuilder. Add (limit. tostring ());
+
Return pagingbuilder. tosqlstring ();
}
}

The above Code comes from idea. After the modification, compile a new nhib.pdf. dll.

[Postscript]

After converting nhib.pdf to version 2.1ga, the distribution bug disappears.

The original code has only one change:

# Region 1.2 available
// Recordcount = convert. toint32 (criteria. setprojection (projections. rowcount (). uniqueresult ());
// Eliminates and facilitates Reuse
// The following error is reported in version 1.3!
// Criteria. setprojection (null );
# Endregion

 

# Region 1.3 has a change to setprojection.
// * Icriteria. setprojection now takes a Params array of projections, instead of a single projection
// Only a breaking change if you are implementing icriteria, there is full source code compatability
// Projections. projectionlist (): Create a New Projection list. Each query creates a new projectionlist.
// This eliminates the projection setting issue!
Recordcount = convert. toint32 (criteria. setprojection (projections. projectionlist ()
. Add (projections. rowcount (). uniqueresult ());

# Endregion