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

[RSmith]

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 

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

[Clemens Ladisch]

- 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[-]

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 

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

[-]

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 <clem...@ladisch.de>
To: <sqlite-users@sqlite.org>
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
> 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] Questions from a novice - basic browsing of records in a listview.

[Teg]

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, -  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 

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

[Simon Slavin]

On 10 Jul 2014, at 12:54pm, -  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[-]

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 <slav...@bigfraud.org>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
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, - <mz2n6u7c.temp...@xs4all.nl> 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
follo

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

[Clemens Ladisch]

- 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 9 Jul 2014, at 2:03pm, -  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
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

[sqlite] 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users