Puneet,
Thank you for your help.
Yes your solution would work. Due to the fact that i have many (up to 10) keys
within a huge table, I would have to recreate the temp table with a additional
key for every index or 'order by' clause I would ever use. This must happen
every day or at least every few days for these tables with up to 10 million
rows. That could use quite some resources.
It would be much easier if there is a way to query the internal btree table to
request at least the internal pagenr where the requested key is saved. so we
got an estimate i.e. Key is saved in Page 10000 from 20000 = 50%.
Due to the fact that this problem should have been faced by so many SQLite
users before - may be I'm on the completly wrong way...
George
-----Ursprüngliche Nachricht-----
Von: P Kishor <punk.k...@gmail.com>
An: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Datum: 24-02-2009 15:05
Betreff: Re: [sqlite] How to size and position a scrollbar within a virtual
listview
On Tue, Feb 24, 2009 at 3:16 AM, Mail.sqlite <mail.sql...@imtas.de> wrote:
>
> Hi All,
> I searched trough many messages and docs but did not find a solution to this
> trivial looking problem. It would be really nice if someone could point me
> into the right direction.
>
> my ToDo:
> I have to use a virtual listview for a database with some medium and large
> tables. I would give the user the opportunity to select the active displayed
> sorting order with a click on the corresponding listview header.
> To get the necessary number of records for the table with high performance,
> even for large files, this can be done with a trigger that updates a record
> elsewhere with every delete or insert operation.
>
> My Problem:
> how to get the relative position for the selected row depending on the
> selected “order by” clause as fast as possible? An estimate should be enough
> to position the scrollbar.
>
If you had sequential IDs of some sort in your returned result set,
this would be a trivial problem.
Of course, you are likely to not have a continuous sequence in a
database table because some rows may have been deleted. But, you can
fool it by creating a temp table to store your result set... consider
the following --
sqlite> create table foo (a integer primary key, b text);
sqlite> insert into foo (b) values ('blah');
sqlite> insert into foo (b) values ('grop');
sqlite> insert into foo (b) values ('farc');
sqlite> insert into foo (b) values ('drat');
sqlite> select rowid, a, b from foo;
1|1|blah
2|2|grop
3|3|farc
4|4|drat
sqlite> delete from foo where a = 3;
sqlite> select rowid, a, b from foo;
1|1|blah
2|2|grop
4|4|drat
sqlite> create temp table bar as select a, b from foo;
sqlite> select rowid, a, b from bar;
1|1|blah
2|2|grop
3|4|drat
sqlite>
Now we are back to where your problem is trivial. You can use a
rowid/count(*) value to determine the "position" of a row in an
ordered set.
> Thanks for any hints.
>
> George
>
--
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
Sent from: Madison WI United States.
_______________________________________________
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
