Template Engine for SQL

=> ‘twowaysql’

What

TwoWaySQL is a Template Engine for SQL.

With TwoWaySQL, you can bind variables to SQL or modify SQL conditionally and run and preview TwoWaySQL-style SQL by tools like pgAdmin3, since the SQL is still valid. see more docs

Installing

(sudo) gem install twowaysql

The basics

see docs

Demonstration of usage

simple case


  # given SQL string with TwoWaySQL comments
  sql = "SELECT * FROM emp WHERE job = /*ctx[:job]*/'CLERK'"

  # parse the SQL to create template object
  template = TwoWaySQL::Template.parse(sql)

  # merge data with template
  merged = template.merge(:job => 'MANAGER')

  p merged.sql      #=> "SELECT * FROM emp WHERE job = ?"
  p merged.bound_variables          #=> ['MANAGER']

complex case


  # given SQL string with TwoWaySQL comments
  sql = <<-EOS
    SELECT * FROM emp
    /*BEGIN*/WHERE
      /*IF ctx[:job]*/ job = /*ctx[:job]*/'CLERK' /*END*/
      /*IF ctx[:deptno_list]*/ AND deptno IN /*ctx[:deptno_list]*/(20, 30) /*END*/
      /*IF ctx[:age]*/ AND age > /*ctx[:age]*/30 /*END*/
    /*END*/
    /*IF ctx[:order_by] */ ORDER BY /*$ctx[:order_by]*/id /*$ctx[:order]*/ASC /*END*/
  EOS


  # parse the SQL to create template object
  template = TwoWaySQL::Template.parse(sql)


  # merge data with template
  data = {
    :age => 35,
    :deptno_list => [10,20,30],
    :order_by => 'age',
    :order => 'DESC'
  }
  merged = template.merge(data)


  expected_sql = <<-EOS
    SELECT * FROM emp
    WHERE
      
       deptno IN (?, ?, ?) 
       AND age > ? 
    
     ORDER BY age DESC 
  EOS

  merged.sql == expected_sql      #=> true
  merged.bound_variables          #=> [10,20,30,35]


  # use merged SQL and variables with any O-R Mapper you like (ex. Sequel)
  require 'sequel'
  DB = Sequel.connect('postgres://user:pass@localhost:5432/mydb')
  rows = DB.fetch(merged.sql, *merged.bound_variables).all
  . . .

more examples

How to submit patches

You can fetch the source from:

git clone git://github.com/twada/twowaysql.git

Feel free to fork it and send me patches or pull requests

Build and test instructions

cd twowaysql
rake spec
rake install_gem

License

This code is free to use under the terms of the ASL license.

Links

Author(s)

Contributors

Takuto Wada, 3rd November 2008
Theme extended from Paul Battley