manielse's not so random finds on the web

Simplify SQL Migration Scripts with SQrbL

Posted by Sebastien Auvray on Aug 24, 2009 08:13 PM

Community

Ruby

Topics

Database Design

Tags

ActiveRecord ,

SQL ,

migration

Managing SQL-based script can become a nightmare with time. Rails solved this with ActiveRecord Migration. Sam Livingston-Gray wrote a small standalone Ruby tool to generate hierarchical migration script. Based on the fact that SQL scripts can become very verbose and duplication-prone, Sam started SQrbL which is a mix of SQL and Ruby.

You'll be writing such script:

include SqrblSqrbl.migration do    @output_directory='/path/to/generated/sql'    group "Widgets" do      step "Create widgets" do        up do                 helpers do            def widget_import_note                '"Imported from old_widgets"'            end          end                 action "Migrate old_widgets" do            <<-SQL              #{                insert_into("new_widgets", {                  :name     => 'widget_name',                  :part_num => 'CONCAT("X_", part_number)',                  :note     => widget_import_note,                })              }              FROM old_widgets            SQL          end        end        down do          action "Drop imported organizational contacts" do            'DELETE FROM new_widgets WHERE note LIKE "Imported from old_widgets"'          end        end      end    end          group 'Second Group' do      step 'Step one' do        up { write '-- Step one up' }        down { write '-- Step one down' }      end      step 'Step two' do        up { write '-- Step two up' }        down { write '-- Step two down'}      end    end              end

And SQrbL will produce the following files:

/path/to/generated/sql/up/1_widgets/1_create_widgets.sql/path/to/generated/sql/down/1_widgets/1_create_widgets.sql/path/to/generated/sql/up/2_second_group/1_step_one.sql/path/to/generated/sql/down/2_second_group/1_step_one.sql/path/to/generated/sql/up/2_second_group/2_step_two.sql/path/to/generated/sql/down/2_second_group/2_step_two.sql/path/to/generated/sql/all_up.sql/path/to/generated/sql/all_down.sql

For example all_up.sql is filled up with the SQL queries:

-- Migrate old_widgetsINSERT INTO new_widgets (    name,    part_num,    note)SELECT    widget_name AS name,    CONCAT("X_", part_number) AS part_num,    "Imported from old_widgets" AS noteFROM old_widgets-- Step one up-- Step two up

For the moment SQrbL only insert_into to simplify INSERT writing statements.

While the use of SQrbL might not look the best solution for people already using ActiveRecord Migration, it still might satisfy people looking for a quick simple standalone tool. For the moment SQrbL is in its 0.1.3 version and is lacking a proper SQL DSL.