Re: [sqlite] Questions from a novice - basic browsing of records ina listview.
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 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 <rsm...@rsweb.co.za> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 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, thou
Re: [sqlite] Questions from a novice - basic browsing of records ina listview.
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Questions from a novice - basic browsing of records ina 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
Re: [sqlite] Questions from a novice - basic browsing of records ina listview.
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 <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 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, - <mz2n6u7c.temp...@xs4all.nl> 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. Alt
Re: [sqlite] Questions from a novice - basic browsing of records ina listview.
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 <phi...@blastbay.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> 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 > > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
