# cheat_sheet.rdoc

Path: | doc/cheat_sheet.rdoc
Last Update: | Sat May 19 14:08:30 +0000 2012

# 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, ...}], ...]

[Validate]