Updating the Database
When a new release of the Rule Repository, Audit or the MCP server needs changes to the database (a new table, a new column, a new index), it ships them as numbered update scripts. You run them yourself, in order, before or together with deploying the new libraries. This page explains how to tell which scripts a database needs, how to run them safely, and what the libraries do when a database is behind.
You need this because the libraries never change the database schema themselves. They have no "migrate" or "upgrade" method and never run a script. Every change to your database goes through your own hands, your own change process and your own deployment account, so nothing surprises your DBAs.
How Versions Work
- Every script is named
NNNNN.Name.sql: a five-digit number, then a short name, such as 00002.EvaluationArchive.sql (an illustration of the format, not a real script). 00001.Database.sql creates the database; every later script changes it.
- Each script records itself as its last statement: a row in
SettingRecord with SettingName Schema.NNNNN (the script's number) and SettingValue the script's file name and the release that shipped it, such as 00001.Database.sql; 7.0. CreatedOn says when it ran.
- The database's schema version is the highest number among its
Schema. rows.
- Each library declares the version it needs.
Context.CheckDatabase (Repository) and AuditRecorder.CheckDatabase (Audit) compare the two and tell you what to run.
- A later schema is fine. Within a major version, schema changes only add (new tables, new nullable columns, new indexes), so an older library keeps working on a newer database. Update the database first, then the application, and you can roll the application back without touching the database.
- Never delete the
Schema. rows. They are how the libraries and you know where the database stands.
Finding Out Which Scripts to Run
Ask the database:
-- SQL Server
SELECT SettingName, SettingValue, CreatedOn FROM dbo.SettingRecord WHERE SettingName LIKE 'Schema.%' ORDER BY SettingName;
-- PostgreSQL
SELECT settingname, settingvalue, createdon FROM settingrecord WHERE settingname LIKE 'Schema.%' ORDER BY settingname;
Or ask the libraries, which is what your application does at startup:
var repository = Context.CheckDatabase(db);
var audit = AuditRecorder.CheckDatabase(db); // only if you use Audit
foreach (var check in new[] { repository, audit })
if (!check.IsCurrent) Console.WriteLine(check.Message);
When the database is behind, Message names the scripts: for example, "The Rule Repository database needs an update: it is at schema version 1 and this version of the Rule Repository needs 3. Run the update scripts 00002..sql to 00003..sql from the Code Effects portal, in order."
Run every script numbered after the database's current version, up to the highest number in the release you are installing, even if one of them was shipped for a library you do not use. The numbering is shared by all the libraries.
Getting the Update Scripts
Update scripts are delivered through the Code Effects Portal, in a zip archive per database type (SQL Server or PostgreSQL), with each release that needs them. Keep the archives with your deployment records: they are the history of your schema.
Running Them
Treat an update like any other schema change in your organization:
Read the scripts. They are plain SQL, with comments saying what each change is for.
Back up the database, or take a snapshot, first.
Plan the window. Most scripts only add and run in seconds. A script that adds an index to a large table (for example, the evaluations table of a busy Audit installation) can take longer and may lock the table while it runs, so read each script's comments and time it on a copy of your production data first. Audit keeps recording into its local journal while the table is locked and moves the records in afterwards.
Run the scripts in number order, one at a time, with your deployment account, stopping at the first error:
sqlcmd -S your-server -d CodeEffectsRules -E -b -i 00002.Name.sql
sqlcmd -S your-server -d CodeEffectsRules -E -b -i 00003.Name.sql
psql -h your-server -U your-admin -d codeeffects_rules -v ON_ERROR_STOP=1 -f 00002.Name.sql
psql -h your-server -U your-admin -d codeeffects_rules -v ON_ERROR_STOP=1 -f 00003.Name.sql
Check that a Schema.NNNNN row exists for every script you ran.
Grant your application account rights on any table a script added, if your grants are per table. On PostgreSQL, default privileges set for the deployment account cover new tables automatically (see The Database).
Deploy the new libraries. At startup, CheckDatabase now reports the database as current.
If a script fails part way, restore the backup (or fix the cause and finish the script by hand, if your DBA prefers), and run it again. Do not write its Schema. row by hand unless every statement in it has run.
If you changed the schema name when you created the database (see The Database), make the same change in every update script before you run it.
In a deployment pipeline
Many teams put the scripts in source control beside the application and let the pipeline run the ones the database has not seen. Read the highest Schema. number, run every script with a higher number in order, then deploy the application. Tools such as DbUp, Flyway, Liquibase or SQL Server Data Tools pipelines can run the files as they are; keep the scripts' own Schema. rows, which the libraries read, even if your tool keeps a journal of its own.
When the Database Is Behind
If the application starts on a database that is behind and you did not stop it at the schema check:
- Requests that touch only unchanged tables keep working.
- The first statement that names a missing table or column fails with control number CE19004. Its message says the database needs an update and names the scripts to run, in place of the database's own "invalid object name" error, which is kept as the inner exception.
- Nothing is changed or lost; run the scripts and the same request succeeds.
A host that writes its own database dialect should report missing tables and columns as DbFailureKind.MissingObject, or this diagnosis cannot happen. See Connecting to the Database.
Rolling Back
Because update scripts only add within a major version, rolling the application back to an earlier release needs no database change: the earlier libraries ignore what they do not know. Rolling the database back means restoring the backup you took before the update; there are no "down" scripts.