$30 off During Our Annual Pro Sale. View Details »

Sane Database Change Management with Sqitch

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.

David E. Wheeler

January 17, 2013
Tweet

More Decks by David E. Wheeler

Other Decks in Technology

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

    View Slide

  2. Whats Wrong with
    Migrations?
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  11. Imagine this Change
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  14. Not So Much
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  21. Thursday, January 17, 13

    View Slide

  22. Introducing
    Sqitch
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  27. Sqitch Philosophy
    Thursday, January 17, 13

    View Slide

  28. Sqitch Philosophy
    No opinions
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  35. Sqitch Philosophy
    Thursday, January 17, 13

    View Slide

  36. Sqitch Philosophy
    Reduced duplication
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  42. Sqitch Terminology
    Thursday, January 17, 13

    View Slide

  43. Sqitch Terminology
    change
    Thursday, January 17, 13

    View Slide

  44. Sqitch Terminology
    change
    tag
    Thursday, January 17, 13

    View Slide

  45. Sqitch Terminology
    change
    tag
    state
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  55. How it Works
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  62. Add User Config
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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  '[email protected]'
    >
    Add User Config
    >
    Thursday, January 17, 13

    View Slide

  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  '[email protected]'
    >
    Add User Config
    >
    Thursday, January 17, 13

    View Slide

  68. ~/.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

    View Slide

  69. ~/.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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  74. Make It So
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  76. First Deployment
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  87. Make it So!
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  104. Trust, But Verify
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    How’s it Look?
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    How’s it Look?
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    How’s it Look?
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    How’s it Look?
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    How’s it Look?
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    How’s it Look?
    Thursday, January 17, 13

    View Slide

  114. Go Back
    >
    Thursday, January 17, 13

    View Slide

  115. >  sqitch  -­‐-­‐db-­‐name  flipr_test  revert
       Revert  all  changes  from  flipr_test?  [Yes]
    Go Back
    >

    Thursday, January 17, 13

    View Slide

  116. >  sqitch  -­‐-­‐db-­‐name  flipr_test  revert
       Revert  all  changes  from  flipr_test?  [Yes]
    Go Back
    >

    Thursday, January 17, 13

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  120. History
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  124. Commit It!
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  130. Status Update
    >
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    Status Update
    >
    Thursday, January 17, 13

    View Slide

  132. Save My Fingers
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    No --db-name
    Thursday, January 17, 13

    View Slide

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

    View Slide

  136. Dependencies!
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  151. Make Users
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  155. Verify Users
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

  157. Status Update
    >
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    Status Update
    >
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    Status Update
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  163. Symbolic Tags
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  168. Lose Users
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

  171. Lose Users
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

  173. Lose Users
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  176. Up to Date
    >
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    Thursday, January 17, 13

    View Slide

  180. A Twofer
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

  211. Deploy Functions
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  215. Status Update
    >
    Thursday, January 17, 13

    View Slide

  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  
    #  
    Nothing  to  deploy  (up-­‐to-­‐date)
    >
    Thursday, January 17, 13

    View Slide

  217. Revert
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  221. Commit It
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  228. Tag It!
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  233. Bundle It!
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  241. Reworking Changes
    Thursday, January 17, 13

    View Slide

  242. Ruh-Roh
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  245. Add pgcrypto
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  250. Add pgcrypto
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  257. How to Modify?
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  264. >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  268. Rework It
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  273. Same Files?
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

  276.    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?
    >
    As of
    @v1.0.0-dev1
    Thursday, January 17, 13

    View Slide

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

    View Slide

  278.    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?
    >
    Previous deploy
    becomes revert
    Thursday, January 17, 13

    View Slide

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

    View Slide

  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

    View Slide

  281. Rework change_pass
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  288. Send it Up!
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  295. Verify How?
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

  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

    View Slide

  306. What About Bundling?
    >
    Thursday, January 17, 13

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  316. Make it So
    >
    Thursday, January 17, 13

    View Slide

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

    View Slide

  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

    View Slide

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

    View Slide

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

    View Slide

  321. Ship It!
    Thursday, January 17, 13

    View Slide

  322. Other Commands
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  327. Forthcoming
    Thursday, January 17, 13

    View Slide

  328. Forthcoming
    Port to SQLite
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  333. Forthcoming
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  339. Fork It
    Thursday, January 17, 13

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  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

    View Slide

  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

    View Slide

  346. Thank you.
    Thursday, January 17, 13

    View Slide

  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

    View Slide