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:

github: http://github.com/twada/twowaysql/tree/master

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.

Author(s)

Takuto Wada (takuto.wada at gmail dot com)

Contributors

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