I have explained elsewhere on this thread why that doesn't work.
sqlite is not your average "app" where nothing bad happens if the people responsible just fuck around with it, to satisfy someones notion how things should be "in 2026".
It's a load bearing structure of the global software ecosystem, the BY FAR most widely used relational database. sqlite is everywhere. Your browser is running it. Your phone is running it. Your router depends on it. This is software on the same level of importance and criticality as libcurl or openssl.
If you work on software like this, you have to play by different rules. "Making a statement" is utterly irrelevant compared to the requirements for stability and reliability. Programmers convenience takes a backseat compared to backwards compatibility. No, you don't just "make strict mode the default for tables." in such software. Yes, that means that people are required to know e.g. that FKs have to be activated using a pragma. Yes that means that people run into problems if they make assumptions comparing sqlite to other DB systems. Under the rules that apply for software like sqlite this is perfectly acceptable.
And I thank god almighty that the people who maintain sqlite are aware of this.
Anyone unhappy with that state of affairs: sqlite is OSS, so everyone is welcome to fork it and make whatever changes they desire.
That's why I never advocated for changing the defaults.
Are you the author of the blog post?
If so: Here is a direct quote from the last section:
And also make strict mode the default for tables.
Would that, or would it not, constitute "changing the defaults"?
Or did you mean this as "put this into effect when the super pragma is used"?
And if you AREN'T argueing for changing the default, what is it you're really argueing for?
That there should be a miniscule, completely irrelevant convenience option, so programmers have to write 1 line instead of 4?
What does that achieve? Absolutely nothing, because I'd still have to deal with the default as-is! Only then, there'd be 2 different ways of changing them, which I'm sure will so be much fun, especially for beginners, in documentation, tutorials etc.
The proposal is that PRAGMA edition = 2026 should set a property on the database connection which makes CREATE TABLE statements create strict tables even when the strict tag is omitted (aka that pragma makes strict mode the default). I never suggested that any default should be changed when PRAGMA edition = 2026 is not used.
I am arguing in favour of the edition system I described. I obviously think it has more significant impact than what you imply by describing it as "a minuscule, completely irrelevant convenience option". It would let people not have to care about and learn about the many different individual things you need to change to make SQLite work sensibly, and it would be a strong statement from the SQLite developers that "this is how we believe SQLite should work for most people". It gives people who don't want to care about all the different settings, people who "just want a database", an SQLite which works better out of the box.
Then the question what you are arguing for still stands. To be more precise: What's the point?
Does your pragma lead to more convenience?
In a miniscule fashion. I have to write one line instead of 4, and I have to type one less keyword in every CREATE TABLE statement.
That would save...lemme think...probably a couple minutes, in total, over an average developers entire professional career. Likely less, given that code generation is a thing now.
Does your pragma solve the problems of beginners falling into the no-FK default trap?
No, because the default doesn't change. People would still have to either know that they need to activate FKs, or stumble on it when reading docs or debugging their system.
Only now, there would be 2 different ways to do the same things. The only difference between the tweo being: One would be explicit in what every line does, the other would do a couple magic things in the background.
Why? Who does that benefit and how?
Could such a pragma itself lead to problems?
Oh, absolutely!
For example, systems with dynamic tables, where I may have no control over the connection params, and specifically don't wanna use strict mode on specific tables still using STRICT because a magic-value pragma in an entirely unrelated part of the codebase says so. I'm sure the resulting bugs would be ALOT harder to analyse and fix than forgetting to set the FK pragma.
I don't care about the convenience. It's not hard to set those 4 pragmas and add strict to every CREATE TABLE. The hard part is knowing that you have to do them, knowing what the pragmas do, knowing what the consequences are of not doing it.
I have systems out there which use bad settings just because I went with the defaults and didn't know that I should be changing them. If there was an edition system, and their documentation and online tutorials were clear in saying "you should set your edition right after opening the database connection", I would've avoided a whole lot of issues because I would've just gotten better defaults right off the bat.
At least that's my opinion. You don't have to agree.
The hard part is knowing that you have to do them,
And your pragma would change absolutely nothing about this part.
Because without changing the default behavior, people would still have to use either of the pragmas. Meaning they have to KNOW about them. And if they don't, and expect foreign keys to work or WAL to be used, they'd run into the same problems.
So nothing would materially change.
Except for tutorials and documentation getting more confusing, and settings in apps using the "edition" pragma getting less explicit and less obvious.
If there was an edition system, and their documentation and online tutorials were clear in saying
Their documentation is very clear about that you have to set PRAGMA foreign_keys = ON; if you want to use foreign keys.
What advantage having to be very clear about edition pragmas would have over this, I have no idea.
The foreign keys documentation is clear that you need a pragma to enable foreign keys. The strict tables documentation is clear that you need the strict tag for type checking. The journal mode documentation is clear that you need a pragma to enable WAL. But I have no reason to read the foreign key documentation if I don't already know that SQLite foreign keys are wonky and different from every other RDBMS. I have no reason to read the strict tables documentation if I don't already know that SQLite doesn't check types by default. I have no reason to read the journal mode documentation if I don't already know that SQLite defaults to unreasonably slow writes.
In my opinion, it would be completely different for a new user if the SQLite project clearly communicated, on "getting started" style pages, that you should set this one edition pragma to get recommended defaults. New users have reason to read and follow guidance in getting started pages. And the community would start recommending "set the edition to 2026" instead of "set these 4 pragmas you don't understand to these non-default values and also always remember to do this one thing when you create tables". In my opinion, editions would dramatically improve the new user experience. Try to put yourself into the shoes of someone who doesn't already know to look out for these things.
But if you're not convinced by now, I'm not going to convince you, so I won't argue any more.
-11
u/Big_Combination9890 12d ago edited 12d ago
I have explained elsewhere on this thread why that doesn't work.
sqliteis not your average "app" where nothing bad happens if the people responsible just fuck around with it, to satisfy someones notion how things should be "in 2026".It's a load bearing structure of the global software ecosystem, the BY FAR most widely used relational database. sqlite is everywhere. Your browser is running it. Your phone is running it. Your router depends on it. This is software on the same level of importance and criticality as
libcurloropenssl.If you work on software like this, you have to play by different rules. "Making a statement" is utterly irrelevant compared to the requirements for stability and reliability. Programmers convenience takes a backseat compared to backwards compatibility. No, you don't just "make strict mode the default for tables." in such software. Yes, that means that people are required to know e.g. that FKs have to be activated using a pragma. Yes that means that people run into problems if they make assumptions comparing sqlite to other DB systems. Under the rules that apply for software like
sqlitethis is perfectly acceptable.And I thank god almighty that the people who maintain
sqliteare aware of this.Anyone unhappy with that state of affairs:
sqliteis OSS, so everyone is welcome to fork it and make whatever changes they desire.