Hacker News Reader

Hacker News

Comment by tehlike | Hacker News Reader

Full thread

tehlike

It really should be default, but it isn't due to backward compatibility (i assume).

View on HN

It’s a stated [0] goal of the project:

> SQLite strives to be flexible regarding the datatype of the content that it stores.

[0]: https://sqlite.org/stricttables.html

You can be flexible with strict tables, type every column as ANY and you pretty much get back the original behaviour.

Sure, but you lose the representation of the developer’s intention that way. I would be pretty pissed off if I inherited a project and the schema was all ANYs.

The intent of ANY is obviously that the values be flexible. That’s why it’s there if you need it.

“I intended this to be an integer but it could really be anything” is not very useful.

Sure it is. If you encounter something that’s not an int, that could be a signal you have a bug in your writers. Or in the source of the data. That’s useful information compared to “oh, I have some ints and some strings, that’s ANY, everything is ok.”

> If you encounter something that’s not an int, that could be a signal you have a bug in your writers

Which, is something you could have caught before it got written at all if you had your db enforcing your types.

i have no idea why this would be preferable to failing immediately on write

The developer's intention is that anything can go in there.

You would only inherit a project where everything was ANY if anything could go anywhere.

With SQLite's default behavior, anything can always go anywhere, so the type definitions are at best semi-accidentally observed by the code, and at worst completely misleading. You have no idea which of the two the developer intended.

I get the impression that this SQLite behavior is a historical oddity caused by the original use case for the tool, rather than something that was intentionally planned and thought through, and was later retconned to be intentional and benign. To me, it makes no sense, even after reading the explanation on sqlite's website.

IIRC SQLite originated as a Tcl extension. In Tcl at the user level "everything is a string" or a number. So it's logical SQLite would accept values as a string or number. Interestingly a Tcl function defines its own semantics, an input value means whatever the function says it means, perhaps a timestamp. SQLite inherited these attributes, and as many commenters observe, SQLite largely continues to work that way.

Implies documentation is crucial. Fortunately SQLite's documentation is among the best out there.

But why aren't you pissed at a database where the columns have types that aren't enforced?

But that is still better than getting a table with typed columns that have been used like ANY columns.

Postgres is flexible, too. Sometimes design choices are just bad, and it's ok.

I’m kind of curious why the decision to have implicit casting like this was made in the first place. I can’t think of a single upside other than not having to type out cast(foo as bar)

IIRC, the project started out as TCL code and it carried that vibe through to what it is today.

SQLite docs: The Advantages Of Flexible Typing: https://sqlite.org/flextypegood.html

SQLite was originally started as a local database library for use during development for times when the main networked database was not available. It used dbm as the underlying storage mechanism, with the dbm API roughly being string keys with string values. ie all underlying values were actually stored as strings. The SQLite code would automatically do conversions - eg the plus operator would convert the strings to int or float, add them, and generate a stringified number as a result. The vast majority of implementation code did not have to care about types, and very local decisions could be made such as in the addition example.

TCL was used as a dev wrapper language at the time, and it functioned the same way.

It was only in mid-2004 that SQLite 3 was released which used its own storage backend, and that allowed for the 5 supported storage types (int64, string, bytes, float, null). It was API compatible (with minor adjustments) with the earlier SQLite 2, so the lack of static typing continued, otherwise everyone would have to rewrite their code. You do get dynamic typing, which hasn't been a problem for the vast majority of SQLite users.

Do remember that SQLite is competition for fopen, not Oracle / Postgres etc. It is trying to make things as effective as possible in that scenario. If you don't want numbers in your string column, then don't do that!

If I remember correctly mysql also started with a berkeleydb(dbm) storage layer. before myisam then later innodb.

I used to sort of dismiss berkeleydb(why so simple?), but a disk backed b-tree indexed key value store is not trivial to get right and having a prebuilt library to do it provides a huge value.

OpenLDAP also originally used BerkeleyDB

It's even worse than implicit casting, if the value can't be cast to the the column's type, it's just inserted without casting. Eg. into an integer column, '10' -> 10 and '1O' -> '1O'

That is documented behaviour - think of it as making a best effort, and not losing the value.

As of January 2006 you could add CHECK constraints using the TYPEOF function to reject that at the SQL level. And it is your own code - there is no server - doing the insertions. As was common back then, protecting you from your own bugs was not a high priority for APIs!

Reply on news.ycombinator.com