2016-04-15 16:43 GMT+02:00 R Smith <rsmith at rsweb.co.za>:
>
>
> On 2016/04/15 2:09 PM, Cecil Westerhof wrote:
>
>> If you go to:
>> https://www.sqlite.org/lang_createtable.html
>>
>> You will find:
>> According to the SQL standard, PRIMARY KEY should always
>> imply NOT NULL. Unfortunately, due to a bug in some early
>> versions, this is not the case in SQLite. Unless the column
>> is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID
>> table or the column is declared NOT NULL, SQLite allows NULL
>> values in a PRIMARY KEY column. SQLite could be fixed to
>> conform to the standard, but doing so might break legacy
>> applications. Hence, it has been decided to merely document
>> the fact that SQLite allowing NULLs in most PRIMARY KEY
>> columns.
>>
>
> I'm sorry, I think we are talking past each other - the above is exactly
> my point made. The documentation clearly explains how it works in SQLite,
> even if different to the other DBs (and yes, sadly the standard[1] too in
> this case) there is no reason to "expect" behaviour different to what is
> documented. My point was that your "expectation" was based on your beliefs
> and not based on what you've read in SQLite documentation, which can be the
> only real incitement of expectation from any "SQLite" system. There does
> not exist a single DB engine which implements the SQLite99 (or other)
> standard to the letter - PostGres and SQLite probably being some of the
> closest adherents.
>
?Do you want me to tell you that you always read all the documentation of
all the software before you use it? I surely do not. And I think most
people do not. To be honest I think you do also not: it is nearly
impossible.
When reading what the documentation says, I interpret it as: we really
would like to comply to the standard. But we cannot.
I (think I) gave a way to give the maintainers what they want. If there is
a reason that what I suggest will not work, I do not mind to hear it. But
just saying (in this case) you should have read the documentation, I find
not helpful and to be honest a little cheap. Especially because the fix is
mostly for new users. I know what to do now. I just want to make sure that
SQLite gives the least surprise.
https://en.wikipedia.org/wiki/Principle_of_least_astonishment
> And I am not the only one who has fallen for this problem. I found out
>
>> because I noticed people complaining about it. Then I checked and found
>> out
>> that it did not work as expected.
>>
>> If a car company creates a car where the brakes does not work when you
>> make
>> a left turn, but they put this in the documentation of the car. Do you
>> think it is the customers fault if the find out this when they want to
>> brake while making a left turn?
>>
>
> This example is a stick-man argument, brakes are legally required to be in
> the position they are, but your argument could work if moved sideways
> somewhat to, for instance, removing the headlight switch and making it
> automated. This would certainly be unexpected and probably not fall inside
> any standard, however, if it is documented correctly and fully (perhaps
> marked in red) and it is working as documented, nobody would have a real
> cause for dismay. This kind of thing does happen - and yes, I have seen
> many complaints as you noted, but 10 or even 20 complaints by people who
> are all not reading the documentation is still hard to hold up as
> validation for breaking backward compatibility to millions of systems. (And
> yes, this *is* what you are asking for still).
?I do not think it is. When you add something to the database to signify
that a primary key is not allowed to be NULL, then this is not in an old
database, ergo in the old database NULLs are allowed. Where does backward
compatibility get broken?
As I see it, it is as with partial indexes. That is a big change (I think),
but it did not break backward compatibility.
?I know that I am new here, but I do not think I should be mocked. You make
>> my suggestion sound needlessly complicated. SQLite could be made in such a
>> way that when it creates a new database, this is with the pragma. Then
>> everything works perfect. The old database does not brake, because it does
>> not have the pragma. And when creating a new database it confirms to the
>> SQL standard and those not create nasty surprises for the unaware.
>>
>
> Firstly, I am terribly sorry if my response came across as mocking, this
> was never my intent and probably only sounds that way cause you are new and
> have not seen many of my posts. Please allow me to assure you that I would
> never mock you and in no way thought that your suggestion was mock-worthy.
?Thank you for clarifying that up.
?
> I fully understand your need (as I've said, we've seen it many times
> before), and I am also not saying that your assertion and request is
> unfounded. What I /AM/ saying is that, even though it does bite -
> A. The documentation clearly shows the truth, anyone can read it there.
>
?I do not find this a valid argument in this case. Nobody reads all the
documentation. Especially when you ?know? what something is supposed to do.
At least make sure it is almost impossible to not know this. Especially if
you find out that a lot of people trip over it.
For me it is not a big problem anymore: I know the discrepancy. I am
thinking about all the people who are going to use SQLite and will be
bitten.
?
> B. Your suggestion would break backward compatibility, no matter how
> "light" you coat it.
>
?I really do not see this. Could you expand on that?
C. The suggested work-around would introduce more complication than it is
> solving.
>
?I do not see that either. Could you enlighten me?
?
> If I thought your suggestions or requests were silly, I would not bother
> to answer (luckily we have not many of those on this forum). Also, welcome
> to SQLite, it's one of the best things that exist in this World, and please
> do post all your questions, even if they do sound silly - chances are
> others watching the list might learn from the replies - I know I do. :)
?I like SQLite very much also. I even gave a presentation about it on
T-DOSE. As you can see from the plethora of questions I ask(ed) I want to
get serious with it. I do not use MySQL anymore and plan to migrate what I
still have in H2 to SQLite also. :-)
I do not say there is never a reason for another database, but I think that
in my case there is not (at this moment of time).
--
Cecil Westerhof
