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

Agile Database Development 3ed

Agile Database Development 3ed

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), database unit testing (pgTAP), and database change management and deployment (Sqitch). 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, 2ed January 2014, 3ed January 2016.

David E. Wheeler

January 29, 2016
Tweet

More Decks by David E. Wheeler

Other Decks in Technology

Transcript

  1. Agile
    Database Development
    David E. Wheeler
    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.
    sqitch.org
    iovation
    January 28, 2016

    View full-size slide

  2. Agile
    Database Development
    David E. Wheeler
    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.

    sqitch.org
    iovation
    January 28, 2016

    View full-size slide

  3. David E. Wheeler
    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 full-size slide

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

    View full-size slide

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

    CEO, Data Architect
    Agile Archapelago, Inc.

    View full-size slide

  6. This is Genius

    View full-size slide

  7. This is Genius
    I had this idea

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  11. 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 full-size 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
    Introducing…

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  15. How it Works
    antisocial network

    View full-size slide

  16. How it Works
    Microblogging platform
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  21. 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 full-size slide

  22. Your Task
    antisocial network

    View full-size slide

  23. Your Task
    Create the database
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  27. 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 full-size slide

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

    View full-size slide

  29. But first…
    antisocial network

    View full-size slide

  30. NDA
    antisocial network

    View full-size slide

  31. Job Requirements
    antisocial network

    View full-size slide

  32. Job Requirements
    PostgreSQL
    Should be installed
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  37. We good?
    antisocial network

    View full-size slide

  38. Let’s do this thang.
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  43. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  47. Create a Remote

    View full-size slide

  48. Create a Remote
    Create Git project in Stash

    View full-size slide

  49. Create a Remote
    Create Git project in Stash
    Or GitHub or BitBucket

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  56. 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 full-size 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
    > 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 full-size 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 .
    > 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 full-size 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
    >
    We have a
    Git repo.

    View full-size slide

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

    View full-size slide

  61. git push origin --set-upstream master
    Counting objects: 3, done.
    Writing objects: 100% (3/3), 260 bytes, done.
    Total 3 (delta 0), reused 0 (delta 0)
    To ssh://[email protected]:7999/~david.wheeler/
    agile-flipr.git
    * [new branch] master -> master
    Branch master set up to track remote branch master from
    origin.
    >
    git remote add origin ssh://[email protected]:7999
    >
    >
    Origin

    View full-size slide

  62. git push origin --set-upstream master
    Counting objects: 3, done.
    Writing objects: 100% (3/3), 260 bytes, done.
    Total 3 (delta 0), reused 0 (delta 0)
    To ssh://[email protected]:7999/~david.wheeler/
    agile-flipr.git
    * [new branch] master -> master
    Branch master set up to track remote branch master from
    origin.
    >
    git remote add origin ssh://[email protected]:7999
    >
    >
    Origin

    View full-size slide

  63. git push origin --set-upstream master
    Counting objects: 3, done.
    Writing objects: 100% (3/3), 260 bytes, done.
    Total 3 (delta 0), reused 0 (delta 0)
    To ssh://[email protected]:7999/~david.wheeler/
    agile-flipr.git
    * [new branch] master -> master
    Branch master set up to track remote branch master from
    origin.
    >
    git remote add origin ssh://[email protected]:7999
    >
    >
    Origin

    View full-size slide

  64. git push origin --set-upstream master
    Counting objects: 3, done.
    Writing objects: 100% (3/3), 260 bytes, done.
    Total 3 (delta 0), reused 0 (delta 0)
    To ssh://[email protected]:7999/~david.wheeler/
    agile-flipr.git
    * [new branch] master -> master
    Branch master set up to track remote branch master from
    origin.
    >
    git remote add origin ssh://[email protected]:7999
    >
    >
    Origin

    View full-size slide

  65. Swimming Upstream
    >

    View full-size slide

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

    View full-size slide

  67. 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] appschema -> upstream/appschema
    * [new branch] change_pass -> upstream/change_pass
    * [new branch] flips -> upstream/flips
    * [new branch] insert_user -> upstream/insert_user
    * [new branch] insert_user2 -> upstream/insert_user2
    * [new branch] master -> upstream/master
    * [new branch] reltag -> upstream/reltag
    * [new branch] userfuncs -> upstream/userfuncs
    * [new branch] users -> upstream/users
    >

    View full-size slide

  68. 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] appschema -> upstream/appschema
    * [new branch] change_pass -> upstream/change_pass
    * [new branch] flips -> upstream/flips
    * [new branch] insert_user -> upstream/insert_user
    * [new branch] insert_user2 -> upstream/insert_user2
    * [new branch] master -> upstream/master
    * [new branch] reltag -> upstream/reltag
    * [new branch] userfuncs -> upstream/userfuncs
    * [new branch] users -> upstream/users
    >
    For
    resetting
    later

    View full-size slide

  69. Git?
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  76. Why Git?
    antisocial network

    View full-size slide

  77. Why Git?
    Anyone can clone
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  82. 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 full-size slide

  83. PiSHA1
    antisocial network

    View full-size slide

  84. PiSHA1
    SHA1 ID for every object
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  92. >
    Making a hash of it

    View full-size slide

  93. > git cat-file commit HEAD
    tree 8b0955fd015782a26953e20d41db5fadbb347c14
    parent 0f38581c4d19313d910c2080b3470cae07f3694e
    author David Wheeler 1453941938 -0800
    committer David Wheeler 1453941938 -0800
    Get through "Ship it!".
    >
    Making a hash of it

    View full-size slide

  94. > git cat-file commit HEAD
    tree 8b0955fd015782a26953e20d41db5fadbb347c14
    parent 0f38581c4d19313d910c2080b3470cae07f3694e
    author David Wheeler 1453941938 -0800
    committer David Wheeler 1453941938 -0800
    Get through "Ship it!".
    >
    Making a hash of it

    View full-size slide

  95. > git cat-file commit HEAD
    tree 8b0955fd015782a26953e20d41db5fadbb347c14
    parent 0f38581c4d19313d910c2080b3470cae07f3694e
    author David Wheeler 1453941938 -0800
    committer David Wheeler 1453941938 -0800
    Get through "Ship it!".
    >
    Making a hash of it

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  99. SHAzam!
    antisocial network

    View full-size slide

  100. SHAzam!
    Each commit (except root) includes parent
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  104. SHAzam!
    Each commit (except root) 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 full-size slide

  105. SHAzam!
    Each commit (except root) 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/
    Also: Bitcoin Blockchain
    antisocial network

    View full-size slide

  106. antisocial network
    Your Turn

    View full-size slide

  107. antisocial network
    Your Turn
    Configure Git

    View full-size slide

  108. antisocial network
    Your Turn
    Configure Git
    Initialize repository

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  113. Who am I again?
    >

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  118. ~/.sqitch/sqitc
    ~/
    .sqitch/sqitch.conf
    [core "pg"]
    client = /opt/local/pgsql/bin/psql
    [user]
    name = David E. Wheeler
    email = [email protected]

    View full-size slide

  119. ~/.sqitch/sqitc
    ~/
    .sqitch/sqitch.conf
    [core "pg"]
    client = /opt/local/pgsql/bin/psql
    [user]
    name = David E. Wheeler
    email = [email protected]
    Good for
    all projects

    View full-size slide

  120. >
    Scratch that Sqitch

    View full-size slide

  121. > sqitch init flipr --engine pg \
    --uri file://../flipr-remote
    Created sqitch.conf
    Created sqitch.plan
    Created deploy/
    Created revert/
    Created verify/
    >
    >
    Scratch that Sqitch

    View full-size slide

  122. > sqitch init flipr --engine pg \
    --uri file://../flipr-remote
    Created sqitch.conf
    Created sqitch.plan
    Created deploy/
    Created revert/
    Created verify/
    >
    >
    Scratch that Sqitch

    View full-size slide

  123. > sqitch init flipr --engine pg \
    --uri file://../flipr-remote
    Created sqitch.conf
    Created sqitch.plan
    Created deploy/
    Created revert/
    Created verify/
    >
    >
    Scratch that Sqitch
    Use remote URL

    View full-size slide

  124. > sqitch init flipr --engine pg \
    --uri file://../flipr-remote
    Created sqitch.conf
    Created sqitch.plan
    Created deploy/
    Created revert/
    Created verify/
    >
    >
    Scratch that Sqitch

    View full-size slide

  125. > sqitch init flipr --engine pg \
    --uri file://../flipr-remote
    Created sqitch.conf
    Created sqitch.plan
    Created deploy/
    Created revert/
    Created verify/
    >
    >
    Scratch that Sqitch

    View full-size slide

  126. > sqitch init flipr --engine pg \
    --uri file://../flipr-remote
    Created sqitch.conf
    Created sqitch.plan
    Created deploy/
    Created revert/
    Created verify/
    >
    >
    Scratch that Sqitch

    View full-size slide

  127. > sqitch init flipr --engine pg \
    --uri file://../flipr-remote
    Created sqitch.conf
    Created sqitch.plan
    Created deploy/
    Created revert/
    Created verify/
    >
    >
    Scratch that Sqitch

    View full-size slide

  128. > sqitch init flipr --engine pg \
    --uri file://../flipr-remote
    Created sqitch.conf
    Created sqitch.plan
    Created deploy/
    Created revert/
    Created verify/
    >
    >
    Scratch that Sqitch

    View full-size slide

  129. > sqitch init flipr --engine pg \
    --uri file://../flipr-remote
    Created sqitch.conf
    Created sqitch.plan
    Created deploy/
    Created revert/
    Created verify/
    >
    >
    Scratch that Sqitch
    emacs sqitch.conf
    >

    View full-size slide

  130. sqitch.conf
    sqitch.conf
    [core]
    engine = pg
    # plan_file = sqitch.plan
    # top_dir = .
    # [engine "pg"]
    # target = db:pg:
    # registry = sqitch
    # client = /usr/local/pgsql/bin/psql

    View full-size slide

  131. sqitch.conf
    sqitch.conf
    [core]
    engine = pg
    # plan_file = sqitch.plan
    # top_dir = .
    # [engine "pg"]
    # target = db:pg:
    # registry = sqitch
    # client = /usr/local/pgsql/bin/psql
    --engine pg

    View full-size slide

  132. What’s the Plan Man?
    >

    View full-size slide

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

    View full-size slide

  134. sqitch.plan
    sqitch.plan
    %syntax-version=1.0.0
    %project=flipr
    %uri=file://../flipr-remote

    View full-size slide

  135. sqitch.plan
    sqitch.plan
    %syntax-version=1.0.0
    %project=flipr
    %uri=file://../flipr-remote
    Identified

    View full-size slide

  136. Make It So
    > git add .
    > git commit -m 'Initialize Sqitch configuration.'
    [master 297c07d] Initialize Sqitch configuration.
    2 files changed, 20 insertions(+)
    create mode 100644 sqitch.conf
    create mode 100644 sqitch.plan
    >

    View full-size slide

  137. Make It So
    > git add .
    > git commit -m 'Initialize Sqitch configuration.'
    [master 297c07d] Initialize Sqitch configuration.
    2 files changed, 20 insertions(+)
    create mode 100644 sqitch.conf
    create mode 100644 sqitch.plan
    >

    View full-size slide

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

    View full-size slide

  139. Make It So
    > git add .
    > git commit -m 'Initialize Sqitch configuration.'
    [master 297c07d] Initialize Sqitch configuration.
    2 files changed, 20 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), 564 bytes, done.
    Total 4 (delta 0), reused 0 (delta 0)
    To ../flipr-remote
    7d28be0..297c07d master -> master
    >

    View full-size slide

  140. Make It So
    > git add .
    > git commit -m 'Initialize Sqitch configuration.'
    [master 297c07d] Initialize Sqitch configuration.
    2 files changed, 20 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), 564 bytes, done.
    Total 4 (delta 0), reused 0 (delta 0)
    To ../flipr-remote
    7d28be0..297c07d master -> master
    >

    View full-size slide

  141. Make It So
    > git add .
    > git commit -m 'Initialize Sqitch configuration.'
    [master 297c07d] Initialize Sqitch configuration.
    2 files changed, 20 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), 564 bytes, done.
    Total 4 (delta 0), reused 0 (delta 0)
    To ../flipr-remote
    7d28be0..297c07d master -> master
    >

    View full-size slide

  142. Where’ve We Been?
    >

    View full-size slide

  143. git log
    commit a4f765f88c1875ffe9427e73f72a6b66c297c07d
    Author: David E. Wheeler
    Date: Tue Jan 26 14:34:01 2016 -0800
    Initialize Sqitch configuration.
    commit 2c4b51017929634a30f4a74e20268c05e7d28be0
    Author: David E. Wheeler
    Date: Tue Jan 26 13:28:09 2016 -0800
    Initialize repo, add README.
    >
    Where’ve We Been?
    >

    View full-size slide

  144. git log
    commit a4f765f88c1875ffe9427e73f72a6b66c297c07d
    Author: David E. Wheeler
    Date: Tue Jan 26 14:34:01 2016 -0800
    Initialize Sqitch configuration.
    commit 2c4b51017929634a30f4a74e20268c05e7d28be0
    Author: David E. Wheeler
    Date: Tue Jan 26 13:28:09 2016 -0800
    Initialize repo, add README.
    >
    Where’ve We Been?
    >

    View full-size slide

  145. git log
    commit a4f765f88c1875ffe9427e73f72a6b66c297c07d
    Author: David E. Wheeler
    Date: Tue Jan 26 14:34:01 2016 -0800
    Initialize Sqitch configuration.
    commit 2c4b51017929634a30f4a74e20268c05e7d28be0
    Author: David E. Wheeler
    Date: Tue Jan 26 13:28:09 2016 -0800
    Initialize repo, add README.
    >
    Where’ve We Been?
    >

    View full-size slide

  146. First Change
    >

    View full-size slide

  147. 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 full-size slide

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

    View full-size slide

  151. deploy/appschem
    deploy/appschema.sql
    -- Deploy flipr:appschema to pg
    BEGIN;
    COMMIT;
    -- XXX Add DDLs here.

    View full-size slide

  152. deploy/appschem
    deploy/appschema.sql
    -- Deploy flipr:appschema to pg
    BEGIN;
    COMMIT;
    CREATE SCHEMA flipr;

    View full-size slide

  153. > 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 full-size slide

  154. > 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 full-size slide

  155. revert/appschem
    revert/appschema.sql
    -- Revert flipr:appschema from pg
    BEGIN;
    COMMIT;
    -- XXX Add DDLs here.

    View full-size slide

  156. revert/appschem
    revert/appschema.sql
    -- Revert flipr:appschema from pg
    BEGIN;
    COMMIT;
    DROP SCHEMA flipr;

    View full-size slide

  157. Make it So!
    >

    View full-size slide

  158. createdb flipr_test
    > 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 full-size slide

  159. createdb flipr_test
    > 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!
    >
    Might need
    -U postgres

    View full-size slide

  160. createdb flipr_test
    > 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 full-size slide

  161. createdb flipr_test
    > 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 full-size slide

  162. createdb flipr_test
    > 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 full-size slide

  163. createdb flipr_test
    > 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 full-size slide

  164. createdb flipr_test
    > 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 full-size slide

  165. createdb flipr_test
    > 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 full-size slide

  166. createdb flipr_test
    > 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 full-size slide

  167. -- Verify flipr:appschema on pg
    BEGIN;
    ROLLBACK;
    verify/appschem
    verify/appschema.sql
    -- XXX Add verifications here.

    View full-size slide

  168. -- Verify flipr:appschema on pg
    BEGIN;
    ROLLBACK;
    SELECT pg_catalog.has_schema_privilege('nada', 'usage');
    verify/appschem
    verify/appschema.sql

    View full-size slide

  169. -- Verify flipr:appschema on pg
    BEGIN;
    ROLLBACK;
    SELECT pg_catalog.has_schema_privilege('nada', 'usage');
    verify/appschem
    verify/appschema.sql
    Let’s try
    it, first

    View full-size slide

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

    View full-size slide

  171. Trust, But Verify
    > 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
    >

    View full-size slide

  172. Trust, But Verify
    > 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
    >

    View full-size slide

  173. Trust, But Verify
    > 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
    >

    View full-size slide

  174. Trust, But Verify
    > 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
    >

    View full-size slide

  175. Trust, But Verify
    >

    View full-size slide

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

    View full-size slide

  177. 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 full-size slide

  178. >
    How’s it Look?

    View full-size slide

  179. >
    How’s it Look?
    > sqitch status db:pg:flipr_test
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 14:49:54 -0800
    # By: David E. Wheeler
    #
    Nothing to deploy (up-to-date)
    >

    View full-size slide

  180. >
    How’s it Look?
    > sqitch status db:pg:flipr_test
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 14:49:54 -0800
    # By: David E. Wheeler
    #
    Nothing to deploy (up-to-date)
    >

    View full-size slide

  181. >
    How’s it Look?
    > sqitch status db:pg:flipr_test
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 14:49:54 -0800
    # By: David E. Wheeler
    #
    Nothing to deploy (up-to-date)
    >

    View full-size slide

  182. >
    How’s it Look?
    > sqitch status db:pg:flipr_test
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 14:49:54 -0800
    # By: David E. Wheeler
    #
    Nothing to deploy (up-to-date)
    >

    View full-size slide

  183. >
    How’s it Look?
    > sqitch status db:pg:flipr_test
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 14:49:54 -0800
    # By: David E. Wheeler
    #
    Nothing to deploy (up-to-date)
    >

    View full-size slide

  184. >
    How’s it Look?
    > sqitch status db:pg:flipr_test
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 14:49:54 -0800
    # By: David E. Wheeler
    #
    Nothing to deploy (up-to-date)
    >

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  188. > 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 full-size slide

  189. > 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 full-size slide

  190. History
    >
    > sqitch log db:pg:flipr_test
    On database db:pg:flipr_test
    Revert 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    Name: appschema
    Committer: David E. Wheeler
    Date: 2016-01-26 15:35:04 -0800
    Adds flipr app schema.
    Deploy 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    Name: appschema
    Committer: David E. Wheeler
    Date: 2016-01-26 14:49:54 -0800
    Adds flipr app schema.

    View full-size slide

  191. History
    >
    > sqitch log db:pg:flipr_test
    On database db:pg:flipr_test
    Revert 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    Name: appschema
    Committer: David E. Wheeler
    Date: 2016-01-26 15:35:04 -0800
    Adds flipr app schema.
    Deploy 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    Name: appschema
    Committer: David E. Wheeler
    Date: 2016-01-26 14:49:54 -0800
    Adds flipr app schema.

    View full-size slide

  192. History
    >
    > sqitch log db:pg:flipr_test
    On database db:pg:flipr_test
    Revert 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    Name: appschema
    Committer: David E. Wheeler
    Date: 2016-01-26 15:35:04 -0800
    Adds flipr app schema.
    Deploy 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    Name: appschema
    Committer: David E. Wheeler
    Date: 2016-01-26 14:49:54 -0800
    Adds flipr app schema.

    View full-size slide

  193. Commit It!
    > git add .
    > git commit -m 'Add flipr schema.'
    [master dc23038] 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 full-size slide

  194. Commit It!
    > git add .
    > git commit -m 'Add flipr schema.'
    [master dc23038] 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 full-size slide

  195. Commit It!
    > git add .
    > git commit -m 'Add flipr schema.'
    [master dc23038] 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 full-size slide

  196. Commit It!
    > git add .
    > git commit -m 'Add flipr schema.'
    [master dc23038] 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), 943 bytes, done.
    Total 9 (delta 0), reused 0 (delta 0)
    To ../flipr-remote
    a4f765f..dc23038 master -> master

    View full-size slide

  197. Commit It!
    > git add .
    > git commit -m 'Add flipr schema.'
    [master dc23038] 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), 943 bytes, done.
    Total 9 (delta 0), reused 0 (delta 0)
    To ../flipr-remote
    a4f765f..dc23038 master -> master

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  202. Status Update
    >

    View full-size slide

  203. > sqitch status db:pg:flipr_test
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 15:39:31 -0800
    # By: David E. Wheeler
    #
    Nothing to deploy (up-to-date)
    >
    Status Update
    >

    View full-size slide

  204. Save My Fingers
    >

    View full-size slide

  205. Save My Fingers
    > sqitch config core.target db:pg:flipr_test
    >

    View full-size slide

  206. Save My Fingers
    > sqitch config core.target db:pg:flipr_test
    > sqitch status
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 15:39:31 -0800
    # By: David E. Wheeler
    #
    Nothing to deploy (up-to-date)
    >
    No URI

    View full-size slide

  207. Save My Fingers
    > sqitch config core.target db:pg:flipr_test
    > sqitch status
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 15:39:31 -0800
    # By: David E. Wheeler
    #
    Nothing to deploy (up-to-date)
    >
    No URI
    > sqitch config --bool deploy.verify true
    > sqitch config --bool rebase.verify true
    > Always
    verify.

    View full-size slide

  208. Commit Config
    >

    View full-size slide

  209. Commit Config
    > git add .
    > git commit -m 'Add db_name and verify to config.'
    [master 795697f] Add db_name and verify to config.
    1 file changed, 6 insertions(+)
    >

    View full-size slide

  210. Commit Config
    > git add .
    > git commit -m 'Add db_name and verify to config.'
    [master 795697f] Add db_name and verify to config.
    1 file changed, 6 insertions(+)
    >

    View full-size slide

  211. 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), 376 bytes, done.
    Total 3 (delta 2), reused 0 (delta 0)
    To ../flipr-remote
    dc23038..795697f master -> master
    >
    git add .
    > git commit -m 'Add db_name and verify to config.'
    [master 795697f] Add db_name and verify to config.
    1 file changed, 6 insertions(+)
    >

    View full-size slide

  212. Not Migrations?
    antisocial network

    View full-size slide

  213. Not Migrations?
    Incomplete mini-language
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  220. SQL Migrations?
    Incomplete mini-language
    No logical replication integration
    Numbered scripts hard to track
    No VCS awareness
    ———————————————
    ———————————————————
    Managing procedures is a PITA
    antisocial network

    View full-size slide

  221. Sq—what?
    sql anges
    ch
    antisocial network

    View full-size slide

  222. Sq—what?
    sq ch
    antisocial network

    View full-size slide

  223. it
    Sq—what?
    sq ch
    antisocial network

    View full-size slide

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

    View full-size slide

  225. Sqitch Philosophy
    antisocial network

    View full-size slide

  226. Sqitch Philosophy
    No opinions
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  232. 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 full-size slide

  233. SHAzbat
    antisocial network

    View full-size slide

  234. SHAzbat
    SHA1 ID for every object
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  240. 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 full-size slide

  241. 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 full-size slide

  242. 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 full-size slide

  243. 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 full-size slide

  244. 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 full-size slide

  245. 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 full-size slide

  246. 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 full-size slide

  247. 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 full-size slide

  248. 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 full-size slide

  249. 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 full-size 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 full-size 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.
    >

    View full-size 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 full-size slide

  253. SHApay!
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  257. 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 full-size slide

  258. 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 full-size slide

  259. Sqitch Features
    antisocial network

    View full-size slide

  260. Sqitch Features
    Git-style interface
    antisocial network

    View full-size slide

  261. Sqitch Features
    Git-style interface
    Reduced duplication
    antisocial network

    View full-size slide

  262. Sqitch Features
    Git-style interface
    Reduced duplication
    Built-in configuration
    antisocial network

    View full-size slide

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

    View full-size slide

  264. Sqitch Features
    Git-style interface
    Reduced duplication
    Built-in configuration
    Iterative development
    Deployment planning
    antisocial network

    View full-size slide

  265. Sqitch Features
    Git-style interface
    Reduced duplication
    Built-in configuration
    Iterative development
    Deployment planning
    Release tagging
    antisocial network

    View full-size slide

  266. antisocial network
    Your Turn

    View full-size slide

  267. antisocial network
    Your Turn
    Configure Sqitch

    View full-size slide

  268. antisocial network
    Your Turn
    Configure Sqitch
    Initialize project

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  273. Add Tests
    > mkdir test
    >

    View full-size slide

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

    View full-size slide

  275. test/appschema.
    pgTAP Basics

    View full-size slide

  276. test/appschema.
    pgTAP Basics
    SET client_min_messages TO warning;
    CREATE EXTENSION IF NOT EXISTS pgtap;
    RESET client_min_messages;
    BEGIN;
    -- Plan the tests.
    SELECT plan(1);
    SELECT has_schema('nada');
    -- Clean up.
    SELECT finish();
    ROLLBACK;

    View full-size slide

  277. test/appschema.
    pgTAP Basics
    SET client_min_messages TO warning;
    CREATE EXTENSION IF NOT EXISTS pgtap;
    RESET client_min_messages;
    BEGIN;
    -- Plan the tests.
    SELECT plan(1);
    SELECT has_schema('nada');
    -- Clean up.
    SELECT finish();
    ROLLBACK;

    View full-size slide

  278. test/appschema.
    pgTAP Basics
    SET client_min_messages TO warning;
    CREATE EXTENSION IF NOT EXISTS pgtap;
    RESET client_min_messages;
    BEGIN;
    -- Plan the tests.
    SELECT plan(1);
    SELECT has_schema('nada');
    -- Clean up.
    SELECT finish();
    ROLLBACK;

    View full-size slide

  279. test/appschema.
    pgTAP Basics
    SET client_min_messages TO warning;
    CREATE EXTENSION IF NOT EXISTS pgtap;
    RESET client_min_messages;
    BEGIN;
    -- Plan the tests.
    SELECT plan(1);
    SELECT has_schema('nada');
    -- Clean up.
    SELECT finish();
    ROLLBACK;

    View full-size slide

  280. test/appschema.
    pgTAP Basics
    SET client_min_messages TO warning;
    CREATE EXTENSION IF NOT EXISTS pgtap;
    RESET client_min_messages;
    BEGIN;
    -- Plan the tests.
    SELECT plan(1);
    SELECT has_schema('nada');
    -- Clean up.
    SELECT finish();
    ROLLBACK;

    View full-size slide

  281. test/appschema.
    pgTAP Basics
    SET client_min_messages TO warning;
    CREATE EXTENSION IF NOT EXISTS pgtap;
    RESET client_min_messages;
    BEGIN;
    -- Plan the tests.
    SELECT plan(1);
    SELECT has_schema('nada');
    -- Clean up.
    SELECT finish();
    ROLLBACK;

    View full-size slide

  282. test/appschema.
    pgTAP Basics
    SET client_min_messages TO warning;
    CREATE EXTENSION IF NOT EXISTS pgtap;
    RESET client_min_messages;
    BEGIN;
    -- Plan the tests.
    SELECT plan(1);
    SELECT has_schema('nada');
    -- Clean up.
    SELECT finish();
    ROLLBACK;

    View full-size slide

  283. >
    Run the Test

    View full-size slide

  284. >
    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 full-size slide

  285. >
    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 full-size slide

  286. >
    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 full-size slide

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

    View full-size slide

  288. 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 full-size slide

  289. 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 full-size slide

  290. git add .
    >
    Pass it On
    >

    View full-size slide

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

    View full-size slide

  292. git add .
    > git commit -m 'Add appschema test.'
    [master e46bdf9] 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), 487 bytes, done.
    Total 4 (delta 1), reused 0 (delta 0)
    To ../flipr-remote
    795697f..e46bdf9 master -> master
    >

    View full-size slide

  293. OMG TAP WTF?
    The Test Anything Protocol (TAP) is a
    protocol to allow communication between
    unit tests and a test harness. It allows
    individual tests (TAP producers) to
    communicate test results to the testing
    harness in a language-agnostic way.
    —Wikipedia

    View full-size slide

  294. What is TAP?
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  299. 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 full-size slide

  300. 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 full-size slide

  301. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  307. 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 full-size slide

  308. Scalarly
    antisocial network

    View full-size slide

  309. Scalarly
    Includes simple scalar test functions:
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  314. 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 full-size slide

  315. 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 full-size slide

  316. It’s All Relative
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  320. 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 full-size slide

  321. 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 full-size slide

  322. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  326. 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 full-size slide

  327. 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 full-size slide

  328. Schematics
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  331. 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 full-size slide

  332. 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 full-size slide

  333. Other Features and Topics
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  343. antisocial network
    Let’s do it!

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  349. 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 full-size slide

  350. 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 full-size slide

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

    View full-size slide

  352. antisocial network
    antisocial network

    View full-size slide

  353. TDD
    antisocial network
    antisocial network

    View full-size slide

  354. “The act of writing a unit test is
    more an act of design than of
    verification. It is also more an
    act of documentation than of
    verification. The act of writing a
    unit test closes a remarkable
    number of feedback loops, the
    least of which is the one per–
    taining to the verification of
    function.”
    —Robert C. Martin

    View full-size slide

  355. antisocial network

    View full-size slide

  356. TDD is an act
    of design.
    antisocial network

    View full-size slide

  357. antisocial network

    View full-size slide

  358. TDD is an act of
    documentation.
    antisocial network

    View full-size slide

  359. antisocial network

    View full-size slide

  360. Okay.
    antisocial network

    View full-size slide

  361. antisocial network

    View full-size slide

  362. But…
    antisocial network

    View full-size slide

  363. Database Design
    antisocial network

    View full-size slide

  364. Database Design
    Specify requirements
    antisocial network

    View full-size slide

  365. Database Design
    Specify requirements
    Implement schema design
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  371. Why TDDD
    antisocial network

    View full-size slide

  372. Why TDDD
    Ensure data quality
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  378. Why TDDD
    antisocial network

    View full-size slide

  379. Why TDDD
    Identify defects early
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  386. “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 full-size slide

  387. “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 full-size slide

  388. “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 full-size slide

  389. antisocial network

    View full-size slide

  390. Okay.
    antisocial network

    View full-size slide

  391. antisocial network

    View full-size slide

  392. How?
    antisocial network

    View full-size slide

  393. TDD How
    antisocial network

    View full-size slide

  394. TDD How
    Ideally separate from app tests
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  397. 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 full-size slide

  398. 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 full-size slide

  399. 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 full-size slide

  400. Branching Out
    >

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  403. git checkout -b users master
    Switched to a new branch 'users'
    >
    Branching Out
    >
    > emacs test/users.sql
    >

    View full-size slide

  404. test/users.sql
    Table For One

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  409. 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 full-size slide

  410. 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 full-size slide

  411. 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 full-size slide

  412. Sqitch Dependencies!
    >

    View full-size slide

  413. Sqitch Dependencies!
    >
    > sqitch add users --requires appschema \
    -n 'Creates table to track our users.'
    Created deploy/users.sql
    Created revert/users.sql
    Created verify/users.sql
    Added "users [appschema]" to sqitch.plan
    >

    View full-size slide

  414. Sqitch Dependencies!
    >
    > sqitch add users --requires appschema \
    -n 'Creates table to track our users.'
    Created deploy/users.sql
    Created revert/users.sql
    Created verify/users.sql
    Added "users [appschema]" to sqitch.plan
    >

    View full-size slide

  415. Sqitch Dependencies!
    >
    > sqitch add users --requires appschema \
    -n 'Creates table to track our users.'
    Created deploy/users.sql
    Created revert/users.sql
    Created verify/users.sql
    Added "users [appschema]" to sqitch.plan
    > emacs deploy/users.sql
    >

    View full-size slide

  416. deploy/users.sq
    deploy/users.sql
    -- XXX Add DDLs here.
    COMMIT;
    -- Deploy flipr:users to pg
    -- requires: appschema
    BEGIN;

    View full-size slide

  417. deploy/users.sq
    deploy/users.sql
    -- XXX Add DDLs here.
    COMMIT;
    -- Deploy flipr:users to pg
    -- requires: appschema
    BEGIN;

    View full-size slide

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

    View full-size slide

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

    Bare
    Minimum

    View full-size slide

  420. Verily, Users
    >
    > sqitch add users --requires appschema \
    -n 'Creates table to track our users.'
    Created deploy/users.sql
    Created revert/users.sql
    Created verify/users.sql
    Added "users [appschema]" to sqitch.plan
    > emacs deploy/users.sql
    >

    View full-size slide

  421. Verily, Users
    >
    > sqitch add users --requires appschema \
    -n 'Creates table to track our users.'
    Created deploy/users.sql
    Created revert/users.sql
    Created verify/users.sql
    Added "users [appschema]" to sqitch.plan
    > emacs deploy/users.sql
    > emacs verify/users.sql

    View full-size slide

  422. -- Verify flipr:users on pg
    BEGIN;
    verify/users.sq
    verify/users.sql
    -- XXX Add verifications here.
    ROLLBACK;

    View full-size slide

  423. -- Verify flipr:users on pg
    BEGIN;
    SELECT nickname
    FROM flipr.users
    WHERE FALSE;
    verify/users.sq
    verify/users.sql
    ROLLBACK;

    View full-size slide

  424. Unusered
    >
    > sqitch add users --requires appschema \
    -n 'Creates table to track our users.'
    Created deploy/users.sql
    Created revert/users.sql
    Created verify/users.sql
    Added "users [appschema]" to sqitch.plan
    > emacs deploy/users.sql
    > emacs verify/users.sql
    >

    View full-size slide

  425. Unusered
    >
    > sqitch add users --requires appschema \
    -n 'Creates table to track our users.'
    Created deploy/users.sql
    Created revert/users.sql
    Created verify/users.sql
    Added "users [appschema]" to sqitch.plan
    > emacs deploy/users.sql
    > emacs verify/users.sql
    >
    > emacs revert/users.sql

    View full-size slide

  426. revert/users.sq
    revert/users.sql
    -- Revert flipr:users from pg
    BEGIN;
    COMMIT;
    -- XXX Add DDLs here.

    View full-size slide

  427. revert/users.sq
    revert/users.sql
    -- Revert flipr:users from pg
    BEGIN;
    COMMIT;
    DROP TABLE flipr.users;

    View full-size slide

  428. sqitch deploy
    Deploying changes to db:pg:flipr_test
    + users .. ok
    >
    Make Users
    >

    View full-size slide

  429. sqitch deploy
    Deploying changes to db:pg:flipr_test
    + users .. ok
    >
    Make Users
    >

    View full-size slide

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

    View full-size slide

  431. sqitch deploy
    Deploying changes to db:pg: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 db:pg:flipr_test
    * appschema .. ok
    * users ...... ok
    Verify successful
    >

    View full-size slide

  432. 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 full-size slide

  433. 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 full-size slide

  434. 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 full-size slide

  435. 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 full-size slide

  436. SET client_min_messages TO warning;
    CREATE EXTENSION IF NOT EXISTS pgtap;
    RESET client_min_messages;
    BEGIN;
    SELECT no_plan();
    SET search_path TO flipr,public;
    SELECT has_table( 'users' );
    test/users.sql
    Columnist
    SELECT finish();
    ROLLBACK;

    View full-size slide

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

    View full-size slide

  438. 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 full-size slide

  439. 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 full-size slide

  440. MOAR Deploy
    >

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  443. -- Deploy flipr:users to pg
    -- 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 full-size slide

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

    View full-size slide

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

    View full-size slide

  446. -- Verify flipr:users on pg
    BEGIN;
    SELECT nickname
    FROM flipr.users
    WHERE FALSE;
    COMMIT;
    verify/users.sq
    verify/users.sql

    View full-size slide

  447. -- Verify flipr:users on pg
    BEGIN;
    SELECT nickname
    FROM flipr.users
    WHERE FALSE;
    COMMIT;
    , password, timestamp
    verify/users.sq
    verify/users.sql

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  454. Sqitch Tags
    antisocial network

    View full-size slide

  455. Sqitch Tags
    Start with @
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  462. 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 full-size slide

  463. Specifying Changes
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  468. 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 full-size slide

  469. 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 full-size slide

  470. 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 users
    antisocial network

    View full-size slide

  471. > sqitch revert --to @HEAD^ -y
    Reverting changes to appschema from db:pg:flipr_test
    - users .. ok
    >
    Whither Users
    >

    View full-size slide

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

    View full-size slide

  473. > sqitch revert --to @HEAD^ -y
    Reverting changes to appschema from db:pg:flipr_test
    - users .. ok
    >
    sqitch status
    # On database db:pg:flipr_test
    # Project: flipr
    # Change: 748346dfe73cf2af32a8b7088fd75ad8d7aecda3
    # Name: appschema
    # Deployed: 2016-01-26 15:39:31 -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 full-size slide

  474. Whither Users
    >

    View full-size slide

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

    View full-size slide

  476. sqitch deploy
    Deploying changes to db:pg:flipr_test
    + users .. ok
    >
    Back At It
    >

    View full-size slide

  477. sqitch deploy
    Deploying changes to db:pg:flipr_test
    + users .. ok
    >
    Back At It
    >
    Add

    View full-size slide

  478. sqitch deploy
    Deploying changes to db:pg: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 full-size slide

  479. sqitch deploy
    Deploying changes to db:pg: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 full-size slide

  480. sqitch deploy
    Deploying changes to db:pg: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 full-size slide

  481. SET search_path = public,tap;
    BEGIN;
    SELECT * FROM no_plan();
    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 full-size slide

  482. SET search_path = public,tap;
    BEGIN;
    SELECT * FROM no_plan();
    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 full-size slide

  483. 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 * FROM no_plan();
    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 full-size slide

  484. 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 * FROM no_plan();
    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 full-size slide

  485. 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 * FROM no_plan();
    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 full-size slide

  486. > 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 full-size slide

  487. > 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 full-size slide

  488. User Typography
    >

    View full-size slide

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

    View full-size slide

  490. -- Deploy flipr:users to pg
    -- 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 full-size slide

  491. -- Deploy flipr:users to pg
    -- 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 full-size slide

  492. -- Deploy flipr:users to pg
    -- 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 full-size slide

  493. -- Deploy flipr:users to pg
    -- 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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  498. sqitch rebase @HEAD^ -y
    Reverting changes to appschema from db:pg:flipr_test
    - users .. ok
    Deploying changes to db:pg: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 full-size slide

  499. sqitch rebase @HEAD^ -y
    Reverting changes to appschema from db:pg:flipr_test
    - users .. ok
    Deploying changes to db:pg: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 full-size slide

  500. Additives
    > git add .
    >

    View full-size slide

  501. Additives
    > git add .
    > git commit -am 'Add users table.'
    [users 610b318] Add users table.
    5 files changed, 59 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 full-size slide

  502. Pushers
    > git push --set-upstream origin users
    Counting objects: 17, done.
    Delta compression using up to 4 threads.
    Compressing objects: 100% (11/11), done.
    Writing objects: 100% (11/11), 1.53 KiB, done.
    Total 11 (delta 1), reused 0 (delta 0)
    To ../flipr-remote
    * [new branch] users -> users
    Branch users set up to track remote branch users
    from origin.
    >

    View full-size slide

  503. Pushers
    > git push --set-upstream origin users
    Counting objects: 17, done.
    Delta compression using up to 4 threads.
    Compressing objects: 100% (11/11), done.
    Writing objects: 100% (11/11), 1.53 KiB, done.
    Total 11 (delta 1), reused 0 (delta 0)
    To ../flipr-remote
    * [new branch] users -> users
    Branch users set up to track remote branch users
    from origin.
    >

    View full-size slide

  504. Pushers
    > git push --set-upstream origin users
    Counting objects: 17, done.
    Delta compression using up to 4 threads.
    Compressing objects: 100% (11/11), done.
    Writing objects: 100% (11/11), 1.53 KiB, done.
    Total 11 (delta 1), reused 0 (delta 0)
    To ../flipr-remote
    * [new branch] users -> users
    Branch users set up to track remote branch users
    from origin.
    >

    View full-size slide

  505. Wash, Rinse, Repeat
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  512. Wash, Rinse, Repeat
    Add simple failing 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 full-size slide

  513. Caution: Hard Reset
    Ahead

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  518. 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 full-size slide

  519. 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 full-size slide

  520. Time to Work!
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  525. 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 full-size slide

  526. 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 full-size slide

  527. 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 full-size slide

  528. antisocial network
    Flip Out

    View full-size slide

  529. antisocial network
    Flip Out
    Create flips branch

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  536. 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 full-size slide

  537. Functional Testing
    >

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  541. test/insert_use
    test/insert_user.sql

    View full-size slide

  542. 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 full-size slide

  543. 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 full-size slide

  544. 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;
    -- Plan the tests.
    BEGIN;
    SELECT plan(11);

    View full-size slide

  545. 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;
    -- Plan the tests.
    BEGIN;
    SELECT plan(11);

    View full-size slide

  546. test/insert_use
    SELECT plan(11);

    View full-size slide

  547. 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 full-size slide

  548. 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 full-size slide

  549. 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 full-size slide

  550. 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 full-size slide

  551. 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 full-size slide

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

    View full-size slide

  553. 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 full-size slide

  554. 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 full-size slide

  555. 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 full-size slide

  556. 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 full-size slide

  557. 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 full-size slide

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

    View full-size slide

  559. 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 full-size slide

  560. 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 full-size slide

  561. 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 full-size slide

  562. 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 full-size slide

  563. 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 full-size slide

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

    View full-size slide

  565. 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 full-size slide

  566. 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 full-size slide

  567. 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 full-size slide

  568. 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 full-size slide

  569. 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 full-size slide

  570. 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 full-size slide

  571. 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 full-size slide

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

    View full-size slide

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

    View full-size slide

  574. deploy/insert_u
    deploy/insert_user.sql
    -- Deploy flipr:insert_user to pg
    -- requires: appschema
    -- requires: users
    BEGIN;
    -- XXX Add DDLs here.
    COMMIT;

    View full-size slide

  575. deploy/insert_u
    deploy/insert_user.sql
    -- Deploy flipr:insert_user to pg
    -- requires: appschema
    -- requires: users
    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 full-size slide

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

    View full-size slide

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

    View full-size slide

  578. revert/insert_u
    revert/insert_user.sql
    -- Revert flipr:insert_user to pg
    BEGIN;
    COMMIT;
    -- XXX Add DDLs here.

    View full-size slide

  579. revert/insert_u
    revert/insert_user.sql
    -- Revert flipr:insert_user to pg
    BEGIN;
    COMMIT;
    DROP FUNCTION flipr.insert_user(TEXT, TEXT);

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  582. verify/insert_u
    verify/insert_user.sql
    -- Verify flipr:insert_user to pg
    BEGIN;
    -- XXX Add DDLs here.
    ROLLBACK;

    View full-size slide

  583. verify/insert_u
    verify/insert_user.sql
    -- Verify flipr:insert_user to pg
    BEGIN;
    SELECT has_function_privilege(
    'flipr.insert_user(text, text)',
    'execute'
    );
    ROLLBACK;

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  586. We Good?
    emacs verify/insert_user.sql
    >
    >
    pg_prove -d flipr_test test/*.sql
    test/appschema.sql .... ok
    test/insert_user.sql .. 1/11
    # Failed test 1: "Function insert_user() should exis
    # Failed test 2: "Function insert_user(text, text) s
    # Failed test 3: "Function insert_user(text, text) s
    # Function insert_user(text, text) does not exis
    # Failed test 4: "Function insert_user(text, text) s
    # Function insert_user(text, text) does not exis
    # Failed test 6: "Insert a user"
    # died: 42883: function insert_user(unknown,
    # Failed test 7: "The user should have been inserted
    # have: NULL
    # want: (theory,acbd18db4cc2f85cedef654fccc4

    View full-size slide

  587. We Good?
    emacs verify/insert_user.sql
    >
    >
    pg_prove -d flipr_test test/*.sql
    test/appschema.sql .... ok
    test/insert_user.sql .. 1/11
    # Failed test 1: "Function insert_user() should exis
    # Failed test 2: "Function insert_user(text, text) s
    # Failed test 3: "Function insert_user(text, text) s
    # Function insert_user(text, text) does not exis
    # Failed test 4: "Function insert_user(text, text) s
    # Function insert_user(text, text) does not exis
    # Failed test 6: "Insert a user"
    # died: 42883: function insert_user(unknown,
    # Failed test 7: "The user should have been inserted
    # have: NULL
    # want: (theory,acbd18db4cc2f85cedef654fccc4
    Yay!

    View full-size slide

  588. We Good?
    test/insert_user.sql
    Failed tests: 1-11
    Files=3, Tests=27, 0 wallclock secs
    Result: FAIL
    >

    View full-size slide

  589. We Good?
    test/insert_user.sql
    Failed tests: 1-11
    Files=3, Tests=27, 0 wallclock secs
    Result: FAIL
    > sqitch deploy
    Deploying changes to db:pg:flipr_test
    + insert_user .. ok
    >

    View full-size slide

  590. We Good?
    test/insert_user.sql
    Failed tests: 1-11
    Files=3, Tests=27, 0 wallclock secs
    Result: FAIL
    >
    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
    >
    sqitch deploy
    Deploying changes to db:pg:flipr_test
    + insert_user .. ok
    >

    View full-size slide

  591. git add .
    > git commit -m 'Add `insert_user()`.'
    [userfuncs 40eabe1] Add `insert_user()`.
    6 files changed, 101 insertions(+)
    create mode 100644 -n
    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 full-size slide

  592. Push It Real Good…
    >

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  596. Reset, Mes Amis!
    antisocial network

    View full-size slide

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

    View full-size slide

  598. Reset, Mes Amis!
    git checkout users
    git revert -y
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  602. Reset, Mes Amis!
    git checkout users
    git revert -y
    git reset --hard upstream/users
    git checkout -b userfuncs
    git reset --hard upstream/insert_user
    git deploy
    antisocial network

    View full-size slide

  603. antisocial network
    None Shall Pass

    View full-size slide

  604. antisocial network
    None Shall Pass
    Create change_pass()

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  611. 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 full-size slide

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

    View full-size slide

  613. > sqitch revert -y
    > git reset --hard upstream/change_pass
    HEAD is now at 048017a Add `change_pass()`.
    > git checkout master
    >
    Resets
    >

    View full-size slide

  614. > sqitch revert -y
    > git reset --hard upstream/change_pass
    HEAD is now at 048017a Add `change_pass()`.
    >
    git reset --hard upstream/appschema
    HEAD is now at e46bdf9 Add appschema test.
    >
    git checkout master
    >
    Resets
    >

    View full-size slide

  615. > sqitch revert -y
    > git reset --hard upstream/change_pass
    HEAD is now at 048017a Add `change_pass()`.
    >
    git reset --hard upstream/appschema
    HEAD is now at e46bdf9 Add appschema test.
    >
    git checkout master
    >
    Resets
    >
    Known good.

    View full-size slide

  616. > sqitch revert -y
    > git reset --hard upstream/change_pass
    HEAD is now at 048017a Add `change_pass()`.
    >
    git reset --hard upstream/appschema
    HEAD is now at e46bdf9 Add appschema test.
    >
    git checkout master
    >
    Resets
    >
    Known good.
    sqitch deploy

    View full-size slide

  617. Mergers and Acquisitions
    >

    View full-size slide

  618. git merge users
    Updating d72aeb4..c343b84
    Fast-forward
    deploy/users.sql | 13 +++++++++++++
    revert/users.sql | 7 +++++++
    sqitch.plan | 1 +
    test/users.sql | 29 +++++++++++++++++++++++++++++
    verify/users.sql | 9 +++++++++
    5 files changed, 59 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 full-size slide

  619. git merge users
    Updating d72aeb4..c343b84
    Fast-forward
    deploy/users.sql | 13 +++++++++++++
    revert/users.sql | 7 +++++++
    sqitch.plan | 1 +
    test/users.sql | 29 +++++++++++++++++++++++++++++
    verify/users.sql | 9 +++++++++
    5 files changed, 59 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 full-size slide

  620. Mergers and Acquisitions
    >

    View full-size slide

  621. > git merge flips
    Updating c343b84..d6f4ee2
    Fast-forward
    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 full-size slide

  622. > git merge flips
    Updating c343b84..d6f4ee2
    Fast-forward
    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 full-size slide

  623. Mergers and Acquisitions
    >

    View full-size slide

  624. > git merge userfuncs
    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 full-size slide

  625. > git merge userfuncs
    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 full-size slide

  626. > git merge userfuncs
    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 full-size slide

  627. > git merge userfuncs
    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 full-size slide

  628. Back in Time…
    >

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  631. Branching Out
    antisocial network

    View full-size slide

  632. Branching Out
    users branched from master
    antisocial network

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  636. 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 full-size slide

  637. Backbrancher
    master
    A

    View full-size slide

  638. Backbrancher
    master
    users
    A
    branch

    View full-size slide

  639. Backbrancher
    master
    users
    A
    B
    Add
    users
    table
    branch

    View full-size slide

  640. Backbrancher
    master
    users
    A
    B
    flips
    branch
    branch

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  657. sqitch revert -y
    > git reset --hard HEAD
    HEAD is now at d6f4ee2 Add flips table.
    >
    Reset
    >

    View full-size slide

  658. sqitch revert -y
    > git reset --hard HEAD
    HEAD is now at d6f4ee2 Add flips table.
    >
    Reset
    >
    Use with
    care!

    View full-size slide

  659. sqitch revert -y
    > git reset --hard HEAD
    HEAD is now at d6f4ee2 Add flips table.
    >
    Reset
    >

    View full-size slide

  660. sqitch revert -y
    > git reset --hard HEAD
    HEAD is now at d6f4ee2 Add flips table.
    >
    Reset
    >
    git checkout userfuncs
    Switched to branch 'userfuncs'
    >

    View full-size slide

  661. sqitch revert -y
    > git reset --hard HEAD
    HEAD is now at d6f4ee2 Add flips table.
    >
    Reset
    >
    git checkout userfuncs
    Switched to branch 'userfuncs'
    > sqitch deploy
    >

    View full-size slide

  662. > 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:
    /Users/david/Desktop/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 full-size slide

  663. > 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:
    /Users/david/Desktop/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 full-size slide