【IT168 技术】随着时间的推移，管理基于SQL的脚本简直成了梦魇。Rails则通过ActiveRecord Migration解决了这个问题。Sam Livingston-Gray编写了一个独立的Ruby小工具，来生成层次化的迁移脚本。因为SQL脚本通常都比较繁复冗赘，因此Sam将SQL和Ruby相结合创建了SQrbL。
你可以这样来编写脚本：
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
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
于是SQrbL会生成如下文件：
/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
/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
例如all_up.sql中的SQL语句便是：
-- 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
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
目前，SQrbL还仅能通过insert_into来简化INSERT语句。
尽管对于已经使用ActiveRecord Migration的开发者来说，并没有什么理由再去使用SQrbL，然而它依然适合于那些寻找快速且简单的独立工具的人们。当前SQrbL的版本是0.1.3，尚缺乏一套适合的SQL DSL。
