• Immutable Page
• Info
• Attachments
• More Actions: Raw Text Print View Render as Docbook Delete Cache ------------------------ Check Spelling Like Pages Local Site Map ------------------------ Rename Page Delete Page ------------------------ Subscribe User ------------------------ Remove Spam Revert to this revision Package Pages Sync Pages ------------------------ Load Save SlideShow

• Process Name: Database Schema Changes

• Process Owner: Kiko

• Parent Process/Activity: None

Process Overview

Process Description

This process explains how to create and submit Database Schema Changes.

Rationale

• PQM runs the launchpad test suite
• The Launchpad test suite runs against sample data
• However, there are problems that can occur with schema changes or data update scripts, running against production data, which do not occur running them against staging data. Examples are:
  • The update does not run correctly because of integrity errors.
  • The update takes a very long time to run.
• So, we test new database schemas (patch levels above production) out on the staging server, which uses a daily-updated copy of the production database.
  1. We take a copy of the production database, then run whatever schema changes and updates are needed to bring it up to the patch level currently on mainline.
  2. Then we run mainline code against that new staging database.
• The fact that staging is actually running, and that it got updated in a reasonable amount of time, shows that an update from production to the code in mainline is actually possible. The output from the staging update process shows the time it took to process each database patch.
• Running on staging still doesn't catch all the errors we may find when actually doing a production database schema update; although the data will be the same, the organisation within the database may be different. For example, tables may need vacuuming, or be stored in a way that causes scripts to take a long time to run when they did not take so long when run on the same data on staging. This is because when we move the data from production to staging, we're moving just the data, not the exact internal arrangement of data in the database.

Triggers

1. Developer needs a database schema change implemented as part of his/her development activities
2. Developer needs a database schema change implemented as part of his/her bug fixing activities

Inputs

1. Developer originated schema change concept

Outputs

1. SQL Patch File

Participants

1. Developer
2. DBA (in our case, Stuart)

Subprocesses

• N/A.

Standard Path Events/Activities

1. When a developer has a change they want to make to the database schema, they write a database patch (see database/schema in the source tree).
2. They can give themselves a provisional patch number on their development tree.

3. When they're sure about what they want, they should add their branch details to the DBA section of PendingReviews to have their database patch approved. This works a bit like a code review, but just on the database SQL patch (including comments).

4. When it has passed review, Stuart gives them a database patch number, which becomes the filename of the SQL.

5. Stuart (or the acting DBA) will also issue further instuctions on how to procede. This generally involves adding the branch details to PendingDatabasePatches, where they will be landed in a batch, or to land the branch immediately to rocketfuel/launchpad/devel.

Notes:

• Because each database patch number is unique, various database schema changes can be worked on by different members of the team in parallel, and successfully merged together in different ways.
• This is most often used just for schema changes.
• Sometimes schema changes have accompanying data updates.
• See supporting documentation section for Stuart's official documentation

Alternative Path Events/Activities

• If code changes need to land in tandem with a database patch, these needs to be discussed on a case by case basis with StuartBishop.

Supporting Documentation

• DatabaseSchemaChanges - schema change HOWTO

Comments