Sane Database Change Management with Sqitch

Sqitch is the sane database schema deployment tool. This presentation, for the Portland Perl Mongers, provides a technical introduction to Sqitch, with detailed usage examples to help get you started. See also the tutorial at https://metacpan.org/module/sqitchtutorial.

Transcript

1. 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
2. Whats Wrong with Migrations? Thursday, January 17, 13
3. Whats Wrong with Migrations? Incomplete mini-language Thursday, January 17, 13
4. Whats Wrong with Migrations? Incomplete mini-language No logical replication integration Thursday, January 17, 13
5. Whats Wrong with Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track Thursday, January 17, 13
6. Whats Wrong with Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness Thursday, January 17, 13
7. What about SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness Thursday, January 17, 13
8. What about SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness ——————————————— Thursday, January 17, 13
9. What about SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness ——————————————— ——————————————————— Thursday, January 17, 13
10. What about SQL Migrations? Incomplete mini-language No logical replication integration Numbered scripts hard to track No VCS awareness ——————————————— ——————————————————— Managing procedures is a PITA Thursday, January 17, 13
11. Imagine this Change > Thursday, January 17, 13
12. 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
13. 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 Simple, right? > Thursday, January 17, 13
14. Not So Much Thursday, January 17, 13
15. Not So Much Paste entire function to new “up” script Thursday, January 17, 13
16. Not So Much Paste entire function to new “up” script Edit the new file Thursday, January 17, 13
17. 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
18. 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
19. 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
20. 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
21. Thursday, January 17, 13
22. Introducing Sqitch Thursday, January 17, 13
23. Sq—what? sql anges ch Thursday, January 17, 13
24. Sq—what? sq ch Thursday, January 17, 13
25. it Sq—what? sq ch Thursday, January 17, 13
26. it Sq—what? sq ch There is no “u” Thursday, January 17, 13
27. Sqitch Philosophy Thursday, January 17, 13
28. Sqitch Philosophy No opinions Thursday, January 17, 13
29. Sqitch Philosophy No opinions Native scripting Thursday, January 17, 13
30. Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution Thursday, January 17, 13
31. Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution No numbering Thursday, January 17, 13
32. Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution No numbering Distribution bundling Thursday, January 17, 13
33. Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution No numbering Distribution bundling Integrated validation testing Thursday, January 17, 13
34. Sqitch Philosophy No opinions Native scripting Cross-project dependency resolution No numbering Distribution bundling Integrated validation testing VCS integration Thursday, January 17, 13
35. Sqitch Philosophy Thursday, January 17, 13
36. Sqitch Philosophy Reduced duplication Thursday, January 17, 13
37. Sqitch Philosophy Reduced duplication Built-in configuration Thursday, January 17, 13
38. Sqitch Philosophy Reduced duplication Built-in configuration Iterative development Thursday, January 17, 13
39. Sqitch Philosophy Reduced duplication Built-in configuration Iterative development Deployment planning Thursday, January 17, 13
40. Sqitch Philosophy Reduced duplication Built-in configuration Iterative development Deployment planning Git-style interface Thursday, January 17, 13
41. Sqitch Philosophy Reduced duplication Built-in configuration Iterative development Deployment planning Git-style interface Deployment tagging Thursday, January 17, 13
42. Sqitch Terminology Thursday, January 17, 13
43. Sqitch Terminology change Thursday, January 17, 13
44. Sqitch Terminology change tag Thursday, January 17, 13
45. Sqitch Terminology change tag state Thursday, January 17, 13
46. Sqitch Terminology change tag state plan Thursday, January 17, 13
47. Sqitch Terminology change tag state plan add Thursday, January 17, 13
48. Sqitch Terminology change tag state plan add deploy Thursday, January 17, 13
49. Sqitch Terminology change tag state plan add deploy revert Thursday, January 17, 13
50. Sqitch Terminology change tag state plan add deploy revert rebase Thursday, January 17, 13
51. Sqitch Terminology change tag state plan add deploy revert rebase rework Thursday, January 17, 13
52. Sqitch Terminology change tag state plan add deploy revert rebase rework verify Thursday, January 17, 13
53. Sqitch Terminology change tag state plan add deploy revert rebase rework verify committer Thursday, January 17, 13
54. Sqitch Terminology change tag state plan add deploy revert rebase rework verify committer planner Thursday, January 17, 13
55. How it Works > Thursday, January 17, 13
56.    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
57.    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
58.    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
59.    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
60. 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
61. 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  =   --engine pg Thursday, January 17, 13
62. Add User Config > Thursday, January 17, 13
63.    sqitch  config  -­‐-­‐user  core.pg.client  /opt/local/pgsql/bin/psql > Add User Config > Thursday, January 17, 13
64.    sqitch  config  -­‐-­‐user  core.pg.client  /opt/local/pgsql/bin/psql > Add User Config > Thursday, January 17, 13
65.    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
66.    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  'marge@example.com' > Add User Config > Thursday, January 17, 13
67.    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  'marge@example.com' > Add User Config > Thursday, January 17, 13
68. ~/.sqitch/sqitc ~/ .sqitch/sqitch.conf [core  "pg"]   client  =  /opt/local/pgsql/bin/psql [user]     name  =  Marge  N.  O’Vera       email  =  marge@example.com Thursday, January 17, 13
69. ~/.sqitch/sqitc ~/ .sqitch/sqitch.conf [core  "pg"]   client  =  /opt/local/pgsql/bin/psql [user]     name  =  Marge  N.  O’Vera       email  =  marge@example.com Good for all projects Thursday, January 17, 13
70. What’s the Plan Man? > Thursday, January 17, 13
71. What’s the Plan Man?    emacs  sqitch.plan > Thursday, January 17, 13
72. sqitch.plan sqitch.plan %syntax-­‐version=1.0.0-­‐b1 %project=flipr %uri=https://github.com/theory/sqitch-­‐intro/ Thursday, January 17, 13
73. sqitch.plan sqitch.plan %syntax-­‐version=1.0.0-­‐b1 %project=flipr %uri=https://github.com/theory/sqitch-­‐intro/ Identified Thursday, January 17, 13
74. Make It So > Thursday, January 17, 13
75. 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
76. First Deployment > Thursday, January 17, 13
77. >  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
78. >  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
79. >  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
80. >  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 >    emacs  deploy/appschema.sql Thursday, January 17, 13
81. deploy/appschem deploy/appschema.sql -­‐-­‐  Deploy  appschema BEGIN; COMMIT; -­‐-­‐  XXX  Add  DDLs  here. Thursday, January 17, 13
82. deploy/appschem deploy/appschema.sql -­‐-­‐  Deploy  appschema BEGIN; COMMIT; CREATE  SCHEMA  flipr; Thursday, January 17, 13
83. 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
84. 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 > >    emacs  revert/appschema.sql Thursday, January 17, 13
85. revert/appschem revert/appschema.sql -­‐-­‐  Revert  appschema BEGIN; COMMIT; -­‐-­‐  XXX  Add  DDLs  here. Thursday, January 17, 13
86. revert/appschem revert/appschema.sql -­‐-­‐  Revert  appschema BEGIN; COMMIT; DROP  SCHEMA  flipr; Thursday, January 17, 13
87. Make it So! > Thursday, January 17, 13
88. 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
89. 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
90. 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
91. 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
92. 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
93. 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 > Trust, but verify Thursday, January 17, 13
94. 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 >    emacs  verify/appschema.sql Thursday, January 17, 13
95. verify/appschem verify/appschema.sql -­‐-­‐  Verify  appschema BEGIN; COMMIT; -­‐-­‐  XXX  Add  verifications  here. Thursday, January 17, 13
96. SELECT  pg_catalog.has_schema_privilege('nada',  'usage'); verify/appschem verify/appschema.sql -­‐-­‐  Verify  appschema BEGIN; COMMIT; Thursday, January 17, 13
97. 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
98. SELECT  pg_catalog.has_schema_privilege('nada',  'usage'); verify/appschem verify/appschema.sql -­‐-­‐  Verify  appschema BEGIN; COMMIT; Thursday, January 17, 13
99. Trust, But Verify >    emacs  verify/appschema.sql > Thursday, January 17, 13
100. 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
101. 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
102. 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
103. 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
104. Trust, But Verify > Thursday, January 17, 13
105.    perl  -­‐i  -­‐pe  's/nada/flipr/' >   Trust, But Verify > Thursday, January 17, 13
106.    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
107. > How’s it Look? Thursday, January 17, 13
108. > >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > How’s it Look? Thursday, January 17, 13
109. > >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > How’s it Look? Thursday, January 17, 13
110. > >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > How’s it Look? Thursday, January 17, 13
111. > >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > How’s it Look? Thursday, January 17, 13
112. > >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > How’s it Look? Thursday, January 17, 13
113. > >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > How’s it Look? Thursday, January 17, 13
114. Go Back > Thursday, January 17, 13
115. >  sqitch  -­‐-­‐db-­‐name  flipr_test  revert    Revert  all  changes  from  flipr_test?  [Yes] Go Back > ▮ Thursday, January 17, 13
116. >  sqitch  -­‐-­‐db-­‐name  flipr_test  revert    Revert  all  changes  from  flipr_test?  [Yes] Go Back > ▮ Thursday, January 17, 13
117. >  sqitch  -­‐-­‐db-­‐name  flipr_test  revert    Revert  all  changes  from  flipr_test?  [Yes] Go Back >    -­‐  appschema  ..  ok > Thursday, January 17, 13
118. >  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
119. >  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 >    sqitch  -­‐d  flipr_test  status #  On  database  flipr_test No  changes  deployed > Thursday, January 17, 13
120. History > Thursday, January 17, 13
121. History > >  sqitch  -­‐d  flipr_test  log On  database  flipr_test Revert  ae1263b92b3355cf705c7832271730fe4ccb3c77 Name:            appschema Committer:  Marge  N.  O’Vera  <marge@example.com> Date:            2012-­‐12-­‐31  11:01:22  -­‐0800        App  schema  for  all  flipr  objects. Deploy  ae1263b92b3355cf705c7832271730fe4ccb3c77 Name:            appschema Committer:  Marge  N.  O’Vera  <marge@example.com> Date:            2012-­‐12-­‐31  11:00:29  -­‐0800        App  schema  for  all  flipr  objects. Thursday, January 17, 13
122. History > >  sqitch  -­‐d  flipr_test  log On  database  flipr_test Revert  ae1263b92b3355cf705c7832271730fe4ccb3c77 Name:            appschema Committer:  Marge  N.  O’Vera  <marge@example.com> Date:            2012-­‐12-­‐31  11:01:22  -­‐0800        App  schema  for  all  flipr  objects. Deploy  ae1263b92b3355cf705c7832271730fe4ccb3c77 Name:            appschema Committer:  Marge  N.  O’Vera  <marge@example.com> Date:            2012-­‐12-­‐31  11:00:29  -­‐0800        App  schema  for  all  flipr  objects. Thursday, January 17, 13
123. History > >  sqitch  -­‐d  flipr_test  log On  database  flipr_test Revert  ae1263b92b3355cf705c7832271730fe4ccb3c77 Name:            appschema Committer:  Marge  N.  O’Vera  <marge@example.com> Date:            2012-­‐12-­‐31  11:01:22  -­‐0800        App  schema  for  all  flipr  objects. Deploy  ae1263b92b3355cf705c7832271730fe4ccb3c77 Name:            appschema Committer:  Marge  N.  O’Vera  <marge@example.com> Date:            2012-­‐12-­‐31  11:00:29  -­‐0800        App  schema  for  all  flipr  objects. Thursday, January 17, 13
124. Commit It! > Thursday, January 17, 13
125. >  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 > Commit It! > Thursday, January 17, 13
126. >  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
127. >  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! > Integrated! Thursday, January 17, 13
128. >  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
129. >  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! >    psql  -­‐d  flipr_test  -­‐c  '\dn  flipr' List  of  schemas  Name    |  Owner   -­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐  flipr  |  marge Thursday, January 17, 13
130. Status Update > Thursday, January 17, 13
131. >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > Status Update > Thursday, January 17, 13
132. Save My Fingers > Thursday, January 17, 13
133. Save My Fingers >    sqitch  config  core.pg.db_name  flipr_test > Thursday, January 17, 13
134. 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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > No --db-name Thursday, January 17, 13
135. 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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > No --db-name >  sqitch  config  -­‐-­‐bool  deploy.verify  true >  sqitch  config  -­‐-­‐bool  rebase.verify  true > Always verify. Thursday, January 17, 13
136. Dependencies! > Thursday, January 17, 13
137. 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
138. 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
139. 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 >    emacs  deploy/users.sql Thursday, January 17, 13
140. deploy/users.sq deploy/users.sql -­‐-­‐  XXX  Add  DDLs  here. COMMIT; -­‐-­‐  Deploy  users -­‐-­‐  requires:  appschema BEGIN; Thursday, January 17, 13
141. deploy/users.sq deploy/users.sql -­‐-­‐  XXX  Add  DDLs  here. COMMIT; -­‐-­‐  Deploy  users -­‐-­‐  requires:  appschema BEGIN; Thursday, January 17, 13
142. 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
143. 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 >  emacs  deploy/users.sql > Thursday, January 17, 13
144. 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 >  emacs  deploy/users.sql >    emacs  verify/users.sql Thursday, January 17, 13
145. -­‐-­‐  Verify  users BEGIN; verify/users.sq verify/users.sql -­‐-­‐  XXX  Add  verifications  here. COMMIT; Thursday, January 17, 13
146. -­‐-­‐  Verify  users BEGIN; SELECT  nickname,  password,  timestamp    FROM  flipr.users  WHERE  FALSE; verify/users.sq verify/users.sql COMMIT; Thursday, January 17, 13
147. 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 >  emacs  deploy/users.sql >  emacs  verify/users.sql > Thursday, January 17, 13
148. 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 >  emacs  deploy/users.sql >  emacs  verify/users.sql > >  emacs  revert/users.sql Thursday, January 17, 13
149. revert/users.sq revert/users.sql -­‐-­‐  Revert  users BEGIN; COMMIT; -­‐-­‐  XXX  Add  DDLs  here. Thursday, January 17, 13
150. revert/users.sq revert/users.sql -­‐-­‐  Revert  users BEGIN; COMMIT; DROP  TABLE  flipr.users; Thursday, January 17, 13
151. Make Users > Thursday, January 17, 13
152.    sqitch  deploy Deploying  changes  to  flipr_test    +  users  ..  ok > Make Users > Thursday, January 17, 13
153.    sqitch  deploy Deploying  changes  to  flipr_test    +  users  ..  ok > Make Users > Thursday, January 17, 13
154.    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
155. Verify Users > Thursday, January 17, 13
156.    sqitch  verify Verifying  flipr_test    *  appschema  ..  ok    *  users  ......  ok Verify  successful > Verify Users > Thursday, January 17, 13
157. Status Update > Thursday, January 17, 13
158. >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > Status Update > Thursday, January 17, 13
159. >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > Status Update > Thursday, January 17, 13
160. >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) >    sqitch  revert  -­‐-­‐to  @HEAD^  -­‐y Reverting  changes  to  appschema  from  flipr_test    -­‐  users  ..  ok > Status Update > Thursday, January 17, 13
161. >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) >    sqitch  revert  -­‐-­‐to  @HEAD^  -­‐y Reverting  changes  to  appschema  from  flipr_test    -­‐  users  ..  ok > Status Update > Yes, really. Thursday, January 17, 13
162. >  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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) >    sqitch  revert  -­‐-­‐to  @HEAD^  -­‐y Reverting  changes  to  appschema  from  flipr_test    -­‐  users  ..  ok > Status Update > Thursday, January 17, 13
163. Symbolic Tags Thursday, January 17, 13
164. Symbolic Tags @HEAD Last change Thursday, January 17, 13
165. Symbolic Tags @HEAD Last change @ROOT First change Thursday, January 17, 13
166. Symbolic Tags @HEAD Last change @ROOT First change ^ Previous change Thursday, January 17, 13
167. Symbolic Tags @HEAD Last change @ROOT First change ^ Previous change ~ Following change Thursday, January 17, 13
168. Lose Users > Thursday, January 17, 13
169. Lose Users > >  psql  -­‐d  flipr_test  -­‐c  '\d  flipr.users' Did  not  find  any  relation  named  "flipr.users". > Thursday, January 17, 13
170.    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  <marge@example.com> #   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
171. Lose Users > Thursday, January 17, 13
172. >  sqitch  verify Verifying  flipr_test    *  appschema  ..  ok Undeployed  change:    *  users Verify  successful > Lose Users > Thursday, January 17, 13
173. Lose Users > Thursday, January 17, 13
174. 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
175. 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 >    sqitch  deploy Deploying  changes  to  flipr_test    +  users  ..  ok > Thursday, January 17, 13
176. Up to Date > Thursday, January 17, 13
177. 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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > Thursday, January 17, 13
178. 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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > Thursday, January 17, 13
179. 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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > Thursday, January 17, 13
180. A Twofer > Thursday, January 17, 13
181. 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 > Thursday, January 17, 13
182. 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 > Thursday, January 17, 13
183. 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
184. 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
185. 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
186. 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  <marge@example.com>  #   App  schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. Thursday, January 17, 13
187. 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  <marge@example.com>  #   App  schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. Thursday, January 17, 13
188. 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  <marge@example.com>  #   App  schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. Thursday, January 17, 13
189. 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  <marge@example.com>  #   App  schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. Thursday, January 17, 13
190. 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  <marge@example.com>  #   App  schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. Thursday, January 17, 13
191. 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  <marge@example.com>  #   App  schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. Thursday, January 17, 13
192. 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  <marge@example.com>  #   App  schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. Thursday, January 17, 13
193. 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  <marge@example.com>  #   App  schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. Thursday, January 17, 13
194. >  emacs  sqitch.plan > A Twofer Thursday, January 17, 13
195. >  emacs  sqitch.plan > >  emacs  deploy/insert_user.sql A Twofer Thursday, January 17, 13
196. deploy/insert_u deploy/insert_user.sql -­‐-­‐  XXX  Add  DDLs  here. COMMIT; -­‐-­‐  Deploy  insert_user -­‐-­‐  requires:  appuser -­‐-­‐  requires:  users BEGIN; Thursday, January 17, 13
197. deploy/insert_u deploy/insert_user.sql -­‐-­‐  XXX  Add  DDLs  here. COMMIT; -­‐-­‐  Deploy  insert_user -­‐-­‐  requires:  appuser -­‐-­‐  requires:  users BEGIN; Thursday, January 17, 13
198. 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
199. A Twofer >    emacs  sqitch.plan >  emacs  deploy/insert_user.sql > Thursday, January 17, 13
200. A Twofer >    emacs  sqitch.plan >  emacs  deploy/insert_user.sql >    emacs  verify/insert_user.sql Thursday, January 17, 13
201. -­‐-­‐  Verify  insert_user BEGIN; verify/insert_u verify/insert_user.sql -­‐-­‐  XXX  Add  verifications  here. COMMIT; Thursday, January 17, 13
202. -­‐-­‐  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
203. A Twofer >    emacs  sqitch.plan >  emacs  deploy/insert_user.sql >  emacs  verify/insert_user.sql > Thursday, January 17, 13
204. A Twofer >    emacs  sqitch.plan >  emacs  deploy/insert_user.sql >  emacs  verify/insert_user.sql >    emacs  insert/change_pass.sql Thursday, January 17, 13
205. -­‐-­‐  Deploy  change_pass -­‐-­‐  :requires:  appuser -­‐-­‐  :requires:  users BEGIN; deploy/change_p deploy/change_pass.sql -­‐-­‐  XXX  Add  DDLs  here. COMMIT; Thursday, January 17, 13
206. -­‐-­‐  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
207. A Twofer >    emacs  sqitch.plan >  emacs  deploy/insert_user.sql >  emacs  verify/insert_user.sql >  emacs  insert/change_pass.sql > Thursday, January 17, 13
208. 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
209. -­‐-­‐  Verify  change_pass BEGIN; verify/change_p verify/change_pass.sql -­‐-­‐  XXX  Add  verifications  here. COMMIT; Thursday, January 17, 13
210. -­‐-­‐  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
211. Deploy Functions > Thursday, January 17, 13
212. >  sqitch  deploy Deploying  changes  to  flipr_test    +  insert_user  ..  ok    +  change_pass  ..  ok > Deploy Functions > Thursday, January 17, 13
213. >  sqitch  deploy Deploying  changes  to  flipr_test    +  insert_user  ..  ok    +  change_pass  ..  ok > Deploy Functions > >  psql  -­‐d  flipr_test  -­‐c  '\df  flipr.*'                                                                        List  of  function  Schema  |        Name          |  Result  data  type  |                     -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐  flipr    |  change_pass  |  boolean                    |  nick  text  flipr    |  insert_user  |  void                          |  nickname   > Thursday, January 17, 13
214. >  sqitch  deploy Deploying  changes  to  flipr_test    +  insert_user  ..  ok    +  change_pass  ..  ok > Deploy Functions > >  psql  -­‐d  flipr_test  -­‐c  '\df  flipr.*'                                                                        List  of  function  Schema  |        Name          |  Result  data  type  |                     -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐  flipr    |  change_pass  |  boolean                    |  nick  text  flipr    |  insert_user  |  void                          |  nickname   > Thursday, January 17, 13
215. Status Update > Thursday, January 17, 13
216. 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  <marge@example.com> #   Nothing  to  deploy  (up-­‐to-­‐date) > Thursday, January 17, 13
217. Revert > Thursday, January 17, 13
218. Revert > >  sqitch  revert  -­‐y  -­‐-­‐to  @HEAD^^ Reverting  changes  to  users  from  flipr_test    -­‐  change_pass  ..  ok    -­‐  insert_user  ..  ok > Thursday, January 17, 13
219. Revert > >  sqitch  revert  -­‐y  -­‐-­‐to  @HEAD^^ Reverting  changes  to  users  from  flipr_test    -­‐  change_pass  ..  ok    -­‐  insert_user  ..  ok > Thursday, January 17, 13
220. 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
221. Commit It Thursday, January 17, 13
222. Commit It git add && git commit Thursday, January 17, 13
223. Commit It git add && git commit sqitch deploy Thursday, January 17, 13
224. Commit It git add && git commit sqitch deploy And now… Thursday, January 17, 13
225. Commit It git add && git commit sqitch deploy And now… Tag Thursday, January 17, 13
226. Commit It git add && git commit sqitch deploy And now… Tag Bundle Thursday, January 17, 13
227. Commit It git add && git commit sqitch deploy And now… Tag Bundle Ship! Thursday, January 17, 13
228. Tag It! > Thursday, January 17, 13
229. 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
230. 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
231. 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
232. 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(+) >    git  tag  v1.0.0-­‐dev1  -­‐am  'Tag  v1.0.0-­‐dev1' > Thursday, January 17, 13
233. Bundle It! > Thursday, January 17, 13
234. 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
235. 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
236. Test and Ship It! > Thursday, January 17, 13
237. Test and Ship It! >    cd  bundle > Thursday, January 17, 13
238. Test and Ship It! >    createdb  flipr_stage >    cd  bundle > Thursday, January 17, 13
239.    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
240.    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 > >  cd  .. >  mv  bundle  flipr-­‐1.0.0-­‐dev1   >  tar  -­‐czf  flipr-­‐1.0.0-­‐dev1.tar.gz  flipr-­‐1.0.0-­‐dev1 > Thursday, January 17, 13
241. Reworking Changes Thursday, January 17, 13
242. Ruh-Roh > Thursday, January 17, 13
243.    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
244.    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 > Not good. Thursday, January 17, 13
245. Add pgcrypto > Thursday, January 17, 13
246.    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
247.    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 >    emacs  deploy/pgcrpyto.sql Thursday, January 17, 13
248. deploy/pgcrypto deploy/pgcrypto.sql -­‐-­‐  Deploy  pgcrypto BEGIN; COMMIT; -­‐-­‐  XXX  Add  DDLs  here. Thursday, January 17, 13
249. deploy/pgcrypto deploy/pgcrypto.sql -­‐-­‐  Deploy  pgcrypto BEGIN; COMMIT; CREATE  EXTENSION  pgcrypto; Thursday, January 17, 13
250. Add pgcrypto > Thursday, January 17, 13
251.    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
252.    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 >    emacs  verify/pgcrpyto.sql Thursday, January 17, 13
253. -­‐-­‐  Verify  pgcrypto BEGIN; verify/pgcrypto verify/pgcrypto.sql -­‐-­‐  XXX  Add  verifications  here. COMMIT; Thursday, January 17, 13
254. -­‐-­‐  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
255. -­‐-­‐  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
256. -­‐-­‐  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
257. How to Modify? Thursday, January 17, 13
258. How to Modify? Copy insert_user.sql to new deploy file Thursday, January 17, 13
259. How to Modify? Copy insert_user.sql to new deploy file Change that new file Thursday, January 17, 13
260. 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
261. 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
262. 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
263. 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
264. > Thursday, January 17, 13
265. >  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
266. >  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)); +$$; > Oy. Thursday, January 17, 13
267. Let Sqitch do the work. Thursday, January 17, 13
268. Rework It > Thursday, January 17, 13
269. Rework It >    sqitch  rework  insert_user  -­‐r  pgcrypto  \    -­‐n  'Changes  insert_user  to  use  pgcrypto.' Added  "insert_user  [insert_user@v1.0.0-­‐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
270. Rework It >    sqitch  rework  insert_user  -­‐r  pgcrypto  \    -­‐n  'Changes  insert_user  to  use  pgcrypto.' Added  "insert_user  [insert_user@v1.0.0-­‐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
271. Rework It >    sqitch  rework  insert_user  -­‐r  pgcrypto  \    -­‐n  'Changes  insert_user  to  use  pgcrypto.' Added  "insert_user  [insert_user@v1.0.0-­‐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
272. Rework It > Same files?    sqitch  rework  insert_user  -­‐r  pgcrypto  \    -­‐n  'Changes  insert_user  to  use  pgcrypto.' Added  "insert_user  [insert_user@v1.0.0-­‐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
273. Same Files? > Thursday, January 17, 13
274.    git  status #  On  branch  master #  Changes  not  staged  for  commit: #      (use  "git  add  <file>..."  to  update  what  will  be  committe #      (use  "git  checkout  -­‐-­‐  <file>..."  to  discard  changes  in  w # #   modified:      revert/insert_user.sql #   modified:      sqitch.plan # #  Untracked  files: #      (use  "git  add  <file>..."  to  include  in  what  will  be  comm # #   deploy/insert_user@v1.0.0-­‐dev1.sql #   revert/insert_user@v1.0.0-­‐dev1.sql #   verify/insert_user@v1.0.0-­‐dev1.sql no  changes  added  to  commit  (use  "git  add"  and/or  "git  commit Same Files? > Thursday, January 17, 13
275.    git  status #  On  branch  master #  Changes  not  staged  for  commit: #      (use  "git  add  <file>..."  to  update  what  will  be  committe #      (use  "git  checkout  -­‐-­‐  <file>..."  to  discard  changes  in  w # #   modified:      revert/insert_user.sql #   modified:      sqitch.plan # #  Untracked  files: #      (use  "git  add  <file>..."  to  include  in  what  will  be  comm # #   deploy/insert_user@v1.0.0-­‐dev1.sql #   revert/insert_user@v1.0.0-­‐dev1.sql #   verify/insert_user@v1.0.0-­‐dev1.sql no  changes  added  to  commit  (use  "git  add"  and/or  "git  commit Same Files? > Thursday, January 17, 13
276.    git  status #  On  branch  master #  Changes  not  staged  for  commit: #      (use  "git  add  <file>..."  to  update  what  will  be  committe #      (use  "git  checkout  -­‐-­‐  <file>..."  to  discard  changes  in  w # #   modified:      revert/insert_user.sql #   modified:      sqitch.plan # #  Untracked  files: #      (use  "git  add  <file>..."  to  include  in  what  will  be  comm # #   deploy/insert_user@v1.0.0-­‐dev1.sql #   revert/insert_user@v1.0.0-­‐dev1.sql #   verify/insert_user@v1.0.0-­‐dev1.sql no  changes  added  to  commit  (use  "git  add"  and/or  "git  commit Same Files? > As of @v1.0.0-dev1 Thursday, January 17, 13
277.    git  status #  On  branch  master #  Changes  not  staged  for  commit: #      (use  "git  add  <file>..."  to  update  what  will  be  committe #      (use  "git  checkout  -­‐-­‐  <file>..."  to  discard  changes  in  w # #   modified:      revert/insert_user.sql #   modified:      sqitch.plan # #  Untracked  files: #      (use  "git  add  <file>..."  to  include  in  what  will  be  comm # #   deploy/insert_user@v1.0.0-­‐dev1.sql #   revert/insert_user@v1.0.0-­‐dev1.sql #   verify/insert_user@v1.0.0-­‐dev1.sql no  changes  added  to  commit  (use  "git  add"  and/or  "git  commit Same Files? > Thursday, January 17, 13
278.    git  status #  On  branch  master #  Changes  not  staged  for  commit: #      (use  "git  add  <file>..."  to  update  what  will  be  committe #      (use  "git  checkout  -­‐-­‐  <file>..."  to  discard  changes  in  w # #   modified:      revert/insert_user.sql #   modified:      sqitch.plan # #  Untracked  files: #      (use  "git  add  <file>..."  to  include  in  what  will  be  comm # #   deploy/insert_user@v1.0.0-­‐dev1.sql #   revert/insert_user@v1.0.0-­‐dev1.sql #   verify/insert_user@v1.0.0-­‐dev1.sql no  changes  added  to  commit  (use  "git  add"  and/or  "git  commit Same Files? > Previous deploy becomes revert Thursday, January 17, 13
279. What’s the Diff? > Thursday, January 17, 13
280. 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
281. Rework change_pass > Thursday, January 17, 13
282. Rework change_pass >    sqitch  rework  change_pass  -­‐r  pgcrypto    \    -­‐n  'Change  change_pass  to  use  pgcrypto.'   Added  "change_pass  [change_pass@v1.0.0-­‐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
283. Rework change_pass >    sqitch  rework  change_pass  -­‐r  pgcrypto    \    -­‐n  'Change  change_pass  to  use  pgcrypto.'   Added  "change_pass  [change_pass@v1.0.0-­‐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
284. Rework change_pass >    sqitch  rework  change_pass  -­‐r  pgcrypto    \    -­‐n  'Change  change_pass  to  use  pgcrypto.'   Added  "change_pass  [change_pass@v1.0.0-­‐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
285. Rework change_pass > Same files?    sqitch  rework  change_pass  -­‐r  pgcrypto    \    -­‐n  'Change  change_pass  to  use  pgcrypto.'   Added  "change_pass  [change_pass@v1.0.0-­‐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
286. What’s the Diff? > Thursday, January 17, 13
287. 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
288. Send it Up! > Thursday, January 17, 13
289. Send it Up! >  sqitch  deploy Deploying  changes  to  flipr_test    +  insert_user  ..  ok    +  change_pass  ..  ok > > Thursday, January 17, 13
290. 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
291. 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 > \o/ Thursday, January 17, 13
292. Can We Go Back? > Thursday, January 17, 13
293. 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
294.    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
295. Verify How? > Thursday, January 17, 13
296. Verify How? >  emacs  verify/insert_user.sql > Thursday, January 17, 13
297. -­‐-­‐  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
298. 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
299. 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; Yeah, compare source. Thursday, January 17, 13
300. >  emacs  verify/insert_user.sql > Verify How? Thursday, January 17, 13
301. >  emacs  verify/insert_user.sql > Verify How?    emacs  verify/change_pass.sql Thursday, January 17, 13
302. -­‐-­‐  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
303. 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
304. >  emacs  verify/insert_user.sql >  emacs  verify/change_pass.sql > Let’s Go! Thursday, January 17, 13
305. >  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
306. What About Bundling? > Thursday, January 17, 13
307. 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
308. 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
309. 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
310. 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
311. 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  <marge@example.com>  #  App   schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera  <marge@example.com>  #   Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. @v1.0.0-­‐dev1  2013-­‐01-­‐03T21:33:00Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag   v1.0.0-­‐dev1. pgcrypto  2013-­‐01-­‐03T21:36:22Z  Marge  N.  O’Vera  <marge@example.com>  #  Loads  the   pgcrypto  extension. insert_user  [insert_user@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:09:50Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  insert_user  to  use  pgcrypto. change_pass  [change_pass@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:13:58Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  change_pass  to  use  pgcrypto. @v1.0.0-­‐b1  2013-­‐01-­‐03T22:25:42Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag Thursday, January 17, 13
312. 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  <marge@example.com>  #  App   schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera  <marge@example.com>  #   Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. @v1.0.0-­‐dev1  2013-­‐01-­‐03T21:33:00Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag   v1.0.0-­‐dev1. pgcrypto  2013-­‐01-­‐03T21:36:22Z  Marge  N.  O’Vera  <marge@example.com>  #  Loads  the   pgcrypto  extension. insert_user  [insert_user@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:09:50Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  insert_user  to  use  pgcrypto. change_pass  [change_pass@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:13:58Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  change_pass  to  use  pgcrypto. @v1.0.0-­‐b1  2013-­‐01-­‐03T22:25:42Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag Thursday, January 17, 13
313. 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  <marge@example.com>  #  App   schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera  <marge@example.com>  #   Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. @v1.0.0-­‐dev1  2013-­‐01-­‐03T21:33:00Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag   v1.0.0-­‐dev1. pgcrypto  2013-­‐01-­‐03T21:36:22Z  Marge  N.  O’Vera  <marge@example.com>  #  Loads  the   pgcrypto  extension. insert_user  [insert_user@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:09:50Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  insert_user  to  use  pgcrypto. change_pass  [change_pass@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:13:58Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  change_pass  to  use  pgcrypto. @v1.0.0-­‐b1  2013-­‐01-­‐03T22:25:42Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag Thursday, January 17, 13
314. 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  <marge@example.com>  #  App   schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera  <marge@example.com>  #   Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. @v1.0.0-­‐dev1  2013-­‐01-­‐03T21:33:00Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag   v1.0.0-­‐dev1. pgcrypto  2013-­‐01-­‐03T21:36:22Z  Marge  N.  O’Vera  <marge@example.com>  #  Loads  the   pgcrypto  extension. insert_user  [insert_user@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:09:50Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  insert_user  to  use  pgcrypto. change_pass  [change_pass@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:13:58Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  change_pass  to  use  pgcrypto. @v1.0.0-­‐b1  2013-­‐01-­‐03T22:25:42Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag Thursday, January 17, 13
315. 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  <marge@example.com>  #  App   schema  for  all  flipr  objects. users  [appschema]  2012-­‐12-­‐31T19:38:06Z  Marge  N.  O’Vera  <marge@example.com>  #   Creates  table  to  track  our  users. insert_user  [users  appschema]  2012-­‐12-­‐31T22:44:27Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  insert  a  user. change_pass  [users  appschema]  2012-­‐12-­‐31T22:44:38Z  Marge  N.  O’Vera   <marge@example.com>  #  Creates  a  function  to  change  a  user  password. @v1.0.0-­‐dev1  2013-­‐01-­‐03T21:33:00Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag   v1.0.0-­‐dev1. pgcrypto  2013-­‐01-­‐03T21:36:22Z  Marge  N.  O’Vera  <marge@example.com>  #  Loads  the   pgcrypto  extension. insert_user  [insert_user@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:09:50Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  insert_user  to  use  pgcrypto. change_pass  [change_pass@v1.0.0-­‐dev1  pgcrypto]  2013-­‐01-­‐03T22:13:58Z  Marge  N.   O’Vera  <marge@example.com>  #  Change  change_pass  to  use  pgcrypto. @v1.0.0-­‐b1  2013-­‐01-­‐03T22:25:42Z  Marge  N.  O’Vera  <marge@example.com>  #  Tag Thursday, January 17, 13
316. Make it So > Thursday, January 17, 13
317.    cd  bundle > Make it So > Thursday, January 17, 13
318.    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
319.    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  <marge@example.com> #   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
320.    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  <marge@example.com> #   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
321. Ship It! Thursday, January 17, 13
322. Other Commands Thursday, January 17, 13
323. Other Commands help - Get it Thursday, January 17, 13
324. Other Commands help - Get it log - Like git log Thursday, January 17, 13
325. Other Commands help - Get it log - Like git log rebase - Revert and deploy in one command Thursday, January 17, 13
326. Other Commands help - Get it log - Like git log rebase - Revert and deploy in one command check - Validate plan Thursday, January 17, 13
327. Forthcoming Thursday, January 17, 13
328. Forthcoming Port to SQLite Thursday, January 17, 13
329. Forthcoming Port to SQLite Add VCS/SCM integration Thursday, January 17, 13
330. Forthcoming Port to SQLite Add VCS/SCM integration Sync tags Thursday, January 17, 13
331. Forthcoming Port to SQLite Add VCS/SCM integration Sync tags Fetch reworked files from history Thursday, January 17, 13
332. Forthcoming Port to SQLite Add VCS/SCM integration Sync tags Fetch reworked files from history Bootstrap plan from history Thursday, January 17, 13
333. Forthcoming Thursday, January 17, 13
334. Forthcoming Add plan modification commands: Thursday, January 17, 13
335. Forthcoming Add plan modification commands: Delete change Thursday, January 17, 13
336. Forthcoming Add plan modification commands: Delete change Delete tag Thursday, January 17, 13
337. Forthcoming Add plan modification commands: Delete change Delete tag Reorder changes Thursday, January 17, 13
338. Forthcoming Add plan modification commands: Delete change Delete tag Reorder changes Modify dependencies Thursday, January 17, 13
339. Fork It Thursday, January 17, 13
340. Fork It http:/ /sqitch.org/ Thursday, January 17, 13
341. Fork It http:/ /sqitch.org/ https:/ /github.com/theory/sqitch/ Thursday, January 17, 13
342. Fork It http:/ /sqitch.org/ https:/ /github.com/theory/sqitch/ Opinions wanted Thursday, January 17, 13
343. Fork It http:/ /sqitch.org/ https:/ /github.com/theory/sqitch/ Opinions wanted Coders wanted Thursday, January 17, 13
344. Fork It http:/ /sqitch.org/ https:/ /github.com/theory/sqitch/ Opinions wanted Coders wanted Doc writers and web designers wanted! Thursday, January 17, 13
345. 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
346. Thank you. Thursday, January 17, 13
347. 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