5 ms·
Before rendering judgment, please read SQLite's own page on the subject. I think you will see that the choice wasn't sloppy but thoughtfully designed, https://s
by combatentropy 5y ago
Before rendering judgment, please read SQLite's own page on the subject. I think you will see that the choice wasn't sloppy but thoughtfully designed, https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html
My takeaway from that is: (1) for many people, SQLite is not your database, but you already knew that, (2) it is nice there exists a database out there with this flexibility, if you want it, and (3) if you choose it, you might consider declaring columns NUMERIC. From the documentation: "A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal . . . If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT." So NUMERIC is kind of like TEXT except when the text is a pure number.
Some ask, why would you want a database like this? I don't know, perhaps to match your application language if it is also typed dynamically. For example, PHP.
- cirrus3 5y ago> why would you want a database like this? I don't know Yea, I hope anyone who takes advantage of this does know, and intentionally uses it to their advantage in a scenario that outweighs the long-term downsides.
- piaste 5y ago> Before rendering judgment, please read SQLite's own page on the subject. I think you will see that the choice wasn't sloppy but thoughtfully designed, https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html I've read the whole page. It's an excellent piece of documentation and reference, but it says nothing about the reasoning behind that design choice, nor show that it was a design choice at all (rather than being e.g. a TCL legacy as some other commenters have hypothesized). The introduction says "However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases", but it doesn't say what those things are, or explain why they're considered a good trade-off against the well-known footguns of implicit type coercion. (Indeed, if SQLite had rigid static typing, all the detailed rules on that page wouldn't need to exist!)
- jbverschoor 5y agoIt’s the classic correct vs lazy to use things. MySQL, mongo, JavaScript. They all are incorrect in their behavior, but because of their defaults and ease of getting started, they got big.
- amarshall 5y agoFor MySQL and JS I’d say they got big off just being the only thing that was there. Browser? JS, no choice. Shared hosting? MySQL, usually no choice.
- meltedcapacitor 5y agoChicken and egg issue here: why did shared hosting providers choose MySql? (I guess because of tight integration with PHP, but then same question with PHP.)
- de_keyboard 5y agoI think cost drove MySQL adoption. Remember it arrived when most companies were using Oracle / IBM / MS databases.
- Macha 5y agoAlso while they've converged somewhat in the years since, when you were comparing MySQL 4 to whatever Postgres version was in the 00s, MySQL's tradeoff of performance over correctness vs Postgres tradeoff of correctness over performance would have been very appealing to shared providers looking to bundle the most customers on the least hardware
- jbverschoor 5y agoBut that's the things.. There's always a reason to compromise correctness over something else.
- 5y ago
- mike_hock 5y agoIs there a switch somewhere to turn reasonable behavior on?
- capableweb 5y agoWhat does "reasonable" mean here? Clearly, for SQLite, it is reasonable because that's how they want their database to work. But for someone who is used to MongoDB, maybe "reasonable" means that you can store documents instead. Actually, your comment almost strikes me as mean. Instead of asking "can I make it not dynamically typed?", you're asking for "reasonable" behavior, implying the existing behavior is not reasonable, basically spitting on the SQLite projects decision.
- vbezhenar 5y agoMost RDBMSs use static typing. That's what people expect from a database. It is SQL database, so people naturally expect familiar concepts to behave consistently with their previous experience. If database fails that assumption, it's fair to call this behaviour unreasonable. It's understandable that this was early design error which is impossible to correct now, but calling it reasonable just not to hurt someones feelings is unreasonable.
- em500 5y agoThink of SQLite not as a replacement for Oracle but as a replacement for fopen().
- capableweb 5y agoMost databases also allows you to remotely connect to them, does that mean that SQLite not offering that is unreasonable? No, it means that your understanding of SQLite is incorrect, as the use case for SQLite is not to offer remote connections. Same goes for dynamically/statically typed. Your comment about this being a "design error" is also in bad taste. Have you tried looking up where this decision comes from and why it is like that? According to the SQLite folks, this is on purpose, not by accident. > As far as we can tell, the SQL language specification allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python. https://www.sqlite.org/different.html https://www.sqlite.org/different.html
- vbezhenar 5y ago> you might consider declaring columns NUMERIC. From the documentation: "A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal . . . If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT." So NUMERIC is kind of like TEXT except when the text is a pure number. So it'll corrupt text value "1.0000"? Sounds like a dangerous feature.
- clnhlzmn 5y agoIf you're storing the text "1.0000" in a NUMERIC column maybe you're the dangerous one.
- goto11 5y agoThe FAQ basically admit it was a design mistake to make "flexible typing" the default, but it is too late to fix due to backwards compatibility. https://sqlite.org/quirks.html#flexible_typing https://sqlite.org/quirks.html#flexible_typing It is probably not as big a problem in an embedded database as it would be in a server-based database. Nevertheless "flexible typing" should have been an opt-in feature per column, not the default.
- layer8 5y ago> So NUMERIC is kind of like TEXT except when the text is a pure number. That‘s exactly the thing that always comes back to bite you in Excel.