uri = 'postgres://user:pass@host/database' DB = Sequel.connect(uri) DB = Sequel.postgres(database, :host =>host) DB = Sequel.sqlite # Memory database
# Set defaults for future datasets DB.quote_identifiers = true # Create Datasets dataset = DB[:attendees] # Setup SQL Loggers DB.loggers << Logger.new($stdout) # Handle transactions: block required, no way # to leave a transaction open indefinitely DB.transaction { # Execute SQL directly rows = DB['SELECT * FROM ...'].all DB["INSERT ..."].insert DB["DELETE ..."].delete DB << "SET ..." }
DB[:table].limit(5, 2).order(:column4). select(:column1, :column2). filter(:column3 =>0..100).all
DB[:attendees].insert(:name => 'Jeremy Evans') DB[:attendees].filter(:confirmed => nil). update(:confirmed => true) DB[:attendees].filter(:paid => false).delete
ds = DB[:attendees] # Strings ds.filter('n = 1') # n = 1 ds.filter('n > ?', 'M') # n > 'M' # Hashes ds.filter(:n =>1) # n = 1 ds.filter(:n =>nil) # n IS NULL ds.filter(:fn =>'ln') # fn = 'ln' ds.filter(:fn =>:ln) # fn = ln ds.filter(:n =>[1, 2]) # n IN (1, 2) ds.filter(:n =>1..2) # n >= 1 AND n <= 2
ds.filter(:p * :q + 1 < :r) # p * q + 1 < r ds.filter(:p / (:q + 1) >= :r) # p / (q + 1) >= r ds.filter({:p => 3} | :r) # p = 3 OR r ds.filter(~{:p => 'A'} & :r) # p != 'A' AND r ds.filter(~:p) # NOT p ds.filter(~(~{:p =>:q} & :r)) # p = q OR NOT r ds.filter(:p =>ds.select(:q).filter(:r)) # p IN (SELECT q FROM attendees WHERE r)
ds.select(:p + :q) # p + q ds.select(:p.sql_string + :q) # p || q ds.select([:p, :q].sql_string_join) # p || q
ds.filter(:p.like(:q)) # p LIKE q ds.filter(:p.like('q', /r/)) # p LIKE 'q' OR p ~ 'r' ds.filter([:p, :q].sql_string_join.like('Test')) # (p || q) LIKE 'Test'
ds = DB[:attendees]. join_table(:inner, :events, :id =>:event_id) # FROM attendees INNER JOIN events # ON (events.id = attendees.event_id)
ds = ds.join(:locations, :id =>:location_id) # ... INNER JOIN locations ON # (locations.id = events.location_id) ds = ds.left_outer_join(:caterers, \ :id =>:events__caterer_id) # ... LEFT OUTER JOIN caterers ON # (caterers.id = events.caterer.id)
# attendees: id, name, event_id # events: id, name ds = DB[:attendees].join(:events, :id =>:event_id) ds.all # => [{:id=>event.id, :name=>event.name}, ...]
ds.select(:attendees__name___attendee, :events__name___event) ds.all # => [{:attendee=>attendees.name, \ # :event=>events.name}, ...]
# attendees: id, name, event_id # events: id, name ds = DB[:attendees].graph(:events, :id =>:event_id) ds.all # => [{:attendees=>{...}, :events=>{...}}, ...]
class Attendee < Sequel::Model; end class Event < Sequel::Model(:events); end class Foo < Sequel::Model set_dataset db[:bar].join(:baz, :id =>:baz_id) end
Player.many_to_one :team Team.one_to_many :players Player.first.team # SELECT * FROM team WHERE id = #{player.team_id} Team.first.players # SELECT * FROM players WHERE team_id = #{team.id} Attendee.many_to_many :events Attendee.first.events # SELECT events.* FROM events INNER JOIN # attendee_events ON attendee_events.event_id=events.id # AND attendee_events.attendee_id = #{attendee.id}
Player.many_to_one :team player.team # Team or nil player.team = team Team.one_to_many :players team.players # Array of Players team.players_dataset # Sequel::Dataset for this # team's players team.add_player(player) team.remove_player(player) team.remove_all_players
# Only return players with 20 or more goals Team.one_to_many(:high_scorers, :class =>Player){|ds| ds.filter{|o| o.goals >= 20}}
module SameName def self_titled first(:name=>model_object.name) end end Artist.one_to_many :albums, :extend=>SameName Artist.first.albums_dataset.self_titled
# AR has_many :through=>has_many # Firm one_to_many Clients one_to_many Invoices Firm.one_to_many :invoices, :dataset =>proc{ Invoice.eager_graph(:client). filter(:client__firm_id =>pk)} # Joining on any of multiple keys to a single key # through a third table Artist.one_to_many :songs, :dataset=>proc{ Song.select(:songs.*).join(Lyric, :id =>:lyric_id, id=>[:composer_id, :arranger_id, :vocalist_id, :lyricist_id])}
Firm.one_to_many :invoices, :eager_loader =>( proc{|key_hash, firms, associations| # key_hash: {:id=>{1=>[firm1], 2=>[firm2]}, # :type_id=>{1=>[firm1, firm2], 2=>[firm3, firm4]}} id_map = key_hash[Firm.primary_key] firms.each{|f| f.associations[:invoices] = []} Invoice.eager_graph(:client). filter(:client__firm_id =>id_map.keys).all{|i| id_map[inv.client.firm_id].each {|firm| firm.associations[:invoices] << inv }}})
validates do format_of :a, :with=>/\A.*@.*\..*\z/ uniqueness_of :a_id, :b_id # both unique uniqueness_of [:a_id, :b_id] # combination end
validates_each(:amount, :if=>:confirmed?){|o,a,v| o.errors[a] << "is less than 100" if v < 100 end
DB.create_table(:attendees) do primary_key :id # integer/serial/identity String :name # varchar(255)/text column :registered_at, DateTime # timestamp money :price # money foreign_key :event_id, :events index :name, :unique =>true index [:name, :event_id] constraint :b, ~{:price =>0} # price != 0 check{|o| o.registered_at > '2008-12-31'} primary_key [:name, :price] # composite pk foreign_key [:name, :price], :blah, \ :key => [:att_name, :att_price] # composite fk end
DB.alter_table(:attendees) do add_column :confirmed, :boolean, :null =>false drop_constraint :b add_constraint :c do |o| # price != 0 if confirmed {:confirmed =>~{:price=>0}}.case(true) end add_foreign_key :foo_id, :foos add_primary_key :id rename_column :id, :attendee_id drop_column :id set_column_default :name, 'Jeremy' set_column_type :price, Numeric set_column_allow_null :confirmed, true end
DB.add_column :attendees, :confirmed, :boolean DB.add_index :attendees, :confirmed DB.drop_index :attendees, :confirmed DB.rename_column :attendees, :id, :attendee_id DB.drop_column :attendees, :attendee_id DB.set_column_default :attendees, :name, 'Jeremy' DB.set_column_type :attendees, :price, Numeric DB.rename_table :attendees, :people DB.drop_table :people DB.create_view :ac, DB[:attendees].where(:confirmed) DB.drop_view :ac
class CreateAttendees < Sequel::Migration def up create_table(:attendees) { primary_key :id String :name } end def down drop_table(:attendees) end end # CreateAttendees.apply(DB, :up)
Sequel::Migrator.apply(DB, '.') # To current version Sequel::Migrator.apply(DB, '.', 5, 1) # To 5 from 1 # $ sequel -m /path/to/migrations -M 5 postgres://...
ds = DB[:items].filter(:name =>:$n) ds.call(:select, :n =>'Jim') ds.call(:update, {:n =>'Jim', :new_n =>'Bob'}, \ :name =>:$new_n)
ds = DB[:items].filter(:name =>:$n) ps = ds.prepare(:select, :select_by_name) ps.call(:n =>'Jim') DB.call(:select_by_name, :n =>'Jim') ps2 = ds.prepare(:update, :update_name, \ :name =>:$new_n) ps2.call(:n =>'Jim', :new_n =>'Bob')
DB[:table].call_sproc(:select, :mysp, \ 'param1', 'param2') sp = DB[:table].prepare_sproc(:select, :mysp) sp.call('param1', 'param2') sp.call('param3', 'param4')
DB=Sequel.connect('postgres://m/db', :servers => \ {:read_only =>proc{|db| :host =>db.slave}}, \ :max_connections =>16) # 4 connections per slave # Master host: m; slave hosts: s1, s2, s3, s4 def DB.slave; "s#{((@current_host||=-1)+=1)%4}" end
s = {} %w'a b c d'.each{|x| s[x.to_sym] = {:host=>"s#{x}"}} DB=Sequel.connect('postgres://m/db', :servers=>s) DB[:table].server(:a).filter(:num=>10).all DB[:table].server(:b).filter(:num=>100).delete
h = {:a =>1, :b =>2} # or [[:a,1],[:b,2]] h.sql_expr # a = 1 AND b = 2 h.sql_negate # a != 1 AND b != 2 h.sql_or # a = 1 OR b = 2 ~h # a != 1 OR b != 2 h.case(0, :c) # CASE c WHEN a THEN 1 # WHEN b THEN 2 ELSE 0 END
# (a,b) IN (1=2 AND 3=4) filter([:a,:b]=>[[1,2],[3,4]]) # (a,b) IN ((1,2),(3,4)) filter([:a,:b]=>[[1,2],[3,4]].sql_array) {:a=>1} & :b # a = 1 AND b {:a=>1} | :b # a = 1 OR b :a.as(:b) # a AS b 'a'.as(:b) # 'a' AS b :a.cast(:integer) # CAST(a AS integer) :a.cast_numeric << 1 # CAST(a AS integer) << 1 '1.0'.cast_numeric(:real) # CAST('1.0' AS real) :a.cast_string + :b # CAST(a AS varchar(255)) || b
'a' # 'a' 'a'.lit # a 'a'.to_sequel_blob # Needed for dealing with blob # columns on most databases :a + :b - :c * :d / :e # (a + b) - ((c * d) / e) :a & :b | ~:c # (a AND b) OR NOT c :a.sql_function # a() :a.sql_function(:b, :c) # a(b, c) :a.extract(:year) # EXTRACT(year FROM a) # Except on Ruby 1.9: :a < 'a'; :b >= 1 # a < 'a'; b >= 1 :a[] # a() :a[:b, :c] # a(b, c)
:a__b # a.b :a__b.identifier # a__b :a.qualify(:b) # b.a :a.qualify(:b__a) # b.a.a :b__c.qualify(:a) # a.b.c :a.like(:b) # PostgreSQL: a LIKE b :a.like('b') # MySQL: a LIKE BINARY 'b' :a.like(/b/) # PostgreSQL: a ~ 'b' :a.like(/b/) # MySQL: a REGEXP BINARY 'b' :a.ilike(:b) # MySQL: a LIKE b :a.ilike('b') # PostgreSQL: a ILIKE 'b' :a.ilike(/b/) # PostgreSQL: a ~* 'b' :a.ilike(/b/) # MySQL: a REGEXP 'b' :a.sql_number << 1 # a << 1