== Ambitious SQL

A simple experiment and even simpler query library.

I could tell you all about how awesome the internals are, or 
how fun it was to write, or how it'll make you rich and famous, 
but instead I'm just going to show you some examples. 

The goal is this: write once, run with ActiveRecord, Sequel, DataMapper,
whatever. Kind of like Rack for databases.

== Git It (Not with Git, though)

  $ sudo gem install ambition -y

This will suck in Ambition and its dependencies (ParseTree & ActiveRecord). 
It's fully usable outside of Rails (I use it in a Camping app or two), as long
as you're riding ActiveRecord.

To use with Rails, after installing the gem:
  
  $ cd vendor/plugins
  $ gem unpack ambition

RDoc exists: http://rock.errtheblog.com/ambition

== Examples

Basically, you write your SQL in Ruby.  No, not in Ruby.  As Ruby.

  User.select { |u| u.city == 'San Francisco' }.each do |user|
    puts user.name
  end

And that's it.

The key is that queries arent actually run until the data they represent is 
requested. Usually this is done with what I call a kicker method. You can call them 
that, too.

Kicker methods are guys like +detect+, +each+, +each_with_index+, +map+, +entries+, 
+to_a+, and +first+ (with no argument). Methods like +select+, +sort_by+, and +first+ 
(with an argument) are not kicker methods and return a +Query+ object without running any SQL.

Our +Query+ object has two useful methods: +to_sql+ and +to_hash+.  With these,
we can check out what exactly we're building.  Not everyone has +to_sql+,
though.  Mostly ignore these methods and treat everything like you normally
would.

See, +to_sql+:
  >> User.select { |m| m.name == 'jon' }.to_sql
  => "SELECT * FROM users WHERE users.`name` = 'jon'"

See, +to_hash+:
  >> User.select { |m| m.name == 'jon' }.to_hash
  => {:conditions=>"users.`name` = 'jon'"} 

== Limitations

You can use variables, but any more complex Ruby (right now) won't work
inside your blocks.  Just do it outside the block and assign it to a variable.

Instead of:
  User.select { |m| m.date == 2.days.ago }

Just do:
  date = 2.days.ago
  User.select { |m| m.date == date }

Instance variables and globals work, too.  Same with method calls.

== Equality -- select { |u| u.field == 'bob' }

  User.select { |m| m.name == 'jon' }
  "SELECT * FROM users WHERE users.`name` = 'jon'"

  User.select { |m| m.name != 'jon' }
  "SELECT * FROM users WHERE users.`name` <> 'jon'"

  User.select { |m| m.name == 'jon' && m.age == 21 }
  "SELECT * FROM users WHERE (users.`name` = 'jon' AND users.`age` = 21)"

  User.select { |m| m.name == 'jon' || m.age == 21 }
  "SELECT * FROM users WHERE (users.`name` = 'jon' OR users.`age` = 21)"
    
  User.select { |m| m.name == 'jon' || m.age == 21 && m.password == 'pass' }
  "SELECT * FROM users WHERE (users.`name` = 'jon' OR (users.`age` = 21 AND users.`password` = 'pass'))"

  User.select { |m| (m.name == 'jon' || m.name == 'rick') && m.age == 21 }
  "SELECT * FROM users WHERE ((users.`name` = 'jon' OR users.`name` = 'rick') AND users.`age` = 21)"

== Associations -- select { |u| u.field == 'bob' && u.association.field == 'bob@bob.com' }

The +to_sql+ method doesn't work on associations yet, but that's okay: they can 
still query through ActiveRecord just fine.

  User.select { |u| u.email == 'chris@ozmm.org' && u.profile.name == 'chris wanstrath' }.map(&:title)
  SELECT users.`id` AS t0_r0, ... FROM users LEFT OUTER JOIN profiles ON profiles.user_id = users.id WHERE ((users.`email` = 'chris@ozmm.org' AND profiles.name = 'chris wanstrath')) 

== Comparisons -- select { |u| u.age > 21 }

  User.select { |m| m.age > 21 }
  "SELECT * FROM users WHERE users.`age` > 21"

  User.select { |m| m.age < 21 }.to_sql
  "SELECT * FROM users WHERE users.`age` < 21"

  User.select { |m| [1, 2, 3, 4].include? m.id }
  "SELECT * FROM users WHERE users.`id` IN (1, 2, 3, 4)"

== LIKE and REGEXP (RLIKE) -- select { |m| m.name =~ 'chris' }

  User.select { |m| m.name =~ 'chris' }
  "SELECT * FROM users WHERE users.`name` LIKE 'chris'"

  User.select { |m| m.name =~ 'chri%' }
  "SELECT * FROM users WHERE users.`name` LIKE 'chri%'"

  User.select { |m| m.name !~ 'chris' }
  "SELECT * FROM users WHERE users.`name` NOT LIKE 'chris'"

  User.select { |m| !(m.name =~ 'chris') }
  "SELECT * FROM users WHERE users.`name` NOT LIKE 'chris'"

  User.select { |m| m.name =~ /chris/ }
  "SELECT * FROM users WHERE users.`name` REGEXP 'chris'"

== #detect

  User.detect { |m| m.name == 'chris' }
  "SELECT * FROM users WHERE users.`name` = 'chris' LIMIT 1"

== LIMITs -- first, first(x), [offset, limit], [range], slice

  User.select { |m| m.name == 'jon' }.first
  "SELECT * FROM users WHERE users.`name` = 'jon' LIMIT 1"

  User.select { |m| m.name == 'jon' }.first(5)
  "SELECT * FROM users WHERE users.`name` = 'jon' LIMIT 5"

  User.select { |m| m.name == 'jon' }[10, 20]
  "SELECT * FROM users WHERE users.`name` = 'jon' LIMIT 10, 20"

  User.select { |m| m.name == 'jon' }[10..20]
  "SELECT * FROM users WHERE users.`name` = 'jon' LIMIT 10, 10"

== ORDER -- sort_by { |u| u.field }

  User.select { |m| m.name == 'jon' }.sort_by { |m| m.name }
  "SELECT * FROM users WHERE users.`name` = 'jon' ORDER BY users.name"

  User.select { |m| m.name == 'jon' }.sort_by { |m| [ m.name,  m.age ] }
  "SELECT * FROM users WHERE users.`name` = 'jon' ORDER BY users.name, users.age"

  User.select { |m| m.name == 'jon' }.sort_by { |m| [ m.name,  -m.age ] }
  "SELECT * FROM users WHERE users.`name` = 'jon' ORDER BY users.name, users.age DESC"

  User.select { |m| m.name == 'jon' }.sort_by { |m| [ -m.name,  -m.age ] }
  "SELECT * FROM users WHERE users.`name` = 'jon' ORDER BY users.name DESC, users.age DESC"

  User.select { |m| m.name == 'jon' }.sort_by { |m| -m.age }
  "SELECT * FROM users WHERE users.`name` = 'jon' ORDER BY users.age DESC"

  User.select { |m| m.name == 'jon' }.sort_by { |m| -m.profiles.title }
  "SELECT users.`id` AS t0_r0, ... FROM users LEFT OUTER JOIN profiles ON profiles.user_id = users.id 
   WHERE (users.`name` = 'jon') ORDER BY profiles.title DESC"

  User.select { |m| m.name == 'jon' }.sort_by { rand }
  "SELECT * FROM users WHERE users.`name` = 'jon' ORDER BY RAND()"

== COUNT -- select { |u| u.name == 'jon' }.size

  User.select { |m| m.name == 'jon' }.size
  SELECT count(*) AS count_all FROM users WHERE (users.`name` = 'jon') 

  >> User.select { |m| m.name == 'jon' }.size
  => 21

== Other Enumerables

These methods perform COUNT() operations rather than loading your array into memory.  They're all 
kickers.
  
  User.any? { |m| m.name == 'jon' }
  User.all? { |m| m.name == 'jon' }
  User.select { |m| m.name == 'jon' }.empty?

== More Sugar

The +downcase+ and +upcase+ methods will map to LOWER() and UPPER(), respectively.

  >> User.select { |m| m.name.downcase =~ 'jon%' }.to_sql
  => "SELECT * FROM users WHERE LOWER(users.`name`) LIKE 'jon%'"
  

== SELECT * FROM bugs 

Found a bug?  Sweet.  Add it at the Lighthouse: http://err.lighthouseapp.com/projects/466-plugins/tickets/new

Feature requests are welcome. 

* Chris Wanstrath [ chris@ozmm.org ]