BT

Simplify SQL Migration Scripts with SQrbL

by Sebastien Auvray on Aug 24, 2009 |

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 Sqrbl

Sqrbl.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_widgets

INSERT INTO new_widgets (
    name,
    part_num,
    note
)
SELECT
    widget_name AS name,
    CONCAT("X_", part_number) AS part_num,
    "Imported from old_widgets" AS note
FROM 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.

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss

Educational Content

General Feedback
Bugs
Advertising
Editorial
InfoQ.com and all content copyright © 2006-2014 C4Media Inc. InfoQ.com hosted at Contegix, the best ISP we've ever worked with.
Privacy policy
BT