cheat sheets.

$ command line ruby cheat sheets
Open a database

  require 'rubygems'
  require 'sequel'

  DB = Sequel.sqlite 'my_blog.db'
  DB = Sequel.connect('postgres://user:password@localhost/my_db')
  DB = Sequel.mysql '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 RAM.

  DB = Sequel.sqlite

Logging SQL statements

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

Using raw SQL

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

Create a dataset

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

Most dataset methods are chainable

  dataset = DB[:managers].where(:salary => 5000..10000).order(:name, :department)
  # or
  dataset = DB.query do
    from :managers
    where :salary => 5000..10000
    order :name, :department
  end

Insert rows

  dataset.insert(:name => 'Sharon', :grade => 50)
  dataset << {:name => 'Sharon', :grade => 50} # same effect as above

Retrieve rows

  dataset.each {|r| p r}
  dataset.all #=> [{...}, {...}, ...]
  dataset.first
  dataset.order(:name).last # works only for ordered datasets

Retrieve single value
  id= DB[:item].filter(:name => 'item1').select(:id).single_value

Update/Delete rows

  dataset.filter(:active => false).delete
  dataset.filter('price < ?', 100).update(:active => true)
  DB[:user].filter(:id => 1).update(:count => :count + 1) #=> 
    "UPDATE user SET count = (count + 1) WHERE (id = 1)"

Datasets are Enumerable

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

  dataset.inject {|sum, r| sum + r[:value]}

Filtering (see also doc/dataset_filtering.rdoc)

  dataset.filter(:name => 'abc')
  dataset.filter('name = ?', 'abc')
  dataset.filter(:value > 100)
  dataset.exclude(:value <= 100)

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

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

  # Get the first record that matches a condition
  dataset[:name => 'abc'] # Same as:
  dataset.filter(:name => 'abc').first

  # Filter using a subquery
  dataset.filter('price > ?', dataset.select('AVG(price) + 100'))

Advanced filtering using ruby expressions without blocks

Available as of Sequel 2.0:

  DB[:items].filter(:price < 100).sql
  #=> "SELECT * FROM items WHERE (price < 100)"

  DB[:items].filter(:name.like('AL%')).sql
  #=> "SELECT * FROM items WHERE (name LIKE 'AL%')"

There's support for nested expressions with AND, OR and 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))"

You can use arithmetic operators and specify SQL functions:

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

  DB[:items].filter(:price < :AVG[:price] + 100).sql
  #=> "SELECT * FROM items WHERE (price < (AVG(price) + 100))"

Ordering

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

Row ranges

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

Pagination

  paginated = dataset.paginate(1, 10) # first page, 10 rows per page
  paginated.page_count #=> number of pages in dataset
  paginated.current_page #=> 1
  paginated.next_page #=> next page number or nil
  paginated.prev_page #=> previous page number or nil
  paginated.first_page? #=> true if page number = 1
  paginated.last_page? #=> true if page number = page_count

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(:users, :id => :user_id).join(:resources, :id => :items__resource_id).sql #=>
    "SELECT * FROM items INNER JOIN users ON (users.id = items.user_id) INNER JOIN resources ON (resources.id = items.resource_id)"

  DB.from(:items___t, :users___u, :resources___r).filter(:u__id=>:t__user_id, :r__id=>:t__resource_id).sql #=>
    "SELECT * FROM items AS t, users AS u, resources AS r WHERE ((u.id = t.user_id) AND (r.id = t.resource_id))"

Summarizing

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

  dataset.group(:category).select(:category, :AVG[:price])

SQL Functions / Literals

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

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

  dataset.filter({:updated_at.extract(:year) => 3} & {:updated_at.extract(:month) => 1}) 

Schema Manipulation

  DB.create_table :items do
    primary_key :id
    text :name, :unique => true, :null => false
    boolean :active, :default => true
    foreign_key :category_id, :categories
    index :grade
    constraint(:check_grade) { {:grade => 0} | {:active => false} }
  end

  DB.drop_table :items

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

Migrations
  class AddUser < Sequel::Migration
    def up
      create_table :users do
        primary_key :id
        text :username, :unique => true, :null => false
        text :email, :unique => true, :null => false
        varchar :password, :size => 40, :null => false
        date :dob, :null => false
        boolean :disabled, :default => false
        timestamp :created_at
        timestamp :updated_at
      end
    end

    def down
      drop_table :users
    end
  end

  class ModifyProfiles < Sequel::Migration
    def up
      alter_table :profiles do
        add_column :firstname, :text
        add_column :lastname, :text
	add_index :zip
        add_constraint(:check_count) { :count > 0 }
        rename_column :col1, :col2
        set_column_default :age, 21
      end
    end

    def down
      alter_table :profiles do
        drop_column :firstname
        drop_column :lastname
        drop_index :zip
        rename_column :col2, :col1
      end
    end
  end



Aliasing

  DB[:items].select(:name.as(:item_name))
  DB[:items].select(:name => :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 << {:first_name => 'Inigo', :last_name => 'Montoya'}
    dataset << {: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::Error::Rollback:

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

Supported Types:

 DB.create_table(:cats) do
      primary_key :id, :type=>Integer             # integer
      String :a                                   # varchar(255)
      String :a2, :size=>50                       # varchar(50)
      String :a3, :fixed=>true                    # char(255)
      String :a4, :fixed=>true, :size=>50         # char(50)
      String :a5, :text=>true                     # text
      column :b, File                             # blob
      Fixnum :c                                   # integer
      foreign_key :d, :other_table, :type=>Bignum # bigint
      Float :e                                    # double precision
      BigDecimal :f                               # numeric
      BigDecimal :f2, :size=>10                   # numeric(10)
      BigDecimal :f3, :size=>[10, 2]              # numeric(10, 2)
      Date :g                                     # date
      DateTime :h                                 # timestamp
      Time :i                                     # timestamp
      Time :i2, :only_time=>true                  # time
      Numeric :j                                  # numeric
      TrueClass :k                                # boolean
      FalseClass :l                               # boolean
    end

Miscellaneous:

  dataset.sql #=> "SELECT * FROM items"
  dataset.delete_sql #=> "DELETE FROM items"
  dataset.where(:name => 'sequel').exists #=> "EXISTS ( SELECT 1 FROM items WHERE name = 'sequel' )"
  b= DB.fetch("SELECT #{DB[:item].filter(:name => 'sequel').exists}").single_value #there maybe a better way
  dataset.print #=> pretty table print to $stdout
  dataset.columns #=> array of columns in the result set, does a SELECT
  DB.schema_for_table(:items) => [[:id, {:type=>:integer, ...}], [:name, {:type=>:string, ...}], ...]
                                 # Works on PostgreSQL, MySQL, SQLite, and possibly elsewhere

  # indexing, and programatically creating updates
  v= 123
  periods= [:year, :month, :day]
  idx= 10
  DB[:items].update_sql(periods.inject({}){|h, p| h[p|idx] = (p|idx) + v; h}) #=> 
    "UPDATE items SET month[10] = (month[10] + 123), year[10] = (year[10] + 123), week[10] = (week[10] + 123)"