5 ms·
While everyone's praising SQLite, I want to note that SQLite type system is absolutely horrible. When you can store string in the integer column, you don't have
by vbezhenar 7y ago
While everyone's praising SQLite, I want to note that SQLite type system is absolutely horrible. When you can store string in the integer column, you don't have a type system. And it's absolutely not intuitive for people having experience with other databases.
- zachwill 7y agoI actually prefer SQLite's take on type affinity: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- senderista 7y agoI agree, if you want dynamically typed columns then just define a separate Any or Variant type. I don’t see the point of schema if it’s not enforced.
- v64 7y agoHipp talks about that in the interview a little bit: > SQLite really started life as a Tcl extension, Tcl being the programming language, the Tcl/Tk. The project I was working was working on was written in Tcl/Tk and so SQLite began as a Tcl extension and as a scripting language, like Perl or Python, where you can put any type of value you want in a variable. So a variable might hold a string, a number, a byte array or whatever. So I made SQLite the same way, where just because you’ve declared a column of a table to be text doesn’t mean you can’t put integer in there, or just because you declared a column in the table to be a short int, why not put a 2-megabyte blob there? So what? It’ll do that. > Or if you have a column that’s declared integer and you try to put text in it, it looks like an integer and it can be converted without loss. It will convert and store it as an integer. But if you try and put a blob into a short int or something, there’s no way to convert that, so it just stores the original and it gives flexibility there. And this is useful in a lot of cases, because sometimes you just have a miscellaneous column in a table that you might need to store lots of different things in. And in traditional database systems you actually have to have multiple columns, one for each possible data type, whereas in SQLite you put it all in one column. So it works well. > And for that matter, with SQLite you don’t have to give the column a type at all. You can just say, CreateTable T1 (a,b,c) and then you’ve got a table with three columns named a, b and c and you put whatever you want there. > Well, it flows directly out of the scripting language traditions. You don’t declare types for variables in Tcl; you didn’t used to do it in Python, I guess you can do it some, now. You don’t do it in JavaScript… You just say it’s a var.
- nine_k 7y agoThis apparently has roots in some ancient practices (Tcl is not exactly young). In 21th century, we likely don't need to save a few bytes and reuse a variable, and can afford to preserve our sanity instead. Well, we can even afford a built-in ACID-compliant SQL database!..
- scottlamb 7y agoSQLite's quirks page [1] actually suggests the same thing: > In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature. I wish they had done this in the first place. I wish even now that they implemented a pragma to enforce types (and introduce the ANY type for folks who explicitly want it). But SQLite has enough good things going for it that I'm happy to use it in spite of this problem. [1] https://www.sqlite.org/quirks.html https://www.sqlite.org/quirks.html
- nine_k 7y agoI don't see why this must be a breaking change. (1) Add a flag when opening a database to show that you want data types enforced. (2) Add code to all the input pathways to check for type compliance. This way a database with correct data types will stay correct. (3) Do not change anything in the storage layer. What am I missing?
- murftown 7y agoSeems like (1) is doable, as long as it's opt-in. But if it becomes the default behavior, existing applications would break when they do something against the type system. I'm not sure what (2) buys you.
- nine_k 7y ago(1) is the interface to trigger (2).
- scottlamb 7y agoThis is very similar to what they did for foreign key support. https://www.sqlite.org/pragma.html#pragma_foreign_keys https://www.sqlite.org/pragma.html#pragma_foreign_keys
- mark-r 7y agoIt's absolutely intuitive for people used to programming languages that aren't strongly typed, like Python.
- rankam 7y agoJust FYI Python is strongly typed https://stackoverflow.com/questions/11328920/is-python-strongly-typed https://stackoverflow.com/questions/11328920/is-python-stron...
- mark-r 7y agoForgive me, I was confusing strongly typed with statically typed. My point still stands though, for someone used to a dynamically typed language like Python having database columns that aren't locked to a single rigid type will seem quite natural.
- j-pb 7y agoIt doesn't though. There are almost no dynamically typed languages where you supply a type yet don't enforce it, maybe forth is the closest with its stack effect comments. If you give me a type, I do expect a type, if you omit the type fine, I'm gonna assume it'll be dynamic at runtime. All these dynamic languages are also strongly typed for a reason, even javascript uses === everywhere instead of the weakly typed ==. The bugs that occur because of automagic are just ridiculously hard to track down, and even statically typed languages can fall into this pit, for example Scala with its implicits shudder. But SQLite does the worst of all worlds, you have to specify a type, yet you cannot rely on it, it sometimes does type conversion for you e.g. int to string and back, and sometimes does not. They should've just implemented a single ANY type or have the syntax not use types at all and deviate from the SQL spec (which they do a lot anyways).
- massysett 7y agoAll you have to do is put a column constraint on there. CREATE TABLE t ( intcol CHECK (typeof(intcol) = ‘integer’));
- kccqzy 7y agoCan't you just add CHECK constraints then?
- hinkley 7y agoI finished the podcast a couple hours ago. It was pretty clear to me that Hipp has the perspective of a person working on small teams, where most of the changes to the system either don't affect you or are highly visible. This is not a man who fears the existential dread of discovering, just after you've stated loudly that some part of the system could not possibly break that way, that some other developer has pulled the rug out from under you when you weren't looking. The odds that I add the string "52" to a column I know damn well has integers is pretty low, but non-zero. And I'm gonna transpose some fields eventually. The odds of any particular coworker doing the same is some multiple of that. But q^n across my entire team, across months and years, across new hires and people switching into and out of the team? We are so deep into Murphy's Law territory that it's almost a certainty. It's a fool's errand to try to prevent absolutely every one of those scenarios happening. All I ask is that we have a way to discover them early and cheaply.