3 ms·

I think it is time for a real version/generation 4 of SQLite which drops some backwards compatibility (e.g. file format) and has 1) strict always enforced.

I think it is time for a real version/generation 4 of SQLite which drops some backwards compatibility (e.g. file format) and has 1) strict always enforced. 2) full datatypes (ints, floats, datetime, jsonb) 3) all "ALTER TABLE" functionality, even if it has to rewrite the table

bruce511 2y ago
Not to be contrary but... why? Why make a new vesion that breaks compatibility with the old version? Why make a new version just so it behaves like all the other database engines out there? Isn't having difference the point of having choices?
- silvestrov 2y ago
  As I interpret the record format (https://www.sqlite.org/fileformat.html#record_format https://www.sqlite.org/fileformat.html#record_format) then new native datatypes (datetime, jsonb) cannot be added without breaking the file format. When file format is no longer backwards compatible, it makes sense to make a new major (semantic) version, so you don't have a situation where version 3.50 can read a file but version 3.43 cannot.
- D13Fd 2y ago
  Agreed. Most of that can be achieved with SQL (how hard is it to add STRICT?) or the code you use to interact with SQLite. I don't see how it's worth sacrificing backwards compatibility to achieve those things, which are mostly non-issues in practice.
  - pdimitar 2y ago
    I don't see what is so appealing about backwards compatibility. The database is an implementation detail and its specifics should not block anything. Seeing several comments like yours is puzzling to me because I find myself unable to understand what are the devs preaching for it gaining from SQLite's extremely conservative backwards compatibility policy. For example, PostgreSQL has `pg_upgrade`. You run that after you upgrade its major version and it's a bulletproof and easy transition 99% of the time.
    - bruce511 2y ago
      Like with most things, context matters. If you have a "closed" system (such that "you" control the database, and all access to it) then upgrades are "easy" to do. You just upgrade the server and all clients. If your system is more diverse then it's harder because in that case server and clients gave to be upgraded together. If I'm using multiple different programs (Accounting, Payroll, Access control etc) possibly from multiple vendors, then coordinating everything to happen at the same time can be impossible. If the server is not backwards compatible with old clients then you can get stuck. In the case of SQLite for example lots of systems rely on the stability of the file format. Breaking that would not be welcome.

bruce511 2y ago

Not to be contrary but... why? Why make a new vesion that breaks compatibility with the old version? Why make a new version just so it behaves like all the other database engines out there? Isn't having difference the point of having choices?

silvestrov 2y ago
As I interpret the record format (https://www.sqlite.org/fileformat.html#record_format https://www.sqlite.org/fileformat.html#record_format) then new native datatypes (datetime, jsonb) cannot be added without breaking the file format. When file format is no longer backwards compatible, it makes sense to make a new major (semantic) version, so you don't have a situation where version 3.50 can read a file but version 3.43 cannot.
D13Fd 2y ago
Agreed. Most of that can be achieved with SQL (how hard is it to add STRICT?) or the code you use to interact with SQLite. I don't see how it's worth sacrificing backwards compatibility to achieve those things, which are mostly non-issues in practice.
- pdimitar 2y ago
  I don't see what is so appealing about backwards compatibility. The database is an implementation detail and its specifics should not block anything. Seeing several comments like yours is puzzling to me because I find myself unable to understand what are the devs preaching for it gaining from SQLite's extremely conservative backwards compatibility policy. For example, PostgreSQL has `pg_upgrade`. You run that after you upgrade its major version and it's a bulletproof and easy transition 99% of the time.
  - bruce511 2y ago
    Like with most things, context matters. If you have a "closed" system (such that "you" control the database, and all access to it) then upgrades are "easy" to do. You just upgrade the server and all clients. If your system is more diverse then it's harder because in that case server and clients gave to be upgraded together. If I'm using multiple different programs (Accounting, Payroll, Access control etc) possibly from multiple vendors, then coordinating everything to happen at the same time can be impossible. If the server is not backwards compatible with old clients then you can get stuck. In the case of SQLite for example lots of systems rely on the stability of the file format. Breaking that would not be welcome.