Tweet
Tweet

Slide 1

Slide 1 text

BEYOND GOOD & ORMS Uncovering The Hidden Features of Your Database @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 2

Slide 2 text

Friday, March 27, 15

Slide 3

Slide 3 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 4

Slide 4 text

@kerrizor Ancient City Ruby 2015 Globe Theater David Welch CC BY-NC-SA 2.0 https://www.flickr.com/photos/dlnwelch/6000888348 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 5

Slide 5 text

@kerrizor Ancient City Ruby 2015 Unattributed @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 6

Slide 6 text

@kerrizor Ancient City Ruby 2015 Romeo + Juliet Postcard Michelle B. CC BY-ND 2.0 https://www.flickr.com/photos/42176496@N05/3903316620 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 7

Slide 7 text

@kerrizor Ancient City Ruby 2015 Romeo & Juliet Anna Magal CC BY-NC 2.0 https://www.flickr.com/photos/annamagal/6282984898 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 8

Slide 8 text

Lead Software Development Engineer @kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 9

Slide 9 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 10

Slide 10 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 11

Slide 11 text

@kerrizor Ancient City Ruby 2015 User.all do |user| puts user.first_name puts user.email puts user.birthday end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 12

Slide 12 text

@kerrizor Ancient City Ruby 2015 Hammer and nails Anders Illum CC BY-NC-ND 2.0 https://www.flickr.com/photos/aai/6936657289 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 13

Slide 13 text

@kerrizor Ancient City Ruby 2015 falling down barn Becca CC BY-ND 2.0 https://www.flickr.com/photos/51845556@N00/136735885 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 14

Slide 14 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 15

Slide 15 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 16

Slide 16 text

@kerrizor Ancient City Ruby 2015 Kuroshio Sea - 2nd largest aquarium tank in the world Jon Rawlinson CC BY 2.0 https://www.flickr.com/photos/london/3745603114 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 17

Slide 17 text

@kerrizor Ancient City Ruby 2015 Views “A view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions.” Kuroshio Sea - 2nd largest aquarium tank in the world Jon Rawlinson CC BY 2.0 https://www.flickr.com/photos/london/3745603114 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 18

Slide 18 text

@kerrizor Ancient City Ruby 2015 id name 1 East Coast 2 Midwest 3 Alaska 4 Hawaii restaurant_i d region_id 1 1 1 2 1 3 2 1 2 4 3 3 3 4 id name annual_sales 1 Thai Tanic 120,000 2 Lard Have Mercy 130,000 3 Grillenium Falcon 135,000 4 La Fishcotheque 87,000 restaurants restaurants_regions regions @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 19

Slide 19 text

@kerrizor Ancient City Ruby 2015 Restaurant.all.each do |r| puts r.name puts r.annual_sales puts r.regions.first.name end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 20

Slide 20 text

@kerrizor Ancient City Ruby 2015 Restaurant.all.include(:regions).each do |r| puts r.name puts r.annual_sales puts r.regions.first.name end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 21

Slide 21 text

@kerrizor Ancient City Ruby 2015 Restaurant.all.include(:regions, :managers).each do |r| puts r.name puts r.annual_sales puts r.regions.first.name puts "Managers:" puts "---------" r.managers.each do |manager| puts " " + manager.name end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 22

Slide 22 text

@kerrizor Ancient City Ruby 2015 SELECT "restaurants".* FROM "restaurants" SELECT "restaurants_regions".* FROM "restaurants_regions" WHERE "restaurants_regions"."restaurant_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50) SELECT "regions".* FROM "regions" WHERE "regions"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) SELECT "restaurants_managers".* FROM "restaurants_managers" WHERE "restaurants_managers"."restaurant_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50) SELECT "managers".* FROM "managers" WHERE "managers"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100) @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 23

Slide 23 text

@kerrizor Ancient City Ruby 2015 SELECT "restaurants"."id" AS t0_r0, "restaurants"."name" AS t0_r1, "restaurants"."annual_sales" AS t0_r2, "restaurants"."created_at" AS t0_r3, "restaurants"."updated_at" AS t0_r4, "regions"."id" AS t1_r0, "regions"."name" AS t1_r1, "regions"."created_at" AS t1_r2, "regions"."updated_at" AS t1_r3, "managers"."id" AS t2_r0, "managers"."name" AS t2_r1, "managers"."assistant" AS t2_r2, "managers"."created_at" AS t2_r3, "managers"."updated_at" AS t2_r4 FROM "restaurants" LEFT OUTER JOIN "restaurants_regions" ON "restaurants_regions"."restaurant_id" = "restaurants"."id" LEFT OUTER JOIN "regions" ON "regions"."id" = "restaurants_regions"."region_id" LEFT OUTER JOIN "restaurants_managers" ON "restaurants_managers"."restaurant_id" = "restaurants"."id" LEFT OUTER JOIN "managers" ON "managers"."id" = "restaurants_managers"."manager_id" @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 24

Slide 24 text

@kerrizor Ancient City Ruby 2015 + + @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 25

Slide 25 text

@kerrizor Ancient City Ruby 2015 class CreateRestaurantRegionManagerReports < ActiveRecord::Migration def up ActiveRecord::Base.connection.execute('CREATE VIEW restaurant_region_manager_reports AS SELECT r.name AS restaurant_name, re.name AS region_name, m.name AS manager_name, annual_sales FROM restaurants r JOIN restaurants_regions rd ON r.id = rd.restaurant_id JOIN regions re ON rd.region_id = re.id JOIN restaurants_managers rm ON r.id = rm.restaurant_id JOIN managers m ON rm.manager_id = m.id;') end def down ActiveRecord::Base.connection.execute('DROP VIEW restaurant_region_manager_reports') end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 26

Slide 26 text

@kerrizor Ancient City Ruby 2015 RestaurantRegionManagerReport.each do |record| puts record.restaurant_name puts record.annual_sales puts record.region_name puts record.manager_name end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 27

Slide 27 text

@kerrizor Ancient City Ruby 2015 Broxigar's Aquarium Scott Akerman CC BY 2.0 https://www.flickr.com/photos/sterlic/3193335016 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 28

Slide 28 text

@kerrizor Ancient City Ruby 2015 Materialized Views Broxigar's Aquarium Scott Akerman CC BY 2.0 https://www.flickr.com/photos/sterlic/3193335016 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 29

Slide 29 text

@kerrizor Ancient City Ruby 2015 "Duck-Rabbit illusion". Licensed under Public Domain via Wikimedia Commons http://commons.wikimedia.org/wiki/File:Duck-Rabbit_illusion.jpg#/media/File:Duck-Rabbit_illusion.jpg @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 30

Slide 30 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 31

Slide 31 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 32

Slide 32 text

@kerrizor Ancient City Ruby 2015 Today's Specials Jon Cockley CC BY-NC 2.0 https://www.flickr.com/photos/jonnycocker/3422187692 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 33

Slide 33 text

@kerrizor Ancient City Ruby 2015 Stored Procedures Today's Specials Jon Cockley CC BY-NC 2.0 https://www.flickr.com/photos/jonnycocker/3422187692 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 34

Slide 34 text

@kerrizor Ancient City Ruby 2015 Stored Procedures SPs are like onions. They can really improve a dish, but I wouldn't want a dish that was 90% onions. Also, why are there onions in my ice cream? Today's Specials Jon Cockley CC BY-NC 2.0 https://www.flickr.com/photos/jonnycocker/3422187692 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 35

Slide 35 text

@kerrizor Ancient City Ruby 2015 “Kerri, it can execute javascript? Does that mean I can load my node.js application into Postgres and have it run there?!” Today's Specials Jon Cockley CC BY-NC 2.0 https://www.flickr.com/photos/jonnycocker/3422187692 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 36

Slide 36 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 37

Slide 37 text

@kerrizor Ancient City Ruby 2015 > CREATE OR REPLACE FUNCTION plv8_test(keys text[], vals text[]) RETURNS text AS $$ var o = {}; for(var i=0; i

Slide 38

Slide 38 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 39

Slide 39 text

@kerrizor Ancient City Ruby 2015 CREATE OR REPLACE FUNCTION log_purchase() RETURNS trigger AS $$ DECLARE BEGIN INSERT INTO logs (purchase_id, user_id, total_amount) VALUES (NEW.purchase_id, NEW.user_id, NEW.total_amount); RETURN NEW; END; $$ LANGUAGE plpgsql; @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 40

Slide 40 text

@kerrizor Ancient City Ruby 2015 CREATE TRIGGER log_purchases AFTER INSERT ON purchases FOR EACH ROW EXECUTE PROCEDURE log_purchase(); @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 41

Slide 41 text

@kerrizor Ancient City Ruby 2015 AR Default Data Types • :primary_key • :string • :text • :integer • :float • :decimal • :datetime • :time • :date • :binary • :boolean @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 42

Slide 42 text

@kerrizor Ancient City Ruby 2015 Solar Array Pattern Amaresh Sundaram Kuppuswamy CC BY-NC-SA 2.0 https://www.flickr.com/photos/zaldoe/3066243656 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 43

Slide 43 text

@kerrizor Ancient City Ruby 2015 Solar Array Pattern Amaresh Sundaram Kuppuswamy CC BY-NC-SA 2.0 https://www.flickr.com/photos/zaldoe/3066243656 Arrays @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 44

Slide 44 text

@kerrizor Ancient City Ruby 2015 title price inventory description :string :decimal :integer :text products @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 45

Slide 45 text

@kerrizor Ancient City Ruby 2015 title price inventory description colors Shirt 24.95 64 “Sweet swag!” Red, Blue, Green products @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 46

Slide 46 text

@kerrizor Ancient City Ruby 2015 # shirt.colors = "Red, Blue, Green" shirt_colors = shirt.colors.split(",").map{ |color| color.strip } @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 47

Slide 47 text

@kerrizor Ancient City Ruby 2015 $ rails c 2.1.5 :001 > product = Product.first Product Load (0.6ms) SELECT "products".* FROM "products" ORDER BY "products"."id" ASC LIMIT 1 => #, inventory: 64, description: "Sweet swag!", colors: ["Red", "Blue", "Green"]> 2.1.5 :002 > product.colors.class => Array @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 48

Slide 48 text

@kerrizor Ancient City Ruby 2015 $ rails c 2.1.5 :010 > Product.where('colors @> ARRAY[?]', ["Red"]) Product Load (0.4ms) SELECT "products".* FROM "products" WHERE (colors @> ARRAY['Red']) => #, inventory: 64, description: "Sweet swag!", colors: ["Red", "Blue", "Green"]>]> @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 49

Slide 49 text

@kerrizor Ancient City Ruby 2015 2.1.5 :010 > Product.where('colors @> ARRAY[?]', ["Blue", "Red"]) Product Load (0.4ms) SELECT "products".* FROM "products" WHERE (colors @> ARRAY['Blue','Red']) => #, inventory: 64, description: "Sweet swag!", colors: ["Red", "Blue", "Green"]>]> @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 50

Slide 50 text

@kerrizor Ancient City Ruby 2015 2.1.5 :010 > Product.where('colors @> ARRAY[?]', ["Blue", "Red"]) Product Load (0.4ms) SELECT "products".* FROM "products" WHERE (colors @> ARRAY['Blue','Red']) => #, inventory: 64, description: "Sweet swag!", colors: ["Red", "Blue", "Green"]>]> @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 51

Slide 51 text

@kerrizor Ancient City Ruby 2015 class Product < ActiveRecord::Base scope :any_of, -> (colors){where('colors && ARRAY[?]', colors)} scope :all_of -> (colors){where('colors @> ARRAY[?]', colors)} end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 52

Slide 52 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 53

Slide 53 text

@kerrizor Ancient City Ruby 2015 class CreateProducts < ActiveRecord::Migration def change create_table :products do |t| t.string :title t.decimal :price t.integer :inventory t.text :description t.text :colors, array: true, default: [] end add_index(:products, :colors, :using => 'gin') end end Friday, March 27, 15

Slide 54

Slide 54 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 55

Slide 55 text

@kerrizor Ancient City Ruby 2015 class AddHstore < ActiveRecord::Migration def up execute 'CREATE EXTENSION hstore' end def down execute 'DROP EXTENSION hstore' end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 56

Slide 56 text

@kerrizor Ancient City Ruby 2015 class AddHstoreColumnToProducts < ActiveRecord::Migration def change add_column :products, :color_inventory, :hstore end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 57

Slide 57 text

@kerrizor Ancient City Ruby 2015 2.1.5 :010 > Product.where("color_inventory -> 'Blue' > '0'") @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 58

Slide 58 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 59

Slide 59 text

@kerrizor Ancient City Ruby 2015 class AddJsonColumnToProducts < ActiveRecord::Migration def change add_column :products, :inventory, :json end end @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 60

Slide 60 text

@kerrizor Ancient City Ruby 2015 2.1.5 :010 > Product.where("inventory -> 'Blue' > '0'") 2.1.5 :010 > Product.where("inventory ->> 'Blue' > '0'") @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 61

Slide 61 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 62

Slide 62 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 63

Slide 63 text

@kerrizor Ancient City Ruby 2015 • Range • Money • Composites • ENUMs • IP Addresses (INET/CIDR) • UUID • Geospatial records and calculations @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 64

Slide 64 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 65

Slide 65 text

@kerrizor Ancient City Ruby 2015 “Now is the winter of our discontent Made glorious summer by this sun of York;” @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 66

Slide 66 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 67

Slide 67 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 “They made the store disappear. AMAZING!” Ernie Miller - Used Without Permission https://twitter.com/erniemiller/status/580806706306904065 Friday, March 27, 15

Slide 68

Slide 68 text

@kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 69

Slide 69 text

math (and premise): http://abstrusegoose.com/474 1 + 1 = 2 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 70

Slide 70 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 71

Slide 71 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 72

Slide 72 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 73

Slide 73 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15

Slide 74

Slide 74 text

@kerrizor Ancient City Ruby 2015 @kerrizor Ancient City Ruby 2015 Friday, March 27, 15