On 15 Apr 2016, at 10:05pm, Cecil Westerhof <cldwesterhof at gmail.com> wrote:
> I was ?bitten? by the fact that in SQLite primary keys can contain NULL > values. As I understood it, I was certainly not the first person to who > this happened. It would not surprise me if there are more deviations that > could spell problems for users of SQLite. That is why I think it would be a > good idea to add a caveat entry It might help you to know that I can't remember the last time anyone raised problems associated with having NULLs in primary key columns. Most users are aware that all SQL engines have a problem deciding whether two "NULL" values should be considered equal or not and this feeds directly into what happens when you try to make primary indexes with nulls. I don't think adding anything to the 'about' page is going to help those having problems. It wouldn't occur to me to look there if I found something unexpected with SQLite. However you do raise an interesting possibility of creating a 'caveat' or 'gotcha' page, like the "How to corrupt" page. The problems most commonly mentioned here seem to be (in no particular order) * SQLite datatypes and how SQLite decides which datatype you want * SQLite uses affinities not column types * thinking you should index each column instead of indexes for queries * consequences of various PRAGMAs which might appear to speed SQLite up * consequences of the two different journal types (including file size) Each of the above can be summarised and explained clearly with explanations which won't need to be changed over time. If you want to add questions which are harder to answer and have answers which change over time then perhaps these should be considered: * compilation errors/warnings in various tools/environments * when you need the component source files not just the amalgamation * problems using Windows DLL in some environments/versions * problems not in SQLite but introduced by various SQLite shells/libraries when trying to bridge the gaps between SQLite and the way their language or data structure works (e.g. pretending that SQLite has a date/time type) I should add that I ignore posts about the FTS capability of SQLite. I don't know whether people post the same problems with it repeatedly or each post is about something different. Perhaps someone will contribute an opinion. The most common mistake which leads to posts to this list is -- still the winnah after five years ! -- premature optimization. I can't believe how much coding time and software complication is wasted by people who spend hours on multi-thread, queueing and partitioning code instead of redesigning their schema or thinking through how indexes work. Simon.
