5 ms·

I 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 ago
Hipp 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 ago
  This 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 ago
SQLite'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 ago
  I 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 ago
    Seems 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 ago
    This 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

v64 7y ago

Hipp 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 ago
This 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 ago

SQLite'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 ago
I 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 ago
  Seems 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 ago
  This 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