Co-author of "Refactoring Databases:Evolutionary Database Development"
Pramod Sadalage is the co-author of the 2007 Jolt Productivity Award winning"Refactoring Databases: Evolutionary Database Development", a Martin Fowler
signature series book and author of "Recipes for Continuous Database
Integration". Pramod works as a DBA and developer at ThoughtWorks. He works on
large custom-developed applications that use agile methodologies. While on these
projects, he pioneered the practices and processes of agility in the database.
Pramod writes and speaks about these concepts and practices to help those
interested in using agile practices on databases. He has written and presented
about database administration on XP projects, the adoption of agile processes
with databases, and the impact of agile practices on database administration and
design. Pramod is the owner and founder of the agiledatabases Yahoo! group; he
moderates group discussions and helps group members learn about agile databases.
When he is not working, you can find him spending time with his wife Rupali and
daughter Arula, and trying to improve his running.
Presentations by Pramod Sadalage
Recipies for Continuous Database Integration
is your database being tested and integrated like your application code? do you have a database integration strategy? do you have a process to upgrade your database as you make subsequent releases? Make the database part of your Continuous Integration cycle and benefit from it.Database Refactoring
Evolve an existing database schema a small bit at a time to improve the quality of its design without changing its semantics.Books by Pramod Sadalage
by Pramod Sadalage, Scott Ambler
-
Refactoring has proven its value in a wide range of development projects,
helping software professionals improve system designs, maintainability,
extensibility, and performance. This book introduces powerful refactoring
techniques specifically designed for database systems.
Demonstrates how small changes to table structures, data,
stored procedures, and triggers can significantly enhance virtually any database
design -- without changing semantics. You will learn how to evolve database
schemas in step with source code -- and become far more effective in projects
relying on iterative, agile methodologies.
This comprehensive guide and reference covers every fundamental concept
underlying database refactoring, and helps you overcome the practical obstacles
to refactoring real-world databases. Start-to-finish examples walk you through
refactoring both simple standalone database applications and sophisticated
multi-application scenarios. You will master every task involved in refactoring
database schemas, and discover best practices for deploying refactorings in even
the most complex production environments.
The second half of this book systematically covers five major categories of
database refactorings. You will learn how to use refactoring to enhance database
structure, data quality, and referential integrity; and how to refactor both
architectures and methods. The book provides an extensive set of examples, built
with Oracle and Java, and easily adaptable for other languages, such as C#, C++,
or VB.NET.
Using this books techniques and examples, you can reduce waste, rework, risk,
and cost -- and build database systems capable of evolving smoothly, far into
the future. - Available At: http://www.amazon.com/exec/obidos/ASIN/0321293533/agiledba-2..
by Pramod Sadalage
-
Continuous Integration changed the way software is written. Why not extend and make the database part of the same Continuous Integration cycle so that you can see integrated results of your application as well as your database? Delivered in PDF format for quick and easy access, Recipes for Continuous Database Integration shows how the database can be brought under the preview of Continuous Integration, allowing all teams to integrate not only their application code, but also their database.
This Short Cut presents a recipe for each task that needs to be done. Each recipe starts with a statement of a problem, followed by an explanation and solution. It provides concrete ways and examples to implement ideas in Refactoring Databases: Evolutionary Database Design by Scott W Ambler and Pramod Sadalage. - Available At: http://www.informit.com/store/product.aspx?isbn=032150206X&r..
Agile DBA
My thoughts on evolutionary design in regards to databases. Database administration. Best Pratices, Database utilities and other things
Friday, June 6, 2008
So we version control/source control everything on our project.. code/data/artifacts/diagrams etc. yesterday I said why not extend it to my writings to everything I have. So I started this long journey of refactoring my folder layout and making a nice folder structure to hold all the things I have written about have other artifacts in the process of writing and moved them all to subversion, now all my example code and writings are all under version control that gets backed up everyday.... feels liberating
Wednesday, June 4, 2008
Japanese translation of "Refactoring Databases: Evolutionary Database Design" has been released, thanks to Yasuo Honda for the information. The Japanese version can be found here
Wednesday, May 21, 2008
I'm always on the lookout for better tool support to do database refactoring. Just noticed that LiquiBase has come out with a IntelliJ plugin to support database refactoring.
This is really cool and hopefully one of long list of tools that will support database refactoring in the future. so enjoy
Tuesday, May 6, 2008
We had a weird requirement on our project recently..
Find all the Rows in All the tables that do not comply with the Constraints that we have in development but not in QA environments
Best way to do this we thought was to write a SQL statement against the table for each column that was going to have a Foreign Key constrained column and find out what data was not right or did not match the constraint. For example: If we have a INVOICE table that has a ITEMID on it. I want to find all the rows in the INVOICE table that have a ITEMID that does not exist in the ITEM table. Writing this SQL for our 400+ tables database was going to be huge task.
Oracles (or for that matter any databases metadata) metadata to the rescue and we ended up writing a SQL that would generate our above SQL.
here is the SQL that generated the above SQL
SELECT 'SELECT '''||table_name||'-'||column_name||''', count(*) FROM '||
table_name|| ' WHERE not exists (select 1 from '|| remote_table ||' where '||
remote_table||'.'||remote_column||' = '||table_name||'.'||column_name||') AND '
||table_name||'.'||column_name||' IS NOT NULL
UNION ALL'
FROM (
SELECT a.table_name,
column_name,
( SELECT table_name FROM user_constraints
WHERE constraint_name = a.R_CONSTRAINT_NAME) remote_table,
( SELECT column_name FROM user_cons_columns
WHERE constraint_name = a.R_CONSTRAINT_NAME) remote_column
FROM user_constraints a, user_cons_columns b
WHERE a.constraint_name = b.constraint_name
AND a.constraint_type = 'R' )
This SQL generates SQL that when run will give us data about tables that do not match our constraints requirements. If you have a CUSTOMER table which has CUSTOMERTYPEID and STATUSID on it, then the SQL generated would be.
SELECT 'CUSTOMER-CUSTOMERTYPEID', COUNT(*) FROM CUSTOMER WHERE NOT EXISTS
(SELECT 1 FROM CUSTOMERTYPE WHERE
CUSTOMERTYPE.CUSTOMERTYPEID = CUSTOMER.CUSTOMERTYPEID)
AND CUSTOMER.CUSTOMERTYPEID IS NOT NULL
UNION ALL
SELECT 'CUSTOMER-STATUSID', COUNT(*) FROM CUSTOMER WHERE NOT EXISTS
(SELECT 1 FROM STATUS WHERE
STATUS.STATUSID = CUSTOMER.STATUSID)
AND CUSTOMER.STATUSID IS NOT NULL
Once the above SQL is run, the results will show us data that does not match the constraints we want to introduce into the QA environments.
Thursday, April 17, 2008
When doing Performance Testing or running Unit/Functional tests on a database, there is a need to periodically get the database to a known state, so that the tests behave in a predictable way and to get rid of all the data created by the tests. Some of the ways to get a clean database are.
Using Scripts: Recreate the database using scripts, the same scripts that are used in development environment.
Using DB Backup: Especially when the database (and the data set) is large (using the scripts approach above will be slow) is to make a backup of the database in its pristine state and then run the tests, once the tests are done running, restore the database with the backup that was done before the tests corrupted the data.
Using Virtual Machine: The DB backup approach can be improved by using Virtual Machine (VM). Setup a VM and run the database server inside the VM, get the database and data so that the tests can run. Now make a image of the VM and run the tests, when the tests are done all that needs to be done is to restore the image of the VM.
