geeksam/sqrbl | GitXplorer

== SQrbL: Making database conversions suck less since July 2009!

The source code for SQrbL can be found on GitHub at:

SQrbL's creator and primary author is Sam Livingston-Gray.

== DESCRIPTION:

SQrbL was created to help manage an extremely specific problem: managing SQL-based database conversions.

In essence, SQrbL is a tool for managing multiple SQL queries using Ruby. SQrbL borrows some terminology and ideas from ActiveRecord's schema migrations, but where ActiveRecord manages changes to your database schema over time, SQrbL was written to manage the process of transforming your data from one schema to another. (Of course, you could use SQrbL for the former case as well -- just use it to write DDL queries -- but ActiveRecord has better tools for figuring out which migrations have already been applied.)

===How It Works:

You describe the steps in your conversion in a SQrbL file. Each step can produce as much or as little SQL as you like. Each step has an "up" and a "down" part -- so you can do and undo each step as many times as you need to, until you get it just right. When you run your SQrbL file, it creates a tree of *.sql files containing the output from your conversion steps, one file per step. It also creates the combined files "all_up.sql" and "all_down.sql"; these contain all of your steps combined into one giant file so that, when Cutover Day arrives, you can copy/paste the whole thing into your SQL client and run it all at once.

===Why It Exists:

SQL is a fantastic DSL for describing queries. It's not bad at doing transformations, either. Unfortunately, SQL is, um... rather verbose. And, it lacks tools for reducing duplication -- if you have to run five similar queries that differ only in their parameters, you have to figure out how to use a prepared statement, but if you have to run five similar queries that differ in the field names they use, that's a bit more work. SQrbL lets you use SQL for the things SQL is good at, and Ruby for the other stuff.

===About the Name:

SQL.rb seemed a bit too pretentious, so I went with SQrbL -- as in, "You got Ruby in my SQL." I pronounce it "squirble" (rhymes with "squirrel"). The proper capitalization can be annoying to type, so I've aliased the SQrbL class as Sqrbl.

Note: While not quite a literal pronunciation, "Screwball" is an accepted alternate. Mostly 'cause it's funny.

== SYNOPSIS:

(Note that, in the following code sample, I'm using the convention that do/end blocks are used primarily for their side effects, and curly-brace blocks are used primarily for their return value.)

Sqrbl.conversion "Convert from old widgets to new widgets" do set_output_directory '/path/to/generated/sql'

helpers do
  def widget_import_note
    '"Imported from old_widgets"'
  end
end

group "Widgets" do
  step "Create widgets" do
    up do
      action "Migrate old_widgets" {
        <<-SQL
          #{
            insert_into("new_widgets", {
              :name     => 'widget_name',
              :part_num => 'CONCAT("X_", part_number)',
              :note     => widget_import_note,
            })
          }
          FROM old_widgets
        SQL
      }
    end

    down do
      action "Drop imported organizational contacts" {
        'DELETE FROM new_widgets WHERE note LIKE "Imported from old_widgets"'
      }
    end
  end
end

end

The above code sample describes a conversion with one step: moving the data in an old_widgets table to a new_widgets table. When run, this will generate a set of *.sql files in the directory /path/to/generated/sql.

== REQUIREMENTS:

Ruby. (SQrbL was written using MRI Ruby 1.8.6, and has not been tested using other versions.)

== INSTALL:

sudo gem install rasantiago-sqrbl --source http://gems.github.com

== LICENSE:

SQrbL is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/.

sqrbl

Commits

Update TODO

API change: s/migration/conversion/g

Update todo

Reformatting.

Added Step#helpers method for code cosmetics.

Fix requirements list in README.txt, add TODO.txt.

README