elegant database modifications
Post on 29-Jun-2015
1.474 Views
Preview:
DESCRIPTION
TRANSCRIPT
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?
top related