Agile Database Development 2ed

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

Slide 4

Slide 4 text

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.

Slide 5

Slide 5 text

David E. Wheeler ! ! ! January 2014 Portland License: Attribution-Noncommercial-Share Alike 3.0 United States: http:/ /creativecommons.org/licenses/by-nc-sa/3.0/us/

Slide 6

Slide 6 text

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:/ /creativecommons.org/licenses/by-nc-sa/3.0/us/ ✔ CEO, Data Architect Agile Assholes, Inc.

Slide 7

Slide 7 text

This is Genius

Slide 8

Slide 8 text

This is Genius I had this idea

Slide 9

Slide 9 text

This is Genius I had this idea Social networking is HAWT

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

This is Genius I had this idea Social networking is HAWT Has been for waaaay too long The backlash is long overdue

Slide 12

Slide 12 text

This is Genius I had this idea Social networking is HAWT Has been for waaaay too long The backlash is long overdue Getting ahead of the curve

Slide 13

Slide 13 text

This is Genius I had this idea Social networking is HAWT Has been for waaaay too long The backlash is long overdue Getting ahead of the curve Introducing…

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

How it Works antisocial network

Slide 17

Slide 17 text

How it Works Microblogging platform antisocial network

Slide 18

Slide 18 text

How it Works Microblogging platform Everyone follows you antisocial network

Slide 19

Slide 19 text

How it Works Microblogging platform Everyone follows you New users follow everyone antisocial network

Slide 20

Slide 20 text

How it Works Microblogging platform Everyone follows you New users follow everyone Goal: Alienate your followers antisocial network

Slide 21

Slide 21 text

How it Works Microblogging platform Everyone follows you New users follow everyone Goal: Alienate your followers Get them to unfollow you antisocial network

Slide 22

Slide 22 text

How it Works Microblogging platform Everyone follows you New users follow everyone Goal: Alienate your followers Get them to unfollow you Leaderboard: Those with fewest followers antisocial network

Slide 23

Slide 23 text

Your Task antisocial network

Slide 24

Slide 24 text

Your Task Create the database antisocial network

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

Your Task Create the database Use agile development to make it right Contribute to VC-funded Corp antisocial network

Slide 27

Slide 27 text

Your Task Create the database Use agile development to make it right Contribute to VC-funded Corp Proﬁt! antisocial network

Slide 28

Slide 28 text

Your Task Create the database Use agile development to make it right Contribute to VC-funded Corp Proﬁt! For VC-funded Corp antisocial network

Slide 29

Slide 29 text

Slide 30

Slide 30 text

But ﬁrst… antisocial network

Slide 31

Slide 31 text

NDA antisocial network

Slide 32

Slide 32 text

Job Requirements antisocial network

Slide 33

Slide 33 text

Job Requirements PostgreSQL Should be installed antisocial network

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

We good? antisocial network

Slide 39

Slide 39 text

Let’s do this thang. antisocial network

Slide 40

Slide 40 text

> Who Am I?

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

git config -‐-‐global user.name 'David E. Wheeler' > git config -‐-‐global user.email [email protected] > > Who Am I? Please use your own name and email.

Slide 46

Slide 46 text

git config -‐-‐global user.name 'David E. Wheeler' > git config -‐-‐global user.email [email protected] > > Who Am I? emacs ~/.gitconfig >

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

Create a Remote

Slide 50

Slide 50 text

Create a Remote Create Git project in Stash

Slide 51

Slide 51 text

Create a Remote Create Git project in Stash Or Git or BitBucket

Slide 52

Slide 52 text

Create a Remote Create Git project in Stash Or Git or BitBucket Wherever you like

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

Gitiup >

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

mkdir flipr-‐db > cd flipr-‐db > git init Initialized empty Git repository in flipr-‐db/.git/ > Gitiup > echo Flipr Database Project > README.md >

Slide 58

Slide 58 text

mkdir flipr-‐db > cd flipr-‐db > git init Initialized empty Git repository in flipr-‐db/.git/ > Gitiup > echo Flipr Database Project > README.md > git add . >

Slide 59

Slide 59 text

mkdir flipr-‐db > cd flipr-‐db > git init Initialized empty Git repository in flipr-‐db/.git/ > Gitiup > echo Flipr Database Project > README.md > 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 >

Slide 60

Slide 60 text

Slide 61

Slide 61 text

Slide 62

Slide 62 text

> Origin

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

Slide 66

Slide 66 text

Slide 67

Slide 67 text

Slide 68

Slide 68 text

Swimming Upstream >

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

git remote add upstream \ https://github.com/theory/agile-‐flipr.git > 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 From https://github.com/theory/agile-‐flipr * [new tag] insert_user2 -‐> insert_user2 * [new tag] reltag -‐> reltag * [new tag] v1.0.0-‐r1 -‐> v1.0.0-‐r1 >

Slide 71

Slide 71 text

Slide 72

Slide 72 text

Git? antisocial network

Slide 73

Slide 73 text

Git? Manage tree of ﬁles over time antisocial network

Slide 74

Slide 74 text

Git? Manage tree of ﬁles over time Distributed development antisocial network

Slide 75

Slide 75 text

Git? Manage tree of ﬁles over time Distributed development Commit changes locally antisocial network

Slide 76

Slide 76 text

Git? Manage tree of ﬁles over time Distributed development Commit changes locally Merge to remote (origin, upstream) antisocial network

Slide 77

Slide 77 text

Git? Manage tree of ﬁles over time Distributed development Commit changes locally Merge to remote (origin, upstream) Speedy, responsive antisocial network

Slide 78

Slide 78 text

Git? Manage tree of ﬁles over time Distributed development Commit changes locally Merge to remote (origin, upstream) Speedy, responsive Flexible, robust antisocial network

Slide 79

Slide 79 text

Why Git? antisocial network

Slide 80

Slide 80 text

Why Git? Anyone can clone antisocial network

Slide 81

Slide 81 text

Why Git? Anyone can clone Complete repository copy antisocial network

Slide 82

Slide 82 text

Why Git? Anyone can clone Complete repository copy Cheap branching antisocial network

Slide 83

Slide 83 text

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

Slide 84

Slide 84 text

Why Git? Anyone can clone Complete repository copy Cheap branching Make and test local changes Submit patches, pull requests antisocial network

Slide 85

Slide 85 text

Why Git? Anyone can clone Complete repository copy Cheap branching Make and test local changes Submit patches, pull requests Pull from upstream antisocial network

Slide 86

Slide 86 text

PiSHA1 antisocial network

Slide 87

Slide 87 text

PiSHA1 SHA1 ID for every object antisocial network

Slide 88

Slide 88 text

PiSHA1 SHA1 ID for every object commit, tag, tree, blob antisocial network

Slide 89

Slide 89 text

PiSHA1 SHA1 ID for every object commit, tag, tree, blob Hashed commit text includes: antisocial network

Slide 90

Slide 90 text

PiSHA1 SHA1 ID for every object commit, tag, tree, blob Hashed commit text includes: tree ID antisocial network

Slide 91

Slide 91 text

PiSHA1 SHA1 ID for every object commit, tag, tree, blob Hashed commit text includes: tree ID parent ID antisocial network

Slide 92

Slide 92 text

PiSHA1 SHA1 ID for every object commit, tag, tree, blob Hashed commit text includes: tree ID parent ID author antisocial network

Slide 93

Slide 93 text

PiSHA1 SHA1 ID for every object commit, tag, tree, blob Hashed commit text includes: tree ID parent ID author committer antisocial network

Slide 94

Slide 94 text

PiSHA1 SHA1 ID for every object commit, tag, tree, blob Hashed commit text includes: tree ID parent ID author committer message antisocial network

Slide 95

Slide 95 text

> Making a hash of it

Slide 96

Slide 96 text

> 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

Slide 97

Slide 97 text

Slide 98

Slide 98 text

Slide 99

Slide 99 text

Slide 100

Slide 100 text

Slide 101

Slide 101 text

Slide 102

Slide 102 text

SHAzam! antisocial network

Slide 103

Slide 103 text

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

Slide 104

Slide 104 text

SHAzam! Each commit (except ﬁrst) includes parent Can trace from any commit to the beginning antisocial network

Slide 105

Slide 105 text

SHAzam! Each commit (except ﬁrst) includes parent Can trace from any commit to the beginning Tampering (corruption) detectable antisocial network

Slide 106

Slide 106 text

SHAzam! Each commit (except ﬁrst) includes parent Can trace from any commit to the beginning Tampering (corruption) detectable Because the hash will be wrong antisocial network

Slide 107

Slide 107 text

SHAzam! Each commit (except ﬁrst) includes parent Can trace from any commit to the beginning Tampering (corruption) detectable Because the hash will be wrong Linus Torvalds’s “greatest invention” http:/ /perl.plover.com/yak/git/ antisocial network

Slide 108

Slide 108 text

antisocial network Your Turn

Slide 109

Slide 109 text

antisocial network Your Turn Conﬁgure Git

Slide 110

Slide 110 text

antisocial network Your Turn Conﬁgure Git Initialize repository

Slide 111

Slide 111 text

antisocial network Your Turn Conﬁgure Git Initialize repository Add origin remote

Slide 112

Slide 112 text

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

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

Who am I again? >

Slide 115

Slide 115 text

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

Slide 116

Slide 116 text

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

Slide 117

Slide 117 text

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

Slide 118

Slide 118 text

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

Slide 119

Slide 119 text

~/.sqitch/sqitc ~/ .sqitch/sqitch.conf [user] name = David E. Wheeler email = [email protected]

Slide 120

Slide 120 text

~/.sqitch/sqitc ~/ .sqitch/sqitch.conf [user] name = David E. Wheeler email = [email protected] Good for all projects

Slide 121

Slide 121 text

> Scratch that Sqitch

Slide 122

Slide 122 text

> 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

Slide 123

Slide 123 text

Slide 124

Slide 124 text

Slide 125

Slide 125 text

Slide 126

Slide 126 text

Slide 127

Slide 127 text

Slide 128

Slide 128 text

Slide 129

Slide 129 text

Slide 130

Slide 130 text

Slide 131

Slide 131 text

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

Slide 132

Slide 132 text

Slide 133

Slide 133 text

What’s the Plan Man? >

Slide 134

Slide 134 text

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

Slide 135

Slide 135 text

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

Slide 136

Slide 136 text

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

Slide 137

Slide 137 text

Make It So >

Slide 138

Slide 138 text

Slide 139

Slide 139 text

Slide 140

Slide 140 text

Slide 141

Slide 141 text

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 > git push Counting objects: 5, done. Delta compression using up to 4 threads. Compressing objects: 100% (4/4), done. Writing objects: 100% (4/4), 555 bytes, done. Total 4 (delta 0), reused 0 (delta 0) To https://github.com/theory/agile-‐flipr.git 9531fab..e56e7c8 master -‐> master >

Slide 142

Slide 142 text

Slide 143

Slide 143 text

Slide 144

Slide 144 text

Where’ve We Been? >

Slide 145

Slide 145 text

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? >

Slide 146

Slide 146 text

Slide 147

Slide 147 text

Slide 148

Slide 148 text

First Change >

Slide 149

Slide 149 text

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 > >

Slide 150

Slide 150 text

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 > >

Slide 151

Slide 151 text

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 > >

Slide 152

Slide 152 text

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 > > emacs deploy/appschema.sql >

Slide 153

Slide 153 text

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

Slide 154

Slide 154 text

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

Slide 155

Slide 155 text

> 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 > First Change > emacs deploy/appschema.sql >

Slide 156

Slide 156 text

Slide 157

Slide 157 text

revert/appschem revert/appschema.sql -‐-‐ Revert appschema ! BEGIN; ! ! ! COMMIT; -‐-‐ XXX Add DDLs here.

Slide 158

Slide 158 text

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

Slide 159

Slide 159 text

Make it So! >

Slide 160

Slide 160 text

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

Slide 161

Slide 161 text

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

Slide 162

Slide 162 text

Slide 163

Slide 163 text

Slide 164

Slide 164 text

Slide 165

Slide 165 text

Slide 166

Slide 166 text

createdb flipr_test > createuser -‐s -‐U postgres `whoami` > 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! psql -‐d flipr_test -‐c '\dn flipr' List of schemas Name | Owner -‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐ flipr | david > >

Slide 167

Slide 167 text

Slide 168

Slide 168 text

Slide 169

Slide 169 text

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

Slide 170

Slide 170 text

-‐-‐ Verify appschema ! BEGIN; ! ! ! ROLLBACK; SELECT pg_catalog.has_schema_privilege('nada', 'usage'); verify/appschem verify/appschema.sql

Slide 171

Slide 171 text

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

Slide 172

Slide 172 text

emacs verify/appschema.sql > Trust, But Verify >

Slide 173

Slide 173 text

emacs verify/appschema.sql > 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 >

Slide 174

Slide 174 text

Slide 175

Slide 175 text

Slide 176

Slide 176 text

Slide 177

Slide 177 text

Trust, But Verify >

Slide 178

Slide 178 text

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

Slide 179

Slide 179 text

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.

Slide 180

Slide 180 text

> How’s it Look?

Slide 181

Slide 181 text

> 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)

Slide 182

Slide 182 text

Slide 183

Slide 183 text

Slide 184

Slide 184 text

Slide 185

Slide 185 text

Slide 186

Slide 186 text

Slide 187

Slide 187 text

Go Back >

Slide 188

Slide 188 text

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

Slide 189

Slide 189 text

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

Slide 190

Slide 190 text

> sqitch revert db:pg:flipr_test Revert all changes from db:pg:flipr_test? [Yes] Go Back > -‐ appschema .. ok >

Slide 191

Slide 191 text

> sqitch revert db:pg:flipr_test Revert all changes from db:pg:flipr_test? [Yes] Go Back > psql -‐d flipr_test -‐c '\dn flipr' List of roles List of schemas Name | Owner -‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐ > -‐ appschema .. ok >

Slide 192

Slide 192 text

Slide 193

Slide 193 text

History >

Slide 194

Slide 194 text

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.

Slide 195

Slide 195 text

Slide 196

Slide 196 text

Slide 197

Slide 197 text

Commit It! >

Slide 198

Slide 198 text

Slide 199

Slide 199 text

Slide 200

Slide 200 text

Slide 201

Slide 201 text

Commit It! > git add . > git commit -‐m 'Add flipr schema.' [master 07548c2] Add flipr schema. 4 files changed, 22 insertions(+) create mode 100644 deploy/appschema.sql create mode 100644 revert/appschema.sql create mode 100644 verify/appschema.sql > git push Counting objects: 11, done. Delta compression using up to 4 threads. Compressing objects: 100% (6/6), done. Writing objects: 100% (9/9), 950 bytes, done. Total 9 (delta 0), reused 0 (delta 0) To https://github.com/theory/agile-‐flipr.git e56e7c8..07548c2 master -‐> master

Slide 202

Slide 202 text

Slide 203

Slide 203 text

Redeploy >

Slide 204

Slide 204 text

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

Slide 205

Slide 205 text

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

Slide 206

Slide 206 text

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

Slide 207

Slide 207 text

> sqitch deploy db:pg:flipr_test -‐-‐verify Deploying changes to flipr_test + appschema .. ok > Redeploy > psql -‐d flipr_test -‐c '\dn flipr' List of schemas Name | Owner -‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐ flipr | david >

Slide 208

Slide 208 text

Status Update >

Slide 209

Slide 209 text

> sqitch status db:pg:flipr_test # On database db:pg:flipr_test # Project: flipr # Change: e01feba4bb79607339a13981574855b6e2aa7526 # Name: appschema # Deployed: 2014-‐01-‐07 16:13:50 -‐0800 # By: David E. Wheeler # Nothing to deploy (up-‐to-‐date) > Status Update >

Slide 210

Slide 210 text

On Target >

Slide 211

Slide 211 text

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

Slide 212

Slide 212 text

On Target > sqitch target add flipr_test db:pg:flipr_test > sqitch config core.pg.target flipr_test >

Slide 213

Slide 213 text

On Target > sqitch target add flipr_test db:pg:flipr_test > sqitch config core.pg.target flipr_test > > sqitch status # On database flipr_test # Project: flipr # Change: e01feba4bb79607339a13981574855b6e2aa7526 # Name: appschema # Deployed: 2014-‐01-‐07 16:13:50 -‐0800 # By: David E. Wheeler # Nothing to deploy (up-‐to-‐date) > No URI

Slide 214

Slide 214 text

On Target > sqitch target add flipr_test db:pg:flipr_test > sqitch config core.pg.target flipr_test > > sqitch config -‐-‐bool deploy.verify true > sqitch config -‐-‐bool rebase.verify true > > sqitch status # On database flipr_test # Project: flipr # Change: e01feba4bb79607339a13981574855b6e2aa7526 # Name: appschema # Deployed: 2014-‐01-‐07 16:13:50 -‐0800 # By: David E. Wheeler # Nothing to deploy (up-‐to-‐date) > Always verify.

Slide 215

Slide 215 text

Commit Conﬁg >

Slide 216

Slide 216 text

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(+) >

Slide 217

Slide 217 text

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(+) >

Slide 218

Slide 218 text

Commit Conﬁg > git push Counting objects: 5, done. Delta compression using up to 4 threads. Compressing objects: 100% (3/3), done. Writing objects: 100% (3/3), 361 bytes, done. Total 3 (delta 2), reused 0 (delta 0) To https://github.com/theory/agile-‐flipr.git 07548c2..099b677 master -‐> master > git add . > git commit -‐m 'Add target and verify to config.' [master 099b677] Add target and verify to config. 1 file changed, 4 insertions(+) >

Slide 219

Slide 219 text

Not Migrations? antisocial network

Slide 220

Slide 220 text

Not Migrations? Incomplete mini-language antisocial network

Slide 221

Slide 221 text

Not Migrations? Incomplete mini-language No logical replication integration antisocial network

Slide 222

Slide 222 text

Not Migrations? Incomplete mini-language No logical replication integration Numbered scripts difﬁcult to track antisocial network

Slide 223

Slide 223 text

Not Migrations? Incomplete mini-language No logical replication integration Numbered scripts difﬁcult to track No VCS awareness antisocial network

Slide 224

Slide 224 text

SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness antisocial network

Slide 225

Slide 225 text

SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness ——————————————— antisocial network

Slide 226

Slide 226 text

SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness ——————————————— ——————————————————— antisocial network

Slide 227

Slide 227 text

Slide 228

Slide 228 text

Sq—what? sql anges ch antisocial network

Slide 229

Slide 229 text

Sq—what? sq ch antisocial network

Slide 230

Slide 230 text

it Sq—what? sq ch antisocial network

Slide 231

Slide 231 text

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

Slide 232

Slide 232 text

Sqitch Philosophy antisocial network

Slide 233

Slide 233 text

Sqitch Philosophy No opinions antisocial network

Slide 234

Slide 234 text

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

Slide 235

Slide 235 text

Sqitch Philosophy No opinions Native scripting (psql, sqlite3, SQL*Plus) Cross-project dependency resolution antisocial network

Slide 236

Slide 236 text

Sqitch Philosophy No opinions Native scripting (psql, sqlite3, SQL*Plus) Cross-project dependency resolution Distribution bundling antisocial network

Slide 237

Slide 237 text

Sqitch Philosophy No opinions Native scripting (psql, sqlite3, SQL*Plus) Cross-project dependency resolution Distribution bundling Integrated veriﬁcation testing antisocial network

Slide 238

Slide 238 text

Sqitch Philosophy No opinions Native scripting (psql, sqlite3, SQL*Plus) Cross-project dependency resolution Distribution bundling Integrated veriﬁcation testing No numbering antisocial network

Slide 239

Slide 239 text

Sqitch Philosophy No opinions Native scripting (psql, sqlite3, SQL*Plus) Cross-project dependency resolution Distribution bundling Integrated veriﬁcation testing No numbering Reliable sequential deployment ordering antisocial network

Slide 240

Slide 240 text

SHAzbat antisocial network

Slide 241

Slide 241 text

SHAzbat SHA1 ID for every object antisocial network

Slide 242

Slide 242 text

SHAzbat SHA1 ID for every object Stolen from Git antisocial network

Slide 243

Slide 243 text

SHAzbat SHA1 ID for every object Stolen from Git change, tag, deploy, revert, verify antisocial network

Slide 244

Slide 244 text

SHAzbat SHA1 ID for every object Stolen from Git change, tag, deploy, revert, verify Hashed change text includes: antisocial network

Slide 245

Slide 245 text

SHAzbat SHA1 ID for every object Stolen from Git change, tag, deploy, revert, verify Hashed change text includes: Project antisocial network

Slide 246

Slide 246 text

SHAzbat SHA1 ID for every object Stolen from Git change, tag, deploy, revert, verify Hashed change text includes: Project Name antisocial network

Slide 247

Slide 247 text

SHAzbat SHA1 ID for every object Stolen from Git change, tag, deploy, revert, verify Hashed change text includes: Project Name Parent ID antisocial network

Slide 248

Slide 248 text

SHAzbat SHA1 ID for every object Stolen from Git change, tag, deploy, revert, verify Hashed change text includes: Project Name Parent ID Planner… antisocial network

Slide 249

Slide 249 text

SHAsome >

Slide 250

Slide 250 text

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. >

Slide 251

Slide 251 text

Slide 252

Slide 252 text

Slide 253

Slide 253 text

Slide 254

Slide 254 text

Slide 255

Slide 255 text

Slide 256

Slide 256 text

Slide 257

Slide 257 text

Slide 258

Slide 258 text

Slide 259

Slide 259 text

Slide 260

Slide 260 text

Slide 261

Slide 261 text

SHApay! antisocial network

Slide 262

Slide 262 text

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

Slide 263

Slide 263 text

SHApay! Each change (except ﬁrst) includes parent Can trace from any change to the beginning antisocial network

Slide 264

Slide 264 text

SHApay! Each change (except ﬁrst) includes parent Can trace from any change to the beginning Change tampering (corruption) detectable antisocial network

Slide 265

Slide 265 text

SHApay! Each change (except ﬁrst) includes parent Can trace from any change to the beginning Change tampering (corruption) detectable Because the hash will be wrong antisocial network

Slide 266

Slide 266 text

SHApay! Each change (except ﬁrst) includes parent Can trace from any change to the beginning Change tampering (corruption) detectable Because the hash will be wrong Stole Linus Torvalds’s “greatest invention” antisocial network

Slide 267

Slide 267 text

Sqitch Features antisocial network

Slide 268

Slide 268 text

Sqitch Features Reduced duplication antisocial network

Slide 269

Slide 269 text

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

Slide 270

Slide 270 text

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

Slide 271

Slide 271 text

Sqitch Features Reduced duplication Built-in conﬁguration Iterative development Targeted deployment antisocial network

Slide 272

Slide 272 text

Sqitch Features Reduced duplication Built-in conﬁguration Iterative development Targeted deployment Git-style interface antisocial network

Slide 273

Slide 273 text

Sqitch Features Reduced duplication Built-in conﬁguration Iterative development Targeted deployment Git-style interface Deployment tagging antisocial network

Slide 274

Slide 274 text

antisocial network Your Turn

Slide 275

Slide 275 text

antisocial network Your Turn Conﬁgure Sqitch

Slide 276

Slide 276 text

antisocial network Your Turn Conﬁgure Sqitch Initialize project

Slide 277

Slide 277 text

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

Slide 278

Slide 278 text

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

Slide 279

Slide 279 text

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

Slide 280

Slide 280 text

antisocial network Your Turn Conﬁgure Sqitch Initialize project Add appschema change Deploy/Revert Commit/push https:/ /github.com/ theory/agile-ﬂipr.git

Slide 281

Slide 281 text

Add Tests >

Slide 282

Slide 282 text

Add Tests > mkdir test >

Slide 283

Slide 283 text

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

Slide 284

Slide 284 text

test/appschema. pgTAP Basics

Slide 285

Slide 285 text

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;

Slide 286

Slide 286 text

Slide 287

Slide 287 text

Slide 288

Slide 288 text

Slide 289

Slide 289 text

Slide 290

Slide 290 text

Slide 291

Slide 291 text

Slide 292

Slide 292 text

> Run the Test

Slide 293

Slide 293 text

> 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

Slide 294

Slide 294 text

Slide 295

Slide 295 text

Slide 296

Slide 296 text

First Pass >

Slide 297

Slide 297 text

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

Slide 298

Slide 298 text

perl -‐i -‐pe 's/nada/flipr/' test/appschema.sql > First Pass > > pg_prove -‐v -‐d flipr_test test/appschema.sql test/appschema.sql .. 1..1 ok 1 -‐ Schema flipr should exist ok All tests successful. Files=1, Tests=1, 0 wallclock secs Result: PASS

Slide 299

Slide 299 text

perl -‐i -‐pe 's/nada/flipr/' test/appschema.sql > First Pass W00t! > > pg_prove -‐v -‐d flipr_test test/appschema.sql test/appschema.sql .. 1..1 ok 1 -‐ Schema flipr should exist ok All tests successful. Files=1, Tests=1, 0 wallclock secs Result: PASS

Slide 300

Slide 300 text

Pass it On >

Slide 301

Slide 301 text

git add . > Pass it On >

Slide 302

Slide 302 text

git add . > git commit -‐m 'Add appschema test.' [master 92bd85c] Add appschema test. 1 file changed, 14 insertions(+) create mode 100644 test/appschema.sql > Pass it On >

Slide 303

Slide 303 text

git add . > git commit -‐m 'Add appschema test.' [master 92bd85c] Add appschema test. 1 file changed, 14 insertions(+) create mode 100644 test/appschema.sql > Pass it On > git push Counting objects: 5, done. Delta compression using up to 4 threads. Compressing objects: 100% (3/3), done. Writing objects: 100% (4/4), 486 bytes, done. Total 4 (delta 1), reused 0 (delta 0) To ssh://[email protected]:7999/~david.w 099b677..c965fb6 master -‐> master >

Slide 304

Slide 304 text

No content

Slide 305

Slide 305 text

OMG TAP WTF?

Slide 306

Slide 306 text

What is TAP? antisocial network

Slide 307

Slide 307 text

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

Slide 308

Slide 308 text

What does that mean in practice? Test output easy to interpret What is TAP? antisocial network

Slide 309

Slide 309 text

What does that mean in practice? Test output easy to interpret By humans What is TAP? antisocial network

Slide 310

Slide 310 text

What does that mean in practice? Test output easy to interpret By humans By computers What is TAP? antisocial network

Slide 311

Slide 311 text

What does that mean in practice? Test output easy to interpret By humans By computers pg_prove (the harness) What is TAP? antisocial network

Slide 312

Slide 312 text

What does that mean in practice? Test output easy to interpret By humans By computers pg_prove (the harness) By aliens What is TAP? antisocial network

Slide 313

Slide 313 text

What does that mean in practice? Test output easy to interpret By humans By computers pg_prove (the harness) By aliens By gum! What is TAP? antisocial network

Slide 314

Slide 314 text

What’s the plan, man? antisocial network

Slide 315

Slide 315 text

What’s the plan, man? Includes Test controls: antisocial network

Slide 316

Slide 316 text

What’s the plan, man? Includes Test controls: plan() — How many tests? antisocial network

Slide 317

Slide 317 text

What’s the plan, man? Includes Test controls: plan() — How many tests? no_plan() — Unknown number of tests antisocial network

Slide 318

Slide 318 text

What’s the plan, man? Includes Test controls: plan() — How many tests? no_plan() — Unknown number of tests diag() — Diagnostic output antisocial network

Slide 319

Slide 319 text

What’s the plan, man? Includes Test controls: plan() — How many tests? no_plan() — Unknown number of tests diag() — Diagnostic output ﬁnish() — Test ﬁnished, report! antisocial network

Slide 320

Slide 320 text

Scalarly antisocial network

Slide 321

Slide 321 text

Scalarly Includes simple scalar test functions: antisocial network

Slide 322

Slide 322 text

Scalarly Includes simple scalar test functions: ok() — Boolean antisocial network

Slide 323

Slide 323 text

Scalarly Includes simple scalar test functions: ok() — Boolean is() — Value comparison antisocial network

Slide 324

Slide 324 text

Scalarly Includes simple scalar test functions: ok() — Boolean is() — Value comparison isnt() — NOT is() antisocial network

Slide 325

Slide 325 text

Scalarly Includes simple scalar test functions: ok() — Boolean is() — Value comparison isnt() — NOT is() cmp_ok() — Compare with speciﬁc operator antisocial network

Slide 326

Slide 326 text

Scalarly Includes simple scalar test functions: ok() — Boolean is() — Value comparison isnt() — NOT is() cmp_ok() — Compare with speciﬁc operator matches() — Regex comparison antisocial network

Slide 327

Slide 327 text

Scalarly Includes simple scalar test functions: ok() — Boolean is() — Value comparison isnt() — NOT is() cmp_ok() — Compare with speciﬁc operator matches() — Regex comparison imatches() — Case-insensitive regex comparison antisocial network

Slide 328

Slide 328 text

It’s All Relative antisocial network

Slide 329

Slide 329 text

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

Slide 330

Slide 330 text

It’s All Relative Includes functions for testing relations: results_eq() — Ordered results antisocial network

Slide 331

Slide 331 text

It’s All Relative Includes functions for testing relations: results_eq() — Ordered results set_eq() — Set of values antisocial network

Slide 332

Slide 332 text

It’s All Relative Includes functions for testing relations: results_eq() — Ordered results set_eq() — Set of values bag_eq() — Bag of values antisocial network

Slide 333

Slide 333 text

It’s All Relative Includes functions for testing relations: results_eq() — Ordered results set_eq() — Set of values bag_eq() — Bag of values row_eq() — Single row antisocial network

Slide 334

Slide 334 text

It’s All Relative Includes functions for testing relations: results_eq() — Ordered results set_eq() — Set of values bag_eq() — Bag of values row_eq() — Single row is_empty() — No results antisocial network

Slide 335

Slide 335 text

I’m Okay, You’re Okay antisocial network

Slide 336

Slide 336 text

I’m Okay, You’re Okay throws_ok() — Throws an exception antisocial network

Slide 337

Slide 337 text

I’m Okay, You’re Okay throws_ok() — Throws an exception throws_like() — Exception matches regex antisocial network

Slide 338

Slide 338 text

I’m Okay, You’re Okay throws_ok() — Throws an exception throws_like() — Exception matches regex skip() — Skip a subset of tests antisocial network

Slide 339

Slide 339 text

I’m Okay, You’re Okay throws_ok() — Throws an exception throws_like() — Exception matches regex skip() — Skip a subset of tests todo() — Expect subset of tests to fail antisocial network

Slide 340

Slide 340 text

Schematics antisocial network

Slide 341

Slide 341 text

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

Slide 342

Slide 342 text

Schematics has_table(), has_view(), has_function(), etc. columns_are(), has_pk(), fk_ok(), etc. antisocial network

Slide 343

Slide 343 text

Schematics has_table(), has_view(), has_function(), etc. columns_are(), has_pk(), fk_ok(), etc. col_type_is(), col_not_null(), col_default_is() antisocial network

Slide 344

Slide 344 text

Schematics has_table(), has_view(), has_function(), etc. columns_are(), has_pk(), fk_ok(), etc. col_type_is(), col_not_null(), col_default_is() So, so much more… antisocial network

Slide 345

Slide 345 text

Other Features and Topics antisocial network

Slide 346

Slide 346 text

Other Features and Topics xUnit-Style testing antisocial network

Slide 347

Slide 347 text

Other Features and Topics xUnit-Style testing Test-Driven development antisocial network

Slide 348

Slide 348 text

Other Features and Topics xUnit-Style testing Test-Driven development Integration with Perl unit tests antisocial network

Slide 349

Slide 349 text

Other Features and Topics xUnit-Style testing Test-Driven development Integration with Perl unit tests Integration with pg_regress antisocial network

Slide 350

Slide 350 text

Other Features and Topics xUnit-Style testing Test-Driven development Integration with Perl unit tests Integration with pg_regress Negative assertions antisocial network

Slide 351

Slide 351 text

Other Features and Topics xUnit-Style testing Test-Driven development Integration with Perl unit tests Integration with pg_regress Negative assertions Role and privilege assertions antisocial network

Slide 352

Slide 352 text

Other Features and Topics xUnit-Style testing Test-Driven development Integration with Perl unit tests Integration with pg_regress Negative assertions Role and privilege assertions http:/ /pgtap.org/ antisocial network

Slide 353

Slide 353 text

Slide 354

Slide 354 text

antisocial network Let’s do it!

Slide 355

Slide 355 text

antisocial network Let’s do it! Create appschema test

Slide 356

Slide 356 text

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

Slide 357

Slide 357 text

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

Slide 358

Slide 358 text

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

Slide 359

Slide 359 text

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

Slide 360

Slide 360 text

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

Slide 361

Slide 361 text

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/ theory/agile-ﬂipr.git

Slide 362

Slide 362 text

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

Slide 363

Slide 363 text

antisocial network antisocial network

Slide 364

Slide 364 text

TDD antisocial network antisocial network

Slide 365

Slide 365 text

antisocial network

Slide 366

Slide 366 text

TDD is an act of design. antisocial network

Slide 367

Slide 367 text

antisocial network

Slide 368

Slide 368 text

TDD is an act of documentation. antisocial network

Slide 369

Slide 369 text

Database Design antisocial network

Slide 370

Slide 370 text

Database Design Specify requirements antisocial network

Slide 371

Slide 371 text

Database Design Specify requirements Implement schema design antisocial network

Slide 372

Slide 372 text

Database Design Specify requirements Implement schema design Program applications antisocial network

Slide 373

Slide 373 text

Database Design Specify requirements Implement schema design Program applications QA antisocial network

Slide 374

Slide 374 text

Database Design Specify requirements Implement schema design Program applications QA Big jump! antisocial network

Slide 375

Slide 375 text

Database Design Specify requirements Implement schema design Program applications QA Pricy to ﬁx antisocial network

Slide 376

Slide 376 text

Database Design Specify requirements Implement schema design Program applications QA Never mind busy DBA schedules! antisocial network

Slide 377

Slide 377 text

Why TDDD antisocial network

Slide 378

Slide 378 text

Why TDDD Ensure data quality antisocial network

Slide 379

Slide 379 text

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

Slide 380

Slide 380 text

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

Slide 381

Slide 381 text

Why TDDD Ensure data quality Data is a core asset Validate business rules Stored procedures antisocial network

Slide 382

Slide 382 text

Why TDDD Ensure data quality Data is a core asset Validate business rules Stored procedures Triggers antisocial network

Slide 383

Slide 383 text

Why TDDD Ensure data quality Data is a core asset Validate business rules Stored procedures Triggers Views antisocial network

Slide 384

Slide 384 text

Why TDDD antisocial network

Slide 385

Slide 385 text

Why TDDD Identify defects early antisocial network

Slide 386

Slide 386 text

Why TDDD Identify defects early …and often! antisocial network

Slide 387

Slide 387 text

Why TDDD Identify defects early …and often! Create design iteratively antisocial network

Slide 388

Slide 388 text

Why TDDD Identify defects early …and often! Create design iteratively Evolutionarily antisocial network

Slide 389

Slide 389 text

Why TDDD Identify defects early …and often! Create design iteratively Evolutionarily Validate refactorings antisocial network

Slide 390

Slide 390 text

Why TDDD Identify defects early …and often! Create design iteratively Evolutionarily Validate refactorings Make sure nothing breaks antisocial network

Slide 391

Slide 391 text

Three Questions for Database Professionals from Scott Ambler antisocial network

Slide 392

Slide 392 text

No content

Slide 393

Slide 393 text

“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?”

Slide 394

Slide 394 text

No content

Slide 395

Slide 395 text

“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?”

Slide 396

Slide 396 text

No content

Slide 397

Slide 397 text

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

Slide 398

Slide 398 text

antisocial network

Slide 399

Slide 399 text

Okay. antisocial network

Slide 400

Slide 400 text

antisocial network

Slide 401

Slide 401 text

How? antisocial network

Slide 402

Slide 402 text

TDD How antisocial network

Slide 403

Slide 403 text

TDD How Ideally separate from app tests antisocial network

Slide 404

Slide 404 text

TDD How Ideally separate from app tests May be many apps antisocial network

Slide 405

Slide 405 text

TDD How Ideally separate from app tests May be many apps DB should present interface to all antisocial network

Slide 406

Slide 406 text

TDD How Ideally separate from app tests May be many apps DB should present interface to all Apps may use different permissions antisocial network

Slide 407

Slide 407 text

TDD How Ideally separate from app tests May be many apps DB should present interface to all Apps may use different permissions Ideally use DB test Framework antisocial network

Slide 408

Slide 408 text

TDD How Ideally separate from app tests May be many apps DB should present interface to all Apps may use different permissions Ideally use DB test Framework Like…pgTAP! antisocial network

Slide 409

Slide 409 text

Test Template

Slide 410

Slide 410 text

Test Template Sqitch scripts generated from templates

Slide 411

Slide 411 text

Test Template Sqitch scripts generated from templates Can add new templates for new ﬁles

Slide 412

Slide 412 text

Test Template Sqitch scripts generated from templates Can add new templates for new ﬁles Always want test for change?

Slide 413

Slide 413 text

Test Template Sqitch scripts generated from templates Can add new templates for new ﬁles Always want test for change? Add test template!

Slide 414

Slide 414 text

Templates Where? >

Slide 415

Slide 415 text

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

Slide 416

Slide 416 text

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

Slide 417

Slide 417 text

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

Slide 418

Slide 418 text

pg.tmpl Template Your Tests SET client_min_messages TO warning; CREATE EXTENSION IF NOT EXISTS pgtap; RESET client_min_messages; ! BEGIN; ! ! ! SELECT ! SELECT finish(); ROLLBACK; has_schema('flipr'); SELECT plan(1);

Slide 419

Slide 419 text

Slide 420

Slide 420 text

Slide 421

Slide 421 text

Branching Out >

Slide 422

Slide 422 text

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

Slide 423

Slide 423 text

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

Slide 424

Slide 424 text

git checkout -‐b users master Switched to a new branch 'users' > 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 >

Slide 425

Slide 425 text

Slide 426

Slide 426 text

Slide 427

Slide 427 text

git checkout -‐b users master Switched to a new branch 'users' > Branching Out > > emacs test/users.sql > > 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 >

Slide 428

Slide 428 text

SET client_min_messages TO warning; CREATE EXTENSION IF NOT EXISTS pgtap; RESET client_min_messages; ! BEGIN; SELECT no_plan(); -‐-‐ SELECT plan(1); ! ! ! ! test/users.sql Table For One SELECT pass('Test users!'); SELECT finish(); ROLLBACK;

Slide 429

Slide 429 text

Slide 430

Slide 430 text

SET client_min_messages TO warning; CREATE EXTENSION IF NOT EXISTS pgtap; RESET client_min_messages; ! BEGIN; SELECT no_plan(); -‐-‐ SELECT plan(1); ! ! ! ! test/users.sql Table For One SET search_path TO flipr,public; SELECT has_table( 'users' ); SELECT finish(); ROLLBACK;

Slide 431

Slide 431 text

SET client_min_messages TO warning; CREATE EXTENSION IF NOT EXISTS pgtap; RESET client_min_messages; ! BEGIN; SELECT no_plan(); -‐-‐ SELECT plan(1); ! ! ! ! test/users.sql Table For One SET search_path TO flipr,public; SELECT has_table( 'users' ); SELECT finish(); ROLLBACK;

Slide 432

Slide 432 text

SET client_min_messages TO warning; CREATE EXTENSION IF NOT EXISTS pgtap; RESET client_min_messages; ! BEGIN; SELECT no_plan(); -‐-‐ SELECT plan(1); ! ! ! ! test/users.sql Table For One SET search_path TO flipr,public; SELECT has_table( 'users' ); SELECT finish(); ROLLBACK;

Slide 433

Slide 433 text

Run ’Em >

Slide 434

Slide 434 text

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 # Looks like you failed 1 test of 1 Failed 1/1 subtests Test Summary Report -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ test/users.sql (Wstat: 0 Tests: 1 Failed: 1) Failed test: 1 Files=1, Tests=1, 0 wallclock secs Result: FAIL > Run ’Em >

Slide 435

Slide 435 text

Slide 436

Slide 436 text

Slide 437

Slide 437 text

Slide 438

Slide 438 text

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

Slide 439

Slide 439 text

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

Slide 440

Slide 440 text

SET client_min_messages = 'warning'; CREATE TABLE flipr.users ( nickname TEXT ); deploy/users.sq deploy/users.sql COMMIT; -‐-‐ Deploy users -‐-‐ requires: appschema ! BEGIN; !  

Slide 441

Slide 441 text

SET client_min_messages = 'warning'; CREATE TABLE flipr.users ( nickname TEXT ); deploy/users.sq deploy/users.sql COMMIT; -‐-‐ Deploy users -‐-‐ requires: appschema ! BEGIN; !   Bare Minimum

Slide 442

Slide 442 text

Verily, Users > emacs deploy/users.sql >

Slide 443

Slide 443 text

Verily, Users > emacs deploy/users.sql > emacs verify/users.sql

Slide 444

Slide 444 text

-‐-‐ Verify users ! BEGIN; verify/users.sq verify/users.sql -‐-‐ XXX Add verifications here. ROLLBACK;

Slide 445

Slide 445 text

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

Slide 446

Slide 446 text

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

Slide 447

Slide 447 text

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

Slide 448

Slide 448 text

revert/users.sq revert/users.sql -‐-‐ Revert users ! BEGIN; ! ! ! COMMIT; -‐-‐ XXX Add DDLs here.

Slide 449

Slide 449 text

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

Slide 450

Slide 450 text

Make Users >

Slide 451

Slide 451 text

sqitch deploy Deploying changes to flipr_test + users .. ok > Make Users >

Slide 452

Slide 452 text

sqitch deploy Deploying changes to flipr_test + users .. ok > Make Users >

Slide 453

Slide 453 text

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

Slide 454

Slide 454 text

Slide 455

Slide 455 text

Make Users >

Slide 456

Slide 456 text

pg_prove -‐d flipr_test -‐v test/users.sql test/users.sql .. ok 1 -‐ Table users should exist 1..1 ok All tests successful. Files=1, Tests=1, 1 wallclock secs Result: PASS > Make Users >

Slide 457

Slide 457 text

pg_prove -‐d flipr_test -‐v test/users.sql test/users.sql .. ok 1 -‐ Table users should exist 1..1 ok All tests successful. Files=1, Tests=1, 1 wallclock secs Result: PASS > Make Users >

Slide 458

Slide 458 text

pg_prove -‐d flipr_test -‐v test/users.sql test/users.sql .. ok 1 -‐ Table users should exist 1..1 ok All tests successful. Files=1, Tests=1, 1 wallclock secs Result: PASS > Make Users > Woohoo!

Slide 459

Slide 459 text

Slide 460

Slide 460 text

Slide 461

Slide 461 text

SET client_min_messages TO warning; CREATE EXTENSION IF NOT EXISTS pgtap; RESET client_min_messages; ! BEGIN; SELECT no_plan(); -‐-‐ SELECT plan(1); ! SET search_path TO flipr,public; SELECT has_table( 'users' ); SELECT has_column( 'users', 'nickname' ); SELECT has_column( 'users', 'password' ); SELECT has_column( 'users', 'timestamp' ); test/users.sql Columnist SELECT finish(); ROLLBACK;

Slide 462

Slide 462 text

Dead Again >

Slide 463

Slide 463 text

pg_prove -‐d flipr_test -‐v test/users.sql test/users.sql .. ok 1 -‐ Table users should exist 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 -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ test/users.sql (Wstat: 0 Tests: 4 Failed: 2) Failed tests: 3-‐4 Files=1, Tests=4, 0 wallclock secs Result: FAIL Dead Again >

Slide 464

Slide 464 text

Slide 465

Slide 465 text

MOAR Deploy >

Slide 466

Slide 466 text

> emacs deploy/users.sql > MOAR Deploy >

Slide 467

Slide 467 text

-‐-‐ Deploy users -‐-‐ requires: appschema ! BEGIN; ! SET client_min_messages = 'warning'; CREATE TABLE flipr.users ( nickname TEXT deploy/users.sq deploy/users.sql ); ! COMMIT;

Slide 468

Slide 468 text

-‐-‐ Deploy users -‐-‐ requires: appschema ! BEGIN; ! SET client_min_messages = 'warning'; CREATE TABLE flipr.users ( nickname TEXT , password TEXT, timestamp TIMESTAMPTZ deploy/users.sq deploy/users.sql ); ! COMMIT;

Slide 469

Slide 469 text

> emacs deploy/users.sql > Update Verify >

Slide 470

Slide 470 text

> emacs deploy/users.sql > Update Verify > emacs verify/users.sql >

Slide 471

Slide 471 text

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

Slide 472

Slide 472 text

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

Slide 473

Slide 473 text

Revert Overhead > emacs deploy/users.sql > emacs verify/users.sql >

Slide 474

Slide 474 text

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

Slide 475

Slide 475 text

Revert Overhead sqitch revert -‐-‐to @HEAD^ -‐y Reverting changes to appschema from flipr_test -‐ users .. ok > Yes, really. > emacs deploy/users.sql > emacs verify/users.sql >

Slide 476

Slide 476 text

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

Slide 477

Slide 477 text

Revert Overhead sqitch revert -‐-‐to @HEAD^ -‐y Reverting changes to appschema from flipr_test -‐ users .. ok > Remove > emacs deploy/users.sql > emacs verify/users.sql >

Slide 478

Slide 478 text

Revert Overhead sqitch revert -‐-‐to @HEAD^ -‐y Reverting changes to appschema from flipr_test -‐ users .. ok > > emacs deploy/users.sql > emacs verify/users.sql > What’s that?

Slide 479

Slide 479 text

Sqitch Tags antisocial network

Slide 480

Slide 480 text

Sqitch Tags Start with @ antisocial network

Slide 481

Slide 481 text

Sqitch Tags Start with @ To distinguish from changes antisocial network

Slide 482

Slide 482 text

Sqitch Tags Start with @ To distinguish from changes Two symbolic tags: antisocial network

Slide 483

Slide 483 text

Sqitch Tags Start with @ To distinguish from changes Two symbolic tags: @HEAD Last change antisocial network

Slide 484

Slide 484 text

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

Slide 485

Slide 485 text

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

Slide 486

Slide 486 text

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

Slide 487

Slide 487 text

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

Slide 488

Slide 488 text

Specifying Changes antisocial network

Slide 489

Slide 489 text

Specifying Changes users Change named “users” antisocial network

Slide 490

Slide 490 text

Specifying Changes users Change named “users” @HEAD^ Second to last change antisocial network

Slide 491

Slide 491 text

Specifying Changes users Change named “users” @HEAD^ Second to last change users^ Change before users antisocial network

Slide 492

Slide 492 text

Specifying Changes users Change named “users” @HEAD^ Second to last change users^ Change before users @ROOT~ Second change antisocial network

Slide 493

Slide 493 text

Specifying Changes users Change named “users” @HEAD^ Second to last change users^ Change before users @ROOT~ Second change appschema~ Change after appschema antisocial network

Slide 494

Slide 494 text

Specifying Changes users Change named “users” @HEAD^ Second to last change users^ Change before users @ROOT~ Second change appschema~ Change after appschema @HEAD^^ Third to last change antisocial network

Slide 495

Slide 495 text

Slide 496

Slide 496 text

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

Slide 497

Slide 497 text

> sqitch revert -‐-‐to @HEAD^ -‐y Reverting changes to appschema from flipr_test -‐ users .. ok > Whither Users > > psql -‐d flipr_test -‐c '\d flipr.users' Did not find any relation named "flipr.users". >

Slide 498

Slide 498 text

> sqitch revert -‐-‐to @HEAD^ -‐y Reverting changes to appschema from flipr_test -‐ users .. ok > sqitch status # On database flipr_test # Project: flipr # Change: e01feba4bb79607339a13981574855b6e2aa7526 # Name: appschema # Deployed: 2014-‐01-‐07 16:13:50 -‐0800 # By: David E. Wheeler # Undeployed change: * users Whither Users > > psql -‐d flipr_test -‐c '\d flipr.users' Did not find any relation named "flipr.users". >

Slide 499

Slide 499 text

Whither Users >

Slide 500

Slide 500 text

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

Slide 501

Slide 501 text

Back At It >

Slide 502

Slide 502 text

sqitch deploy Deploying changes to flipr_test + users .. ok > Back At It >

Slide 503

Slide 503 text

sqitch deploy Deploying changes to flipr_test + users .. ok > Back At It > Add

Slide 504

Slide 504 text

sqitch deploy Deploying changes to flipr_test + users .. ok > Back At It > pg_prove -‐d flipr_test -‐v test/users.sql test/users.sql .. ok 1 -‐ Table users should exist ok 2 -‐ Column users.nickname should exist ok 3 -‐ Column users.password should exist ok 4 -‐ Column users."timestamp" should exist 1..4 ok All tests successful. Files=1, Tests=4, 0 wallclock secs Result: PASS >

Slide 505

Slide 505 text

Slide 506

Slide 506 text

Slide 507

Slide 507 text

SET search_path = public,tap; ! BEGIN; SELECT no_plan(); -‐-‐ SELECT plan(1); ! SELECT has_table( 'users' ); SELECT has_column( 'users', 'timestamp' ); test/users.sql SELECT has_column( 'users', 'nickname' ); SELECT has_column( 'users', 'password' ); SELECT finish(); ROLLBACK;

Slide 508

Slide 508 text

Slide 509

Slide 509 text

Slide 510

Slide 510 text

SELECT col_type_is( 'users', 'nickname', 'text' ); SELECT col_hasnt_default( 'users', 'nickname' ); SELECT col_is_pk( 'users', 'nickname' ); SET search_path = public,tap; ! BEGIN; SELECT no_plan(); -‐-‐ SELECT plan(1); ! SELECT has_table( 'users' ); SELECT has_column( 'users', 'timestamp' ); test/users.sql SELECT has_column( 'users', 'nickname' ); SELECT has_column( 'users', 'password' ); SELECT has_pk( 'users' ); SELECT col_type_is( 'users', 'password', 'text' ); SELECT col_not_null( 'users', 'password' ); SELECT col_hasnt_default( 'users', 'password' ); SELECT finish(); ROLLBACK;

Slide 511

Slide 511 text

SELECT col_type_is( 'users', 'nickname', 'text' ); SELECT col_hasnt_default( 'users', 'nickname' ); SELECT col_is_pk( 'users', 'nickname' ); SET search_path = public,tap; ! BEGIN; SELECT no_plan(); -‐-‐ SELECT plan(1); ! SELECT has_table( 'users' ); SELECT has_column( 'users', 'timestamp' ); test/users.sql SELECT has_column( 'users', 'nickname' ); SELECT has_column( 'users', 'password' ); SELECT has_pk( 'users' ); 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;

Slide 512

Slide 512 text

Columny >

Slide 513

Slide 513 text

> 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 # Looks like you failed 6 tests of 15 test/users.sql .. Failed 6/15 subtests Test Summary Report -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ test/users.sql (Wstat: 0 Tests: 15 Failed: 6) Failed tests: 2, 6, 9, 13-‐15 Files=1, Tests=15, 0 wallclock secs Result: FAIL Columny >

Slide 514

Slide 514 text

Slide 515

Slide 515 text

User Typography >

Slide 516

Slide 516 text

emacs deploy/users.sql > User Typography >

Slide 517

Slide 517 text

-‐-‐ Deploy users -‐-‐ requires: appschema ! BEGIN; ! SET client_min_messages = 'warning'; CREATE TABLE flipr.users ( nickname TEXT password TEXT timestamp TIMESTAMPTZ ); ! COMMIT; deploy/users.sq deploy/users.sql , ,

Slide 518

Slide 518 text

Slide 519

Slide 519 text

Slide 520

Slide 520 text

Slide 521

Slide 521 text

User Typography > emacs deploy/users.sql >

Slide 522

Slide 522 text

sqitch rebase @HEAD^ -‐y Reverting changes to appschema from flipr_test -‐ users .. ok Deploying changes to flipr_test + users .. ok > User Typography > emacs deploy/users.sql >

Slide 523

Slide 523 text

sqitch rebase @HEAD^ -‐y Reverting changes to appschema from flipr_test -‐ users .. ok Deploying changes to flipr_test + users .. ok > User Typography > emacs deploy/users.sql >

Slide 524

Slide 524 text

sqitch rebase @HEAD^ -‐y Reverting changes to appschema from flipr_test -‐ users .. ok Deploying changes to flipr_test + users .. ok > User Typography > emacs deploy/users.sql >

Slide 525

Slide 525 text

sqitch rebase @HEAD^ -‐y Reverting changes to appschema from flipr_test -‐ users .. ok Deploying changes to flipr_test + users .. ok > User Typography > emacs deploy/users.sql > pg_prove -‐d flipr_test test/users.sql test/users.sql .. ok All tests successful. Files=1, Tests=15, 0 wallclock secs Result: PASS >

Slide 526

Slide 526 text

Slide 527

Slide 527 text

Additives >

Slide 528

Slide 528 text

Additives > git add . >

Slide 529

Slide 529 text

Additives > git add . > git commit -‐am 'Add users table.' [users 693ca89] Add users table. 5 files changed, 60 insertions(+) create mode 100644 deploy/users.sql create mode 100644 revert/users.sql create mode 100644 test/users.sql create mode 100644 verify/users.sql >

Slide 530

Slide 530 text

Pushers >

Slide 531

Slide 531 text

Pushers > git push -‐-‐set-‐upstream origin users Counting objects: 20, done. Delta compression using up to 4 threads. Compressing objects: 100% (14/14), done. Writing objects: 100% (15/15), 1.95 KiB, done. Total 15 (delta 2), reused 0 (delta 0) To https://github.com/theory/agile-‐flipr.git * [new branch] users -‐> users Branch users set up to track remote branch users from origin. >

Slide 532

Slide 532 text

Slide 533

Slide 533 text

Slide 534

Slide 534 text

Wash, Rinse, Repeat antisocial network

Slide 535

Slide 535 text

Wash, Rinse, Repeat Add failing simple test antisocial network

Slide 536

Slide 536 text

Wash, Rinse, Repeat Add failing simple test Add and deploy change antisocial network

Slide 537

Slide 537 text

Wash, Rinse, Repeat Add failing simple test Add and deploy change Revise test antisocial network

Slide 538

Slide 538 text

Wash, Rinse, Repeat Add failing simple test Add and deploy change Revise test Revise and rebase change antisocial network

Slide 539

Slide 539 text

Wash, Rinse, Repeat Add failing simple test Add and deploy change Revise test Revise and rebase change Wash, Rinse, Repeat antisocial network

Slide 540

Slide 540 text

Wash, Rinse, Repeat Add failing simple test Add and deploy change Revise test Revise and rebase change Wash, Rinse, Repeat Commit/Push when done antisocial network

Slide 541

Slide 541 text

Wash, Rinse, Repeat Add failing simple test Add and deploy change Revise test Revise and rebase change Wash, Rinse, Repeat Commit/Push when done Breathe in, breathe out antisocial network

Slide 542

Slide 542 text

Time to Work! antisocial network

Slide 543

Slide 543 text

Time to Work! Prepare to hack! antisocial network

Slide 544

Slide 544 text

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

Slide 545

Slide 545 text

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

Slide 546

Slide 546 text

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

Slide 547

Slide 547 text

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

Slide 548

Slide 548 text

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

Slide 549

Slide 549 text

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

Slide 550

Slide 550 text

Caution: Hard Reset Ahead

Slide 551

Slide 551 text

Caution: Hard Reset Ahead A rare destructive Git command

Slide 552

Slide 552 text

Caution: Hard Reset Ahead A rare destructive Git command Deletes HEAD snapshot

Slide 553

Slide 553 text

Caution: Hard Reset Ahead A rare destructive Git command Deletes HEAD snapshot Replaces it with new snapshot

Slide 554

Slide 554 text

Caution: Hard Reset Ahead A rare destructive Git command Deletes HEAD snapshot Replaces it with new snapshot Almost un-reversible

Slide 555

Slide 555 text

Caution: Hard Reset Ahead A rare destructive Git command Deletes HEAD snapshot Replaces it with new snapshot Almost un-reversible Useful for starting from known point

Slide 556

Slide 556 text

Caution: Hard Reset Ahead A rare destructive Git command Deletes HEAD snapshot Replaces it with new snapshot Almost un-reversible Useful for starting from known point USE WITH CAUTION!

Slide 557

Slide 557 text

antisocial network Flip Out

Slide 558

Slide 558 text

antisocial network Flip Out Create ﬂips branch

Slide 559

Slide 559 text

antisocial network Flip Out Create ﬂips branch Create ﬂips table

Slide 560

Slide 560 text

antisocial network Flip Out Create flips branch Create flips table flip_id SERIAL PK

Slide 561

Slide 561 text

antisocial network Flip Out Create flips branch Create flips table flip_id SERIAL PK nickname FK

Slide 562

Slide 562 text

antisocial network Flip Out Create flips branch Create flips table flip_id SERIAL PK nickname FK body TEXT

Slide 563

Slide 563 text

antisocial network Flip Out Create flips branch Create flips table flip_id SERIAL PK nickname FK body TEXT timestamptz

Slide 564

Slide 564 text

antisocial network Flip Out Create flips branch Create flips table flip_id SERIAL PK nickname FK body TEXT timestamptz Use TDDD

Slide 565

Slide 565 text

antisocial network Flip Out Create flips branch Create flips table flip_id SERIAL PK nickname FK body TEXT timestamptz Use TDDD https:/ /github.com/ theory/agile-flipr.git

Slide 566

Slide 566 text

Functional Testing >

Slide 567

Slide 567 text

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

Slide 568

Slide 568 text

Functional Testing git checkout -‐b userfuncs users Switched to a new branch 'userfuncs' > > 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 >

Slide 569

Slide 569 text

Slide 570

Slide 570 text

test/insert_use test/insert_user.sql

Slide 571

Slide 571 text

test/insert_use test/insert_user.sql SET client_min_messages TO warning; CREATE EXTENSION IF NOT EXISTS pgtap; RESET client_min_messages; SET search_path TO flipr,public;

Slide 572

Slide 572 text

test/insert_use test/insert_user.sql SET client_min_messages TO warning; CREATE EXTENSION IF NOT EXISTS pgtap; RESET client_min_messages; SET search_path TO flipr,public; BEGIN; -‐-‐ SELECT no_plan(); SELECT plan(11);

Slide 573

Slide 573 text

Slide 574

Slide 574 text

test/insert_use SELECT plan(11);

Slide 575

Slide 575 text

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( 'insert_user', ARRAY['text', 'text'], 'void' ); SELECT volatility_is( 'insert_user', ARRAY['text', 'text'], 'volatile' );

Slide 576

Slide 576 text

Slide 577

Slide 577 text

Slide 578

Slide 578 text

Slide 579

Slide 579 text

Slide 580

Slide 580 text

test/insert_use test/insert_user.sql 'volatile' );

Slide 581

Slide 581 text

test/insert_use test/insert_user.sql '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' );

Slide 582

Slide 582 text

Slide 583

Slide 583 text

Slide 584

Slide 584 text

Slide 585

Slide 585 text

Slide 586

Slide 586 text

test/insert_use test/insert_user.sql 'The user should have been inserted' );

Slide 587

Slide 587 text

test/insert_use test/insert_user.sql 'The user should have been inserted' ); SELECT lives_ok( $$ SELECT insert_user('strongrrl', 'w00t') $$, 'Insert another user' ); SELECT bag_eq( 'SELECT * FROM users', $$ VALUES ('theory', md5('foo'), NOW()), ('strongrrl', md5('w00t'), NOW()) $$, 'Both users should be present' );

Slide 588

Slide 588 text

Slide 589

Slide 589 text

Slide 590

Slide 590 text

Slide 591

Slide 591 text

Slide 592

Slide 592 text

test/insert_use 'Both users should be present' );

Slide 593

Slide 593 text

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( 'SELECT * FROM users', $$ VALUES ('theory', md5('foo'), NOW()), ('strongrrl', md5('w00t'), NOW()) $$, 'Should still have just the two users' ); test/insert_use 'Both users should be present' );

Slide 594

Slide 594 text

Slide 595

Slide 595 text

Slide 596

Slide 596 text

Slide 597

Slide 597 text

Slide 598

Slide 598 text

Slide 599

Slide 599 text

Slide 600

Slide 600 text

Functional Testing emacs test/insert_user.sql > >

Slide 601

Slide 601 text

Functional Testing emacs test/insert_user.sql > > emacs deploy/insert_user.sql >

Slide 602

Slide 602 text

deploy/insert_u deploy/insert_user.sql -‐-‐ Deploy insert_user -‐-‐ requires: users -‐-‐ requires: appschema ! BEGIN; -‐-‐ XXX Add DDLs here. COMMIT;

Slide 603

Slide 603 text

deploy/insert_u deploy/insert_user.sql -‐-‐ Deploy insert_user -‐-‐ requires: users -‐-‐ requires: appschema ! 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;

Slide 604

Slide 604 text

Functional Testing emacs deploy/insert_user.sql > >

Slide 605

Slide 605 text

Functional Testing emacs deploy/insert_user.sql > > emacs revert/insert_user.sql >

Slide 606

Slide 606 text

revert/insert_u revert/insert_user.sql -‐-‐ Revert insert_user ! BEGIN; ! ! ! COMMIT; -‐-‐ XXX Add DDLs here.

Slide 607

Slide 607 text

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

Slide 608

Slide 608 text

Functional Testing emacs revert/insert_user.sql > >

Slide 609

Slide 609 text

Functional Testing emacs revert/insert_user.sql > > emacs verify/insert_user.sql >

Slide 610

Slide 610 text

verify/insert_u verify/insert_user.sql -‐-‐ Verify insert_user ! BEGIN; -‐-‐ XXX Add DDLs here. ROLLBACK;

Slide 611

Slide 611 text

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

Slide 612

Slide 612 text

verify/insert_u verify/insert_user.sql -‐-‐ Verify insert_user ! BEGIN; SELECT has_function_privilege( 'flipr.insert_user(text, text)', 'execute' ); ROLLBACK; Convenient!

Slide 613

Slide 613 text

We Good? emacs verify/insert_user.sql > >

Slide 614

Slide 614 text

We Good? emacs verify/insert_user.sql > > sqitch deploy Deploying changes to flipr_test + insert_user .. ok >

Slide 615

Slide 615 text

We Good? emacs verify/insert_user.sql > > sqitch deploy Deploying changes to flipr_test + insert_user .. ok > pg_prove -‐d flipr_test test/*.sql test/appschema.sql .... ok test/insert_user.sql .. ok test/users.sql ........ ok All tests successful. Files=3, Tests=27, 0 wallclock secs Result: PASS >

Slide 616

Slide 616 text

Commitment >

Slide 617

Slide 617 text

git add . > git commit -‐m 'Add `insert_user()`.' [userfuncs e1c7769] Add `insert_user()`. 5 files changed, 101 insertions(+) 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 >

Slide 618

Slide 618 text

Push It Real Good… >

Slide 619

Slide 619 text

git push origin -‐-‐set-‐upstream userfuncs Counting objects: 17, done. Delta compression using up to 4 threads. Compressing objects: 100% (11/11), done. Writing objects: 100% (11/11), 1.91 KiB, done. Total 11 (delta 1), reused 0 (delta 0) To https://github.com/theory/agile-‐flipr.git * [new branch] userfuncs -‐> userfuncs Branch userfuncs set up to track remote branch userfuncs from origin. > Push It Real Good… >

Slide 620

Slide 620 text

Slide 621

Slide 621 text

Slide 622

Slide 622 text

Reset, Mes Amis! antisocial network

Slide 623

Slide 623 text

Reset, Mes Amis! git checkout users antisocial network

Slide 624

Slide 624 text

Reset, Mes Amis! git checkout users git reset --hard upstream/users antisocial network

Slide 625

Slide 625 text

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

Slide 626

Slide 626 text

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

Slide 627

Slide 627 text

antisocial network None Shall Pass

Slide 628

Slide 628 text

antisocial network None Shall Pass Create change_pass()

Slide 629

Slide 629 text

antisocial network None Shall Pass Create change_pass() Params:

Slide 630

Slide 630 text

antisocial network None Shall Pass Create change_pass() Params: nickname

Slide 631

Slide 631 text

antisocial network None Shall Pass Create change_pass() Params: nickname old_pass

Slide 632

Slide 632 text

antisocial network None Shall Pass Create change_pass() Params: nickname old_pass new_pass

Slide 633

Slide 633 text

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

Slide 634

Slide 634 text

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

Slide 635

Slide 635 text

antisocial network None Shall Pass Create change_pass() Params: nickname old_pass new_pass Only update if old pass correct Use TDDD https:/ /github.com/ theory/agile-ﬂipr.git

Slide 636

Slide 636 text

Resets >

Slide 637

Slide 637 text

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

Slide 638

Slide 638 text

Resets > > sqitch checkout master -‐y Last change before the branches diverged: appschema Reverting changes to appschema from flipr_test -‐ change_pass .. ok -‐ insert_user .. ok -‐ users ........ ok Switched to branch 'master' Nothing to deploy (up-‐to-‐date) > > git reset -‐-‐hard upstream/change_pass HEAD is now at 048017a Add `change_pass()`.

Slide 639

Slide 639 text

Slide 640

Slide 640 text

Slide 641

Slide 641 text

Slide 642

Slide 642 text

Slide 643

Slide 643 text

Slide 644

Slide 644 text

Resets > > sqitch checkout master -‐y Last change before the branches diverged: appschema Reverting changes to appschema from flipr_test -‐ change_pass .. ok -‐ insert_user .. ok -‐ users ........ ok Switched to branch 'master' Nothing to deploy (up-‐to-‐date) > git reset -‐-‐hard appschema HEAD is now at e46bdf9 Add appschema test. > > git reset -‐-‐hard upstream/change_pass HEAD is now at 048017a Add `change_pass()`.

Slide 645

Slide 645 text

Resets > Known good. > sqitch checkout master -‐y Last change before the branches diverged: appschema Reverting changes to appschema from flipr_test -‐ change_pass .. ok -‐ insert_user .. ok -‐ users ........ ok Switched to branch 'master' Nothing to deploy (up-‐to-‐date) > git reset -‐-‐hard appschema HEAD is now at e46bdf9 Add appschema test. > > git reset -‐-‐hard upstream/change_pass HEAD is now at 048017a Add `change_pass()`.

Slide 646

Slide 646 text

Mergers and Acquisitions >

Slide 647

Slide 647 text

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 +++++++++ 5 files changed, 60 insertions(+) create mode 100644 deploy/users.sql create mode 100644 revert/users.sql create mode 100644 test/users.sql create mode 100644 verify/users.sql > Mergers and Acquisitions >

Slide 648

Slide 648 text

Slide 649

Slide 649 text

Mergers and Acquisitions >

Slide 650

Slide 650 text

> git merge -‐-‐no-‐ff flips -‐m 'Merge branch "flips".' Merge made by the 'recursive' strategy. deploy/flips.sql | 15 +++++++++++++++ revert/flips.sql | 7 +++++++ sqitch.plan | 1 + test/flips.sql | 39 +++++++++++++++++++++++++++++++++++++ verify/flips.sql | 12 ++++++++++++ 5 files changed, 74 insertions(+) create mode 100644 deploy/flips.sql create mode 100644 revert/flips.sql create mode 100644 test/flips.sql create mode 100644 verify/flips.sql > Mergers and Acquisitions >

Slide 651

Slide 651 text

Slide 652

Slide 652 text

Mergers and Acquisitions >

Slide 653

Slide 653 text

> 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. > Mergers and Acquisitions >

Slide 654

Slide 654 text

Slide 655

Slide 655 text

Slide 656

Slide 656 text

Slide 657

Slide 657 text

Back in Time… >

Slide 658

Slide 658 text

Back in Time… git checkout -‐b userfuncs users Switched to a new branch 'userfuncs' > >

Slide 659

Slide 659 text

Back in Time… git checkout -‐b userfuncs users Switched to a new branch 'userfuncs' > > Ah-ha!

Slide 660

Slide 660 text

Branching Out antisocial network

Slide 661

Slide 661 text

Branching Out users branched from master antisocial network

Slide 662

Slide 662 text

Branching Out users branched from master ﬂips branched from users antisocial network

Slide 663

Slide 663 text

Branching Out users branched from master ﬂips branched from users userfuncs branched from users antisocial network

Slide 664

Slide 664 text

Branching Out users branched from master ﬂips branched from users userfuncs branched from users users and ﬂips merged to master antisocial network

Slide 665

Slide 665 text

Branching Out users branched from master flips branched from users userfuncs branched from users users and flips merged to master userfuncs unaware of flips antisocial network

Slide 666

Slide 666 text

Backbrancher master A

Slide 667

Slide 667 text

Backbrancher master users A branch

Slide 668

Slide 668 text

Backbrancher master users A B Add users table branch

Slide 669

Slide 669 text

Backbrancher master users A B ﬂips branch branch

Slide 670

Slide 670 text

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

Slide 671

Slide 671 text

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

Slide 672

Slide 672 text

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

Slide 673

Slide 673 text

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

Slide 674

Slide 674 text

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

Slide 675

Slide 675 text

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

Slide 676

Slide 676 text

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

Slide 677

Slide 677 text

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

Slide 678

Slide 678 text

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

Slide 679

Slide 679 text

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

Slide 680

Slide 680 text

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

Slide 681

Slide 681 text

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

Slide 682

Slide 682 text

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

Slide 683

Slide 683 text

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

Slide 684

Slide 684 text

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

Slide 685

Slide 685 text

\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

Slide 686

Slide 686 text

Reset >

Slide 687

Slide 687 text

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

Slide 688

Slide 688 text

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

Slide 689

Slide 689 text

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

Slide 690

Slide 690 text

git reset -‐-‐hard HEAD HEAD is now at e1cfc5d Merge branch "flips". > Reset > git checkout userfuncs Switched to branch 'userfuncs' >

Slide 691

Slide 691 text

Rebase >

Slide 692

Slide 692 text

> 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... Auto-‐merging sqitch.plan CONFLICT (content): Merge conflict in sqitch.plan 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 >

Slide 693

Slide 693 text

Slide 694

Slide 694 text

Slide 695

Slide 695 text

Slide 696

Slide 696 text

Slide 697

Slide 697 text

Slide 698

Slide 698 text

Wha Happen? antisocial network

Slide 699

Slide 699 text

Wha Happen? Same conﬂict antisocial network

Slide 700

Slide 700 text

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

Slide 701

Slide 701 text

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

Slide 702

Slide 702 text

Wha Happen? >

Slide 703

Slide 703 text

> 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 >>>>>> Add `insert_user()`. Wha Happen? >

Slide 704

Slide 704 text

Slide 705

Slide 705 text

Slide 706

Slide 706 text

Slide 707

Slide 707 text

Slide 708

Slide 708 text

Scratch that Sqitch antisocial network

Slide 709

Slide 709 text

Scratch that Sqitch Screwed either way? antisocial network

Slide 710

Slide 710 text

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

Slide 711

Slide 711 text

Scratch that Sqitch Screwed either way? Fortunately, this is Git Tell it to treat Sqitch plans differently antisocial network

Slide 712

Slide 712 text

Scratch that Sqitch Screwed either way? Fortunately, this is Git Tell it to treat Sqitch plans differently Changes on single lines antisocial network

Slide 713

Slide 713 text

Scratch that Sqitch Screwed either way? Fortunately, this is Git Tell it to treat Sqitch plans differently Changes on single lines Only appended to plan ﬁle antisocial network

Slide 714

Slide 714 text

Scratch that Sqitch Screwed either way? Fortunately, this is Git Tell it to treat Sqitch plans differently Changes on single lines Only appended to plan ﬁle Use the “union” merge antisocial network

Slide 715

Slide 715 text

Re: Union Merge

Slide 716

Slide 716 text

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

Slide 717

Slide 717 text

Hallelunion antisocial network

Slide 718

Slide 718 text

Hallelunion Just appends lines antisocial network

Slide 719

Slide 719 text

Hallelunion Just appends lines Exactly how changes work antisocial network

Slide 720

Slide 720 text

Hallelunion Just appends lines Exactly how changes work Let’s clean up our mess antisocial network

Slide 721

Slide 721 text

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

Slide 722

Slide 722 text

Reemerge >

Slide 723

Slide 723 text

git rebase -‐-‐abort > Reemerge >

Slide 724

Slide 724 text

echo sqitch.plan merge=union > .gitattributes > git rebase -‐-‐abort > Reemerge >

Slide 725

Slide 725 text

echo sqitch.plan merge=union > .gitattributes > 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... Auto-‐merging sqitch.plan Applying: Add `change_pass()`. Using index info to reconstruct a base tree... M sqitch.plan Falling back to patching base and 3-‐way merge... Auto-‐merging sqitch.plan > git rebase -‐-‐abort > Reemerge >

Slide 726

Slide 726 text

Slide 727

Slide 727 text

Slide 728

Slide 728 text

Slide 729

Slide 729 text

Slide 730

Slide 730 text

Slide 731

Slide 731 text

Slide 732

Slide 732 text

Slide 733

Slide 733 text

sqitch.plan What’s the Plan, Man? %syntax-‐version=1.0.0-‐b2 %project=flipr %uri=https://github.com/theory/agile-‐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 E. Wheeler # Creates a function to

Slide 734

Slide 734 text

Slide 735

Slide 735 text

Slide 736

Slide 736 text

Slide 737

Slide 737 text

Slide 738

Slide 738 text

Slide 739

Slide 739 text

Slide 740

Slide 740 text

Work It >

Slide 741

Slide 741 text

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

Slide 742

Slide 742 text

Slide 743

Slide 743 text

Slide 744

Slide 744 text

Slide 745

Slide 745 text

Make it So >

Slide 746

Slide 746 text

> git add . > Make it So >

Slide 747

Slide 747 text

Slide 748

Slide 748 text

> git add . > git commit -‐m 'Use union merge for `sqitch.plan`.' [userfuncs f38ef5c] Use union merge for `sqitch.plan`. 1 file changed, 1 insertion(+) create mode 100644 .gitattributes > git push -‐-‐force origin userfuncs Counting objects: 31, done. Delta compression using up to 4 threads. Compressing objects: 100% (24/24), done. Writing objects: 100% (25/25), 3.83 KiB, done. Total 25 (delta 7), reused 0 (delta 0) To https://github.com/theory/agile-‐flipr.git + 1813323...f38ef5c userfuncs -‐> userfuncs (forced upda > Make it So > Overwrite history

Slide 749

Slide 749 text

Slide 750

Slide 750 text

Slide 751

Slide 751 text

> git checkout master Switched to branch 'master' > >

Slide 752

Slide 752 text

git merge -‐-‐no-‐ff userfuncs -‐m 'Merge branch "userfuncs".' Merge made by the 'recursive' strategy. .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 +++++++++ 10 files changed, 190 insertions(+) create mode 100644 .gitattributes create mode 100644 deploy/change_pass.sql create mode 100644 deploy/insert_user.sql create mode 100644 revert/change_pass.sql create mode 100644 revert/insert_user.sql create mode 100644 test/change_pass.sql create mode 100644 test/insert_user.sql create mode 100644 verify/change_pass.sql create mode 100644 verify/insert_user.sql > > git checkout master Switched to branch 'master' > >

Slide 753

Slide 753 text

Slide 754

Slide 754 text

Pusher >

Slide 755

Slide 755 text

git push Counting objects: 1, done. Writing objects: 100% (1/1), 231 bytes, done. Total 1 (delta 0), reused 0 (delta 0) To https://github.com/theory/agile-‐flipr.git e1cfc5d..7c5937a master -‐> master > Pusher >

Slide 756

Slide 756 text

antisocial network

Slide 757

Slide 757 text

Ship it! antisocial network

Slide 758

Slide 758 text

Ship Shape antisocial network

Slide 759

Slide 759 text

Ship Shape Good work so far antisocial network

Slide 760

Slide 760 text

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

Slide 761

Slide 761 text

Ship Shape Good work so far People gonna ﬂip out Let’s tag a dev release antisocial network

Slide 762

Slide 762 text

Ship Shape Good work so far People gonna ﬂip out Let’s tag a dev release Bundle it up antisocial network

Slide 763

Slide 763 text

Ship Shape Good work so far People gonna ﬂip out Let’s tag a dev release Bundle it up And ship it antisocial network

Slide 764

Slide 764 text

You’re It >

Slide 765

Slide 765 text

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

Slide 766

Slide 766 text

git commit -‐am 'Tag the database @v1.0.0-‐r1.' [master dce7606] Tag the database @v1.0.0-‐r1. 1 file changed, 1 insertion(+) > sqitch tag v1.0.0-‐r1 -‐n 'Tag v1.0.0-‐r1.' Tagged "change_pass" with @v1.0.0-‐r1 > You’re It >

Slide 767

Slide 767 text

Slide 768

Slide 768 text

git tag v1.0.0-‐r1 -‐am 'Tag v1.0.0-‐r1.' > git commit -‐am 'Tag the database @v1.0.0-‐r1.' [master dce7606] Tag the database @v1.0.0-‐r1. 1 file changed, 1 insertion(+) > sqitch tag v1.0.0-‐r1 -‐n 'Tag v1.0.0-‐r1.' Tagged "change_pass" with @v1.0.0-‐r1 > git push Counting objects: 5, done. Writing objects: 100% (3/3), 344 bytes, done. Total 3 (delta 2), reused 0 (delta 0) To https://github.com/theory/agile-‐flipr.git 7c5937a..dce7606 master -‐> master > You’re It >

Slide 769

Slide 769 text

Slide 770

Slide 770 text

Bundle Up >

Slide 771

Slide 771 text

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 >

Slide 772

Slide 772 text

Slide 773

Slide 773 text

Bundled? >

Slide 774

Slide 774 text

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

Slide 775

Slide 775 text

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

Slide 776

Slide 776 text

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 >

Slide 777

Slide 777 text

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 >

Slide 778

Slide 778 text

antisocial network Merge Madness

Slide 779

Slide 779 text

antisocial network Merge Madness Merge everything

Slide 780

Slide 780 text

antisocial network Merge Madness Merge everything Back to master

Slide 781

Slide 781 text

antisocial network Merge Madness Merge everything Back to master users

Slide 782

Slide 782 text

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

Slide 783

Slide 783 text

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

Slide 784

Slide 784 text

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

Slide 785

Slide 785 text

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

Slide 786

Slide 786 text

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

Slide 787

Slide 787 text

Ruh-Roh >

Slide 788

Slide 788 text

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

Slide 789

Slide 789 text

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 > > git reset -‐-‐hard reltag HEAD is now at dce7606 Tag the database @v1.0.0-‐r1. > Same password

Slide 790

Slide 790 text

Slide 791

Slide 791 text

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 > Not good. > git reset -‐-‐hard reltag HEAD is now at dce7606 Tag the database @v1.0.0-‐r1. >

Slide 792

Slide 792 text

PGCryptonite >

Slide 793

Slide 793 text

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 >

Slide 794

Slide 794 text

Slide 795

Slide 795 text

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

Slide 796

Slide 796 text

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

Slide 797

Slide 797 text

Slide 798

Slide 798 text

Slide 799

Slide 799 text

-‐-‐ Verify pgcrypto ! BEGIN; verify/pgcrypto verify/pgcrypto.sql -‐-‐ XXX Add verifications here. COMMIT;

Slide 800

Slide 800 text

-‐-‐ 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');

Slide 801

Slide 801 text

Slide 802

Slide 802 text

Slide 803

Slide 803 text

You Know the Drill antisocial network

Slide 804

Slide 804 text

You Know the Drill Write revert script antisocial network

Slide 805

Slide 805 text

You Know the Drill Write revert script Add test antisocial network

Slide 806

Slide 806 text

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

Slide 807

Slide 807 text

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

Slide 808

Slide 808 text

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

Slide 809

Slide 809 text

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

Slide 810

Slide 810 text

Slide 811

Slide 811 text

> 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( $$ SELECT insert_user('strongrrl', 'w00t') $$, 'Insert another user' );

Slide 812

Slide 812 text

Slide 813

Slide 813 text

Slide 814

Slide 814 text

-‐SELECT bag_eq( -‐ 'SELECT * FROM users', -‐ $$ 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( $$ SELECT insert_user('theory', 'ha-‐ha') $$,

Slide 815

Slide 815 text

Slide 816

Slide 816 text

Slide 817

Slide 817 text

Slide 818

Slide 818 text

@@ -‐56,14 +56,8 @@ SELECT throws_ok( 'Should get an error for duplicate nickname' ); -‐SELECT bag_eq( -‐ 'SELECT * FROM users', -‐ $$ 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; >

Slide 819

Slide 819 text

Slide 820

Slide 820 text

Slide 821

Slide 821 text

FAIL >

Slide 822

Slide 822 text

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" # Looks like you failed 2 tests of 12 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 Files=1, Tests=12, 0 wallclock secs Result: FAIL >

Slide 823

Slide 823 text

Slide 824

Slide 824 text

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

Slide 825

Slide 825 text

Managing procedures is a PITA! antisocial network

Slide 826

Slide 826 text

Consider this Change >

Slide 827

Slide 827 text

Consider this Change 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 ) RETURNS VOID LANGUAGE SQL SECURITY DEFINER AS $$ -‐ INSERT INTO flipr.users VALUES($1, md5($2)); + INSERT INTO flipr.users values($1, crypt($2, gen_salt('md5'))); $$; COMMIT; > >

Slide 828

Slide 828 text

Slide 829

Slide 829 text

Not So Much antisocial network

Slide 830

Slide 830 text

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

Slide 831

Slide 831 text

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

Slide 832

Slide 832 text

Not So Much Copy insert_user.sql to new deploy file Change that new file Copy insert_user.sql to new revert file antisocial network

Slide 833

Slide 833 text

Not So Much Copy insert_user.sql to new deploy file Change that new file Copy insert_user.sql to new revert file Test it antisocial network

Slide 834

Slide 834 text

Not So Much Copy insert_user.sql to new deploy file Change that new file Copy insert_user.sql to new revert file Test it Do the same for change_pass.sql antisocial network

Slide 835

Slide 835 text

Not So Much Copy insert_user.sql to new deploy file Change that new file Copy insert_user.sql to new revert file Test it Do the same for change_pass.sql The problem with that… antisocial network

Slide 836

Slide 836 text

Slide 837

Slide 837 text

> 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 new file mode 100644 index 0000000..a7f4e31 -‐-‐-‐ /dev/null +++ b/revert/insert_user_crypt.sql @@ -‐0,0 +1,8 @@ +-‐-‐ requires: users, appuser + +CREATE OR REPLACE FUNCTION insert_user( + nickname TEXT, + password TEXT +) RETURNS VOID LANGUAGE SQL AS $$ + INSERT INTO users values($1, md5($2)); +$$; >

Slide 838

Slide 838 text

Slide 839

Slide 839 text

Let Sqitch do the work. antisocial network

Slide 840

Slide 840 text

Rework It >

Slide 841

Slide 841 text

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 >

Slide 842

Slide 842 text

Slide 843

Slide 843 text

Slide 844

Slide 844 text

Slide 845

Slide 845 text

Same Files? >

Slide 846

Slide 846 text

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? >

Slide 847

Slide 847 text

Slide 848

Slide 848 text

Slide 849

Slide 849 text

Slide 850

Slide 850 text

What’s the Diff? >

Slide 851

Slide 851 text

What’s the Diff? git diff deploy/insert_user.sql diff -‐-‐git a/deploy/insert_user.sql b/deploy/insert_user.sql @@ -‐1,6 +1,7 @@ -‐-‐ Deploy insert_user -‐-‐ requires: users -‐-‐ requires: appschema +-‐-‐ requires: pgcrypto BEGIN; @@ -‐8,7 +9,7 @@ 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)); + INSERT INTO flipr.users values($1, crypt($2, gen_salt('md5'))); $$; COMMIT; >

Slide 852

Slide 852 text

Send it Up! >

Slide 853

Slide 853 text

Send it Up! > sqitch deploy Deploying changes to flipr_test + insert_user .. ok > >

Slide 854

Slide 854 text

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

Slide 855

Slide 855 text

Slide 856

Slide 856 text

Can We Go Back? >

Slide 857

Slide 857 text

Can We Go Back? > sqitch revert -‐-‐to @HEAD^ -‐y -‐ insert_user .. ok > >

Slide 858

Slide 858 text

psql -‐d flipr_test -‐c " DELETE FROM flipr.users; 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) Can We Go Back? > sqitch revert -‐-‐to @HEAD^ -‐y -‐ insert_user .. ok > >

Slide 859

Slide 859 text

Verify How? >

Slide 860

Slide 860 text

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

Slide 861

Slide 861 text

-‐-‐ Verify insert_user ! BEGIN; SELECT has_function_privilege( 'flipr.insert_user(text, text)', 'execute' ); verify/insert_u verify/insert_user.sql COMMIT;

Slide 862

Slide 862 text

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

Slide 863

Slide 863 text

Slide 864

Slide 864 text

> emacs verify/insert_user.sql > Let’s Go!

Slide 865

Slide 865 text

> emacs verify/insert_user.sql > Let’s Go! > sqitch deploy Deploying changes to flipr_test + insert_user .. ok >

Slide 866

Slide 866 text

> emacs verify/insert_user.sql > Let’s Go! > sqitch deploy Deploying changes to flipr_test + insert_user .. ok > > psql -‐d flipr_test -‐c 'DELETE FROM flipr.users' DELETE 2 >

Slide 867

Slide 867 text

> emacs verify/insert_user.sql > Let’s Go! > sqitch deploy Deploying changes to flipr_test + 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 All tests successful. Files=1, Tests=12, 0 wallclock secs Result: PASS > Shazam!

Slide 868

Slide 868 text

Add, Commit, Push, Go >

Slide 869

Slide 869 text

Slide 870

Slide 870 text

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 > Add, Commit, Push, Go > git push Counting objects: 17, done. Delta compression using up to 4 threads. Compressing objects: 100% (10/10), done. Writing objects: 100% (10/10), 1.77 KiB, done. Total 10 (delta 6), reused 0 (delta 0) To https://github.com/theory/agile-‐flipr.git 9a9f1da..e4edde6 master -‐> master > >

Slide 871

Slide 871 text

antisocial network Change It Up

Slide 872

Slide 872 text

antisocial network Change It Up git reset --hard insert_user2

Slide 873

Slide 873 text

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

Slide 874

Slide 874 text

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

Slide 875

Slide 875 text

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

Slide 876

Slide 876 text

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

Slide 877

Slide 877 text

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

Slide 878

Slide 878 text

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

Slide 879

Slide 879 text

No content

Slide 880

Slide 880 text

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.

Slide 881

Slide 881 text

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

Slide 882

Slide 882 text

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

Slide 883

Slide 883 text

I hope you enjoyed working here. antisocial network RIP

Slide 884

Slide 884 text

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

Slide 885

Slide 885 text

Git Skillz antisocial network RIP

Slide 886

Slide 886 text

Git Skillz Branching antisocial network RIP

Slide 887

Slide 887 text

Git Skillz Branching Difﬁng antisocial network RIP

Slide 888

Slide 888 text

Git Skillz Branching Difﬁng Rebasing antisocial network RIP

Slide 889

Slide 889 text

Git Skillz Branching Difﬁng Rebasing Merging antisocial network RIP

Slide 890

Slide 890 text

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

Slide 891

Slide 891 text

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

Slide 892

Slide 892 text

MOAR Git antisocial network RIP

Slide 893

Slide 893 text

MOAR Git Bisecting antisocial network RIP

Slide 894

Slide 894 text

MOAR Git Bisecting Blaming antisocial network RIP

Slide 895

Slide 895 text

MOAR Git Bisecting Blaming Pull requests antisocial network RIP

Slide 896

Slide 896 text

MOAR Git Bisecting Blaming Pull requests Submitting patches antisocial network RIP

Slide 897

Slide 897 text

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

Slide 898

Slide 898 text

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

Slide 899

Slide 899 text

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

Slide 900

Slide 900 text

pgTAP Skillz antisocial network RIP

Slide 901

Slide 901 text

pgTAP Skillz TDDD antisocial network RIP

Slide 902

Slide 902 text

pgTAP Skillz TDDD Schema testing antisocial network RIP

Slide 903

Slide 903 text

pgTAP Skillz TDDD Schema testing Scalar testing antisocial network RIP

Slide 904

Slide 904 text

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

Slide 905

Slide 905 text

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

Slide 906

Slide 906 text

MOAR pgTAP antisocial network RIP

Slide 907

Slide 907 text

MOAR pgTAP Testing privileges antisocial network RIP

Slide 908

Slide 908 text

MOAR pgTAP Testing privileges Mocking interfaces antisocial network RIP

Slide 909

Slide 909 text

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

Slide 910

Slide 910 text

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

Slide 911

Slide 911 text

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

Slide 912

Slide 912 text

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

Slide 913

Slide 913 text

Sqitch Skillz antisocial network RIP

Slide 914

Slide 914 text

Sqitch Skillz Add changes antisocial network RIP

Slide 915

Slide 915 text

Sqitch Skillz Add changes Specify Dependencies antisocial network RIP

Slide 916

Slide 916 text

Sqitch Skillz Add changes Specify Dependencies Manage targets antisocial network RIP

Slide 917

Slide 917 text

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

Slide 918

Slide 918 text

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

Slide 919

Slide 919 text

Sqitch Skillz Add changes Specify Dependencies Manage targets Deploy, Verify, Revert changes Rebase changes Rework changes antisocial network RIP

Slide 920

Slide 920 text

Sqitch Skillz Add changes Specify Dependencies Manage targets Deploy, Verify, Revert changes Rebase changes Rework changes Changing branches antisocial network RIP

Slide 921

Slide 921 text

MOAR Sqitch antisocial network RIP

Slide 922

Slide 922 text

MOAR Sqitch Cross-project dependencies antisocial network RIP

Slide 923

Slide 923 text

MOAR Sqitch Cross-project dependencies Multiple projects, one database antisocial network RIP

Slide 924

Slide 924 text

MOAR Sqitch Cross-project dependencies Multiple projects, one database Script templating antisocial network RIP

Slide 925

Slide 925 text

MOAR Sqitch Cross-project dependencies Multiple projects, one database Script templating Variables, loops, conditionals antisocial network RIP

Slide 926

Slide 926 text

MOAR Sqitch Cross-project dependencies Multiple projects, one database Script templating Variables, loops, conditionals Logging antisocial network RIP

Slide 927

Slide 927 text

MOAR Sqitch Cross-project dependencies Multiple projects, one database Script templating Variables, loops, conditionals Logging Formatting output antisocial network RIP

Slide 928

Slide 928 text

MOAR Sqitch Cross-project dependencies Multiple projects, one database Script templating Variables, loops, conditionals Logging Formatting output sqitch help antisocial network RIP