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