database version control without pain - the phpnw10 version
Post on 29-Nov-2014
5.127 Views
Preview:
DESCRIPTION
TRANSCRIPT
PHPNW10 - Manchester - 9th October 2010Harrie Verveer
Database Version Control Without Pain
zaterdag 9 oktober 2010
Oh hai!
2
Harrie Verveeraka Hairy
DutchyI live in Goes, which probably sounds awkward to you
Software engineer at ibuildings since 2004Vlissingen (Flushing) office
zaterdag 9 oktober 2010
zaterdag 9 oktober 2010
zaterdag 9 oktober 2010
zaterdag 9 oktober 2010
zaterdag 9 oktober 2010
zaterdag 9 oktober 2010
zaterdag 9 oktober 2010
“Yeah, don’t get bogged down in trying to be funny. Be yourself and present the info.”
zaterdag 9 oktober 2010
9
Sorry, this talk won’t be funny
zaterdag 9 oktober 2010
Database Version ControlWhat’s the problem?
10zaterdag 9 oktober 2010
http://www.sxc.hu/photo/1207911
zaterdag 9 oktober 2010
http
://en
.wik
iped
ia.o
rg/w
iki/F
ile:S
TS1
20La
unch
HiR
es.jp
g
zaterdag 9 oktober 2010
http://www.flickr.com/photos/kt/1217157/
zaterdag 9 oktober 2010
http://www.sxc.hu/photo/1255121
zaterdag 9 oktober 2010
Why Database Version Control
15zaterdag 9 oktober 2010
Simple Patching Strategy
16
The basic idea
zaterdag 9 oktober 2010
Simple patching strategy
17zaterdag 9 oktober 2010
Example patch file
18
patch-001.sql
zaterdag 9 oktober 2010
http://www.sxc.hu/photo/314594
zaterdag 9 oktober 2010
Simple patching strategy
20
Development
Write codeWrite DB patches
Apply patches to own DB
Update
Apply patches Commit
zaterdag 9 oktober 2010
Fixing patches
21
patch-002.sql
zaterdag 9 oktober 2010
Fixing patches
21
patch-002.sql
zaterdag 9 oktober 2010
Fixing patches
21
patch-002.sql
zaterdag 9 oktober 2010
Fixing patches
22
patch-003.sql
zaterdag 9 oktober 2010
Fixing patches
22
patch-003.sql
Never modify a patch file onceit’s under version control
zaterdag 9 oktober 2010
Undo patch files
23
undo-001.sql
zaterdag 9 oktober 2010
http://www.freefoto.com/preview/41-07-7zaterdag 9 oktober 2010
Bonus points
Install.sql
25zaterdag 9 oktober 2010
Bonus points
Initial dummy content
26zaterdag 9 oktober 2010
http://www.sxc.hu/photo/643214
zaterdag 9 oktober 2010
http://www.sxc.hu/photo/946855
zaterdag 9 oktober 2010
Automating the processFaster & Safer
29zaterdag 9 oktober 2010
Typical patch script
30zaterdag 9 oktober 2010
Typical patch script
31
update.sh
zaterdag 9 oktober 2010
BranchesMerge problems
32zaterdag 9 oktober 2010
Branches
33
Trunk
patch-001.sql
patch-002.sql
patch-003.sql
zaterdag 9 oktober 2010
Trunk
patch-001.sql
patch-002.sql
patch-003.sql
create branch
patch-004.sql
patch-005.sqlpatch-004.sql
patch-005.sql
Branch A
Merge
Branches
34zaterdag 9 oktober 2010
Trunk
patch-001.sql
patch-002.sql
patch-003.sql
create branch
patch-004.sql
patch-005.sqlpatch-004.sql
patch-005.sql
Branch A
Merge
Branches
34
patch-006.sql
patch-007.sql
zaterdag 9 oktober 2010
Branches
35
Trunk
patch-001.sql
patch-002.sql
patch-003.sql
create branch
patch-004.sql
patch-005.sql
patch-004.sql
patch-005.sql
Branch B
create branch
patch-002.sql
patch-003.sql
Branch A
Merge
patch-004.sql
Merge
patch-005.sql
Merge
Merge
zaterdag 9 oktober 2010
Separate sequence
db/patches/trunk/
patch-001.sqlpatch-002.sqlpatch-003.sql
36
db/patches/branchA/
patch-001.sqlpatch-002.sql
zaterdag 9 oktober 2010
Separate sequence
db/patches/trunk/
patch-001.sqlpatch-002.sqlpatch-003.sql
36
db/patches/branchA/
patch-001.sqlpatch-002.sql
Trunk version 3BranchA version 2
zaterdag 9 oktober 2010
Patchfile naming
37
patch_20100611_1430.sql
zaterdag 9 oktober 2010
Patch log
38zaterdag 9 oktober 2010
http://www.sxc.hu/photo/692740
zaterdag 9 oktober 2010
There is no silver bulletWhy it can’t exist
40zaterdag 9 oktober 2010
http://www.flickr.com/photos/eschipul/4160817135/
zaterdag 9 oktober 2010
Comparing tables
42
Table in DB 1Table in DB 1
column 1 int
column 2 varchar(255)
column 3 varchar(255)
Table in DB 2Table in DB 2
column 1 int
column 2 varchar(255)
column 4 varchar(255)
zaterdag 9 oktober 2010
Comparing tables
43
Table in DB 1Table in DB 1
id int
name varchar(255)
city varchar(255)
Table in DB 2Table in DB 2
id int
name varchar(255)
website varchar(255)
zaterdag 9 oktober 2010
Comparing tables
44
Table in DB 1Table in DB 1
id int
firstname varchar(255)
lname varchar(255)
Table in DB 2Table in DB 2
id int
firstname varchar(255)
lastname varchar(255)
zaterdag 9 oktober 2010
45
To synchronize two database structures without unexpected
dataloss, communicating all steps needed to get from A to B is
inevitable.
zaterdag 9 oktober 2010
Phing & DB Deploy
46zaterdag 9 oktober 2010
Phing & DB Deploy
47Cartoon by Oliver Widder - http://geekandpoke.typepad.com/zaterdag 9 oktober 2010
Phing
49zaterdag 9 oktober 2010
50zaterdag 9 oktober 2010
DB Deploy
51zaterdag 9 oktober 2010
Phing & DB Deploy
52zaterdag 9 oktober 2010
DB Deploy
53
db/patches/1-create_user_table.sql
zaterdag 9 oktober 2010
DB Deploy
54
changelog table:
zaterdag 9 oktober 2010
DB Deploy
54
changelog table:
zaterdag 9 oktober 2010
DB Deploy
55
PHP
zaterdag 9 oktober 2010
DB Deploy
56
SQL
zaterdag 9 oktober 2010
DB Deploy
57
Features
zaterdag 9 oktober 2010
LiquibaseThe most complete solution I know
58zaterdag 9 oktober 2010
Liquibase
59
www.liquibase.org
zaterdag 9 oktober 2010
Liquibase
60zaterdag 9 oktober 2010
Liquibase
61zaterdag 9 oktober 2010
Liquibase
62zaterdag 9 oktober 2010
Liquibase
Updating
Reverting
Tagging
Generate XML
Diff
63zaterdag 9 oktober 2010
http://www.sxc.hu/photo/1241520
zaterdag 9 oktober 2010
Liquibase
Branches
65zaterdag 9 oktober 2010
Liquibase
DBMS Support
66zaterdag 9 oktober 2010
Liquibase
Documentation
67zaterdag 9 oktober 2010
Liquibase
Java
68zaterdag 9 oktober 2010
Akrabat DB Schema ManagerZend Framework specific solution
69zaterdag 9 oktober 2010
Akrabat DB Schema Manager
Zend Framework
Proposed by Rob Allen in 2006
Recently implemented
Might be in ZF in the near future
http://github.com/akrabat/Akrabat
70zaterdag 9 oktober 2010
Akrabat DB Schema Manager
Zend Framework
Proposed by Rob Allen in 2006
Recently implemented
Might be in ZF in the near future
Might not be in ZF in the near future as well
http://github.com/akrabat/Akrabat
71zaterdag 9 oktober 2010
Writing the patch
72
scripts/migrations/001-CreateUserTable.php
zaterdag 9 oktober 2010
Writing the patch
73
scripts/migrations/001-CreateUserTable.php
zaterdag 9 oktober 2010
Akrabat DB Schema Manager
PHP Patches
74zaterdag 9 oktober 2010
Akrabat DB Schema Manager
Easy syntax if you know ZF
75zaterdag 9 oktober 2010
Akrabat DB Schema Manager
Easy to install, easy to use
76zaterdag 9 oktober 2010
Akrabat DB Schema Manager
Easy to install, easy to use
76zaterdag 9 oktober 2010
Akrabat DB Schema Manager
Branch merging
77zaterdag 9 oktober 2010
Doctrine MigrationsDoctrine ORM specific solution
78zaterdag 9 oktober 2010
Doctrine Migrations
79
http://www.doctrine-project.org/
zaterdag 9 oktober 2010
Doctrine Migrations Patch
80zaterdag 9 oktober 2010
Doctrine Migrations
81
Update-to-version
./doctrine migrate
zaterdag 9 oktober 2010
YAML
82zaterdag 9 oktober 2010
YAML
82
./doctrine generate-migrations-diff
zaterdag 9 oktober 2010
Doctrine Migrations
83zaterdag 9 oktober 2010
Doctrine Migrations
84
Generate patches
zaterdag 9 oktober 2010
Doctrine Migrations
85
Useful if you’re already using Doctrine ORM
zaterdag 9 oktober 2010
Summary
86zaterdag 9 oktober 2010
Summary
Patchfiles• Automating
• Branch merging
• Why there is no silver bullet
Phing + DB Deployhttp://phing.infohttp://dbdeploy.com
Liquibasehttp://www.liquibase.org
87
Akrabat DB Schema Managerhttp://akrabat.comhttp://github.com/akrabat/Akrabat
Doctrine Migrationshttp://www.doctrine-project.org
zaterdag 9 oktober 2010
Questions ?
88zaterdag 9 oktober 2010
89
Contact me
Harrie VerveerSoftware Engineer at Ibuildings
E-mail:harrie@ibuildings.nl
Skype:harrie-ibuildings
Twitter:@harrieverveer
http://joind.in/2056
zaterdag 9 oktober 2010
top related