> None of the things mentioned in the article appear to have any significant impact on the resource usage and or embeddability if they were implemented, so it's totally fair to draw comparisons here.
Which is why I mention "history and philosophy" on the same line of my comment.
The `ALTER TABLE` documentation on SQLite [0] has a very clear reasoning on why it's implemented the way it is, and gives steps on how to reproduce more common usage of `ALTER TABLE` in other SQL engines.
To directly quote from their docs:
> Why ALTER TABLE is such a problem for SQLite
> Most SQL database engines store the schema already parsed into various system tables. On those database engines, ALTER TABLE merely has to make modifications to the corresponding system tables.
> SQLite is different in that it stores the schema in the sqlite_schema table as the original text of the CREATE statements that define the schema. Hence ALTER TABLE needs to revise the text of the CREATE statement. Doing so can be tricky for certain "creative" schema designs.
> The SQLite approach of storing the schema as text has advantages for an embedded relational database. For one, it means that the schema takes up less space in the database file. This is important since a common SQLite usage pattern is to have many small, separate database files instead of putting everything in one big global database file, which is the usual approach for client/server database engines. Since the schema is duplicated in each separate database file, it is important to keep the schema representation compact.
> Storing the schema as text rather than as parsed tables also give flexibility to the implementation. Since the internal parse of the schema is regenerated each time the database is opened, the internal representation of the schema can change from one release to the next. This is important, as sometimes new features require enhancements to the internal schema representation. Changing the internal schema representation would be much more difficult if the schema representation was exposed in the database file. So, in other words, storing the schema as text helps maintain backwards compatibility, and helps ensure that older database files can be read and written by newer versions of SQLite.
> Storing the schema as text also makes the SQLite database file format easier to define, document, and understand. This helps make SQLite database files a recommended storage format for long-term archiving of data.
> The downside of storing schema a text is that it can make the schema tricky to modify. And for that reason, the ALTER TABLE support in SQLite has traditionally lagged behind other SQL database engines that store their schemas as parsed system tables that are easier to modify.
Which is why I mention "history and philosophy" on the same line of my comment.
The `ALTER TABLE` documentation on SQLite [0] has a very clear reasoning on why it's implemented the way it is, and gives steps on how to reproduce more common usage of `ALTER TABLE` in other SQL engines.
To directly quote from their docs:
> Why ALTER TABLE is such a problem for SQLite
> Most SQL database engines store the schema already parsed into various system tables. On those database engines, ALTER TABLE merely has to make modifications to the corresponding system tables.
> SQLite is different in that it stores the schema in the sqlite_schema table as the original text of the CREATE statements that define the schema. Hence ALTER TABLE needs to revise the text of the CREATE statement. Doing so can be tricky for certain "creative" schema designs.
> The SQLite approach of storing the schema as text has advantages for an embedded relational database. For one, it means that the schema takes up less space in the database file. This is important since a common SQLite usage pattern is to have many small, separate database files instead of putting everything in one big global database file, which is the usual approach for client/server database engines. Since the schema is duplicated in each separate database file, it is important to keep the schema representation compact.
> Storing the schema as text rather than as parsed tables also give flexibility to the implementation. Since the internal parse of the schema is regenerated each time the database is opened, the internal representation of the schema can change from one release to the next. This is important, as sometimes new features require enhancements to the internal schema representation. Changing the internal schema representation would be much more difficult if the schema representation was exposed in the database file. So, in other words, storing the schema as text helps maintain backwards compatibility, and helps ensure that older database files can be read and written by newer versions of SQLite.
> Storing the schema as text also makes the SQLite database file format easier to define, document, and understand. This helps make SQLite database files a recommended storage format for long-term archiving of data.
> The downside of storing schema a text is that it can make the schema tricky to modify. And for that reason, the ALTER TABLE support in SQLite has traditionally lagged behind other SQL database engines that store their schemas as parsed system tables that are easier to modify.
[0] https://www.sqlite.org/lang_altertable.html