Sane Database Change Management with Sqitch

Slide 1

Slide 1 text

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. Sane SQL Change Management with Sqitch David E. Wheeler http:/ /sqitch.org/ PDX.pm January 9, 2013 Thursday, January 17, 13

Slide 2

Slide 2 text

Whats Wrong with Migrations? Thursday, January 17, 13

Slide 3

Slide 3 text

Whats Wrong with Migrations? Incomplete mini-language Thursday, January 17, 13

Slide 4

Slide 4 text

Whats Wrong with Migrations? Incomplete mini-language No logical replication integration Thursday, January 17, 13

Slide 5

Slide 5 text

Whats Wrong with Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track Thursday, January 17, 13

Slide 6

Slide 6 text

Whats Wrong with Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness Thursday, January 17, 13

Slide 7

Slide 7 text

What about SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness Thursday, January 17, 13

Slide 8

Slide 8 text

What about SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness ——————————————— Thursday, January 17, 13

Slide 9

Slide 9 text

What about SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness ——————————————— ——————————————————— Thursday, January 17, 13

Slide 10

Slide 10 text

Slide 11

Slide 11 text

Imagine this Change > Thursday, January 17, 13

Slide 12

Slide 12 text

Imagine this Change git diff diff -‐-‐git a/deploy/recur.sql b/deploy/recur.sql index 622d52e..56e419e 100644 -‐-‐-‐ a/deploy/recur.sql +++ b/deploy/recur.sql @@ -‐22,7 +22,10 @@ recurrence <> 'none' OR ( recurrence = 'none' -‐ AND starts_at BETWEEN range_start AND range_end + AND ( + starts_at BETWEEN range_start AND range_end + OR ends_at BETWEEN range_start AND range_end + ) ) ) LOOP > Thursday, January 17, 13

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Not So Much Thursday, January 17, 13

Slide 15

Slide 15 text

Not So Much Paste entire function to new “up” script Thursday, January 17, 13

Slide 16

Slide 16 text

Not So Much Paste entire function to new “up” script Edit the new file Thursday, January 17, 13

Slide 17

Slide 17 text

Not So Much Paste entire function to new “up” script Edit the new file Copy the function to the new “down” script Thursday, January 17, 13

Slide 18

Slide 18 text

Not So Much Paste entire function to new “up” script Edit the new file Copy the function to the new “down” script Three copies of the function! Thursday, January 17, 13

Slide 19

Slide 19 text

Not So Much Paste entire function to new “up” script Edit the new file Copy the function to the new “down” script Three copies of the function! No real source code management Thursday, January 17, 13

Slide 20

Slide 20 text

Not So Much Paste entire function to new “up” script Edit the new file Copy the function to the new “down” script Three copies of the function! No real source code management This sucks Thursday, January 17, 13

Slide 21

Slide 21 text

Thursday, January 17, 13

Slide 22

Slide 22 text

Introducing Sqitch Thursday, January 17, 13

Slide 23

Slide 23 text

Sq—what? sql anges ch Thursday, January 17, 13

Slide 24

Slide 24 text

Sq—what? sq ch Thursday, January 17, 13

Slide 25

Slide 25 text

it Sq—what? sq ch Thursday, January 17, 13

Slide 26

Slide 26 text

it Sq—what? sq ch There is no “u” Thursday, January 17, 13

Slide 27

Slide 27 text

Sqitch Philosophy Thursday, January 17, 13

Slide 28

Slide 28 text

Sqitch Philosophy No opinions Thursday, January 17, 13

Slide 29

Slide 29 text

Sqitch Philosophy No opinions Native scripting Thursday, January 17, 13

Slide 30

Slide 30 text

Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution Thursday, January 17, 13

Slide 31

Slide 31 text

Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution No numbering Thursday, January 17, 13

Slide 32

Slide 32 text

Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution No numbering Distribution bundling Thursday, January 17, 13

Slide 33

Slide 33 text

Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution No numbering Distribution bundling Integrated validation testing Thursday, January 17, 13

Slide 34

Slide 34 text

Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution No numbering Distribution bundling Integrated validation testing VCS integration Thursday, January 17, 13

Slide 35

Slide 35 text

Sqitch Philosophy Thursday, January 17, 13

Slide 36

Slide 36 text

Sqitch Philosophy Reduced duplication Thursday, January 17, 13

Slide 37

Slide 37 text

Sqitch Philosophy Reduced duplication Built-in configuration Thursday, January 17, 13

Slide 38

Slide 38 text

Sqitch Philosophy Reduced duplication Built-in configuration Iterative development Thursday, January 17, 13

Slide 39

Slide 39 text

Sqitch Philosophy Reduced duplication Built-in configuration Iterative development Deployment planning Thursday, January 17, 13

Slide 40

Slide 40 text

Sqitch Philosophy Reduced duplication Built-in configuration Iterative development Deployment planning Git-style interface Thursday, January 17, 13

Slide 41

Slide 41 text

Sqitch Philosophy Reduced duplication Built-in configuration Iterative development Deployment planning Git-style interface Deployment tagging Thursday, January 17, 13

Slide 42

Slide 42 text

Sqitch Terminology Thursday, January 17, 13

Slide 43

Slide 43 text

Sqitch Terminology change Thursday, January 17, 13

Slide 44

Slide 44 text

Sqitch Terminology change tag Thursday, January 17, 13

Slide 45

Slide 45 text

Sqitch Terminology change tag state Thursday, January 17, 13

Slide 46

Slide 46 text

Sqitch Terminology change tag state plan Thursday, January 17, 13

Slide 47

Slide 47 text

Sqitch Terminology change tag state plan add Thursday, January 17, 13

Slide 48

Slide 48 text

Sqitch Terminology change tag state plan add deploy Thursday, January 17, 13

Slide 49

Slide 49 text

Sqitch Terminology change tag state plan add deploy revert Thursday, January 17, 13

Slide 50

Slide 50 text

Sqitch Terminology change tag state plan add deploy revert rebase Thursday, January 17, 13

Slide 51

Slide 51 text

Sqitch Terminology change tag state plan add deploy revert rebase rework Thursday, January 17, 13

Slide 52

Slide 52 text

Sqitch Terminology change tag state plan add deploy revert rebase rework verify Thursday, January 17, 13

Slide 53

Slide 53 text

Sqitch Terminology change tag state plan add deploy revert rebase rework verify committer Thursday, January 17, 13

Slide 54

Slide 54 text

Sqitch Terminology change tag state plan add deploy revert rebase rework verify committer planner Thursday, January 17, 13

Slide 55

Slide 55 text

How it Works > Thursday, January 17, 13

Slide 56

Slide 56 text

mkdir flipr > cd flipr > git init . Initialized empty Git repository in /flipr/.git/ > touch README.md > git add . > git commit -‐am 'Fist post!' > How it Works > Thursday, January 17, 13

Slide 57

Slide 57 text

mkdir flipr > cd flipr > git init . Initialized empty Git repository in /flipr/.git/ > touch README.md > git add . > git commit -‐am 'Fist post!' > How it Works > sqitch -‐-‐engine pg init flipr -‐-‐uri https://github Created sqitch.conf Created sqitch.plan Created deploy/ Created revert/ Created verify/ > Thursday, January 17, 13

Slide 58

Slide 58 text

Slide 59

Slide 59 text

mkdir flipr > cd flipr > git init . Initialized empty Git repository in /flipr/.git/ > touch README.md > git add . > git commit -‐am 'Fist post!' > How it Works > emacs sqitch.conf sqitch -‐-‐engine pg init flipr -‐-‐uri https://github Created sqitch.conf Created sqitch.plan Created deploy/ Created revert/ Created verify/ > Thursday, January 17, 13

Slide 60

Slide 60 text

sqitch.conf sqitch.conf [core] engine = pg # plan_file = sqitch.plan # top_dir = . # deploy_dir = deploy # revert_dir = revert # test_dir = test # extension = sql # [core "pg"] # db_name = # client = /usr/local/pgsql/bin/psql # sqitch_schema = sqitch # password = # port = # host = # username = Thursday, January 17, 13

Slide 61

Slide 61 text

Slide 62

Slide 62 text

Add User Config > Thursday, January 17, 13

Slide 63

Slide 63 text

sqitch config -‐-‐user core.pg.client /opt/local/pgsql/bin/psql > Add User Config > Thursday, January 17, 13

Slide 64

Slide 64 text

sqitch config -‐-‐user core.pg.client /opt/local/pgsql/bin/psql > Add User Config > Thursday, January 17, 13

Slide 65

Slide 65 text

sqitch config -‐-‐user core.pg.client /opt/local/pgsql/bin/psql > sqitch config -‐-‐user user.name 'Marge N. O’Vera' > Add User Config > Thursday, January 17, 13

Slide 66

Slide 66 text

sqitch config -‐-‐user core.pg.client /opt/local/pgsql/bin/psql > sqitch config -‐-‐user user.name 'Marge N. O’Vera' > sqitch config -‐-‐user user.email '[email protected]' > Add User Config > Thursday, January 17, 13

Slide 67

Slide 67 text

sqitch config -‐-‐user core.pg.client /opt/local/pgsql/bin/psql > sqitch config -‐-‐user user.name 'Marge N. O’Vera' > emacs ~/.sqitch/sqitch.conf sqitch config -‐-‐user user.email '[email protected]' > Add User Config > Thursday, January 17, 13

Slide 68

Slide 68 text

~/.sqitch/sqitc ~/ .sqitch/sqitch.conf [core "pg"] client = /opt/local/pgsql/bin/psql [user] name = Marge N. O’Vera email = [email protected] Thursday, January 17, 13

Slide 69

Slide 69 text

~/.sqitch/sqitc ~/ .sqitch/sqitch.conf [core "pg"] client = /opt/local/pgsql/bin/psql [user] name = Marge N. O’Vera email = [email protected] Good for all projects Thursday, January 17, 13

Slide 70

Slide 70 text

What’s the Plan Man? > Thursday, January 17, 13

Slide 71

Slide 71 text

What’s the Plan Man? emacs sqitch.plan > Thursday, January 17, 13

Slide 72

Slide 72 text

sqitch.plan sqitch.plan %syntax-‐version=1.0.0-‐b1 %project=flipr %uri=https://github.com/theory/sqitch-‐intro/ Thursday, January 17, 13

Slide 73

Slide 73 text

sqitch.plan sqitch.plan %syntax-‐version=1.0.0-‐b1 %project=flipr %uri=https://github.com/theory/sqitch-‐intro/ Identified Thursday, January 17, 13

Slide 74

Slide 74 text

Make It So > Thursday, January 17, 13

Slide 75

Slide 75 text

Make It So > git add . > git commit -‐am 'Initialize Sqitch configuration.' [master be9ab3e] Initialize Sqitch configuration. 2 files changed, 23 insertions(+) create mode 100644 sqitch.conf create mode 100644 sqitch.plan > > Thursday, January 17, 13

Slide 76

Slide 76 text

First Deployment > Thursday, January 17, 13

Slide 77

Slide 77 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 Deployment > Thursday, January 17, 13

Slide 78

Slide 78 text

Slide 79

Slide 79 text

Slide 80

Slide 80 text

Slide 81

Slide 81 text

deploy/appschem deploy/appschema.sql -‐-‐ Deploy appschema BEGIN; COMMIT; -‐-‐ XXX Add DDLs here. Thursday, January 17, 13

Slide 82

Slide 82 text

deploy/appschem deploy/appschema.sql -‐-‐ Deploy appschema BEGIN; COMMIT; CREATE SCHEMA flipr; Thursday, January 17, 13

Slide 83

Slide 83 text

First Deployment > sqitch add appschema -‐n 'Adds flipr app schema.' Created deploy/appschema.sql Created revert/appschema.sql Created test/appschema.sql Added "appschema" to sqitch.plan > emacs deploy/appschema.sql > > Thursday, January 17, 13

Slide 84

Slide 84 text

Slide 85

Slide 85 text

revert/appschem revert/appschema.sql -‐-‐ Revert appschema BEGIN; COMMIT; -‐-‐ XXX Add DDLs here. Thursday, January 17, 13

Slide 86

Slide 86 text

revert/appschem revert/appschema.sql -‐-‐ Revert appschema BEGIN; COMMIT; DROP SCHEMA flipr; Thursday, January 17, 13

Slide 87

Slide 87 text

Make it So! > Thursday, January 17, 13

Slide 88

Slide 88 text

Make it So! > createdb flipr_test > sqitch -‐-‐db-‐name flipr_test deploy Adding metadata tables to flipr_test Deploying to flipr_test + appschema .. ok > Thursday, January 17, 13

Slide 89

Slide 89 text

Make it So! > createdb flipr_test > sqitch -‐-‐db-‐name flipr_test deploy Adding metadata tables to flipr_test Deploying to flipr_test + appschema .. ok > Thursday, January 17, 13

Slide 90

Slide 90 text

Make it So! > createdb flipr_test > sqitch -‐-‐db-‐name flipr_test deploy Adding metadata tables to flipr_test Deploying to flipr_test + appschema .. ok > Thursday, January 17, 13

Slide 91

Slide 91 text

Make it So! > createdb flipr_test > sqitch -‐-‐db-‐name flipr_test deploy Adding metadata tables to flipr_test Deploying to flipr_test + appschema .. ok > Thursday, January 17, 13

Slide 92

Slide 92 text

Make it So! > psql -‐d flipr_test -‐c '\dn flipr' List of schemas Name | Owner -‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐ flipr | marge > createdb flipr_test > sqitch -‐-‐db-‐name flipr_test deploy Adding metadata tables to flipr_test Deploying to flipr_test + appschema .. ok > Thursday, January 17, 13

Slide 93

Slide 93 text

Slide 94

Slide 94 text

Slide 95

Slide 95 text

verify/appschem verify/appschema.sql -‐-‐ Verify appschema BEGIN; COMMIT; -‐-‐ XXX Add verifications here. Thursday, January 17, 13

Slide 96

Slide 96 text

SELECT pg_catalog.has_schema_privilege('nada', 'usage'); verify/appschem verify/appschema.sql -‐-‐ Verify appschema BEGIN; COMMIT; Thursday, January 17, 13

Slide 97

Slide 97 text

SELECT pg_catalog.has_schema_privilege('nada', 'usage'); verify/appschem verify/appschema.sql -‐-‐ Verify appschema BEGIN; COMMIT; Let’s try it, first Thursday, January 17, 13

Slide 98

Slide 98 text

SELECT pg_catalog.has_schema_privilege('nada', 'usage'); verify/appschem verify/appschema.sql -‐-‐ Verify appschema BEGIN; COMMIT; Thursday, January 17, 13

Slide 99

Slide 99 text

Trust, But Verify > emacs verify/appschema.sql > Thursday, January 17, 13

Slide 100

Slide 100 text

Trust, But Verify > emacs verify/appschema.sql > sqitch -‐-‐db-‐name flipr_test verify Verifying 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 > Thursday, January 17, 13

Slide 101

Slide 101 text

Slide 102

Slide 102 text

Slide 103

Slide 103 text

Slide 104

Slide 104 text

Trust, But Verify > Thursday, January 17, 13

Slide 105

Slide 105 text

perl -‐i -‐pe 's/nada/flipr/' > Trust, But Verify > Thursday, January 17, 13

Slide 106

Slide 106 text

sqitch -‐-‐db-‐name flipr_test verify Verifying flipr_test * appschema .. ok Verify successful > perl -‐i -‐pe 's/nada/flipr/' > Trust, But Verify > Mo betta. Thursday, January 17, 13

Slide 107

Slide 107 text

> How’s it Look? Thursday, January 17, 13

Slide 108

Slide 108 text

> > sqitch -‐d flipr_test status # On database flipr_test # Project: flipr # Change: ae1263b92b3355cf705c7832271730fe4ccb3c77 # Name: appschema # Deployed: 2012-‐12-‐31 11:00:29 -‐0800 # By: Marge N. O’Vera # Nothing to deploy (up-‐to-‐date) > How’s it Look? Thursday, January 17, 13

Slide 109

Slide 109 text

Slide 110

Slide 110 text

Slide 111

Slide 111 text

Slide 112

Slide 112 text

Slide 113

Slide 113 text

Slide 114

Slide 114 text

Go Back > Thursday, January 17, 13

Slide 115

Slide 115 text

> sqitch -‐-‐db-‐name flipr_test revert Revert all changes from flipr_test? [Yes] Go Back > ▮ Thursday, January 17, 13

Slide 116

Slide 116 text

> sqitch -‐-‐db-‐name flipr_test revert Revert all changes from flipr_test? [Yes] Go Back > ▮ Thursday, January 17, 13

Slide 117

Slide 117 text

> sqitch -‐-‐db-‐name flipr_test revert Revert all changes from flipr_test? [Yes] Go Back > -‐ appschema .. ok > Thursday, January 17, 13

Slide 118

Slide 118 text

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

Slide 119

Slide 119 text

Slide 120

Slide 120 text

History > Thursday, January 17, 13

Slide 121

Slide 121 text

History > > sqitch -‐d flipr_test log On database flipr_test Revert ae1263b92b3355cf705c7832271730fe4ccb3c77 Name: appschema Committer: Marge N. O’Vera Date: 2012-‐12-‐31 11:01:22 -‐0800 App schema for all flipr objects. Deploy ae1263b92b3355cf705c7832271730fe4ccb3c77 Name: appschema Committer: Marge N. O’Vera Date: 2012-‐12-‐31 11:00:29 -‐0800 App schema for all flipr objects. Thursday, January 17, 13

Slide 122

Slide 122 text

Slide 123

Slide 123 text

Slide 124

Slide 124 text

Commit It! > Thursday, January 17, 13

Slide 125

Slide 125 text

Slide 126

Slide 126 text

> git add . > git commit -‐m 'Add flipr schema.' [master e6464c0] 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 > sqitch -‐-‐db-‐name flipr_test deploy -‐-‐verify Deploying changes to flipr_test + appschema .. ok > Commit It! > Thursday, January 17, 13

Slide 127

Slide 127 text

Slide 128

Slide 128 text

Slide 129

Slide 129 text

Slide 130

Slide 130 text

Status Update > Thursday, January 17, 13

Slide 131

Slide 131 text

> sqitch -‐d flipr_test status # On database flipr_test # Project: flipr # Change: ae1263b92b3355cf705c7832271730fe4ccb3c77 # Name: appschema # Deployed: 2012-‐12-‐31 11:03:18 -‐0800 # By: Marge N. O’Vera # Nothing to deploy (up-‐to-‐date) > Status Update > Thursday, January 17, 13

Slide 132

Slide 132 text

Save My Fingers > Thursday, January 17, 13

Slide 133

Slide 133 text

Save My Fingers > sqitch config core.pg.db_name flipr_test > Thursday, January 17, 13

Slide 134

Slide 134 text

Save My Fingers > sqitch config core.pg.db_name flipr_test > sqitch status # On database flipr_test # Project: flipr # Change: ae1263b92b3355cf705c7832271730fe4ccb3c77 # Name: appschema # Deployed: 2012-‐12-‐31 11:03:18 -‐0800 # By: Marge N. O’Vera # Nothing to deploy (up-‐to-‐date) > No --db-name Thursday, January 17, 13

Slide 135

Slide 135 text

Slide 136

Slide 136 text

Dependencies! > Thursday, January 17, 13

Slide 137

Slide 137 text

Dependencies! > > sqitch add users -‐-‐requires appschema \ -‐n 'Creates table to track our users.' Created deploy/users.sql Created revert/users.sql Created verify/users.sql Added "users [appschema]" to sqitch.plan > Thursday, January 17, 13

Slide 138

Slide 138 text

Slide 139

Slide 139 text

Slide 140

Slide 140 text

deploy/users.sq deploy/users.sql -‐-‐ XXX Add DDLs here. COMMIT; -‐-‐ Deploy users -‐-‐ requires: appschema BEGIN; Thursday, January 17, 13

Slide 141

Slide 141 text

deploy/users.sq deploy/users.sql -‐-‐ XXX Add DDLs here. COMMIT; -‐-‐ Deploy users -‐-‐ requires: appschema BEGIN; Thursday, January 17, 13

Slide 142

Slide 142 text

CREATE TABLE flipr.users ( nickname TEXT PRIMARY KEY, password TEXT NOT NULL, timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW() ); deploy/users.sq deploy/users.sql COMMIT; -‐-‐ Deploy users -‐-‐ requires: appschema BEGIN; Thursday, January 17, 13

Slide 143

Slide 143 text

Slide 144

Slide 144 text

Slide 145

Slide 145 text

-‐-‐ Verify users BEGIN; verify/users.sq verify/users.sql -‐-‐ XXX Add verifications here. COMMIT; Thursday, January 17, 13

Slide 146

Slide 146 text

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

Slide 147

Slide 147 text

Slide 148

Slide 148 text

Slide 149

Slide 149 text

revert/users.sq revert/users.sql -‐-‐ Revert users BEGIN; COMMIT; -‐-‐ XXX Add DDLs here. Thursday, January 17, 13

Slide 150

Slide 150 text

revert/users.sq revert/users.sql -‐-‐ Revert users BEGIN; COMMIT; DROP TABLE flipr.users; Thursday, January 17, 13

Slide 151

Slide 151 text

Make Users > Thursday, January 17, 13

Slide 152

Slide 152 text

sqitch deploy Deploying changes to flipr_test + users .. ok > Make Users > Thursday, January 17, 13

Slide 153

Slide 153 text

sqitch deploy Deploying changes to flipr_test + users .. ok > Make Users > Thursday, January 17, 13

Slide 154

Slide 154 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 | not null password | text | not null timestamp | timestamptz | not null default now() Indexes: "users_pkey" PRIMARY KEY, btree (nickname) > Thursday, January 17, 13

Slide 155

Slide 155 text

Verify Users > Thursday, January 17, 13

Slide 156

Slide 156 text

sqitch verify Verifying flipr_test * appschema .. ok * users ...... ok Verify successful > Verify Users > Thursday, January 17, 13

Slide 157

Slide 157 text

Status Update > Thursday, January 17, 13

Slide 158

Slide 158 text

> sqitch status # On database flipr_test # Project: flipr # Change: bbb98e0660898ced0ba5f84c577036b5dafa3a55 # Name: users # Deployed: 2012-‐12-‐31 11:42:17 -‐0800 # By: Marge N. O’Vera # Nothing to deploy (up-‐to-‐date) > Status Update > Thursday, January 17, 13

Slide 159

Slide 159 text

Slide 160

Slide 160 text

Slide 161

Slide 161 text

Slide 162

Slide 162 text

Slide 163

Slide 163 text

Symbolic Tags Thursday, January 17, 13

Slide 164

Slide 164 text

Symbolic Tags @HEAD Last change Thursday, January 17, 13

Slide 165

Slide 165 text

Symbolic Tags @HEAD Last change @ROOT First change Thursday, January 17, 13

Slide 166

Slide 166 text

Symbolic Tags @HEAD Last change @ROOT First change ^ Previous change Thursday, January 17, 13

Slide 167

Slide 167 text

Symbolic Tags @HEAD Last change @ROOT First change ^ Previous change ~ Following change Thursday, January 17, 13

Slide 168

Slide 168 text

Lose Users > Thursday, January 17, 13

Slide 169

Slide 169 text

Lose Users > > psql -‐d flipr_test -‐c '\d flipr.users' Did not find any relation named "flipr.users". > Thursday, January 17, 13

Slide 170

Slide 170 text

sqitch status # On database flipr_test # Project: flipr # Change: ae1263b92b3355cf705c7832271730fe4ccb3c77 # Name: appschema # Deployed: 2012-‐12-‐31 11:03:18 -‐0800 # By: Marge N. O’Vera # Undeployed change: * users > Lose Users > > psql -‐d flipr_test -‐c '\d flipr.users' Did not find any relation named "flipr.users". > Thursday, January 17, 13

Slide 171

Slide 171 text

Lose Users > Thursday, January 17, 13

Slide 172

Slide 172 text

> sqitch verify Verifying flipr_test * appschema .. ok Undeployed change: * users Verify successful > Lose Users > Thursday, January 17, 13

Slide 173

Slide 173 text

Lose Users > Thursday, January 17, 13

Slide 174

Slide 174 text

Lose Users > git add . > git commit -‐am 'Add users table.' [master 6aae0e9] Add users table. 4 files changed, 31 insertions(+) create mode 100644 deploy/users.sql create mode 100644 revert/users.sql create mode 100644 verify/users.sql > Thursday, January 17, 13

Slide 175

Slide 175 text

Slide 176

Slide 176 text

Up to Date > Thursday, January 17, 13

Slide 177

Slide 177 text

Up to Date > > sqitch status # On database flipr_test # Project: flipr # Change: bbb98e0660898ced0ba5f84c577036b5dafa3a55 # Name: users # Deployed: 2012-‐12-‐31 14:04:11 -‐0800 # By: Marge N. O’Vera # Nothing to deploy (up-‐to-‐date) > Thursday, January 17, 13

Slide 178

Slide 178 text

Slide 179

Slide 179 text

Slide 180

Slide 180 text

A Twofer > Thursday, January 17, 13

Slide 181

Slide 181 text

Slide 182

Slide 182 text

Slide 183

Slide 183 text

A Twofer > > 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 verify/insert_user.sql Added "insert_user [users appschema]" to sqitch.plan > sqitch add change_pass -‐r users -‐r appschema \ -‐n 'Creates a function to change a user password.' Created deploy/change_pass.sql Created revert/change_pass.sql Created verify/change_pass.sql Added "change_pass [users appschema]" to sqitch.plan > Thursday, January 17, 13

Slide 184

Slide 184 text

Slide 185

Slide 185 text

A Twofer > emacs sqitch.plan > 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 verify/insert_user.sql Added "insert_user [users appschema]" to sqitch.plan > sqitch add change_pass -‐r users -‐r appschema \ -‐n 'Creates a function to change a user password.' Created deploy/change_pass.sql Created revert/change_pass.sql Created verify/change_pass.sql Added "change_pass [users appschema]" to sqitch.plan > Thursday, January 17, 13

Slide 186

Slide 186 text

sqitch.plan sqitch.plan %syntax-‐version=1.0.0-‐b2 %project=flipr %uri=https://github.com/theory/sqitch-‐intro/ appschema 2012-‐12-‐29T01:08:29Z Marge N. O’Vera # App schema for all flipr objects. users [appschema] 2012-‐12-‐31T19:38:06Z Marge N. O’Vera # Creates table to track our users. insert_user [users appschema] 2012-‐12-‐31T22:44:27Z Marge N. O’Vera # Creates a function to insert a user. change_pass [users appschema] 2012-‐12-‐31T22:44:38Z Marge N. O’Vera # Creates a function to change a user password. Thursday, January 17, 13

Slide 187

Slide 187 text

Slide 188

Slide 188 text

Slide 189

Slide 189 text

Slide 190

Slide 190 text

Slide 191

Slide 191 text

Slide 192

Slide 192 text

Slide 193

Slide 193 text

Slide 194

Slide 194 text

> emacs sqitch.plan > A Twofer Thursday, January 17, 13

Slide 195

Slide 195 text

> emacs sqitch.plan > > emacs deploy/insert_user.sql A Twofer Thursday, January 17, 13

Slide 196

Slide 196 text

deploy/insert_u deploy/insert_user.sql -‐-‐ XXX Add DDLs here. COMMIT; -‐-‐ Deploy insert_user -‐-‐ requires: appuser -‐-‐ requires: users BEGIN; Thursday, January 17, 13

Slide 197

Slide 197 text

deploy/insert_u deploy/insert_user.sql -‐-‐ XXX Add DDLs here. COMMIT; -‐-‐ Deploy insert_user -‐-‐ requires: appuser -‐-‐ requires: users BEGIN; Thursday, January 17, 13

Slide 198

Slide 198 text

deploy/insert_u deploy/insert_user.sql COMMIT; -‐-‐ Deploy insert_user -‐-‐ requires: appuser -‐-‐ requires: users BEGIN; CREATE OR REPLACE FUNCTION flipr.insert_user( nickname TEXT, password TEXT ) RETURNS VOID LANGUAGE SQL SECURITY DEFINER AS $$ INSERT INTO users VALUES($1, md5($2)); $$; Thursday, January 17, 13

Slide 199

Slide 199 text

A Twofer > emacs sqitch.plan > emacs deploy/insert_user.sql > Thursday, January 17, 13

Slide 200

Slide 200 text

A Twofer > emacs sqitch.plan > emacs deploy/insert_user.sql > emacs verify/insert_user.sql Thursday, January 17, 13

Slide 201

Slide 201 text

-‐-‐ Verify insert_user BEGIN; verify/insert_u verify/insert_user.sql -‐-‐ XXX Add verifications here. COMMIT; Thursday, January 17, 13

Slide 202

Slide 202 text

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

Slide 203

Slide 203 text

A Twofer > emacs sqitch.plan > emacs deploy/insert_user.sql > emacs verify/insert_user.sql > Thursday, January 17, 13

Slide 204

Slide 204 text

A Twofer > emacs sqitch.plan > emacs deploy/insert_user.sql > emacs verify/insert_user.sql > emacs insert/change_pass.sql Thursday, January 17, 13

Slide 205

Slide 205 text

-‐-‐ Deploy change_pass -‐-‐ :requires: appuser -‐-‐ :requires: users BEGIN; deploy/change_p deploy/change_pass.sql -‐-‐ XXX Add DDLs here. COMMIT; Thursday, January 17, 13

Slide 206

Slide 206 text

-‐-‐ Deploy change_pass -‐-‐ :requires: appuser -‐-‐ :requires: users BEGIN; CREATE OR REPLACE FUNCTION flipr.change_pass( nick TEXT, oldpass TEXT, newpass TEXT ) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN UPDATE users SET password = md5($3) WHERE nickname = $1 AND password = md5($2); RETURN FOUND; END; $$; deploy/change_p deploy/change_pass.sql COMMIT; Thursday, January 17, 13

Slide 207

Slide 207 text

A Twofer > emacs sqitch.plan > emacs deploy/insert_user.sql > emacs verify/insert_user.sql > emacs insert/change_pass.sql > Thursday, January 17, 13

Slide 208

Slide 208 text

A Twofer > emacs sqitch.plan > emacs deploy/insert_user.sql > emacs verify/insert_user.sql > emacs insert/change_pass.sql > emacs verify/change_pass.sql Thursday, January 17, 13

Slide 209

Slide 209 text

-‐-‐ Verify change_pass BEGIN; verify/change_p verify/change_pass.sql -‐-‐ XXX Add verifications here. COMMIT; Thursday, January 17, 13

Slide 210

Slide 210 text

-‐-‐ Verify change_pass BEGIN; SELECT has_function_privilege( 'flipr.change_pass(text, text, text)', 'execute' ); verify/change_p verify/change_pass.sql COMMIT; Thursday, January 17, 13

Slide 211

Slide 211 text

Deploy Functions > Thursday, January 17, 13

Slide 212

Slide 212 text

> sqitch deploy Deploying changes to flipr_test + insert_user .. ok + change_pass .. ok > Deploy Functions > Thursday, January 17, 13

Slide 213

Slide 213 text

Slide 214

Slide 214 text

Slide 215

Slide 215 text

Status Update > Thursday, January 17, 13

Slide 216

Slide 216 text

Status Update > > sqitch status # On database flipr_test # Project: flipr # Change: 0acf77aaafc1a828e37e8e857d04d13ee22a2d36 # Name: change_pass # Deployed: 2012-‐12-‐31 14:45:18 -‐0800 # By: Marge N. O’Vera # Nothing to deploy (up-‐to-‐date) > Thursday, January 17, 13

Slide 217

Slide 217 text

Revert > Thursday, January 17, 13

Slide 218

Slide 218 text

Revert > > sqitch revert -‐y -‐-‐to @HEAD^^ Reverting changes to users from flipr_test -‐ change_pass .. ok -‐ insert_user .. ok > Thursday, January 17, 13

Slide 219

Slide 219 text

Revert > > sqitch revert -‐y -‐-‐to @HEAD^^ Reverting changes to users from flipr_test -‐ change_pass .. ok -‐ insert_user .. ok > Thursday, January 17, 13

Slide 220

Slide 220 text

Revert > psql -‐d flipr_test -‐c '\df flipr.*' List of functions Schema | Name | Result data type | Argument data ty -‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ > > sqitch revert -‐y -‐-‐to @HEAD^^ Reverting changes to users from flipr_test -‐ change_pass .. ok -‐ insert_user .. ok > Thursday, January 17, 13

Slide 221

Slide 221 text

Commit It Thursday, January 17, 13

Slide 222

Slide 222 text

Commit It git add && git commit Thursday, January 17, 13

Slide 223

Slide 223 text

Commit It git add && git commit sqitch deploy Thursday, January 17, 13

Slide 224

Slide 224 text

Commit It git add && git commit sqitch deploy And now… Thursday, January 17, 13

Slide 225

Slide 225 text

Commit It git add && git commit sqitch deploy And now… Tag Thursday, January 17, 13

Slide 226

Slide 226 text

Commit It git add && git commit sqitch deploy And now… Tag Bundle Thursday, January 17, 13

Slide 227

Slide 227 text

Commit It git add && git commit sqitch deploy And now… Tag Bundle Ship! Thursday, January 17, 13

Slide 228

Slide 228 text

Tag It! > Thursday, January 17, 13

Slide 229

Slide 229 text

Tag It! > > sqitch tag v1.0.0-‐dev1 -‐n 'Tag v1.0.0-‐dev1.' Tagged "change_pass" with @v1.0.0-‐dev1 > Thursday, January 17, 13

Slide 230

Slide 230 text

Tag It! > > sqitch tag v1.0.0-‐dev1 -‐n 'Tag v1.0.0-‐dev1.' Tagged "change_pass" with @v1.0.0-‐dev1 > Thursday, January 17, 13

Slide 231

Slide 231 text

Tag It! > > sqitch tag v1.0.0-‐dev1 -‐n 'Tag v1.0.0-‐dev1.' Tagged "change_pass" with @v1.0.0-‐dev1 > git commit -‐am 'Tag the database with v1.0.0-‐dev1.' [master b215e03] Tag the database with v1.0.0-‐dev1. 1 file changed, 1 insertion(+) > Thursday, January 17, 13

Slide 232

Slide 232 text

Slide 233

Slide 233 text

Bundle It! > Thursday, January 17, 13

Slide 234

Slide 234 text

Bundle It! > > sqitch bundle Bundling into bundle/ Writing config Writing plan Writing scripts + appschema + users + insert_user + change_pass @v1.0.0-‐dev1 > Thursday, January 17, 13

Slide 235

Slide 235 text

Bundle It! > > sqitch bundle Bundling into bundle/ Writing config Writing plan Writing scripts + appschema + users + insert_user + change_pass @v1.0.0-‐dev1 > Thursday, January 17, 13

Slide 236

Slide 236 text

Test and Ship It! > Thursday, January 17, 13

Slide 237

Slide 237 text

Test and Ship It! > cd bundle > Thursday, January 17, 13

Slide 238

Slide 238 text

Test and Ship It! > createdb flipr_stage > cd bundle > Thursday, January 17, 13

Slide 239

Slide 239 text

sqitch -‐-‐db-‐name flipr_stage deploy Adding metadata tables to flipr_stage Deploying changes to flipr_stage + appschema ................. ok + users ..................... ok + insert_user ............... ok + change_pass @v1.0.0-‐dev1 .. ok > Test and Ship It! > createdb flipr_stage > cd bundle > Thursday, January 17, 13

Slide 240

Slide 240 text

Slide 241

Slide 241 text

Reworking Changes Thursday, January 17, 13

Slide 242

Slide 242 text

Ruh-Roh > Thursday, January 17, 13

Slide 243

Slide 243 text

psql -‐d flipr_test -‐c " SELECT insert_user('foo', 'secr3t'), insert_user('bar', 'secr3t'); SELECT nickname, password FROM users; " nickname | password -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ foo | 9695da4dd567a19f9b92065f240c6725 bar | 9695da4dd567a19f9b92065f240c6725 Ruh-Roh > Thursday, January 17, 13

Slide 244

Slide 244 text

Slide 245

Slide 245 text

Add pgcrypto > Thursday, January 17, 13

Slide 246

Slide 246 text

sqitch add pgcrypto -‐n 'Loads pgcrypto extension.' Adding deploy/pgcrypto.sql Adding revert/pgcrypto.sql Adding verify/pgcrypto.sql Added "pgcrypto" to sqitch.plan > Add pgcrypto > Thursday, January 17, 13

Slide 247

Slide 247 text

Slide 248

Slide 248 text

deploy/pgcrypto deploy/pgcrypto.sql -‐-‐ Deploy pgcrypto BEGIN; COMMIT; -‐-‐ XXX Add DDLs here. Thursday, January 17, 13

Slide 249

Slide 249 text

deploy/pgcrypto deploy/pgcrypto.sql -‐-‐ Deploy pgcrypto BEGIN; COMMIT; CREATE EXTENSION pgcrypto; Thursday, January 17, 13

Slide 250

Slide 250 text

Add pgcrypto > Thursday, January 17, 13

Slide 251

Slide 251 text

sqitch add pgcrypto -‐n 'Loads pgcrypto extension.' Adding deploy/pgcrypto.sql Adding revert/pgcrypto.sql Adding verify/pgcrypto.sql Added "pgcrypto" to sqitch.plan > emacs deploy/pgcrpyto.sql > Add pgcrypto > Thursday, January 17, 13

Slide 252

Slide 252 text

Slide 253

Slide 253 text

-‐-‐ Verify pgcrypto BEGIN; verify/pgcrypto verify/pgcrypto.sql -‐-‐ XXX Add verifications here. COMMIT; Thursday, January 17, 13

Slide 254

Slide 254 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'); Thursday, January 17, 13

Slide 255

Slide 255 text

Slide 256

Slide 256 text

Slide 257

Slide 257 text

How to Modify? Thursday, January 17, 13

Slide 258

Slide 258 text

How to Modify? Copy insert_user.sql to new deploy file Thursday, January 17, 13

Slide 259

Slide 259 text

How to Modify? Copy insert_user.sql to new deploy file Change that new file Thursday, January 17, 13

Slide 260

Slide 260 text

How to Modify? Copy insert_user.sql to new deploy file Change that new file Copy insert_user.sql to new revert file Thursday, January 17, 13

Slide 261

Slide 261 text

How to Modify? Copy insert_user.sql to new deploy file Change that new file Copy insert_user.sql to new revert file Test it Thursday, January 17, 13

Slide 262

Slide 262 text

How to Modify? 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 Thursday, January 17, 13

Slide 263

Slide 263 text

How to Modify? 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… Thursday, January 17, 13

Slide 264

Slide 264 text

> Thursday, January 17, 13

Slide 265

Slide 265 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)); +$$; > Thursday, January 17, 13

Slide 266

Slide 266 text

Slide 267

Slide 267 text

Let Sqitch do the work. Thursday, January 17, 13

Slide 268

Slide 268 text

Rework It > Thursday, January 17, 13

Slide 269

Slide 269 text

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

Slide 270

Slide 270 text

Slide 271

Slide 271 text

Slide 272

Slide 272 text

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

Slide 273

Slide 273 text

Same Files? > Thursday, January 17, 13

Slide 274

Slide 274 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 # # Untracked files: # (use "git add ..." to include in what will be comm # # deploy/[email protected]‐dev1.sql # revert/[email protected]‐dev1.sql # verify/[email protected]‐dev1.sql no changes added to commit (use "git add" and/or "git commit Same Files? > Thursday, January 17, 13

Slide 275

Slide 275 text

Slide 276

Slide 276 text

Slide 277

Slide 277 text

Slide 278

Slide 278 text

Slide 279

Slide 279 text

What’s the Diff? > Thursday, January 17, 13

Slide 280

Slide 280 text

What’s the Diff? diff -‐u 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; > Thursday, January 17, 13

Slide 281

Slide 281 text

Rework change_pass > Thursday, January 17, 13

Slide 282

Slide 282 text

Rework change_pass > sqitch rework change_pass -‐r pgcrypto \ -‐n 'Change change_pass to use pgcrypto.' Added "change_pass [[email protected]‐dev1 pgcrypto]" to sqitch.plan. Modify these files as appropriate: * deploy/change_pass.sql * revert/change_pass.sql * verify/change_pass.sql > Thursday, January 17, 13

Slide 283

Slide 283 text

Slide 284

Slide 284 text

Slide 285

Slide 285 text

Rework change_pass > Same files? sqitch rework change_pass -‐r pgcrypto \ -‐n 'Change change_pass to use pgcrypto.' Added "change_pass [[email protected]‐dev1 pgcrypto]" to sqitch.plan. Modify these files as appropriate: * deploy/change_pass.sql * revert/change_pass.sql * verify/change_pass.sql > Thursday, January 17, 13

Slide 286

Slide 286 text

What’s the Diff? > Thursday, January 17, 13

Slide 287

Slide 287 text

What’s the Diff? diff -‐u deploy/change_pass.sql @@ -‐1,6 +1,7 @@ -‐-‐ Deploy change_pass -‐-‐ requires: users -‐-‐ requires: appschema +-‐-‐ requires: pgcrypto BEGIN; @@ -‐11,9 +12,9 @@ CREATE OR REPLACE FUNCTION flipr.change_pass( ) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN UPDATE flipr.users -‐ SET password = md5($3) + SET password = crypt($3, gen_salt('md5')) WHERE nickname = $1 -‐ AND password = md5($2); + AND password = crypt($2, password); RETURN FOUND; END; $$; > Thursday, January 17, 13

Slide 288

Slide 288 text

Send it Up! > Thursday, January 17, 13

Slide 289

Slide 289 text

Send it Up! > sqitch deploy Deploying changes to flipr_test + insert_user .. ok + change_pass .. ok > > Thursday, January 17, 13

Slide 290

Slide 290 text

Send it Up! > sqitch deploy Deploying changes to flipr_test + insert_user .. ok + change_pass .. ok > > psql -‐d flipr_test -‐c " DELETE FROM users; SELECT insert_user('foo', 'secr3t'), insert_user('bar', 'secr3t'); SELECT nickname, password FROM users; " nickname | password -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ foo | $1$l6OEKyF3$kv5ae7505ROub75d9QKTh/ bar | $1$J4NJDgaJ$578i9Lt6b8ohJwi6WhNNO1 > Thursday, January 17, 13

Slide 291

Slide 291 text

Slide 292

Slide 292 text

Can We Go Back? > Thursday, January 17, 13

Slide 293

Slide 293 text

Can We Go Back? > sqitch revert -‐-‐to @HEAD^^ Reverting changes to pgcrypto from flipr_test -‐ change_pass .. ok -‐ insert_user .. ok > > Thursday, January 17, 13

Slide 294

Slide 294 text

psql -‐d flipr_test -‐c " DELETE FROM users; SELECT insert_user('foo', 'secr3t'), insert_user('bar', 'secr3t'); SELECT nickname, password FROM users; " nickname | password -‐-‐-‐-‐-‐-‐-‐-‐-‐-‐+-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐-‐ foo | 9695da4dd567a19f9b92065f240c6725 bar | 9695da4dd567a19f9b92065f240c6725 > Can We Go Back? > sqitch revert -‐-‐to @HEAD^^ Reverting changes to pgcrypto from flipr_test -‐ change_pass .. ok -‐ insert_user .. ok > > Thursday, January 17, 13

Slide 295

Slide 295 text

Verify How? > Thursday, January 17, 13

Slide 296

Slide 296 text

Verify How? > emacs verify/insert_user.sql > Thursday, January 17, 13

Slide 297

Slide 297 text

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

Slide 298

Slide 298 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; Thursday, January 17, 13

Slide 299

Slide 299 text

Slide 300

Slide 300 text

> emacs verify/insert_user.sql > Verify How? Thursday, January 17, 13

Slide 301

Slide 301 text

> emacs verify/insert_user.sql > Verify How? emacs verify/change_pass.sql Thursday, January 17, 13

Slide 302

Slide 302 text

-‐-‐ Verify change_pass BEGIN; SELECT has_function_privilege( 'flipr.change_pass(text, text, text)', 'execute' ); verify/change_p verify/change_pass.sql COMMIT; Thursday, January 17, 13

Slide 303

Slide 303 text

SELECT 1/COUNT(*) FROM pg_catalog.pg_proc WHERE proname = 'change_pass' AND pg_get_functiondef(oid) LIKE $$%crypt($3, gen_salt('md5'))%$$; -‐-‐ Verify change_pass BEGIN; SELECT has_function_privilege( 'flipr.change_pass(text, text, text)', 'execute' ); verify/change_p verify/change_pass.sql COMMIT; Thursday, January 17, 13

Slide 304

Slide 304 text

> emacs verify/insert_user.sql > emacs verify/change_pass.sql > Let’s Go! Thursday, January 17, 13

Slide 305

Slide 305 text

> emacs verify/insert_user.sql > emacs verify/change_pass.sql > Let’s Go! sqitch deploy Deploying changes to flipr_test + insert_user .. ok + change_pass .. ok > Shazam! Thursday, January 17, 13

Slide 306

Slide 306 text

What About Bundling? > Thursday, January 17, 13

Slide 307

Slide 307 text

What About Bundling? > > sqitch tag v1.0.0-‐b1 -‐n 'Tag v1.0.0-‐b1.' > git tag v1.0.0-‐b1 -‐am 'Tag v1.0.0-‐b1' > sqitch bundle Bundling into bundle Writing config Writing plan Writing scripts + appschema + users + insert_user + change_pass @v1.0.0-‐dev1 + pgcrypto + insert_user + change_pass @v1.0.0-‐b1 > Thursday, January 17, 13

Slide 308

Slide 308 text

Slide 309

Slide 309 text

Slide 310

Slide 310 text

What About Bundling? > emacs bundle/sqitch.plan > sqitch tag v1.0.0-‐b1 -‐n 'Tag v1.0.0-‐b1.' > git tag v1.0.0-‐b1 -‐am 'Tag v1.0.0-‐b1' > sqitch bundle Bundling into bundle Writing config Writing plan Writing scripts + appschema + users + insert_user + change_pass @v1.0.0-‐dev1 + pgcrypto + insert_user + change_pass @v1.0.0-‐b1 > Thursday, January 17, 13

Slide 311

Slide 311 text

bundle/sqitch.p What’s the Plan? %syntax-‐version=1.0.0-‐b2 %project=flipr %uri=https://github.com/theory/sqitch-‐intro/ appschema 2012-‐12-‐29T01:08:29Z Marge N. O’Vera # App schema for all flipr objects. users [appschema] 2012-‐12-‐31T19:38:06Z Marge N. O’Vera # Creates table to track our users. insert_user [users appschema] 2012-‐12-‐31T22:44:27Z Marge N. O’Vera # Creates a function to insert a user. change_pass [users appschema] 2012-‐12-‐31T22:44:38Z Marge N. O’Vera # Creates a function to change a user password. @v1.0.0-‐dev1 2013-‐01-‐03T21:33:00Z Marge N. O’Vera # Tag v1.0.0-‐dev1. pgcrypto 2013-‐01-‐03T21:36:22Z Marge N. O’Vera # Loads the pgcrypto extension. insert_user [[email protected]‐dev1 pgcrypto] 2013-‐01-‐03T22:09:50Z Marge N. O’Vera # Change insert_user to use pgcrypto. change_pass [[email protected]‐dev1 pgcrypto] 2013-‐01-‐03T22:13:58Z Marge N. O’Vera # Change change_pass to use pgcrypto. @v1.0.0-‐b1 2013-‐01-‐03T22:25:42Z Marge N. O’Vera # Tag Thursday, January 17, 13

Slide 312

Slide 312 text

Slide 313

Slide 313 text

Slide 314

Slide 314 text

Slide 315

Slide 315 text

Slide 316

Slide 316 text

Make it So > Thursday, January 17, 13

Slide 317

Slide 317 text

cd bundle > Make it So > Thursday, January 17, 13

Slide 318

Slide 318 text

cd bundle > Make it So > sqitch -‐-‐db-‐name flipr_stage deploy Deploying changes to flipr_stage + pgcrypto ................ ok + insert_user ............. ok + change_pass @v1.0.0-‐b1 .. ok > Thursday, January 17, 13

Slide 319

Slide 319 text

cd bundle > Make it So > sqitch -‐-‐db-‐name flipr_stage status # On database flipr_stage # Project: flipr # Change: 6238d88bb11fa0e9ac9b1a90831bb45f16a8e19a # Name: change_pass # Tag: @v1.0.0-‐b1 # Deployed: 2013-‐01-‐08 14:02:42 -‐0800 # By: Marge N. O’Vera # Nothing to deploy (up-‐to-‐date) sqitch -‐-‐db-‐name flipr_stage deploy Deploying changes to flipr_stage + pgcrypto ................ ok + insert_user ............. ok + change_pass @v1.0.0-‐b1 .. ok > Thursday, January 17, 13

Slide 320

Slide 320 text

Slide 321

Slide 321 text

Ship It! Thursday, January 17, 13

Slide 322

Slide 322 text

Other Commands Thursday, January 17, 13

Slide 323

Slide 323 text

Other Commands help - Get it Thursday, January 17, 13

Slide 324

Slide 324 text

Other Commands help - Get it log - Like git log Thursday, January 17, 13

Slide 325

Slide 325 text

Other Commands help - Get it log - Like git log rebase - Revert and deploy in one command Thursday, January 17, 13

Slide 326

Slide 326 text

Other Commands help - Get it log - Like git log rebase - Revert and deploy in one command check - Validate plan Thursday, January 17, 13

Slide 327

Slide 327 text

Forthcoming Thursday, January 17, 13

Slide 328

Slide 328 text

Forthcoming Port to SQLite Thursday, January 17, 13

Slide 329

Slide 329 text

Forthcoming Port to SQLite Add VCS/SCM integration Thursday, January 17, 13

Slide 330

Slide 330 text

Forthcoming Port to SQLite Add VCS/SCM integration Sync tags Thursday, January 17, 13

Slide 331

Slide 331 text

Forthcoming Port to SQLite Add VCS/SCM integration Sync tags Fetch reworked files from history Thursday, January 17, 13

Slide 332

Slide 332 text

Forthcoming Port to SQLite Add VCS/SCM integration Sync tags Fetch reworked files from history Bootstrap plan from history Thursday, January 17, 13

Slide 333

Slide 333 text

Forthcoming Thursday, January 17, 13

Slide 334

Slide 334 text

Forthcoming Add plan modification commands: Thursday, January 17, 13

Slide 335

Slide 335 text

Forthcoming Add plan modification commands: Delete change Thursday, January 17, 13

Slide 336

Slide 336 text

Forthcoming Add plan modification commands: Delete change Delete tag Thursday, January 17, 13

Slide 337

Slide 337 text

Forthcoming Add plan modification commands: Delete change Delete tag Reorder changes Thursday, January 17, 13

Slide 338

Slide 338 text

Forthcoming Add plan modification commands: Delete change Delete tag Reorder changes Modify dependencies Thursday, January 17, 13

Slide 339

Slide 339 text

Fork It Thursday, January 17, 13

Slide 340

Slide 340 text

Fork It http:/ /sqitch.org/ Thursday, January 17, 13

Slide 341

Slide 341 text

Fork It http:/ /sqitch.org/ https:/ /github.com/theory/sqitch/ Thursday, January 17, 13

Slide 342

Slide 342 text

Fork It http:/ /sqitch.org/ https:/ /github.com/theory/sqitch/ Opinions wanted Thursday, January 17, 13

Slide 343

Slide 343 text

Fork It http:/ /sqitch.org/ https:/ /github.com/theory/sqitch/ Opinions wanted Coders wanted Thursday, January 17, 13

Slide 344

Slide 344 text

Fork It http:/ /sqitch.org/ https:/ /github.com/theory/sqitch/ Opinions wanted Coders wanted Doc writers and web designers wanted! Thursday, January 17, 13

Slide 345

Slide 345 text

Fork It http:/ /sqitch.org/ https:/ /github.com/theory/sqitch/ Opinions wanted Coders wanted Doc writers and web designers wanted! Make it great! Thursday, January 17, 13