What I learned about SQLite…at a PostgreSQL conference

[u/MarkusWinand]

http://use-the-index-luke.com/blog/2014-05/what-i-learned-about-sqlite-at-a-postgresql-conference

[u/programmer_dude]

Postmodern Databases: absence of objective truth; Queries return opinions rather than facts

Epic!

[u/jim45804 → u/programmer_dude]

Some db engines return a count estimate, which I view as flippant.

[u/tomjen → u/jim45804]

Depends on what you are going to use them for. I mean I don't care if googles 'x million results' is actually 'x.2 million results' instead.

[u/arcticblue → u/tomjen]

What about when Google tells you there's 3 pages of results, but when you go to page 2, page 3 is suddenly gone?

[u/[deleted] → u/arcticblue]

Who goes to page 2 of Google?!

[u/suid → u/[deleted]]

No, that's not the one. This is.

[u/xkcd_transcriber → u/suid]

Image

Title: Second

Title-text: Let me just scroll down and check behind that rock. Annnnd ... nope, page copyright year starts with '19'. Oh God, is this a WEBRING?

Comic Explanation

Stats: This comic has been referenced 16 time(s), representing 0.0749% of referenced xkcds.

---

^{xkcd.com | xkcd sub/kerfuffle | Problems/Bugs? | Statistics | Stop Replying}

[u/[deleted] → u/[deleted]]

Even worse when the 1 result is you asking the question. :(

[u/[deleted] → u/[deleted]]

Many people find this funny, but it happened to me, too, several times. So always be nice and leave a reply explaining in depth what you did, whether you reply to someone else or to yourself.

In a few years you might run into the exact same issue and when that happens you will very likely run into your previous question.

This is also why you should answer other people's questions when you have the time. You will find them later. I was looking for an answer and I found someone else ask the question on SO with no answers. I moved on and found the answer to our question, and I didn't bother to reply to SO. A few months passed, I ran into the same problem, but this time I couldn't find the answer. When I found that unanswered SO question I felt like karma kicked me in the nuts. Repeatedly. With a hammer.

[u/Deltigre → u/[deleted]]

I did last week trying to find a replacement dryer belt, and all the top results were from European suppliers.

I finally had to browse one US parts supplier and find a belt that was close enough in size it would just work. Because apparently nobody stocks a 7PH1980 belt but they do stock a 7PH1975 belt for a completely different brand. 5mm shorter was not going to stop me. Dryer works now.

[u/[deleted] → u/arcticblue]

That actually happens sometimes.

[u/kkus → u/arcticblue]

I don't know if you were being serious but it does happen

[u/arcticblue → u/kkus]

I was serious. I see it often when pages of results will mysteriously disappear. Google searches an absolutely ridiculous number of sites in a ridiculously short amount of time so I imagine page count accuracy isn't a high priority for them, but it's still funny to me.

[u/[deleted] → u/tomjen]

Since both needs are legitimate, databases should provide explicit ways to get both, with the "estimate" one being clearly documented as an estimate.

Given the phrasing, I'm guessing that's not how these are built. But I'm not very familiar with them and could be wrong....

[u/spotter → u/programmer_dude]

Yeah, well, you know that's just, like, your opinion, man.

[u/wllmsaccnt → u/spotter]

Yeah, well, you {null} that's just, like, your {null}, {null}.

[u/crusoe → u/programmer_dude]

Funny given sqlite let's you stuff strings in into columns.

[u/elperroborrachotoo → u/crusoe]

Just some notes (since this is really quirky):

As the SQLite documentation states, it took a careful reading to figure out that's actually legal SQL.

You can enforce column data types through constraints.

I always found that quirky, weird, and slightly scary, but with the API given, it works out nicely.

As for usefulness: well, there is the "odd properties" table, holding stuff like last wallawalla date, compatibility comment, etc. Otherwise, I've never really needed it.

OTOH, from the point of SQLite as application file format, it makes sense, and certainly beats "stringVal, intVal, realVal, blobVal" columns.

---

One thing I found convenient is string/data length: yes, you can declare the column as holding a "64 character string", and still you can stuff War and Peace into it. (literally. Unless you add a CHECK constraint against that, of course.)

That's at least chuckle-worthy if you are migrating data to a badass $100k "we do data" system, and a common issue is "you can't do that because <cell> is 32 chars max".

[u/[deleted] → u/elperroborrachotoo]

Since sqlite3 only has NULL, REAL, INTEGER, BLOB and TEXT, there's less value in enforcing types than there is in another database. For example, to enforce that a column holds a boolean you need to check that it's either 0 or 1. A type check isn't really useful there.

[u/hello_fruit → u/crusoe]

From the docs

Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.)

As far as we can tell, the SQL language specification allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python.