Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Agile Database Development 2ed

Agile Database Development 2ed

Hi, I'm David. I like to write database apps. Just as much as I like to write web apps. (Maybe more!) How? Not by relying on bolted-on, half-baked database integration tools like migrations, I'll tell you that!. Instead, I make extensive use of best-of-breed tools for source control ([Git](http://git-scm.org/)), database unit testing ([pgTAP](http://pgtap.org/)), and database change management and deployment ([Sqitch](http://sqitch.org/). If you'd like to get as much pleasure out of database development as you do application development, join me for this tutorial. We'll develop a sample application using the processes and tools I've come to depend on, and you'll find out whether they might work for you. Either way, I promise it will at least be an amusing use of your time.

[Originally presented at PGCon 2013](http://www.pgcon.org/2013/schedule/events/615.en.html), revised January 2014.

David E. Wheeler

January 09, 2014
Tweet

More Decks by David E. Wheeler

Other Decks in Technology

Transcript

  1. Instructions
    https:/
    /github.com/theory/agile-flipr

    View Slide

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

    View Slide

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

    iovation

    View Slide

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

    View Slide

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

    View Slide

  6. Build My VC-Funded App
    for Me for Free
    David E. Wheeler
    !
    !
    !
    January 2014
    Portland
    License: Attribution-Noncommercial-Share Alike 3.0 United
    States: http:/
    /creativecommons.org/licenses/by-nc-sa/3.0/us/

    CEO, Data Architect
    Agile Assholes, Inc.

    View Slide

  7. This is Genius

    View Slide

  8. This is Genius
    I had this idea

    View Slide

  9. This is Genius
    I had this idea
    Social networking is HAWT

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  16. How it Works
    antisocial network

    View Slide

  17. How it Works
    Microblogging platform
    antisocial network

    View Slide

  18. How it Works
    Microblogging platform
    Everyone follows you
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  23. Your Task
    antisocial network

    View Slide

  24. Your Task
    Create the database
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  28. Your Task
    Create the database
    Use agile development to make it right
    Contribute to VC-funded Corp
    Profit!
    For VC-funded Corp
    antisocial network

    View Slide

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

    View Slide

  30. But first…
    antisocial network

    View Slide

  31. NDA
    antisocial network

    View Slide

  32. Job Requirements
    antisocial network

    View Slide

  33. Job Requirements
    PostgreSQL
    Should be installed
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  38. We good?
    antisocial network

    View Slide

  39. Let’s do this thang.
    antisocial network

    View Slide

  40. >
    Who Am I?

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  47. ~/.gitconfig
    ~/
    .gitconfig
    [user]  
        name  =  David  E.  Wheeler  
          email  =  [email protected]

    View Slide

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

    View Slide

  49. Create a Remote

    View Slide

  50. Create a Remote
    Create Git project in Stash

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  54. Gitiup
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  59. mkdir flipr-­‐db
    > cd flipr-­‐db
    > git init
    Initialized empty Git repository in flipr-­‐db/.git/
    >
    Gitiup
    >
    echo Flipr Database Project > README.md
    > git add .
    > git commit -­‐m 'Initialize repo, add README.'
    [master (root-­‐commit) 9531fab] Initialize repo, add README.
    1 file changed, 1 insertion(+)
    create mode 100644 README.md
    >

    View Slide

  60. mkdir flipr-­‐db
    > cd flipr-­‐db
    > git init
    Initialized empty Git repository in flipr-­‐db/.git/
    >
    Gitiup
    >
    echo Flipr Database Project > README.md
    > git add .
    > git commit -­‐m 'Initialize repo, add README.'
    [master (root-­‐commit) 9531fab] Initialize repo, add README.
    1 file changed, 1 insertion(+)
    create mode 100644 README.md
    >

    View Slide

  61. mkdir flipr-­‐db
    > cd flipr-­‐db
    > git init
    Initialized empty Git repository in flipr-­‐db/.git/
    >
    Gitiup
    >
    echo Flipr Database Project > README.md
    > git add .
    > git commit -­‐m 'Initialize repo, add README.'
    [master (root-­‐commit) 9531fab] Initialize repo, add README.
    1 file changed, 1 insertion(+)
    create mode 100644 README.md
    >
    We have a
    Git repo.

    View Slide

  62. >
    Origin

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  68. Swimming Upstream
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  72. Git?
    antisocial network

    View Slide

  73. Git?
    Manage tree of files over time
    antisocial network

    View Slide

  74. Git?
    Manage tree of files over time
    Distributed development
    antisocial network

    View Slide

  75. Git?
    Manage tree of files over time
    Distributed development
    Commit changes locally
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  79. Why Git?
    antisocial network

    View Slide

  80. Why Git?
    Anyone can clone
    antisocial network

    View Slide

  81. Why Git?
    Anyone can clone
    Complete repository copy
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  86. PiSHA1
    antisocial network

    View Slide

  87. PiSHA1
    SHA1 ID for every object
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  95. >
    Making a hash of it

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  102. SHAzam!
    antisocial network

    View Slide

  103. SHAzam!
    Each commit (except first) includes parent
    antisocial network

    View Slide

  104. SHAzam!
    Each commit (except first) includes parent
    Can trace from any commit to the beginning
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  108. antisocial network
    Your Turn

    View Slide

  109. antisocial network
    Your Turn
    Configure Git

    View Slide

  110. antisocial network
    Your Turn
    Configure Git
    Initialize repository

    View Slide

  111. antisocial network
    Your Turn
    Configure Git
    Initialize repository
    Add origin remote

    View Slide

  112. antisocial network
    Your Turn
    Configure Git
    Initialize repository
    Add origin remote
    Add upstream remote

    View Slide

  113. antisocial network
    Your Turn
    Configure Git
    Initialize repository
    Add origin remote
    Add upstream remote
    https:/
    /github.com/
    theory/agile-flipr.git

    View Slide

  114. Who am I again?
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  121. >
    Scratch that Sqitch

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  133. What’s the Plan Man?
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  137. Make It So
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  141. Make It So
    >
       git  add  .
    >  git  commit  -­‐m  'Initialize  Sqitch  configuration.'
    [master  e56e7c8]  Initialize  Sqitch  configuration.
     2  files  changed,  16  insertions(+)
     create  mode  100644  sqitch.conf
     create  mode  100644  sqitch.plan
    >
       git  push
    Counting  objects:  5,  done.
    Delta  compression  using  up  to  4  threads.
    Compressing  objects:  100%  (4/4),  done.
    Writing  objects:  100%  (4/4),  555  bytes,  done.
    Total  4  (delta  0),  reused  0  (delta  0)
    To  https://github.com/theory/agile-­‐flipr.git
         9531fab..e56e7c8    master  -­‐>  master
    >

    View Slide

  142. Make It So
    >
       git  add  .
    >  git  commit  -­‐m  'Initialize  Sqitch  configuration.'
    [master  e56e7c8]  Initialize  Sqitch  configuration.
     2  files  changed,  16  insertions(+)
     create  mode  100644  sqitch.conf
     create  mode  100644  sqitch.plan
    >
       git  push
    Counting  objects:  5,  done.
    Delta  compression  using  up  to  4  threads.
    Compressing  objects:  100%  (4/4),  done.
    Writing  objects:  100%  (4/4),  555  bytes,  done.
    Total  4  (delta  0),  reused  0  (delta  0)
    To  https://github.com/theory/agile-­‐flipr.git
         9531fab..e56e7c8    master  -­‐>  master
    >

    View Slide

  143. Make It So
    >
       git  add  .
    >  git  commit  -­‐m  'Initialize  Sqitch  configuration.'
    [master  e56e7c8]  Initialize  Sqitch  configuration.
     2  files  changed,  16  insertions(+)
     create  mode  100644  sqitch.conf
     create  mode  100644  sqitch.plan
    >
       git  push
    Counting  objects:  5,  done.
    Delta  compression  using  up  to  4  threads.
    Compressing  objects:  100%  (4/4),  done.
    Writing  objects:  100%  (4/4),  555  bytes,  done.
    Total  4  (delta  0),  reused  0  (delta  0)
    To  https://github.com/theory/agile-­‐flipr.git
         9531fab..e56e7c8    master  -­‐>  master
    >

    View Slide

  144. Where’ve We Been?
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  148. First Change
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  159. Make it So!
    >

    View Slide

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

    View Slide

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

    View Slide

  162.    createdb  flipr_test
    >
       createuser  -­‐s  -­‐U  postgres  `whoami`
    >
       sqitch  deploy  db:pg:flipr_test
    Adding  registry  tables  to  db:pg:flipr_test
    Deploying  to  db:pg:flipr_test
       +  appschema  ..  ok
    >
    Make it So!
    >

    View Slide

  163.    createdb  flipr_test
    >
       createuser  -­‐s  -­‐U  postgres  `whoami`
    >
       sqitch  deploy  db:pg:flipr_test
    Adding  registry  tables  to  db:pg:flipr_test
    Deploying  to  db:pg:flipr_test
       +  appschema  ..  ok
    >
    Make it So!
    >

    View Slide

  164.    createdb  flipr_test
    >
       createuser  -­‐s  -­‐U  postgres  `whoami`
    >
       sqitch  deploy  db:pg:flipr_test
    Adding  registry  tables  to  db:pg:flipr_test
    Deploying  to  db:pg:flipr_test
       +  appschema  ..  ok
    >
    Make it So!
    >

    View Slide

  165.    createdb  flipr_test
    >
       createuser  -­‐s  -­‐U  postgres  `whoami`
    >
       sqitch  deploy  db:pg:flipr_test
    Adding  registry  tables  to  db:pg:flipr_test
    Deploying  to  db:pg:flipr_test
       +  appschema  ..  ok
    >
    Make it So!
    >

    View Slide

  166.    createdb  flipr_test
    >
       createuser  -­‐s  -­‐U  postgres  `whoami`
    >
       sqitch  deploy  db:pg:flipr_test
    Adding  registry  tables  to  db:pg:flipr_test
    Deploying  to  db:pg:flipr_test
       +  appschema  ..  ok
    >
    Make it So!
       psql  -­‐d  flipr_test  -­‐c  '\dn  flipr'
    List  of  schemas
     Name    |  Owner  
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐
     flipr  |  david
    >
    >

    View Slide

  167.    createdb  flipr_test
    >
       createuser  -­‐s  -­‐U  postgres  `whoami`
    >
       sqitch  deploy  db:pg:flipr_test
    Adding  registry  tables  to  db:pg:flipr_test
    Deploying  to  db:pg:flipr_test
       +  appschema  ..  ok
    >
    Make it So!
       psql  -­‐d  flipr_test  -­‐c  '\dn  flipr'
    List  of  schemas
     Name    |  Owner  
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐
     flipr  |  david
    >
    Trust, but
    verify
    >

    View Slide

  168.    createdb  flipr_test
    >
       createuser  -­‐s  -­‐U  postgres  `whoami`
    >
       sqitch  deploy  db:pg:flipr_test
    Adding  registry  tables  to  db:pg:flipr_test
    Deploying  to  db:pg:flipr_test
       +  appschema  ..  ok
    >
    Make it So!
       psql  -­‐d  flipr_test  -­‐c  '\dn  flipr'
    List  of  schemas
     Name    |  Owner  
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐
     flipr  |  david
    >
       emacs  verify/appschema.sql
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  177. Trust, But Verify
    >

    View Slide

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

    View Slide

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

    View Slide

  180. >
    How’s it Look?

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  187. Go Back
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  193. History
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  197. Commit It!
    >

    View Slide

  198. Commit It!
    >
       git  add  .
    >  git  commit  -­‐m  'Add  flipr  schema.'
    [master  07548c2]  Add  flipr  schema.
     4  files  changed,  22  insertions(+)
     create  mode  100644  deploy/appschema.sql
     create  mode  100644  revert/appschema.sql
     create  mode  100644  verify/appschema.sql
    >

    View Slide

  199. Commit It!
    >
       git  add  .
    >  git  commit  -­‐m  'Add  flipr  schema.'
    [master  07548c2]  Add  flipr  schema.
     4  files  changed,  22  insertions(+)
     create  mode  100644  deploy/appschema.sql
     create  mode  100644  revert/appschema.sql
     create  mode  100644  verify/appschema.sql
    >

    View Slide

  200. Commit It!
    >
       git  add  .
    >  git  commit  -­‐m  'Add  flipr  schema.'
    [master  07548c2]  Add  flipr  schema.
     4  files  changed,  22  insertions(+)
     create  mode  100644  deploy/appschema.sql
     create  mode  100644  revert/appschema.sql
     create  mode  100644  verify/appschema.sql
    >

    View Slide

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

    View Slide

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

    View Slide

  203. Redeploy
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  208. Status Update
    >

    View Slide

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

    View Slide

  210. On Target
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  215. Commit Config
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  219. Not Migrations?
    antisocial network

    View Slide

  220. Not Migrations?
    Incomplete mini-language
    antisocial network

    View Slide

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

    View Slide

  222. Not Migrations?
    Incomplete mini-language
    No logical replication integration
    Numbered scripts difficult to track
    antisocial network

    View Slide

  223. Not Migrations?
    Incomplete mini-language
    No logical replication integration
    Numbered scripts difficult to track
    No VCS awareness
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  228. Sq—what?
    sql anges
    ch
    antisocial network

    View Slide

  229. Sq—what?
    sq ch
    antisocial network

    View Slide

  230. it
    Sq—what?
    sq ch
    antisocial network

    View Slide

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

    View Slide

  232. Sqitch Philosophy
    antisocial network

    View Slide

  233. Sqitch Philosophy
    No opinions
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  240. SHAzbat
    antisocial network

    View Slide

  241. SHAzbat
    SHA1 ID for every object
    antisocial network

    View Slide

  242. SHAzbat
    SHA1 ID for every object
    Stolen from Git
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  249. SHAsome
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  261. SHApay!
    antisocial network

    View Slide

  262. SHApay!
    Each change (except first) includes parent
    antisocial network

    View Slide

  263. SHApay!
    Each change (except first) includes parent
    Can trace from any change to the beginning
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  267. Sqitch Features
    antisocial network

    View Slide

  268. Sqitch Features
    Reduced duplication
    antisocial network

    View Slide

  269. Sqitch Features
    Reduced duplication
    Built-in configuration
    antisocial network

    View Slide

  270. Sqitch Features
    Reduced duplication
    Built-in configuration
    Iterative development
    antisocial network

    View Slide

  271. Sqitch Features
    Reduced duplication
    Built-in configuration
    Iterative development
    Targeted deployment
    antisocial network

    View Slide

  272. Sqitch Features
    Reduced duplication
    Built-in configuration
    Iterative development
    Targeted deployment
    Git-style interface
    antisocial network

    View Slide

  273. Sqitch Features
    Reduced duplication
    Built-in configuration
    Iterative development
    Targeted deployment
    Git-style interface
    Deployment tagging
    antisocial network

    View Slide

  274. antisocial network
    Your Turn

    View Slide

  275. antisocial network
    Your Turn
    Configure Sqitch

    View Slide

  276. antisocial network
    Your Turn
    Configure Sqitch
    Initialize project

    View Slide

  277. antisocial network
    Your Turn
    Configure Sqitch
    Initialize project
    Add appschema
    change

    View Slide

  278. antisocial network
    Your Turn
    Configure Sqitch
    Initialize project
    Add appschema
    change
    Deploy/Revert

    View Slide

  279. antisocial network
    Your Turn
    Configure Sqitch
    Initialize project
    Add appschema
    change
    Deploy/Revert
    Commit/push

    View Slide

  280. antisocial network
    Your Turn
    Configure Sqitch
    Initialize project
    Add appschema
    change
    Deploy/Revert
    Commit/push
    https:/
    /github.com/
    theory/agile-flipr.git

    View Slide

  281. Add Tests
    >

    View Slide

  282. Add Tests
    >
       mkdir  test
    >

    View Slide

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

    View Slide

  284. test/appschema.
    pgTAP Basics

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  292. >
    Run the Test

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  296. First Pass
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  300. Pass it On
    >

    View Slide

  301. git add .
    >
    Pass it On
    >

    View Slide

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

    View Slide

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

    View Slide

  304. View Slide

  305. OMG TAP
    WTF?

    View Slide

  306. What is TAP?
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  314. What’s the plan, man?
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  320. Scalarly
    antisocial network

    View Slide

  321. Scalarly
    Includes simple scalar test functions:
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  325. Scalarly
    Includes simple scalar test functions:
    ok() — Boolean
    is() — Value comparison
    isnt() — NOT is()
    cmp_ok() — Compare with specific operator
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

  328. It’s All Relative
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  335. I’m Okay, You’re Okay
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  340. Schematics
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  345. Other Features and Topics
    antisocial network

    View Slide

  346. Other Features and Topics
    xUnit-Style testing
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  354. antisocial network
    Let’s do it!

    View Slide

  355. antisocial network
    Let’s do it!
    Create appschema
    test

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  363. antisocial network
    antisocial network

    View Slide

  364. TDD
    antisocial network
    antisocial network

    View Slide

  365. antisocial network

    View Slide

  366. TDD is an act
    of design.
    antisocial network

    View Slide

  367. antisocial network

    View Slide

  368. TDD is an act of
    documentation.
    antisocial network

    View Slide

  369. Database Design
    antisocial network

    View Slide

  370. Database Design
    Specify requirements
    antisocial network

    View Slide

  371. Database Design
    Specify requirements
    Implement schema design
    antisocial network

    View Slide

  372. Database Design
    Specify requirements
    Implement schema design
    Program applications
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

  375. Database Design
    Specify requirements
    Implement schema design
    Program applications
    QA
    Pricy to fix
    antisocial network

    View Slide

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

    View Slide

  377. Why TDDD
    antisocial network

    View Slide

  378. Why TDDD
    Ensure data quality
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  384. Why TDDD
    antisocial network

    View Slide

  385. Why TDDD
    Identify defects early
    antisocial network

    View Slide

  386. Why TDDD
    Identify defects early
    …and often!
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  391. Three Questions
    for Database Professionals
    from
    Scott Ambler
    antisocial network

    View Slide

  392. View Slide

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

    View Slide

  394. View Slide

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

    View Slide

  396. View Slide

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

    View Slide

  398. antisocial network

    View Slide

  399. Okay.
    antisocial network

    View Slide

  400. antisocial network

    View Slide

  401. How?
    antisocial network

    View Slide

  402. TDD How
    antisocial network

    View Slide

  403. TDD How
    Ideally separate from app tests
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  409. Test Template

    View Slide

  410. Test Template
    Sqitch scripts generated from templates

    View Slide

  411. Test Template
    Sqitch scripts generated from templates
    Can add new templates for new files

    View Slide

  412. Test Template
    Sqitch scripts generated from templates
    Can add new templates for new files
    Always want test for change?

    View Slide

  413. Test Template
    Sqitch scripts generated from templates
    Can add new templates for new files
    Always want test for change?
    Add test template!

    View Slide

  414. Templates Where?
    >

    View Slide

  415. Templates Where?
       mkdir  -­‐p  ~/.sqitch/templates/test
    >
    >
    Or system-wide:

    `sqitch --etc-path`/templates

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  419. pg.tmpl
    Template Your Tests
    SET  client_min_messages  TO  warning;  
    CREATE  EXTENSION  IF  NOT  EXISTS  pgtap;  
    RESET  client_min_messages;  
    !
    BEGIN;  
    !
    !
    !
    SELECT  
    !
    SELECT  finish();  
    ROLLBACK;
    has_schema('flipr');
    Adjust
    test
    counts.
    SELECT  no_plan();
    -­‐-­‐  SELECT  plan(1);

    View Slide

  420. pg.tmpl
    Template Your Tests
    SET  client_min_messages  TO  warning;  
    CREATE  EXTENSION  IF  NOT  EXISTS  pgtap;  
    RESET  client_min_messages;  
    !
    BEGIN;  
    !
    !
    !
    SELECT  
    !
    SELECT  finish();  
    ROLLBACK;
    pass('Test  [%  change  %]!');
    Put tests
    here.
    SELECT  no_plan();
    -­‐-­‐  SELECT  plan(1);

    View Slide

  421. Branching Out
    >

    View Slide

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

    View Slide

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

    View Slide

  424.    git  checkout  -­‐b  users  master
    Switched  to  a  new  branch  'users'
    >
    Branching Out
    >
    >  sqitch  add  users  -­‐-­‐requires  appschema  \
       -­‐n  'Creates  table  to  track  our  users.'
    Created  deploy/users.sql
    Created  revert/users.sql
    Created  test/users.sql
    Created  verify/users.sql
    Added  "users  [appschema]"  to  sqitch.plan
    >

    View Slide

  425.    git  checkout  -­‐b  users  master
    Switched  to  a  new  branch  'users'
    >
    Branching Out
    >
    >  sqitch  add  users  -­‐-­‐requires  appschema  \
       -­‐n  'Creates  table  to  track  our  users.'
    Created  deploy/users.sql
    Created  revert/users.sql
    Created  test/users.sql
    Created  verify/users.sql
    Added  "users  [appschema]"  to  sqitch.plan
    >
    Cool!

    View Slide

  426.    git  checkout  -­‐b  users  master
    Switched  to  a  new  branch  'users'
    >
    Branching Out
    >
    >  sqitch  add  users  -­‐-­‐requires  appschema  \
       -­‐n  'Creates  table  to  track  our  users.'
    Created  deploy/users.sql
    Created  revert/users.sql
    Created  test/users.sql
    Created  verify/users.sql
    Added  "users  [appschema]"  to  sqitch.plan
    >

    View Slide

  427.    git  checkout  -­‐b  users  master
    Switched  to  a  new  branch  'users'
    >
    Branching Out
    >
    >  emacs  test/users.sql
    >
    >  sqitch  add  users  -­‐-­‐requires  appschema  \
       -­‐n  'Creates  table  to  track  our  users.'
    Created  deploy/users.sql
    Created  revert/users.sql
    Created  test/users.sql
    Created  verify/users.sql
    Added  "users  [appschema]"  to  sqitch.plan
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  433. Run ’Em
    >

    View Slide

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

    View Slide

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

    View Slide

  436. pg_prove -­‐d flipr_test -­‐v test/users.sql
    test/users.sql ..
    not ok 1 -­‐ Table users should exist
    # Failed test 1: "Table users should exist"
    1..1
    # Looks like you failed 1 test of 1
    Failed 1/1 subtests
    Test Summary Report
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
    test/users.sql (Wstat: 0 Tests: 1 Failed: 1)
    Failed test: 1
    Files=1, Tests=1, 0 wallclock secs
    Result: FAIL
    >
    Run ’Em
    As expected.
    >

    View Slide

  437. pg_prove -­‐d flipr_test -­‐v test/users.sql
    test/users.sql ..
    not ok 1 -­‐ Table users should exist
    # Failed test 1: "Table users should exist"
    1..1
    # Looks like you failed 1 test of 1
    Failed 1/1 subtests
    Test Summary Report
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
    test/users.sql (Wstat: 0 Tests: 1 Failed: 1)
    Failed test: 1
    Files=1, Tests=1, 0 wallclock secs
    Result: FAIL
    >
    Run ’Em
    >
       emacs  deploy/users.sql

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    Bare
    Minimum

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  450. Make Users
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  454.    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  |  
    >
       sqitch  verify
    Verifying  flipr_test
       *  appschema  ..  ok
       *  users  ......  ok
    Verify  successful
    >

    View Slide

  455. Make Users
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  462. Dead Again
    >

    View Slide

  463.    pg_prove  -­‐d  flipr_test  -­‐v  test/users.sql
    test/users.sql  ..  
    ok  1  -­‐  Table  users  should  exist
    ok  2  -­‐  Column  users.nickname  should  exist
    not  ok  3  -­‐  Column  users.password  should  exist
    #  Failed  test  3:  "Column  users.password  should  exist"
    not  ok  4  -­‐  Column  users."timestamp"  should  exist
    #  Failed  test  4:  "Column  users."timestamp"  should  exist"
    1..4
    #  Looks  like  you  failed  2  tests  of  4
    Failed  2/4  subtests  
    Test  Summary  Report
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
    test/users.sql  (Wstat:  0  Tests:  4  Failed:  2)
       Failed  tests:    3-­‐4
    Files=1,  Tests=4,    0  wallclock  secs
    Result:  FAIL
    Dead Again
    >

    View Slide

  464.    pg_prove  -­‐d  flipr_test  -­‐v  test/users.sql
    test/users.sql  ..  
    ok  1  -­‐  Table  users  should  exist
    ok  2  -­‐  Column  users.nickname  should  exist
    not  ok  3  -­‐  Column  users.password  should  exist
    #  Failed  test  3:  "Column  users.password  should  exist"
    not  ok  4  -­‐  Column  users."timestamp"  should  exist
    #  Failed  test  4:  "Column  users."timestamp"  should  exist"
    1..4
    #  Looks  like  you  failed  2  tests  of  4
    Failed  2/4  subtests  
    Test  Summary  Report
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
    test/users.sql  (Wstat:  0  Tests:  4  Failed:  2)
       Failed  tests:    3-­‐4
    Files=1,  Tests=4,    0  wallclock  secs
    Result:  FAIL
    Dead Again
    >
    Guess we should
    add them.

    View Slide

  465. MOAR Deploy
    >

    View Slide

  466. >  emacs  deploy/users.sql
    >
    MOAR Deploy
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  479. Sqitch Tags
    antisocial network

    View Slide

  480. Sqitch Tags
    Start with @
    antisocial network

    View Slide

  481. Sqitch Tags
    Start with @
    To distinguish from changes
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  488. Specifying Changes
    antisocial network

    View Slide

  489. Specifying Changes
    users Change named “users”
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  499. Whither Users
    >

    View Slide

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

    View Slide

  501. Back At It
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  512. Columny
    >

    View Slide

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

    View Slide

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

    View Slide

  515. User Typography
    >

    View Slide

  516.    emacs  deploy/users.sql
    >
    User Typography
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  521. User Typography
    >  emacs  deploy/users.sql  
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  527. Additives
    >

    View Slide

  528. Additives
    >
       git  add  .
    >

    View Slide

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

    View Slide

  530. Pushers
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  534. Wash, Rinse, Repeat
    antisocial network

    View Slide

  535. Wash, Rinse, Repeat
    Add failing simple test
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  542. Time to Work!
    antisocial network

    View Slide

  543. Time to Work!
    Prepare to hack!
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  550. Caution: Hard Reset
    Ahead

    View Slide

  551. Caution: Hard Reset
    Ahead
    A rare destructive Git command

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  557. antisocial network
    Flip Out

    View Slide

  558. antisocial network
    Flip Out
    Create flips branch

    View Slide

  559. antisocial network
    Flip Out
    Create flips branch
    Create flips table

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  566. Functional Testing
    >

    View Slide

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

    View Slide

  568. Functional Testing
       git  checkout  -­‐b  userfuncs  users
    Switched  to  a  new  branch  'userfuncs'
    >
    >
       sqitch  add  insert_user  -­‐r  users  -­‐r  appschema  \
               -­‐n  'Creates  a  function  to  insert  a  user.'
    Created  deploy/insert_user.sql
    Created  revert/insert_user.sql
    Created  test/insert_user.sql
    Created  verify/insert_user.sql
    Added  "insert_user  [users  appschema]"  to  sqitch.plan
    >

    View Slide

  569. Functional Testing
       git  checkout  -­‐b  userfuncs  users
    Switched  to  a  new  branch  'userfuncs'
    >
    >
       sqitch  add  insert_user  -­‐r  users  -­‐r  appschema  \
               -­‐n  'Creates  a  function  to  insert  a  user.'
    Created  deploy/insert_user.sql
    Created  revert/insert_user.sql
    Created  test/insert_user.sql
    Created  verify/insert_user.sql
    Added  "insert_user  [users  appschema]"  to  sqitch.plan
    >
       emacs  test/insert_user.sql
    >

    View Slide

  570. test/insert_use
    test/insert_user.sql

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  574. test/insert_use
    SELECT  plan(11);

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  593. SELECT  throws_ok(
           $$  SELECT  insert_user('theory',  'ha-­‐ha')  $$,
           23505,  -­‐-­‐  duplicate  key  violation
           NULL,    -­‐-­‐  localized  error  message
           'Should  get  an  error  for  duplicate  nickname'
    );
    SELECT  bag_eq(
           'SELECT  *  FROM  users',
           $$  VALUES
                   ('theory',        md5('foo'),    NOW()),
                   ('strongrrl',  md5('w00t'),  NOW())
           $$,
           'Should  still  have  just  the  two  users'
    );
    test/insert_use
           'Both  users  should  be  present'  
    );

    View Slide

  594. SELECT  throws_ok(
           $$  SELECT  insert_user('theory',  'ha-­‐ha')  $$,
           23505,  -­‐-­‐  duplicate  key  violation
           NULL,    -­‐-­‐  localized  error  message
           'Should  get  an  error  for  duplicate  nickname'
    );
    SELECT  bag_eq(
           'SELECT  *  FROM  users',
           $$  VALUES
                   ('theory',        md5('foo'),    NOW()),
                   ('strongrrl',  md5('w00t'),  NOW())
           $$,
           'Should  still  have  just  the  two  users'
    );
    test/insert_use
           'Both  users  should  be  present'  
    );

    View Slide

  595. SELECT  throws_ok(
           $$  SELECT  insert_user('theory',  'ha-­‐ha')  $$,
           23505,  -­‐-­‐  duplicate  key  violation
           NULL,    -­‐-­‐  localized  error  message
           'Should  get  an  error  for  duplicate  nickname'
    );
    SELECT  bag_eq(
           'SELECT  *  FROM  users',
           $$  VALUES
                   ('theory',        md5('foo'),    NOW()),
                   ('strongrrl',  md5('w00t'),  NOW())
           $$,
           'Should  still  have  just  the  two  users'
    );
    test/insert_use
           'Both  users  should  be  present'  
    );
    Appendix A

    View Slide

  596. SELECT  throws_ok(
           $$  SELECT  insert_user('theory',  'ha-­‐ha')  $$,
           23505,  -­‐-­‐  duplicate  key  violation
           NULL,    -­‐-­‐  localized  error  message
           'Should  get  an  error  for  duplicate  nickname'
    );
    SELECT  bag_eq(
           'SELECT  *  FROM  users',
           $$  VALUES
                   ('theory',        md5('foo'),    NOW()),
                   ('strongrrl',  md5('w00t'),  NOW())
           $$,
           'Should  still  have  just  the  two  users'
    );
    test/insert_use
           'Both  users  should  be  present'  
    );

    View Slide

  597. SELECT  throws_ok(
           $$  SELECT  insert_user('theory',  'ha-­‐ha')  $$,
           23505,  -­‐-­‐  duplicate  key  violation
           NULL,    -­‐-­‐  localized  error  message
           'Should  get  an  error  for  duplicate  nickname'
    );
    SELECT  bag_eq(
           'SELECT  *  FROM  users',
           $$  VALUES
                   ('theory',        md5('foo'),    NOW()),
                   ('strongrrl',  md5('w00t'),  NOW())
           $$,
           'Should  still  have  just  the  two  users'
    );
    test/insert_use
           'Both  users  should  be  present'  
    );

    View Slide

  598. SELECT  throws_ok(
           $$  SELECT  insert_user('theory',  'ha-­‐ha')  $$,
           23505,  -­‐-­‐  duplicate  key  violation
           NULL,    -­‐-­‐  localized  error  message
           'Should  get  an  error  for  duplicate  nickname'
    );
    SELECT  bag_eq(
           'SELECT  *  FROM  users',
           $$  VALUES
                   ('theory',        md5('foo'),    NOW()),
                   ('strongrrl',  md5('w00t'),  NOW())
           $$,
           'Should  still  have  just  the  two  users'
    );
    test/insert_use
           'Both  users  should  be  present'  
    );

    View Slide

  599. SELECT  throws_ok(
           $$  SELECT  insert_user('theory',  'ha-­‐ha')  $$,
           23505,  -­‐-­‐  duplicate  key  violation
           NULL,    -­‐-­‐  localized  error  message
           'Should  get  an  error  for  duplicate  nickname'
    );
    SELECT  bag_eq(
           'SELECT  *  FROM  users',
           $$  VALUES
                   ('theory',        md5('foo'),    NOW()),
                   ('strongrrl',  md5('w00t'),  NOW())
           $$,
           'Should  still  have  just  the  two  users'
    );
    test/insert_use
           'Both  users  should  be  present'  
    );
    SELECT  finish();
    ROLLBACK;

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  613. We Good?
       emacs  verify/insert_user.sql  
    >
    >

    View Slide

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

    View Slide

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

    View Slide

  616. Commitment
    >

    View Slide

  617.    git  add  .
    >  git  commit  -­‐m  'Add  `insert_user()`.'
    [userfuncs  e1c7769]  Add  `insert_user()`.
     5  files  changed,  101  insertions(+)
     create  mode  100644  deploy/insert_user.sql
     create  mode  100644  revert/insert_user.sql
     create  mode  100644  test/insert_user.sql
     create  mode  100644  verify/insert_user.sql
    >
    Commitment
    >

    View Slide

  618. Push It Real Good…
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  622. Reset, Mes Amis!
    antisocial network

    View Slide

  623. Reset, Mes Amis!
    git checkout users
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  627. antisocial network
    None Shall Pass

    View Slide

  628. antisocial network
    None Shall Pass
    Create change_pass()

    View Slide

  629. antisocial network
    None Shall Pass
    Create change_pass()
    Params:

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  636. Resets
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  646. Mergers and Acquisitions
    >

    View Slide

  647.    git  merge  -­‐-­‐no-­‐ff  users  -­‐m  'Merge  branch  "users".'
    Merge  made  by  the  'recursive'  strategy.
     deploy/users.sql  |  13  +++++++++++++
     revert/users.sql  |    7  +++++++
     sqitch.plan            |    1  +
     test/users.sql      |  30  ++++++++++++++++++++++++++++++
     verify/users.sql  |    9  +++++++++
     5  files  changed,  60  insertions(+)
     create  mode  100644  deploy/users.sql
     create  mode  100644  revert/users.sql
     create  mode  100644  test/users.sql
     create  mode  100644  verify/users.sql
    >
    Mergers and Acquisitions
    >

    View Slide

  648.    git  merge  -­‐-­‐no-­‐ff  users  -­‐m  'Merge  branch  "users".'
    Merge  made  by  the  'recursive'  strategy.
     deploy/users.sql  |  13  +++++++++++++
     revert/users.sql  |    7  +++++++
     sqitch.plan            |    1  +
     test/users.sql      |  30  ++++++++++++++++++++++++++++++
     verify/users.sql  |    9  +++++++++
     5  files  changed,  60  insertions(+)
     create  mode  100644  deploy/users.sql
     create  mode  100644  revert/users.sql
     create  mode  100644  test/users.sql
     create  mode  100644  verify/users.sql
    >
    Mergers and Acquisitions
    >
    So far…

    View Slide

  649. Mergers and Acquisitions
    >

    View Slide

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

    View Slide

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

    View Slide

  652. Mergers and Acquisitions
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  657. Back in Time…
    >

    View Slide

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

    View Slide

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

    View Slide

  660. Branching Out
    antisocial network

    View Slide

  661. Branching Out
    users branched from master
    antisocial network

    View Slide

  662. Branching Out
    users branched from master
    flips branched from users
    antisocial network

    View Slide

  663. Branching Out
    users branched from master
    flips branched from users
    userfuncs branched from users
    antisocial network

    View Slide

  664. Branching Out
    users branched from master
    flips branched from users
    userfuncs branched from users
    users and flips merged to master
    antisocial network

    View Slide

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

    View Slide

  666. Backbrancher
    master
    A

    View Slide

  667. Backbrancher
    master
    users
    A
    branch

    View Slide

  668. Backbrancher
    master
    users
    A
    B
    Add
    users
    table
    branch

    View Slide

  669. Backbrancher
    master
    users
    A
    B
    flips
    branch
    branch

    View Slide

  670. Backbrancher
    master
    users
    A
    B
    flips
    C
    Add
    flips
    table
    branch
    branch

    View Slide

  671. Backbrancher
    master
    users
    A
    B
    flips
    C
    userfuncs
    branch
    branch
    branch

    View Slide

  672. Backbrancher
    master
    users
    A
    B
    flips
    C
    userfuncs
    Add
    insert_user
    function
    D
    branch
    branch
    branch

    View Slide

  673. Backbrancher
    master
    users
    A
    B
    flips
    C
    userfuncs
    D
    branch
    branch
    branch E
    Add
    change_pass
    function

    View Slide

  674. Backbrancher
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    D
    branch
    branch
    branch E

    View Slide

  675. Backbrancher
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    D
    branch
    branch
    branch E

    View Slide

  676. Backbrancher
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    D
    F
    merge D
    &
    E
    branch
    branch
    branch E

    View Slide

  677. Backbrancher
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    D No C!
    F
    merge D
    &
    E
    branch
    branch
    branch E

    View Slide

  678. Backbrancher
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    D
    conflict!
    branch
    branch
    branch E

    View Slide

  679. Backbrancher
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    D
    conflict!
    Now
    what?
    branch
    branch
    branch E

    View Slide

  680. Rebase master
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    branch
    branch
    branch D E

    View Slide

  681. Rebase master
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    branch
    branch
    branch
    Last common
    with master: B

    View Slide

  682. Rebase master
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    branch
    branch
    branch
    C

    View Slide

  683. Rebase master
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    branch
    branch
    branch
    C D
    Rebased
    E

    View Slide

  684. Rebase master
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    branch
    branch
    branch
    C D
    E
    merge D & E
    E

    View Slide

  685. \o/
    Rebase master
    master
    users
    A
    B
    flips
    C
    userfuncs
    B
    merge B
    C
    merge C
    branch
    branch
    branch
    C D
    E
    merge D & E
    E

    View Slide

  686. Reset
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  691. Rebase
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  698. Wha Happen?
    antisocial network

    View Slide

  699. Wha Happen?
    Same conflict
    antisocial network

    View Slide

  700. Wha Happen?
    Same conflict
    Both branches modified sqitch.plan
    antisocial network

    View Slide

  701. Wha Happen?
    Same conflict
    Both branches modified sqitch.plan
    The same line!
    antisocial network

    View Slide

  702. Wha Happen?
    >

    View Slide

  703. >  git  diff
    diff  -­‐-­‐cc  sqitch.plan
    index  7526d1c,e2c966c..0000000
    -­‐-­‐-­‐  a/sqitch.plan
    +++  b/sqitch.plan
    @@@  -­‐4,4  -­‐4,4  +4,8  @@@
       
       appschema  2014-­‐01-­‐07T23:47:31Z  David  E.  Wheeler      users  [appschema]  2014-­‐01-­‐08T04:13:38Z  David  E.  Wh
    ++<<<<<<<  HEAD
     +flips  [appschema  users]  2014-­‐01-­‐08T04:48:29Z  David
    ++=======
    +  insert_user  [users  appschema]  2014-­‐01-­‐08T04:52:50Z
    ++>>>>>>>  Add  `insert_user()`.
    Wha Happen?
    >

    View Slide

  704. >  git  diff
    diff  -­‐-­‐cc  sqitch.plan
    index  7526d1c,e2c966c..0000000
    -­‐-­‐-­‐  a/sqitch.plan
    +++  b/sqitch.plan
    @@@  -­‐4,4  -­‐4,4  +4,8  @@@
       
       appschema  2014-­‐01-­‐07T23:47:31Z  David  E.  Wheeler      users  [appschema]  2014-­‐01-­‐08T04:13:38Z  David  E.  Wh
    ++<<<<<<<  HEAD
     +flips  [appschema  users]  2014-­‐01-­‐08T04:48:29Z  David
    ++=======
    +  insert_user  [users  appschema]  2014-­‐01-­‐08T04:52:50Z
    ++>>>>>>>  Add  `insert_user()`.
    Wha Happen?
    >

    View Slide

  705. >  git  diff
    diff  -­‐-­‐cc  sqitch.plan
    index  7526d1c,e2c966c..0000000
    -­‐-­‐-­‐  a/sqitch.plan
    +++  b/sqitch.plan
    @@@  -­‐4,4  -­‐4,4  +4,8  @@@
       
       appschema  2014-­‐01-­‐07T23:47:31Z  David  E.  Wheeler      users  [appschema]  2014-­‐01-­‐08T04:13:38Z  David  E.  Wh
    ++<<<<<<<  HEAD
     +flips  [appschema  users]  2014-­‐01-­‐08T04:48:29Z  David
    ++=======
    +  insert_user  [users  appschema]  2014-­‐01-­‐08T04:52:50Z
    ++>>>>>>>  Add  `insert_user()`.
    Wha Happen?
    >
    B

    View Slide

  706. >  git  diff
    diff  -­‐-­‐cc  sqitch.plan
    index  7526d1c,e2c966c..0000000
    -­‐-­‐-­‐  a/sqitch.plan
    +++  b/sqitch.plan
    @@@  -­‐4,4  -­‐4,4  +4,8  @@@
       
       appschema  2014-­‐01-­‐07T23:47:31Z  David  E.  Wheeler      users  [appschema]  2014-­‐01-­‐08T04:13:38Z  David  E.  Wh
    ++<<<<<<<  HEAD
     +flips  [appschema  users]  2014-­‐01-­‐08T04:48:29Z  David
    ++=======
    +  insert_user  [users  appschema]  2014-­‐01-­‐08T04:52:50Z
    ++>>>>>>>  Add  `insert_user()`.
    Wha Happen?
    >
    C
    B

    View Slide

  707. >  git  diff
    diff  -­‐-­‐cc  sqitch.plan
    index  7526d1c,e2c966c..0000000
    -­‐-­‐-­‐  a/sqitch.plan
    +++  b/sqitch.plan
    @@@  -­‐4,4  -­‐4,4  +4,8  @@@
       
       appschema  2014-­‐01-­‐07T23:47:31Z  David  E.  Wheeler      users  [appschema]  2014-­‐01-­‐08T04:13:38Z  David  E.  Wh
    ++<<<<<<<  HEAD
     +flips  [appschema  users]  2014-­‐01-­‐08T04:48:29Z  David
    ++=======
    +  insert_user  [users  appschema]  2014-­‐01-­‐08T04:52:50Z
    ++>>>>>>>  Add  `insert_user()`.
    Wha Happen?
    >
    C
    D
    B

    View Slide

  708. Scratch that Sqitch
    antisocial network

    View Slide

  709. Scratch that Sqitch
    Screwed either way?
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  715. Re: Union Merge

    View Slide

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

    View Slide

  717. Hallelunion
    antisocial network

    View Slide

  718. Hallelunion
    Just appends lines
    antisocial network

    View Slide

  719. Hallelunion
    Just appends lines
    Exactly how changes work
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

  722. Reemerge
    >

    View Slide

  723.    git  rebase  -­‐-­‐abort
    >
    Reemerge
    >

    View Slide

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

    View Slide

  725.    echo  sqitch.plan  merge=union  >  .gitattributes
    >
       git  rebase  master
    First,  rewinding  head  to  replay  your  work  on  top  of  it...
    Applying:  Add  `insert_user()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    Applying:  Add  `change_pass()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    >
       git  rebase  -­‐-­‐abort
    >
    Reemerge
    >

    View Slide

  726.    echo  sqitch.plan  merge=union  >  .gitattributes
    >
       git  rebase  master
    First,  rewinding  head  to  replay  your  work  on  top  of  it...
    Applying:  Add  `insert_user()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    Applying:  Add  `change_pass()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    >
       git  rebase  -­‐-­‐abort
    >
    Reemerge
    >
    Back to B,
    applies C

    View Slide

  727.    echo  sqitch.plan  merge=union  >  .gitattributes
    >
       git  rebase  master
    First,  rewinding  head  to  replay  your  work  on  top  of  it...
    Applying:  Add  `insert_user()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    Applying:  Add  `change_pass()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    >
       git  rebase  -­‐-­‐abort
    >
    Reemerge
    >
    That’s D

    View Slide

  728.    echo  sqitch.plan  merge=union  >  .gitattributes
    >
       git  rebase  master
    First,  rewinding  head  to  replay  your  work  on  top  of  it...
    Applying:  Add  `insert_user()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    Applying:  Add  `change_pass()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    >
       git  rebase  -­‐-­‐abort
    >
    Reemerge
    >
    Union merge

    View Slide

  729.    echo  sqitch.plan  merge=union  >  .gitattributes
    >
       git  rebase  master
    First,  rewinding  head  to  replay  your  work  on  top  of  it...
    Applying:  Add  `insert_user()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    Applying:  Add  `change_pass()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    >
       git  rebase  -­‐-­‐abort
    >
    Reemerge
    >
    That’s E

    View Slide

  730.    echo  sqitch.plan  merge=union  >  .gitattributes
    >
       git  rebase  master
    First,  rewinding  head  to  replay  your  work  on  top  of  it...
    Applying:  Add  `insert_user()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    Applying:  Add  `change_pass()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    >
       git  rebase  -­‐-­‐abort
    >
    Reemerge
    >
    Union merge

    View Slide

  731.    echo  sqitch.plan  merge=union  >  .gitattributes
    >
       git  rebase  master
    First,  rewinding  head  to  replay  your  work  on  top  of  it...
    Applying:  Add  `insert_user()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    Applying:  Add  `change_pass()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    >
       git  rebase  -­‐-­‐abort
    >
    Reemerge
    >
    Success!

    View Slide

  732.    echo  sqitch.plan  merge=union  >  .gitattributes
    >
       git  rebase  master
    First,  rewinding  head  to  replay  your  work  on  top  of  it...
    Applying:  Add  `insert_user()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    Applying:  Add  `change_pass()`.
    Using  index  info  to  reconstruct  a  base  tree...
    M   sqitch.plan
    Falling  back  to  patching  base  and  3-­‐way  merge...
    Auto-­‐merging  sqitch.plan
    >
       git  rebase  -­‐-­‐abort
    >
    Reemerge
    >
       emacs  sqitch.plan
    >
    Success!

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  740. Work It
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  745. Make it So
    >

    View Slide

  746. >  git  add  .
    >
    Make it So
    >

    View Slide

  747. >  git  add  .
    >
       git  commit  -­‐m  'Use  union  merge  for  `sqitch.plan`.'
    [userfuncs  f38ef5c]  Use  union  merge  for  `sqitch.plan`.
     1  file  changed,  1  insertion(+)
     create  mode  100644  .gitattributes
    >
    Make it So
    >

    View Slide

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

    View Slide

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

    View Slide

  750. >

    View Slide

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

    View Slide

  752.    git  merge  -­‐-­‐no-­‐ff  userfuncs  -­‐m  'Merge  branch  "userfuncs".'              
    Merge  made  by  the  'recursive'  strategy.
     .gitattributes                  |    1  +
     deploy/change_pass.sql  |  21  ++++++++++++++++++
     deploy/insert_user.sql  |  14  ++++++++++++
     revert/change_pass.sql  |    7  ++++++
     revert/insert_user.sql  |    7  ++++++
     sqitch.plan                        |    2  ++
     test/change_pass.sql      |  52  ++++++++++++++++++++++++++++++++++
     test/insert_user.sql      |  69  ++++++++++++++++++++++++++++++++++
     verify/change_pass.sql  |    7  ++++++
     verify/insert_user.sql  |  10  +++++++++
     10  files  changed,  190  insertions(+)
     create  mode  100644  .gitattributes
     create  mode  100644  deploy/change_pass.sql
     create  mode  100644  deploy/insert_user.sql
     create  mode  100644  revert/change_pass.sql
     create  mode  100644  revert/insert_user.sql
     create  mode  100644  test/change_pass.sql
     create  mode  100644  test/insert_user.sql
     create  mode  100644  verify/change_pass.sql
     create  mode  100644  verify/insert_user.sql
    >
    >  git  checkout  master
    Switched  to  branch  'master'
    >
    >

    View Slide

  753.    git  merge  -­‐-­‐no-­‐ff  userfuncs  -­‐m  'Merge  branch  "userfuncs".'              
    Merge  made  by  the  'recursive'  strategy.
     .gitattributes                  |    1  +
     deploy/change_pass.sql  |  21  ++++++++++++++++++
     deploy/insert_user.sql  |  14  ++++++++++++
     revert/change_pass.sql  |    7  ++++++
     revert/insert_user.sql  |    7  ++++++
     sqitch.plan                        |    2  ++
     test/change_pass.sql      |  52  ++++++++++++++++++++++++++++++++++
     test/insert_user.sql      |  69  ++++++++++++++++++++++++++++++++++
     verify/change_pass.sql  |    7  ++++++
     verify/insert_user.sql  |  10  +++++++++
     10  files  changed,  190  insertions(+)
     create  mode  100644  .gitattributes
     create  mode  100644  deploy/change_pass.sql
     create  mode  100644  deploy/insert_user.sql
     create  mode  100644  revert/change_pass.sql
     create  mode  100644  revert/insert_user.sql
     create  mode  100644  test/change_pass.sql
     create  mode  100644  test/insert_user.sql
     create  mode  100644  verify/change_pass.sql
     create  mode  100644  verify/insert_user.sql
    >
    >  git  checkout  master
    Switched  to  branch  'master'
    >
    >
    \O/

    View Slide

  754. Pusher
    >

    View Slide

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

    View Slide

  756. antisocial network

    View Slide

  757. Ship it!
    antisocial network

    View Slide

  758. Ship Shape
    antisocial network

    View Slide

  759. Ship Shape
    Good work so far
    antisocial network

    View Slide

  760. Ship Shape
    Good work so far
    People gonna flip out
    antisocial network

    View Slide

  761. Ship Shape
    Good work so far
    People gonna flip out
    Let’s tag a dev release
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

  764. You’re It
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  770. Bundle Up
    >

    View Slide

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

    View Slide

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

    View Slide

  773. Bundled?
    >

    View Slide

  774. >  cd  flipr-­‐1.0.0-­‐r1
    >
    Bundled?
    >

    View Slide

  775.    createdb  flipr_qa  
    >
    >  cd  flipr-­‐1.0.0-­‐r1
    >
    Bundled?
    >

    View Slide

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

    View Slide

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

    View Slide

  778. antisocial network
    Merge Madness

    View Slide

  779. antisocial network
    Merge Madness
    Merge everything

    View Slide

  780. antisocial network
    Merge Madness
    Merge everything
    Back to master

    View Slide

  781. antisocial network
    Merge Madness
    Merge everything
    Back to master
    users

    View Slide

  782. antisocial network
    Merge Madness
    Merge everything
    Back to master
    users
    flips

    View Slide

  783. antisocial network
    Merge Madness
    Merge everything
    Back to master
    users
    flips
    userfuncs

    View Slide

  784. antisocial network
    Merge Madness
    Merge everything
    Back to master
    users
    flips
    userfuncs
    Union merge
    sqitch.plan

    View Slide

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

    View Slide

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

    View Slide

  787. Ruh-Roh
    >

    View Slide

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

    View Slide

  789.    psql  -­‐d  flipr_test  -­‐c  "
           SELECT  flipr.insert_user('foo',  'secr3t'),
                         flipr.insert_user('bar',  'secr3t');
           SELECT  nickname,  password  FROM  flipr.users;
    "
     nickname  |                          password                          
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
     foo            |  9695da4dd567a19f9b92065f240c6725
     bar            |  9695da4dd567a19f9b92065f240c6725
    (2  rows)
    >
    Ruh-Roh
    >
    >  git  reset  -­‐-­‐hard  reltag
    HEAD  is  now  at  dce7606  Tag  the  database  @v1.0.0-­‐r1.
    >
    Same
    password

    View Slide

  790.    psql  -­‐d  flipr_test  -­‐c  "
           SELECT  flipr.insert_user('foo',  'secr3t'),
                         flipr.insert_user('bar',  'secr3t');
           SELECT  nickname,  password  FROM  flipr.users;
    "
     nickname  |                          password                          
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
     foo            |  9695da4dd567a19f9b92065f240c6725
     bar            |  9695da4dd567a19f9b92065f240c6725
    (2  rows)
    >
    Ruh-Roh
    >
    >  git  reset  -­‐-­‐hard  reltag
    HEAD  is  now  at  dce7606  Tag  the  database  @v1.0.0-­‐r1.
    >

    View Slide

  791.    psql  -­‐d  flipr_test  -­‐c  "
           SELECT  flipr.insert_user('foo',  'secr3t'),
                         flipr.insert_user('bar',  'secr3t');
           SELECT  nickname,  password  FROM  flipr.users;
    "
     nickname  |                          password                          
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
     foo            |  9695da4dd567a19f9b92065f240c6725
     bar            |  9695da4dd567a19f9b92065f240c6725
    (2  rows)
    >
    Ruh-Roh
    >
    Not good.
    >  git  reset  -­‐-­‐hard  reltag
    HEAD  is  now  at  dce7606  Tag  the  database  @v1.0.0-­‐r1.
    >

    View Slide

  792. PGCryptonite
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  803. You Know the Drill
    antisocial network

    View Slide

  804. You Know the Drill
    Write revert script
    antisocial network

    View Slide

  805. You Know the Drill
    Write revert script
    Add test
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  810. >

    View Slide

  811. >
       git  diff  test/insert_user.sql
    diff  -­‐-­‐git  a/test/insert_user.sql  b/test/insert_user.sql
    index  225bb18..2a775a6  100644
    -­‐-­‐-­‐  a/test/insert_user.sql
    +++  b/test/insert_user.sql
    @@  -­‐29,25  +29,25  @@  SELECT  lives_ok(
             'Insert  a  user'
     );
     
    -­‐SELECT  row_eq(
    -­‐      'SELECT  *  FROM  users',
    -­‐      ROW('theory',  md5('foo'),  NOW())::users,
    -­‐      'The  user  should  have  been  inserted'
    -­‐);
    +SELECT  ok(  EXISTS(
    +        SELECT  1  FROM  flipr.users
    +          WHERE  nickname  =  'theory'
    +              AND  password  =  crypt('foo',  password)
    +),  'The  user  should  have  been  inserted'  );
     
     SELECT  lives_ok(
             $$  SELECT  insert_user('strongrrl',  'w00t')  $$,
             'Insert  another  user'
     );

    View Slide

  812. >
       git  diff  test/insert_user.sql
    diff  -­‐-­‐git  a/test/insert_user.sql  b/test/insert_user.sql
    index  225bb18..2a775a6  100644
    -­‐-­‐-­‐  a/test/insert_user.sql
    +++  b/test/insert_user.sql
    @@  -­‐29,25  +29,25  @@  SELECT  lives_ok(
             'Insert  a  user'
     );
     
    -­‐SELECT  row_eq(
    -­‐      'SELECT  *  FROM  users',
    -­‐      ROW('theory',  md5('foo'),  NOW())::users,
    -­‐      'The  user  should  have  been  inserted'
    -­‐);
    +SELECT  ok(  EXISTS(
    +        SELECT  1  FROM  flipr.users
    +          WHERE  nickname  =  'theory'
    +              AND  password  =  crypt('foo',  password)
    +),  'The  user  should  have  been  inserted'  );
     
     SELECT  lives_ok(
             $$  SELECT  insert_user('strongrrl',  'w00t')  $$,
             'Insert  another  user'
     );

    View Slide

  813. >
       git  diff  test/insert_user.sql
    diff  -­‐-­‐git  a/test/insert_user.sql  b/test/insert_user.sql
    index  225bb18..2a775a6  100644
    -­‐-­‐-­‐  a/test/insert_user.sql
    +++  b/test/insert_user.sql
    @@  -­‐29,25  +29,25  @@  SELECT  lives_ok(
             'Insert  a  user'
     );
     
    -­‐SELECT  row_eq(
    -­‐      'SELECT  *  FROM  users',
    -­‐      ROW('theory',  md5('foo'),  NOW())::users,
    -­‐      'The  user  should  have  been  inserted'
    -­‐);
    +SELECT  ok(  EXISTS(
    +        SELECT  1  FROM  flipr.users
    +          WHERE  nickname  =  'theory'
    +              AND  password  =  crypt('foo',  password)
    +),  'The  user  should  have  been  inserted'  );
     
     SELECT  lives_ok(
             $$  SELECT  insert_user('strongrrl',  'w00t')  $$,
             'Insert  another  user'
     );

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  818. @@  -­‐56,14  +56,8  @@  SELECT  throws_ok(  
             'Should  get  an  error  for  duplicate  nickname'  
     );  
       
    -­‐SELECT  bag_eq(  
    -­‐        'SELECT  *  FROM  users',  
    -­‐        $$  VALUES  
    -­‐                ('theory',        md5('foo'),    NOW()),  
    -­‐                ('strongrrl',  md5('w00t'),  NOW())  
    -­‐        $$,  
    -­‐        'Should  still  have  just  the  two  users'  
    -­‐);  
    +SELECT  is(COUNT(*)::INT,  2,  'Should  still  have  two  users')  
    +    FROM  flipr.users;  
       
     SELECT  finish();  
     ROLLBACK;  
    >

    View Slide

  819. @@  -­‐56,14  +56,8  @@  SELECT  throws_ok(  
             'Should  get  an  error  for  duplicate  nickname'  
     );  
       
    -­‐SELECT  bag_eq(  
    -­‐        'SELECT  *  FROM  users',  
    -­‐        $$  VALUES  
    -­‐                ('theory',        md5('foo'),    NOW()),  
    -­‐                ('strongrrl',  md5('w00t'),  NOW())  
    -­‐        $$,  
    -­‐        'Should  still  have  just  the  two  users'  
    -­‐);  
    +SELECT  is(COUNT(*)::INT,  2,  'Should  still  have  two  users')  
    +    FROM  flipr.users;  
       
     SELECT  finish();  
     ROLLBACK;  
    >

    View Slide

  820. @@  -­‐56,14  +56,8  @@  SELECT  throws_ok(  
             'Should  get  an  error  for  duplicate  nickname'  
     );  
       
    -­‐SELECT  bag_eq(  
    -­‐        'SELECT  *  FROM  users',  
    -­‐        $$  VALUES  
    -­‐                ('theory',        md5('foo'),    NOW()),  
    -­‐                ('strongrrl',  md5('w00t'),  NOW())  
    -­‐        $$,  
    -­‐        'Should  still  have  just  the  two  users'  
    -­‐);  
    +SELECT  is(COUNT(*)::INT,  2,  'Should  still  have  two  users')  
    +    FROM  flipr.users;  
       
     SELECT  finish();  
     ROLLBACK;  
    >

    View Slide

  821. FAIL
    >

    View Slide

  822. FAIL
    >  pg_prove  -­‐d  flipr_test  test/insert_user.sql
    #  Failed  test  7:  "The  user  should  have  been  inserted"
    #  Failed  test  10:  "The  second  user  should  have  been  inserted"
    #  Looks  like  you  failed  2  tests  of  12
    test/insert_user.sql  ..  Failed  2/12  subtests  
    Test  Summary  Report
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
    test/insert_user.sql  (Wstat:  0  Tests:  12  Failed:  2)
       Failed  tests:    7,  10
    Files=1,  Tests=12,    0  wallclock  secs
    Result:  FAIL
    >

    View Slide

  823. FAIL
    >  pg_prove  -­‐d  flipr_test  test/insert_user.sql
    #  Failed  test  7:  "The  user  should  have  been  inserted"
    #  Failed  test  10:  "The  second  user  should  have  been  inserted"
    #  Looks  like  you  failed  2  tests  of  12
    test/insert_user.sql  ..  Failed  2/12  subtests  
    Test  Summary  Report
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
    test/insert_user.sql  (Wstat:  0  Tests:  12  Failed:  2)
       Failed  tests:    7,  10
    Files=1,  Tests=12,    0  wallclock  secs
    Result:  FAIL
    >
    As expected.

    View Slide

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

    View Slide

  825. Managing
    procedures
    is a PITA!
    antisocial network

    View Slide

  826. Consider this Change
    >

    View Slide

  827. Consider this Change
       git  diff  deploy  
    diff  -­‐-­‐git  a/deploy/insert_user.sql  b/deploy/insert_user.sql
    index  eb30fed..5c28d02  100644
    -­‐-­‐-­‐  a/deploy/insert_user.sql
    +++  b/deploy/insert_user.sql
    @@  -­‐8,7  +8,7  @@  CREATE  OR  REPLACE  FUNCTION  flipr.insert_user(
             nickname  TEXT,
             password  TEXT
     )  RETURNS  VOID  LANGUAGE  SQL  SECURITY  DEFINER  AS  $$
    -­‐        INSERT  INTO  flipr.users  VALUES($1,  md5($2));
    +        INSERT  INTO  flipr.users  values($1,  crypt($2,  gen_salt('md5')));
     $$;
     
     COMMIT;
    >
    >

    View Slide

  828. Consider this Change
       git  diff  deploy  
    diff  -­‐-­‐git  a/deploy/insert_user.sql  b/deploy/insert_user.sql
    index  eb30fed..5c28d02  100644
    -­‐-­‐-­‐  a/deploy/insert_user.sql
    +++  b/deploy/insert_user.sql
    @@  -­‐8,7  +8,7  @@  CREATE  OR  REPLACE  FUNCTION  flipr.insert_user(
             nickname  TEXT,
             password  TEXT
     )  RETURNS  VOID  LANGUAGE  SQL  SECURITY  DEFINER  AS  $$
    -­‐        INSERT  INTO  flipr.users  VALUES($1,  md5($2));
    +        INSERT  INTO  flipr.users  values($1,  crypt($2,  gen_salt('md5')));
     $$;
     
     COMMIT;
    >
    >
    Simple, right?

    View Slide

  829. Not So Much
    antisocial network

    View Slide

  830. Not So Much
    Copy insert_user.sql to new deploy file
    antisocial network

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  836. >

    View Slide

  837. >  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));
    +$$;
    >

    View Slide

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

    View Slide

  839. Let Sqitch do the work.
    antisocial network

    View Slide

  840. Rework It
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  845. Same Files?
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  850. What’s the Diff?
    >

    View Slide

  851. What’s the Diff?
       git  diff  deploy/insert_user.sql
    diff  -­‐-­‐git  a/deploy/insert_user.sql  b/deploy/insert_user.sql
    @@  -­‐1,6  +1,7  @@
     -­‐-­‐  Deploy  insert_user
     -­‐-­‐  requires:  users
     -­‐-­‐  requires:  appschema
    +-­‐-­‐  requires:  pgcrypto
     
     BEGIN;
     
    @@  -­‐8,7  +9,7  @@  CREATE  OR  REPLACE  FUNCTION  flipr.insert_user(
             nickname  TEXT,
             password  TEXT
     )  RETURNS  VOID  LANGUAGE  SQL  SECURITY  DEFINER  AS  $$
    -­‐        INSERT  INTO  flipr.users  VALUES($1,  md5($2));
    +        INSERT  INTO  flipr.users  values($1,  crypt($2,  gen_salt('md5')));
     $$;
     
     COMMIT;
    >

    View Slide

  852. Send it Up!
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  856. Can We Go Back?
    >

    View Slide

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

    View Slide

  858.    psql  -­‐d  flipr_test  -­‐c  "
           DELETE  FROM  flipr.users;
           SELECT  flipr.insert_user('foo',  'secr3t'),
                         flipr.insert_user('bar',  'secr3t');
           SELECT  nickname,  password  FROM  flipr.users;
    "
     nickname  |                          password                          
    -­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐+-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐-­‐
     foo            |  9695da4dd567a19f9b92065f240c6725
     bar            |  9695da4dd567a19f9b92065f240c6725
    (2  rows)
    Can We Go Back?
    >  sqitch  revert  -­‐-­‐to  @HEAD^  -­‐y
       -­‐  insert_user  ..  ok
    >
    >

    View Slide

  859. Verify How?
    >

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  867. >  emacs  verify/insert_user.sql  
    >
    Let’s Go!
    >  sqitch  deploy
    Deploying  changes  to  flipr_test
       +  insert_user  ..  ok
    >
    >  psql  -­‐d  flipr_test  -­‐c  'DELETE  FROM  flipr.users'
    DELETE  2
    >
       pg_prove  -­‐d  flipr_test  test/insert_user.sql        
    test/insert_user.sql  ..  ok          
    All  tests  successful.
    Files=1,  Tests=12,    0  wallclock  secs
    Result:  PASS
    >
    Shazam!

    View Slide

  868. Add, Commit, Push, Go
    >

    View Slide

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

    View Slide

  870.    git  add  .
    >  git  commit  -­‐m  'Update  insert_user  to  use  pgcrypto.'
    [master  e4edde6]  Update  insert_user  to  use  pgcrypto.
     8  files  changed,  64  insertions(+),  24  deletions(-­‐)
     create  mode  100644  deploy/[email protected]­‐r1.sql
     create  mode  100644  revert/[email protected]­‐r1.sql
     create  mode  100644  verify/[email protected]­‐r1.sql
    >
    Add, Commit, Push, Go
    >  git  push
    Counting  objects:  17,  done.
    Delta  compression  using  up  to  4  threads.
    Compressing  objects:  100%  (10/10),  done.
    Writing  objects:  100%  (10/10),  1.77  KiB,  done.
    Total  10  (delta  6),  reused  0  (delta  0)
    To  https://github.com/theory/agile-­‐flipr.git
         9a9f1da..e4edde6    master  -­‐>  master
    >
    >

    View Slide

  871. antisocial network
    Change It Up

    View Slide

  872. antisocial network
    Change It Up
    git reset --hard
    insert_user2

    View Slide

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

    View Slide

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

    View Slide

  875. antisocial network
    Change It Up
    git reset --hard
    insert_user2
    Rework change_pass
    Use pgcrypo
    Test first!

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  879. View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  883. I hope you enjoyed
    working here.
    antisocial network
    RIP

    View Slide

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

    View Slide

  885. Git Skillz
    antisocial network
    RIP

    View Slide

  886. Git Skillz
    Branching
    antisocial network
    RIP

    View Slide

  887. Git Skillz
    Branching
    Diffing
    antisocial network
    RIP

    View Slide

  888. Git Skillz
    Branching
    Diffing
    Rebasing
    antisocial network
    RIP

    View Slide

  889. Git Skillz
    Branching
    Diffing
    Rebasing
    Merging
    antisocial network
    RIP

    View Slide

  890. Git Skillz
    Branching
    Diffing
    Rebasing
    Merging
    Committing
    antisocial network
    RIP

    View Slide

  891. Git Skillz
    Branching
    Diffing
    Rebasing
    Merging
    Committing
    Pushing
    antisocial network
    RIP

    View Slide

  892. MOAR Git
    antisocial network
    RIP

    View Slide

  893. MOAR Git
    Bisecting
    antisocial network
    RIP

    View Slide

  894. MOAR Git
    Bisecting
    Blaming
    antisocial network
    RIP

    View Slide

  895. MOAR Git
    Bisecting
    Blaming
    Pull requests
    antisocial network
    RIP

    View Slide

  896. MOAR Git
    Bisecting
    Blaming
    Pull requests
    Submitting patches
    antisocial network
    RIP

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  900. pgTAP Skillz
    antisocial network
    RIP

    View Slide

  901. pgTAP Skillz
    TDDD
    antisocial network
    RIP

    View Slide

  902. pgTAP Skillz
    TDDD
    Schema testing
    antisocial network
    RIP

    View Slide

  903. pgTAP Skillz
    TDDD
    Schema testing
    Scalar testing
    antisocial network
    RIP

    View Slide

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

    View Slide

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

    View Slide

  906. MOAR pgTAP
    antisocial network
    RIP

    View Slide

  907. MOAR pgTAP
    Testing privileges
    antisocial network
    RIP

    View Slide

  908. MOAR pgTAP
    Testing privileges
    Mocking interfaces
    antisocial network
    RIP

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  913. Sqitch Skillz
    antisocial network
    RIP

    View Slide

  914. Sqitch Skillz
    Add changes
    antisocial network
    RIP

    View Slide

  915. Sqitch Skillz
    Add changes
    Specify Dependencies
    antisocial network
    RIP

    View Slide

  916. Sqitch Skillz
    Add changes
    Specify Dependencies
    Manage targets
    antisocial network
    RIP

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  921. MOAR Sqitch
    antisocial network
    RIP

    View Slide

  922. MOAR Sqitch
    Cross-project dependencies
    antisocial network
    RIP

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  929. Good luck out there.
    antisocial network
    RIP

    View Slide

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

    View Slide