Agile Database Development 2ed

Instructions
https:/
/github.com/theory/agile-ﬂipr

View Slide

Agile
Database Development
David E. Wheeler
!
!
!
January 2014
Portland
Text: Attribution-Noncommercial-Share Alike 3.0 United States:
http:/
/creativecommons.org/licenses/by-nc-sa/3.0/us/
Images licensed independently and © Their respective owners.
iovation

View Slide

Agile
David E. Wheeler
!
!
!
January 2014
Portland
http:/
✘
iovation

View Slide

David E. Wheeler
!
!
!
January 2014
Portland
http:/

View Slide

David E. Wheeler
!
!
!
January 2014
Portland
License: Attribution-Noncommercial-Share Alike 3.0 United
States: http:/

View Slide

Build My VC-Funded App
for Me for Free
David E. Wheeler
!
!
!
January 2014
Portland
License: Attribution-Noncommercial-Share Alike 3.0 United
States: http:/
✔
CEO, Data Architect
Agile Assholes, Inc.

View Slide

This is Genius

View Slide

This is Genius
I had this idea

View Slide

This is Genius
I had this idea
Social networking is HAWT

View Slide

This is Genius
I had this idea
Has been for waaaay too long

View Slide

This is Genius
I had this idea
The backlash is long overdue

View Slide

This is Genius
I had this idea
Getting ahead of the curve

View Slide

This is Genius
I had this idea
Getting ahead of the curve
Introducing…

View Slide

http:/
/ﬂic.kr/p/8j5gG8 © 2010 Strongrrl. All rights reserved. Used with permission.

View Slide

antisocial network
http:/
/ﬂic.kr/p/8j5gG8 © 2010 Strongrrl. All rights reserved. Used with permission.

View Slide

How it Works
antisocial network

View Slide

How it Works
Microblogging platform
antisocial network

View Slide

How it Works
Everyone follows you
antisocial network

View Slide

How it Works
New users follow everyone
antisocial network

View Slide

How it Works
Goal: Alienate your followers
antisocial network

View Slide

How it Works
Get them to unfollow you
antisocial network

View Slide

How it Works
Get them to unfollow you
Leaderboard: Those with fewest followers
antisocial network

View Slide

Your Task
antisocial network

View Slide

Your Task
Create the database
antisocial network

View Slide

Your Task
Create the database
Use agile development to make it right
antisocial network

View Slide

Your Task
Create the database
Contribute to VC-funded Corp
antisocial network

View Slide

Your Task
Create the database
Proﬁt!
antisocial network

View Slide

Your Task
Create the database
Proﬁt!
For VC-funded Corp
antisocial network

View Slide

http:/
/ﬂic.kr/p/2honiQ © 2007 James Duncan Davidson. All rights reserved. Used with permission.

View Slide

But ﬁrst…
antisocial network

View Slide

NDA
antisocial network

View Slide

Job Requirements
antisocial network

View Slide

Job Requirements
PostgreSQL
Should be installed
antisocial network

View Slide

Job Requirements
PostgreSQL
Should be installed
Git
Should be installed
antisocial network

View Slide

Job Requirements
PostgreSQL
Should be installed
Git
Should be installed
Sqitch
sudo yum install sqitch-pg
antisocial network

View Slide

Job Requirements
PostgreSQL
Should be installed
Git
Should be installed
Sqitch
pgTAP
sudo yum install pgtap92
antisocial network

View Slide

Job Requirements
PostgreSQL
Should be installed
Git
Should be installed
Sqitch
pgTAP
sudo yum install pgtap92
pg_prove
yum install perl-TAP-Parser-SourceHandler-pgTAP
antisocial network

View Slide

We good?
antisocial network

View Slide

Let’s do this thang.
antisocial network

View Slide

>
Who Am I?

View Slide

git config -‐-‐global user.name 'David E. Wheeler'
>
>
Who Am I?

View Slide

> git config -‐-‐global user.email [email protected]
>
>
Who Am I?

View Slide

>
>
Who Am I?
Please use
your own name
and email.

View Slide

>
>
Who Am I?
emacs ~/.gitconfig
>

View Slide

~/.gitconfig
~/
.gitconﬁg
[user]
name = David E. Wheeler
email = [email protected]

View Slide

~/.gitconfig
~/
.gitconﬁg
[user]
Good for
all projects

View Slide

Create a Remote

View Slide

Create a Remote
Create Git project in Stash

View Slide

Create a Remote
Or Git or BitBucket

View Slide

Create a Remote
Or Git or BitBucket
Wherever you like

View Slide

Create a Remote
Or Git or BitBucket
Wherever you like
Record remote URL

View Slide

Gitiup
>

View Slide

mkdir flipr-‐db
> cd flipr-‐db
> git init
Initialized empty Git repository in flipr-‐db/.git/
>
Gitiup
>

View Slide

mkdir flipr-‐db
> cd flipr-‐db
> git init
>
Gitiup
>
echo Flipr Database Project > README.md
>

View Slide

mkdir flipr-‐db
> cd flipr-‐db
> git init
>
Gitiup
>
> git add .
>

View Slide

mkdir flipr-‐db
> cd flipr-‐db
> git init
>
Gitiup
>
> git add .
> git commit -‐m 'Initialize repo, add README.'
[master (root-‐commit) 9531fab] Initialize repo, add README.
1 file changed, 1 insertion(+)
create mode 100644 README.md
>

View Slide

mkdir flipr-‐db
> cd flipr-‐db
> git init
>
Gitiup
>
> git add .
> git commit -‐m 'Initialize repo, add README.'
[master (root-‐commit) 9531fab] Initialize repo, add README.
create mode 100644 README.md
>
We have a
Git repo.

View Slide

>
Origin

View Slide

git remote add origin ssh://[email protected]:7999
>
>
Origin

View Slide

git push -‐u origin master
Counting objects: 3, done.
Writing objects: 100% (3/3), 260 bytes, done.
Total 3 (delta 0), reused 0 (delta 0)
To https://github.com/theory/agile-‐flipr.git
* [new branch] master -‐> master
Branch master set up to track remote branch master from orig
>
git remote add origin ssh://[email protected]:7999
>
>
Origin

View Slide

Swimming Upstream
>

View Slide

git remote add upstream \
https://github.com/theory/agile-‐flipr.git
>
Swimming Upstream
>

View Slide

>
Swimming Upstream
>
git fetch upstream
From https://github.com/theory/agile-‐flipr
* [new branch] change_pass -‐> upstream/change_pass
* [new branch] flips -‐> upstream/flips
* [new branch] master -‐> upstream/master
* [new branch] userfuncs -‐> upstream/userfuncs
* [new branch] users -‐> upstream/users
* [new tag] insert_user2 -‐> insert_user2
* [new tag] reltag -‐> reltag
* [new tag] v1.0.0-‐r1 -‐> v1.0.0-‐r1
>

View Slide

>
Swimming Upstream
>
git fetch upstream
* [new branch] change_pass -‐> upstream/change_pass
* [new branch] flips -‐> upstream/flips
* [new branch] master -‐> upstream/master
* [new branch] userfuncs -‐> upstream/userfuncs
* [new branch] users -‐> upstream/users
* [new tag] insert_user2 -‐> insert_user2
* [new tag] reltag -‐> reltag
* [new tag] v1.0.0-‐r1 -‐> v1.0.0-‐r1
>
For
resetting
later

View Slide

Git?
antisocial network

View Slide

Git?
Manage tree of ﬁles over time
antisocial network

View Slide

Git?
Distributed development
antisocial network

View Slide

Git?
Commit changes locally
antisocial network

View Slide

Git?
Merge to remote (origin, upstream)
antisocial network

View Slide

Git?
Speedy, responsive
antisocial network

View Slide

Git?
Speedy, responsive
Flexible, robust
antisocial network

View Slide

Why Git?
antisocial network

View Slide

Why Git?
Anyone can clone
antisocial network

View Slide

Why Git?
Anyone can clone
Complete repository copy
antisocial network

View Slide

Why Git?
Anyone can clone
Cheap branching
antisocial network

View Slide

Why Git?
Anyone can clone
Cheap branching
Make and test local changes
antisocial network

View Slide

Why Git?
Anyone can clone
Cheap branching
Submit patches, pull requests
antisocial network

View Slide

Why Git?
Anyone can clone
Cheap branching
Submit patches, pull requests
Pull from upstream
antisocial network

View Slide

PiSHA1
antisocial network

View Slide

PiSHA1
SHA1 ID for every object
antisocial network

View Slide

PiSHA1
commit, tag, tree, blob
antisocial network

View Slide

PiSHA1
Hashed commit text includes:
antisocial network

View Slide

PiSHA1
tree ID
antisocial network

View Slide

PiSHA1
tree ID
parent ID
antisocial network

View Slide

PiSHA1
tree ID
parent ID
author
antisocial network

View Slide

PiSHA1
tree ID
parent ID
author
committer
antisocial network

View Slide

PiSHA1
tree ID
parent ID
author
committer
message
antisocial network

View Slide

>
Making a hash of it

View Slide

> git cat-‐file commit HEAD
tree 8b0955fd015782a26953e20d41db5fadbb347c14
parent 0f38581c4d19313d910c2080b3470cae07f3694e
author David Wheeler 1367971872 -‐0700
committer David Wheeler 1367971872 -‐0700
Get through "Ship it!".
>
Making a hash of it

View Slide

SHAzam!
antisocial network

View Slide

SHAzam!
Each commit (except ﬁrst) includes parent
antisocial network

View Slide

SHAzam!
Can trace from any commit to the beginning
antisocial network

View Slide

SHAzam!
Tampering (corruption) detectable
antisocial network

View Slide

SHAzam!
Because the hash will be wrong
antisocial network

View Slide

SHAzam!
Linus Torvalds’s “greatest invention”
http:/
/perl.plover.com/yak/git/
antisocial network

View Slide

antisocial network
Your Turn

View Slide

antisocial network
Your Turn
Conﬁgure Git

View Slide

antisocial network
Your Turn
Conﬁgure Git
Initialize repository

View Slide

antisocial network
Your Turn
Conﬁgure Git
Add origin remote

View Slide

antisocial network
Your Turn
Conﬁgure Git
Add origin remote
Add upstream remote

View Slide

antisocial network
Your Turn
Conﬁgure Git
Add origin remote
Add upstream remote
https:/
/github.com/
theory/agile-ﬂipr.git

View Slide

Who am I again?
>

View Slide

sqitch config -‐-‐user user.name 'David E. Wheeler'
>
Who am I again?
>

View Slide

>
sqitch config -‐-‐user user.email [email protected]
>
Who am I again?
>

View Slide

>
sqitch config -‐-‐user user.email [email protected]
>
>
Who am I again?
>
emacs ~/.sqitch/sqitch.conf
>

View Slide

~/.sqitch/sqitc
~/
.sqitch/sqitch.conf
[user]

View Slide

~/.sqitch/sqitc
~/
.sqitch/sqitch.conf
[user]
Good for
all projects

View Slide

>
Scratch that Sqitch

View Slide

> sqitch -‐-‐engine pg init flipr \
-‐-‐uri https://github.com/theory/agile-‐flipr
Created sqitch.conf
Created sqitch.plan
Created deploy/
Created revert/
Created verify/
>
>
Scratch that Sqitch

View Slide

Created sqitch.conf
Created sqitch.plan
Created deploy/
Created revert/
Created verify/
>
>
Scratch that Sqitch
Use remote URL

View Slide

Created sqitch.conf
Created sqitch.plan
Created deploy/
Created revert/
Created verify/
>
>
Scratch that Sqitch

View Slide

Created sqitch.conf
Created sqitch.plan
Created deploy/
Created revert/
Created verify/
>
>
Scratch that Sqitch
emacs sqitch.conf
>

View Slide

sqitch.conf
sqitch.conf
[core]
engine = pg
# plan_file = sqitch.plan
# top_dir = .
# deploy_dir = deploy
# revert_dir = revert
# verify_dir = verify
# extension = sql
# [core "pg"]
# target = db:pg:
# registry = sqitch
# client = psql

View Slide

sqitch.conf
sqitch.conf
[core]
engine = pg
# plan_file = sqitch.plan
# top_dir = .
# deploy_dir = deploy
# revert_dir = revert
# verify_dir = verify
# extension = sql
# [core "pg"]
# target = db:pg:
# registry = sqitch
# client = psql
--engine pg

View Slide

What’s the Plan Man?
>

View Slide

What’s the Plan Man?
emacs sqitch.plan
>
>

View Slide

sqitch.plan
sqitch.plan
%syntax-‐version=1.0.0-‐b1
%project=flipr
%uri=https://github.com/theory/agile-‐flipr

View Slide

sqitch.plan
sqitch.plan
%project=flipr
Identiﬁed

View Slide

Make It So
>

View Slide

Make It So
>
git add .
> git commit -‐m 'Initialize Sqitch configuration.'
[master e56e7c8] Initialize Sqitch configuration.
2 files changed, 16 insertions(+)
create mode 100644 sqitch.conf
create mode 100644 sqitch.plan
>

View Slide

Make It So
>
git add .
> git commit -‐m 'Initialize Sqitch configuration.'
[master e56e7c8] Initialize Sqitch configuration.
create mode 100644 sqitch.conf
create mode 100644 sqitch.plan
>
git push
Delta compression using up to 4 threads.
Compressing objects: 100% (4/4), done.
9531fab..e56e7c8 master -‐> master
>

View Slide

Where’ve We Been?
>

View Slide

git log
commit e56e7c88d85fa84e7f726df36bd0ae7a74a618aa
Author: David E. Wheeler
Date: Tue Jan 7 15:44:40 2014 -‐0800
Initialize Sqitch configuration.
commit 9531fab56ecd9abe77636045584d1dc1b74fbc7b
Author: David E. Wheeler
Date: Tue Jan 7 15:18:30 2014 -‐0800
Initialize repo, add README.
Where’ve We Been?
>

View Slide

First Change
>

View Slide

First Change
> sqitch add appschema -‐n 'Adds flipr app schema.'
Created deploy/appschema.sql
Created revert/appschema.sql
Created verify/appschema.sql
Added "appschema" to sqitch.plan
>
>

View Slide

First Change
>
>
emacs deploy/appschema.sql
>

View Slide

deploy/appschem
deploy/appschema.sql
-‐-‐ Deploy appschema
!
BEGIN;
!
!
!
COMMIT;
-‐-‐ XXX Add DDLs here.

View Slide

deploy/appschem
deploy/appschema.sql
-‐-‐ Deploy appschema
!
BEGIN;
!
!
!
COMMIT;
CREATE SCHEMA flipr;

View Slide

>
First Change
>
>

View Slide

>
First Change
>
>
emacs revert/appschema.sql
>

View Slide

revert/appschem
revert/appschema.sql
-‐-‐ Revert appschema
!
BEGIN;
!
!
!
COMMIT;

View Slide

revert/appschem
revert/appschema.sql
-‐-‐ Revert appschema
!
BEGIN;
!
!
!
COMMIT;
DROP SCHEMA flipr;

View Slide

Make it So!
>

View Slide

createuser -‐s -‐U postgres `whoami`
>
Make it So!
>

View Slide

createdb flipr_test
>
>
Make it So!
>

View Slide

createdb flipr_test
>
>
sqitch deploy db:pg:flipr_test
Adding registry tables to db:pg:flipr_test
Deploying to db:pg:flipr_test
+ appschema .. ok
>
Make it So!
>

View Slide

createdb flipr_test
>
>
+ appschema .. ok
>
Make it So!
psql -‐d flipr_test -‐c '\dn flipr'
List of schemas
Name | Owner
-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐
flipr | david
>
>

View Slide

createdb flipr_test
>
>
+ appschema .. ok
>
Make it So!
List of schemas
Name | Owner
-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐
flipr | david
>
Trust, but
verify
>

View Slide

createdb flipr_test
>
>
+ appschema .. ok
>
Make it So!
List of schemas
Name | Owner
-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐
flipr | david
>
emacs verify/appschema.sql
>

View Slide

-‐-‐ Verify appschema
!
BEGIN;
!
!
!
ROLLBACK;
verify/appschem
verify/appschema.sql
-‐-‐ XXX Add verifications here.

View Slide

!
BEGIN;
!
!
!
ROLLBACK;
SELECT pg_catalog.has_schema_privilege('nada', 'usage');
verify/appschem

View Slide

!
BEGIN;
!
!
!
ROLLBACK;
SELECT pg_catalog.has_schema_privilege('nada', 'usage');
verify/appschem
Let’s try
it, ﬁrst

View Slide

>
Trust, But Verify
>

View Slide

>
sqitch verify db:pg:flipr_test
Verifying db:pg:flipr_test
* appschema .. psql:verify/appschema.sql:5:
ERROR: schema "nada" does not exist
# Verify script "verify/appschema.sql" failed.
not ok
Verify Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Changes: 1
Errors: 1
Verify failed
>
Trust, But Verify
>

View Slide

Trust, But Verify
>

View Slide

perl -‐i -‐pe 's/nada/flipr/' verify/appschema.sql
>
Trust, But Verify
>

View Slide

sqitch verify db:pg:flipr_test
Verifying db:pg:flipr_test
* appschema .. ok
Verify successful
>
perl -‐i -‐pe 's/nada/flipr/' verify/appschema.sql
>
Trust, But Verify
>
Mo betta.

View Slide

>
How’s it Look?

View Slide

>
How’s it Look?
> sqitch status db:pg:flipr_test
# On database db:pg:flipr_test
# Project: flipr
# Change: e01feba4bb79607339a13981574855b6e2aa7526
# Name: appschema
# Deployed: 2014-‐01-‐07 16:07:22 -‐0800
# By: David E. Wheeler
#
Nothing to deploy (up-‐to-‐date)

View Slide

Go Back
>

View Slide

> sqitch revert db:pg:flipr_test
Revert all changes from db:pg:flipr_test? [Yes]
Go Back
>
▮

View Slide

Go Back
>
-‐ appschema .. ok
>

View Slide

Go Back
>
List of roles
List of schemas
Name | Owner
-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐
>
>

View Slide

Go Back
>
List of roles
List of schemas
Name | Owner
-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐
>
>
sqitch status db:pg:flipr_test
No changes deployed
>

View Slide

History
>

View Slide

History
>
> sqitch log db:pg:flipr_test
On database flipr_test
Revert e01feba4bb79607339a13981574855b6e2aa7526
Name: appschema
Committer: David E. Wheeler
Date: 2014-‐01-‐07 16:07:44 -‐0800
Adds flipr app schema.
Deploy e01feba4bb79607339a13981574855b6e2aa7526
Name: appschema
Committer: David E. Wheeler
Date: 2014-‐01-‐07 16:07:22 -‐0800
Adds flipr app schema.

View Slide

Commit It!
>

View Slide

Commit It!
>
git add .
> git commit -‐m 'Add flipr schema.'
[master 07548c2] Add flipr schema.
create mode 100644 deploy/appschema.sql
create mode 100644 revert/appschema.sql
create mode 100644 verify/appschema.sql
>

View Slide

Commit It!
>
git add .
> git commit -‐m 'Add flipr schema.'
[master 07548c2] Add flipr schema.
create mode 100644 deploy/appschema.sql
create mode 100644 revert/appschema.sql
create mode 100644 verify/appschema.sql
>
git push
e56e7c8..07548c2 master -‐> master

View Slide

Redeploy
>

View Slide

> sqitch deploy db:pg:flipr_test -‐-‐verify
Deploying changes to flipr_test
+ appschema .. ok
>
Redeploy
>

View Slide

+ appschema .. ok
>
Redeploy
>
Integrated!

View Slide

+ appschema .. ok
>
Redeploy
>

View Slide

+ appschema .. ok
>
Redeploy
>
List of schemas
Name | Owner
-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐
flipr | david
>

View Slide

Status Update
>

View Slide

> sqitch status db:pg:flipr_test
# Project: flipr
# Name: appschema
# Deployed: 2014-‐01-‐07 16:13:50 -‐0800
#
>
Status Update
>

View Slide

On Target
>

View Slide

On Target
>
sqitch target add flipr_test db:pg:flipr_test
>
Like Git
Remotes

View Slide

On Target
>
>
sqitch config core.pg.target flipr_test
>

View Slide

On Target
>
>
>
> sqitch status
# On database flipr_test
# Project: flipr
# Name: appschema
# Deployed: 2014-‐01-‐07 16:13:50 -‐0800
#
>
No URI

View Slide

On Target
>
>
>
> sqitch config -‐-‐bool deploy.verify true
> sqitch config -‐-‐bool rebase.verify true
>
> sqitch status
# Project: flipr
# Name: appschema
# Deployed: 2014-‐01-‐07 16:13:50 -‐0800
#
>
Always
verify.

View Slide

Commit Conﬁg
>

View Slide

Commit Conﬁg
>
git add .
> git commit -‐m 'Add target and verify to config.'
[master 099b677] Add target and verify to config.
1 file changed, 4 insertions(+)
>

View Slide

Commit Conﬁg
>
git push
07548c2..099b677 master -‐> master
>
git add .
> git commit -‐m 'Add target and verify to config.'
[master 099b677] Add target and verify to config.
>

View Slide

Not Migrations?
antisocial network

View Slide

Not Migrations?
Incomplete mini-language
antisocial network

View Slide

Not Migrations?
No logical replication integration
antisocial network

View Slide

Not Migrations?
Numbered scripts difﬁcult to track
antisocial network

View Slide

Not Migrations?
Numbered scripts difﬁcult to track
No VCS awareness
antisocial network

View Slide

SQL Migrations?
Numbered scripts hard to track
No VCS awareness
antisocial network

View Slide

SQL Migrations?
No VCS awareness
———————————————
antisocial network

View Slide

SQL Migrations?
No VCS awareness
———————————————
———————————————————
antisocial network

View Slide

Sq—what?
sql anges
ch
antisocial network

View Slide

Sq—what?
sq ch
antisocial network

View Slide

it
Sq—what?
sq ch
antisocial network

View Slide

it
Sq—what?
sq ch
There is no “u”
antisocial network

View Slide

Sqitch Philosophy
antisocial network

View Slide

Sqitch Philosophy
No opinions
antisocial network

View Slide

Sqitch Philosophy
No opinions
Native scripting (psql, sqlite3, SQL*Plus)
antisocial network

View Slide

Sqitch Philosophy
No opinions
Cross-project dependency resolution
antisocial network

View Slide

Sqitch Philosophy
No opinions
Distribution bundling
antisocial network

View Slide

Sqitch Philosophy
No opinions
Integrated veriﬁcation testing
antisocial network

View Slide

Sqitch Philosophy
No opinions
No numbering
antisocial network

View Slide

Sqitch Philosophy
No opinions
No numbering
Reliable sequential deployment ordering
antisocial network

View Slide

SHAzbat
antisocial network

View Slide

SHAzbat
Stolen from Git
antisocial network

View Slide

SHAzbat
Stolen from Git
change, tag, deploy, revert, verify
antisocial network

View Slide

SHAzbat
Stolen from Git
Hashed change text includes:
antisocial network

View Slide

SHAzbat
Stolen from Git
Project
antisocial network

View Slide

SHAzbat
Stolen from Git
Project
Name
antisocial network

View Slide

SHAzbat
Stolen from Git
Project
Name
Parent ID
antisocial network

View Slide

SHAzbat
Stolen from Git
Project
Name
Parent ID
Planner…
antisocial network

View Slide

SHAsome
>

View Slide

SHAsome
>
sqitch show change @HEAD
project pgxn_manager
uri https://github.com/pgxn/pgxn-‐manager.git
change distview
parent 39d7e105cb00e1798cfabb45f4b74cbc14a82513
planner David E. Wheeler
date 2013-‐02-‐08T23:51:19Z
requires
+ roles
+ pgxn_core:types
+ distributions
+ extensions
Adds the filters table.
>

View Slide

SHAsome
>
change distview
date 2013-‐02-‐08T23:51:19Z
requires
+ roles
+ pgxn_core:types
+ distributions
+ extensions
>
Symbolic
Sqitch tag

View Slide

SHAsome
>
change distview
date 2013-‐02-‐08T23:51:19Z
requires
+ roles
+ pgxn_core:types
+ distributions
+ extensions
>

View Slide

SHApay!
antisocial network

View Slide

SHApay!
Each change (except ﬁrst) includes parent
antisocial network

View Slide

SHApay!
Can trace from any change to the beginning
antisocial network

View Slide

SHApay!
Change tampering (corruption) detectable
antisocial network

View Slide

SHApay!
antisocial network

View Slide

SHApay!
Stole Linus Torvalds’s “greatest invention”
antisocial network

View Slide

Sqitch Features
antisocial network

View Slide

Sqitch Features
Reduced duplication
antisocial network

View Slide

Sqitch Features
Reduced duplication
Built-in conﬁguration
antisocial network

View Slide

Sqitch Features
Reduced duplication
Iterative development
antisocial network

View Slide

Sqitch Features
Reduced duplication
Targeted deployment
antisocial network

View Slide

Sqitch Features
Reduced duplication
Targeted deployment
Git-style interface
antisocial network

View Slide

Sqitch Features
Reduced duplication
Targeted deployment
Git-style interface
Deployment tagging
antisocial network

View Slide

antisocial network
Your Turn

View Slide

antisocial network
Your Turn
Conﬁgure Sqitch

View Slide

antisocial network
Your Turn
Conﬁgure Sqitch
Initialize project

View Slide

antisocial network
Your Turn
Conﬁgure Sqitch
Initialize project
Add appschema
change

View Slide

antisocial network
Your Turn
Conﬁgure Sqitch
Initialize project
Add appschema
change
Deploy/Revert

View Slide

antisocial network
Your Turn
Conﬁgure Sqitch
Initialize project
Add appschema
change
Deploy/Revert
Commit/push

View Slide

antisocial network
Your Turn
Conﬁgure Sqitch
Initialize project
Add appschema
change
Deploy/Revert
Commit/push
https:/
/github.com/

View Slide

Add Tests
>

View Slide

Add Tests
>
mkdir test
>

View Slide

Add Tests
>
mkdir test
>
emacs test/appschema.sql
>

View Slide

test/appschema.
pgTAP Basics

View Slide

test/appschema.
pgTAP Basics
SET client_min_messages TO warning;
CREATE EXTENSION IF NOT EXISTS pgtap;
RESET client_min_messages;
BEGIN;
SELECT plan(1);
SELECT has_schema('nada');
SELECT finish();
ROLLBACK;

View Slide

>
Run the Test

View Slide

>
Run the Test
pg_prove -‐v -‐d flipr_test test/appschema.sql
test/appschema.sql ..
1..1
not ok 1 -‐ Schema nada should exist
# Failed test 1: "Schema nada should exist"
# Looks like you failed 1 test of 1
Failed 1/1 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
test/appschema.sql (Wstat: 0 Tests: 1 Failed: 1)
Failed test: 1
Files=1, Tests=1, 0 wallclock secs
Result: FAIL

View Slide

First Pass
>

View Slide

perl -‐i -‐pe 's/nada/flipr/' test/appschema.sql
>
First Pass
>

View Slide

>
First Pass
>
> pg_prove -‐v -‐d flipr_test test/appschema.sql
1..1
ok 1 -‐ Schema flipr should exist
ok
All tests successful.
Result: PASS

View Slide

>
First Pass
W00t!
>
> pg_prove -‐v -‐d flipr_test test/appschema.sql
1..1
ok 1 -‐ Schema flipr should exist
ok
Result: PASS

View Slide

Pass it On
>

View Slide

git add .
>
Pass it On
>

View Slide

git add .
> git commit -‐m 'Add appschema test.'
[master 92bd85c] Add appschema test.
create mode 100644 test/appschema.sql
>
Pass it On
>

View Slide

git add .
> git commit -‐m 'Add appschema test.'
[master 92bd85c] Add appschema test.
create mode 100644 test/appschema.sql
>
Pass it On
>
git push
To ssh://[email protected]:7999/~david.w
099b677..c965fb6 master -‐> master
>

View Slide

View Slide

OMG TAP
WTF?

View Slide

What is TAP?
antisocial network

View Slide

What does that mean in practice?
What is TAP?
antisocial network

View Slide

Test output easy to interpret
What is TAP?
antisocial network

View Slide

By humans
What is TAP?
antisocial network

View Slide

By humans
By computers
What is TAP?
antisocial network

View Slide

By humans
By computers
pg_prove (the harness)
What is TAP?
antisocial network

View Slide

By humans
By computers
By aliens
What is TAP?
antisocial network

View Slide

By humans
By computers
By aliens
By gum!
What is TAP?
antisocial network

View Slide

What’s the plan, man?
antisocial network

View Slide

Includes Test controls:
antisocial network

View Slide

plan() — How many tests?
antisocial network

View Slide

no_plan() — Unknown number of tests
antisocial network

View Slide

diag() — Diagnostic output
antisocial network

View Slide

diag() — Diagnostic output
ﬁnish() — Test ﬁnished, report!
antisocial network

View Slide

Scalarly
antisocial network

View Slide

Scalarly
Includes simple scalar test functions:
antisocial network

View Slide

Scalarly
ok() — Boolean
antisocial network

View Slide

Scalarly
ok() — Boolean
is() — Value comparison
antisocial network

View Slide

Scalarly
ok() — Boolean
isnt() — NOT is()
antisocial network

View Slide

Scalarly
ok() — Boolean
isnt() — NOT is()
cmp_ok() — Compare with speciﬁc operator
antisocial network

View Slide

Scalarly
ok() — Boolean
isnt() — NOT is()
matches() — Regex comparison
antisocial network

View Slide

Scalarly
ok() — Boolean
isnt() — NOT is()
matches() — Regex comparison
imatches() — Case-insensitive regex
comparison
antisocial network

View Slide

It’s All Relative
antisocial network

View Slide

It’s All Relative
Includes functions for testing relations:
antisocial network

View Slide

It’s All Relative
results_eq() — Ordered results
antisocial network

View Slide

It’s All Relative
set_eq() — Set of values
antisocial network

View Slide

It’s All Relative
bag_eq() — Bag of values
antisocial network

View Slide

It’s All Relative
row_eq() — Single row
antisocial network

View Slide

It’s All Relative
row_eq() — Single row
is_empty() — No results
antisocial network

View Slide

I’m Okay, You’re Okay
antisocial network

View Slide

throws_ok() — Throws an exception
antisocial network

View Slide

throws_like() — Exception matches regex
antisocial network

View Slide

skip() — Skip a subset of tests
antisocial network

View Slide

skip() — Skip a subset of tests
todo() — Expect subset of tests to fail
antisocial network

View Slide

Schematics
antisocial network

View Slide

Schematics
has_table(), has_view(), has_function(), etc.
antisocial network

View Slide

Schematics
columns_are(), has_pk(), fk_ok(), etc.
antisocial network

View Slide

Schematics
col_type_is(), col_not_null(),
col_default_is()
antisocial network

View Slide

Schematics
col_type_is(), col_not_null(),
col_default_is()
So, so much more…
antisocial network

View Slide

Other Features and Topics
antisocial network

View Slide

xUnit-Style testing
antisocial network

View Slide

xUnit-Style testing
Test-Driven development
antisocial network

View Slide

xUnit-Style testing
Integration with Perl unit tests
antisocial network

View Slide

xUnit-Style testing
Integration with pg_regress
antisocial network

View Slide

xUnit-Style testing
Negative assertions
antisocial network

View Slide

xUnit-Style testing
Negative assertions
Role and privilege assertions
antisocial network

View Slide

xUnit-Style testing
Negative assertions
http:/
/pgtap.org/
antisocial network

View Slide

xUnit-Style testing
Negative assertions
http:/
/pgtap.org/
http:/
/pgxn.org/extension/pgtap/
antisocial network

View Slide

antisocial network
Let’s do it!

View Slide

antisocial network
Let’s do it!
Create appschema
test

View Slide

antisocial network
Let’s do it!
Create appschema
test
Use pgTAP

View Slide

antisocial network
Let’s do it!
Create appschema
test
Use pgTAP
Run test with
pg_prove

View Slide

antisocial network
Let’s do it!
Create appschema
test
Use pgTAP
Run test with
pg_prove
Make it fail

View Slide

antisocial network
Let’s do it!
Create appschema
test
Use pgTAP
Run test with
pg_prove
Make it fail
Make it pass!

View Slide

antisocial network
Let’s do it!
Create appschema
test
Use pgTAP
Run test with
pg_prove
Make it fail
Make it pass!
Commit/Push

View Slide

antisocial network
Let’s do it!
Create appschema
test
Use pgTAP
Run test with
pg_prove
Make it fail
Make it pass!
Commit/Push
https:/
/github.com/

View Slide

Let’s talk about…
antisocial network
antisocial network
antisocial network

View Slide

antisocial network
antisocial network

View Slide

TDD
antisocial network
antisocial network

View Slide

antisocial network

View Slide

TDD is an act
of design.
antisocial network

View Slide

antisocial network

View Slide

TDD is an act of
documentation.
antisocial network

View Slide

Database Design
antisocial network

View Slide

Database Design
Specify requirements
antisocial network

View Slide

Database Design
Implement schema design
antisocial network

View Slide

Database Design
Program applications
antisocial network

View Slide

Database Design
QA
antisocial network

View Slide

Database Design
QA
Big jump!
antisocial network

View Slide

Database Design
QA
Pricy to ﬁx
antisocial network

View Slide

Database Design
QA
Never mind busy DBA schedules!
antisocial network

View Slide

Why TDDD
antisocial network

View Slide

Why TDDD
Ensure data quality
antisocial network

View Slide

Why TDDD
Ensure data quality
Data is a core asset
antisocial network

View Slide

Why TDDD
Ensure data quality
Validate business rules
antisocial network

View Slide

Why TDDD
Ensure data quality
Stored procedures
antisocial network

View Slide

Why TDDD
Ensure data quality
Stored procedures
Triggers
antisocial network

View Slide

Why TDDD
Ensure data quality
Stored procedures
Triggers
Views
antisocial network

View Slide

Why TDDD
antisocial network

View Slide

Why TDDD
Identify defects early
antisocial network

View Slide

Why TDDD
…and often!
antisocial network

View Slide

Why TDDD
…and often!
Create design iteratively
antisocial network

View Slide

Why TDDD
…and often!
Evolutionarily
antisocial network

View Slide

Why TDDD
…and often!
Evolutionarily
Validate refactorings
antisocial network

View Slide

Why TDDD
…and often!
Evolutionarily
Validate refactorings
Make sure nothing breaks
antisocial network

View Slide

Three Questions
for Database Professionals
from
Scott Ambler
antisocial network

View Slide

View Slide

“If you’re implementing code
in the DB in the form of
stored procedures, triggers...
shouldn’t you test that code
to the same level that you
test your app code?”

View Slide

View Slide

“Think of all the data quality
problems you’ve run into
over the years. Wouldn't it
have been nice if someone
had originally tested and
discovered those problems
before you did?”

View Slide

View Slide

“Wouldn’t it be nice to have
a test suite to run so that
you could determine how
(and if) the DB actually
works?”

View Slide

antisocial network

View Slide

Okay.
antisocial network

View Slide

antisocial network

View Slide

How?
antisocial network

View Slide

TDD How
antisocial network

View Slide

TDD How
Ideally separate from app tests
antisocial network

View Slide

TDD How
May be many apps
antisocial network

View Slide

TDD How
May be many apps
DB should present interface to all
antisocial network

View Slide

TDD How
May be many apps
Apps may use different permissions
antisocial network

View Slide

TDD How
May be many apps
Ideally use DB test Framework
antisocial network

View Slide

TDD How
May be many apps
Ideally use DB test Framework
Like…pgTAP!
antisocial network

View Slide

Test Template

View Slide

Test Template
Sqitch scripts generated from templates

View Slide

Test Template
Can add new templates for new ﬁles

View Slide

Test Template
Always want test for change?

View Slide

Test Template
Always want test for change?
Add test template!

View Slide

Templates Where?
>

View Slide

Templates Where?
mkdir -‐p ~/.sqitch/templates/test
>
>
Or system-wide: 
`sqitch --etc-path`/templates

View Slide

Templates Where?
>
>
cp test/appschema.sql ~/.sqitch/templates/test/pg.tmpl
>
Will create ﬁle in
project test directory.

View Slide

emacs ~/.sqitch/templates/test/pg.tmpl
>
Templates Where?
>
>
cp test/appschema.sql ~/.sqitch/templates/test/pg.tmpl
>

View Slide

pg.tmpl
Template Your Tests
!
BEGIN;
!
!
!
SELECT
!
SELECT finish();
ROLLBACK;
has_schema('flipr');
SELECT plan(1);

View Slide

pg.tmpl
Template Your Tests
!
BEGIN;
!
!
!
SELECT
!
SELECT finish();
ROLLBACK;
has_schema('flipr');
Adjust
test
counts.
SELECT no_plan();
-‐-‐ SELECT plan(1);

View Slide

pg.tmpl
Template Your Tests
!
BEGIN;
!
!
!
SELECT
!
SELECT finish();
ROLLBACK;
pass('Test [% change %]!');
Put tests
here.
SELECT no_plan();

View Slide

Branching Out
>

View Slide

git checkout -‐b users master
Switched to a new branch 'users'
>
Branching Out
>

View Slide

>
Branching Out
> Branched off
from others

View Slide

>
Branching Out
>
> sqitch add users -‐-‐requires appschema \
-‐n 'Creates table to track our users.'
Created deploy/users.sql
Created revert/users.sql
Created test/users.sql
Created verify/users.sql
Added "users [appschema]" to sqitch.plan
>

View Slide

>
Branching Out
>
>
Cool!

View Slide

>
Branching Out
>
>

View Slide

>
Branching Out
>
> emacs test/users.sql
>
>

View Slide

!
BEGIN;
SELECT no_plan();
!
!
!
!
test/users.sql
Table For One
SELECT pass('Test users!');
SELECT finish();
ROLLBACK;

View Slide

!
BEGIN;
SELECT no_plan();
!
!
!
!
test/users.sql
Table For One
SET search_path TO flipr,public;
SELECT has_table( 'users' );
SELECT finish();
ROLLBACK;

View Slide

Run ’Em
>

View Slide

pg_prove -‐d flipr_test -‐v test/users.sql
test/users.sql ..
not ok 1 -‐ Table users should exist
# Failed test 1: "Table users should exist"
1..1
Failed 1/1 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
test/users.sql (Wstat: 0 Tests: 1 Failed: 1)
Failed test: 1
Result: FAIL
>
Run ’Em
>

View Slide

test/users.sql ..
1..1
Failed 1/1 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Failed test: 1
Result: FAIL
>
Run ’Em
As expected.
>

View Slide

test/users.sql ..
1..1
Failed 1/1 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Failed test: 1
Result: FAIL
>
Run ’Em
>
emacs deploy/users.sql

View Slide

deploy/users.sq
deploy/users.sql
COMMIT;
-‐-‐ Deploy users
-‐-‐ requires: appschema
!
BEGIN;
!
 

View Slide

SET client_min_messages = 'warning';
CREATE TABLE flipr.users (
nickname TEXT
);
deploy/users.sq
deploy/users.sql
COMMIT;
!
BEGIN;
!
 

View Slide

nickname TEXT
);
deploy/users.sq
deploy/users.sql
COMMIT;
!
BEGIN;
!
 
Bare
Minimum

View Slide

Verily, Users
>
>

View Slide

Verily, Users
>
>
emacs verify/users.sql

View Slide

-‐-‐ Verify users
!
BEGIN;
verify/users.sq
verify/users.sql
ROLLBACK;

View Slide

!
BEGIN;
SELECT nickname
FROM flipr.users
WHERE FALSE;
verify/users.sq
verify/users.sql
ROLLBACK;

View Slide

Unusered
>
> emacs deploy/users.sql
> emacs verify/users.sql
>

View Slide

Unusered
>
>
emacs revert/users.sql

View Slide

revert/users.sq
revert/users.sql
-‐-‐ Revert users
!
BEGIN;
!
!
!
COMMIT;

View Slide

revert/users.sq
revert/users.sql
-‐-‐ Revert users
!
BEGIN;
!
!
!
COMMIT;
DROP TABLE flipr.users;

View Slide

Make Users
>

View Slide

sqitch deploy
+ users .. ok
>
Make Users
>

View Slide

sqitch deploy
+ users .. ok
>
Make Users
>
psql -‐d flipr_test -‐c '\d flipr.users'
Table "flipr.users"
Column | Type | Modifiers
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
nickname | text |
>

View Slide

sqitch deploy
+ users .. ok
>
Make Users
>
psql -‐d flipr_test -‐c '\d flipr.users'
Table "flipr.users"
Column | Type | Modifiers
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
nickname | text |
>
sqitch verify
Verifying flipr_test
* appschema .. ok
* users ...... ok
Verify successful
>

View Slide

Make Users
>

View Slide

test/users.sql ..
ok 1 -‐ Table users should exist
1..1
ok
Result: PASS
>
Make Users
>

View Slide

test/users.sql ..
1..1
ok
Result: PASS
>
Make Users
>
Woohoo!

View Slide

test/users.sql ..
1..1
ok
Result: PASS
>
Make Users
>
emacs test/users.sql
>

View Slide

!
BEGIN;
SELECT no_plan();
!
test/users.sql
Columnist
SELECT finish();
ROLLBACK;

View Slide

!
BEGIN;
SELECT no_plan();
!
SELECT has_column( 'users', 'nickname' );
SELECT has_column( 'users', 'password' );
SELECT has_column( 'users', 'timestamp' );
test/users.sql
Columnist
SELECT finish();
ROLLBACK;

View Slide

Dead Again
>

View Slide

test/users.sql ..
ok 2 -‐ Column users.nickname should exist
not ok 3 -‐ Column users.password should exist
# Failed test 3: "Column users.password should exist"
not ok 4 -‐ Column users."timestamp" should exist
# Failed test 4: "Column users."timestamp" should exist"
1..4
# Looks like you failed 2 tests of 4
Failed 2/4 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Failed tests: 3-‐4
Result: FAIL
Dead Again
>

View Slide

test/users.sql ..
not ok 3 -‐ Column users.password should exist
# Failed test 3: "Column users.password should exist"
not ok 4 -‐ Column users."timestamp" should exist
# Failed test 4: "Column users."timestamp" should exist"
1..4
Failed 2/4 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Failed tests: 3-‐4
Result: FAIL
Dead Again
>
Guess we should
add them.

View Slide

MOAR Deploy
>

View Slide

>
MOAR Deploy
>

View Slide

!
BEGIN;
!
nickname TEXT
deploy/users.sq
deploy/users.sql
);
!
COMMIT;

View Slide

!
BEGIN;
!
nickname TEXT
,
password TEXT,
timestamp TIMESTAMPTZ
deploy/users.sq
deploy/users.sql
);
!
COMMIT;

View Slide

>
Update Verify
>

View Slide

>
Update Verify
>
emacs verify/users.sql
>

View Slide

!
BEGIN;
!
SELECT nickname
FROM flipr.users
WHERE FALSE;
!
COMMIT;
verify/users.sq
verify/users.sql

View Slide

!
BEGIN;
!
SELECT nickname
FROM flipr.users
WHERE FALSE;
!
COMMIT;
, password, timestamp
verify/users.sq
verify/users.sql

View Slide

Revert Overhead
>

View Slide

Revert Overhead
sqitch revert -‐-‐to @HEAD^ -‐y
Reverting changes to appschema from flipr_test
-‐ users .. ok
>
>

View Slide

Revert Overhead
-‐ users .. ok
>
Yes, really.
>

View Slide

Revert Overhead
-‐ users .. ok
>
>

View Slide

Revert Overhead
-‐ users .. ok
>
Remove
>

View Slide

Revert Overhead
-‐ users .. ok
>
>
What’s
that?

View Slide

Sqitch Tags
antisocial network

View Slide

Sqitch Tags
Start with @
antisocial network

View Slide

Sqitch Tags
Start with @
To distinguish from changes
antisocial network

View Slide

Sqitch Tags
Start with @
Two symbolic tags:
antisocial network

View Slide

Sqitch Tags
Start with @
Two symbolic tags:
@HEAD Last change
antisocial network

View Slide

Sqitch Tags
Start with @
Two symbolic tags:
@HEAD Last change
@ROOT First change
antisocial network

View Slide

Sqitch Tags
Start with @
Two symbolic tags:
@HEAD Last change
@ROOT First change
Two modiﬁers:
antisocial network

View Slide

Sqitch Tags
Start with @
Two symbolic tags:
@HEAD Last change
@ROOT First change
Two modiﬁers:
^ Previous change
antisocial network

View Slide

Sqitch Tags
Start with @
Two symbolic tags:
@HEAD Last change
@ROOT First change
Two modiﬁers:
^ Previous change
~ Following change
antisocial network

View Slide

Specifying Changes
antisocial network

View Slide

Specifying Changes
users Change named “users”
antisocial network

View Slide

Specifying Changes
@HEAD^ Second to last change
antisocial network

View Slide

Specifying Changes
users^ Change before users
antisocial network

View Slide

Specifying Changes
@ROOT~ Second change
antisocial network

View Slide

Specifying Changes
appschema~ Change after
appschema
antisocial network

View Slide

Specifying Changes
appschema
@HEAD^^ Third to last change
antisocial network

View Slide

Specifying Changes
appschema
@HEAD^^ Third to last change
users~4 4th change after
antisocial network

View Slide

> sqitch revert -‐-‐to @HEAD^ -‐y
-‐ users .. ok
>
Whither Users
>

View Slide

-‐ users .. ok
>
Whither Users
>
> psql -‐d flipr_test -‐c '\d flipr.users'
Did not find any relation named "flipr.users".
>

View Slide

-‐ users .. ok
>
sqitch status
# Project: flipr
# Name: appschema
# Deployed: 2014-‐01-‐07 16:13:50 -‐0800
#
Undeployed change:
* users
Whither Users
>
> psql -‐d flipr_test -‐c '\d flipr.users'
Did not find any relation named "flipr.users".
>

View Slide

Whither Users
>

View Slide

> sqitch verify
Verifying flipr_test
* appschema .. ok
Undeployed change:
* users
Verify successful
>
Whither Users
>

View Slide

Back At It
>

View Slide

sqitch deploy
+ users .. ok
>
Back At It
>

View Slide

sqitch deploy
+ users .. ok
>
Back At It
>
Add

View Slide

sqitch deploy
+ users .. ok
>
Back At It
>
test/users.sql ..
ok 3 -‐ Column users.password should exist
ok 4 -‐ Column users."timestamp" should exist
1..4
ok
Result: PASS
>

View Slide

sqitch deploy
+ users .. ok
>
Back At It
>
test/users.sql ..
1..4
ok
Result: PASS
>
Woot!

View Slide

sqitch deploy
+ users .. ok
>
Back At It
>
test/users.sql ..
1..4
ok
Result: PASS
>
emacs test/users.sql

View Slide

SET search_path = public,tap;
!
BEGIN;
SELECT no_plan();
!
test/users.sql
SELECT finish();
ROLLBACK;

View Slide

!
BEGIN;
SELECT no_plan();
!
test/users.sql
SELECT has_pk( 'users' );
SELECT finish();
ROLLBACK;

View Slide

SELECT col_type_is( 'users', 'nickname', 'text' );
SELECT col_hasnt_default( 'users', 'nickname' );
SELECT col_is_pk( 'users', 'nickname' );
!
BEGIN;
SELECT no_plan();
!
test/users.sql
SELECT finish();
ROLLBACK;

View Slide

!
BEGIN;
SELECT no_plan();
!
test/users.sql
SELECT col_type_is( 'users', 'password', 'text' );
SELECT col_not_null( 'users', 'password' );
SELECT col_hasnt_default( 'users', 'password' );
SELECT finish();
ROLLBACK;

View Slide

!
BEGIN;
SELECT no_plan();
!
test/users.sql
SELECT col_type_is('users', 'timestamp', 'timestamp with time zone');
SELECT col_not_null( 'users', 'timestamp' );
SELECT col_has_default( 'users', 'timestamp' );
SELECT col_default_is( 'users', 'timestamp', 'now()' );
SELECT col_type_is( 'users', 'password', 'text' );
SELECT col_not_null( 'users', 'password' );
SELECT col_hasnt_default( 'users', 'password' );
SELECT finish();
ROLLBACK;

View Slide

Columny
>

View Slide

> pg_prove -‐d flipr_test test/users.sql
test/users.sql .. 1/?
# Failed test 2: "Table users should have a primary key"
# Failed test 6: "Column users(nickname) should be a primary key"
# have: NULL
# want: {nickname}
# Failed test 9: "Column users.password should be NOT NULL"
# Failed test 13: "Column users."timestamp" should be NOT NULL"
# Failed test 14: "Column users."timestamp" should have a default"
# Failed test 15: "Column users."timestamp" should default to 'now()'"
# Column users."timestamp" has no default
test/users.sql .. Failed 6/15 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Failed tests: 2, 6, 9, 13-‐15
Result: FAIL
Columny
>

View Slide

> pg_prove -‐d flipr_test test/users.sql
test/users.sql .. 1/?
# Failed test 2: "Table users should have a primary key"
# Failed test 6: "Column users(nickname) should be a primary key"
# have: NULL
# want: {nickname}
# Failed test 9: "Column users.password should be NOT NULL"
# Failed test 13: "Column users."timestamp" should be NOT NULL"
# Failed test 14: "Column users."timestamp" should have a default"
# Failed test 15: "Column users."timestamp" should default to 'now()'"
# Column users."timestamp" has no default
test/users.sql .. Failed 6/15 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
Failed tests: 2, 6, 9, 13-‐15
Result: FAIL
Columny
>
Let’s make
it so.

View Slide

User Typography
>

View Slide

>
User Typography
>

View Slide

!
BEGIN;
!
nickname TEXT
password TEXT
);
!
COMMIT;
deploy/users.sq
deploy/users.sql
,
,

View Slide

!
BEGIN;
!
nickname TEXT
password TEXT
);
!
COMMIT;
deploy/users.sq
deploy/users.sql
,
PRIMARY KEY,

View Slide

!
BEGIN;
!
nickname TEXT
password TEXT
);
!
COMMIT;
deploy/users.sq
deploy/users.sql
PRIMARY KEY,
NOT NULL,

View Slide

!
BEGIN;
!
nickname TEXT
password TEXT
);
!
COMMIT;
deploy/users.sq
deploy/users.sql
PRIMARY KEY,
NOT NULL,
NOT NULL DEFAULT NOW()

View Slide

User Typography
>

View Slide

sqitch rebase @HEAD^ -‐y
-‐ users .. ok
+ users .. ok
>
User Typography
>

View Slide

-‐ users .. ok
+ users .. ok
>
User Typography
>
pg_prove -‐d flipr_test test/users.sql
test/users.sql .. ok
Result: PASS
>

View Slide

-‐ users .. ok
+ users .. ok
>
User Typography
>
pg_prove -‐d flipr_test test/users.sql
test/users.sql .. ok
Result: PASS
> Boom.

View Slide

Additives
>

View Slide

Additives
>
git add .
>

View Slide

Additives
>
git add .
>
git commit -‐am 'Add users table.'
[users 693ca89] Add users table.
create mode 100644 deploy/users.sql
create mode 100644 revert/users.sql
create mode 100644 test/users.sql
create mode 100644 verify/users.sql
>

View Slide

Pushers
>

View Slide

Pushers
>
git push -‐-‐set-‐upstream origin users
Writing objects: 100% (15/15), 1.95 KiB, done.
* [new branch] users -‐> users
Branch users set up to track remote branch users
from origin.
>

View Slide

Wash, Rinse, Repeat
antisocial network

View Slide

Wash, Rinse, Repeat
Add failing simple test
antisocial network

View Slide

Wash, Rinse, Repeat
Add and deploy change
antisocial network

View Slide

Wash, Rinse, Repeat
Revise test
antisocial network

View Slide

Wash, Rinse, Repeat
Revise test
Revise and rebase change
antisocial network

View Slide

Wash, Rinse, Repeat
Revise test
Wash, Rinse, Repeat
antisocial network

View Slide

Wash, Rinse, Repeat
Revise test
Wash, Rinse, Repeat
Commit/Push when done
antisocial network

View Slide

Wash, Rinse, Repeat
Revise test
Wash, Rinse, Repeat
Commit/Push when done
Breathe in, breathe out
antisocial network

View Slide

Time to Work!
antisocial network

View Slide

Time to Work!
Prepare to hack!
antisocial network

View Slide

Time to Work!
Prepare to hack!
git checkout master
antisocial network

View Slide

Time to Work!
Prepare to hack!
git checkout master
git branch -D users
antisocial network

View Slide

Time to Work!
Prepare to hack!
git checkout master
git branch -D users
git checkout -b users
antisocial network

View Slide

Time to Work!
Prepare to hack!
git checkout master
git branch -D users
git reset --hard upstream/users
antisocial network

View Slide

Time to Work!
Prepare to hack!
git checkout master
git branch -D users
git log
antisocial network

View Slide

Time to Work!
Prepare to hack!
git checkout master
git branch -D users
git log
Should be at “Add users table.”
antisocial network

View Slide

Caution: Hard Reset
Ahead

View Slide

Caution: Hard Reset
Ahead
A rare destructive Git command

View Slide

Caution: Hard Reset
Ahead
Deletes HEAD snapshot

View Slide

Caution: Hard Reset
Ahead
Replaces it with new snapshot

View Slide

Caution: Hard Reset
Ahead
Almost un-reversible

View Slide

Caution: Hard Reset
Ahead
Useful for starting from known point

View Slide

Caution: Hard Reset
Ahead
Useful for starting from known point
USE WITH CAUTION!

View Slide

antisocial network
Flip Out

View Slide

antisocial network
Flip Out
Create ﬂips branch

View Slide

antisocial network
Flip Out
Create ﬂips table

View Slide

antisocial network
Flip Out
Create ﬂips table
ﬂip_id SERIAL PK

View Slide

antisocial network
Flip Out
Create ﬂips table
ﬂip_id SERIAL PK
nickname FK

View Slide

antisocial network
Flip Out
Create ﬂips table
ﬂip_id SERIAL PK
nickname FK
body TEXT

View Slide

antisocial network
Flip Out
Create ﬂips table
ﬂip_id SERIAL PK
nickname FK
body TEXT
timestamptz

View Slide

antisocial network
Flip Out
Create ﬂips table
ﬂip_id SERIAL PK
nickname FK
body TEXT
timestamptz
Use TDDD

View Slide

antisocial network
Flip Out
Create ﬂips table
ﬂip_id SERIAL PK
nickname FK
body TEXT
timestamptz
Use TDDD
https:/
/github.com/

View Slide

Functional Testing
>

View Slide

Functional Testing
git checkout -‐b userfuncs users
Switched to a new branch 'userfuncs'
>
>
Branches
from users

View Slide

Functional Testing
>
>
sqitch add insert_user -‐r users -‐r appschema \
-‐n 'Creates a function to insert a user.'
Created deploy/insert_user.sql
Created revert/insert_user.sql
Created test/insert_user.sql
Created verify/insert_user.sql
Added "insert_user [users appschema]" to sqitch.plan
>

View Slide

Functional Testing
>
>
sqitch add insert_user -‐r users -‐r appschema \
-‐n 'Creates a function to insert a user.'
Created deploy/insert_user.sql
Created revert/insert_user.sql
Created test/insert_user.sql
Created verify/insert_user.sql
Added "insert_user [users appschema]" to sqitch.plan
>
emacs test/insert_user.sql
>

View Slide

test/insert_use
test/insert_user.sql

View Slide

test/insert_use

View Slide

test/insert_use
BEGIN;
-‐-‐ SELECT no_plan();
SELECT plan(11);

View Slide

test/insert_use
SELECT plan(11);

View Slide

test/insert_use
SELECT plan(11);
SELECT has_function( 'insert_user' );
SELECT has_function(
'insert_user', ARRAY['text', 'text']
);
SELECT function_lang_is(
'insert_user', ARRAY['text', 'text'],
'sql'
);
SELECT function_returns(
'void'
);
SELECT volatility_is(
'volatile'
);

View Slide

test/insert_use
'volatile'
);

View Slide

test/insert_use
'volatile'
);
SELECT lives_ok(
$$ SELECT insert_user('theory', 'foo') $$,
'Insert a user'
);
SELECT row_eq(
'SELECT * FROM users',
ROW('theory', md5('foo'), NOW())::users,
'The user should have been inserted'
);

View Slide

test/insert_use
);

View Slide

test/insert_use
);
SELECT lives_ok(
$$ SELECT insert_user('strongrrl', 'w00t') $$,
'Insert another user'
);
SELECT bag_eq(
$$ VALUES
('theory', md5('foo'), NOW()),
('strongrrl', md5('w00t'), NOW())
$$,
'Both users should be present'
);

View Slide

test/insert_use
);
SELECT lives_ok(
);
SELECT bag_eq(
$$ VALUES
$$,
);
Includes
dupes

View Slide

test/insert_use
);
SELECT lives_ok(
);
SELECT bag_eq(
$$ VALUES
$$,
);

View Slide

test/insert_use
);

View Slide

SELECT throws_ok(
$$ SELECT insert_user('theory', 'ha-‐ha') $$,
23505, -‐-‐ duplicate key violation
NULL, -‐-‐ localized error message
'Should get an error for duplicate nickname'
);
SELECT bag_eq(
$$ VALUES
$$,
'Should still have just the two users'
);
test/insert_use
);

View Slide

SELECT throws_ok(
);
SELECT bag_eq(
$$ VALUES
$$,
);
test/insert_use
);
Appendix A

View Slide

SELECT throws_ok(
);
SELECT bag_eq(
$$ VALUES
$$,
);
test/insert_use
);

View Slide

SELECT throws_ok(
);
SELECT bag_eq(
$$ VALUES
$$,
);
test/insert_use
);
SELECT finish();
ROLLBACK;

View Slide

Functional Testing
>
>

View Slide

Functional Testing
>
>
emacs deploy/insert_user.sql
>

View Slide

deploy/insert_u
deploy/insert_user.sql
-‐-‐ Deploy insert_user
-‐-‐ requires: users
!
BEGIN;
COMMIT;

View Slide

deploy/insert_u
deploy/insert_user.sql
!
BEGIN;
CREATE OR REPLACE FUNCTION flipr.insert_user(
nickname TEXT,
password TEXT
) RETURNS VOID LANGUAGE SQL SECURITY DEFINER AS $$
INSERT INTO flipr.users VALUES($1, md5($2));
$$;
COMMIT;

View Slide

Functional Testing
>
>

View Slide

Functional Testing
>
>
emacs revert/insert_user.sql
>

View Slide

revert/insert_u
revert/insert_user.sql
-‐-‐ Revert insert_user
!
BEGIN;
!
!
!
COMMIT;

View Slide

revert/insert_u
revert/insert_user.sql
-‐-‐ Revert insert_user
!
BEGIN;
!
!
!
COMMIT;
DROP FUNCTION flipr.insert_user(TEXT, TEXT);

View Slide

Functional Testing
>
>

View Slide

Functional Testing
>
>
emacs verify/insert_user.sql
>

View Slide

verify/insert_u
verify/insert_user.sql
-‐-‐ Verify insert_user
!
BEGIN;
ROLLBACK;

View Slide

verify/insert_u
!
BEGIN;
SELECT has_function_privilege(
'flipr.insert_user(text, text)',
'execute'
);
ROLLBACK;

View Slide

verify/insert_u
!
BEGIN;
'execute'
);
ROLLBACK;
Convenient!

View Slide

We Good?
>
>

View Slide

We Good?
>
>
sqitch deploy
+ insert_user .. ok
>

View Slide

We Good?
>
>
sqitch deploy
+ insert_user .. ok
>
pg_prove -‐d flipr_test test/*.sql
test/appschema.sql .... ok
test/insert_user.sql .. ok
test/users.sql ........ ok
Result: PASS
>

View Slide

Commitment
>

View Slide

git add .
> git commit -‐m 'Add `insert_user()`.'
[userfuncs e1c7769] Add `insert_user()`.
create mode 100644 deploy/insert_user.sql
create mode 100644 revert/insert_user.sql
create mode 100644 test/insert_user.sql
create mode 100644 verify/insert_user.sql
>
Commitment
>

View Slide

Push It Real Good…
>

View Slide

git push origin -‐-‐set-‐upstream userfuncs
* [new branch] userfuncs -‐> userfuncs
Branch userfuncs set up to track remote branch
userfuncs from origin.
>
Push It Real Good…
>

View Slide

Reset, Mes Amis!
antisocial network

View Slide

Reset, Mes Amis!
git checkout users
antisocial network

View Slide

Reset, Mes Amis!
git checkout users
git checkout -b userfuncs
antisocial network

View Slide

Reset, Mes Amis!
git checkout users
git checkout -b userfuncs
git reset --hard insert_user
antisocial network

View Slide

antisocial network
None Shall Pass

View Slide

antisocial network
None Shall Pass
Create change_pass()

View Slide

antisocial network
None Shall Pass
Params:

View Slide

antisocial network
None Shall Pass
Params:
nickname

View Slide

antisocial network
None Shall Pass
Params:
nickname
old_pass

View Slide

antisocial network
None Shall Pass
Params:
nickname
old_pass
new_pass

View Slide

antisocial network
None Shall Pass
Params:
nickname
old_pass
new_pass
Only update if old
pass correct

View Slide

antisocial network
None Shall Pass
Params:
nickname
old_pass
new_pass
Only update if old
pass correct
Use TDDD

View Slide

antisocial network
None Shall Pass
Params:
nickname
old_pass
new_pass
Only update if old
pass correct
Use TDDD
https:/
/github.com/

View Slide

Resets
>

View Slide

Resets
>
My solution
> git reset -‐-‐hard upstream/change_pass
HEAD is now at 048017a Add `change_pass()`.

View Slide

Resets
>
> sqitch checkout master -‐y
Last change before the branches diverged: appschema
-‐ change_pass .. ok
-‐ insert_user .. ok
-‐ users ........ ok
Switched to branch 'master'
>

View Slide

Resets
>
-‐ users ........ ok
>
git reset -‐-‐hard appschema
HEAD is now at e46bdf9 Add appschema test.
>

View Slide

Resets
>
Known good.
-‐ users ........ ok
>
git reset -‐-‐hard appschema
HEAD is now at e46bdf9 Add appschema test.
>

View Slide

Mergers and Acquisitions
>

View Slide

git merge -‐-‐no-‐ff users -‐m 'Merge branch "users".'
Merge made by the 'recursive' strategy.
deploy/users.sql | 13 +++++++++++++
revert/users.sql | 7 +++++++
sqitch.plan | 1 +
test/users.sql | 30 ++++++++++++++++++++++++++++++
verify/users.sql | 9 +++++++++
>
>

View Slide

>

View Slide

> git merge -‐-‐no-‐ff flips -‐m 'Merge branch "flips".'
deploy/flips.sql | 15 +++++++++++++++
revert/flips.sql | 7 +++++++
sqitch.plan | 1 +
test/flips.sql | 39 +++++++++++++++++++++++++++++++++++++
verify/flips.sql | 12 ++++++++++++
create mode 100644 deploy/flips.sql
create mode 100644 revert/flips.sql
create mode 100644 test/flips.sql
create mode 100644 verify/flips.sql
>
>

View Slide

> git merge -‐-‐no-‐ff flips -‐m 'Merge branch "flips".'
deploy/flips.sql | 15 +++++++++++++++
revert/flips.sql | 7 +++++++
sqitch.plan | 1 +
test/flips.sql | 39 +++++++++++++++++++++++++++++++++++++
verify/flips.sql | 12 ++++++++++++
create mode 100644 deploy/flips.sql
create mode 100644 revert/flips.sql
create mode 100644 test/flips.sql
create mode 100644 verify/flips.sql
>
>
So good…

View Slide

>

View Slide

> git merge -‐-‐no-‐ff userfuncs -‐m 'Merge branch "user
Auto-‐merging sqitch.plan
CONFLICT (content): Merge conflict in sqitch.plan
Automatic merge failed; fix conflicts and then
commit the result.
>
>

View Slide

> git merge -‐-‐no-‐ff userfuncs -‐m 'Merge branch "user
Automatic merge failed; fix conflicts and then
commit the result.
>
>
Wha???

View Slide

Back in Time…
>

View Slide

Back in Time…
>
>

View Slide

Back in Time…
>
>
Ah-ha!

View Slide

Branching Out
antisocial network

View Slide

Branching Out
users branched from master
antisocial network

View Slide

Branching Out
ﬂips branched from users
antisocial network

View Slide

Branching Out
userfuncs branched from users
antisocial network

View Slide

Branching Out
users and ﬂips merged to master
antisocial network

View Slide

Branching Out
users and ﬂips merged to master
userfuncs unaware of ﬂips
antisocial network

View Slide

Backbrancher
master
A

View Slide

Backbrancher
master
users
A
branch

View Slide

Backbrancher
master
users
A
B
Add
users
table
branch

View Slide

Backbrancher
master
users
A
B
ﬂips
branch
branch

View Slide

Backbrancher
master
users
A
B
ﬂips
C
Add
ﬂips
table
branch
branch

View Slide

Backbrancher
master
users
A
B
ﬂips
C
userfuncs
branch
branch
branch

View Slide

Backbrancher
master
users
A
B
ﬂips
C
userfuncs
Add
insert_user
function
D
branch
branch
branch

View Slide

Backbrancher
master
users
A
B
ﬂips
C
userfuncs
D
branch
branch
branch E
Add
change_pass
function

View Slide

Backbrancher
master
users
A
B
ﬂips
C
userfuncs
B
merge B
D
branch
branch
branch E

View Slide

Backbrancher
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
D
branch
branch
branch E

View Slide

Backbrancher
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
D
F
merge D
&
E
branch
branch
branch E

View Slide

Backbrancher
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
D No C!
F
merge D
&
E
branch
branch
branch E

View Slide

Backbrancher
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
D
conﬂict!
branch
branch
branch E

View Slide

Backbrancher
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
D
conﬂict!
Now
what?
branch
branch
branch E

View Slide

Rebase master
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
branch
branch
branch D E

View Slide

Rebase master
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
branch
branch
branch
Last common
with master: B

View Slide

Rebase master
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
branch
branch
branch
C

View Slide

Rebase master
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
branch
branch
branch
C D
Rebased
E

View Slide

Rebase master
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
branch
branch
branch
C D
E
merge D & E
E

View Slide

\o/
Rebase master
master
users
A
B
ﬂips
C
userfuncs
B
merge B
C
merge C
branch
branch
branch
C D
E
merge D & E
E

View Slide

Reset
>

View Slide

git reset -‐-‐hard HEAD
HEAD is now at e1cfc5d Merge branch "flips".
>
Reset
>

View Slide

>
Reset
>
Use with
care!

View Slide

>
Reset
>

View Slide

>
Reset
>
git checkout userfuncs
Switched to branch 'userfuncs'
>

View Slide

Rebase
>

View Slide

> git rebase master
First, rewinding head to replay your work on top of it...
Applying: Add `insert_user()`.
Using index info to reconstruct a base tree...
M sqitch.plan
Falling back to patching base and 3-‐way merge...
Failed to merge in the changes.
Patch failed at 0001 Add `insert_user()`.
The copy of the patch that failed is found in:
flipr-‐db/.git/rebase-‐apply/patch
When you have resolved this problem, run "git rebase -‐-‐contin
If you prefer to skip this patch, run "git rebase -‐-‐skip" ins
To check out the original branch and stop rebasing, run "git
rebase -‐-‐abort".
>
Rebase
>

View Slide

> git rebase master
M sqitch.plan
>
Rebase
>
Back to B,
apply C.

View Slide

> git rebase master
M sqitch.plan
>
Rebase
>
That’s D

View Slide

> git rebase master
M sqitch.plan
>
Rebase
> Here comes D…

View Slide

> git rebase master
M sqitch.plan
>
Rebase
>
D’oh!

View Slide

> git rebase master
M sqitch.plan
>
Rebase
>

View Slide

Wha Happen?
antisocial network

View Slide

Wha Happen?
Same conﬂict
antisocial network

View Slide

Wha Happen?
Same conﬂict
Both branches modiﬁed sqitch.plan
antisocial network

View Slide

Wha Happen?
Same conﬂict
Both branches modiﬁed sqitch.plan
The same line!
antisocial network

View Slide

Wha Happen?
>

View Slide

> git diff
diff -‐-‐cc sqitch.plan
index 7526d1c,e2c966c..0000000
-‐-‐-‐ a/sqitch.plan
+++ b/sqitch.plan
@@@ -‐4,4 -‐4,4 +4,8 @@@

appschema 2014-‐01-‐07T23:47:31Z David E. Wheeler users [appschema] 2014-‐01-‐08T04:13:38Z David E. Wh
++<<<<<<< HEAD
+flips [appschema users] 2014-‐01-‐08T04:48:29Z David
++=======
+ insert_user [users appschema] 2014-‐01-‐08T04:52:50Z
++>>>>>>> Add `insert_user()`.
Wha Happen?
>

View Slide

> git diff
index 7526d1c,e2c966c..0000000
+++ b/sqitch.plan
@@@ -‐4,4 -‐4,4 +4,8 @@@

++<<<<<<< HEAD
++=======
Wha Happen?
>
B

View Slide

> git diff
index 7526d1c,e2c966c..0000000
+++ b/sqitch.plan
@@@ -‐4,4 -‐4,4 +4,8 @@@

++<<<<<<< HEAD
++=======
Wha Happen?
>
C
B

View Slide

> git diff
index 7526d1c,e2c966c..0000000
+++ b/sqitch.plan
@@@ -‐4,4 -‐4,4 +4,8 @@@

++<<<<<<< HEAD
++=======
Wha Happen?
>
C
D
B

View Slide

Scratch that Sqitch
antisocial network

View Slide

Scratch that Sqitch
Screwed either way?
antisocial network

View Slide

Scratch that Sqitch
Screwed either way?
Fortunately, this is Git
antisocial network

View Slide

Scratch that Sqitch
Screwed either way?
Tell it to treat Sqitch plans differently
antisocial network

View Slide

Scratch that Sqitch
Screwed either way?
Changes on single lines
antisocial network

View Slide

Scratch that Sqitch
Screwed either way?
Only appended to plan ﬁle
antisocial network

View Slide

Scratch that Sqitch
Screwed either way?
Only appended to plan ﬁle
Use the “union” merge
antisocial network

View Slide

Re: Union Merge

View Slide

Re: Union Merge
Run 3-way file level merge for text files,
but take lines from both versions, instead
of leaving conflict markers. This tends to
leave the added lines in the resulting file in
random order and the user should verify
the result. Do not use this if you do not
understand the implications.
—Git Manual

View Slide

Hallelunion
antisocial network

View Slide

Hallelunion
Just appends lines
antisocial network

View Slide

Hallelunion
Just appends lines
Exactly how changes work
antisocial network

View Slide

Hallelunion
Just appends lines
Let’s clean up our mess
antisocial network

View Slide

Hallelunion
Just appends lines
Let’s clean up our mess
And try again
antisocial network

View Slide

Reemerge
>

View Slide

git rebase -‐-‐abort
>
Reemerge
>

View Slide

echo sqitch.plan merge=union > .gitattributes
>
>
Reemerge
>

View Slide

>
git rebase master
M sqitch.plan
Applying: Add `change_pass()`.
M sqitch.plan
>
>
Reemerge
>

View Slide

>
git rebase master
M sqitch.plan
M sqitch.plan
>
>
Reemerge
>
Back to B,
applies C

View Slide

>
git rebase master
M sqitch.plan
M sqitch.plan
>
>
Reemerge
>
That’s D

View Slide

>
git rebase master
M sqitch.plan
M sqitch.plan
>
>
Reemerge
>
Union merge

View Slide

>
git rebase master
M sqitch.plan
M sqitch.plan
>
>
Reemerge
>
That’s E

View Slide

>
git rebase master
M sqitch.plan
M sqitch.plan
>
>
Reemerge
>
Union merge

View Slide

>
git rebase master
M sqitch.plan
M sqitch.plan
>
>
Reemerge
>
Success!

View Slide

>
git rebase master
M sqitch.plan
M sqitch.plan
>
>
Reemerge
>
emacs sqitch.plan
>
Success!

View Slide

sqitch.plan
What’s the Plan, Man?
%project=flipr
!
appschema 2014-‐01-‐07T23:47:31Z David E. Wheeler
# Adds flipr app schema.
users [appschema] 2014-‐01-‐08T04:13:38Z David E. Wheeler
# Creates table to track our
users.
flips [appschema users] 2014-‐01-‐08T04:48:29Z David E.
Wheeler # Adds table for storing
flips.
insert_user [users appschema] 2014-‐01-‐08T04:52:50Z David
E. Wheeler # Creates a function to
insert a user.
change_pass [users appschema] 2014-‐01-‐08T05:23:28Z David

View Slide

sqitch.plan
What’s the Plan, Man?
%project=flipr
!
appschema 2014-‐01-‐07T23:47:31Z David E. Wheeler
# Adds flipr app schema.
users [appschema] 2014-‐01-‐08T04:13:38Z David E. Wheeler
# Creates table to track our
users.
flips [appschema users] 2014-‐01-‐08T04:48:29Z David E.
Wheeler # Adds table for storing
flips.
insert_user [users appschema] 2014-‐01-‐08T04:52:50Z David
insert a user.
change_pass [users appschema] 2014-‐01-‐08T05:23:28Z David
Perfect

View Slide

Work It
>

View Slide

> sqitch rebase -‐y
Reverting all changes from flipr_test
-‐ users ........ ok
-‐ appschema .... ok
+ appschema .... ok
+ users ........ ok
+ flips ........ ok
+ insert_user .. ok
+ change_pass .. ok
>
Work It
>

View Slide

-‐ users ........ ok
+ appschema .... ok
+ users ........ ok
+ flips ........ ok
+ insert_user .. ok
+ change_pass .. ok
>
Work It
>
{

View Slide

-‐ users ........ ok
+ appschema .... ok
+ users ........ ok
+ flips ........ ok
+ insert_user .. ok
+ change_pass .. ok
>
Work It
>
{
{

View Slide

-‐ users ........ ok
+ appschema .... ok
+ users ........ ok
+ flips ........ ok
+ insert_user .. ok
+ change_pass .. ok
>
Work It
>
{
{
Awesomsauce

View Slide

Make it So
>

View Slide

> git add .
>
Make it So
>

View Slide

> git add .
>
git commit -‐m 'Use union merge for `sqitch.plan`.'
[userfuncs f38ef5c] Use union merge for `sqitch.plan`.
create mode 100644 .gitattributes
>
Make it So
>

View Slide

> git add .
>
>
git push -‐-‐force origin userfuncs
+ 1813323...f38ef5c userfuncs -‐> userfuncs (forced upda
>
Make it So
> Overwrite
history

View Slide

> git add .
>
>
git push -‐-‐force origin userfuncs
+ 1813323...f38ef5c userfuncs -‐> userfuncs (forced upda
>
Make it So
>

View Slide

>

View Slide

> git checkout master
>
>

View Slide

git merge -‐-‐no-‐ff userfuncs -‐m 'Merge branch "userfuncs".'
.gitattributes | 1 +
deploy/change_pass.sql | 21 ++++++++++++++++++
deploy/insert_user.sql | 14 ++++++++++++
revert/change_pass.sql | 7 ++++++
revert/insert_user.sql | 7 ++++++
sqitch.plan | 2 ++
test/change_pass.sql | 52 ++++++++++++++++++++++++++++++++++
test/insert_user.sql | 69 ++++++++++++++++++++++++++++++++++
verify/change_pass.sql | 7 ++++++
verify/insert_user.sql | 10 +++++++++
create mode 100644 deploy/change_pass.sql
create mode 100644 revert/change_pass.sql
create mode 100644 test/change_pass.sql
create mode 100644 verify/change_pass.sql
>
>
>

View Slide

git merge -‐-‐no-‐ff userfuncs -‐m 'Merge branch "userfuncs".'
.gitattributes | 1 +
deploy/change_pass.sql | 21 ++++++++++++++++++
deploy/insert_user.sql | 14 ++++++++++++
revert/change_pass.sql | 7 ++++++
revert/insert_user.sql | 7 ++++++
sqitch.plan | 2 ++
test/change_pass.sql | 52 ++++++++++++++++++++++++++++++++++
test/insert_user.sql | 69 ++++++++++++++++++++++++++++++++++
verify/change_pass.sql | 7 ++++++
verify/insert_user.sql | 10 +++++++++
create mode 100644 deploy/change_pass.sql
create mode 100644 revert/change_pass.sql
create mode 100644 test/change_pass.sql
create mode 100644 verify/change_pass.sql
>
>
>
\O/

View Slide

Pusher
>

View Slide

git push
e1cfc5d..7c5937a master -‐> master
>
Pusher
>

View Slide

antisocial network

View Slide

Ship it!
antisocial network

View Slide

Ship Shape
antisocial network

View Slide

Ship Shape
Good work so far
antisocial network

View Slide

Ship Shape
Good work so far
People gonna ﬂip out
antisocial network

View Slide

Ship Shape
Good work so far
Let’s tag a dev release
antisocial network

View Slide

Ship Shape
Good work so far
Bundle it up
antisocial network

View Slide

Ship Shape
Good work so far
Bundle it up
And ship it
antisocial network

View Slide

You’re It
>

View Slide

sqitch tag v1.0.0-‐r1 -‐n 'Tag v1.0.0-‐r1.'
Tagged "change_pass" with @v1.0.0-‐r1
>
You’re It
>

View Slide

git commit -‐am 'Tag the database @v1.0.0-‐r1.'
[master dce7606] Tag the database @v1.0.0-‐r1.
>
>
You’re It
>

View Slide

git tag v1.0.0-‐r1 -‐am 'Tag v1.0.0-‐r1.'
>
>
>
You’re It
>
In sync.
In sync.

View Slide

>
>
>
git push
7c5937a..dce7606 master -‐> master
>
You’re It
>

View Slide

>
>
>
git push
7c5937a..dce7606 master -‐> master
>
git push -‐-‐tags
* [new tag] v1.0.0-‐r1 -‐> v1.0.0-‐r1
>
You’re It
>

View Slide

Bundle Up
>

View Slide

sqitch bundle -‐-‐dest-‐dir flipr-‐1.0.0-‐r1
Bundling into flipr-‐1.0.0-‐r1
Writing config
Writing plan
Writing scripts
+ appschema
+ users
+ flips
+ insert_user
+ change_pass @v1.0.0-‐r1
>
Bundle Up
>

View Slide

sqitch bundle -‐-‐dest-‐dir flipr-‐1.0.0-‐r1
Bundling into flipr-‐1.0.0-‐r1
Writing config
Writing plan
Writing scripts
+ appschema
+ users
+ flips
+ insert_user
+ change_pass @v1.0.0-‐r1
>
Bundle Up
>
{

View Slide

Bundled?
>

View Slide

> cd flipr-‐1.0.0-‐r1
>
Bundled?
>

View Slide

createdb flipr_qa
>
> cd flipr-‐1.0.0-‐r1
>
Bundled?
>

View Slide

createdb flipr_qa
>
> cd flipr-‐1.0.0-‐r1
>
Bundled?
>
sqitch deploy db:pg:flipr_qa
Adding registry tables to db:pg:flipr_qa
Deploying changes to db:pg:flipr_qa
+ appschema ............... ok
+ users ................... ok
+ flips ................... ok
+ insert_user ............. ok
+ change_pass @v1.0.0-‐r1 .. ok
>

View Slide

createdb flipr_qa
>
> cd flipr-‐1.0.0-‐r1
>
Bundled?
>
Ship it!
sqitch deploy db:pg:flipr_qa
Adding registry tables to db:pg:flipr_qa
Deploying changes to db:pg:flipr_qa
+ appschema ............... ok
+ users ................... ok
+ flips ................... ok
+ insert_user ............. ok
+ change_pass @v1.0.0-‐r1 .. ok
>

View Slide

antisocial network
Merge Madness

View Slide

antisocial network
Merge Madness
Merge everything

View Slide

antisocial network
Merge Madness
Merge everything
Back to master

View Slide

antisocial network
Merge Madness
Merge everything
Back to master
users

View Slide

antisocial network
Merge Madness
Merge everything
Back to master
users
ﬂips

View Slide

antisocial network
Merge Madness
Merge everything
Back to master
users
ﬂips
userfuncs

View Slide

antisocial network
Merge Madness
Merge everything
Back to master
users
ﬂips
userfuncs
Union merge
sqitch.plan

View Slide

antisocial network
Merge Madness
Merge everything
Back to master
users
ﬂips
userfuncs
Union merge
sqitch.plan
Bundle and ship

View Slide

antisocial network
Merge Madness
Merge everything
Back to master
users
ﬂips
userfuncs
Union merge
sqitch.plan
Bundle and ship
https:/
/github.com/

View Slide

Ruh-Roh
>

View Slide

Ruh-Roh
>
> git reset -‐-‐hard reltag
HEAD is now at dce7606 Tag the database @v1.0.0-‐r1.
>

View Slide

psql -‐d flipr_test -‐c "
SELECT flipr.insert_user('foo', 'secr3t'),
flipr.insert_user('bar', 'secr3t');
SELECT nickname, password FROM flipr.users;
"
nickname | password
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
foo | 9695da4dd567a19f9b92065f240c6725
bar | 9695da4dd567a19f9b92065f240c6725
(2 rows)
>
Ruh-Roh
>
>
Same
password

View Slide

"
nickname | password
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
foo | 9695da4dd567a19f9b92065f240c6725
bar | 9695da4dd567a19f9b92065f240c6725
(2 rows)
>
Ruh-Roh
>
>

View Slide

"
nickname | password
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
foo | 9695da4dd567a19f9b92065f240c6725
bar | 9695da4dd567a19f9b92065f240c6725
(2 rows)
>
Ruh-Roh
>
Not good.
>

View Slide

PGCryptonite
>

View Slide

sqitch add pgcrypto -‐n 'Loads pgcrypto extension.'
Adding deploy/pgcrypto.sql
Adding revert/pgcrypto.sql
Adding test/pgcrypto.sql
Adding verify/pgcrypto.sql
Added "pgcrypto" to sqitch.plan
>
PGCryptonite
>

View Slide

>
PGCryptonite
>
emacs deploy/pgcrpyto.sql
>

View Slide

deploy/pgcrypto
deploy/pgcrypto.sql
-‐-‐ Deploy pgcrypto
!
BEGIN;
!
!
!
COMMIT;

View Slide

deploy/pgcrypto
deploy/pgcrypto.sql
-‐-‐ Deploy pgcrypto
!
BEGIN;
!
!
!
COMMIT;
CREATE EXTENSION pgcrypto;

View Slide

> emacs deploy/pgcrpyto.sql
>
PGCryptonite
>

View Slide

> emacs deploy/pgcrpyto.sql
>
PGCryptonite
>
emacs verify/pgcrpyto.sql
>

View Slide

-‐-‐ Verify pgcrypto
!
BEGIN;
verify/pgcrypto
verify/pgcrypto.sql
COMMIT;

View Slide

-‐-‐ Verify pgcrypto
!
BEGIN;
verify/pgcrypto
verify/pgcrypto.sql
COMMIT;
SELECT 1/count(*) FROM pg_extension WHERE extname = 'pgcrypto';
SELECT has_function_privilege('crypt(text, text)', 'execute');
SELECT has_function_privilege('gen_salt(text)', 'execute');

View Slide

You Know the Drill
antisocial network

View Slide

You Know the Drill
Write revert script
antisocial network

View Slide

You Know the Drill
Write revert script
Add test
antisocial network

View Slide

You Know the Drill
Write revert script
Add test
Use has_function()
antisocial network

View Slide

You Know the Drill
Write revert script
Add test
Use has_function()
Commit
antisocial network

View Slide

You Know the Drill
Write revert script
Add test
Use has_function()
Commit
Push
antisocial network

View Slide

You Know the Drill
Write revert script
Add test
Use has_function()
Commit
Push
Modify the insert_user test
antisocial network

View Slide

>

View Slide

>
git diff test/insert_user.sql
diff -‐-‐git a/test/insert_user.sql b/test/insert_user.sql
index 225bb18..2a775a6 100644
-‐-‐-‐ a/test/insert_user.sql
+++ b/test/insert_user.sql
@@ -‐29,25 +29,25 @@ SELECT lives_ok(
'Insert a user'
);

-‐SELECT row_eq(
-‐ 'SELECT * FROM users',
-‐ ROW('theory', md5('foo'), NOW())::users,
-‐ 'The user should have been inserted'
-‐);
+SELECT ok( EXISTS(
+ SELECT 1 FROM flipr.users
+ WHERE nickname = 'theory'
+ AND password = crypt('foo', password)
+), 'The user should have been inserted' );

SELECT lives_ok(
);

View Slide

-‐SELECT bag_eq(
-‐ $$ VALUES
-‐ ('theory', md5('foo'), NOW()),
-‐ ('strongrrl', md5('w00t'), NOW())
-‐ $$,
-‐ 'Both users should be present'
-‐);
+SELECT is(COUNT(*)::INT, 2, 'There should be two users')
+ FROM flipr.users;
+
+SELECT ok( EXISTS(
+ SELECT 1 FROM flipr.users
+ WHERE nickname = 'strongrrl'
+ AND password = crypt('w00t', password)
+), 'The second user should have been inserted' );

SELECT throws_ok(

View Slide

@@ -‐56,14 +56,8 @@ SELECT throws_ok(
);

-‐SELECT bag_eq(
-‐ $$ VALUES
-‐ ('theory', md5('foo'), NOW()),
-‐ ('strongrrl', md5('w00t'), NOW())
-‐ $$,
-‐ 'Should still have just the two users'
-‐);
+SELECT is(COUNT(*)::INT, 2, 'Should still have two users')
+ FROM flipr.users;

SELECT finish();
ROLLBACK;
>

View Slide

FAIL
>

View Slide

FAIL
> pg_prove -‐d flipr_test test/insert_user.sql
# Failed test 7: "The user should have been inserted"
# Failed test 10: "The second user should have been inserted"
test/insert_user.sql .. Failed 2/12 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
test/insert_user.sql (Wstat: 0 Tests: 12 Failed: 2)
Failed tests: 7, 10
Result: FAIL
>

View Slide

FAIL
> pg_prove -‐d flipr_test test/insert_user.sql
# Failed test 7: "The user should have been inserted"
# Failed test 10: "The second user should have been inserted"
test/insert_user.sql .. Failed 2/12 subtests
Test Summary Report
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
test/insert_user.sql (Wstat: 0 Tests: 12 Failed: 2)
Failed tests: 7, 10
Result: FAIL
>
As expected.

View Slide

Back when discussing
traditional migration
systems, I said…
antisocial network

View Slide

Managing
procedures
is a PITA!
antisocial network

View Slide

Consider this Change
>

View Slide

git diff deploy
diff -‐-‐git a/deploy/insert_user.sql b/deploy/insert_user.sql
index eb30fed..5c28d02 100644
-‐-‐-‐ a/deploy/insert_user.sql
+++ b/deploy/insert_user.sql
@@ -‐8,7 +8,7 @@ CREATE OR REPLACE FUNCTION flipr.insert_user(
nickname TEXT,
password TEXT
-‐ INSERT INTO flipr.users VALUES($1, md5($2));
+ INSERT INTO flipr.users values($1, crypt($2, gen_salt('md5')));
$$;

COMMIT;
>
>

View Slide

git diff deploy
index eb30fed..5c28d02 100644
-‐-‐-‐ a/deploy/insert_user.sql
+++ b/deploy/insert_user.sql
nickname TEXT,
password TEXT
$$;

COMMIT;
>
>
Simple, right?

View Slide

Not So Much
antisocial network

View Slide

Not So Much
Copy insert_user.sql to new deploy ﬁle
antisocial network

View Slide

Not So Much
Change that new ﬁle
antisocial network

View Slide

Not So Much
Copy insert_user.sql to new revert ﬁle
antisocial network

View Slide

Not So Much
Test it
antisocial network

View Slide

Not So Much
Test it
Do the same for change_pass.sql
antisocial network

View Slide

Not So Much
Test it
Do the same for change_pass.sql
The problem with that…
antisocial network

View Slide

>

View Slide

> git diff HEAD^
diff -‐-‐git a/deploy/insert_user_crypt.sql b/deploy/insert_user_crypto.sql
new file mode 100644
index 0000000..fa8d0c6
-‐-‐-‐ /dev/null
+++ b/deploy/insert_user_crypt.sql
@@ -‐0,0 +1,8 @@
+-‐-‐ requires: users, appuser, pgcrypto
+
+CREATE OR REPLACE FUNCTION insert_user(
+ nickname TEXT,
+ password TEXT
+) RETURNS VOID LANGUAGE SQL AS $$
+ INSERT INTO users values($1, crypt($2, gen_salt('md5')));
+$$;
diff -‐-‐git a/revert/insert_user_crypt.sql b/revert/insert_user_crypto.sql
index 0000000..a7f4e31
+++ b/revert/insert_user_crypt.sql
@@ -‐0,0 +1,8 @@
+-‐-‐ requires: users, appuser
+
+ nickname TEXT,
+ password TEXT
+ INSERT INTO users values($1, md5($2));
+$$;
>

View Slide

> git diff HEAD^
diff -‐-‐git a/deploy/insert_user_crypt.sql b/deploy/insert_user_crypto.sql
index 0000000..fa8d0c6
+++ b/deploy/insert_user_crypt.sql
@@ -‐0,0 +1,8 @@
+-‐-‐ requires: users, appuser, pgcrypto
+
+ nickname TEXT,
+ password TEXT
+ INSERT INTO users values($1, crypt($2, gen_salt('md5')));
+$$;
diff -‐-‐git a/revert/insert_user_crypt.sql b/revert/insert_user_crypto.sql
index 0000000..a7f4e31
+++ b/revert/insert_user_crypt.sql
@@ -‐0,0 +1,8 @@
+-‐-‐ requires: users, appuser
+
+ nickname TEXT,
+ password TEXT
+ INSERT INTO users values($1, md5($2));
+$$;
>
Oy.

View Slide

Let Sqitch do the work.
antisocial network

View Slide

Rework It
>

View Slide

sqitch rework insert_user -‐r pgcrypto \
-‐n 'Changes insert_user to use pgcrypto.'
Added "insert_user [[email protected]‐r1 pgcrypto]" to
sqitch.plan.
Modify these files as appropriate:
* deploy/insert_user.sql
* revert/insert_user.sql
* verify/insert_user.sql
>
Rework It
>

View Slide

sqitch rework insert_user -‐r pgcrypto \
-‐n 'Changes insert_user to use pgcrypto.'
Added "insert_user [[email protected]‐r1 pgcrypto]" to
sqitch.plan.
Modify these files as appropriate:
* deploy/insert_user.sql
* revert/insert_user.sql
* verify/insert_user.sql
>
Rework It
>
Same ﬁles?

View Slide

Same Files?
>

View Slide

git status
# On branch master
# Changes not staged for commit:
# (use "git add ..." to update what will be committe
# (use "git checkout -‐-‐ ..." to discard changes in w
#
# modified: revert/insert_user.sql
# modified: sqitch.plan
# modified: test/insert_user.sql
#
# Untracked files:
# (use "git add ..." to include in what will be comm
#
# deploy/[email protected]‐r1.sql
# revert/[email protected]‐r1.sql
# verify/[email protected]‐r1.sql
no changes added to commit (use "git add" and/or "git commit
>
Same Files?
>

View Slide

git status
# On branch master
#
#
# Untracked files:
#
>
Same Files?
>
As of
@v1.0.0-r1

View Slide

git status
# On branch master
#
#
# Untracked files:
#
>
Same Files?
>
Previous deploy
becomes revert

View Slide

What’s the Diff?
>

View Slide

What’s the Diff?
git diff deploy/insert_user.sql
@@ -‐1,6 +1,7 @@
+-‐-‐ requires: pgcrypto

BEGIN;

nickname TEXT,
password TEXT
$$;

COMMIT;
>

View Slide

Send it Up!
>

View Slide

Send it Up!
> sqitch deploy
+ insert_user .. ok
>
>

View Slide

> psql -‐d flipr_test -‐c "
DELETE FROM flipr.users;
"
nickname | password
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
foo | $1$nKO47p03$YRXYTt4NoNncTThLyxzEq1
bar | $1$LbVUs/p.$LVbvPlkD8rJlixW2nS3WP0
(2 rows)
>
Send it Up!
> sqitch deploy
+ insert_user .. ok
>
>

View Slide

> psql -‐d flipr_test -‐c "
"
nickname | password
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
foo | $1$nKO47p03$YRXYTt4NoNncTThLyxzEq1
bar | $1$LbVUs/p.$LVbvPlkD8rJlixW2nS3WP0
(2 rows)
>
Send it Up!
> sqitch deploy
+ insert_user .. ok
>
>
\o/

View Slide

Can We Go Back?
>

View Slide

Can We Go Back?
>
>

View Slide

"
nickname | password
-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐
foo | 9695da4dd567a19f9b92065f240c6725
bar | 9695da4dd567a19f9b92065f240c6725
(2 rows)
Can We Go Back?
>
>

View Slide

Verify How?
>

View Slide

Verify How?
> emacs verify/insert_user.sql
>
>

View Slide

!
BEGIN;
'execute'
);
verify/insert_u
COMMIT;

View Slide

SELECT 1/COUNT(*)
FROM pg_catalog.pg_proc
WHERE proname = 'insert_user'
AND pg_get_functiondef(oid)
LIKE $$%crypt($2, gen_salt('md5'))%$$;
!
BEGIN;
'execute'
);
verify/insert_u
COMMIT;

View Slide

SELECT 1/COUNT(*)
FROM pg_catalog.pg_proc
WHERE proname = 'insert_user'
AND pg_get_functiondef(oid)
LIKE $$%crypt($2, gen_salt('md5'))%$$;
!
BEGIN;
'execute'
);
verify/insert_u
COMMIT;
Yeah,
compare
source.

View Slide

>
Let’s Go!

View Slide

>
Let’s Go!
> sqitch deploy
+ insert_user .. ok
>

View Slide

>
Let’s Go!
> sqitch deploy
+ insert_user .. ok
>
> psql -‐d flipr_test -‐c 'DELETE FROM flipr.users'
DELETE 2
>

View Slide

>
Let’s Go!
> sqitch deploy
+ insert_user .. ok
>
> psql -‐d flipr_test -‐c 'DELETE FROM flipr.users'
DELETE 2
>
pg_prove -‐d flipr_test test/insert_user.sql
test/insert_user.sql .. ok
Result: PASS
>
Shazam!

View Slide

Add, Commit, Push, Go
>

View Slide

git add .
> git commit -‐m 'Update insert_user to use pgcrypto.'
[master e4edde6] Update insert_user to use pgcrypto.
8 files changed, 64 insertions(+), 24 deletions(-‐)
create mode 100644 deploy/[email protected]‐r1.sql
create mode 100644 revert/[email protected]‐r1.sql
create mode 100644 verify/[email protected]‐r1.sql
>
>

View Slide

git add .
> git commit -‐m 'Update insert_user to use pgcrypto.'
[master e4edde6] Update insert_user to use pgcrypto.
8 files changed, 64 insertions(+), 24 deletions(-‐)
create mode 100644 deploy/[email protected]‐r1.sql
create mode 100644 revert/[email protected]‐r1.sql
create mode 100644 verify/[email protected]‐r1.sql
>
> git push
9a9f1da..e4edde6 master -‐> master
>
>

View Slide

antisocial network
Change It Up

View Slide

antisocial network
Change It Up
git reset --hard
insert_user2

View Slide

antisocial network
Change It Up
git reset --hard
insert_user2
Rework change_pass

View Slide

antisocial network
Change It Up
git reset --hard
insert_user2
Rework change_pass
Use pgcrypo

View Slide

antisocial network
Change It Up
git reset --hard
insert_user2
Rework change_pass
Use pgcrypo
Test ﬁrst!

View Slide

antisocial network
Change It Up
git reset --hard
insert_user2
Rework change_pass
Use pgcrypo
Test ﬁrst!
Bundle, tag, release

View Slide

antisocial network
Change It Up
git reset --hard
insert_user2
Rework change_pass
Use pgcrypo
Test ﬁrst!
Bundle, tag, release
https:/
/github.com/

View Slide

I’m afraid I have some
bad news…
antisocial network

View Slide

View Slide

Antisocial Networking Startup Flipr Heads To
The Deadpool
by Michael Arrington on November 2, 2010
antisocial network
I loved this site.
Flipr, an online “antisocial networking” site that encouraged users to alienate
each other in order to increase their antisocial cred, is shutting down. The
startup’s homepage now consists of a letter to Flipr users instructing them to
download their “ﬂips” by November 30, at which point nearly all of the
service’s features will be taken offline and data deleted.
In the letter, Flipr CEO David Wheeler writes that despite ample venture funding and a dedicated
team of database developers, the site underestimated people’s willingness to be assholes. This is
not something I can relate to, although from what I’ve been told by more polite society, it is
indeed the case. Such a shame.

View Slide

I’m afraid it’s true.
antisocial network
RIP

View Slide

I’m sorry I have no money
left to pay you.
antisocial network
RIP

View Slide

I hope you enjoyed
working here.
antisocial network
RIP

View Slide

And that you’re able to
use the skills you’ve
gained in your next job.
antisocial network
RIP

View Slide

Git Skillz
antisocial network
RIP

View Slide

Git Skillz
Branching
antisocial network
RIP

View Slide

Git Skillz
Branching
Difﬁng
antisocial network
RIP

View Slide

Git Skillz
Branching
Difﬁng
Rebasing
antisocial network
RIP

View Slide

Git Skillz
Branching
Difﬁng
Rebasing
Merging
antisocial network
RIP

View Slide

Git Skillz
Branching
Difﬁng
Rebasing
Merging
Committing
antisocial network
RIP

View Slide

Git Skillz
Branching
Difﬁng
Rebasing
Merging
Committing
Pushing
antisocial network
RIP

View Slide

MOAR Git
antisocial network
RIP

View Slide

MOAR Git
Bisecting
antisocial network
RIP

View Slide

MOAR Git
Bisecting
Blaming
antisocial network
RIP

View Slide

MOAR Git
Bisecting
Blaming
Pull requests
antisocial network
RIP

View Slide

MOAR Git
Bisecting
Blaming
Pull requests
Submitting patches
antisocial network
RIP

View Slide

MOAR Git
Bisecting
Blaming
Pull requests
Submitting patches
Rewriting history
antisocial network
RIP

View Slide

MOAR Git
Bisecting
Blaming
Pull requests
Submitting patches
Rewriting history
Log formatting
antisocial network
RIP

View Slide

MOAR Git
Bisecting
Blaming
Pull requests
Submitting patches
Rewriting history
Log formatting
git help --all
antisocial network
RIP

View Slide

pgTAP Skillz
antisocial network
RIP

View Slide

pgTAP Skillz
TDDD
antisocial network
RIP

View Slide

pgTAP Skillz
TDDD
Schema testing
antisocial network
RIP

View Slide

pgTAP Skillz
TDDD
Schema testing
Scalar testing
antisocial network
RIP

View Slide

pgTAP Skillz
TDDD
Schema testing
Scalar testing
Functional testing
antisocial network
RIP

View Slide

pgTAP Skillz
TDDD
Schema testing
Scalar testing
Functional testing
Relational testing
antisocial network
RIP

View Slide

MOAR pgTAP
antisocial network
RIP

View Slide

MOAR pgTAP
Testing privileges
antisocial network
RIP

View Slide

MOAR pgTAP
Testing privileges
Mocking interfaces
antisocial network
RIP

View Slide

MOAR pgTAP
Testing privileges
Mocking interfaces
Custom test functions
antisocial network
RIP

View Slide

MOAR pgTAP
Testing privileges
Mocking interfaces
xUnit-style testing
antisocial network
RIP

View Slide

MOAR pgTAP
Testing privileges
Mocking interfaces
xUnit-style testing
Tests maintained in functions
antisocial network
RIP

View Slide

MOAR pgTAP
Testing privileges
Mocking interfaces
xUnit-style testing
Tests maintained in functions
http:/
/pgtap.org/documentation.html
antisocial network
RIP

View Slide

Sqitch Skillz
antisocial network
RIP

View Slide

Sqitch Skillz
Add changes
antisocial network
RIP

View Slide

Sqitch Skillz
Add changes
Specify Dependencies
antisocial network
RIP

View Slide

Sqitch Skillz
Add changes
Manage targets
antisocial network
RIP

View Slide

Sqitch Skillz
Add changes
Manage targets
Deploy, Verify, Revert changes
antisocial network
RIP

View Slide

Sqitch Skillz
Add changes
Manage targets
Rebase changes
antisocial network
RIP

View Slide

Sqitch Skillz
Add changes
Manage targets
Rebase changes
Rework changes
antisocial network
RIP

View Slide

Sqitch Skillz
Add changes
Manage targets
Rebase changes
Rework changes
Changing branches
antisocial network
RIP

View Slide

MOAR Sqitch
antisocial network
RIP

View Slide

MOAR Sqitch
Cross-project dependencies
antisocial network
RIP

View Slide

MOAR Sqitch
Multiple projects, one database
antisocial network
RIP

View Slide

MOAR Sqitch
Script templating
antisocial network
RIP

View Slide

MOAR Sqitch
Script templating
Variables, loops, conditionals
antisocial network
RIP

View Slide

MOAR Sqitch
Script templating
Logging
antisocial network
RIP

View Slide

MOAR Sqitch
Script templating
Logging
Formatting output
antisocial network
RIP

View Slide

MOAR Sqitch
Script templating
Logging
Formatting output
sqitch help
antisocial network
RIP

View Slide

Good luck out there.
antisocial network
RIP

View Slide

Agile
David E. Wheeler
!
!
!
January 2014
Portland
http:/
iovation

View Slide

Agile Database Development 2ed

Agile Database Development 2ed

David E. Wheeler

More Decks by David E. Wheeler

Other Decks in Technology

Featured

Transcript