Работа&с&базами&данных с"использованием"Sequel 

No content

Dependencies 

No content

Sequel&has&restored&my&faith&in& Ruby.&It's&really&amazing.&The&O/RM& I've&been&hoping&for&for&years. —"Sam"Smoot,"creator"of"DataMapper 

Database # memory database DB = Sequel.sqlite # connection string DB = Sequel.connect('postgres://host/database') 

Database DB.create_table(:users) do primary_key :id String :name end 

Migra&ons Sequel.migration do change do create_table(:users) do primary_key :id String :name end end end 

Dataset dataset = DB[:users] # SELECT * FROM "table" dataset = DB.from(:users) # SELECT * FROM "table" dataset = DB.select(:column) # SELECT "column" 

Dataset dataset = DB[:test]. select(:first_name, :last_name). where(country: 'BY'). order(:created_at) 

Dataset dataset.first dataset.all dataset.each { |row| row } 

Dataset dataset.insert(last_name: 'Test') dataset.update(last_name: 'Test') dataset.delete 

Virtual(Row(Blocks dataset.where(Sequel.expr(:a) > Sequel.function(:b, :c)) # WHERE a > b(c) dataset.where { a > b(c) } 

Model class User < Sequel::Model one_to_many :posts end 

Model • many_to_one • one_to_many • one_to_one • many_to_many • one_through_one 

Model class User < Sequel::Model def validate super validates_presence :name end end 

Model class User < Sequel::Model def before_create # do something super end def after_update super # do something end end 

Model user = User[1234] user.name = 'Test' user.save 

Types • pg_array • pg_hstore • pg_inet • pg_interval • pg_json • pg_range 

pg_array_associa+ons # Database schema: # tags albums # :id (int4) <--\ :id # :name \-- :tag_ids (int4[]) # :name class Album plugin :pg_array_associations pg_array_to_many :tags end class Tag plugin :pg_array_associations many_to_pg_array :albums end 

Constraints create_table(:users) do primary_key :id String :name constraint(:name_min_length) { char_length(name) > 2 } end 

Indexes alter_table(:users) do add_index Sequel.function(:lower, :name), name: :lower_name end # CREATE INDEX "lower_name" ON "users" (lower("name")) 

Func%ons/Triggers DB.create_function(:set_updated_at, <<-SQL, language: :plpgsql, returns: :trigger) BEGIN NEW.updated_at := CURRENT_TIMESTAMP; RETURN NEW; END; SQL # CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS ' # BEGIN # NEW.updated_at := CURRENT_TIMESTAMP; # RETURN NEW; # END;' DB.drop_function(:set_updated_at) # DROP FUNCTION set_updated_at() 

Func%ons/Triggers DB.create_trigger(:table, :trg_updated_at, :set_updated_at, events: :update, each_row: true, when: { new__updated_at: :old__updated_at }) # CREATE TRIGGER trg_updated_at BEFORE UPDATE ON "table" # FOR EACH ROW WHEN ("new"."updated_at" = "old"."updated_at") # EXECUTE PROCEDURE set_updated_at() DB.drop_trigger(:table, :trg_updated_at) # DROP TRIGGER trg_updated_at ON "table" 

sequel_postgresql_triggers • pgt_created_at • pgt_updated_at • pgt_counter_cache • pgt_sum_cache • pgt_immutable • pgt_touch 

Window'Func+ons SELECT lag(timestamp) OVER (ORDER BY timestamp), timestamp FROM events 

Window'Func+ons DB[:events] .select { [lag(timestamp).over(order: :timestamp), timestamp] } 

Common%Table%Expressions WITH moved_rows AS ( DELETE FROM products WHERE "date" BETWEEN '2010-10-01' AND '2010-10-31' RETURNING * ) INSERT INTO products_log SELECT * FROM moved_rows; DB[:products_log]. with(:moved_rows, DB[:products]. where(date: Date.new(2010, 10, 1)..Date.new(2010, 10, 31)). returning. with_sql(:delete_sql)). insert(DB[:moved_rows]) 

Recursive)Common)Table) Expressions 

rcte_tree • h#p:/ /sequel.jeremyevans.net/rdoc8plugins/classes/Sequel/ Plugins/RcteTree.html • h#p:/ /explainextended.com/2009/09/24/adjacency8list8vs8 nested8sets8postgresql/ 

Prepared'transac,ons DB.transaction(prepare: 'some_transaction_id_string') do # ... end DB.commit_prepared_transaction('some_transaction_id_string') DB.rollback_prepared_transaction('some_transaction_id_string') 

Cursors DB[:table].use_cursor.each { |row| } # BEGIN; # DECLARE sequel_cursor NO SCROLL CURSOR WITHOUT HOLD FOR SELECT * FROM "table"; # FETCH FORWARD 1000 FROM sequel_cursor # FETCH FORWARD 1000 FROM sequel_cursor # ... # FETCH FORWARD 1000 FROM sequel_cursor # CLOSE sequel_cursor # COMMIT DB[:table].paged_each { |row| } 

Streaming gem 'sequel_pg' DB.extension(:pg_streaming) DB[:table].stream.each { |row| } DB[:table].paged_each { |row| } 

Plugins h"p:/ /sequel.jeremyevans.net/plugins.html 

Documenta*on h"p:/ /sequel.jeremyevans.net/documenta7on.html