4 ms·

> Early versions of SQLite (prior to 2004) operated on the classic TCL principal that "everything is a string". Beginning with SQLite3 (2004-06-18), SQLite also

> Early versions of SQLite (prior to 2004) operated on the classic TCL principal that "everything is a string". Beginning with SQLite3 (2004-06-18), SQLite also supports binary data. TCL can handle binary data. It is just not a separate type: https://wiki.tcl-lang.org/page/Working+with+binary+data https://wiki.tcl-lang.org/page/Working+with+binary+data SQLite also always had a null type, surely? > However, types are still very flexible in SQLite, just as they are in TCL. SQLite treats the datatypes on column names in a CREATE TABLE statement as suggestions rather than hard requirements This is something I do not much like. Its not compulsory (you can create "strict" tables). It works well in TCL which is an entire language designed around the idea. Less so in SQL. One of the advantages of RDBMSes is that not accepting obviously wrong data makes life easier for developers. You can debug an issue that happens on inserting the data, not when you find the wrong type or other bad much later on.

tyingq 2y ago
There was a time when TCL could not handle binary data at all. The support came in TCL 8.0: "Binary data is now supported in Tcl." https://www.tcl.tk/software/tcltk/relnotes/tcl8.0.txt https://www.tcl.tk/software/tcltk/relnotes/tcl8.0.txt Well before 2004, but worth mentioning because you'll find a fair amount of old posts complaining about it.
- gcr 2y ago
  What do you mean, that TCL strings weren’t 8-bit-clean?
  - pavlov 2y ago
    Maybe they were zero-terminated C strings, which of course can’t represent arbitrary binary data.
    - tyingq 2y ago
      Yes, prior to 8.0, tcl strings mapped to null-terminated C strings. "Binary I/O. The new object system in Tcl 8.0 supports binary strings (internally, strings are counted in addition to being null terminated). There is a new "binary" command for inserting and extracting data to/from binary strings. Commands such as "puts", "gets", and "read" commands now operate correctly on binary data. There is a new variable tcl_platform(byteOrder) to identify the native byte order for the current host."
rscho 2y ago
This typing behaviour, now in combination with strict tables, is a boon for biostats. When you get shitty data to be cleaned, you've got 3 main choices: 1.use slow and untyped scripting languages, 2.use a strictly typed database, meaning you'll have to clean your data in advance, or 3.load it all as strings into SQLlite, then clean the data until it fits into a strict table with check constraints. IMO, it's pretty clear 3 is best by far!
- coliveira 2y ago
  I agree that 3 is great, but you can also do that in any database, just create your input tables as string only and then perform the necessary operations to move them into typed tables.
  - rscho 2y ago
    Yes, but with sqlite there is much less ceremony (no server, etc.) and most importantly can be used without talking to my institution's sysadmin, which is what I'm looking for when manipulating one-off datasets.
    - mb7733 2y ago
      But that advantage has nothing to do with accepting data of the wrong type into a column (by default).
- kstrauser 2y ago
  Is anyone using untyped languages much today, other than shell scripts?
  - colejohnson66 2y ago
    CMake is entirely stringly-typed as well. Like many shells, arrays/lists are just space-separated strings.
    - 2y ago
PittleyDunkin 2y ago
> SQLite also always had a null type, surely? NULL is surely a value, not a type, no? You can restrict use of NULL with the column type, but it doesn't make any sense to have a NULL type rather than, say, a nullable-string type. What would be the point?
- IshKebab 2y ago
  Null/none is a value and a type. E.g. in Python there's None which has the type NoneType. Sometimes they are named the same. This is an example of a singleton type - a type that can only hold one value. It's not the only example though, it's fairly common to support string or integer singleton types, e.g. in Typescript you can have the type "foo" which only has a single possible value, "foo". Or some languages support a type int(5) for which the only possible value is 5. You might think that's useless, but it's very useful when combining types, e.g. via unions. Or sometimes when writing generic code. SQL has an extremely primitive type system so there's no syntax for type unions etc. But you can imagine if it was written by someone who had experience of modern typing you would say `my_nullable_column: string | null`.
  - PittleyDunkin 2y ago
    Ok, but you could also just say "nullable string". Giving null a type doesn't seem to yield a benefit outside of python. Especially when, again, it doesn't appear to have any utility in SQL outside of applying to types.
samatman 2y ago
> SQLite also always had a null type, surely? In 'stringly typed' languages, the natural value of NULL is the empty string. SQLite currently distinguishes between "" and NULL, and I don't know if this was always the case. But it's quite possible that SQLite 1 and 2 conflated the empty string and NULL.

tyingq 2y ago

There was a time when TCL could not handle binary data at all. The support came in TCL 8.0: "Binary data is now supported in Tcl." https://www.tcl.tk/software/tcltk/relnotes/tcl8.0.txt https://www.tcl.tk/software/tcltk/relnotes/tcl8.0.txt Well before 2004, but worth mentioning because you'll find a fair amount of old posts complaining about it.

gcr 2y ago
What do you mean, that TCL strings weren’t 8-bit-clean?
- pavlov 2y ago
  Maybe they were zero-terminated C strings, which of course can’t represent arbitrary binary data.
  - tyingq 2y ago
    Yes, prior to 8.0, tcl strings mapped to null-terminated C strings. "Binary I/O. The new object system in Tcl 8.0 supports binary strings (internally, strings are counted in addition to being null terminated). There is a new "binary" command for inserting and extracting data to/from binary strings. Commands such as "puts", "gets", and "read" commands now operate correctly on binary data. There is a new variable tcl_platform(byteOrder) to identify the native byte order for the current host."

rscho 2y ago

This typing behaviour, now in combination with strict tables, is a boon for biostats. When you get shitty data to be cleaned, you've got 3 main choices: 1.use slow and untyped scripting languages, 2.use a strictly typed database, meaning you'll have to clean your data in advance, or 3.load it all as strings into SQLlite, then clean the data until it fits into a strict table with check constraints. IMO, it's pretty clear 3 is best by far!

coliveira 2y ago
I agree that 3 is great, but you can also do that in any database, just create your input tables as string only and then perform the necessary operations to move them into typed tables.
- rscho 2y ago
  Yes, but with sqlite there is much less ceremony (no server, etc.) and most importantly can be used without talking to my institution's sysadmin, which is what I'm looking for when manipulating one-off datasets.
  - mb7733 2y ago
    But that advantage has nothing to do with accepting data of the wrong type into a column (by default).
kstrauser 2y ago
Is anyone using untyped languages much today, other than shell scripts?
- colejohnson66 2y ago
  CMake is entirely stringly-typed as well. Like many shells, arrays/lists are just space-separated strings.
  - 2y ago

PittleyDunkin 2y ago

> SQLite also always had a null type, surely? NULL is surely a value, not a type, no? You can restrict use of NULL with the column type, but it doesn't make any sense to have a NULL type rather than, say, a nullable-string type. What would be the point?

IshKebab 2y ago
Null/none is a value and a type. E.g. in Python there's None which has the type NoneType. Sometimes they are named the same. This is an example of a singleton type - a type that can only hold one value. It's not the only example though, it's fairly common to support string or integer singleton types, e.g. in Typescript you can have the type "foo" which only has a single possible value, "foo". Or some languages support a type int(5) for which the only possible value is 5. You might think that's useless, but it's very useful when combining types, e.g. via unions. Or sometimes when writing generic code. SQL has an extremely primitive type system so there's no syntax for type unions etc. But you can imagine if it was written by someone who had experience of modern typing you would say `my_nullable_column: string | null`.
- PittleyDunkin 2y ago
  Ok, but you could also just say "nullable string". Giving null a type doesn't seem to yield a benefit outside of python. Especially when, again, it doesn't appear to have any utility in SQL outside of applying to types.

samatman 2y ago

> SQLite also always had a null type, surely? In 'stringly typed' languages, the natural value of NULL is the empty string. SQLite currently distinguishes between "" and NULL, and I don't know if this was always the case. But it's quite possible that SQLite 1 and 2 conflated the empty string and NULL.