= Cheat Sheet   

== Open a database

  require 'rubygems'
  require 'sequel'

  DB = Sequel.sqlite('my_blog.db')
  DB = Sequel.connect('postgres://user:password@localhost/my_db')
  DB = Sequel.postgres('my_db', :user => 'user', :password => 'password', :host => 'localhost')
  DB = Sequel.ado('mydb')

== Open an SQLite memory database

Without a filename argument, the sqlite adapter will setup a new sqlite database in memory.

  DB = Sequel.sqlite

== Logging SQL statements

  require 'logger'
  DB = Sequel.sqlite '', :loggers => [Logger.new($stdout)]
  # or
  DB.loggers << Logger.new(...)

== Using raw SQL

  DB.run "CREATE TABLE users (name VARCHAR(255) NOT NULL, age INT(3) NOT NULL)"
  dataset = DB["SELECT age FROM users WHERE name = ?", name]
  dataset.map(:age)
  DB.fetch("SELECT name FROM users") do |row|
    p row[:name]
  end

== Create a dataset

  dataset = DB[:items]
  dataset = DB.from(:items)

== Most dataset methods are chainable

  dataset = DB[:managers].where(:salary => 5000..10000).order(:name, :department)

== Insert rows

  dataset.insert(:name => 'Sharon', :grade => 50)

== Retrieve rows

  dataset.each{|r| p r}
  dataset.all # => [{...}, {...}, ...]
  dataset.first # => {...}

== Update/Delete rows

  dataset.filter(~:active).delete
  dataset.filter('price < ?', 100).update(:active => true)

== Datasets are Enumerable

  dataset.map{|r| r[:name]}
  dataset.map(:name) # same as above

  dataset.inject(0){|sum, r| sum + r[:value]}
  dataset.sum(:value) # same as above

== Filtering (see also doc/dataset_filtering.rdoc)

=== Equality

  dataset.filter(:name => 'abc')
  dataset.filter('name = ?', 'abc')

=== Inequality

  dataset.filter{value > 100}
  dataset.exclude{value <= 100}

=== Inclusion

  dataset.filter(:value => 50..100)
  dataset.where{(value >= 50) & (value <= 100)}

  dataset.where('value IN ?', [50,75,100])
  dataset.where(:value=>[50,75,100])

  dataset.where(:id=>other_dataset.select(:other_id))

=== Subselects as scalar values

  dataset.where('price > (SELECT avg(price) + 100 FROM table)')
  dataset.filter{price > dataset.select(avg(price) + 100)}

=== LIKE/Regexp

  DB[:items].filter(:name.like('AL%'))
  DB[:items].filter(:name => /^AL/)

=== AND/OR/NOT

  DB[:items].filter{(x > 5) & (y > 10)}.sql 
  # SELECT * FROM items WHERE ((x > 5) AND (y > 10))

  DB[:items].filter({:x => 1, :y => 2}.sql_or & ~{:z => 3}).sql 
  # SELECT * FROM items WHERE (((x = 1) OR (y = 2)) AND (z != 3))

=== Mathematical operators

  DB[:items].filter((:x + :y) > :z).sql 
  # SELECT * FROM items WHERE ((x + y) > z)

  DB[:items].filter{price - 100 < avg(price)}.sql 
  # SELECT * FROM items WHERE ((price - 100) < avg(price))

== Ordering

  dataset.order(:kind)
  dataset.reverse_order(:kind)
  dataset.order(:kind.desc, :name)

== Limit/Offset

  dataset.limit(30) # LIMIT 30
  dataset.limit(30, 10) # LIMIT 30 OFFSET 10

== Joins

  DB[:items].left_outer_join(:categories, :id => :category_id).sql 
  # SELECT * FROM items LEFT OUTER JOIN categories ON categories.id = items.category_id

  DB[:items].join(:categories, :id => :category_id).join(:groups, :id => :items__group_id) 
  # SELECT * FROM items INNER JOIN categories ON categories.id = items.category_id INNER JOIN groups ON groups.id = items.group_id
	
== Aggregate functions methods

  dataset.count #=> record count
  dataset.max(:price)
  dataset.min(:price)
  dataset.avg(:price)
  dataset.sum(:stock)

  dataset.group_and_count(:category)
  dataset.group(:category).select(:category, :AVG.sql_function(:price))

== SQL Functions / Literals

  dataset.update(:updated_at => :NOW.sql_function)
  dataset.update(:updated_at => 'NOW()'.lit)

  dataset.update(:updated_at => "DateValue('1/1/2001')".lit)
  dataset.update(:updated_at => :DateValue.sql_function('1/1/2001'))

== Schema Manipulation

  DB.create_table :items do
    primary_key :id
    String :name, :unique => true, :null => false
    TrueClass :active, :default => true
    foreign_key :category_id, :categories
    DateTime :created_at
    
    index :created_at
  end

  DB.drop_table :items

  DB.create_table :test do
    String :zipcode
    enum :system, :elements => ['mac', 'linux', 'windows']
  end

== Aliasing

  DB[:items].select(:name.as(:item_name))
  DB[:items].select(:name___item_name)
  DB[:items___items_table].select(:items_table__name___item_name)
  # SELECT items_table.name AS item_name FROM items AS items_table

== Transactions

  DB.transaction do
    dataset.insert(:first_name => 'Inigo', :last_name => 'Montoya')
    dataset.insert(:first_name => 'Farm', :last_name => 'Boy')
  end # Either both are inserted or neither are inserted

Database#transaction is re-entrant:

  DB.transaction do # BEGIN issued only here
    DB.transaction
      dataset << {:first_name => 'Inigo', :last_name => 'Montoya'}
    end
  end # COMMIT issued only here

Transactions are aborted if an error is raised:

  DB.transaction do
    raise "some error occurred"
  end # ROLLBACK issued and the error is re-raised

Transactions can also be aborted by raising Sequel::Rollback:

  DB.transaction do
    raise(Sequel::Rollback) if something_bad_happened
  end # ROLLBACK issued and no error raised

Savepoints can be used if the database supports it:

  DB.transaction do
    dataset << {:first_name => 'Farm', :last_name => 'Boy'} # Inserted
    DB.transaction(:savepoint=>true) # This savepoint is rolled back
      dataset << {:first_name => 'Inigo', :last_name => 'Montoya'} # Not inserted
      raise(Sequel::Rollback) if something_bad_happened
    end
    dataset << {:first_name => 'Prince', :last_name => 'Humperdink'} # Inserted
  end

== Miscellaneous:

  dataset.sql # "SELECT * FROM items"
  dataset.delete_sql # "DELETE FROM items"
  dataset.where(:name => 'sequel').exists # "EXISTS ( SELECT * FROM items WHERE name = 'sequel' )"
  dataset.columns #=> array of columns in the result set, does a SELECT
  DB.schema(:items) => [[:id, {:type=>:integer, ...}], [:name, {:type=>:string, ...}], ...]