# SQL Generator library **URL:** \ **Category:** Ruby\ **Created:** [August 25, 2006, 5:07pm UTC](https://www.ruby-forum.com/t/sql-generator-library/66450 "2006-08-25T17:07:12Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\ **Post date:** [August 25, 2006, 5:07pm UTC](https://www.ruby-forum.com/t/sql-generator-library/66450/1 "2006-08-25T17:07:12Z")
I’m writing a library for generating fairly complicated SQL statements in a relatively clean way. The current version of the library can be found at [Linguistic Cognition Laboratory: About](http://lingcog.iit.edu/~bloom/sqlstatement.rb) and some sample code which is fairly representative of what I plan to use the library for is at [Linguistic Cognition Laboratory: About](http://lingcog.iit.edu/~bloom/newstuff.rb) I was curious if anybody had suggestions about ways they might make the library easier to use and/or more maintainable. –Ken B. ---
**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\ **Post date:** [August 25, 2006, 5:37pm UTC](https://www.ruby-forum.com/t/sql-generator-library/66450/2 "2006-08-25T17:37:17Z")
On 25.08.2006 17:00, Ken B. wrote: > I’m writing a library for generating fairly complicated SQL statements in > a relatively clean way. The current version of the library can be found at > [Linguistic Cognition Laboratory: About](http://lingcog.iit.edu/~bloom/sqlstatement.rb) and some sample code which > is fairly representative of what I plan to use the library for is at > [Linguistic Cognition Laboratory: About](http://lingcog.iit.edu/~bloom/newstuff.rb) > > I was curious if anybody had suggestions about ways they might make the > library easier to use and/or more maintainable. Some things you could do to make this more usable: - Spend more whitespace, especially blank lines, to mare the code more readable. - Add an introductory comment at the beginning, especially state the purpose of the lib - Add a simple example that demonstrates usage of the code Another general remark: SQL is extremely flexible, so any tool that generates SQL either has a GUI or restricts the output to a subset of SQL statements or both. Without the restriction and a GUI such a library can easily get as complex as SQL itself and in that case I’d probably rather write SQL directly unless I get something else from such a lib (for example database independence). My 0.02EUR Kind regards ``` robert ``` ---
**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\ **Post date:** [August 26, 2006, 3:02pm UTC](https://www.ruby-forum.com/t/sql-generator-library/66450/3 "2006-08-26T15:02:40Z")
Ken B. wrote: > 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. So you are basically constructing a complex filter condition or even a complete SQL statement via some form of object graph, correct? > 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. What about DELETE? Also, I’m not sure how you are able to create a consistent syntax for all of these as they perform different operations and need a differing set of inputs. > of s-expressions make it very easy to construct an entire sublanguage for > where a lot of syntax concerning types, keys and sequences is much more > variable. But it contains DDL and thus is not portable. With SQL you can only rely on DML being portable. And even then you will occasionally stumble into things you cannot do without DB specific SQL generation. Maybe you should plan for multi dialect SQL generation if you intend to distribute this to a wider audience. If people find it useful I am sure they will want to use it with their favorite brand of RDBMS… Cheers ``` robert ``` ---
**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\ **Post date:** [August 26, 2006, 1:38pm UTC](https://www.ruby-forum.com/t/sql-generator-library/66450/4 "2006-08-26T13:38:29Z")
On Fri, 25 Aug 2006 17:30:02 +0200, Robert K. wrote: > Some things you could do to make this more usable: > generates SQL either has a GUI or restricts the output to a subset of > SQL statements or both. Without the restriction and a GUI such a > library can easily get as complex as SQL itself and in that case I’d > probably rather write SQL directly unless I get something else from such > a lib (for example database independence). My 0.02EUR 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. In time, I’ll probably add classes to help with these too, to have some consistency An interesting library that I’ve seen is CLSQL[1] for Common LISP, or SchemeQL[2] for Scheme that have similar goals. 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. I’m not sure if it’s possible to do that in Ruby (if anyone has, I’d like to know. Criteria[3] perhaps.), but this library covers some basic steps in ironing out SQL’s complexities. I also can’t get the compile time checking that SchemeQL advertises, mostly because of duck typing, but also to an extent becasue Ruby isn’t compiled. 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. –Ken [1] [http://clsql.b9.com/](http://clsql.b9.com/) [2] [Schematics -- Not Found](http://schematics.sourceforge.net/schemeql.html) [http://repository.readscheme.org/ftp/papers/sw2002/schemeunit-schemeql.pdf](http://repository.readscheme.org/ftp/papers/sw2002/schemeunit-schemeql.pdf) [3] [http://mephle.org/Criteria/](http://mephle.org/Criteria/) (via [http://onestepback.org/index.cgi/Tech/Ruby/Criteria.rdoc](http://onestepback.org/index.cgi/Tech/Ruby/Criteria.rdoc)) ---
**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\ **Post date:** [August 27, 2006, 11:15am UTC](https://www.ruby-forum.com/t/sql-generator-library/66450/5 "2006-08-27T11:15:50Z")
Ken B. wrote: > I’m not sure what you mean by an object graph. > A structure of objects in memory that refer to each other one way or > another. Kind regards ``` robert ``` ---
**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\ **Post date:** [September 4, 2006, 2:46pm UTC](https://www.ruby-forum.com/t/sql-generator-library/66450/6 "2006-09-04T14:46:23Z")
> syntax to three statements (INSERT, SELECT, and UPDATE) that seem > useful for most people, who are performing queries and > Criteria[3] > 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. > > –Ken Hey Ken- ``` You may be interested in taking a look at a library I wrote called ``` ez-where[1]. It is distributed as a plugin for rals and focuses on building the WHERE clause for active record queries. But most of it can be used for generating where clauses and could be used wihout rails as a stand alone where generator. Its focus is on having a nice ruby dsl style syntax for declaring the conditions as objects. ``` Here are a few simple examples from the test cases: def test_cc_with_multiple_statements a = c { foo == 'bar' } b = c { baz =~ '%qux%' } c = c { age > 20 } d = c { gemz === (1..5) } expected = ["((foo = ?) AND (baz LIKE ?)) OR ((age > ?) AND ``` NOT (gemz IN (?)))", “bar”, “%qux%”, 20, [1, 2, 3, 4, 5]] assert\_equal expected, (a + b | c - d).to\_sql end ``` def test_multi_clause expected = ["(my_table.title LIKE ? OR my_table.subtitle ``` LIKE ? OR my\_table.body LIKE ? OR my\_table.footnotes LIKE ? OR my\_table.keywords LIKE ?)", “%package%”, “%package%”, “%package%”, “% package%”, “%package%”] ``` multi = EZ::Where::MultiClause.new ``` ([:title, :subtitle, :body, :footnotes, :keywords], :my\_table) multi =~ ‘%package%’ assert\_equal expected, multi.to\_sql ``` cond = EZ::Where::Condition.new :my_table cond.any_of(:title, :subtitle, :body, :footnotes, :keywords) ``` =~ ‘%package%’ ``` assert_equal expected, cond.to_sql cond = EZ::Where::Condition.new :my_table cond.any_of(:title, :subtitle, :body, :footnotes, :keywords) ``` =~ ‘%package%’ cond.all\_of(:active, :flagged) == true ``` expected = ["(my_table.title LIKE ? OR my_table.subtitle ``` LIKE ? OR my\_table.body LIKE ? OR my\_table.footnotes LIKE ? OR my\_table.keywords LIKE ?) AND (my\_table.active = ? AND my\_table.flagged = ?)", “%package%”, “%package%”, “%package%”, “% package%”, “%package%”, true, true] assert\_equal expected, cond.to\_sql ``` expected = ["(my_table.title LIKE ? OR my_table.subtitle ``` LIKE ? OR my\_table.body LIKE ? OR my\_table.footnotes LIKE ? OR my\_table.keywords LIKE ?) OR (my\_table.active = ? AND my\_table.flagged = ?)", “%package%”, “%package%”, “%package%”, “% package%”, “%package%”, true, true] assert\_equal expected, cond.to\_sql(:or) end Cheers- -Ezra [1] svn://rubyforge.org/var/svn/ez-where ---
**Author:** ![sevensoft](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/f9ae1b/32.png) [@sevensoft](https://www.ruby-forum.com/u/sevensoft)\ **Post date:** [August 27, 2006, 6:31am UTC](https://www.ruby-forum.com/t/sql-generator-library/66450/7 "2006-08-27T06:31:03Z")
On Sat, 26 Aug 2006 00:33:03 +0200, Robert K. wrote: > Ken B. wrote: > > > 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. > > So you are basically constructing a complex filter condition or even a > complete SQL statement via some form of object graph, correct? I’m not sure what you mean by an object graph. > > 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. > > What about DELETE? Also, I’m not sure how you are able to create a > consistent syntax for all of these as they perform different operations > and need a differing set of inputs. While there’s a lot of difference in the syntax, there’s quite a bit of similarity in the underlying components. While a straight INSERT statement (not yet implemented) is quite different than a SELECT statement, an INSERT…SELECT statement, a SELECT, a CREATE TABLE…SELECT, and an UPDATE statement are all quite similar in terms of inputs, but all a bit different (and complicated) in terms of where to put things like column names. I haven’t implemented a DELETE statement yet (mostly because when they get complicated enough in MYSQL, I have to do it in 3 steps: CREATE…SELECT, DELETE, DROP TABLE), but I’ll have a look at how to do that. > > of s-expressions make it very easy to construct an entire sublanguage for > > where a lot of syntax concerning types, keys and sequences is much more > > variable. > > But it contains DDL and thus is not portable. Precisely > With SQL you can only > rely on DML being portable. And even then you will occasionally stumble > into things you cannot do without DB specific SQL generation. I haven’t tried to abstract any of the nonportable stuff out. I’ve abstracted out the basic syntax of SQL. All of the more advanced stuff, you’re on your own. > Maybe you > should plan for multi dialect SQL generation if you intend to distribute > this to a wider audience. If people find it useful I am sure they will > want to use it with their favorite brand of RDBMS… What I have now should work with most brands of RDBMS. –Ken