7 ms·

I wouldn't go as far as to say I'm aghast at the lax typing¹, but I look forward to this change hitting a release. Adding some sugary support for a strict DATE

by JNRowe 5y ago

I wouldn't go as far as to say I'm aghast at the lax typing¹, but I look forward to this change hitting a release. Adding some sugary support for a strict DATETIME type in a future release would tick every box I want from sqlite. In a couple of projects I work on the main reason for plopping an ORM between sqlite and the application is to implement type strictness, and removing that need would be excellent. It would also neatly shut down a discussion that comes up far too often when someone wants to use sqlite as data store, which is surely a good thing as it is often just a stop motion argument in my experience. ¹ I am closer to that camp iff I have to choose an option from the article.

gwd 5y ago
I think "other developers are aghast" is a bit strong for technical documentation. I was certainly never aghast: knowing that SQLite started its life aimed at TCL, the decision made sense, and the commitment to backwards compatibility is admirable. Nonetheless I certainly welcome this change. The "doesn't enforce foreign key constraints by default" is much more surprising / annoying to me.
- JNRowe 5y ago
  I suspect if I had to answer as many questions/rants about type strictness as the authors do I'd probably choose much stronger language. It definitely wasn't intended, but I guess my earlier comment could be read as the ten-thousandth entitled complaint of the day too.
- SQLite 5y ago
  Scan this HN thread to see comments from devs who are aghast. :-)
  - crazygringo 5y ago
    And even more, scan the HN thread from 16 days ago: https://news.ycombinator.com/item?id=28050198 https://news.ycombinator.com/item?id=28050198 "Aghast" feels like an appropriately accurate characterization to me. ;) I'm actually curious if this change was a direct (and incredibly fast) reaction to that thread, or if the timing was just coincidental?
    - SQLite 5y ago
      coincidence
      - simonw 5y ago
        It's not completely clear to me how this will impact backwards compatibility: if I create a table with STRICT will versions of SQLite prior to the one that ships the feature still be able to open that database file and read from and write to that table? If they can, and they insert an invalid row, what will happen when a modern version of SQLite attempts to read from the table?
        Lex-2008 5y ago
        I'm an SQLite fan and not a developer, so I don't know for sure, but I think that they will do the same thing as with generated columns. Link: https://www.sqlite.org/gencol.html#compatibility https://www.sqlite.org/gencol.html#compatibility Quote: Generated column support was added with SQLite version 3.31.0 (2020-01-22). If an earlier version of SQLite attempts to read a database file that contains a generated column in its schema, then that earlier version will perceive the generated column syntax as an error and will report that the database schema is corrupt. To clarify: SQLite version 3.31.0 can read and write any database created by any prior version of SQLite going back to SQLite 3.0.0 (2004-06-18). And, earlier versions of SQLite, prior to 3.31.0, can read and write databases created by SQLite version 3.31.0 and later as long as the database schema does not contain features, such as generated columns, that are not understood by the earlier version. Problems only arise if you create a new database that contains generated columns, using SQLite version 3.31.0 or later, and then try to read or write that database file using an earlier version of SQLite that does not understand generated columns. End of quote. I can't find it easily in documentation, but I've read somewhere that SQLite stores schema in database as text - and reasoning behind this was that it allows SQLite devs change internal representation of schema without worrying about backwards compatibility too much.
  - gwd 5y ago
    Yeah, I seem to have made the mistake of actually reading the documentation. I think that's mostly your fault though, for making the documentation so interesting and accessible.

by JNRowe 5y ago

gwd 5y ago

I think "other developers are aghast" is a bit strong for technical documentation. I was certainly never aghast: knowing that SQLite started its life aimed at TCL, the decision made sense, and the commitment to backwards compatibility is admirable. Nonetheless I certainly welcome this change. The "doesn't enforce foreign key constraints by default" is much more surprising / annoying to me.

JNRowe 5y ago
I suspect if I had to answer as many questions/rants about type strictness as the authors do I'd probably choose much stronger language. It definitely wasn't intended, but I guess my earlier comment could be read as the ten-thousandth entitled complaint of the day too.
SQLite 5y ago
Scan this HN thread to see comments from devs who are aghast. :-)
- crazygringo 5y ago
  And even more, scan the HN thread from 16 days ago: https://news.ycombinator.com/item?id=28050198 https://news.ycombinator.com/item?id=28050198 "Aghast" feels like an appropriately accurate characterization to me. ;) I'm actually curious if this change was a direct (and incredibly fast) reaction to that thread, or if the timing was just coincidental?
  - SQLite 5y ago
    coincidence
    - simonw 5y ago
      It's not completely clear to me how this will impact backwards compatibility: if I create a table with STRICT will versions of SQLite prior to the one that ships the feature still be able to open that database file and read from and write to that table? If they can, and they insert an invalid row, what will happen when a modern version of SQLite attempts to read from the table?
      - Lex-2008 5y ago
        I'm an SQLite fan and not a developer, so I don't know for sure, but I think that they will do the same thing as with generated columns. Link: https://www.sqlite.org/gencol.html#compatibility https://www.sqlite.org/gencol.html#compatibility Quote: Generated column support was added with SQLite version 3.31.0 (2020-01-22). If an earlier version of SQLite attempts to read a database file that contains a generated column in its schema, then that earlier version will perceive the generated column syntax as an error and will report that the database schema is corrupt. To clarify: SQLite version 3.31.0 can read and write any database created by any prior version of SQLite going back to SQLite 3.0.0 (2004-06-18). And, earlier versions of SQLite, prior to 3.31.0, can read and write databases created by SQLite version 3.31.0 and later as long as the database schema does not contain features, such as generated columns, that are not understood by the earlier version. Problems only arise if you create a new database that contains generated columns, using SQLite version 3.31.0 or later, and then try to read or write that database file using an earlier version of SQLite that does not understand generated columns. End of quote. I can't find it easily in documentation, but I've read somewhere that SQLite stores schema in database as text - and reasoning behind this was that it allows SQLite devs change internal representation of schema without worrying about backwards compatibility too much.
- gwd 5y ago
  Yeah, I seem to have made the mistake of actually reading the documentation. I think that's mostly your fault though, for making the documentation so interesting and accessible.