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 <rsm...@rsweb.co.za> To: <sqlite-users@sqlite.org> 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 > 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
