Database Archive - woveon/wovtools GitHub Wiki

include ref=HeaderPageArchive

The Database Archive's purpose is to watch for a database's schema changes (NOT DATA) that need to be probagated when you deploy (this is a difficult task since it is hard to know what is a future vs past version).

Definition: Database : A database is named in a project, generally as 'PROJECTNAMEdb'. Currently only Postgres is supported. The database configuration is given its own root-level key in the configuration process (so a PROJECTNAMEdb.json file will exist in the secrets directory, generally with PROJECTNAMEdb_ME.json with developer login/password specifc settings).

Approach: WovTools tracks only differences in database schema via comparing SHA-256 sums of pg_dump (and pg_dump has different outputs depending on Postgres versions). It stores the schema checksums by WovTools project versions (PVER). It stores database deltas as files in the Database Archive, by PVER, and it stores inheritance, so these deltas can be applied across multiple changes (i.e. starting with the root, then applying each delta file). A running database has a 'wovtool' database with stores version number and which deltas have been applied.

Future Work:

  • Support more databases.
  • account for Postgres database version differences.
  • automate placing of database schema changes into the deltas files (this is a major project unto itself)
  • fix automatic application of delta files
  • stability and bug fixes... it isn't perfect

Usage

Development: When database changes are applied, append the SQL into a file wovtools/db/DATABASE.deltas. Archival: The database archive is checked when wov-push-db is called. If checksums do not match, it signals an error, unless there is a file wovtools/db/DBNAME.deltas. The assumption is the deltas file, in SQL format, contains the schema changes that occurred on the local database. Archive will update the version history of the database and store a schema and schema checksum. Deployment: (Doen't work well) This will select the stage's database version (in wovtools database) and apply each delta file from oldest to newest.

Errata

The DB Archive has a DATABASE.json file which stores version history and a DATABASE directory which stores schemas for comparing against.