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
