"Agile Database Development" Presented by: Pramod - - PDF document

AT11

Class 6/9/2011 4:00:00 PM

"Agile Database Development"

Presented by: Pramod Sadalage ThoughtWorks Inc

Brought to you by:

330 Corporate Way, Suite 300, Orange Park, FL 32073 888‐268‐8770 ∙ 904‐278‐0524 ∙ sqeinfo@sqe.com ∙ www.sqe.com

Pramod Sadalage

Pramod Sadalage works as a data architect on large custom applications development—data warehouses—built using agile methodologies. Pramod writes about these concepts to make it easy for everyone to use agile methods with databases and doing database administration on agile projects. Pramod has worked in small teams, very large teams, with NoSQL databases, and really large RDBMS databases. He is the co-author of Refactoring Databases: Evolutionary Database Design (a Jolt-Productivity award winning book, with Scott Ambler), Recipes for Continuous Database Integration, and Behavior Driven Database Development.

Agile Database Development

Pramod Sadalage ThoughtWorks Inc.

Simple rules

ask when you have a question download slides and code www.sadalage.com/adp2011.pdf www.sadalage.com/adp2011.zip

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring

Is driven by stories/features

• I should be able to Lookup a Car
• I should be able to see Accidents reported against a

Car

• I should be able to Lookup a Truck
• I should be able to see Car/Trunk Make, Model and

Year

• I should be able to Lookup Owners of a Car/Truck
• I should be able to see when Car/Truck was first

sold

• I should be able to Lookup a Vehicle
• I should be able to see Odometer reading reported
• I should be able to see how Vehicle was used
• I should be able to see all Owners this Vehicle had

Schema Design

+Car(Table) +S_Car(Sequence) +FK Constraint (Owner) +Owner(Table) +S_Owner(Sequence) +Accident(Table) +S_Accident(Sequence) +CustomerCurrentLocation (View) +VehicleType (Table) +S_VehicleType (Sequence) +Data (Production) +Index on VIN +RegistrationType +PurchasedDate +Index on Name +Index on LastName +Location +CreationDate

• Rename Car to Vehicle

+FK Constraint (Vehicle) +Comments +Comments +Comments +OwnerType(Table) +S_OwnerType(Sequence) +FK OwnerType +VehicleOwner(Table) +S_VehicleOwner(Sequence)

• Remove Owner
• Rename Accident to Event

+EventType(Table) +S_EventType(Sequence) +FK Constraint (EventType)

• EventType

+RegistrationType(Table) +S_RegistrationType(Sequence)

• Remove RegistrationType

+FK VehicleRegistration +VehicleRegistration(Table) +S_VehicleRegistration(Sequence)

Schema

Design

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring Fosters knowledge sharing

Pairin g

Breaks down silos and fiefdoms Allows continuous reviews Reduces staff loss risk Improves team dynamics Helps groom new team members

Pairing

Helps DBA understand the application Writing change scripts Writing database tests Production data migration Gain SQL tuning knowledge Aware of production data Understand performance implications

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring

Configuration Management

Allows common code ownership Everyone sees everything Only checked in code/DB is deployed Include models, DDL, DB code, Data Include configuration data Get any version of code and database Include migration scripts

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring

Sandboxes

Make joining project effortless Reduce waste (waiting time) Automate DBA Tasks Lets the DBA do more Improves productivity Keeps changes local Spinning up environments is easy

Sandboxes Sandboxes

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring

Behavior Driven Development

Like objects, database objects have behavior Over time we tend to rely on this behavior Encode behavior in tests Tests are written to design database object Tests are part of code, can be refactored Schema verification is hard to maintain Makes refactoring of databases easy

Behavior Driven Development

Lets Design a table with BDDD Here are the rules A “Vehicle” table with

• Unique VIN Number
• Model Year 2005 and above
• Model Name Not Null
• Make Not Null
• Miles driven

http://www.methodsandtools.com/archive/archive.php?id=78

BDDD

Tests would look like ShouldNotAllowDuplicateVIN ShouldAllowModelYear2010 ShouldNotAllowModelYear2004AndBelow ShouldNotAllowNullModelName ShouldNotAllowNullMake ShouldSaveMilesDriven ShouldSave2008VehicleWithModelAndMake

http://www.methodsandtools.com/archive/archive.php?id=78

BDDD

Table DDL would be

CREATE TABLE vehicle( id NUMBER(18) NOT NULL, vin VARCHAR2(32) NOT NULL, name VARCHAR2(32) NOT NULL, make VARCHAR2(32) NOT NULL, year NUMBER(4) NOT NULL, miles NUMBER(10) NULL, CONSTRAINT chk_vehicle_year_gt_2005 CHECK (year> 2004)); CREATE UNIQUE INDEX uidx_vehicle_vin ON vehicle(vin); ALTER TABLE VEHICLE ADD CONSTRAINT pk_vehicle PRIMARY KEY (id);

http://www.methodsandtools.com/archive/archive.php?id=78

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring

Continuous Integration

Reduce Integration pain Show current state of code/db to everyone Single place to get artifacts Yes that means the database schema and database Code and Data Faster feedback

Continuous Integration

Continuous Integration

Developer DB Server Continuous Integration Source Control DB Server Developers

Check in Check out Check out Uses Uses Integrate more than once a day http://martinfowler.com/articles/continuousIntegration.html

Continuous Integration

Integration Database

Central integration environment Local dev environment

ANT Maven Make Rake Continuous Integration Engine Source Control Artifacts

Migration scripts

PROD UAT QA Dev DB PROD UAT QA Environment

Check in application code and database migration scripts Check out and build

Migration scripts Apply migration scripts

War Jar Exe DLL War Jar Exe DLL

Continuous Integration

Every change to the database is a migration script Build Number Time Stamp Sequential Number Release Id Database should know its version

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring

Deployment

Any build deployed in any environment Deploy without manual intervention Should be handled like code deployment Branch code/db code Package the database scripts too The deployment script should know what scripts to run

Deployment

Deployment

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring

Refactoring

“Refactoring is a small change to your source code that improves its design without changing its semantics”

• -Martin Fowler

A database refactoring is a small change to your database schema (the DDL, data, and DB code) which improves its design without changing its semantics.

Refactoring

A database refactoring is a simple change to a database schema that improves its design while retaining both its behavioral and informational semantics. A database schema includes both structural aspects such as table and view definitions as well as functional aspects such as stored procedures and triggers.

Refactoring

Its Hard

Your Database Your Application Other Databases Other Applications You Know About Other Applications You Don't Know About Data File Data Extracts Data File Data Imports Persistence Frameworks Test Code

Refactoring Refactoring

Structural Change the table structure of your database

schema

Data Quality Improve and/or ensure the consistency and

usage of the values stored within the database

Architectural Improve the overall manner in which

external programs interact with a database

Method Apply refactoring to database code Transformations Change the semantics of your

database schema by adding new features

Structural Refactoring

Drop Column Drop Table Drop View Introduce Calculated Column Introduce Surrogate Key Replace One-to-Many With Associative Table Replace Surrogate Key With Natural Key Split Column Merge Columns Merge Tables Move Column Rename Column Rename Table Rename View Replace Large Object (LOB) With Table Replace Column Split Table

Data Quality Refactoring

Add Lookup Table Apply Standard Codes Apply Standard Type Consolidate Key Strategy Drop Column Constraint Drop Default Value Drop Non-Nullable Constraint Introduce Column Constraint Introduce Common Format Introduce Default Value Make Column Non- Nullable Move Data Replace Type Code With Property Flags

Referential Integrity Refactoring

Add Foreign Key Constraint Add Trigger For Calculated Column Drop Foreign Key Constraint Introduce Cascading Delete Introduce Hard Delete Introduce Soft Delete Introduce Trigger For History

Architectural Refactoring

Add CRUD Methods Add Mirror Table Add Read Method Encapsulate Table With View Introduce Calculation Method Introduce Index Introduce Read-Only Table Migrate Method From Database Migrate Method To Database Replace Method(s) With View Replace View With Method(s) Use Official Data Source

Method Refactoring

Add Parameter Parameterize Method Remove Parameter Rename Method Reorder Parameters Replace Parameter with Explicit Methods Consolidate Conditional Expression Decompose Conditional Extract Method Introduce Variable Remove Control Flag Remove Middle Man Rename Parameter Replace Literal with Table Lookup Replace Nested Conditional with Guard Clauses Split Temporary Variable Substitute Algorithm

Transformation

Insert Data Introduce New Column Introduce New Table Introduce View Update Data

Practices

Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring Thanks http://sadalage.com http://databaserefactoring.com @pramodsadalage