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
Sequel Gem
method that wraps code in a database transaction
Database#transaction
ex
DB.transaction do
posts. insert(:category => ‘ruby’, :author => ‘david’)
posts. where(‘stamp ‘archived’)
end
Sequel Gem
method that joins two tables
Dataset#join
ex
order_items = DB[:items].join(:order_items, :item_id => :id). where(:order_id => 1234)
SELECT * FROM items INNER JOIN order_items
ON order_items.item_id = items.id
WHERE order_id = 1234
!! item_id is automatically qualified with the table being joined, and id is automatically qualified with the last table joined.
Sequel Gem
execute a select statement manually with a string
Database#fetch
ex
DB.fetch(“SELECT * FROM albums”) do |row|
puts row[:name]
end
Sequel Gem
use a named placeholder with Database#fetch
DB.fetch(“SELECT * FROM albums WHERE name LIKE :pattern”, :pattern=>‘A%’) do |row|
puts row[:name]
end
Sequel Gem
what Database#fetch returns when a block is not provided
Dataset
ex
ds = DB.fetch(“SELECT * FROM albums”)
Sequel Gem
how to insert, update or delete using SQL
first create the dataset, then execute:
ds. insert
ds. update
ds. delete
Sequel Gem
return the SQL that will be used for a given expression
Database#literal
ex
DB.literal(1) # => “1”
DB.literal(:column) # => “"column"”
DB.literal(‘string’) # => “‘string’”
Sequel Gem
create a log file
DB.loggers
Sequel Gem
how identifies are usually specified in Sequel gem
symbols
ex
:column # “column”
Sequel Gem
syntax to qualifiy a column reference that exists in two different tables
double underscore
ex
:table__column # “table”.”column”
Sequel Gem
method that qualifiies a column reference that exists in two different tables
Sequel.qualify
ex
Sequel.qualify(:table, :column) # “table”.”column”
Sequel Gem
syntax to alias a column symbol
triple underscore
ex
:column___alias # “column” AS “alias”
ex with qualification
:table__column___alias # “table”.”column” AS “alias”
Sequel Gem
method that creates an alias
Sequel.as
ex
Sequel.as(:column, :alias) # “column” AS “alias”
Sequel.qualify(:table, :column).as(:alias) # “table”.”column” AS “alias”
Sequel Gem
syntax to use SQL functions
virtual row
ex
DB[:albums].select{func.function} # SELECT func() FROM “albums”
DB[:albums].select{func(col1, col2)} # SELECT func(“col1”, “col2”) FROM “albums”
Sequel Gem
method that allows you to use a SQL function
Sequel.function
ex
Sequel.function(:func) # func()
Sequel.function(:func, :col1, :col2) # func(“col1”, “col2”)
Sequel Gem
method that allows you to use SQL aggregate functions
Sequel.function
ex
Sequel.function(:sum, :column) # sum(column)
Sequel Gem
method that adds the ‘distinct’ modifier to aggregate functions
distinct
ex
DB[:albums].select{sum(:column).distinct} # SELECT sum(DISTINCT column) FROM albums
Sequel Gem
method that allows you to use the wildcard as the sole argument of the aggregate function
*
ex
Sequel.function(:count).* # count(*)
DB[:albums].select{count.function.*} # SELECT count(*) FROM albums
Sequel Gem
equality operator
sequel uses hashes to specify equality
ex
{:column=>1} # (“column” = 1)
Sequel Gem
not equal operator
Sequel.negate or Sequel.~
ex
Sequel.negate(:column => 1) # (“column” != 1)
Sequel.~(:column => 1) # (“column” != 1)
Sequel Gem
difference between Sequel.negate and Sequel.~
Sequel.negate negates all entries
Sequel.~ does a general inversion
ex
Sequel.negate(:column => 1, :foo => 2) # ((“column” != 1) AND (foo != 2))
Sequel.~(:column => 1, :foo => 2) # ((“column” != 1) OR (foo != 2))
Sequel Gem
method that uses not equal operator in where clause
Dataset#exclude
ex
DB[:albums].exclude(:column=>1) # SELECT * FROM “albums” WHERE (“column” != 1)
Sequel Gem
use inclusion and exclusion operators
inclusion and exclusion work the same way as equality and inequality. Sequel swithces from = or != to IN or NOT IN when the hash value is an array or a dataset
ex
{:column=>[1, 2, 3]} # (“column” IN (1, 2, 3))
Sequel.~(:column=>[1, 2, 3]) # (“column” NOT IN (1, 2, 3))
Sequel Gem
method that allows use of EXISTS operator
exists
ex
DB[:albums].exists # EXISTS (SELECT * FROM albums)
Sequel Gem
use identity operators (IS and IS NOT)
identity operators work the same way as equality does
ex
{:column=>nil} # (“column” IS NULL)
{:column=>true} # (“column” IS TRUE)
{:column=>false} # (“column” IS FALSE)
Sequel.~(:column=>nil) # (“column” IS NOT NULL)
Sequel.~(:column=>true) # (“column” IS NOT TRUE)
Sequel.~(:column=>false) # (“column” IS NOT FALSE)
Sequel Gem
use inversion operator (NOT)
Sequel.~
ex
Sequel.~(:column) # NOT “column”
Sequel Gem
syntax to use inequality operators ( =)
= directly on expression objects
ex
Sequel.qualify(:table, :column) > 1 # (“table”.”column” > 1)
Sequel.qualify(:table, :column)
Sequel.function(:func) >= 1 # (func() >= 1)
Sequel.function(:func, :column)
Sequel Gem
use inequality operators ( =) on symbols
Sequel.expr
ex
Sequel.expr(:column) > 1 # (“column” > 1)
Sequel Gem
syntax to use mathematical operators (+ - * /)
+ - * / directly on expression objects
ex
Sequel.expr(:column) + 1 # “column” + 1
Sequel.expr(:table__column) - 1 # “table”.”column” - 1
Sequel.qualify(:table, :column) * 1 # “table”.”column” * 1
Sequel.expr(:column) / 1 # “column” / 1 Sequel.expr(:column) ** 1 # power(“column”, 1)
Sequel Gem
syntax to use boolean operators (AND OR)
& and | methods directly on expression objects
ex
Sequel.expr(:column1) & :column2 # (“column1” AND “column2”)
Sequel.expr(:column1=>1) | {:column2=>2} # ((“column1” = 1) OR (“column2” = 2))
(Sequel.function(:func) > 1) & :column3 # ((func() > 1) AND “column3”)
Sequel Gem
how to cast data types
the cast method directly on expression objects
ex
Sequel.expr(:name).cast(:text) # CAST(“name” AS text)
Sequel.expr(‘1’).cast(:integer) # CAST(‘1’ AS integer)
Sequel.qualify(:table, :column).cast(:date) # CAST(“table”.”column” AS date)
Sequel Gem
use the LIKE operator
the like and ilike methods directly on expression objects
ex
Sequel.expr(:name).like(‘A%’) # (“name” LIKE ‘A%’ ESCAPE ‘')
Sequel.expr(:name).ilike(‘A%’) # (“name” ILIKE ‘A%’ ESCAPE ‘')
Sequel Gem
how to use a regular expression with MySQL or Postgres
by passing a ruby regular expression to like or ilike
ex
Sequel.like(:name, /^A/) # (“name” ~ ‘^A’)
~Sequel.ilike(:name, /^A/) # (“name” !~* ‘^A’)
Sequel Gem
how to use the SQL CASE statement
Sequel.case(conditions_hsh, default_value) # the keys of the hash is WHEN, the values are THEN
ex
Sequel.case({:column=>1}, 0) # (CASE WHEN “column” THEN 1 ELSE 0 END)
Sequel.case([[column, 1]], 0) # (CASE WHEN “column” THEN 1 ELSE 0 END)
Sequel.case({{:column=>nil}=>1}, 0) # (CASE WHEN (column IS NULL) THEN 1 ELSE 0 END)
Sequel Gem
how datasets are generally created
Database#[] # passing in 1 or more table names
ex
ds = DB[:albums, :artists] # this dataset is not ready to be manipulated by chaining methods
ex
ds.select(:id, :name).where(Sequel.like(:name, ‘A%’)).order(:name) # SELECT id, name FROM albums WHERE (name LIKE ‘A%’ ESCAPE ‘') ORDER BY name
Sequel Gem
add additional OR WHERE clause
Dataset#or
ex
ds.where(:name => ‘George’).or([[:id => [2,3]]]) # SELECT * FROM people WHERE ((name = ‘George’) OR (id IN (2, 3)))
Sequel Gem
method that allows you to use the LIKE operator for string comparison
Dataset#grep(:field, ‘match_string’)
ex
ds.grep(:name, ‘G%’) # SELECT * FROM people WHERE ((name LIKE ‘G%’))
Sequel Gem
wildcard used with LIKE operator that matches zero or more of any character
%
ex
‘G%’ matches George, but does not match SING
Sequel Gem
wildcard used with LIKE operator that matches exactly one of any character
_
ex
‘G_’ matches Go but does not match George
Sequel Gem
the main Sequel object that you deal with and how it is instantiated
Sequel::Database
ex
Sequel.connect(‘postgres://localhost/dbname’) => Sequel::Database
Sequel Gem
method that can be used to take any object that Sequel handles and literalize the object to an SQL string fragment
Sequel::Database#literal => string
ex
DB.literal(DB[:table]) => ‘(SELECT * FROM “table”)’
objects that represent abstract collections of rows in the database
Sequel::Dataset
ruby type that represents identifiers (tables, columns, schemas), qualified identifiers (table.column), aliased identifiers (column as alias), and qualified aliased identifiers (table.column as alias)
symbols
ex
:table # “table”
:column # “column”
:table__column # “table”.”column”
:column___alias # “column” AS “alias”
:table__column___alias # “table”.”column” AS “alias”
used to represent an SQL concept that doesn’t map directly to a ruby class
Sequel::SQL::Expression
Sequel Gem
create a literal sequel string, copied verbatim into an SQL statement
Sequel::LiteralString
ex
Sequel.lit(“co’de”) # co’de
ex
DB[:albums].select(‘name’) # SELECT ‘name’ FROM albums DB[:albums].select(Sequel.lit(‘name’)) # SELECT name FROM albums
separate subclasses for representing boolean operations such as AND and OR, mathematical operations such as + and -, and string operations such as || and LIKE
Sequel::SQL::ComplexExpression
ex
Sequel.or(:col1, :col2) # (“col1” OR “col2”)
Sequel.+(:column, 2) # (“column” + 2)
object that represent SQL CASE expressions
Sequel::SQL::CaseExpression
ex
Sequel.case({2=>1}, 0, :a) # CASE “a” WHEN 2 THEN 1 ELSE 0 END
Sequel.case({{:a=>2}=>1}, 0) # CASE WHEN (“a” = 2) THEN 1 ELSE 0 END
object that represents CAST expressions in SQL, which does explicit typecasting in the database
Sequel::SQL::Cast
ex
Sequel.cast(:a, String) # (CAST “a” AS text)
Sequel.cast(:a, :int4) # (CAST “a” AS int4)
object that represents an evaluation that is delayed until query literalization
Sequel::SQL::DelayedEvaluation
ex
Sequel.delay{some_model.updated_at}
object that represents database function calls, which take a function name and any arguments
Sequel::SQL::Function
ex
Sequel.function(:func, :a, 2) # func(“a”, 2)
object that represents a literal SQL string with placeholders for variables
Sequel::SQL::PlaceholderLiteralString
ex
Sequel.lit(‘? = ?’, :a, 1) # “a” = 1
Sequel.lit(‘:b = :v’, :b=>:a, :v=>1) # “a” = 1
Sequel.lit([’’, ‘ = ‘], :a, 1) # “a” = 1
object that represents ascending or descending sorts, used by the Dataset order methods
Sequel::SQL::OrderedExpression
ex
Sequel.asc(:a) # “a” ASC
Sequel.desc(:a) # “a” DESC
Sequel.asc(:a, :nulls=>:first) # “a” ASC NULLS FIRST
Sequel.desc(:a, :nulls=>:last) # “a” DESC NULLS LAST
BasicObject subclass that is the backbone behind the block expression support
Sequel::SQL::VirtualRow
ex
DB[:table].where{a < 1} # the block is instance-evaled inside a VirtualRow instance
