Porpoise is a database schema migration tool. It is ran via the command line. Scripting Porpoise is a breeze, so it can be integrated into most workflows.
Command Line Parameters
usage: Porpoise.groovy -D,--dry-run Outputs log information only. Does not run sql scripts -d,--dir Path to SQL script directory (Optional. Defaults to startup-directory) -F,--force-removals Indicates that removals SHOULD run. Otherwise, script removals are only noted. --no-exit Does not issue the System.exit command. Useful when embedding porpoise inside applications -p,--database-password Database Password (Optional) --post-apply-action Process to run after all scripts have been applied. WILL ONLY RUN IF NEW "UP" SCRIPTS HAVE BEEN APPLIED" -U,--url JDBC URL definition -u,--database-user Database user (Optional)
Porpoise will find all sql files in the specified directory and run any scripts that have not been previously applied to the database. Porpoise works with changesets. The -d argument indicates the root directory for the SQL scripts. Porpoise will traverse the one-level of subdirectories looking for sql files to run. This strategy is useful for seperating feature and bug migrations. The SQL files in the subdirectories will be ran in order.
Example SQL Script Directory
/SQL_Scripts /ChangeSet1 /001-Create-Person-Table.sql /002-Populate-PersonTable.sql /ChangeSet2 /001-Create-Jobs-Table.sql /002-Populate-Jobs.sql
The SQL Scripts should contain both the "up" and "down" sql statements. The sections are seperated by a "--down" comment. The SQL statements should be deliminated with semicolons (;).
create table person (id varchar(50)); --down drop table person;
Porpoise will output the status at the end of the migration. It will indicate scripts in each of the following states:
- Removed (if -F flag is passed)
- Changed since being applied (see below)
- Failed (see below)
- Require Applying (see below)
- Require Removal (see below)
Porpoise generates and maintains an MD5 signature for each script it applies. If the script has changed since it was applied, Porpoise will NOT apply or remove that script. It merely indicates that the script has changed. It is up to you to decide how to handle it.
If a script fails to execute, Porpoise will abort the process. The failed script may be in a half-applied state. You will be responsible for backing out any half-applied scripts. Sorry, such is life.
A script will end up in the "require applying" if a prior script failed. Since Porpoise aborted the remaining scripts.
A script may be in the "require removal" state if the -F flag was not set. Scripts may also end up in this state if a prior script failed.
Porpoise has been tested with the following databases:
But really, it is not difficult to add more database engines. Porpoise looks for a table called "PORP_SCHEMA_LOG". If this table does not exist, it attempts to create it. Database engines use different datatypes for clobs, timestamps, etc. If your database engine is not supported, please review the script and make a pull request. Otherwise, you might be able to create the table manually. Below is a sample create table statement. Modify the datatypes to match your database engines preference.
CREATE TABLE PORP_SCHEMA_LOG( ID varchar(50) NULL, CHANGESET varchar(500) NULL, SCRIPT_NAME varchar(500) NULL, MD5 varchar(32) NULL, DATE_APPLIED datetime NULL, UP_SCRIPT nvarchar(max) NULL, DOWN_SCRIPT nvarchar(max) NULL )
Post Apply Action
The --post-apply-action parameter allows you the ability to run a process if (and only if) SQL scripts have been applied. A comma separated list of changesets will be passed as the first argument.
The Email.groovy script in the plugins directory is a great way to alert your DBA's that some changesets are moving through the deploy pipeline.
usage: Email.groovy -c,--applied-changesets Comma separated list of changests -f,--from Email address for the "from" user -m,--message Additional Message -p,--port SMTP server port (Default 25) -s,--subject Subject of email -S,--server SMTP server address -t,--to Comma separated list of "to" addresses