Ah Charlotte, we've had a problem?
RDBMS Are Not Designed To Handle Change. RDBMS Are Not Designed for Heterogeneous Data. RDBMS Are Not Designed For Scale. RDBMS Are Not Designed for Mixed Workloads. RDBMS Are A Mismatch for Modern App Development.
(2) By doug (doug9forester) on 2021-09-07 00:42:56 in reply to 1 [source]
I've had my share of schema changes which always seem to end up needing to fixed up manually here or there because I forgot this case or that case. I can conceive of a database of schema versions for a project, where each schema change is a step from one project version to the next. Of course, each step must include data transformations and change actions, perhaps in the form of SQL statements - updates, deletes, inserts - to move an existing database from one state to the next.
I can also envision a process where the changes are generated by comparing a new schema to the old and - with some (magic) rules, creates the SQL statements necessary to transform an existing database from the old schema to the new one. Creating the data transformations is a hard one.
How could SQLite help with this? We already have a way to identify the version of a schema with the User Code (provided the user hasn't used it for some other purpose). One can capture a database schema in another database with version information. Do it again with the manually created new schema, and a tool now has the information at had to compare the schemas. It would be ideal to have a "schema version" built-in, but that requires the knowledge that a database is part of a project - a concept beyond SQLite.
Creating the data transformations becomes the next problem. Is there anything more we need from SQLite to produce a tool that would assist a project to move from one schema to the next?