Questions from a novice - basic browsing of records in a listview.

classic Classic list List threaded Threaded
18 messages Options
-
Reply | Threaded
Open this post in threaded view
|

Questions from a novice - basic browsing of records in a listview.

-
Hello all,

I'm quite new at SQLite3, and have a bit of a problem with grasping the
handling of a  database.

After having used the OFFSET and LIMIT 1 method (in conjuction with a
userdata listview) and finding a past post into this forum describing it as
a rookie mistake I'm now trying to implement the "scrolling cursor" method
in that same post.  It leads to a few questions though.

For the above method to work for any database it means I need, for
each-and-every next/previous page request, to send *all* the bottom/top
records data back to the SQLite engine so it knows where to continue.  Even
when assuming the default maximum of columns the accumulated column names
and related data for the "WHERE" clause could get quite big.  Add to that a
possible the "SORT BY" clause and I'm looking at quite a large query, which
has to be created and transferred for every "scroll".  Which is something I
do not really like ...

1) Is it possible to refer to the columns in a kind of shorthand (index
perhaps) ?

2) Is it possible to have the SQLite engine initialize and remember certain
WHERE and ORDER clauses (without creating another database please :-) ), so
they can be used again-and-again (for the duration of a connection).

3) Is it possible, for the above 'scrolling cursor' method, to refer to a
starting record other than by sending the exact data of such a record back
to the SQLite engine ?

Ofcourse, feel (very) free to include other things that I've not thought
about and could be usefull. :-)

Regards,
Rudy Wieser



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

Philip Bennefall
How about using prepared statements in conjunction with bind?

http://www.sqlite.org/c3ref/bind_blob.html

Kind regards,

Philip Bennefall
On 2014-07-09 15:03, - wrote:

> Hello all,
>
> I'm quite new at SQLite3, and have a bit of a problem with grasping the
> handling of a  database.
>
> After having used the OFFSET and LIMIT 1 method (in conjuction with a
> userdata listview) and finding a past post into this forum describing it as
> a rookie mistake I'm now trying to implement the "scrolling cursor" method
> in that same post.  It leads to a few questions though.
>
> For the above method to work for any database it means I need, for
> each-and-every next/previous page request, to send *all* the bottom/top
> records data back to the SQLite engine so it knows where to continue.  Even
> when assuming the default maximum of columns the accumulated column names
> and related data for the "WHERE" clause could get quite big.  Add to that a
> possible the "SORT BY" clause and I'm looking at quite a large query, which
> has to be created and transferred for every "scroll".  Which is something I
> do not really like ...
>
> 1) Is it possible to refer to the columns in a kind of shorthand (index
> perhaps) ?
>
> 2) Is it possible to have the SQLite engine initialize and remember certain
> WHERE and ORDER clauses (without creating another database please :-) ), so
> they can be used again-and-again (for the duration of a connection).
>
> 3) Is it possible, for the above 'scrolling cursor' method, to refer to a
> starting record other than by sending the exact data of such a record back
> to the SQLite engine ?
>
> Ofcourse, feel (very) free to include other things that I've not thought
> about and could be usefull. :-)
>
> Regards,
> Rudy Wieser
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> .
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

Simon Slavin-3
In reply to this post by -

On 9 Jul 2014, at 2:03pm, - <[hidden email]> wrote:

> 1) Is it possible to refer to the columns in a kind of shorthand (index
> perhaps) ?
>
> 2) Is it possible to have the SQLite engine initialize and remember certain
> WHERE and ORDER clauses (without creating another database please :-) ), so
> they can be used again-and-again (for the duration of a connection).
>
> 3) Is it possible, for the above 'scrolling cursor' method, to refer to a
> starting record other than by sending the exact data of such a record back
> to the SQLite engine ?

You've just listed a lot of the concerns involved in the use of scroll-page-by-page.  And there are no good answers to them.

The convenience functions which would give you "Where am I currently in this index ?" don't exist.  If you want to do it you have to roll your own.  Nor is it possible to tell SQLite to preserve the temporary index it made up from your query terms (WHERE and ORDER) so you can reuse it.  Sorry about that.

However, the whole question is almost obsolete.  Users now scroll up and down displays so quickly and frequently that grabbing just one screen worth of data from a database is pointless.  Similarly, users will frequently start a query with a small window, then make the window larger (fullscreen ?) which means it shows more rows.

So rather than the old-style page-by-page listing, with the programming which goes into scrolling, modern systems tend to use a different style which doesn't have some of the concerns you list.  This involves storing and refetching different things as follows.  For my example I will use the following example

SELECT name,phonenumber FROM people WHERE phonenumber LIKE '01707%' ORDER BY name

1) When you need to open the window, collect which rows are returned.  Execute

SELECT rowid FROM people WHERE phonenumber LIKE '01707%' ORDER BY name

and store the array of resulting rowids, even if there are thousands of them.  At this point you don't care about column values at all.

2) When you need to display some rows, use your rowid array to figure out which records you need.  Once you know which rows you want execute something like one of the following, depending on how your code works and what your user is trying to do.

SELECT rowid,name,phonenumber FROM people WHERE rowid BETWEEN this AND that

or

SELECT rowid,name,phonenumber FROM people WHERE rowid IN (line1rid,line2rid,line3rid,...)

At this point you care only about column values and you never need to use SQL to scroll around in a table, which means you don't care about preserving indexes or index points or any of the tricky stuff.  You dealt with that all in step (1) and don't need it any more.

Lastly, the whole of the above ignores systems where the user (or another user !) may insert or delete a row that was in your foundset in another window, while it's being shown.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

Clemens Ladisch
In reply to this post by -
- wrote:
> After having used the OFFSET and LIMIT 1 method (in conjuction with a
> userdata listview) and finding a past post into this forum describing it as
> a rookie mistake I'm now trying to implement the "scrolling cursor" method
> in that same post.

Are you using a list view, or paging?  The scrolling cursor method
is appropriate only for the latter.

The easiest way to handle a list view would be to read the primary key of
_all_ records in the correct order into your application.

If the amount of data isn't too large, OFFSET/LIMIT works just fine.

> For the above method to work for any database it means I need, for
> each-and-every next/previous page request, to send *all* the bottom/top
> records data back to the SQLite engine so it knows where to continue.

Only data in those columns that you are using for sorting.  (But those
must be a unique key for the records.)

> 1) Is it possible to refer to the columns in a kind of shorthand (index
> perhaps) ?

No.

> 2) Is it possible to have the SQLite engine initialize and remember certain
> WHERE and ORDER clauses (without creating another database please :-) ), so
> they can be used again-and-again (for the duration of a connection).

Compiled statements can be reused (and the SQLite database drivers of
many languages have a statement cache).

However, this is unlikely to be a bottleneck.

> 3) Is it possible, for the above 'scrolling cursor' method, to refer to a
> starting record other than by sending the exact data of such a record back
> to the SQLite engine ?

No.  But SQLite has no client/server communication overhead.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

-
In reply to this post by Simon Slavin-3
Hello Simon,

> You've just listed a lot of the concerns involved in the use of
> scroll-page-by-page.  And there are no good answers to them.

Thanks for the above.  It means that I did my homework right. :-)

> Nor is it possible to tell SQLite to ...   Sorry about that.

I already got that feeling, but had to make sure (novice and all that). And
nothing to be sorry about (although it would have been usefull in this
case), just something I have to learn to work with (or rather, without).

> However, the whole question is almost obsolete.  Users now
> scroll up and down displays so quickly and frequently that
> grabbing just one screen worth of data from a database is
> pointless.

Yes, that was also a concern of mine.  But although I already had several
possible approaches to it (like a bit of caching and buttons scrolling more
than a single page, possibly related to the size of the database) I did not
want to concern myself and this forum with all of that at the same time.
One step at a time keeps things simple.

> Similarly, users will frequently start a query with a small
> window, then make the window larger (fullscreen ?)
> which means it shows more rows.

Already considered that, and found LVM_GETCOUNTPERPAGE to work quite well
for it.

> So rather than the old-style page-by-page listing, ....

I was also thinking in that direction.  Get all rowIDs first and use them.
In that regard, thanks for the "rowid IN (line1rid,line2rid,line3rid,...)"
hint, that takes care of one of my concerns of having to send a query for
each-and-every record in a page.

But it might cause another problem:  the database could get/be so large that
the ammount of memory needed to store all the rowIDs in could well exeede
the ammount of memory available to the program.
I could ofcourse use (or at that moment switch over to) a local file
(database!) to store them in, but somehow that feels a bit odd.

> At this point you care only about column values and you
> never need to use SQL to scroll around in a table,

It also restores the use of the listviews own slider to move about in the
list (do away with the "page up", "page down" buttons).  I must say I like
that.

> Lastly, the whole of the above ignores systems where the
> user (or another user !) may insert or delete a row that was
> in your foundset in another window, while it's being shown

I also thought of that, but wasn't prepared to think about the consequences
(good or bad) before the preceeding problems where solved.   My intended
approach to it was to add a "reload" button/key (F5) for it.

Thanks for the full (explanation, hints) reply.

Remark: I've not seen any reference in your reply to my first question where
I wondered if it would be possible to refer (in queries) to columns other
than by their full names. Must I assume its not possible ?

Regards,
Rudy Wieser


----- Original Message -----
From: Simon Slavin <[hidden email]>
To: General Discussion of SQLite Database <[hidden email]>
Sent: Wednesday, July 09, 2014 4:07 PM
Subject: Re: [sqlite] Questions from a novice - basic browsing of records
ina listview.


>
> On 9 Jul 2014, at 2:03pm, - <[hidden email]> wrote:
>
> > 1) Is it possible to refer to the columns in a kind of shorthand (index
> > perhaps) ?
> >
> > 2) Is it possible to have the SQLite engine initialize and remember
certain
> > WHERE and ORDER clauses (without creating another database please :-) ),
so
> > they can be used again-and-again (for the duration of a connection).
> >
> > 3) Is it possible, for the above 'scrolling cursor' method, to refer to
a
> > starting record other than by sending the exact data of such a record
back
> > to the SQLite engine ?
>
> You've just listed a lot of the concerns involved in the use of
scroll-page-by-page.  And there are no good answers to them.
>
> The convenience functions which would give you "Where am I currently in
this index ?" don't exist.  If you want to do it you have to roll your own.
Nor is it possible to tell SQLite to preserve the temporary index it made up
from your query terms (WHERE and ORDER) so you can reuse it.  Sorry about
that.
>
> However, the whole question is almost obsolete.  Users now scroll up and
down displays so quickly and frequently that grabbing just one screen worth
of data from a database is pointless.  Similarly, users will frequently
start a query with a small window, then make the window larger (fullscreen
?) which means it shows more rows.
>
> So rather than the old-style page-by-page listing, with the programming
which goes into scrolling, modern systems tend to use a different style
which doesn't have some of the concerns you list.  This involves storing and
refetching different things as follows.  For my example I will use the
following example
>
> SELECT name,phonenumber FROM people WHERE phonenumber LIKE '01707%' ORDER
BY name
>
> 1) When you need to open the window, collect which rows are returned.
Execute
>
> SELECT rowid FROM people WHERE phonenumber LIKE '01707%' ORDER BY name
>
> and store the array of resulting rowids, even if there are thousands of
them.  At this point you don't care about column values at all.
>
> 2) When you need to display some rows, use your rowid array to figure out
which records you need.  Once you know which rows you want execute something
like one of the following, depending on how your code works and what your
user is trying to do.
>
> SELECT rowid,name,phonenumber FROM people WHERE rowid BETWEEN this AND
that
>
> or
>
> SELECT rowid,name,phonenumber FROM people WHERE rowid IN
(line1rid,line2rid,line3rid,...)
>
> At this point you care only about column values and you never need to use
SQL to scroll around in a table, which means you don't care about preserving
indexes or index points or any of the tricky stuff.  You dealt with that all
in step (1) and don't need it any more.
>
> Lastly, the whole of the above ignores systems where the user (or another
user !) may insert or delete a row that was in your foundset in another
window, while it's being shown.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records ina listview.

-
In reply to this post by Philip Bennefall
Hello Philip,

> How about using prepared statements in conjunction with bind?

I also considered dat, and although it would certainly make the ammount of
data send after the preparation smaller, it could still mean shi(t/p)loads
of data being shutteled to-and-fro.

And although I find the "to" quite acceptable (I need it to be able to
display something :-) ), I do not think the same about the "fro" part
(having to send the just-received data back again) ....

Regards,
Rudy Wieser


----- Original Message -----
From: Philip Bennefall <[hidden email]>
To: General Discussion of SQLite Database <[hidden email]>
Sent: Wednesday, July 09, 2014 3:11 PM
Subject: Re: [sqlite] Questions from a novice - basic browsing of records
ina listview.


> How about using prepared statements in conjunction with bind?
>
> http://www.sqlite.org/c3ref/bind_blob.html
>
> Kind regards,
>
> Philip Bennefall
> On 2014-07-09 15:03, - wrote:
> > Hello all,
> >
> > I'm quite new at SQLite3, and have a bit of a problem with grasping the
> > handling of a  database.
> >
> > After having used the OFFSET and LIMIT 1 method (in conjuction with a
> > userdata listview) and finding a past post into this forum describing it
as
> > a rookie mistake I'm now trying to implement the "scrolling cursor"
method
> > in that same post.  It leads to a few questions though.
> >
> > For the above method to work for any database it means I need, for
> > each-and-every next/previous page request, to send *all* the bottom/top
> > records data back to the SQLite engine so it knows where to continue.
Even
> > when assuming the default maximum of columns the accumulated column
names
> > and related data for the "WHERE" clause could get quite big.  Add to
that a
> > possible the "SORT BY" clause and I'm looking at quite a large query,
which
> > has to be created and transferred for every "scroll".  Which is
something I
> > do not really like ...
> >
> > 1) Is it possible to refer to the columns in a kind of shorthand (index
> > perhaps) ?
> >
> > 2) Is it possible to have the SQLite engine initialize and remember
certain
> > WHERE and ORDER clauses (without creating another database please :-) ),
so
> > they can be used again-and-again (for the duration of a connection).
> >
> > 3) Is it possible, for the above 'scrolling cursor' method, to refer to
a
> > starting record other than by sending the exact data of such a record
back

> > to the SQLite engine ?
> >
> > Ofcourse, feel (very) free to include other things that I've not thought
> > about and could be usefull. :-)
> >
> > Regards,
> > Rudy Wieser
> >
> >
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > .
> >
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

Simon Slavin-3
In reply to this post by -

On 10 Jul 2014, at 12:54pm, - <[hidden email]> wrote:

> But it might cause another problem:  the database could get/be so large that
> the ammount of memory needed to store all the rowIDs in could well exeede
> the ammount of memory available to the program.
> I could ofcourse use (or at that moment switch over to) a local file
> (database!) to store them in, but somehow that feels a bit odd.

You could set a very big maximum (e.g. 5000 rows) on the assumption that users will never actually read or scroll through that many rows.  Use LIMIT 5000 and if you actually get 5000 rows returned put up a messages telling them if the row they want doesn't appear they should be more specific in their query.  Or some other cop-out.

>> At this point you care only about column values and you
>> never need to use SQL to scroll around in a table,
>
> It also restores the use of the listviews own slider to move about in the
> list (do away with the "page up", "page down" buttons).  I must say I like
> that.

We're all still adapting to the changes that the excellent GUIs and ridiculous speed of today's computers require.  I now have trivial little systems which reflect in realtime changes made by other users and in other windows, just because I needed to write those library routines for my 'big' systems.

> Lastly, the whole of the above ignores systems where the
>> user (or another user !) may insert or delete a row that was
>> in your foundset in another window, while it's being shown
>
> I also thought of that, but wasn't prepared to think about the consequences
> (good or bad) before the preceeding problems where solved.   My intended
> approach to it was to add a "reload" button/key (F5) for it.

You can deal with cases where the rowid no longer exists (as long as you do correctly test for it).  But yes, spotting new rows is harder.

> Thanks for the full (explanation, hints) reply.

The clarity of your question suggested that a long answer would be read and understood.

> Remark: I've not seen any reference in your reply to my first question where
> I wondered if it would be possible to refer (in queries) to columns other
> than by their full names. Must I assume its not possible ?

Just that I didn't understand the question well enough to answer it and was hoping someone else did.

It depends on what interface or shim you're using to access your database.  Although several things about SQL syntax betray the fact that columns have an order (for example, you can do INSERT without specifying columns and the third value gets put in the third column) there's no language in SQL to say things like 'column 3' and most APIs don't supply it.

On the other hand, if you were referring to the results of a SELECT, then results are always returned in the order you asked for them (apart from *), and you have to go to extra effort to find the names of the columns of the values that were returned.  So all you have to do is remember what you asked for.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

Teg-3
OP is in windows. Windows can send you display cache hints that tells
you what page it intends to display next. I use these notification to
load up a page worth of data at a time. Keep it in an internal cache.

>> the ammount of memory needed to store all the rowIDs in could well exeede
>> the ammount of memory available to the program.

You're  talking about 2 gigs in 32 bit windows and virtually unlimited
in 64 bit windows. I'd say this is a non-issue.

You get the list of RowID's then "SetItemCount" to tell the list
control how big it is. Then the user just scrolls/pages up/down
resizes as he will. Windows handles the rest. Windows sends you
notifications about what data it needs to display and cache hints
telling you what data it intends to display in the future.

You can use the same technique in non-windows environments like
Android. The details change but, the technique works there too.

SS> You could set a very big maximum (e.g. 5000 rows)

Using the technique discussed here, keeping a list of rowid's to seed
the virtual control, I've had virtual list controls with millions of
lines of records. It's slowish when I get over about 500K but,
functional. It's not really practical to scroll this data but, it
works and the RAM usage isn't really over the top. I don't consider
using a couple hundred megs for a list control to be unreasonable if
that's what the application calls for. Most PC's have more RAM then
they can ever use.

I  think you hit it on head when you suggest that most programmers are
mired  in  the  memory  limited  days.  Even android phones can handle
1000's of records in a list control.

If it's a list control with only 5000 records, you might be better off
loading the entire thing into memory. For my usage, that's a really
small list.


SS> On 10 Jul 2014, at 12:54pm, - <[hidden email]> wrote:

>> But it might cause another problem:  the database could get/be so large that
>> the ammount of memory needed to store all the rowIDs in could well exeede
>> the ammount of memory available to the program.
>> I could ofcourse use (or at that moment switch over to) a local file
>> (database!) to store them in, but somehow that feels a bit odd.

SS> You could set a very big maximum (e.g. 5000 rows) on the
SS> assumption that users will never actually read or scroll through
SS> that many rows.  Use LIMIT 5000 and if you actually get 5000 rows
SS> returned put up a messages telling them if the row they want
SS> doesn't appear they should be more specific in their query.  Or some other cop-out.

>>> At this point you care only about column values and you
>>> never need to use SQL to scroll around in a table,
>>
>> It also restores the use of the listviews own slider to move about in the
>> list (do away with the "page up", "page down" buttons).  I must say I like
>> that.

SS> We're all still adapting to the changes that the excellent GUIs
SS> and ridiculous speed of today's computers require.  I now have
SS> trivial little systems which reflect in realtime changes made by
SS> other users and in other windows, just because I needed to write
SS> those library routines for my 'big' systems.

>> Lastly, the whole of the above ignores systems where the
>>> user (or another user !) may insert or delete a row that was
>>> in your foundset in another window, while it's being shown
>>
>> I also thought of that, but wasn't prepared to think about the consequences
>> (good or bad) before the preceeding problems where solved.   My intended
>> approach to it was to add a "reload" button/key (F5) for it.

SS> You can deal with cases where the rowid no longer exists (as long
SS> as you do correctly test for it).  But yes, spotting new rows is harder.

>> Thanks for the full (explanation, hints) reply.

SS> The clarity of your question suggested that a long answer would be read and understood.

>> Remark: I've not seen any reference in your reply to my first question where
>> I wondered if it would be possible to refer (in queries) to columns other
>> than by their full names. Must I assume its not possible ?

SS> Just that I didn't understand the question well enough to answer
SS> it and was hoping someone else did.

SS> It depends on what interface or shim you're using to access your
SS> database.  Although several things about SQL syntax betray the
SS> fact that columns have an order (for example, you can do INSERT
SS> without specifying columns and the third value gets put in the
SS> third column) there's no language in SQL to say things like
SS> 'column 3' and most APIs don't supply it.

SS> On the other hand, if you were referring to the results of a
SS> SELECT, then results are always returned in the order you asked
SS> for them (apart from *), and you have to go to extra effort to
SS> find the names of the columns of the values that were returned.
SS> So all you have to do is remember what you asked for.

SS> Simon.
SS> _______________________________________________
SS> sqlite-users mailing list
SS> [hidden email]
SS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
Best regards,
 Teg                            mailto:[hidden email]

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

-
In reply to this post by Clemens Ladisch
Hello Clemens,

> Are you using a list view, or paging?  The scrolling
> cursor method is appropriate only for the latter.

I'm using a report-style listview.  And to be honest, I have no idea what a
"paging" component looks like (I'm using standard windows components).

> The easiest way to handle a list view would be to
> read the primary key of _all_ records in the correct
> order into your application.

True.  But as its my intention to create a generic SQLite3 viewer I have no
idea if the computer its used on will have enough memory to store such a
list, as the size of the database is unknown ....

> Only data in those columns that you are using for sorting.
> (But those must be a unique key for the records.)

:-) As for a generic viewer I have no control over that it means I need to
send *all* columns back, *in full*. (and yes, I see blobs creating a problem
there). :-\
Otherwise I either can get stuck (>=) or skip records (>)  when the WHERE
field contains more than a pages worth of the same data.   Already ran into
that ....

> > 1) Is it possible to refer to the columns in a kind of shorthand
> > (index perhaps) ?
>
> No.

Thats (too) bad.

> Compiled statements can be reused (and the SQLite database
> drivers of many languages have a statement cache).

How do I refer to a previously executed (and terminated) statement ?   If
that is not possible, how is that cache of use to whomever needs to repeat a
query ?

> However, this is unlikely to be a bottleneck.

Its not a bottleneck I'm worried about, it is having to cope with a
system/method/environment which demands me to do/send the same thing every
time I need something from it, or having to return data I just got from it.
It just bellows inefficiency to me.

> No.  But SQLite has no client/server communication overhead.

I'm sorry, but I have no idea why you mention that overhead.

The "overhead" I was thinking of is the one where the database has to
re-find a record it has just found and send me the contents of.  Again,
inefficiency.   Another "overhead" is my program having to keep track of
(possibly large ammounts of) data, only so I can send it back (a standard
listview only accepts upto, IIRC, 260 chars and discards the rest).

What I was thinking about was something in the line of "continue/start from
rowID {ID}".

Regards,
Rudy Wieser


----- Original Message -----
From: Clemens Ladisch <[hidden email]>
To: <[hidden email]>
Sent: Wednesday, July 09, 2014 4:15 PM
Subject: Re: [sqlite] Questions from a novice - basic browsing of records
ina listview.


> - wrote:
> > After having used the OFFSET and LIMIT 1 method (in conjuction with a
> > userdata listview) and finding a past post into this forum describing it
as
> > a rookie mistake I'm now trying to implement the "scrolling cursor"
method

> > in that same post.
>
> Are you using a list view, or paging?  The scrolling cursor method
> is appropriate only for the latter.
>
> The easiest way to handle a list view would be to read the primary key of
> _all_ records in the correct order into your application.
>
> If the amount of data isn't too large, OFFSET/LIMIT works just fine.
>
> > For the above method to work for any database it means I need, for
> > each-and-every next/previous page request, to send *all* the bottom/top
> > records data back to the SQLite engine so it knows where to continue.
>
> Only data in those columns that you are using for sorting.  (But those
> must be a unique key for the records.)
>
> > 1) Is it possible to refer to the columns in a kind of shorthand (index
> > perhaps) ?
>
> No.
>
> > 2) Is it possible to have the SQLite engine initialize and remember
certain
> > WHERE and ORDER clauses (without creating another database please :-) ),
so
> > they can be used again-and-again (for the duration of a connection).
>
> Compiled statements can be reused (and the SQLite database drivers of
> many languages have a statement cache).
>
> However, this is unlikely to be a bottleneck.
>
> > 3) Is it possible, for the above 'scrolling cursor' method, to refer to
a
> > starting record other than by sending the exact data of such a record
back

> > to the SQLite engine ?
>
> No.  But SQLite has no client/server communication overhead.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

Noel Frankinet-3
I've done it a long time ago before discovering that limit/offset was not
the way to go

http://www.codeproject.com/Articles/26938/AlphaView

Anyway, it worked quite well even on slow pda.
Best wishes
Noel


On 10 July 2014 15:19, - <[hidden email]> wrote:

> Hello Clemens,
>
> > Are you using a list view, or paging?  The scrolling
> > cursor method is appropriate only for the latter.
>
> I'm using a report-style listview.  And to be honest, I have no idea what a
> "paging" component looks like (I'm using standard windows components).
>
> > The easiest way to handle a list view would be to
> > read the primary key of _all_ records in the correct
> > order into your application.
>
> True.  But as its my intention to create a generic SQLite3 viewer I have no
> idea if the computer its used on will have enough memory to store such a
> list, as the size of the database is unknown ....
>
> > Only data in those columns that you are using for sorting.
> > (But those must be a unique key for the records.)
>
> :-) As for a generic viewer I have no control over that it means I need to
> send *all* columns back, *in full*. (and yes, I see blobs creating a
> problem
> there). :-\
> Otherwise I either can get stuck (>=) or skip records (>)  when the WHERE
> field contains more than a pages worth of the same data.   Already ran into
> that ....
>
> > > 1) Is it possible to refer to the columns in a kind of shorthand
> > > (index perhaps) ?
> >
> > No.
>
> Thats (too) bad.
>
> > Compiled statements can be reused (and the SQLite database
> > drivers of many languages have a statement cache).
>
> How do I refer to a previously executed (and terminated) statement ?   If
> that is not possible, how is that cache of use to whomever needs to repeat
> a
> query ?
>
> > However, this is unlikely to be a bottleneck.
>
> Its not a bottleneck I'm worried about, it is having to cope with a
> system/method/environment which demands me to do/send the same thing every
> time I need something from it, or having to return data I just got from it.
> It just bellows inefficiency to me.
>
> > No.  But SQLite has no client/server communication overhead.
>
> I'm sorry, but I have no idea why you mention that overhead.
>
> The "overhead" I was thinking of is the one where the database has to
> re-find a record it has just found and send me the contents of.  Again,
> inefficiency.   Another "overhead" is my program having to keep track of
> (possibly large ammounts of) data, only so I can send it back (a standard
> listview only accepts upto, IIRC, 260 chars and discards the rest).
>
> What I was thinking about was something in the line of "continue/start from
> rowID {ID}".
>
> Regards,
> Rudy Wieser
>
>
> ----- Original Message -----
> From: Clemens Ladisch <[hidden email]>
> To: <[hidden email]>
> Sent: Wednesday, July 09, 2014 4:15 PM
> Subject: Re: [sqlite] Questions from a novice - basic browsing of records
> ina listview.
>
>
> > - wrote:
> > > After having used the OFFSET and LIMIT 1 method (in conjuction with a
> > > userdata listview) and finding a past post into this forum describing
> it
> as
> > > a rookie mistake I'm now trying to implement the "scrolling cursor"
> method
> > > in that same post.
> >
> > Are you using a list view, or paging?  The scrolling cursor method
> > is appropriate only for the latter.
> >
> > The easiest way to handle a list view would be to read the primary key of
> > _all_ records in the correct order into your application.
> >
> > If the amount of data isn't too large, OFFSET/LIMIT works just fine.
> >
> > > For the above method to work for any database it means I need, for
> > > each-and-every next/previous page request, to send *all* the bottom/top
> > > records data back to the SQLite engine so it knows where to continue.
> >
> > Only data in those columns that you are using for sorting.  (But those
> > must be a unique key for the records.)
> >
> > > 1) Is it possible to refer to the columns in a kind of shorthand (index
> > > perhaps) ?
> >
> > No.
> >
> > > 2) Is it possible to have the SQLite engine initialize and remember
> certain
> > > WHERE and ORDER clauses (without creating another database please :-)
> ),
> so
> > > they can be used again-and-again (for the duration of a connection).
> >
> > Compiled statements can be reused (and the SQLite database drivers of
> > many languages have a statement cache).
> >
> > However, this is unlikely to be a bottleneck.
> >
> > > 3) Is it possible, for the above 'scrolling cursor' method, to refer to
> a
> > > starting record other than by sending the exact data of such a record
> back
> > > to the SQLite engine ?
> >
> > No.  But SQLite has no client/server communication overhead.
> >
> >
> > Regards,
> > Clemens
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
Noël Frankinet
Strategis sprl
0478/90.92.54
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records ina listview.

-
In reply to this post by Simon Slavin-3
Hello Simon,

> You could set a very big maximum (e.g. 5000 rows) on
> the assumption that users will never actually read or scroll
> through that many rows.

:-)  In that case I would simply use a simple listview (a listview can
handle upto 32000 records), and see if I can use its lazy data retrieval
method (never used that method, but there is a first time for everything).

Apart from the problems I would need to solve by *not* imposing some
abitrary limit (and learn from that ofcourse), it was/is my intention to be
able to fully browse a table no matter how long (upto the limits set by
SQLite3, although those seem to be quite vague).

> It depends on what interface or shim you're using to access your database.

I'm programming directly against the SQLite3 DLL, mostly using
"sqlite3_prepare".

> ... there's no language in SQL to say things like 'column 3'
> and most APIs don't supply it.

Shucks!  That means that I need to use the column names in their full glory,
no matter how long they are.   And as far as I can tell they can be *long*
(upto 2 or 4 gigs?), and could, with a few columns easily exhaust the
specified buffer size for a query (IIRC, 110 KByte).

> On the other hand, if you were referring to the results
> of a SELECT, then results are always returned in the
> order you asked for them

Yeah, thats another funny thing.  To be *sure* about the order of the
columns, how the full record is sorted and from which record the "rolling
cursor" should continue you're sending the same order of columns 3 times in
one query ...

> (apart from *)

Guess what: for a simple table query thats probably the most-used selection.
:-(

Currently I'm assuming that querying a table will keep the results in order
of the columns in the table.  Up until now that seems to be true.

Man, trying to understand reasons the designers of the SQL language did
certain things in a certain way gives me a headache. :-\

Regards,
Rudy Wieser


----- Original Message -----
From: Simon Slavin <[hidden email]>
To: General Discussion of SQLite Database <[hidden email]>
Sent: Thursday, July 10, 2014 2:17 PM
Subject: Re: [sqlite] Questions from a novice - basic browsing of records
ina listview.


>
> On 10 Jul 2014, at 12:54pm, - <[hidden email]> wrote:
>
> > But it might cause another problem:  the database could get/be so large
that
> > the ammount of memory needed to store all the rowIDs in could well
exeede
> > the ammount of memory available to the program.
> > I could ofcourse use (or at that moment switch over to) a local file
> > (database!) to store them in, but somehow that feels a bit odd.
>
> You could set a very big maximum (e.g. 5000 rows) on the assumption that
users will never actually read or scroll through that many rows.  Use LIMIT
5000 and if you actually get 5000 rows returned put up a messages telling
them if the row they want doesn't appear they should be more specific in
their query.  Or some other cop-out.
>
> >> At this point you care only about column values and you
> >> never need to use SQL to scroll around in a table,
> >
> > It also restores the use of the listviews own slider to move about in
the
> > list (do away with the "page up", "page down" buttons).  I must say I
like
> > that.
>
> We're all still adapting to the changes that the excellent GUIs and
ridiculous speed of today's computers require.  I now have trivial little
systems which reflect in realtime changes made by other users and in other
windows, just because I needed to write those library routines for my 'big'
systems.
>
> > Lastly, the whole of the above ignores systems where the
> >> user (or another user !) may insert or delete a row that was
> >> in your foundset in another window, while it's being shown
> >
> > I also thought of that, but wasn't prepared to think about the
consequences
> > (good or bad) before the preceeding problems where solved.   My intended
> > approach to it was to add a "reload" button/key (F5) for it.
>
> You can deal with cases where the rowid no longer exists (as long as you
do correctly test for it).  But yes, spotting new rows is harder.
>
> > Thanks for the full (explanation, hints) reply.
>
> The clarity of your question suggested that a long answer would be read
and understood.
>
> > Remark: I've not seen any reference in your reply to my first question
where
> > I wondered if it would be possible to refer (in queries) to columns
other
> > than by their full names. Must I assume its not possible ?
>
> Just that I didn't understand the question well enough to answer it and
was hoping someone else did.
>
> It depends on what interface or shim you're using to access your database.
Although several things about SQL syntax betray the fact that columns have
an order (for example, you can do INSERT without specifying columns and the
third value gets put in the third column) there's no language in SQL to say
things like 'column 3' and most APIs don't supply it.
>
> On the other hand, if you were referring to the results of a SELECT, then
results are always returned in the order you asked for them (apart from *),
and you have to go to extra effort to find the names of the columns of the
values that were returned.  So all you have to do is remember what you asked
for.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records ina listview.

R Smith

On 2014/07/10 16:04, - wrote:

>> You could set a very big maximum (e.g. 5000 rows) on
>> the assumption that users will never actually read or scroll
>> through that many rows.
> :-)  In that case I would simply use a simple listview (a listview can
> handle upto 32000 records), and see if I can use its lazy data retrieval
> method (never used that method, but there is a first time for everything).
>
> Apart from the problems I would need to solve by *not* imposing some
> abitrary limit (and learn from that ofcourse), it was/is my intention to be
> able to fully browse a table no matter how long (upto the limits set by
> SQLite3, although those seem to be quite vague).

Hi Rudy,

Firstly, while I understand the notion of not wanting arbitrary limits - this is just a silly notion when placed under scrutiny.
What is "enough"? How long is a piece of string?
The notion of "no matter how long" is just not feasible. What if the table has 10^16 or more items? (This is more than the amount of
stars in the known universe, so it's probably not likely, but what if it is?) You would need a cray or something to even access a
list like that. Limits are inherent and the best practice is to start out with a very specific limit-universe in mind.

Further to this - the limits are very specific in SQLIte and not vague or fuzzy at all - though most all of them can be adjusted to
suit folks like yourself who wish to push the limits.

Lastly, more pertinent to the question - yes, if you have any kind of dataset/table which is larger than what is comfortable for the
intended devices in a full-load scenario, the best (but not only) other solution is lazy-retrieval, which you already seem familiar
with so I would advise to go with that. I have an example system if you like to see which can display insane amounts of data at
lightning speed using just this sort of convention, but it still is limited to 2^63-1 items, a limit which approaches the total
number of atoms on earth (~ 1.3 x 10^51) and as such is unlikely to ever span a dataset small enough to fit in physical memory of
any size which are made from atoms available on earth - but it still is a limit.  It still gets a bit sticky after about a billion
items and more importantly, as Simon alluded to, it is silly to "display" any list which is so long that it cannot possibly be read
by a human - what would be the purpose of that?  And as other posters alluded to, human readability diminishes very long before the
listing abilities of even mediocre modern systems.

> Shucks! That means that I need to use the column names in their full glory, no matter how long they are. And as far as I can tell
> they can be *long* (upto 2 or 4 gigs?), and could, with a few columns easily exhaust the specified buffer size for a query (IIRC,
> 110 KByte).

It's ~2 giga-characters for a Unicode-enabled string. Anyone who makes column names that long has issues that can only be solved by
a medical doctor, not an SQL engine. (and as such SQLite or any other engine does not really go to lengths to specifically cater for
handling those length column names efficiently - the same reason they do not care to cater for saving 256-bit integers natively,
even though those numbers are sometimes used by people).

Do not confuse system design allowances with actual usage conventions. If you make an app that counts how many hamburgers one eats
in a day, you do not need to make the display counter wide enough to fit 2^32 digits, even if that is the system design limit, I
think we can safely assume no physical human of the kind that traverse the Earth will top a 4-digit number, even in America.

Moral of the story: Pick a limit and build the system according to that.


> Yeah, thats another funny thing. To be *sure* about the order of the columns, how the full record is sorted and from which record
> the "rolling cursor" should continue you're sending the same order of columns 3 times in one query ...

How is that a funny thing? The SQL Engine is under obligation to return EXACTLY what you ASK for, or return all results when you do
not ask for a specific set. it does this, everytime without fail. It is under no obligation to return a specific form or format when
you don't ask for any, and more importantly, this behaviour is not a silly design quirk or oversight of the SQL engine specification
or the designers of any SQL engine, it is specifically required to avoid spending system resources on nonsense ordering and
formatting and the like when in 99+ % of cases it is not needed at all by the reading construct. It has to tie up though, if you
specified the columns in a specific order. This is WHY you specify the columns, and don't worry, it is all cached very nicely inside
SQLite, there is negligible overhead for it.

> Guess what: for a simple table query thats probably the most-used selection. :-( Currently I'm assuming that querying a table will
> keep the results in order of the columns in the table. Up until now that seems to be true. Man, trying to understand reasons the
> designers of the SQL language did certain things in a certain way gives me a headache. :-\

If the reasons are hard to comprehend, maybe it is a good thing that you are not tasked with making them. (smily face)

Seriously though, as a mere matter of ease of doing, currently a * columnset is returned in exactly the same rhythm the table schema
supplies it and you are quite welcome to use it as such. It is even more solid to assume the order will be the same as the previous
run of the same query layout, even if it doesn't reflect the table schema. The point the devs always make is that SQLite (or any
other engine) is under no obligation to do it exactly like that in a next version, so if your system depends on what is merely a
lucky happenstance now, it might not work the same in future, i.e. best practice is to design your system based on the practices
that we absolutely know and trust to be carried forward in next versions. That said, if you decided to make a system "trusting" the
* query output order 10 years ago, it would till this day still work... but historic trends do not prove or inform future trends.
(mostly).

I hope this helps to alleviate your headaches slightly.

In the (lightly paraphrased) words of one of my favourite movie villains: " 'Quick and easy' is how you bake a cake, not how you
design a databasing system... "

Have a great day!
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records ina listview.

R Smith
Quick typo/fact check:

...// What if the table has 10^16 or more items? (This is more than the amount of stars in the known universe//...

should of course read:

...// What if the table has 10^24 or more items? (This is more than the amount of stars in the observable universe//...



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

-
In reply to this post by R Smith
Hello Ryan,

> What if the table has 10^16 or more items?

Is that number within the limits as set by SQLite3 ?  Than its my intention
to handle it.

*How* I would handle it is a whole other matter.   The "scrolling cursor"
method would be good for that, as it does only works with (very) small parts
of the total database (just enough so a windows worth of data can be
displayed).

Ofcourse, that method has got it drawbacks too.   Why do you think I asked
my question in the first place ? :-)

> Limits are inherent and the best practice is to start out
> with a very specific limit-universe in mind.

True. So, where can I find those limits in regard to sqlite3 ?   The
http://www.sqlite.org/limits.html page mentiones a few, but its very vague
about *actual* limits.

> Further to this - the limits are very specific in SQLIte and not vague or
fuzzy at all -

Oh, there are some there, like a SQLITE_MAX_SQL_LENGTH of (max) a gig, and a
SQLITE_MAX_COLUMN of (max) 32767. Together that would mean a maximum
column-name length of about 32768 chars, but *that* limit is not mentioned
anywhere, but probably is much larger.  How much ?  No idea, but I took the
assumption that coulumn names can be as large as the data in such columns,
which is, according to the above document, 2 gig.   Mind you, just one
(crazy long, but legal) column name would not even fit in a query.

Also, the above SQLITE_MAX_COLUMN can be changed at *compile* time.  What am
I supposed to do with my regular API (max 2000 columns) when encountering a
database made with such an altered API ?  Will it even cause troubles at all
?

*That* is what I mean with "vague or fuzzy".

> most all of them can be adjusted to suit folks like yourself
> who wish to push the limits.

I'm afraid you misunderstood. Its not my intention to push those limits, but
I'll be damned if I let my intended basic database-browser refuse to display
a legal table because *I* could not find a way to handle it.

Maybe in the end I still have to conceede defeat and use a few arbitrary
limits, taking the risk the program cannot handle every table. But not
before I tried my d*mn best not to do let that happen. :-)

> yes, if you have any kind of dataset/table which is larger
> than what is comfortable for the intended devices in a
> full-load scenario, the best (but not only) other solution
> is lazy-retrieval, which you already seem familiar with

Well, a kind of lazy retrieval is what I tried at first, using a virtual
listview.  Alas, the LIMIT/OFFSET wasn't the correct way.

The "rolling cursor" method looks a *lot* better (no rowcount limit, very
little actual data stored), but as you might have noticed, I'm a peeved off
on the ammount of data I would need to shuttle to-and-fro (for the "continue
from this record" clause).

The "full-load scenario" sounds nice, but severely limits the size of the
table that can be handled (assuming the rowID table will be stored in
memory).

> and more importantly, as Simon alluded to, it is silly to
> "display" any list which is so long that it cannot possibly
> be read by a human

Well, I wanted to start with browsing.  Adding selections to limit the
output (possibly also hiding interresting entries!) could come later.

The idea behind that is that while browsing you might find stuff that looks
interresting, something that could easily get hidden when limiting the
output (using a LIKE clause)

> It's ~2 giga-characters for a Unicode-enabled string.
> Anyone who makes column names that long has issues
> that can only be solved by a medical doctor, not an
> SQL engine.

Agreed.  But it *is* possible, so a generic browser should be able to handle
it (why did you think I was asking if there was a short-hand for colum-names
is available).

> Do not confuse system design allowances with actual usage conventions.

Is anyone bound to stay within those "actual usage conventions" ?   If not
than its meaningless to me, sorry.

> Moral of the story: Pick a limit and build the system according to that.

And when a fully legal table gets rejected because of such arbitrary limits
I would not really be content with myself (to put it lightly).

> > Yeah, thats another funny thing. To be *sure* about the order of the
columns, ....
>
> How is that a funny thing?

Well, almost the first thing I learned (way back when) about databases is
that duplicate data is *bad*.  And now SQL queries look to be promoting it
...

> > Man, trying to understand reasons the designers of the
> > SQL language did certain things in a certain way gives
> > me a headache. :-\
>
> If the reasons are hard to comprehend, maybe it is a good
> thing that you are not tasked with making them. (smily face)

Wholeheartedly agreed.

> The point the devs always make is that SQLite (or any
> other engine) is under no obligation to do it exactly like
> that in a next version,

Which is why I'm attemting to do it "the right way".  ... Which than brought
me in collision with vague limits.

> I hope this helps to alleviate your headaches slightly.

Not really, but I'm going to try to digest it.

Thanks for your help.

> Have a great day!

And the same to you.

Regards,
Rudy Wieser


----- Original Message -----
From: RSmith <[hidden email]>
To: <[hidden email]>
Sent: Thursday, July 10, 2014 5:31 PM
Subject: Re: [sqlite] Questions from a novice - basic browsing of recordsina
listview.


>
> On 2014/07/10 16:04, - wrote:
> >> You could set a very big maximum (e.g. 5000 rows) on
> >> the assumption that users will never actually read or scroll
> >> through that many rows.
> > :-)  In that case I would simply use a simple listview (a listview can
> > handle upto 32000 records), and see if I can use its lazy data retrieval
> > method (never used that method, but there is a first time for
everything).
> >
> > Apart from the problems I would need to solve by *not* imposing some
> > abitrary limit (and learn from that ofcourse), it was/is my intention to
be
> > able to fully browse a table no matter how long (upto the limits set by
> > SQLite3, although those seem to be quite vague).
>
> Hi Rudy,
>
> Firstly, while I understand the notion of not wanting arbitrary limits -
this is just a silly notion when placed under scrutiny.
> What is "enough"? How long is a piece of string?
> The notion of "no matter how long" is just not feasible. What if the table
has 10^16 or more items? (This is more than the amount of
> stars in the known universe, so it's probably not likely, but what if it
is?) You would need a cray or something to even access a
> list like that. Limits are inherent and the best practice is to start out
with a very specific limit-universe in mind.
>
> Further to this - the limits are very specific in SQLIte and not vague or
fuzzy at all - though most all of them can be adjusted to
> suit folks like yourself who wish to push the limits.
>
> Lastly, more pertinent to the question - yes, if you have any kind of
dataset/table which is larger than what is comfortable for the
> intended devices in a full-load scenario, the best (but not only) other
solution is lazy-retrieval, which you already seem familiar
> with so I would advise to go with that. I have an example system if you
like to see which can display insane amounts of data at
> lightning speed using just this sort of convention, but it still is
limited to 2^63-1 items, a limit which approaches the total
> number of atoms on earth (~ 1.3 x 10^51) and as such is unlikely to ever
span a dataset small enough to fit in physical memory of
> any size which are made from atoms available on earth - but it still is a
limit.  It still gets a bit sticky after about a billion
> items and more importantly, as Simon alluded to, it is silly to "display"
any list which is so long that it cannot possibly be read
> by a human - what would be the purpose of that?  And as other posters
alluded to, human readability diminishes very long before the
> listing abilities of even mediocre modern systems.
>
> > Shucks! That means that I need to use the column names in their full
glory, no matter how long they are. And as far as I can tell
> > they can be *long* (upto 2 or 4 gigs?), and could, with a few columns
easily exhaust the specified buffer size for a query (IIRC,
> > 110 KByte).
>
> It's ~2 giga-characters for a Unicode-enabled string. Anyone who makes
column names that long has issues that can only be solved by
> a medical doctor, not an SQL engine. (and as such SQLite or any other
engine does not really go to lengths to specifically cater for
> handling those length column names efficiently - the same reason they do
not care to cater for saving 256-bit integers natively,
> even though those numbers are sometimes used by people).
>
> Do not confuse system design allowances with actual usage conventions. If
you make an app that counts how many hamburgers one eats
> in a day, you do not need to make the display counter wide enough to fit
2^32 digits, even if that is the system design limit, I
> think we can safely assume no physical human of the kind that traverse the
Earth will top a 4-digit number, even in America.
>
> Moral of the story: Pick a limit and build the system according to that.
>
>
> > Yeah, thats another funny thing. To be *sure* about the order of the
columns, how the full record is sorted and from which record
> > the "rolling cursor" should continue you're sending the same order of
columns 3 times in one query ...
>
> How is that a funny thing? The SQL Engine is under obligation to return
EXACTLY what you ASK for, or return all results when you do
> not ask for a specific set. it does this, everytime without fail. It is
under no obligation to return a specific form or format when
> you don't ask for any, and more importantly, this behaviour is not a silly
design quirk or oversight of the SQL engine specification
> or the designers of any SQL engine, it is specifically required to avoid
spending system resources on nonsense ordering and
> formatting and the like when in 99+ % of cases it is not needed at all by
the reading construct. It has to tie up though, if you
> specified the columns in a specific order. This is WHY you specify the
columns, and don't worry, it is all cached very nicely inside
> SQLite, there is negligible overhead for it.
>
> > Guess what: for a simple table query thats probably the most-used
selection. :-( Currently I'm assuming that querying a table will
> > keep the results in order of the columns in the table. Up until now that
seems to be true. Man, trying to understand reasons the
> > designers of the SQL language did certain things in a certain way gives
me a headache. :-\
>
> If the reasons are hard to comprehend, maybe it is a good thing that you
are not tasked with making them. (smily face)
>
> Seriously though, as a mere matter of ease of doing, currently a *
columnset is returned in exactly the same rhythm the table schema
> supplies it and you are quite welcome to use it as such. It is even more
solid to assume the order will be the same as the previous
> run of the same query layout, even if it doesn't reflect the table schema.
The point the devs always make is that SQLite (or any
> other engine) is under no obligation to do it exactly like that in a next
version, so if your system depends on what is merely a
> lucky happenstance now, it might not work the same in future, i.e. best
practice is to design your system based on the practices
> that we absolutely know and trust to be carried forward in next versions.
That said, if you decided to make a system "trusting" the
> * query output order 10 years ago, it would till this day still work...
but historic trends do not prove or inform future trends.
> (mostly).
>
> I hope this helps to alleviate your headaches slightly.
>
> In the (lightly paraphrased) words of one of my favourite movie villains:
" 'Quick and easy' is how you bake a cake, not how you
> design a databasing system... "
>
> Have a great day!
> Ryan
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

Clemens Ladisch
In reply to this post by -
- wrote:
>> Are you using a list view, or paging?  The scrolling
>> cursor method is appropriate only for the latter.
>
> I'm using a report-style listview.  And to be honest, I have no idea what a
> "paging" component looks like (I'm using standard windows components).

This would be a list without scroll bar but with previous/next page
buttons; mostly used in HTML pages.

(Without prev/next, you do not have the previous row whose values you
could use as basis for fetching the next rows.)

>> The easiest way to handle a list view would be to
>> read the primary key of _all_ records in the correct
>> order into your application.
>
> True.  But as its my intention to create a generic SQLite3 viewer I have no
> idea if the computer its used on will have enough memory to store such a
> list, as the size of the database is unknown ....

When you have a table with millions of rows, and the user happens to
scroll to the 1234567th row, how do you get that row without knowing its
rowid or primary key?

And for huge tables, "browsing" does not make sense.  Even if there were
some interesting value in some row, you wouldn't be able to find it.

> What I was thinking about was something in the line of "continue/start from
> rowID {ID}".

This is possible (if the table was not declared with WITHOUT ROWID, and
if there are no other columns named rowid, _rowid_, or oid).  However,
in a list view, you are not guaranteed to know the rowid to start from.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records in a listview.

R Smith
In reply to this post by -

>
>> What if the table has 10^16 or more items?
> Is that number within the limits as set by SQLite3 ?  Than its my intention
> to handle it.

No, I just made that up out of thin air. SQLite's maximum row limit is 2^63-1 I believe. It is unreachable on current physical media.

> Oh, there are some there, like a SQLITE_MAX_SQL_LENGTH of (max) a gig, and a SQLITE_MAX_COLUMN of (max) 32767. Together that would
> mean a maximum column-name length of about 32768 chars//....

No, it does not mean that at all. Your inference is not only wrong but also unneeded, meaning that you are imagining relationships
where there are none. The limits for max sql length and max column are very clear, and in no way and by no virtue does it imply that
the one informs the other. Why do you imagine that this is necessarily so?

To be clear - when you go inside an elevator - you might see a weight limit stated as "500Kg / 13 Persons". This does not mean the
limit per person is 500/13=38Kg at all, and there is no reason in the known universe to imagine that it does. (Good thing too cause
I won't ever get to use the elevator). It does mean that even if you find an array of 20Kg children, you still cannot pack more than
13 in there, and if you have 5 really big (100Kg+) people hopping on ahead of you, best to wait for the next one. The limit
statement is not fuzzy.

> ...//Also, the above SQLITE_MAX_COLUMN can be changed at *compile* time. What am I supposed to do with my regular API (max 2000
> columns) when encountering a database made with such an altered API ? Will it even cause troubles at all ? *That* is what I mean
> with "vague or fuzzy".

If this was true it would be reason for concern - but the limit you refer to is a SQL language construct limit, not a table-width
limit so the worry is not justified - but In a query one might be able to dream up a set of extracted columns that tops the query
limit easily even if the underlying tables only sports a few columns. For this you will need to plan. The paradigm here is to cater
for what is relevant. Very few people make queries longer than a few columns, but they might. It's a kind of bell curve, and if the
users are those lying at the 6th+ standard deviation of column-count requirements, chances are they will have compiled their own
sqlite version by now, and if they did not, ask them politely to adhere to whichever limit you picked. There is no SQLite-imposed
hard limit (other than the physical), in stead, it supports the wide gamut of needs that cover the 99% bulk, and people with special
needs roll their own (using the various compiler directives and the like).

You don't even need to check this, SQLite will do it for you. Send a query with 101 columns, it will return a
DUDE_THATS_TOO_MANY_COLUMNS result in stead, the (real) codes being available in the documentation. But if you like there is nothing
wrong with setting your own maximum columns and doing your own checking, but the ability doesn't mean SQLite is soft in the head -
just that it isn't restrictive and you have some freedom of design.

>> most all of them can be adjusted to suit folks like yourself
>> who wish to push the limits.
> I'm afraid you misunderstood. Its not my intention to push those limits, but
> I'll be damned if I let my intended basic database-browser refuse to display
> a legal table because *I* could not find a way to handle it.

Not misunderstood, just a bit tongue-in-cheek, but the nuance probably misplaced, I apologise.

> Maybe in the end I still have to conceede defeat and use a few arbitrary
> limits, taking the risk the program cannot handle every table. But not
> before I tried my d*mn best not to do let that happen. :-)

There is no such risk. SQLite will open each and every valid table in existence, and you can query it so long as the query itself
conforms. In this regard you are quite justified to fear a table with 200 columns and you have a 99 col query limit, so you wont be
able to ask for every column by name, though * will still work.  One might say that 99 columns is more than any user might want to
or be able to really look at... but if you disagree (and even I disagree, I think probably 200 is closer to a sensible human limit),
then you might simply decide what it is that you feel would be the most anyone can useful observe in a query, say 500 if you like,
or 1000, and make that your limit. And then, if ever a query needs more than those, split it into 2 queries. SQL DBA's are not as
dumb as you might think, they are very used to working within limits. (Or maybe your intended user is of a different mindset, but
that is up to you to figure out).


> Well, I wanted to start with browsing. Adding selections to limit the output (possibly also hiding interresting entries!) could
> come later. The idea behind that is that while browsing you might find stuff that looks interresting, something that could easily
> get hidden when limiting the output (using a LIKE clause)

Ok, I'm not sure I agree with this one, but definitely willing to give the benefit of the doubt. The solution is again something
like: You need to decide what would be a sensible limit and go with that. SQLite will supply any and all records you ask for, it has
no thoughts on limits in this regard (other than the 64-bit rowid upper bound I mentioned earlier, but as I also mentioned, no HDD
in existence has near that many bits on it, never-mind possible data slots, so reaching that limit simply won't happen and catering
for it requires a special breed of star-trek fan).

>> It's ~2 giga-characters for a Unicode-enabled string.
>> Anyone who makes column names that long has issues
>> that can only be solved by a medical doctor, not an
>> SQL engine.
> Agreed.  But it *is* possible, so a generic browser should be able to handle
> it (why did you think I was asking if there was a short-hand for colum-names
> is available).

Again, apologies for the nuance - I wrongly assumed the statement was implicit. Yes an absurdly long column name is possible (if not
plausible), but how is this a problem? SQLite will handle it, it will report it and will correctly retrieve data for it. As long as
you can hold it in memory in your app and display it to your user. Also, again, feel free to limit the viewable size of a column
name for making the app easier to use (but do not limit the length SQLite can report to your app, obviously), either way SQLite
doesn't care, that is your decision. Internally by the way, SQLite stores it as a hash (other than in the schema), so it really
doesn't care. As long as your maximum query length fits inside the length specified (or which you specified), all is well.


> Is anyone bound to stay within those "actual usage conventions" ? If not than its meaningless to me, sorry.

I think you missed the point on this one, I did not say the convention is a rule or needs to be...  I simply meant as explained
earlier, another way might be to say: SQLite doesn't prescribe limited limits (if you'll excuse the tautology) because it caters for
programmers, but you don't, you cater for users, you should ponder the conventions and design limits that you imagine suitable.

i.e. It is me trying to make a helpful statement (and possibly botching it) about how you might think of the implementation and not
to be confused with reflecting on sqlite's limits or paradigms, those are what they are and the limit documentation is not fuzzy
about it.

>> Moral of the story: Pick a limit and build the system according to that.
> And when a fully legal table gets rejected because of such arbitrary limits
> I would not really be content with myself (to put it lightly).

Rejected by whom? Certainly not SQLite.
I hope the answers above show that this is not possible, and you can rest easy now.


> Well, almost the first thing I learned (way back when) about databases is
> that duplicate data is *bad*.  And now SQL queries look to be promoting it
> ...

Data and Query constructs are not the same thing. In one duplication is bad, in the other it is an arbitrary part of a control signal.
Do you not say the same words "Good morning" to the same colleagues every day? Or wait for the same green light to switch on before
crossing the road?


> Which is why I'm attemting to do it "the right way". ... Which than brought me in collision with vague limits.

I sincerely hope the non-vagueness of the limits are more clear now...

>> I hope this helps to alleviate your headaches slightly.
> Not really, but I'm going to try to digest it.
>
> Thanks for your help.

Always a great pleasure, and sorry everything is not immediately clear. Feel free to post more on any specific limit statement or
any other SQLite thing that seems vague, we'll do our best to make it clear.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
-
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records ina listview.

-
Hello Ryan,

Thanks for your response.   I was writing a lengthy reply when I realized
that most of what I said in it where repetitions of what I have already said
earlier, so I deleted it.

To be honest, its well possible that I currently just can't wrap my head
about the non-strict way SQLite seems to work (its not really what I'm
accustomed to)  ...

Regards,
Rudy Wieser


----- Original Message -----
From: RSmith <[hidden email]>
To: General Discussion of SQLite Database <[hidden email]>
Sent: Friday, July 11, 2014 2:24 AM
Subject: Re: [sqlite] Questions from a novice - basic browsing of records
ina listview.


>
> >
> >> What if the table has 10^16 or more items?
> > Is that number within the limits as set by SQLite3 ?  Than its my
intention
> > to handle it.
>
> No, I just made that up out of thin air. SQLite's maximum row limit is
2^63-1 I believe. It is unreachable on current physical media.
>
> > Oh, there are some there, like a SQLITE_MAX_SQL_LENGTH of (max) a gig,
and a SQLITE_MAX_COLUMN of (max) 32767. Together that would
> > mean a maximum column-name length of about 32768 chars//....
>
> No, it does not mean that at all. Your inference is not only wrong but
also unneeded, meaning that you are imagining relationships
> where there are none. The limits for max sql length and max column are
very clear, and in no way and by no virtue does it imply that
> the one informs the other. Why do you imagine that this is necessarily so?
>
> To be clear - when you go inside an elevator - you might see a weight
limit stated as "500Kg / 13 Persons". This does not mean the
> limit per person is 500/13=38Kg at all, and there is no reason in the
known universe to imagine that it does. (Good thing too cause
> I won't ever get to use the elevator). It does mean that even if you find
an array of 20Kg children, you still cannot pack more than
> 13 in there, and if you have 5 really big (100Kg+) people hopping on ahead
of you, best to wait for the next one. The limit
> statement is not fuzzy.
>
> > ...//Also, the above SQLITE_MAX_COLUMN can be changed at *compile* time.
What am I supposed to do with my regular API (max 2000
> > columns) when encountering a database made with such an altered API ?
Will it even cause troubles at all ? *That* is what I mean
> > with "vague or fuzzy".
>
> If this was true it would be reason for concern - but the limit you refer
to is a SQL language construct limit, not a table-width
> limit so the worry is not justified - but In a query one might be able to
dream up a set of extracted columns that tops the query
> limit easily even if the underlying tables only sports a few columns. For
this you will need to plan. The paradigm here is to cater
> for what is relevant. Very few people make queries longer than a few
columns, but they might. It's a kind of bell curve, and if the
> users are those lying at the 6th+ standard deviation of column-count
requirements, chances are they will have compiled their own
> sqlite version by now, and if they did not, ask them politely to adhere to
whichever limit you picked. There is no SQLite-imposed
> hard limit (other than the physical), in stead, it supports the wide gamut
of needs that cover the 99% bulk, and people with special
> needs roll their own (using the various compiler directives and the like).
>
> You don't even need to check this, SQLite will do it for you. Send a query
with 101 columns, it will return a
> DUDE_THATS_TOO_MANY_COLUMNS result in stead, the (real) codes being
available in the documentation. But if you like there is nothing
> wrong with setting your own maximum columns and doing your own checking,
but the ability doesn't mean SQLite is soft in the head -
> just that it isn't restrictive and you have some freedom of design.
>
> >> most all of them can be adjusted to suit folks like yourself
> >> who wish to push the limits.
> > I'm afraid you misunderstood. Its not my intention to push those limits,
but
> > I'll be damned if I let my intended basic database-browser refuse to
display
> > a legal table because *I* could not find a way to handle it.
>
> Not misunderstood, just a bit tongue-in-cheek, but the nuance probably
misplaced, I apologise.
>
> > Maybe in the end I still have to conceede defeat and use a few arbitrary
> > limits, taking the risk the program cannot handle every table. But not
> > before I tried my d*mn best not to do let that happen. :-)
>
> There is no such risk. SQLite will open each and every valid table in
existence, and you can query it so long as the query itself
> conforms. In this regard you are quite justified to fear a table with 200
columns and you have a 99 col query limit, so you wont be
> able to ask for every column by name, though * will still work.  One might
say that 99 columns is more than any user might want to
> or be able to really look at... but if you disagree (and even I disagree,
I think probably 200 is closer to a sensible human limit),
> then you might simply decide what it is that you feel would be the most
anyone can useful observe in a query, say 500 if you like,
> or 1000, and make that your limit. And then, if ever a query needs more
than those, split it into 2 queries. SQL DBA's are not as
> dumb as you might think, they are very used to working within limits. (Or
maybe your intended user is of a different mindset, but
> that is up to you to figure out).
>
>
> > Well, I wanted to start with browsing. Adding selections to limit the
output (possibly also hiding interresting entries!) could
> > come later. The idea behind that is that while browsing you might find
stuff that looks interresting, something that could easily
> > get hidden when limiting the output (using a LIKE clause)
>
> Ok, I'm not sure I agree with this one, but definitely willing to give the
benefit of the doubt. The solution is again something
> like: You need to decide what would be a sensible limit and go with that.
SQLite will supply any and all records you ask for, it has
> no thoughts on limits in this regard (other than the 64-bit rowid upper
bound I mentioned earlier, but as I also mentioned, no HDD
> in existence has near that many bits on it, never-mind possible data
slots, so reaching that limit simply won't happen and catering
> for it requires a special breed of star-trek fan).
>
> >> It's ~2 giga-characters for a Unicode-enabled string.
> >> Anyone who makes column names that long has issues
> >> that can only be solved by a medical doctor, not an
> >> SQL engine.
> > Agreed.  But it *is* possible, so a generic browser should be able to
handle
> > it (why did you think I was asking if there was a short-hand for
colum-names
> > is available).
>
> Again, apologies for the nuance - I wrongly assumed the statement was
implicit. Yes an absurdly long column name is possible (if not
> plausible), but how is this a problem? SQLite will handle it, it will
report it and will correctly retrieve data for it. As long as
> you can hold it in memory in your app and display it to your user. Also,
again, feel free to limit the viewable size of a column
> name for making the app easier to use (but do not limit the length SQLite
can report to your app, obviously), either way SQLite
> doesn't care, that is your decision. Internally by the way, SQLite stores
it as a hash (other than in the schema), so it really
> doesn't care. As long as your maximum query length fits inside the length
specified (or which you specified), all is well.
>
>
> > Is anyone bound to stay within those "actual usage conventions" ? If not
than its meaningless to me, sorry.
>
> I think you missed the point on this one, I did not say the convention is
a rule or needs to be...  I simply meant as explained
> earlier, another way might be to say: SQLite doesn't prescribe limited
limits (if you'll excuse the tautology) because it caters for
> programmers, but you don't, you cater for users, you should ponder the
conventions and design limits that you imagine suitable.
>
> i.e. It is me trying to make a helpful statement (and possibly botching
it) about how you might think of the implementation and not
> to be confused with reflecting on sqlite's limits or paradigms, those are
what they are and the limit documentation is not fuzzy
> about it.
>
> >> Moral of the story: Pick a limit and build the system according to
that.
> > And when a fully legal table gets rejected because of such arbitrary
limits
> > I would not really be content with myself (to put it lightly).
>
> Rejected by whom? Certainly not SQLite.
> I hope the answers above show that this is not possible, and you can rest
easy now.
>
>
> > Well, almost the first thing I learned (way back when) about databases
is
> > that duplicate data is *bad*.  And now SQL queries look to be promoting
it
> > ...
>
> Data and Query constructs are not the same thing. In one duplication is
bad, in the other it is an arbitrary part of a control signal.
> Do you not say the same words "Good morning" to the same colleagues every
day? Or wait for the same green light to switch on before
> crossing the road?
>
>
> > Which is why I'm attemting to do it "the right way". ... Which than
brought me in collision with vague limits.
>
> I sincerely hope the non-vagueness of the limits are more clear now...
>
> >> I hope this helps to alleviate your headaches slightly.
> > Not really, but I'm going to try to digest it.
> >
> > Thanks for your help.
>
> Always a great pleasure, and sorry everything is not immediately clear.
Feel free to post more on any specific limit statement or
> any other SQLite thing that seems vague, we'll do our best to make it
clear.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Questions from a novice - basic browsing of records ina listview.

R Smith

On 2014/07/12 14:26, - wrote:
> Hello Ryan,
>
> Thanks for your response.   I was writing a lengthy reply when I realized
> that most of what I said in it where repetitions of what I have already said
> earlier, so I deleted it.
>
> To be honest, its well possible that I currently just can't wrap my head
> about the non-strict way SQLite seems to work (its not really what I'm
> accustomed to)  ...

Hi Rudy,

The response is a pleasure and as to the wrapping your head around it, we'll gladly assist. I do not mean to re-iterate things
either, but you add inaccurate statements, so please allow me to be (once more) very clear - SQLite is in no way "non-strict". Maybe
you meant non-constrictive or constrained? The limits are wide and mean to accommodate as many use-cases as possible, but they are
very strict, you cannot slip-through one extra column above the limit, as an example.

I understand the impulse needing compartmentalized absolutes, but it isn't viable. You have no problem understanding the road-laws,
they have limits too, but there are exceptions everywhere. You must drive in a certain lane and your car is not allowed to be wider
than a lane, except for instance when a truck is delivering a cooling tower that's 3-lanes wide, then we use special escorts and
traffic control interventions to move the load. There is however no point in making those exceptions part of standard
road-rule-studies when teaching a teenager to drive - good thing too, because the list of possible and even plausible exceptions
would dwarf the library of congress.

SQL is the road-system and you are the town-planner. It is your job to figure out the use-case and model the limits to suit it. It
is SQLite's task to attempt supplying SQL data interfaces for whatever that mold transpires to be.

If you are used to anything less, then please consider this new horizon a step in the right direction. :)


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users