Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

Dependencies

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

Model • many_to_one • one_to_many • one_to_one • many_to_many • one_through_one

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

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()

Slide 24

Slide 24 text

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"

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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])

Slide 29

Slide 29 text

Recursive)Common)Table) Expressions

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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')

Slide 32

Slide 32 text

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| }

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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