News for hackers. Stuff that matters.
Re:SQLite Release 3.42.0 by airstrike on Tuesday May 16, 2023 @11:57AM (#35963630)
Is there an unavoidable reason why SQLite won't allow foreign keys to be added with ALTER TABLE, only at table creation? The whole "select * into temporary table and create new table with FK constraint" seems so verbose / convoluted, my layman's mind can't comprehend why one can't simply

    ALTER TABLE "foo" ADD FOREIGN KEY...
Re:SQLite Release 3.42.0 by giraffe_lady on Tuesday May 16, 2023 @12:03PM (#35963753)
The alter table docs have a general explanation kinda.

They don't store the parsed representation of the schema, since that would lock the table to a specific version. They store the create command itself, and each time you open a DB the schema is generated from that. Allows for flexible upgrades to the schema system without the need for migrations, and lets you use the same DB file with multiple sqlite versions.

The downside is that alter table commands are just edits to the create table string, which when accounting for all the different versions is fairly difficult and risky, so alter operations are limited.

Re:SQLite Release 3.42.0 by abtinf on Tuesday May 16, 2023 @12:32PM (#35964226)
Is there something special about ALTER DDLs that make it impossible to store them and play them back along with CREATE DDls to derive the final schema?
Re:SQLite Release 3.42.0 by airstrike on Tuesday May 16, 2023 @02:26PM (#35965725)
Exactly... there's no real apparent reason this can't be done
Re:SQLite Release 3.42.0 by masklinn on Tuesday May 16, 2023 @12:14PM (#35963933)
But also FKs remain half-assed and seen with some disdain: it's 2023, support was added (a bit under) 14 years ago, and you still need to activate foreign keys every time you open the database file. There is still no way to enable FKs by default for a database file, let alone prevent disabling it.
Re:SQLite Release 3.42.0 by CodeHz on Tuesday May 16, 2023 @12:22PM (#35964047)
no, you can do it by define SQLITE_DEFAULT_FOREIGN_KEYS=1 while compiling sqlite3
Re:SQLite Release 3.42.0 by hnarn on Tuesday May 16, 2023 @12:28PM (#35964167)
”You can set it before compiling” is such a foreign concept to the real world that it’s almost meaningless, if that is the only way to change the behavior it’s pretty crazy.
Re:SQLite Release 3.42.0 by abtinf on Tuesday May 16, 2023 @12:36PM (#35964284)
I generally agree when it comes to standalone software, but not for libraries.

SQLite is both.

Setting a compiler flag is reasonable for the common case where it is included as a header file in a C/C++ application.

It is certainly inconvenient when you design with that feature in mind, but can't rely on it being part of the standard binary distributions.

Re:SQLite Release 3.42.0 by arp242 on Tuesday May 16, 2023 @12:34PM (#35964245)
Many applications use SQLite as an embedded library; that's how it's intended to be used anyway.

It doesn't really jibe all that well with things like Python though, where you typically don't do that. But it's certainly not a "foreign concept" or "almost meaningless".

For e.g. Python you can still just set the desired parameters when connecting, which should usually be handled by the database connector/driver for you.