Re: [sqlite] [OT] How to implement paging

[Teg]

Hello Sam,

SC> I take it that there is no solution to my problem?

That's our job isn't it? Designing some solution that works?

- Might have a worker thread watching the DB and have it sending
notifications to the GUI as deletes and additions were made to the DB
so, the display would be updated in real time.

- Might design it so, updates to the DB are sent to a table of updates
and the worker thread both integrates the updates AND notifies the GUI
of the specific update (something like a task queue but, in the DB
itself).

- if the updates are external, might make the updater update an
"update table". So, the worker thread knows what was done without
having to scan the whole DB.

- If I couldn't do that, I might keep all the rowid's in memory and
refresh the rowid list from time to time to find the changes (added
and deleted rowids).

This is kind of basic to problems like this. It's not that there isn't
a solution, there's a 100 different ways to do it. You just have to
pick one. The "realtime-ness" of the display will determine the design.


In Windows for instance a "grid" never has to be filling in with data,
it can re-paint the items dynamically in real time as a result of
notifications. I'd probably use a pure virtual list control, worker
thread that monitors the DB and gated notifications to the GUI that
stalls the worker thread until the notification is processed (since in
windows, worker threads can't talk directly to the GUI elements).

In that way the GUI remains responsive, only a single thread talks to
the DB and the display can be updated in real time. It's how I handle
list controls with 500,000+ elements. Then you just pick whether you
want it to scroll or page.



Monday, May 25, 2009, 5:02:22 PM, you wrote:

SC> I understand and agree that things changing in the middle is not
SC> ideal.  In the situation I am dealing with, things MUST disappear in
SC> the middle of the dataset.  As far as adding things, it should happen
SC> at the end, but that is outside of my control, it all depends on how
SC> things are sorted.  Normally things will be added to the end of the
SC> dataset, though.

SC> I take it that there is no solution to my problem?

SC> Sam

SC> On Mon, May 25, 2009 at 4:53 PM, Teg  wrote:
>> Hello Sam,
>>
>> Are you planning on periodically updating the display as the user
>> interacts with it? Have items pop in and pop out again as they're
>> added or deleted? From your description, the data displayed in the GUI
>> will go stale very quickly. I actually have similar logic in my app
>> and I always append new things to the bottom so, it doesn't affect the
>> current displayed page. Nothing annoys me more than to have items
>> dynamically appearing and disappearing while I'm trying to interact
>> with a GUI.
>>
>>
>>
>> Monday, May 25, 2009, 4:32:56 PM, you wrote:
>>
>> SC> On Mon, May 25, 2009 at 4:05 PM, Teg  wrote:
 Hello Sam,

 Paging or scrolling is purely an abstraction you create with the GUI
 itself. If you load up 88 titles into memory and your screen display is
 40 lines then you have 3 "pages" in memory and you simply replace a
 "page" each time they scroll or page up/down.  You seem to be letting
 the back end dictate what the GUI does when in fact it's better to
 abstract the whole thing so, you can change the back end at will and
 not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
 Nothing beats a flat text file when you have a tiny data set.
>>
>> SC> I hear you that paging should be frontend logic, normally.  The
>> SC> problem is that I have a *DYNAMIC* record set that is constantly
>> SC> changing:
>>
>> SC> The nature of the dataset is that it can grow very quickly early on
>> SC> and at any point after that rows can be deleted through out.  It is
>> SC> this last fact that I need the help of the backend to figure out the
>> SC> page.
>>
>> SC> Assume 5 items per page.  If there are 88 items in the record set on
>> SC> one call that returns 50 to 55, before the next call, items 3,12, 17,
>> SC> 32, and 42 are all deleted from the record set, Item #55 is now going
>> SC> to be #50.  If the front end is simply giving the backend an unique
>> SC> identifier of the item, not the PK, per the recommendations of the
>> SC> ScrollingCursor page, how does the front end learn that it now is
>> SC> getting #50 through #54 rather than #55 through #59?
>>
>> SC> Further, I am assuming this is a problem with paging on any dataset in
>> SC> any database, thus a backend issue :)
>>
>> SC> Sam
>>
>>
>>
>> --
>> Best regards,
>>  Teg                            mailto:t...@djii.com
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>



-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users 

Re: [sqlite] [OT] How to implement paging

[Simon Slavin]

On 25 May 2009, at 7:42pm, Sam Carleton wrote:

> So in the end, you are saying that it is completely and totally the
> responsibility of the frontend to keep track of the page number,
> correct?  The result set should simply return a total count so that
> the # of pages can be calculated.  Correct?

Yes.  That is one way of handling the problem.  Bear in mind that your  
database engine doesn't know anything about how many lines you want to  
fit on the screen at once: that is your application's problem and  
nothing to do with the data.

> The main reason is that my application
> is a kiosk system that can be run on a touch screen display.  Paging
> is much easier than scrolling on touch screens.

Ah, for a kiosk system, your original solution is better.  Quite right.


On 25 May 2009, at 9:32pm, Sam Carleton wrote:

> I hear you that paging should be frontend logic, normally.  The
> problem is that I have a *DYNAMIC* record set that is constantly
> changing:


On 25 May 2009, at 9:53pm, Teg wrote:

> Are you planning on periodically updating the display as the user
> interacts with it? Have items pop in and pop out again as they're
> added or deleted? From your description, the data displayed in the GUI
> will go stale very quickly.

The problem is even worse than that.  There are three common approaches:

A) Do the search when someone starts first asks for the list.  Ignore  
all changes to the data until they've exited the list and gone back in.

B) Each time the user moves from page to page, reflect changes to the  
data.

C) Constantly update the display to show changes in the data even if  
the user isn't hitting any keys.

They're all doable, but you're going to have to decide which of these  
you want to do.  I've done (C) in a PHP/AJAX solution and it wasn't  
too hard: you have to constantly check to see if any changes have been  
made to the table.  But it places a lot of load on the server and  
requires some extremely 'cheap' method of checking to see if any  
changes have been made.

Be aware that using just one 'SELECT' gives you solution (A): the  
command makes a table in memory which doesn't change even if the data  
changes before you've got all the rows from the response.

(B), on the other hand, has a number of problems if your users expect  
to see every record as they're paging through.  For instance, suppose  
you're showing 10 records per page.  Your user is on the first page  
when someone deletes the tenth record.  Does this shift record 11 to  
the first page ?  If so, then when the user hits 'next' do they miss  
the eleventh record ?

Another possibility: the user is viewing the second page when someone  
deletes the first five records in the list.  The user now hits  
'previous'.  Do you now show just five records on the display, or do  
you show five records again ?

Questions like the above are reasons I don't like paging solutions for  
live data.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] How to implement paging

[Sam Carleton]

I understand and agree that things changing in the middle is not
ideal.  In the situation I am dealing with, things MUST disappear in
the middle of the dataset.  As far as adding things, it should happen
at the end, but that is outside of my control, it all depends on how
things are sorted.  Normally things will be added to the end of the
dataset, though.

I take it that there is no solution to my problem?

Sam

On Mon, May 25, 2009 at 4:53 PM, Teg  wrote:
> Hello Sam,
>
> Are you planning on periodically updating the display as the user
> interacts with it? Have items pop in and pop out again as they're
> added or deleted? From your description, the data displayed in the GUI
> will go stale very quickly. I actually have similar logic in my app
> and I always append new things to the bottom so, it doesn't affect the
> current displayed page. Nothing annoys me more than to have items
> dynamically appearing and disappearing while I'm trying to interact
> with a GUI.
>
>
>
> Monday, May 25, 2009, 4:32:56 PM, you wrote:
>
> SC> On Mon, May 25, 2009 at 4:05 PM, Teg  wrote:
>>> Hello Sam,
>>>
>>> Paging or scrolling is purely an abstraction you create with the GUI
>>> itself. If you load up 88 titles into memory and your screen display is
>>> 40 lines then you have 3 "pages" in memory and you simply replace a
>>> "page" each time they scroll or page up/down.  You seem to be letting
>>> the back end dictate what the GUI does when in fact it's better to
>>> abstract the whole thing so, you can change the back end at will and
>>> not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
>>> Nothing beats a flat text file when you have a tiny data set.
>
> SC> I hear you that paging should be frontend logic, normally.  The
> SC> problem is that I have a *DYNAMIC* record set that is constantly
> SC> changing:
>
> SC> The nature of the dataset is that it can grow very quickly early on
> SC> and at any point after that rows can be deleted through out.  It is
> SC> this last fact that I need the help of the backend to figure out the
> SC> page.
>
> SC> Assume 5 items per page.  If there are 88 items in the record set on
> SC> one call that returns 50 to 55, before the next call, items 3,12, 17,
> SC> 32, and 42 are all deleted from the record set, Item #55 is now going
> SC> to be #50.  If the front end is simply giving the backend an unique
> SC> identifier of the item, not the PK, per the recommendations of the
> SC> ScrollingCursor page, how does the front end learn that it now is
> SC> getting #50 through #54 rather than #55 through #59?
>
> SC> Further, I am assuming this is a problem with paging on any dataset in
> SC> any database, thus a backend issue :)
>
> SC> Sam
>
>
>
> --
> Best regards,
>  Teg                            mailto:t...@djii.com
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] How to implement paging

[Teg]

Hello Sam,

Are you planning on periodically updating the display as the user
interacts with it? Have items pop in and pop out again as they're
added or deleted? From your description, the data displayed in the GUI
will go stale very quickly. I actually have similar logic in my app
and I always append new things to the bottom so, it doesn't affect the
current displayed page. Nothing annoys me more than to have items
dynamically appearing and disappearing while I'm trying to interact
with a GUI.



Monday, May 25, 2009, 4:32:56 PM, you wrote:

SC> On Mon, May 25, 2009 at 4:05 PM, Teg  wrote:
>> Hello Sam,
>>
>> Paging or scrolling is purely an abstraction you create with the GUI
>> itself. If you load up 88 titles into memory and your screen display is
>> 40 lines then you have 3 "pages" in memory and you simply replace a
>> "page" each time they scroll or page up/down.  You seem to be letting
>> the back end dictate what the GUI does when in fact it's better to
>> abstract the whole thing so, you can change the back end at will and
>> not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
>> Nothing beats a flat text file when you have a tiny data set.

SC> I hear you that paging should be frontend logic, normally.  The
SC> problem is that I have a *DYNAMIC* record set that is constantly
SC> changing:

SC> The nature of the dataset is that it can grow very quickly early on
SC> and at any point after that rows can be deleted through out.  It is
SC> this last fact that I need the help of the backend to figure out the
SC> page.

SC> Assume 5 items per page.  If there are 88 items in the record set on
SC> one call that returns 50 to 55, before the next call, items 3,12, 17,
SC> 32, and 42 are all deleted from the record set, Item #55 is now going
SC> to be #50.  If the front end is simply giving the backend an unique
SC> identifier of the item, not the PK, per the recommendations of the
SC> ScrollingCursor page, how does the front end learn that it now is
SC> getting #50 through #54 rather than #55 through #59?

SC> Further, I am assuming this is a problem with paging on any dataset in
SC> any database, thus a backend issue :)

SC> Sam



-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] How to implement paging

[Sam Carleton]

On Mon, May 25, 2009 at 4:05 PM, Teg  wrote:
> Hello Sam,
>
> Paging or scrolling is purely an abstraction you create with the GUI
> itself. If you load up 88 titles into memory and your screen display is
> 40 lines then you have 3 "pages" in memory and you simply replace a
> "page" each time they scroll or page up/down.  You seem to be letting
> the back end dictate what the GUI does when in fact it's better to
> abstract the whole thing so, you can change the back end at will and
> not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
> Nothing beats a flat text file when you have a tiny data set.

I hear you that paging should be frontend logic, normally.  The
problem is that I have a *DYNAMIC* record set that is constantly
changing:

The nature of the dataset is that it can grow very quickly early on
and at any point after that rows can be deleted through out.  It is
this last fact that I need the help of the backend to figure out the
page.

Assume 5 items per page.  If there are 88 items in the record set on
one call that returns 50 to 55, before the next call, items 3,12, 17,
32, and 42 are all deleted from the record set, Item #55 is now going
to be #50.  If the front end is simply giving the backend an unique
identifier of the item, not the PK, per the recommendations of the
ScrollingCursor page, how does the front end learn that it now is
getting #50 through #54 rather than #55 through #59?

Further, I am assuming this is a problem with paging on any dataset in
any database, thus a backend issue :)

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] How to implement paging

[Teg]

Hello Sam,

Paging or scrolling is purely an abstraction you create with the GUI
itself. If you load up 88 titles into memory and your screen display is
40 lines then you have 3 "pages" in memory and you simply replace a
"page" each time they scroll or page up/down.  You seem to be letting
the back end dictate what the GUI does when in fact it's better to
abstract the whole thing so, you can change the back end at will and
not have to change the GUI.  For 88 items, I doubt I'd even use a DB.
Nothing beats a flat text file when you have a tiny data set.


SC> That is a very good question.  The main reason is that my application
SC> is a kiosk system that can be run on a touch screen display.  Paging
SC> is much easier than scrolling on touch screens.

SC> Sam
SC> ___
SC> sqlite-users mailing list
SC> sqlite-users@sqlite.org
SC> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] How to implement paging

[Sam Carleton]

On Mon, May 25, 2009 at 2:43 PM, Tito Ciuro  wrote:
> Hi Sam,
>
> On May 25, 2009, at 10:58 AM, Sam Carleton wrote:
>
>> Example:  Following the logic of the ScrollingCursor page, lets assume
>> a total result set of 88 titles.  If the lasttitle happens to be the
>> 29th title, so the set that is returned is 30 through 34, how do I
>> determine that this is the 6th page of a total of 18 pages?
>
>
> Let's assume 88 titles. For the sake of the argument, say you decide
> to display 12 per page. That would be 88 / 12 = 7.3 = 8 pages. If you
> need to know in which page a specific title lies (say 63), you can do
> something like ceil (63 % 12) = ceil (5.25) = 6th page. Following the
> same logic, ceil (88 / 12) = 8 pages total. Does that answer your
> question?

Actually, no it doesn't.  The math is simple enough, along with
finding the total count.  The question is how do I find out the number
of the title passed in...

According to the ScrollingCursor page, I should be passing in a title,
aka a string.  Since my record set is very dynamic, the title might be
the 63rd one time, but the next time it could be the 71st or 55th.

How do I find out the "index" of the start of the page as to apply
some basic math to find the page number?

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] How to implement paging

[Tito Ciuro]

Hi Sam,

On May 25, 2009, at 10:58 AM, Sam Carleton wrote:

> Example:  Following the logic of the ScrollingCursor page, lets assume
> a total result set of 88 titles.  If the lasttitle happens to be the
> 29th title, so the set that is returned is 30 through 34, how do I
> determine that this is the 6th page of a total of 18 pages?


Let's assume 88 titles. For the sake of the argument, say you decide  
to display 12 per page. That would be 88 / 12 = 7.3 = 8 pages. If you  
need to know in which page a specific title lies (say 63), you can do  
something like ceil (63 % 12) = ceil (5.25) = 6th page. Following the  
same logic, ceil (88 / 12) = 8 pages total. Does that answer your  
question?

Cheers,

-- Tito
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] How to implement paging

[Sam Carleton]

On Mon, May 25, 2009 at 2:31 PM, Simon Slavin
 wrote:
>
> On 25 May 2009, at 6:58pm, Sam Carleton wrote:
>
>> Example:  Following the logic of the ScrollingCursor page, lets assume
>> a total result set of 88 titles.  If the lasttitle happens to be the
>> 29th title, so the set that is returned is 30 through 34, how do I
>> determine that this is the 6th page of a total of 18 pages?
>
>
> You're going to have to know how many rows are displayed on a page.
> When you get your initial results back from the table, count the
> number of rows (or use the library function that returns it) and
> divide one by the other.  This gives you (more or less) the number of
> the last page of results.
>
> As well as keeping track of which page you're on, keep the current
> page number in a variable.  Just modify it when they hit 'next' or
> 'previous'.

So in the end, you are saying that it is completely and totally the
responsibility of the frontend to keep track of the page number,
correct?  The result set should simply return a total count so that
the # of pages can be calculated.  Correct?

> However, there's another way to do it.  If you know that you're never
> going to have more than a couple of hundred results, why display them
> as pages at all ?  Display them all, and provide a search function
> which lets people see just the records which contain their search
> field.  This is faster and more efficient than asking your users to
> wade through many pages.

That is a very good question.  The main reason is that my application
is a kiosk system that can be run on a touch screen display.  Paging
is much easier than scrolling on touch screens.

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [OT] How to implement paging

[Simon Slavin]

On 25 May 2009, at 6:58pm, Sam Carleton wrote:

> Example:  Following the logic of the ScrollingCursor page, lets assume
> a total result set of 88 titles.  If the lasttitle happens to be the
> 29th title, so the set that is returned is 30 through 34, how do I
> determine that this is the 6th page of a total of 18 pages?


You're going to have to know how many rows are displayed on a page.   
When you get your initial results back from the table, count the  
number of rows (or use the library function that returns it) and  
divide one by the other.  This gives you (more or less) the number of  
the last page of results.

As well as keeping track of which page you're on, keep the current  
page number in a variable.  Just modify it when they hit 'next' or  
'previous'.

However, there's another way to do it.  If you know that you're never  
going to have more than a couple of hundred results, why display them  
as pages at all ?  Display them all, and provide a search function  
which lets people see just the records which contain their search  
field.  This is faster and more efficient than asking your users to  
wade through many pages.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [OT] How to implement paging

[Sam Carleton]

I want to thank the mailing list in general you all have been very
helpful in my learning both SQLite and SQL in general.

I am working on paging right now and simply don't know the SQL way of
implementing it.  I have read through the SQLite page on scrolling
cursor (http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor), VERY
helpful.

One thing the page does not cover, and I think this is more general
SQL than SQLite, but how does one implement paging so that the user
knows which page they are on?

Example:  Following the logic of the ScrollingCursor page, lets assume
a total result set of 88 titles.  If the lasttitle happens to be the
29th title, so the set that is returned is 30 through 34, how do I
determine that this is the 6th page of a total of 18 pages?

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users