PagingWithNhibernateSpec.Should_get_foos_01_through_10 NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id asc limit 10; @p0 = '10'
PagingWithNhibernateSpec.Should_get_foos_11_through_20 NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id asc limit 10, 10; @p0 = '10', @p1 = '10' PagingWithNhibernateSpec.Should_get_foos_21_through_30 NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id asc limit 10, 20; @p0 = '20', @p1 = '10' PagingWithNhibernateSpec.Should_get_foos_31_through_40 NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id asc limit 10, 30; @p0 = '30', @p1 = '10' PagingWithNhibernateSpec.Should_get_foos_41_through_50 NHibernate: SELECT this_.Id as Id0_0_ FROM Foo this_ ORDER BY this_.Id asc limit 10, 40; @p0 = '40', @p1 = '10' I also found this post http://sqlite.phxsoftware.com/forums/t/569.aspx which describes the same problem with the raw SqLite. If I follow this correctly the syntax is backwards. And this came straight from the SqLite documentation http://www.sqlite.org/lang_select.html "The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradition is intentional - it maximizes compatibility with legacy SQL database systems." so if this is true, should the Limit and Offset keywords be used to create the sql statement and remove confusion about the parameters? On Oct 22, 6:23 am, "Ayende Rahien" <[EMAIL PROTECTED]> wrote: > Can you show the generated SQL? > > On Wed, Oct 22, 2008 at 4:04 AM, Jason Meckley <[EMAIL PROTECTED]>wrote: > > > > > I updated my criteria to include order. > > > private IList<Foo> GetResultsStartingAt(int firstResult) > > { > > return session > > .CreateCriteria(typeof (Foo)) > > .AddOrder(Order.Asc("id")) > > .SetFirstResult(firstResult) > > .SetMaxResults(PAGE_SIZE) > > .List<Foo>(); > > } > > > but the results are the same. > > the 1st 2 tests passs > > the last 2 fail. > > > the test fail with the respective messages > > Expected 21 was Actually 11 > > Expected 31 was Actually 11 > > > which looks like it's pulling the 2nd page of results instead of the > > 3rd/4th respectively. > > > If it makes a difference I'm using a SqLite in-memory database for > > this test. Could this be a possible bug with the driver? > > > On Oct 21, 9:46 pm, Jason Meckley <[EMAIL PROTECTED]> wrote: > > > interesting... I assumed that a default order (PK) would be used if no > > > other sort was applied. I will add sorting to the tests above and see > > > how that effects it. > > > > On Oct 21, 9:23 pm, "Ayende Rahien" <[EMAIL PROTECTED]> wrote: > > > > > Unless you specify order, the DB is free to choose whatever order it > > likes.Let > > > > us take this: > > > > > select top 10 * from Foo > > > > > The DB is free to return completely different result sets for this > > query if > > > > you execute it twice > > > > > On Wed, Oct 22, 2008 at 3:16 AM, Jason Meckley <[EMAIL PROTECTED] > > >wrote: > > > > > > I don't follow. What do you mean "orders may be indeterministic"? > > > > > > On Oct 21, 5:02 pm, "Tuna Toksöz" <[EMAIL PROTECTED]> wrote: > > > > > > The orders may be indeterministic > > > > > > > On Tue, Oct 21, 2008 at 11:58 PM, Jason Meckley < > > [EMAIL PROTECTED] > > > > > >wrote: > > > > > > > > I'm having a difficult time understanding page, specifically > > > > > > > SetFirstResult() and SetMaxResults() > > > > > > > > from what I read on different posts SetFirstResult is the > > zero-based > > > > > > > index of the record you want to start with. SetMaxResults is the > > > > > > > number of records you want to return. > > > > > > > > if this is correct then why do tests: > > > > > > > Should_get_foos_21_through_30 > > > > > > > Should_get_foos_31_through_40 > > > > > > > fail? > > > > > > > > //Foo has 1 property Id which is readonly. The id is assigned via > > > > > > > ctor. > > > > > > > > namespace Paging.Spike.Core.Test > > > > > > > { > > > > > > > [TestFixture] > > > > > > > public class PagingWithNhibernateSpec > > > > > > > { > > > > > > > private ISessionFactory factory; > > > > > > > private Configuration cfg; > > > > > > > private ISession session; > > > > > > > private const int PAGE_SIZE = 10; > > > > > > > > [TestFixtureSetUp] > > > > > > > public void TestFixtureSetup() > > > > > > > { > > > > > > > cfg = new Configuration().Configure(); > > > > > > > factory = cfg.BuildSessionFactory(); > > > > > > > } > > > > > > > > [TestFixtureTearDown] > > > > > > > public void TestFixtureTeardown() > > > > > > > { > > > > > > > factory.Dispose(); > > > > > > > } > > > > > > > > [SetUp] > > > > > > > public void SetUp() > > > > > > > { > > > > > > > session = factory.OpenSession(); > > > > > > > new SchemaExport(cfg).Execute(false, true, false, > > true, > > > > > > > session.Connection, null); > > > > > > > using(ITransaction transaction = > > > > > > > session.BeginTransaction()) > > > > > > > { > > > > > > > for (int i = 1; i <= 100; i++) > > > > > > > session.Save(new Foo(i)); > > > > > > > transaction.Commit(); > > > > > > > } > > > > > > > } > > > > > > > > [TearDown] > > > > > > > public void TearDown() > > > > > > > { > > > > > > > session.Dispose(); > > > > > > > } > > > > > > > > [Test] > > > > > > > public void Should_get_foos_01_through_10() > > > > > > > { > > > > > > > ValidateIdsStartingWithId(1, > > > > > > > GetResultsStartingAtIndex(0)); > > > > > > > } > > > > > > > > [Test] > > > > > > > public void Should_get_foos_11_through_20() > > > > > > > { > > > > > > > ValidateIdsStartingWithId(11, > > > > > > > GetResultsStartingAtIndex(10)); > > > > > > > } > > > > > > > > [Test] > > > > > > > public void Should_get_foos_21_through_30() > > > > > > > { > > > > > > > ValidateIdsStartingWithId(21, > > > > > > > GetResultsStartingAtIndex(20)); > > > > > > > } > > > > > > > > [Test] > > > > > > > public void Should_get_foos_31_through_40() > > > > > > > { > > > > > > > ValidateIdsStartingWithId(31, > > > > > > > GetResultsStartingAtIndex(30)); > > > > > > > } > > > > > > > > private IList<Foo> GetResultsStartingAtIndex(int > > firstResult) > > > > > > > { > > > > > > > return session > > > > > > > .CreateCriteria(typeof (Foo)) > > > > > > > .SetFirstResult(firstResult) > > > > > > > .SetMaxResults(PAGE_SIZE) > > > > > > > .List<Foo>(); > > > > > > > } > > > > > > > > private static void ValidateIdsStartingWithId(int id, > > > > > > > ICollection<Foo> foos) > > > > > > > { > > > > > > > foreach (Foo foo in foos) > > > > > > > Console.WriteLine(foo); > > > > > > > > Assert.AreEqual(PAGE_SIZE, foos.Count); > > > > > > > > foreach (Foo foo in foos) > > > > > > > Assert.AreEqual(id++, foo.Id); > > > > > > > } > > > > > > > } > > > > > > > } > > > > > > > -- > > > > > > Tuna Toksöz > > > > > > > Typos included to enhance the readers attention! --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---
