How can I include database changes (DDL patches, one-time data inserts, etc) in my build process?

Question

I run my build script and then I have to remember which of the database SQL and PL/SQL scripts to run each time I deploy my application. How can I include these patches in my build script? Or does everybody just run them manually? Currently I number my patches so I know the order to run them, but sometimes I have to check SVN history to know what number to start at.

I'm using PHP but can use Java in my solution to this problem.

Answer 1

liquibase might solve this problem for you which integrates into ant or maven but can be started from cmd line too

Answer 2

You should be saving your changes as scripts and putting them in source control like the rest of your code. Then you know what changes belong to what build and need to be promoted to prod.

Answer 3

Since you're using PHP phing/DbDeployTask would be a smart choice. For every DB table you will have a start file and a number of patches, e.g.:

001 user.sql
002 project.sql
501 user-AddColumnAvatar.sql

etc.