Sequel - BRUG 21 Feb 2015

Transcript

1. Работа&с&базами&данных с"использованием"Sequel
3. Dependencies
5. 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
6. Database # memory database DB = Sequel.sqlite # connection string DB = Sequel.connect('postgres://host/database')
7. Database DB.create_table(:users) do primary_key :id String :name end
8. Migra&ons Sequel.migration do change do create_table(:users) do primary_key :id String :name end end end
9. Dataset dataset = DB[:users] # SELECT * FROM "table" dataset = DB.from(:users) # SELECT * FROM "table" dataset = DB.select(:column) # SELECT "column"
10. Dataset dataset = DB[:test]. select(:first_name, :last_name). where(country: 'BY'). order(:created_at)
11. Dataset dataset.first dataset.all dataset.each { |row| row }
12. Dataset dataset.insert(last_name: 'Test') dataset.update(last_name: 'Test') dataset.delete
13. Virtual(Row(Blocks dataset.where(Sequel.expr(:a) > Sequel.function(:b, :c)) # WHERE a > b(c) dataset.where { a > b(c) }
14. Model class User < Sequel::Model one_to_many :posts end
15. Model • many_to_one • one_to_many • one_to_one • many_to_many • one_through_one
16. Model class User < Sequel::Model def validate super validates_presence :name end end
17. Model class User < Sequel::Model def before_create # do something super end def after_update super # do something end end
18. Model user = User[1234] user.name = 'Test' user.save
19. Types • pg_array • pg_hstore • pg_inet • pg_interval • pg_json • pg_range
20. 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
21. Constraints create_table(:users) do primary_key :id String :name constraint(:name_min_length) { char_length(name) > 2 } end
22. Indexes alter_table(:users) do add_index Sequel.function(:lower, :name), name: :lower_name end # CREATE INDEX "lower_name" ON "users" (lower("name"))
23. 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()
24. 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"
25. sequel_postgresql_triggers • pgt_created_at • pgt_updated_at • pgt_counter_cache • pgt_sum_cache • pgt_immutable • pgt_touch
26. Window'Func+ons SELECT lag(timestamp) OVER (ORDER BY timestamp), timestamp FROM events
27. Window'Func+ons DB[:events] .select { [lag(timestamp).over(order: :timestamp), timestamp] }
28. 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])
29. Recursive)Common)Table) Expressions
30. rcte_tree • h#p:/ /sequel.jeremyevans.net/rdoc8plugins/classes/Sequel/ Plugins/RcteTree.html • h#p:/ /explainextended.com/2009/09/24/adjacency8list8vs8 nested8sets8postgresql/
31. 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')
32. 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| }
33. Streaming gem 'sequel_pg' DB.extension(:pg_streaming) DB[:table].stream.each { |row| } DB[:table].paged_each { |row| }
34. Plugins h"p:/ /sequel.jeremyevans.net/plugins.html
35. Documenta*on h"p:/ /sequel.jeremyevans.net/documenta7on.html