"Back to the future" with SQL and stored procedures

Slide 1

Slide 1 text

Norman Clarke Business Vision Ruby Labs @compay Volver al futuro con SQL y stored procedures

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

Lo que les espera... • Características, ventajas y desventajas de stored procedures • Una librería experimental basada en stored procedures • Stored procedures vs. ORM

Slide 6

Slide 6 text

Stored Procedures programación "real" con SQL

Slide 7

Slide 7 text

postgres=# select greeting(); greeting ------------- hello world! (1 row)

Slide 8

Slide 8 text

1 CREATE FUNCTION greeting() 2 RETURNS TEXT AS $$ 3 BEGIN 4 RETURN 'hello world!'; 5 END; 6 $$ LANGUAGE 'plpgsql';

Slide 9

Slide 9 text

declare begin select ... if ... then update ... else while ... loop ... end loop; end if; return ...; end;

Slide 10

Slide 10 text

¿Cuántas funciones? Postgres: 2333 Lua: 135

Slide 11

Slide 11 text

Ventajas

Slide 12

Slide 12 text

Cacheo automático de consultas

Slide 13

Slide 13 text

Menos coordinación entre la BD y Ruby O Java, Python, Perl, PHP, etc.

Slide 14

Slide 14 text

Encapsulación del esquema Stored Procedures Esquema Ruby

Slide 15

Slide 15 text

Toda la lógica de negocios en SQL

Slide 16

Slide 16 text

No content

Slide 17

Slide 17 text

No hagan eso por favor

Slide 18

Slide 18 text

Squirm github.com/bvision/squirm

Slide 19

Slide 19 text

Squirm • Azúcar sintáctico para la gema "pg" • Connection pool básico • Stored procedures como procs o lambdas

Slide 20

Slide 20 text

1 Squirm.connect host: "localhost" 2 Squirm.transaction do 3 Squirm.exec "SELECT ..." do |result| 4 result.to_a 5 end 6 Squirm.rollback 7 end

Slide 21

Slide 21 text

1 Squirm do 2 connect host: "localhost" 3 transaction do 4 exec "SELECT ..." do |result| 5 result.to_a 6 end 7 rollback 8 end 9 end

Slide 22

Slide 22 text

1 Squirm do 2 exec "CREATE FUNCTION ..." 3 proc = procedure "greeting" 4 proc.call "Juan" 5 #=> "¡hola Juan!" 6 end

Slide 23

Slide 23 text

1 class Foo 2 3 @@bar = Procedure.load "bar" 4 5 def bar(*args) 6 @@bar.call(*args) 7 end 8 end 9 10 foo = Foo.new 11 foo.bar("hello")

Slide 24

Slide 24 text

GET followers/ids GET friends/ids GET lists/all GET favorites GET statuses/home_timeline GET statuses/mentions GET statuses/user_timeline GET direct_messages

Slide 25

Slide 25 text

SELECT followers.ids() SELECT friends.ids() SELECT lists.all() SELECT favorites() SELECT statuses.home_timeline() SELECT statuses.mentions() SELECT statuses.user_timeline() SELECT direct_messages()

Slide 26

Slide 26 text

Squirm Model github.com/bvision/squirm_model

Slide 27

Slide 27 text

Squirm Model • Generador de tablas, procedures • SQL "scaffolding" • Active Model

Slide 28

Slide 28 text

$ squirm table person id email birth_date access_time bio 1 CREATE TABLE "person" ( 2 "id" SERIAL NOT NULL PRIMARY KEY, 3 "email" VARCHAR(64) NOT NULL UNIQUE, 4 "birth_date" DATE, 5 "access_time" TIMESTAMP WITH TIME ZONE, 6 "bio" TEXT 7 );

Slide 29

Slide 29 text

$ squirm table person id created_at 1 CREATE TABLE "person" ( 2 "id" SERIAL NOT NULL PRIMARY KEY, 3 "created_at" TIMESTAMP WITH TIME ZONE NOT NULL 4 ); 5 6 CREATE OR REPLACE FUNCTION "update_person_created_at_timestamp" 7 RETURNS TRIGGER AS $$ 8 BEGIN 9 NEW.created_at = NOW(); 10 RETURN NEW; 11 END; 12 $$ LANGUAGE 'plpgsql'; 13 14 CREATE TRIGGER "update_person_created_at_timestamp" 15 BEFORE INSERT ON "person" 16 FOR EACH ROW EXECUTE PROCEDURE "update_person_created_at_time

Slide 30

Slide 30 text

$ squirm table person id email --api CREATE TABLE "person" ... CREATE SCHEMA "person" ... CREATE FUNCTION "person.get" ... CREATE FUNCTION "person.create" ... CREATE FUNCTION "person.update" ... CREATE FUNCTION "person.delete" ...

Slide 31

Slide 31 text

1 class Person 2 extend Squirm::Model ... 3 validates_presence_of :name 4 end 5 6 Person.create(...) 7 @person = Person.find(1) 8 @person.valid? 9 @person.to_json 10 redirect_to @person

Slide 32

Slide 32 text

1 class Person 2 extend Squirm::Model 3 4 sample do |s| 5 s.id = 1 6 s.name = "Juan Fulano" 7 end 8 9 validates_presence_of :name 10 end

Slide 33

Slide 33 text

1 class PersonTest < Test::Unit::TestCase 2 def test_create 3 assert Person.create(Person.sample) 4 end 5 end

Slide 34

Slide 34 text

1 Squirm do 2 connect host: "localhost" 3 4 exec Person.to_ddl 5 6 Person.finalize 7 8 p = Person.create name: "John" 9 p.update name: "Johnny" 10 p.delete 11 end

Slide 35

Slide 35 text

ROFLSCALE 0 7.5 15 22.5 30 Squirm Model ActiveRecord Benchmarks

Slide 36

Slide 36 text

¿Por qué no usar un ORM?

Slide 37

Slide 37 text

Usen los ORM • Active Record • DataMapper • Sequel • otros

Slide 38

Slide 38 text

Pero conozcan sus defectos

Slide 39

Slide 39 text

Exhibition.all( :run_time.gt => 2, :run_time.lt => 5 ) run_time > 1 AND run_time < 5 ...you might be wondering how we can specify conditions beyond equality without resorting to SQL. Well, thanks to some clever additions to the Symbol class, it’s easy!

Slide 40

Slide 40 text

table = Product.arel_table Product.where( table[:price].eq(2.99). or(table[:name].matches("%foo")) ).to_sql #=> "WHERE price = 2.99 OR name LIKE '%foo'" railscasts.com/episodes/215-advanced-queries-in-rails-3

Slide 41

Slide 41 text

SQL (mal) generado

Slide 42

Slide 42 text

La abstracción diﬁculta el uso de features avanzados

Slide 43

Slide 43 text

Los stored procedures ofrecen una alternativa interesante

Slide 44

Slide 44 text

"Pensemos diferente"

Slide 45

Slide 45 text

Nihil sub sole novum

Slide 46

Slide 46 text

Postgres y MySQL están muy desaprovechados

Slide 47

Slide 47 text

No usemos abstracciones innecesarias

Slide 48

Slide 48 text

¡Gracias!

Slide 49

Slide 49 text

No content

Slide 50

Slide 50 text

Obelisco: ﬂickr.com/photos/budgetplaces/4173902613/ Matz: ﬂickr.com/photos/rrrodrigo/2394122680/ Gracias, fotógrafos