[mod_python] Pager component, and Count of Elements...

Even with transactions, you have to keep the connection open and assure 
that the same connection is being used for all paging queries for a 
user. Keeping transactions open while waiting for user input is a very, 
very, very bad idea.

A simple solution that will work on all databases is to fetch data into 
a temporary table. If you can assure that you use the same connection 
within a session, you can use TEMP tables (add "INTO TEMP..." to your 
SELECT query), otherwise you'll have to do something like having a table 
with a primary key of (SessionID, RowNr). By numbering the rows, you can 
always fetch the range you need (WHERE RowNr BETWEEN ? AND ?) for the 
pager. A query like "SELECT COUNT(*) FROM tmp WHERE SessionID=?" will 
cost nothing (the DB need not access the table) and in addition, you'll 
always get a correct result.


Mike Looijmans
Philips Natlab / Topic Automation


durumdara wrote:
> Hi !
> 
> I wrote some mails about this theme, but I forgot to ask an important 
> question:
> How to I get the number of elements in the query... ?
> 
> These valued needed for the Pager:
> 
>   1. "Split by" - the max. record number you can see in the page.
>   2. "RecordCount" - how many records you have (total) - to calc number
>      of pages.
>   3. "ThumbCount" - how many page thumb (link) you see in one page.
>   4. "Actual Page Number" - where are we in the page collection.
> 
> Simply I get every of these values except RecordCount. To get it, I must 
> count the records in the full query...
> I have many elements (1000, 10000 or 100000), so I don't want to fetch 
> them all.
> Because this I need to exec the query in twice, because I need to get 
> the count of elements, and I need some elements from it.
> 
> 1.) select count(*) from ...
> 2.) select * from ...
> 
> Some of the databases supports "repeatable read" transaction isolation, 
> and they are protect the view to I get same thing in different times.
> But if database not supports this, I can get different values with these 
> queries - example: new record inserted after COUNT(*), so number of the 
> elements is greater than in the first query...
> 
> How do you handle this problem in MySQL, or other database that cannot 
> isolate the transactions (example) ?