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