6 ms·
> The key point is that SQLite is very forgiving of the type of data that you put into the database. For example, if a column has a datatype of "INTEGER" and th
by ajkjk 4y ago
> The key point is that SQLite is very forgiving of the type of data that you put into the database. For example, if a column has a datatype of "INTEGER" and the application inserts a text string into that column, SQLite will first try to convert the text string into an integer, just like every other SQL database engine. Thus, if one inserts '1234' into an INTEGER column, that value is converted into an integer 1234 and stored. But, if you insert a non-numeric string like 'wxyz' into an INTEGER column, unlike other SQL databases, SQLite does not throw an error. Instead, SQLite stores the actual string value in the column.
wtf. who would ever want that?
- at_a_remove 4y agoMe! 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 agoWere 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 agoWhile 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 agoThere 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 agoThat 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 agoThe 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 agoUnfortunately 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.
- 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 agoI 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 agoirreversible loss of information.
- rscho 4y agoWhat 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 agoMaybe 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 agoLike I said earlier, I don't know what I am getting. Strong, simple typing is premature at this juncture.
- dekhn 4y agoGot it, it's data exploration not a formal ETL pipeline code.
- ajkjk 4y agowell, 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 agoIt'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 agoIt's reasonable to have an arbitrary/untyped data type but an integer data type should not accept non-integers.
- rzzzt 4y agoOr union types. Just tack "...or a string" onto any questionable column.
- galaxyLogic 4y agoRight 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".
- dspillett 4y agoThis always put me off a bit too. I have memories of mySQL supporters back in the early days defending it storing dates like 2022-02-31 and other such type/range/other issues with the standard refrain of “but SELECT * is blazing fast!”… As of last year there is an option to make things more strict (https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html) though as SQLite doesn't have real date types, unless you are using one of the integer storage options code could insert invalid dates like mysql used to allow. -- EDIT: having actually read the linked article, it explicitly mentions the date type issue also.
- paulclinger 4y agoEven in the absence of strict tables, you can also add a CHECK constraint whenever limits are needed on the value types. Here is one example from a recent discussion in SQLite forum threads: `check(datetime(x, '+0 seconds') IS x)`.
- chasil 4y agoAn important proviso on the strict tables is that they will not be observed on older SQLite versions lacking this feature. SQLite has been rock-solid since attaining DO-178B, and commonly isn't ever upgraded in many installations. CentOS 7 is using 3.7.17. Since the v3 database format is standardized, the older version can utilize a STRICT database file, but will not have the capability to alter datatype behavior. For these cases, implementing both STRICT and the relevant CHECK constraints is advisable.
- RedShift1 4y agoIf you use integer unix timstamp as datatype for dates, you technically cannot insert an invalid date
- dspillett 4y agoHence "unless you use one of the integer storage options". I suspect many don't because the values being directly human readable could be useful.
- SPBS 4y agoSQLite needs this, because changing column types is such a pain in the ass. The answer to "I want to move from INT primary keys to TEXT/UUID, how do I change the column type?" is "just insert the data into the column because SQLite allows everything". And the reason changing column types is so hard is because, uh, SQLite stores its schema as human readable plaintext (easier to keep compatibility between versions) and not normalized tables like other databases.
- dekhn 4y agoWhat does it mean for a database to have primary keys of different types? In particular, primary keys are frequently used for clustering and other performance enhancements and I would expect that they would need a total ordering that made sense.
- RedShift1 4y agoIf you're talking about clustering you're probably also beyond the use case of sqlite
- dekhn 4y agoNot multi-machine clustering, index clustering: https://www.sqlite.org/withoutrowid.html https://www.sqlite.org/withoutrowid.html As much as I love sqlite, its table model is really confusing to me coming from a postgres mentality: "WITHOUT ROWID is found only in SQLite and is not compatible with any other SQL database engine, as far as we know. In an elegant system, all tables would behave as WITHOUT ROWID tables even without the WITHOUT ROWID keyword. However, when SQLite was first designed, it used only integer rowids for row keys to simplify the implementation. This approach worked well for many years. But as the demands on SQLite grew, the need for tables in which the PRIMARY KEY really did correspond to the underlying row key grew more acute. The WITHOUT ROWID concept was added in order to meet that need without breaking backwards compatibility with the billions of SQLite databases already in use at the time (circa 2013)."
- jrockway 4y agoIt's even more fun in a boolean column, where some databases accept 't'/'f', 'true'/'false', etc. SQLite accepts some of those, but treats other ones as text.
- rscho 4y ago> wtf. who would ever want that? Each and every biostatistician on this planet. Especially those touching clinical data. Personally, I was saddened to learn that DuckDB did not include dynamically typed, or at least untyped, columns. Happily my data loads are usually small enough for a row-oriented data store. CHECK constraints, now in conjunction with STRICT tables, are the best invention since sliced bread! If I could improve on one thing, it would be to remove any type notion from non-STRICT tables.
- masklinn 4y agoFwiw the ANY type is valid in strict tables, and does what it says. Slightly more so than in non-strict tables in fact: it will store exactly what you give it without trying to reinterpret it e.g. in non-strict, a quoted literal composed of digits will be parsed and stored as an integer, in strict, as a string. Strict mode also doesn’t influence the parser, so e.g. inserting a `true` in a strict table will work, because `true` is interpreted as an integer literal.
- mytherin 4y agoWe have a PR in the works that is adding support for sum types to DuckDB [1]. Not quite fully dynamic, but perhaps still useful :) [1] https://github.com/duckdb/duckdb/pull/4966 https://github.com/duckdb/duckdb/pull/4966
- groue 4y agoI do, for JSON columns. I store UTF8 strings in SQLite, so that it is easy to see JSON values with a plain `SELECT *`). And I load blobs, because I code in Swift and the standard JSON decoder eats raw UTF8 memory buffers, not strings. This avoids two useless conversions: - first from a C string loaded from SQLite to a Swift Unicode string (with UTF8 validation). - next from this Swift Unicode string to a UTF8 memory buffer (so that JSONDecoder can do its job). SQLite is smart enough to strip the trailing \0 when you load a blob from a string stored in the database :-)
- systemvoltage 4y agoSQLite is an excellent app database for local things. It doesn't compete with PostgreSQL or MySQL for huge number of reasons. So, to make it fit in the competition model, perhaps there are a bunch of things are glossed over. Does anyone see a massive pro-sqlite movement going on? Sort of like what happens in JS-ecosystem. Everyone is bandwagoning on it. Criticism of SQLite is much welcomed, specifically exemplifying what its role is and which use cases it serves really well.
- tejtm 4y ago> wtf. who would ever want that? Have you met non-computer scientists? Anything and everything is fair game and it is probably for the best. Job security anyway