I'm updating one of my units in Delphi to automatically output the schema of any database I'm attempting to open while in the debugger for two purposes. One reason that when a user does a FILE> NEW, the most recent database is created based on the SQL commands found in sqlite_master, and the schema to create said database is stored in a resource file generated pre-build and that resource file is included in the EXE. The second reason, at every launch, any database accessed with this unit gets its current schema exported to a text file. That way, while I'm messing around with the database schema during development time, I have a history on what was changed, and any changes I can check into my VCS.
To do all of this, I'm relying on the results from "pragma schema_version". I've noticed that this bumps up every time I run a vacuum or use the backup API against the database. Is this supposed to happen? I assumed (bad thing I know) that with vacuum, I'm not changing the actual schema of the database, just reorganizing data within. I understand that the schema would be how the data is to be structured, not where the data is to be stored. I also assumed that the backup API would retain the schema_version since it also isn't changing the schema... sorta. It could be recreating the database from scratch (Granted a schema change is going from nothing to something), but then I'd assume schema_version would be current, 0 or 1, not current+1. This behavior seems to at least be in SQLite.DLL is 3.7.17. I've upgraded to 3.14.1 and I'm still seeing the same thing. If this *IS* intended behavior, may I ask why it is? Is it because I'm not understanding something correctly? I'd rather not rely on user_version. Doing so would mean that, while in development, I'd have to always get the current schema in text, compare it against the previous schema if one exists, then do an operational WRITE on the user_schema if something changes. I'd rather rely on using my existing external tools to design and maintain the database schema and have it bump up schema_version, while the programs I'm writing just use the structures I provided. I'd also prefer to NOT force my applications to keep to the platter for data access. The volume of data I'm playing with isn't significant, so RAM is nice, and saving access to my SSDs for writes is a bonus. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
