[ANN] SqlStatement 2.0 -- Generate complex SQL statementsprogrammatically

Discussion in 'Ruby' started by Ken Bloom, Jan 20, 2008.

  1. Ken Bloom

    Ken Bloom Guest

    sqlstatement - Generate complex SQL statements programmatically
    ===============================================================

    The main goal of this library is to be able to construct an SQL statement
    from "slices" that concern different aspects of the final query (perhaps
    in different places in your code) and then combine them all together into
    one statement easily.

    Another important goal of this library is to give some consistent Ruby
    syntax to three statements (INSERT, SELECT, and UPDATE) that seem to have
    different enough syntax that one has two write different code to generate
    each kind of statement.

    I use my SQL database (specifically MySQL) largely as a bulk data
    processing engine, by doing INSERT…SELECT or CREATE TABLE…SELECT
    statements. This library is intended to make that kind of coding easier.
    I expect that Object Relational mappers (such as ActiveRecord) are more
    useful for most people, who are performing queries and inserting/updating/
    querying for individual records. I have nevertheless added INSERT…VALUES
    statements, and will add other statements soon, for consistency.

    This library is inspired by CLSQL for Common LISP, or SchemeQL for
    Scheme, although it is very different from these two libraries. Scheme
    and LISP‘s use of s-expressions make it very easy to construct an entire
    sublanguage for the WHERE clause, simply by list parsing. The Criteria
    library for Ruby has attempted this, but in a more limited manner than
    SchemeQL or CLSQL. My library aims to cover much of the functionality in
    these libraries.

    This library doesn‘t try to abstract out the limitations of your DBMS,
    and I think that the SQL it uses should be fairly portable, in large
    measure because it hasn‘t attempted to deal with serious CREATE TABLE
    statements, where a lot of syntax concerning types, keys and sequences is
    much more variable.

    This library can be downloaded from rubyforge.org/projects/sqlstatement

    Changes in this release
    =======================
    * Vastly redesign the DSL. The methods for adding components to an
    SQL statement have all been renamed, and internal state is no longer
    directly exposed to the outside. The interface is now much more intutive
    and consistent. Please read the documentation to find out how this
    version of the library works.
    * Left joins have been added
    * There‘s no more SelectParts class — the corresponding semantics
    have been built directly into the Select class.
    * Select.new do |s| … end syntax has been added
    * Select statements now remember the order of fields and tables. This
    makes the next two changes feasible.
    o Support for Mysql‘s STRAIGHT_JOIN modifier has been added
    o Unit tests have been added

    Example
    =======
    If we wanted to generate SQL code similar to

    SELECT `value1`,`value2`
    FROM `jointable`,`dictionary1`,`dictionary2`
    WHERE `jointable`.`id1`=`dictionary1`.`id` AND
    `jointable`.`id2`=`dictionary2`.`id`

    we could generate it as follows:

    def foo(x)
    Select.new do |s|
    s.field :"value#{x}"
    s.table :"dictionary#{x}"
    s.condition string_func("`jointable`.`id#{x}`=`dictionary#{x}
    `.`id`")
    end
    end

    stmt=Select.new
    stmt.table :jointable
    (1..2).each do |x|
    stmt << foo(x)
    end
    dbh.execute(stmt)

    or like this:
    stmt=Select.new do |s|
    s.table :jointable
    [1,2].each do |num|
    s.field "value#{num}".dbid
    s.table "dictionary#{num}".dbid
    s.condition string_func("`jointable`.`id#{num}`=`dictionary#{num}
    `.`id`")
    end
    end

    dbh.execute stmt


    --
    Ken (Chanoch) Bloom. PhD candidate. Linguistic Cognition Laboratory.
    Department of Computer Science. Illinois Institute of Technology.
    http://www.iit.edu/~kbloom1/
     
    Ken Bloom, Jan 20, 2008
    #1
    1. Advertising

  2. Ken Bloom

    Ken Bloom Guest

    On Sun, 20 Jan 2008 11:40:31 -0600, Ken Bloom wrote:

    > sqlstatement - Generate complex SQL statements programmatically
    > ===============================================================


    I forgot to mention that the documentation is online at
    http://sqlstatement.rubyforge.org/

    > Example
    > =======
    > If we wanted to generate SQL code similar to
    >
    > SELECT `value1`,`value2`
    > FROM `jointable`,`dictionary1`,`dictionary2` WHERE
    > `jointable`.`id1`=`dictionary1`.`id` AND
    > `jointable`.`id2`=`dictionary2`.`id`


    The examples actually generate slightly more verbose SQL code, using
    INNER JOIN instead of a comma to indicate a join. (This is to avoid
    precedence issues when performing LEFT JOINs).

    --Ken

    --
    Ken (Chanoch) Bloom. PhD candidate. Linguistic Cognition Laboratory.
    Department of Computer Science. Illinois Institute of Technology.
    http://www.iit.edu/~kbloom1/
     
    Ken Bloom, Jan 20, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. news.amnet.net.au
    Replies:
    1
    Views:
    581
    =?UTF-8?b?TMSByrtpZSBUZWNoaWU=?=
    Apr 13, 2004
  2. Stanimir Stamenkov
    Replies:
    2
    Views:
    749
    Stanimir Stamenkov
    Oct 25, 2005
  3. Robert Mark Bram
    Replies:
    0
    Views:
    686
    Robert Mark Bram
    Feb 4, 2007
  4. Harlan Messinger
    Replies:
    2
    Views:
    2,304
    John Bell
    Mar 28, 2010
  5. Ken Bloom
    Replies:
    3
    Views:
    213
Loading...

Share This Page