9 ms·

STRICT tables forbid the use of any other types besides INT/INTEGER, REAL, TEXT, BLOB (and ANY). What about TIMESTAMP? DATETIME? You can't use that as a type n

by SPBS 5y ago

STRICT tables forbid the use of any other types besides INT/INTEGER, REAL, TEXT, BLOB (and ANY). What about TIMESTAMP? DATETIME? You can't use that as a type name anymore. Either pick INT, REAL or TEXT for that. What about JSON? You can't use it as a type name anymore. Pick TEXT. Basically type names, even if SQLite didn't have explicit support for that type, were helpful documentation hints in what the column contained. Now everything devolves to either INT or TEXT. I have UUID fields, I can't even call them UUID or BINARY(16), just BLOB.

melony 5y ago
And more importantly, JSON.
- derefr 5y ago
  Note that you don't have to have a JSON-typed column in SQLite in order to do JSON-y things. In SQLite, JSON is just "text that happens to contain JSON", and JSON functions are just functions that operate on "text that happens to contain JSON".
derefr 5y ago
Presumably the point of the strict typing forbidding aliases here, is to force the developer to realize/acknowledge that SQLite does not in fact have separate internal native types with these names. It's like the modern C convention of avoiding "int" in favor of u8/u16/u32/u64. Rather than pretending "int" is a separate thing, you have to explicitly choose and acknowledge the concrete primitive type you're really getting, and explicitly opt into the semantics that use of that concrete primitive type implies.
- nayuki 5y ago
  Not a great analogy because the semantics of the classic C types still leak through. For example, ((uint16_t)0xFFFF) * ((uint16_t)0xFFFF) would cause undefined behavior if int is 32 bits wide, due to implicit arithmetic promotions. Also, many APIs of the C standard library are defined to take or give char/int/etc., and it takes effort to translate between those types and u8/u32/etc. in a safe way that works on all platforms.
  - derefr 5y ago
    That's not because "int is 32 bits wide", though; that's because you're on a machine whose native register size is more than 16 bits, and so your processor's MUL instruction likely expects to always write the product to a 32/64-bit destination register, even if the source registers are both 16-bit. IMHO C is far too lax with things like this; an ideal systems language would force you to annotate maths operations with the "working precision" for each operation, making explicit any promotion done to the inputs before the math happens. And then, if your "working precision" exceeded the destination variable's size, you'd also be forced to do an explicit demoting cast, just as if you were assigning from a declared larger-sized variable. Yes, it'd be mostly irrelevant for everyday int math stuff; but it'd make things so much clearer as soon as you started working with floats (where you'd have to explicitly opt into Intel's 80-bit extended "working precision.")
    - the_duke 5y ago
      Rust requires explicit conversions for numeric types for exactly the reasons you mention.
      - derefr 5y ago
        Most systems languages (that aren't C) force you to be explicit about demoting casts between formal variables; that's the easy part. But no language I know of (not even Rust) gets rid of implicit promotion during math operations, in favor of 1. forcing you to specify the size of the destination register you want the instruction to use (i.e. the working precision), and then 2. requiring an explicit demoting cast if the destination formal variable is smaller than the working precision you specified. I mean, C's inline assembler, and LLVM IR, both require this; but they also require you to specify which work register you're talking about, rather than just the size, which breaks the conceit of a "portable systems language."
- ComputerGuru 5y ago
  Yup. Most newcomers to SQLite are bitten hard by the fake datetime support.
StreamBright 5y ago
> What about TIMESTAMP? DATETIME? I usually use TEXT and store ISO 8601. Datetime and timestamp is fine if you need date functions ("- interval '7' day", etc.) but most of the time you can just convert the text to datetime when running the query without much performance penalty in other databases. What is the usecase for TIMESTAMP and DATETIME that cannot be solved today?
- hnarn 5y ago
  If you’re going to convert it anyway, why use a human readable format in the first place? If you store it as unix epoch, not only do you never have to worry about time zones, it’s also a sortable integer instead of text.
  - roelb 5y ago
    Regarding time and timezones, it's best to not prematurely optimise. Ideally store both. It's much more valuable to track the timezone from your source then to have to worry about it later. ISO8601 is the universal standard. A case where you see this issue play out is the horrible Strava-*device sync. You track in a different timezone and they store+visualise activities against some weird client-side profile setting, which causes morning runs to render at 11pm. Only adding timezone later on. They totally mess this up.
    - hnarn 5y ago
      First of all, I don't see how using unix epoch timestamps can be called "premature optimization", it's a pretty widely used and standardized way of saving timestamps. Secondly, if you're in "SQLite Strict Land" and you don't have access to abstractions like "tell the database this is a date", then the best way of storing timestamps is unix epoch, I would be extremely surprised if databases don't already do this behind the scenes when abstracting away things like dates for the user. Thirdly, what is the value of tracking "source timezone"? This is solving a non-existent problem: if you are getting the timestamp in unix epoch from the source, and you're storing it in unix epoch, the "source timezone" is already known: it's UTC just like all unix timestamps are. Timezones is fundamentally a data presentation concern, and I strongly believe they should not be a part of the source data. > You track in a different timezone and they store+visualise activities against some weird client-side profile setting, which causes morning runs to render at 11pm. Only adding timezone later on. They totally mess this up. This is exactly the type of issue that happens because you involve timezones in your source data. If all applications and databases only concern themselves with unix timestamps, and the conversion to a specific timezone only happens in the application layer upon display, this type of issue simply does not happen, because time "1638097466" when I'm writing this is the exact same time everywhere on the globe. (Of course, similar issues can happen due to user error if two applications have different time zone settings and the user mistakenly enter a timestamp in the wrong TZ, but that's definitely not solved by making time zones a part of the data itself)
skissane 5y ago
I think a logical future step would be to add the SQL-99 CREATE DOMAIN statement. That allows you to define a custom type alias, for example: CREATE DOMAIN UUID AS TEXT; Or if you prefer: CREATE DOMAIN UUID AS BINARY; Without CREATE DOMAIN, for STRICT tables they don't know which basic type to use for an unknown type name. They could use something like "unknown types are equivalent to ANY" but that would mean they couldn't implement CREATE DOMAIN in the future without breaking backward compatibility. If they did implement CREATE DOMAIN I expect it would be supported for STRICT tables only. What makes it really powerful is you can also define a CHECK constraint, NOT NULL constraint, and DEFAULTs to be automatically applied to table columns of this custom type. Examples: CREATE DOMAIN UUID AS TEXT CHECK (value REGEXP '[a-f0-9]{8}(-[a-f0-9]{4}){4}[a-f0-9]{8}'); CREATE DOMAIN UUID AS BINARY CHECK (LENGTH(value) = 16); I think Postgres is the only major relational database to implement CREATE DOMAIN, although there are some more obscure RDBMSes which support it too (such as Firebird, Interbase, Sybase/SAP SQL Anywhere). The SQLite developers haven't said they plan to do this, but I imagine they are thinking about it and may do it at some point. I think it is the kind of feature likely to appeal to them, it should be relatively simple to implement (especially if they don't implement ALTER DOMAIN, only CREATE and DROP), but provide a lot of power in exchange.
- DemocracyFTW 5y ago
  came here to say this (approximately)
dotancohen 5y ago
> What about TIMESTAMP? DATETIME? The same thing as we've always done: proper column names and strong datatypes in the application layer.
- SPBS 5y ago
  Yeah, I'm starting to warm to the idea of `CREATE TABLE tbl ( created_at INT ) STRICT;`. The name still hints at what domain type it contains, and enforcing INT timestamps should make any indexes on that column considerably smaller.

by SPBS 5y ago

melony 5y ago

And more importantly, JSON.

derefr 5y ago
Note that you don't have to have a JSON-typed column in SQLite in order to do JSON-y things. In SQLite, JSON is just "text that happens to contain JSON", and JSON functions are just functions that operate on "text that happens to contain JSON".

derefr 5y ago

Presumably the point of the strict typing forbidding aliases here, is to force the developer to realize/acknowledge that SQLite does not in fact have separate internal native types with these names. It's like the modern C convention of avoiding "int" in favor of u8/u16/u32/u64. Rather than pretending "int" is a separate thing, you have to explicitly choose and acknowledge the concrete primitive type you're really getting, and explicitly opt into the semantics that use of that concrete primitive type implies.

nayuki 5y ago
Not a great analogy because the semantics of the classic C types still leak through. For example, ((uint16_t)0xFFFF) * ((uint16_t)0xFFFF) would cause undefined behavior if int is 32 bits wide, due to implicit arithmetic promotions. Also, many APIs of the C standard library are defined to take or give char/int/etc., and it takes effort to translate between those types and u8/u32/etc. in a safe way that works on all platforms.
- derefr 5y ago
  That's not because "int is 32 bits wide", though; that's because you're on a machine whose native register size is more than 16 bits, and so your processor's MUL instruction likely expects to always write the product to a 32/64-bit destination register, even if the source registers are both 16-bit. IMHO C is far too lax with things like this; an ideal systems language would force you to annotate maths operations with the "working precision" for each operation, making explicit any promotion done to the inputs before the math happens. And then, if your "working precision" exceeded the destination variable's size, you'd also be forced to do an explicit demoting cast, just as if you were assigning from a declared larger-sized variable. Yes, it'd be mostly irrelevant for everyday int math stuff; but it'd make things so much clearer as soon as you started working with floats (where you'd have to explicitly opt into Intel's 80-bit extended "working precision.")
  - the_duke 5y ago
    Rust requires explicit conversions for numeric types for exactly the reasons you mention.
    - derefr 5y ago
      Most systems languages (that aren't C) force you to be explicit about demoting casts between formal variables; that's the easy part. But no language I know of (not even Rust) gets rid of implicit promotion during math operations, in favor of 1. forcing you to specify the size of the destination register you want the instruction to use (i.e. the working precision), and then 2. requiring an explicit demoting cast if the destination formal variable is smaller than the working precision you specified. I mean, C's inline assembler, and LLVM IR, both require this; but they also require you to specify which work register you're talking about, rather than just the size, which breaks the conceit of a "portable systems language."
ComputerGuru 5y ago
Yup. Most newcomers to SQLite are bitten hard by the fake datetime support.

StreamBright 5y ago

> What about TIMESTAMP? DATETIME? I usually use TEXT and store ISO 8601. Datetime and timestamp is fine if you need date functions ("- interval '7' day", etc.) but most of the time you can just convert the text to datetime when running the query without much performance penalty in other databases. What is the usecase for TIMESTAMP and DATETIME that cannot be solved today?

hnarn 5y ago
If you’re going to convert it anyway, why use a human readable format in the first place? If you store it as unix epoch, not only do you never have to worry about time zones, it’s also a sortable integer instead of text.
- roelb 5y ago
  Regarding time and timezones, it's best to not prematurely optimise. Ideally store both. It's much more valuable to track the timezone from your source then to have to worry about it later. ISO8601 is the universal standard. A case where you see this issue play out is the horrible Strava-*device sync. You track in a different timezone and they store+visualise activities against some weird client-side profile setting, which causes morning runs to render at 11pm. Only adding timezone later on. They totally mess this up.
  - hnarn 5y ago
    First of all, I don't see how using unix epoch timestamps can be called "premature optimization", it's a pretty widely used and standardized way of saving timestamps. Secondly, if you're in "SQLite Strict Land" and you don't have access to abstractions like "tell the database this is a date", then the best way of storing timestamps is unix epoch, I would be extremely surprised if databases don't already do this behind the scenes when abstracting away things like dates for the user. Thirdly, what is the value of tracking "source timezone"? This is solving a non-existent problem: if you are getting the timestamp in unix epoch from the source, and you're storing it in unix epoch, the "source timezone" is already known: it's UTC just like all unix timestamps are. Timezones is fundamentally a data presentation concern, and I strongly believe they should not be a part of the source data. > You track in a different timezone and they store+visualise activities against some weird client-side profile setting, which causes morning runs to render at 11pm. Only adding timezone later on. They totally mess this up. This is exactly the type of issue that happens because you involve timezones in your source data. If all applications and databases only concern themselves with unix timestamps, and the conversion to a specific timezone only happens in the application layer upon display, this type of issue simply does not happen, because time "1638097466" when I'm writing this is the exact same time everywhere on the globe. (Of course, similar issues can happen due to user error if two applications have different time zone settings and the user mistakenly enter a timestamp in the wrong TZ, but that's definitely not solved by making time zones a part of the data itself)

skissane 5y ago

I think a logical future step would be to add the SQL-99 CREATE DOMAIN statement. That allows you to define a custom type alias, for example: CREATE DOMAIN UUID AS TEXT; Or if you prefer: CREATE DOMAIN UUID AS BINARY; Without CREATE DOMAIN, for STRICT tables they don't know which basic type to use for an unknown type name. They could use something like "unknown types are equivalent to ANY" but that would mean they couldn't implement CREATE DOMAIN in the future without breaking backward compatibility. If they did implement CREATE DOMAIN I expect it would be supported for STRICT tables only. What makes it really powerful is you can also define a CHECK constraint, NOT NULL constraint, and DEFAULTs to be automatically applied to table columns of this custom type. Examples: CREATE DOMAIN UUID AS TEXT CHECK (value REGEXP '[a-f0-9]{8}(-[a-f0-9]{4}){4}[a-f0-9]{8}'); CREATE DOMAIN UUID AS BINARY CHECK (LENGTH(value) = 16); I think Postgres is the only major relational database to implement CREATE DOMAIN, although there are some more obscure RDBMSes which support it too (such as Firebird, Interbase, Sybase/SAP SQL Anywhere). The SQLite developers haven't said they plan to do this, but I imagine they are thinking about it and may do it at some point. I think it is the kind of feature likely to appeal to them, it should be relatively simple to implement (especially if they don't implement ALTER DOMAIN, only CREATE and DROP), but provide a lot of power in exchange.

DemocracyFTW 5y ago
came here to say this (approximately)

dotancohen 5y ago

> What about TIMESTAMP? DATETIME? The same thing as we've always done: proper column names and strong datatypes in the application layer.

SPBS 5y ago
Yeah, I'm starting to warm to the idea of `CREATE TABLE tbl ( created_at INT ) STRICT;`. The name still hints at what domain type it contains, and enforcing INT timestamps should make any indexes on that column considerably smaller.