Evolutionary Database Design

50 %
50 %
Information about Evolutionary Database Design

Published on July 1, 2009

Author: asolntsev

Source: slideshare.net


Presentation makes overview of existing database change management tools

July 1, 2009 Evolutionary database design DevClub.eu, 30.06.2009 Andrei Solntsev

Agenda  begin » background » my experience » tools  end;

begin процесс разработки программного обеспечения Плановый («водопад») Эволюционный На раннем этапе • Допускать изменения • выявить требования, на всех стадиях • согласовать, • Но управлять изменениями • спроектировать, • согласовать и затем приступить к кодированию принципиальная позиция – минимизация изменений за счет максимально упростить обширной предварительной работы. возможность изменений

@author Martin Fowler 1999: Refactoring: Improving the Design of Existing Code http://www.amazon.com/exec/obidos/ASIN/0201485672 2000: Continuous Integration http://www.martinfowler.com/articles/continuousIntegration.html 2003: Evolutionary Database Design http://www.martinfowler.com/articles/evodb.html

@compare Рефакторинг базы данных сложнее, чем рефакторинг кода • Сохранение информационной семантики • Миграция данных • В отличие от обычного ПО, невозможно просто удалить старый код и заменить новым. • большое количество связей • период поддержки устаревшего кода

<img src=“book.pdf”/>

@experience My experience  PRIA  HireRight  HireRight 2.0 ?

PRIA Single DB environment Developers Product Manager Salespeople Production Production Testers Users Deployment is not a problem 

Multi-DB environment Developer #1 Tester #1 Salespeople Production Users Demo Production Developer #2 Tester #2 Deployment becomes a problem 

HireRight: deployment CVS DB ALTER TABLE man ADD COLUMN length Create index man_idx CREATE INDEX Installation women_id_idx scripts DROP TABLE raha CREATE TABLE money ( eek NUMBER Problems: ); • scripts reinstallation • DB recreation ALTER TABLE money ADD CONS • DB synchronization

Database Change Management An automated process is needed to make the process of upgrading out-of-date databases simple  We need a Database Change Management tool. My findings: Ruby migrations - http://www.oracle.com/technology/pub/articles/kern-rails-migrations.html RedGate SQL compare - http://www.red-gate.com/products/SQL_Compare/index.htm MySQL Workbench – Diff & Synchronization http://dev.mysql.com/tech-resources/articles/workbench_database_management.html Oracle Enterprise Manager (change management pack) DB Deploy - http://dbdeploy.com/ LiquiBase - http://www.liquibase.org/

Apache DDL Utils http://db.apache.org/ddlutils/ <database name="testdb"> <table name="author"> <column name="author_id" type="INTEGER" primaryKey="true" required="true"/> <column name="name" type="VARCHAR" size="50" required="true"/> <column name="organisation" type="VARCHAR" size="50" required="false"/> </table> <index name="book_isbn"> <index-column name="isbn"/> </index> </database> Based on XML, automatically generates SQL scripts to update DB.

Incremental Schema Changes  All database schemas can be thought of as a compilation of incremental changes made over time to accommodate new functionality  As updates are applied to a database, the changes will be recorded in a table similar to the following: Change Date 1_Create_Customer_Table.sql 4-15-07 2_Add_e-mail_address_column.sql 4-17-07 3_Add_fax_number_column.sql 4-18-07 4_Add_transaction_table.sql 4-21-07 5_Add_transaction_status_column.sql 4-24-07 6_Add_customer-transaction_view.sql 4-27-07

DBDeploy ThoughWorks.com 1. Go to directory with SQL files: • “1 create_customer_table.sql” • “2 add_customer_id_constraint.sql” • … • Run “ant” Output: [dbdeploy] dbdeploy 3.0-SNAPSHOT [dbdeploy] Reading change scripts from directory /tmp/dbdeploy/dbdeploy-3.0-SNAPSHOT/example... [dbdeploy] Changes currently applied to database: [dbdeploy] (none) [dbdeploy] Scripts available: [dbdeploy] 1, 2 [dbdeploy] To be applied: [dbdeploy] 1, 2 [dbdeploy] Generating undo scripts... [sql] Executing resource: /tmp/dbdeploy/dbdeploy-3.0-SNAPSHOT/example/output.sql [sql] 7 of 7 SQL statements executed successfully

DBDeploy options • Naming convention for delta scripts: NUMBER COMMENT.SQL • Re-execution – NOT POSSIBLE • Undo section – marked by comments: CREATE TABLE FOO ( FOO_ID INTEGER NOT NULL, FOO_VALUE VARCHAR(30) ); ALTER TABLE FOO ADD CONSTRAINT PK_FOO PRIMARY KEY (FOO_ID); --//@UNDO DROP TABLE FOO; http://dbdeploy.com/documentation/getting-started/rules-for-using-dbdeploy/

LiquiBase LiquiBase.org The main XML describing all delta scripts: <databaseChangeLog> <changeSet id="1" author="bob"> <createTable tableName="department"> <column name="id" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="name" type="varchar(50)"> <constraints nullable="false"/> </column> <column name="active" type="boolean" defaultValue="1"/> </createTable> </changeSet> </databaseChangeLog>

LiquiBase Run: liquibase --driver=com.mysql.jdbc.Driver --classpath=/path/to/classes --changeLogFile=com/example/db.changelog.xml --url="jdbc:mysql://localhost/example" --username=user --password=asdf migrate Also possible to run: • Ant script • Maven plugin • Built-in java code • Spring • Grails • Servlet Listener

LiquiBase options • Custom order of scripts (user-defined) • Re-execution – POSSIBLE (with attributes)

LiquiBase options And many other options: • DBMS • SQL checksum • context • long transaction • pre-conditions

Outcome My findings: RedGate SQL compare 1. commercial MySQL Workbench – Diff & Synchronization 2. RDBMS-specific 3. but probably good Oracle Enterprise Manager Ruby migrations - Seems to be cool, need to try - Written on Ruby DB Deploy - Written by authoritative people - Simple - Convection over configuration Written LiquiBase - More configuration than DBDeploy on - More options than DBDeploy Java - Works with any RDBMS - Eclipse plugin

end; Links: http://www.refactoring.com/ - Code refactoring (1999) http://databaserefactoring.com/ - DB refactoring (2003) Books: Рефакторинг баз данных: эволюционное проектирование. http://www.williamspublishing.com/Books/978-5-8459-1157-5.html

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

Evolutionary Database Design - Martin Fowler

Evolutionary Database Design. Over the last few years we've developed a number of techniques that allow a database design to evolve as an application develops.
Read more

Refactoring Databases: Evolutionary Database Design: Scott ...

Refactoring Databases: Evolutionary Database Design [Scott W. Ambler, Pramodkumar J. Sadalage] on Amazon.com. *FREE* shipping on qualifying offers. ...
Read more

Refactoring Databases, Evolutionary Database Design ...

Evolutionary Database Design Scott W. Ambler, Pramod J. Sadalage. ISBN: 978-0-3212-9353-4 384 Seiten Datum: März 2006 Sprache: Englisch
Read more

Refactoring Databases - Martin Fowler

Refactoring Databases . Evolutionary Database Design. ... This freed up the application developers to use evolutionary design in the code too.
Read more

Evolutionary database design | Schema Evolution

An approach for evolutionary database design is presented which tries to remedy some of the shortcomings of previous design methods. The approach ...
Read more

database refactoring

A collection of database refactoring patterns and database development practices to enable evolutionary database design. An essential practice to enable ...
Read more

Refactoring Databases: Evolutionary Database Design

Refactoring Databases: Evolutionary Database ... refactoring-databases-evolutionary-database-design?forum ... on database refactoring: http ...
Read more

Evolutionary database design. - Free Online Library

Free Online Library: Evolutionary database design.(analysis) by "Database and Network Journal"; Business Computers and office automation ...
Read more

Refactoring Databases: Evolutionary Database Design ...

Refactoring Databases: Evolutionary Database Design Paperback Addison-Wesley Signature: Amazon.de: Scott J. Ambler, Pramodkumar J. Sadalage: Fremdsprachige ...
Read more

Refactoring Databases: Evolutionary Database Design

Refactoring Databases: Evolutionary Database Design. by Scott W. Ambler and Pramod J. Sadalage. Addison Wesley Professional ISBN#: 0-321-29353-3
Read more