Hi Andreas,
Our application uses 3 Sqlite dbs and System.Data.Sqlite.dll (dropped by
installer). Functionality allows users to start a new instance of one of the
databases at run time. Hence we ship empty databases in a binary format,
created by one of our developers. The process of making changes to the DDL
is basically using a sqlite admin tool, make the changes, then update the
installer with the new db file. I am looking for a better way how to
automate this and have better source control integration. At my other job,
we got MS SQL ddl source files as text files in source control, verify them
at build time, and then ship the scripts.
I would like accomplish these (or at least some of these) for the Sqlite
dbs:
- [R1] text files for sql in source control
- [R2] build them from command line during msbuild for syntax verification
- ship text files in the installer (I know how to do that)
- [R3]run the text files against a newly created db at run time in order
"install" the schema and data (I think this is just a simple execute the sql
ddl agains the db, right?)
- [R4] support schema updates. If V2 of the product ships, all dbs that are
older should automatically be migrated to V2 when loaded. Is this as simple
as writing the ddl as IF COLUMN EXISTS etc. statements?
- [R5] version detection. At run time need to detect that a given db is of
version v1. simple column in dbversion table?
TCL automates the installation of a dev schema or DDL schema version in
a production database. The script generates an empty version_db from the
new, development db schema. The script attaches the version_db to the
production database production_db. It copies the data from the
production db into the new, empty version_db. Using SQL statements of
INSERT SELECT copies the data. After version_db has the new data; SQL
queries recursively validate the data using SQL statements. Finally an
execution of the SQLite3 CLI checks the version_db integrity to avoid
any generation errors. The automation script does:
- Backup dev_db and production_db using SQLite3 Backup API.
- Use SQLite3 CLI .schema command to generate and backup the schemas for
dev_db and production_db.
- Attach version_db to production_db to copy the production data to the
new db.
- Recursively INSERT SELECT the data from each table in the
production_db to the version_db.
- Validate and check the data in each table using natural keys.
- Use SQLite3 CLI to check the integrity of version_db.
The schemas generated satisfy [R1].
Use SQLite3 CLI to complete [R2].
[R3] is the TCL automation script. You are correct in "simple execute
the sql ddl against the db".
The TCL script should also complete [R4].
[R5] To quote the mailing list "PRAGMA user_version is intended for this
very purpose."
I added a further comment of explanation below that does not disturb the
flow.
I would be interested in what installer and how you install in [R2].
Good luck
- Gary Gabriel
Explanation [R1]. Originally the script queried sqlite_master for the
metadata to generate the schema. Avoiding using the CLI would decrease
dependencies. However the query generated differently formatted schemas
for the two dbs. They were not exactly the same and required further
processing. The CLI generates matching schemas for a good diff.
[R4] INSERT SELECT. It took some work to find SQL INSERT SELECT
statements to populate the tables that would also validate.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
