Separate the data logic from your application

Transcript

1. Separate The Data Logic From Your Application Jack Chen @aquajach
6. Object Relational Mapper (ORM) Ruby - Rails - Active Record Python - Django - QuerySets PHP - Laravel - Eloquent
7. Why ORM?
8. ORM Example class User < ActiveRecord::Base validates :email, format: { with: /\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\z/i, on: :create } end
9. ORM Example def index @users = User.all end def show @user = User.where('email = ? OR phone = ?', params[:login], params[:login]).first end
10. ORM Example def create @user = User.new(params) if @user.save render 'show' else @errors = @user.errors render 'error' end end
11. ORM Example json.data do json.name user.name json.email user.email json.phone user.phone json.registered_at user.created_at end
12. ORM Example json.errors do json.array! @errors.full_messages do |message| json.message message end end
13. ORM Example Get http://your_host/user.json?login=jack@chen.com Response: { "name": "Jack Chen", "email": "jack@chen.com", "phone": "+85266669999", "registered_at": "2016-10-09T07:13:00.338133" }
14. ORM Example Post http://your_host/users.json Request: { "user": { "name": "Jack", "email": “jack@invalid_email”, "phone": "+86139131239123" } } Response: { "errors": [ { "message": "Email is invalid" } ] }
16. ORM Works...but
18. class Carnival def list_members_by_prize(prize) self.joins('INNER JOIN booths on booth.carnival_id = carnivals.id, INNER JOIN games ...') .select('...') .group('...') .where('...') .order('...') end end
20. Is DB only a storage?
21. Stored Procedures SQL
22. PostgreSQL Example create table users( name varchar, email varchar UNIQUE CONSTRAINT valid_email CHECK (email ~ '\A\S+@\S+\.\S+\Z'), phone varchar CONSTRAINT valid_phone CHECK (phone ~ '\A\+\d+\Z'), created_at timestamp default now() not null, updated_at timestamp default now() not null );
23. PostgreSQL Example create type user_json as ( name varchar(255), email varchar(255), phone varchar(255), registered_at TIMESTAMP );
24. PostgreSQL Example -- Get all users create function all_users(OUT data jsonb) as $$ DECLARE user_info user_json[]; BEGIN user_info := array_agg(s) from (select name, email, phone, created_at from users) as s; data := array_to_json(user_info); END; $$ LANGUAGE PLPGSQL;
25. PostgreSQL Example -- Get a user create function get_user(login VARCHAR, OUT data jsonb) as $$ DECLARE user_info user_json; BEGIN select * from users where email = login or phone = login limit 1 into user_info; data := row_to_json(user_info); END; $$ LANGUAGE PLPGSQL;
26. PostgreSQL Example -- Create a user create function create_user(new_name VARCHAR, new_email varchar, new_phone varchar, OUT data jsonb) as $$ BEGIN insert into users(name, email, phone) values(new_name, new_email, new_phone); data := get_user(new_email); Exception when integrity_constraint_violation then if sqlerrm like '%valid_phone%' then RAISE 'The phone format is invalid' USING CONSTRAINT = 'valid_phone'; ELSIF sqlerrm like '%valid_email%' then RAISE 'The email format is invalid' USING CONSTRAINT = 'valid_email'; END IF; END; $$ LANGUAGE PLPGSQL;
27. def query(sql, params = []) @db = PG::Connection.new(dbname: 'plsql_orm_dev') @db.exec_params("select data from #{sql}", params).first['data'] end
28. def index render json: query('all_users()') end def show render json: query('get_user($1)', [params[:login]]) end def create render json: query('create_user($1, $2, $3)', [user_params[:name], user_params[:email], user_params[:phone]]) end
29. PL/pgSQL, PL/Perl, PL/Python, PL/V8, PL/Java, PL/Ruby
30. create extension plv8; create or replace function print() returns VARCHAR as $$ return 'Hong Kong Code Conf is' + (false == [] ? '' : ' not') + ' awesome'; $$ LANGUAGE plv8; select print();
31. create extension plv8; create or replace function print() returns VARCHAR as $$ return 'Hong Kong Code Conf is' + (false == [] ? '' : ' not') + ' awesome'; $$ LANGUAGE plv8; select print(); // => “Hong Kong Code Conf is awesome!”
32. create or replace function plruby_call_handler() returns language_handler as ‘../plruby.bundle’ language C; create or replace language plruby handler plruby_call_handler;
33. CREATE OR REPLACE FUNCTION encrypt_code(data_string varchar, key_string varchar) returns varchar as $$ require “base64" require “openssl” cipher = OpenSSL::Cipher.new("AES-128-CBC") cipher.encrypt cipher.key = key_string crypt = cipher.update(data_string) + cipher.final crypt_string = (Base64.urlsafe_encode64(crypt)) return crypt_string $$ language plruby; select encrypt_code('-1476244774', 'dE2fhq/fD4f8Lq/4qoMXLg');
34. PostgreSQL extensions • Rust: https://github.com/thehydroimpulse/postgres-extension.rs • Python: Psycopg: http://initd.org/psycopg/docs/index.html
35. Deployment • Source Control vs Not Source Control • Deployment Hook
36. Data Logic vs Business Logic
37. Simple vs Complex
38. Don’t forget the hidden costs!
39. Fast vs Slow
40. Wrap Up