7 ms·

Me! I'm often exploring data where either there's no defined standard or the use of the data has drifted from the standard. Now, I could go over this line-by-

Me! I'm often exploring data where either there's no defined standard or the use of the data has drifted from the standard. Now, I could go over this line-by-line, but instead my go-to has been "Hey, let's throw this into SQLite, then run stats on it!" See what shakes out. SQLite kindly and obediently takes all of this mystery data, which ends up being nothing like what I was told, and just accepting it. Then I can begin prodding it and see what is actually going on. This is something that has come up for me for at least a decade: chuck it in SQLite, then figure out what the real standard is.

throw10920 4y ago
Were SQLite not such a stellar piece of software, I would complain, and ask that this kind of flexibility be disabled by default, with an option to explicitly enable it if necessary.
- tehbeard 4y ago
  While I doubt it will ever be enabled by default due to backwards compatibility, there is a strict tables[1] feature now. [1] https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
  - silvestrov 4y ago
    There should be an option to have strict tables, foreign key checks and other modern features to be automatically enforced. E.g. a command line argument when creating a DB, which then taints this database as modern. Something like what html did with "<!DOCTYPE html>". Or simply accept that the time has come to make a version 4 of sqlite.
    - ratrocket 4y ago
      That might have to be version 5 of sqlite. Version 4 came and went: https://sqlite.org/src4/doc/trunk/www/index.wiki https://sqlite.org/src4/doc/trunk/www/index.wiki > SQLite4 was an experimental rewrite of SQLite that was active from 2012 through 2014.
    - tomsmeding 4y ago
      > There should be an option to have strict tables, foreign key checks and other modern features to be automatically enforced. I know to enable foreign key checking, and I learned strict tables from this thread. What are the "other modern features" you were referring to? EDIT: In TFA, section 8 is relevant here I guess: SQLite accepts double-quoted strings as a historical misfeature, which can be disabled with the appropriate C API functions. This is one of the "other modern features" I guess; TIL.
    - tehbeard 4y ago
      The strict keyword per that doc already taints the a table for pre 3.37.0 (2021-11-27) libraries. (There is a way around that for pre 3.37 per the doc, but it's mainly enabling the PRAGMA used for db recovery operations that treats the strict at the end as an error and ignores it to "facilitate recovery"). So the solution your after would be some additional calls when initializing the database to enable the FK checks (alongside any other app related PRAGMA calls like checking the data_version, configuring journal or page size), and ensure any tables created have STRICT applied.
  - chrismorgan 4y ago
    Unfortunately I found strict mode not very usable, because it only allows the six basic datatype names and doesn’t do column affinity determination <https://www.sqlite.org/datatype3.html#determination_of_column_affinity https://www.sqlite.org/datatype3.html#determination_of_colum...>. This thwarts one of the more practical forms of strictness in querying libraries, automatic column type mapping (e.g. mapping DATE (which has NUMERIC affinity) to your language’s date type, or conceivably some other convention like mapping TEXT_Foo to your own Foo type). I’m perplexed as to the rationale for this restriction which feels just gratuitous and unnecessary.
    - tehbeard 4y ago
      That feels like a bit of a hack, tbh. Playing off a mechanism I think? was made for making porting from other day syntaxes easier? Is sqlite converting an iso8601 to a timestamp, or would it just store as given when type coercion fails due to dashes and spaces? From memory, it's the latter. It would be slightly more brittle, but surely this metadata should be at app/orm layer rather than the database?
      - chrismorgan 4y ago
        > surely this metadata should be at app/orm layer rather than the database? I cannot fathom why you would say this, because the way I see it of course it belongs in the database as part of the table definition, as it’s obviously part of the logical schema. Sure, you can’t actually enforce invariants for individual types so that they would be just conventional aliases for the underlying affinities, but that doesn’t mean you should avoid specifying meaningful types. Which of these would you prefer: CREATE TABLE example ( id BLOB PRIMARY KEY, created INTEGER, data TEXT ); CREATE TABLE example ( id uuid PRIMARY KEY, created timestamp, data json, ); I know that I want the latter, because it makes my life much easier when reading the schema, and lets code automatically use the right types based on inspecting the schema (though you will need to define a mapping of SQL type names to your programming language’s types, since they are still only informational rather than structural like in most SQL databases). Ideally you might be able to define your own datatype affinities (SQL even defines a suitable syntax: CREATE TYPE uuid AS BLOB), but it’s not so bad leaning into the built-in rules with BLOB_uuid, INTEGER_timestamp and TEXT_json (… though on reflection I admit this is rather perilous due to the precise affinity determination rules, shown in the example “TEXT_point” which would be INTEGER due to containing “int”, so maybe it is actually better that strict mode doesn’t blithely use the current affinity determination rules on the expressed type). (Actually on the DATE thing I was forgetting and thinking that was a regular feature but it’s actually just the fallback affinity where a type is specified but not matched by any other rule, NUMERIC. Strike out that example as a canonical definition or anything, then. But the rest of the point still stands.)
- systemvoltage 4y ago
  > Were SQLite not such a stellar piece of software, Why? Complaining about a rational and logical thing is orthogonal to something's popularity or how excellent it is otherwise. State your opinions freely.
rscho 4y ago
I do exactly the same. You import from CSV, everything ends up as a string, and then you copy the data to a STRICT table so you can understand what's really going on.
- dekhn 4y ago
  irreversible loss of information.
  - rscho 4y ago
    What do you mean? I don't think this process ends up with less info than the CSV file format itself. Short of doing that, cleaning very dirty data has no satisfactory solution, I think. Optional typing is a nice middle ground between untyped and slow (R, Python) or strictly typed and tedious (all other DB engines).
    - dekhn 4y ago
      Maybe I misunderstand, but if you take a data type that is a union of two datatypes (string and int) and push it into somethign that converts all the data to strings, you lose the information of whether a string that looks like an int was originally a string or int. Maybe instead you mean to use the database functionality to identify problems like that in the original source data. If somebody hands me a string "11101100101001" I would not attempt to interpret it by parsing it into a binary number, but I think that's because I really like strong, simple typing.
      - at_a_remove 4y ago
        Like I said earlier, I don't know what I am getting. Strong, simple typing is premature at this juncture.
        dekhn 4y ago
        Got it, it's data exploration not a formal ETL pipeline code.
ajkjk 4y ago
well, it seems like you should chuck it into an untyped table first. If there are types on a column I'd... really want them to do something.
muttled 4y ago
It's especially great for large important legacy datasets that similarly did no enforcement. You can then interact with the data and clean it up through some commands without trying to work with massive datasets in Excel where it sometimes just crashes the entire app.
thfuran 4y ago
It's reasonable to have an arbitrary/untyped data type but an integer data type should not accept non-integers.
- rzzzt 4y ago
  Or union types. Just tack "...or a string" onto any questionable column.
- galaxyLogic 4y ago
  Right what's the point of declaring a column to have type INTEGER if it really means "This column MAY contain integers, but may contain other kinds of data as well".

throw10920 4y ago

Were SQLite not such a stellar piece of software, I would complain, and ask that this kind of flexibility be disabled by default, with an option to explicitly enable it if necessary.

tehbeard 4y ago
While I doubt it will ever be enabled by default due to backwards compatibility, there is a strict tables[1] feature now. [1] https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
- silvestrov 4y ago
  There should be an option to have strict tables, foreign key checks and other modern features to be automatically enforced. E.g. a command line argument when creating a DB, which then taints this database as modern. Something like what html did with "<!DOCTYPE html>". Or simply accept that the time has come to make a version 4 of sqlite.
  - ratrocket 4y ago
    That might have to be version 5 of sqlite. Version 4 came and went: https://sqlite.org/src4/doc/trunk/www/index.wiki https://sqlite.org/src4/doc/trunk/www/index.wiki > SQLite4 was an experimental rewrite of SQLite that was active from 2012 through 2014.
  - tomsmeding 4y ago
    > There should be an option to have strict tables, foreign key checks and other modern features to be automatically enforced. I know to enable foreign key checking, and I learned strict tables from this thread. What are the "other modern features" you were referring to? EDIT: In TFA, section 8 is relevant here I guess: SQLite accepts double-quoted strings as a historical misfeature, which can be disabled with the appropriate C API functions. This is one of the "other modern features" I guess; TIL.
  - tehbeard 4y ago
    The strict keyword per that doc already taints the a table for pre 3.37.0 (2021-11-27) libraries. (There is a way around that for pre 3.37 per the doc, but it's mainly enabling the PRAGMA used for db recovery operations that treats the strict at the end as an error and ignores it to "facilitate recovery"). So the solution your after would be some additional calls when initializing the database to enable the FK checks (alongside any other app related PRAGMA calls like checking the data_version, configuring journal or page size), and ensure any tables created have STRICT applied.
- chrismorgan 4y ago
  Unfortunately I found strict mode not very usable, because it only allows the six basic datatype names and doesn’t do column affinity determination <https://www.sqlite.org/datatype3.html#determination_of_column_affinity https://www.sqlite.org/datatype3.html#determination_of_colum...>. This thwarts one of the more practical forms of strictness in querying libraries, automatic column type mapping (e.g. mapping DATE (which has NUMERIC affinity) to your language’s date type, or conceivably some other convention like mapping TEXT_Foo to your own Foo type). I’m perplexed as to the rationale for this restriction which feels just gratuitous and unnecessary.
  - tehbeard 4y ago
    That feels like a bit of a hack, tbh. Playing off a mechanism I think? was made for making porting from other day syntaxes easier? Is sqlite converting an iso8601 to a timestamp, or would it just store as given when type coercion fails due to dashes and spaces? From memory, it's the latter. It would be slightly more brittle, but surely this metadata should be at app/orm layer rather than the database?
    - chrismorgan 4y ago
      > surely this metadata should be at app/orm layer rather than the database? I cannot fathom why you would say this, because the way I see it of course it belongs in the database as part of the table definition, as it’s obviously part of the logical schema. Sure, you can’t actually enforce invariants for individual types so that they would be just conventional aliases for the underlying affinities, but that doesn’t mean you should avoid specifying meaningful types. Which of these would you prefer: CREATE TABLE example ( id BLOB PRIMARY KEY, created INTEGER, data TEXT ); CREATE TABLE example ( id uuid PRIMARY KEY, created timestamp, data json, ); I know that I want the latter, because it makes my life much easier when reading the schema, and lets code automatically use the right types based on inspecting the schema (though you will need to define a mapping of SQL type names to your programming language’s types, since they are still only informational rather than structural like in most SQL databases). Ideally you might be able to define your own datatype affinities (SQL even defines a suitable syntax: CREATE TYPE uuid AS BLOB), but it’s not so bad leaning into the built-in rules with BLOB_uuid, INTEGER_timestamp and TEXT_json (… though on reflection I admit this is rather perilous due to the precise affinity determination rules, shown in the example “TEXT_point” which would be INTEGER due to containing “int”, so maybe it is actually better that strict mode doesn’t blithely use the current affinity determination rules on the expressed type). (Actually on the DATE thing I was forgetting and thinking that was a regular feature but it’s actually just the fallback affinity where a type is specified but not matched by any other rule, NUMERIC. Strike out that example as a canonical definition or anything, then. But the rest of the point still stands.)
systemvoltage 4y ago
> Were SQLite not such a stellar piece of software, Why? Complaining about a rational and logical thing is orthogonal to something's popularity or how excellent it is otherwise. State your opinions freely.

rscho 4y ago

I do exactly the same. You import from CSV, everything ends up as a string, and then you copy the data to a STRICT table so you can understand what's really going on.

dekhn 4y ago
irreversible loss of information.
- rscho 4y ago
  What do you mean? I don't think this process ends up with less info than the CSV file format itself. Short of doing that, cleaning very dirty data has no satisfactory solution, I think. Optional typing is a nice middle ground between untyped and slow (R, Python) or strictly typed and tedious (all other DB engines).
  - dekhn 4y ago
    Maybe I misunderstand, but if you take a data type that is a union of two datatypes (string and int) and push it into somethign that converts all the data to strings, you lose the information of whether a string that looks like an int was originally a string or int. Maybe instead you mean to use the database functionality to identify problems like that in the original source data. If somebody hands me a string "11101100101001" I would not attempt to interpret it by parsing it into a binary number, but I think that's because I really like strong, simple typing.
    - at_a_remove 4y ago
      Like I said earlier, I don't know what I am getting. Strong, simple typing is premature at this juncture.
      - dekhn 4y ago
        Got it, it's data exploration not a formal ETL pipeline code.

ajkjk 4y ago

well, it seems like you should chuck it into an untyped table first. If there are types on a column I'd... really want them to do something.

muttled 4y ago

It's especially great for large important legacy datasets that similarly did no enforcement. You can then interact with the data and clean it up through some commands without trying to work with massive datasets in Excel where it sometimes just crashes the entire app.

thfuran 4y ago

It's reasonable to have an arbitrary/untyped data type but an integer data type should not accept non-integers.

rzzzt 4y ago
Or union types. Just tack "...or a string" onto any questionable column.
galaxyLogic 4y ago
Right what's the point of declaring a column to have type INTEGER if it really means "This column MAY contain integers, but may contain other kinds of data as well".