Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Separate the data logic from your application

703496dd6f9097efaa51f2b8c83b47ac?s=47 aquajach
October 17, 2016

Separate the data logic from your application

703496dd6f9097efaa51f2b8c83b47ac?s=128

aquajach

October 17, 2016
Tweet

Transcript

  1. Separate The Data Logic From Your Application Jack Chen @aquajach

  2. None
  3. None
  4. None
  5. None
  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" } ] }
  15. None
  16. ORM Works...but

  17. None
  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
  19. None
  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