Elegant database modifications

Edmund von der Burgevdb@ecclestoad.co.uk

Database changes are hard

We need to make them all the time

Let's make them easy

Disclaimer

The opinions presented in this talk are those of the presenter only. They are based on his limited experience. There are no absolutes in this talk.

Your own experiences may differ.

Please interrupt with questions,corrections and suggestions.

For far too long we have been throwing SQL scripts at standalone binary clients,

it's about time we had some way to throw them at the DBI instead.

Adam Kennedy - SQL::Script

Problems

The database is at the bottom of the stack

View

Legacy codeController Optimisations

ORM

Database & other persistent storage

Web App Hierarchy

Keeping track of changes during

development is hard

No way to refer to schema versions

• SQL standard is not helpful

• Need a standard place to store metadata

• Needs to be easy to query

Can't specify the schema needed

in the code

The data needs to change too

create table locations ( postcode varchar(8) not null

);

,latitude float ,longitude float

"ALTER TABLE"to add the

lat & long fields

run code to populate lat& long from postcode

not nullnot null

"ALTER TABLE" to make lat & long"NOT NULL"

All databasesare different

• PostgreSQL can do 'alter table' in a transaction.

• MySQL can alter several columns in one statement.

• etc, etc...

I don't know why thischange was made

• The schema in the database does not contain a history

• No clear place to put documentation on the schema

• Changes to the schema are not stored in version control - so don't appear alongside code changes in diffs

Developers arenot sys-admins

Changing the schema might lock the database

Deploying changes to live systems involves

other steps

• Display a "down for maintenance" message or switch to "read only" mode.

• Backup everything before starting

• Switch off import & backup systems

• Disable alerting

• Upgrade codebase / commision new virtual servers.

• Switch to new systems when complete

FEAR"Don't change the database"

"I think that the import system needs

that column"

"Just put a hack in the code to fix that"

"Email that change to Bob and he'll apply it during the maintenance window"

"Did that change get applied?"

"We can't really test it without going live"

Solutions!

Surely the answer is on CPAN?

DBIx::Class::Schema::Versioned

DBIx::Migration

DBIx::Migration::Directories

DBIx::SchemaView

DBIx::VersionedSchema

Jifty::DBI::Schema

Yes!

No!

None deal with modifying data in code

Can't specify schema version in the code

Too smart - try do work out the SQL for you

Too dumb - don't give you the control you need

ORMs can generate the SQL statements, but should not be applying them

Add metadata to the schema

create table __metadata ( name varchar(100) primary key, value text not null);

insert into __metadata (name, value) values ( 'schema_version', 0 );

insert into __metadata (name, value) values ( 'your_key', 'your_value');

Specify the schema version in the code

use MyApp::Schemas Main => 72, TheSchwartz => 3, OtherSchema => 12;

A module in your namespace that contains all the schema info

The various schemas you've defined

The versions you require for this code

Create a framework for applying changes

F*******k !

Sample code

Sample code

Sample code

Sample code

Sample code

HierarchyMyApp::SchemasMyApp::Schemas::MainMyApp::Schemas::Main::v001MyApp::Schemas::Main::V002

• Not happy with the v00x naming

• Each level does inherits from the level above

• Bottom level inherits from module on CPAN

• Might call this DBIx::Schemas ?????

Hooks / Handlers:

• start / end of upgrade

• start / end of version jump

• start /end of each step in a version jump

• .... lots more .....

And also:

• Tests - embed them next to the changes they relate to

• Specify the steps to take, not make them

• Opens up potential for other tools to apply the steps

Make it easy for developers

• One clear place to put the schema/data changes.

• Enforce schema changes in code (could autoupgrade when developing)

• Put schema changes in version control

• Know what the schema should look like

Make it easy forsys-admins

• Changes to schema/data clearly defined

• Intention of each step is documented

• Can run the steps (on stage server) to see what happens on real data

• Can add in hooks to ease the upgrade

• Whatever they want.......

Questions?Opinions?

Suggestions?