"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

Schema Design 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 +Owner(Table) Design +S_Owner(Sequence) +Car(Table) +S_Car(Sequence) +Index on LastName +FK Constraint (Owner) +Comments +Index on VIN +Location +Comments +CreationDate +CustomerCurrentLocation +RegistrationType +FK OwnerType (View) +PurchasedDate -Rename Car to Vehicle +OwnerType(Table) -Remove Owner +S_OwnerType(Sequence) -Remove RegistrationType +FK VehicleRegistration +VehicleOwner(Table) +S_VehicleOwner(Sequence) +VehicleRegistration(Table) +S_VehicleRegistration(Sequence) +EventType(Table) +S_EventType(Sequence) +Accident(Table) +S_Accident(Sequence) +VehicleType (Table) +Comments +S_VehicleType (Sequence) +RegistrationType(Table) +FK Constraint (Vehicle) +Data (Production) +S_RegistrationType(Sequence) -Rename Accident to Event +Index on Name +FK Constraint (EventType) -EventType

Practices Schema Design Pairing Configuration Management Sandboxes Behavior Driven Development Continuous Integration Deployment Refactoring Pairin g Fosters knowledge sharing 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 Source Continuous Control Integration Check out Check in Check out Developer Developers Uses Uses DB Server DB Server Integrate more than once a day http://martinfowler.com/articles/continuousIntegration.html Continuous Integration Check in application code and database migration scripts Local dev environment Central integration environment Migration Migration scripts PROD scripts UAT QA ANT Continuous Maven Artifacts Integration Engine Make Source War War PROD Rake Control UAT Jar Jar QA Exe Environment Exe Dev DLL DLL DB Apply Integration Check out migration Database and build scripts

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 Other Other Your Applications Applications Application You Don't You Know About Know About Persistence Your Other Frameworks Database Databases Data Data Imports Extracts Data Data File File 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 Merge Columns Column Drop Merge Tables Table Drop View Move Column Introduce Calculated Rename Column Column Rename Table Introduce Surrogate Key Rename View Replace One-to-Many With Replace Column Associative Table Replace Large Object (LOB) Replace Surrogate Key With Table With Natural Key Split Column Split Table Data Quality Refactoring Add Lookup Table Introduce Column Apply Standard Codes Constraint Apply Standard Type Introduce Common Format Consolidate Key Strategy Introduce Default Value Drop Column Constraint Make Column Non- Nullable Drop Default Value Move Data Drop Non-Nullable Constraint Replace Type Code With Property Flags