Re: [Xen-devel] [OSSTEST PATCH 5/7] Schema: Support database schema updates

[Ian Campbell <ian.campbell@xxxxxxxxxx>]

On Thu, 2015-12-10 at 13:51 +0000, Ian Jackson wrote:
> See schema/README.schema, introduced in this patch, for the design.

That is all I have done here. Some of the questions I had might be answered
in the code.

[...]
> diff --git a/schema/README.updates b/schema/README.updates
> new file mode 100644
> index 0000000..0aa349d
> --- /dev/null
> +++ b/schema/README.updates
> @@ -0,0 +1,122 @@
> +SCHEMA DEFINITION AND SCHEMA UPDATES (PRODUCTION `EXECUTIVE' MODE)
> +==================================================================
> +
> +To generate a new DB, we apply the original schema (in initial.sql)
> +and then apply all the updates, in order.
> +
> +We maintain a table in the DB which records which updates are applied.
> +
> +
> +Schema update snippet format
> +----------------------------
> +
> +Schema update snippets should be called
> +ÂÂÂschema/<updatename>.sql
> +
> +They should contain DDL commands (ALTER TABLE etc.) to make whatever
> +changes are needed.
> +
> +They MUST NOT contain BEGIN or COMMIT.
> +
> +They must contain a special comment near the top:
> +
> +ÂÂ-- ##OSSTEST## <sequence> <status>
> +
> +<updatename> is a string (/^[a-z][0-9a-z-]+$/) which uniquely identifies
> +the update.ÂÂIt must not be changed because existing installations
> +rely on updates having stable names.
> +
> +<sequence> is a positive integer, which should be unique.ÂÂUpdates are
> +applied in order.
> +
> +<status> reflects the compatibility of various schema versions, see
> +below.
> +
> +
> +Update orders
> +-------------
> +
> +There are three reasonable plans for schema changes:

You've listed 4, which one is unreasonable then ;-)

> +
> + * Fully intercompatible: both old code and new code are each
> +ÂÂÂcompatible with both old schema and new schema.ÂÂThe code and
> +ÂÂÂschema updates may be done in any order.
> +
> +ÂÂÂSuch a schema change is always in state:
> +ÂÂÂÂÂÂHarmless
> +
> + * Explicit conditional: first update the code to understand both
> +ÂÂÂversions of the schema; then update the schema; then drop the
> +ÂÂÂcompatibility code.
> +
> +ÂÂÂSuch a schema change is in state:
> +ÂÂÂÂÂÂUnfinished (or absent)ÂÂÂin old code
> +ÂÂÂÂÂÂReadyÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂin intermediate code
> +ÂÂÂÂÂÂNeededÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂin the final code
> +
> + * Code first: the new code works with either old or new schema,
> +ÂÂÂbut the old code cannot cope with the new schema.
> +
> +ÂÂÂSuch a schema change is in state:
> +ÂÂÂÂÂÂUnfinished (or absent)ÂÂÂin old code
> +ÂÂÂÂÂÂReady'ÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂin new code

Is this really "Ready-prime" or is that a typo?

The "Unfinished", "Ready", "Needed" etc are the literal strings to be used
as <status> in the special comment, correct?

Who or what is responsible for cranking the handle on the state machine? I
_think_ it is going to be the commits which make the corresponding changes
to the code or introduce the new schema snippet?

Am I right that it would be unusual to have a literal "Unfinished" in a
schema/*.sql, but rather they would be implicitly in that state by being
absent?

> +
> + * Schema first: the new schema works with any code; but the old
> +ÂÂÂschema does not work with new code.
> +
> +ÂÂÂSuch a schema change is in state
> +ÂÂÂÂÂÂPreparatoryÂÂÂÂÂÂÂÂÂÂÂÂÂÂin old code
> +ÂÂÂÂÂÂNeededÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂÂin the new code
> +
> +
> +Update order for Populate-then-rely
> +-----------------------------------
> +
> +This is for when we want to record new information and then later rely
> +on it.ÂÂThere are typically two schema changes: to add the column(s)
> +(`add') and then to add appropriate constraints (`constraint') to
> +prevent it being left blank.

Which of the 4 plans above does this correspond to?

I have a feeling that this actually encapsulates two schema changes in
lockstep, which may have independent determination of the plan, from the
state names it looks like the `add' schema is a "Schema first" change and
the `constraint' one is either "Code first" or "Explicit conditional".

> +1. Commit: new schema update `add', in state Preparatory.

Does "Commit" mean simple push to pretest or does it have to pass through
push gate too?Â

> +1. Commit: new schema update `constraint', in state Unfinished.

The two instances of "1." are because these can happen in parallel?

> +
> +2. Apply: `add'.

This is something which automated machinery does I think?

> +
> +2. Commit: code to populate new column; changing `add' to state
> +ÂÂÂNeeded and `constraint' to state Ready.
> +
> +3. Optionally commit: idempotent utility script to populate missing
> +ÂÂÂdata.ÂÂ(This can be done with DML in the `constraint' update.)
> +
> +3. Wait for all executions of old code to finish.

this is automated also?

Also the two "3." happen in parallel? I'm not sure because "Commit" and
"Wait" in parallel seems racy.

> +5. Apply: `constraint'.

Does `constraint' go to `Needed' at this point, or does that depend on the
nature of the change (and therefore whether it is "Code first" or "Explicit
conditional"?

> +
> +
> +States and rules for push and db update
> +---------------------------------------
> +
> +ÂÂHarmless
> +ÂÂPreparatory
> +ÂÂÂÂÂNo restrictions
> +
> +ÂÂUnfinished
> +ÂÂ(sql fragment entirely missing is equivalent to Unfinished)
> +ÂÂÂÂÂSchema update: prevented
> +ÂÂÂÂÂCode push: unrestricted
> +
> +ÂÂReady
> +ÂÂÂÂÂSchema update: need all live code to be Preparatory/Ready/Needed
> +ÂÂÂÂÂCode push: unrestricted
> +
> +ÂÂNeeded
> +ÂÂÂÂÂSchema update: need all live code to be Preparatory/Ready/Needed
> +ÂÂÂÂÂCode push: depends on schema update
> +
> +
> +"Push depends on schema update" is not currently implemented.
> +
> +"Checks for live old code" means

These two phrases don't existing in this document.

I think "Push depends on schema update" was called "Schema first" further
up?

>  to look for the state of this schema
> +update in other running versions of osstest.ÂÂThis is not currently
> +implemented.
> diff --git a/schema/schema-updates.sql b/schema/schema-updates.sql
> new file mode 100644
> index 0000000..cd8dc0c
> --- /dev/null
> +++ b/schema/schema-updates.sql
> @@ -0,0 +1,6 @@
> +-- ##OSSTEST## 001 Harmless
> +
> +CREATE TABLE schema_updates (
> +ÂÂÂÂupdatename TEXT PRIMARY KEY,
> +ÂÂÂÂapplytime integer NOT NULL
> +);

_______________________________________________
Xen-devel mailing list
Xen-devel@xxxxxxxxxxxxx
http://lists.xen.org/xen-devel