Arel. Why do we need it?

Arel. Why do we need it?

From time to time, I hear questions like "What's wrong with my SQL code?". The good idea is to use Arel instead of trying to re-invent the weel.

For a new Rails developers Arel appears a dark horse.

9986d4cfe597f7ce12331ffdd75f471f?s=128

Denis Yagofarov

October 25, 2013
Tweet

Transcript

  1. 1.

    Arel. Why do we need it? Denis Yagofarov | @denyago

    суббота, 2 ноября 13 г.
  2. 3.

    Writing SQL is dead simple! INNER JOIN "post_subscriptions" as "post_subscriptions_2"

    ON "post_subscriptions"."user_id" = "post_subscriptions_2"."source_id" AND "post_subscriptions_2"."user_id" = "post_subscriptions"."source_id" суббота, 2 ноября 13 г.
  3. 4.

    Writing SQL is dead simple! INNER JOIN "post_subscriptions" as "post_subscriptions_2"

    ON "post_subscriptions"."user_id" = "post_subscriptions_2"."source_id" AND "post_subscriptions_2"."user_id" = "post_subscriptions"."source_id" ... And fun! суббота, 2 ноября 13 г.
  4. 6.

    If you know, what you are doing. • domain model

    суббота, 2 ноября 13 г.
  5. 7.

    If you know, what you are doing. • domain model

    • it’s mapping to DB суббота, 2 ноября 13 г.
  6. 8.

    If you know, what you are doing. • domain model

    • it’s mapping to DB • and the mapping is simple суббота, 2 ноября 13 г.
  7. 11.

    Don’t we want to build beautiful DSL, and not maintain

    old ? суббота, 2 ноября 13 г.
  8. 15.

    SELECT * FROM "users" WHERE "id" = 1; «In the

    Relational Algebra, there are no queries per se; everything is either a relation or an operation on a relation» http://web.archive.org/web/20120718093140/http://magicscalingsprinkles.wordpress.com/2010/01/28/why-i-wrote-arel/ Why Arel? by Nathan Kallen суббота, 2 ноября 13 г.
  9. 18.

    Arel • Simplifies the generation of complex SQL queries •

    Adapts to various RDBMS systems суббота, 2 ноября 13 г.
  10. 19.

    Arel • Simplifies the generation of complex SQL queries •

    Adapts to various RDBMS systems • Might be faster, than ActiveRecord.create! суббота, 2 ноября 13 г.
  11. 20.

    Arel • Simplifies the generation of complex SQL queries •

    Adapts to various RDBMS systems • Might be faster, than ActiveRecord.create! • No worries on escaping (i.e. Security) суббота, 2 ноября 13 г.
  12. 23.

    «It is intended to be a framework framework: that is,

    you can build your own ORM with it, focusing on innovative object and collection modeling as opposed to database compatibility and query generation.» https://github.com/rails/arel суббота, 2 ноября 13 г.
  13. 24.

    «It is intended to be a framework framework: that is,

    you can build your own ORM with it, focusing on innovative object and collection modeling as opposed to database compatibility and query generation.» суббота, 2 ноября 13 г.
  14. 25.

    «It is intended to be a framework framework: that is,

    you can build your own ORM with it, focusing on innovative object and collection modeling as opposed to database compatibility and query generation.» суббота, 2 ноября 13 г.
  15. 29.

    Staff.with_roles(:kill_people, :save_britan).first #=> #<Staff id: 007, user_id: 1, user_full_name: 'James

    Bond'> Do it simple and self-descriptive суббота, 2 ноября 13 г.
  16. 30.

    Staff.with_roles(:kill_people, :save_britan).first #=> #<Staff id: 007, user_id: 1, user_full_name: 'James

    Bond'> Do it simple and self-descriptive Not on a low-level суббота, 2 ноября 13 г.
  17. 31.

    Staff.with_roles(:kill_people, :save_britan).first #=> #<Staff id: 007, user_id: 1, user_full_name: 'James

    Bond'> SELECT "staff".* FROM "staff" WHERE ( ("staff"."role_mask" & 8 != 0 OR "staff"."role_mask" & 4 != 0) ) Do it simple and self-descriptive Not on a low-level суббота, 2 ноября 13 г.
  18. 32.

    ActiveRecord::Base.connection.execute %q{ SELECT "staff".* FROM "staff" WHERE ( ("staff"."role_mask" &

    8 != 0 OR 'staff'."role_mask" & 4 != 0) )} Any mistakes? суббота, 2 ноября 13 г.
  19. 33.

    ActiveRecord::Base.connection.execute %q{ SELECT "staff".* FROM "staff" WHERE ( ("staff"."role_mask" &

    8 != 0 OR 'staff'."role_mask" & 4 != 0) )} Any mistakes? ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "." LINE 1: ..." WHERE (("staff"."role_mask" & 8 != 0 OR 'staff'."role_mas... суббота, 2 ноября 13 г.
  20. 34.

    ActiveRecord::Base.connection.execute %q{ SELECT "staff".* FROM "staff" WHERE ( ("staff"."role_mask" &

    8 != 0 OR 'staff'."role_mask" & 4 != 0) )} Any mistakes? ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "." LINE 1: ..." WHERE (("staff"."role_mask" & 8 != 0 OR 'staff'."role_mas... Arrr!!! Not again! суббота, 2 ноября 13 г.
  21. 35.

    And now... mistakes? ActiveRecord::Base.connection.execute %q{ SELECT "staff".* FROM "staff" WHERE

    ( ("staff"."role_mask" & 8 != 0 OR "staff"."role_mask" & 4 != 0) )} суббота, 2 ноября 13 г.
  22. 36.

    And now... mistakes? ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in

    your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.* FROM "staff" WHERE (("staff"."role_mask" & 8 != 0 OR "staff"."role_mask" & 4' at line 1: SELECT "staff".* FROM "staff" WHERE (("staff"."role_mask" & 8 != 0 OR "staff"."role_mask" & 4 != 0)) ActiveRecord::Base.connection.execute %q{ SELECT "staff".* FROM "staff" WHERE ( ("staff"."role_mask" & 8 != 0 OR "staff"."role_mask" & 4 != 0) )} суббота, 2 ноября 13 г.
  23. 37.

    And now... mistakes? ... ActiveRecord::StatementInvalid: Mysql2::Error: You have an error

    in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.* FROM "staff" WHERE (("staff"."role_mask" & 8 != 0 OR "staff"."role_mask" & 4' at line 1: SELECT "staff".* FROM "staff" WHERE (("staff"."role_mask" & 8 != 0 OR "staff"."role_mask" & 4 != 0)) ActiveRecord::Base.connection.execute %q{ SELECT "staff".* FROM "staff" WHERE ( ("staff"."role_mask" & 8 != 0 OR "staff"."role_mask" & 4 != 0) )} суббота, 2 ноября 13 г.
  24. 38.

    Do you remember? «framework framework» «build your own ORM» def

    role_in(roles) roles = ( roles.map(&:to_sym) & Staff::ROLES.keys ) return self.scoped if roles.empty? self.where(condition_for_roles(roles, column: :role_mask)) end суббота, 2 ноября 13 г.
  25. 39.

    Do you remember? «framework framework» «build your own ORM» def

    condition_for_roles(roles, options) r = roles.dup condition = role_present(r.shift, options[:column]) r.inject(condition) do |cond, role| cond.or(role_present(role, options[:column])) end end суббота, 2 ноября 13 г.
  26. 40.

    Do you remember? «framework framework» «build your own ORM» def

    role_present(role, column) (Arel::Nodes::InfixOperation.new "&", self.arel_table[column.to_sym], (2**Staff::ROLES[role.to_sym]) ).not_eq(0) end суббота, 2 ноября 13 г.
  27. 42.

    In a non-15-minute- blog apps you • have complex DB

    schema (we had ~20 tables and thousands of join’s) суббота, 2 ноября 13 г.
  28. 43.

    In a non-15-minute- blog apps you • have complex DB

    schema (we had ~20 tables and thousands of join’s) • want to port the app to different DBs (our social intranet was running on PostgreSQL, MySQL, whatever they had) суббота, 2 ноября 13 г.
  29. 44.

    In a non-15-minute- blog apps you • have complex DB

    schema (we had ~20 tables and thousands of join’s) • want to port the app to different DBs (our social intranet was running on PostgreSQL, MySQL, whatever they had) • describe complex things with simple code суббота, 2 ноября 13 г.
  30. 45.

    arel = User.arel_table[:id].eq(1) n = 50000 Benchmark.bm(10) do |x| x.report('String:')

    { n.times { User.where("id = ?", 1) }} x.report('Hash:') { n.times { User.where(id: 1) }} x.report('ARel exp:') { n.times { User.arel_table[:id].eq(1) }} x.report('ARel:') { n.times { User.where(arel) }} end Performance * Ruby 2.0.0p247, arel (4.0.0), activerecord (4.0.0) суббота, 2 ноября 13 г.
  31. 46.

    arel = User.arel_table[:id].eq(1) n = 50000 Benchmark.bm(10) do |x| x.report('String:')

    { n.times { User.where("id = ?", 1) }} x.report('Hash:') { n.times { User.where(id: 1) }} x.report('ARel exp:') { n.times { User.arel_table[:id].eq(1) }} x.report('ARel:') { n.times { User.where(arel) }} end Performance By logic... Who would be a winner? * Ruby 2.0.0p247, arel (4.0.0), activerecord (4.0.0) суббота, 2 ноября 13 г.
  32. 49.

    Wait-wait?! String Hash Arel 3.3 3.0 Arel 4 Arel 3.2

    суббота, 2 ноября 13 г.
  33. 50.

    Wait-wait?! String Hash Arel 3.3 3.0 1.4 Arel 4 Arel

    3.2 суббота, 2 ноября 13 г.
  34. 51.

    Wait-wait?! String Hash Arel 3.3 3.0 1.4 Arel(4.0.0) is 2

    times faster Arel 4 Arel 3.2 суббота, 2 ноября 13 г.
  35. 52.

    Wait-wait?! String Hash Arel 3.3 3.0 1.4 Arel(4.0.0) is 2

    times faster Arel 4 4.7 Arel 3.2 суббота, 2 ноября 13 г.
  36. 53.

    Wait-wait?! String Hash Arel 3.3 3.0 1.4 Arel(4.0.0) is 2

    times faster Arel 4 4.7 3.7 Arel 3.2 суббота, 2 ноября 13 г.
  37. 54.

    Wait-wait?! String Hash Arel 3.3 3.0 1.4 Arel(4.0.0) is 2

    times faster Arel 4 4.7 3.7 3.0 Arel 3.2 суббота, 2 ноября 13 г.
  38. 55.

    Result user system total real String: 3.280000 0.010000 3.290000 (

    3.311442) Hash: 2.930000 0.010000 2.940000 ( 2.962005) ARel exp: 0.110000 0.000000 0.110000 ( 0.111368) ARel: 1.290000 0.000000 1.290000 ( 1.323271) суббота, 2 ноября 13 г.
  39. 58.

    User.where('full_name = ?', "O'Connor").to_sql #=> "SELECT \"users\".* FROM \"users\" WHERE

    (full_name = 'O''Connor')" Good. It escapes. суббота, 2 ноября 13 г.
  40. 59.

    User.where('full_name = ?', "O'Connor").to_sql #=> "SELECT \"users\".* FROM \"users\" WHERE

    (full_name = 'O''Connor')" Good. It escapes. What if you don’t have ActiveRecord at ALL? суббота, 2 ноября 13 г.
  41. 60.

    User.where('full_name = ?', "O'Connor").to_sql #=> "SELECT \"users\".* FROM \"users\" WHERE

    (full_name = 'O''Connor')" Good. It escapes. What if you don’t have ActiveRecord at ALL? query = "INSERT OR REPLACE INTO postcodes(code, lat,lon) VALUES ( '#{code}', #{'%.5f' % lat}, #{'%.5f' % lon});" суббота, 2 ноября 13 г.
  42. 61.

    User.where('full_name = ?', "O'Connor").to_sql #=> "SELECT \"users\".* FROM \"users\" WHERE

    (full_name = 'O''Connor')" Good. It escapes. What if you don’t have ActiveRecord at ALL? query = "INSERT OR REPLACE INTO postcodes(code, lat,lon) VALUES ( '#{code}', #{'%.5f' % lat}, #{'%.5f' % lon});" oh yes... very straightforward! суббота, 2 ноября 13 г.
  43. 62.

    User.where('full_name = ?', "O'Connor").to_sql #=> "SELECT \"users\".* FROM \"users\" WHERE

    (full_name = 'O''Connor')" Good. It escapes. What if you don’t have ActiveRecord at ALL? query = "INSERT OR REPLACE INTO postcodes(code, lat,lon) VALUES ( '#{code}', #{'%.5f' % lat}, #{'%.5f' % lon});" oh yes... very straightforward! INSERT OR REPLACE INTO postcodes(code, lat,lon) VALUES ('2606 O'MALLEY', -35.35434, 149.10677); : near "MALLEY": syntax error суббота, 2 ноября 13 г.
  44. 64.

    Why not just ARὑ ? On 10_000 records: user system

    total real AR: 29.200000 2.690000 31.890000 ( 79.686113) AR, mass insert: 29.330000 2.870000 32.200000 ( 86.788195) AR, no validations: 11.850000 1.630000 13.480000 ( 32.592245) AR Import gem, models: 16.180000 0.780000 16.960000 ( 30.329767) AR Import gem, raw data: 12.430000 0.780000 13.210000 ( 28.032837) RAW SQL: 1.040000 0.370000 1.410000 ( 8.810435) суббота, 2 ноября 13 г.
  45. 65.

    Why not just ARὑ ? On 10_000 records: user system

    total real AR: 29.200000 2.690000 31.890000 ( 79.686113) AR, mass insert: 29.330000 2.870000 32.200000 ( 86.788195) AR, no validations: 11.850000 1.630000 13.480000 ( 32.592245) AR Import gem, models: 16.180000 0.780000 16.960000 ( 30.329767) AR Import gem, raw data: 12.430000 0.780000 13.210000 ( 28.032837) RAW SQL: 1.040000 0.370000 1.410000 ( 8.810435) x9 суббота, 2 ноября 13 г.
  46. 66.

    Why not just ARὑ ? On 10_000 records: user system

    total real AR: 29.200000 2.690000 31.890000 ( 79.686113) AR, mass insert: 29.330000 2.870000 32.200000 ( 86.788195) AR, no validations: 11.850000 1.630000 13.480000 ( 32.592245) AR Import gem, models: 16.180000 0.780000 16.960000 ( 30.329767) AR Import gem, raw data: 12.430000 0.780000 13.210000 ( 28.032837) RAW SQL: 1.040000 0.370000 1.410000 ( 8.810435) x9 x4 суббота, 2 ноября 13 г.
  47. 67.

    Why not just ARὑ ? On 10_000 records: user system

    total real AR: 29.200000 2.690000 31.890000 ( 79.686113) AR, mass insert: 29.330000 2.870000 32.200000 ( 86.788195) AR, no validations: 11.850000 1.630000 13.480000 ( 32.592245) AR Import gem, models: 16.180000 0.780000 16.960000 ( 30.329767) AR Import gem, raw data: 12.430000 0.780000 13.210000 ( 28.032837) RAW SQL: 1.040000 0.370000 1.410000 ( 8.810435) x9 x4 x3 суббота, 2 ноября 13 г.
  48. 68.

    Why not just ARὑ ? On 10_000 records: user system

    total real AR: 29.200000 2.690000 31.890000 ( 79.686113) AR, mass insert: 29.330000 2.870000 32.200000 ( 86.788195) AR, no validations: 11.850000 1.630000 13.480000 ( 32.592245) AR Import gem, models: 16.180000 0.780000 16.960000 ( 30.329767) AR Import gem, raw data: 12.430000 0.780000 13.210000 ( 28.032837) RAW SQL: 1.040000 0.370000 1.410000 ( 8.810435) x9 x4 x3 Not only secure, it’s fast. суббота, 2 ноября 13 г.
  49. 70.

    query = build_query(code, lat.to_f.round(5), lon.to_f.round(5)) def build_query(code, lat, lon) m

    = build_insert_manager m.insert [ [table[:code], code], [table[:lat], lat], [table[:lon], lon] ] m.to_sql.gsub(/^INSERT/,'INSERT OR REPLACE') end def build_insert_manager Arel::InsertManager.new Arel::Table.engine end def table @table ||= Postcode.arel_table end суббота, 2 ноября 13 г.
  50. 71.

    Compared to hand- made SQL generator On 10_000 records: user

    system total real AR: 29.200000 2.690000 31.890000 ( 79.686113) Generated SQL: 1.920000 0.420000 2.340000 ( 11.347266) RAW SQL: 1.040000 0.370000 1.410000 ( 8.810435) суббота, 2 ноября 13 г.
  51. 74.
  52. 75.

    Who uses Arel, anyway? • ActiveRecord • MetaSearch | Ransack

    • Squeel суббота, 2 ноября 13 г.
  53. 79.

    BTW, check out Sqeel Person.where( '(name LIKE ? AND salary

    < ?) OR (name LIKE ? AND salary > ?)', 'Ernie%', 50000, 'Joe%', 100000 ) суббота, 2 ноября 13 г.
  54. 80.

    BTW, check out Sqeel Person.where( '(name LIKE ? AND salary

    < ?) OR (name LIKE ? AND salary > ?)', 'Ernie%', 50000, 'Joe%', 100000 ) Person.where{ (name =~ 'Ernie%') & (salary < 50000) | (name =~ 'Joe%') & (salary > 100000) } суббота, 2 ноября 13 г.
  55. 81.

    So why do we need it? Personaly, I need it

    to write: expresive, secure, portable code. That runs fast. And do not worry about SQL суббота, 2 ноября 13 г.