Fry-IT

CreateAlterPatch.py - A script for creating ALTER TABLE SQL code

By: Peter Bengtsson, 24th of April 2007

24^th of April 2007

I write a lot of SQL code and don't use a ORM (Object Relationship Mapper) and during the course of developing an application I often find the need to alter the design of tables but I don't want to ruin my current development environment by recreating the tables from blank. So, I write a lot of ALTER TABLE ... SQL code. Now, with this new little script I can write those files much faster simply by using CreateAlterPatch, a command line script for generating SQL code.

Code explains it best. Here's how to get the help message up:

 peterbe@trillian:~ $ CreateAlterPatch --help
 USAGE: /home/peterbe/bin/CreateAlterPatch [options]
 Spits out SQL for alterting a table.

   -c, --cli             start interactive cli
   -t, --tablename=      table name to change
   -f, --fieldname=      field/column name to alter
   -a, --action=         action to take (default is ADD)
   -T, --type=           type of new field if action is to add
   -l, --length=         length of field if applicable
   -d, --default=        default value if appliable
   -r, --references=     table to references (if adding an int)
   -n, --null            ok with null values
   -h, --help            this help message

 version:  0.1.0
 (c) Peter Bengtsson, peter@fry-it.com, Fry-IT

You can either create the SQL code interatively with the --cli option like this:

 peterbe@trillian:~ $ CreateAlterPatch --cli
 > Create Alter Patch
 > Table: expenses
 > Field/column: age
 > Action [ADD|drop]: 
 > Type: int 
 > References: 
 > Default: 10
 ---------- Cut here ----------------------------------------
 BEGIN;

 ALTER TABLE expenses
   ADD age INT;

 ALTER TABLE expenses
   ALTER age SET DEFAULT 10;

 UPDATE expenses
   SET age = 10
   WHERE age IS NULL;

 ALTER TABLE expenses
   ALTER age SET NOT NULL;

 COMMIT;

Or you can call it with options to generate the SQL directly:

 peterbe@trillian:~ $ CreateAlterPatch --tablename=expenses \
 --fieldname=comment -a add --type=varchar --length=250
 BEGIN;

 ALTER TABLE expenses
   ADD comment VARCHAR(250);

 ALTER TABLE expenses
   ALTER comment SET DEFAULT '';

 UPDATE expenses
   SET comment = ''
   WHERE comment IS NULL;

 ALTER TABLE expenses
   ALTER comment SET NOT NULL;

 COMMIT;

And of course you can direct this into a file and save it so that you can apply it with psql (the command line interface to PostgreSQL):

 peterbe@trillian:~ $ CreateAlterPatch -t users -f age -a drop > drop_age_patch.sql
 peterbe@trillian:~ $ psql mydatabase -f drop_age_patch.sql

It's far from feature complete but does all the things I'm personally so tired of typing. Actually most of it is copy and pasting from old patch files but that too takes unnecessarily long time.

Comment