Sequel Gem Flashcards
Sequel Gem
what is sequel
SQL database access toolkit for Ruby
Sequel Gem
what is a Dataset
a dataset object encapsulates an SQL query;
it supports chainability;
retrieves records only when needed;
accessed using Enumerable interface
ex
DB[:countries].filter(region: ‘Middle East’).avg(:GDP)
equivalent to
SELECT avg(GDP) FROM countries WHERE region = ‘Middle East’;
Sequel Gem
how to connect to a database
Sequel.connect(‘url’)
supports other options
ex
DB = Sequel.connect(“postgres://user:password@host:port/database_name”, :max_connections => 10, :logger => Logger.new(‘log/db.log’))
Sequel Gem
naming convention for instance of a database connection
DB
Sequel Gem
execute raw sql statements in Sequel
DB.run(“create table t (a text, b text)”)
DB.run(“insert into t values (‘a’, ‘b’)”)
Sequel Gem
create a dataset with raw sql
dataset = DB[‘select id from items’]
dataset. count
dataset. map(:id)
Sequel Gem
fetch records with a dataset created with raw sql
DB[‘select * from items’].each { |row| p row }
Sequel Gem
use placeholders in raw sql statements
name = ‘Jim’
DB[‘select * from items where name = ?’, name].each { |row| p row }
Sequel Gem
the primary way records are retrieved and manipulated
datasets
Sequel Gem
two primary ways datasets are created
Database#from or Database#[]
ex
posts = DB.from(:posts)
posts = DB[:posts] # same
Sequel Gem
characteristic of datasets that allows you to manipulate them after they are created: to filter records, change ordering, join tables, etc
datasets only fetch records when you tell them to
Sequel Gem
method that retrieves all records from a dataset
Dataset#all
returns an array of hashes, where each hash corresponds to a record
if a block is given, yields all objects after loading them
ex
posts.all
SELECT * FROM posts
Sequel Gem
iterate through a dataset one record at a time
Dataset#each
ex
posts.each { |row| p row }
Sequel Gem
retrieve the first or last record from a dataset
Dataset#first
ex
posts.first
ex
posts.order(:stamp).last
SELECT * FROM posts ORDER BY stamp DESC LIMIT 1
Sequel Gem
return an array of one or more column values from a dataset
Dataset#map
ex
DB[:table].map(:id)
=> [1, 2, 3, …]
Sequel Gem
retrieve a single record with a specific value from a dataset
Dataset#[]
ex
posts[:id => 1]
SELECT * FROM posts WHERE id = 1 LIMIT 1
Sequel Gem
method that allows you to filter records in a dataset
Dataset#where
Sequel Gem
use Dataset#where to filter based on a hash of values
my_posts = posts.where(:category => ‘ruby’, :author => ‘david’)
WHERE category = ‘ruby’ AND author = ‘david’
Sequel Gem
use Dataset#where to filter based on a range
my_posts = posts.where(:stamp => (Date.today - 14)..(Date.today - 7))
WHERE stamp >= ‘2010-06-30’ AND stamp
Sequel Gem
use Dataset#where to filter based on an array of values
my_posts = posts.where(:category => [‘ruby’, ‘postgres’, ‘linux’])
WHERE category IN (‘ruby’, ‘postgres’, ‘linux’)
Sequel Gem
method that allows you to use an inverse filter on a dataset; i.e. opposite of Dataset#where
Dataset#exclude
ex
my_posts = posts.exclude(:category => [‘ruby’, ‘postgres’, ‘linux’])
WHERE category NOT IN (‘ruby’, ‘postgres’, ‘linux’)
Sequel Gem
use a custom where clause with Dataset#where
posts.where(‘stamp IS NOT NULL’)
WHERE stamp IS NOT NULL
Sequel Gem
return the number of records in a dataset
Dataset#count
ex
posts.where(Sequel.like(:category, ‘%ruby%’)).count
SELECT COUNT(*) FROM posts WHERE category LIKE ‘%ruby%’
Sequel Gem
query maximum/minimum values in a dataset
Dataset#max, Dataset#min
ex
max = DB[:history].max(:value)
SELECT max(value) FROM history
min = DB[:history].min(:value)
SELECT min(value) FROM history
Sequel Gem
calculate a sum or average from a dataset
Dataset#sum
ex
sum = DB[:items].sum(:price)
SELECT sum(price) FROM items
avg = DB[:items].avg(:price)
SELECT avg(price) FROM items
Sequel Gem
order the records in a dataset
Dataset#order
ex
posts.order(:stamp)
ORDER BY stamp
posts.order(:stamp, :name)
ORDER BY stamp, name
Sequel Gem
methods used to chain additional order by clauses on a dataset
Dataset#order_append, Dataset#order_prepend
ex
posts.order(:stamp).order_append(:name)
ORDER BY stamp, name
posts.order(:stamp).order_prepend(:name)
ORDER BY name, stamp
Sequel Gem
specify descending order in an order by clause
Dataset#reverse_order
ex
posts.reverse_order(:stamp)
ORDER BY stamp DESC
Sequel Gem
select specific columns to be returned from a dataset
Dataset#select
ex
posts.select(:stamp)
SELECT stamp FROM posts
posts.select(:stamp, :name)
SELECT stamp, name FROM posts
Sequel Gem
methods used to chain additional select statements on a dataset
Dataset#select_append
ex
posts.select(:stamp).select_append(:name)
SELECT stamp, name FROM posts
Sequel Gem
delete rows from a table
Dataset#delete
posts.where(‘stamp
DELETE FROM posts WHERE stamp
call WHERE before DELETE
Sequel Gem
insert records into a table
Dataset#insert
ex
posts.insert(:category => ‘ruby’, :author => ‘david’)
INSERT INTO posts (category, author) VALUES (‘ruby’, ‘david’)
Sequel Gem
update records in a dataset
Dataset#update
ex
posts.where(‘stamp ‘archived’)
UPDATE posts SET state = ‘archived’ WHERE stamp
Sequel Gem
update records in a dataset with a value from another column
posts.where{|o| o.stamp Sequel.+(:backup_number, 1))
UPDATE posts SET backup_number = backup_number + 1 WHERE stamp
