SHARE
TWEET

NoSequel

a guest Mar 10th, 2010 526 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. $:.unshift('sequel/lib')
  2. $:.unshift('sequel-mongo/lib')
  3.  
  4. require 'rubygems'
  5. require 'sequel'
  6. require 'benchmark'
  7.  
  8. SQLITE = Sequel.sqlite('nosequel.sqlite')
  9. MONGO = Sequel.connect('mongo:///nosequel')
  10. SQLITE_LOG = Object.new
  11. def SQLITE_LOG.info(msg, args=[])
  12.   puts "SQLite: #{msg}"
  13. end
  14. MONGO_LOG = Object.new
  15. def MONGO_LOG.info(msg, args=[])
  16.   puts " Mongo: #{msg}"
  17. end
  18. OBJ_MAP = {SQLITE=>'SQlite: ', MONGO=>' Mongo: '}
  19.  
  20. SQLITE.create_table!(:t) do
  21.   primary_key :_id
  22.   Integer :a
  23.   Integer :b
  24.   String :c
  25. end
  26. class ST < Sequel::Model(SQLITE[:t])
  27.   many_to_one :square_root, :class=>self, :key=>:a, :primary_key=>:b
  28. end
  29.  
  30. MONGO[:t].insert(:a=>1, :b=>2, :c=>3)
  31. class MT < Sequel::Model(MONGO[:t])
  32.   many_to_one :square_root, :class=>self, :key=>:a, :primary_key=>:b
  33. end
  34. MONGO[:t].delete
  35.  
  36. M = {MONGO=>MT, SQLITE=>ST}
  37.  
  38. def both_log(code)
  39.   both(code, :log=>true)
  40. end
  41.  
  42. def both_quiet(code)
  43.   both(code, :quiet=>true)
  44. end
  45.  
  46. def both(code, opts={})
  47.   puts "Code: #{code}" unless opts[:quiet]
  48.   rs = [[SQLITE, SQLITE_LOG], [MONGO, MONGO_LOG]].map do |db, log|
  49.     db.loggers << log if opts[:log]
  50.     begin
  51.       eval(code)
  52.     rescue => e
  53.       "#{db.class.name} raised exception: #{e}"
  54.     ensure
  55.       db.loggers.clear
  56.     end
  57.   end
  58.   unless opts[:quiet]
  59.     r1 = rs.first
  60.     if rs.all?{|r| r == r1}
  61.       puts "Same answer for all databases: #{r1.inspect}"
  62.     else
  63.       puts "Different answers:"
  64.       puts [SQLITE, MONGO].zip(rs).map{|obj, r| "#{OBJ_MAP[obj]}#{r.inspect}"}
  65.     end
  66.     puts ""
  67.   end
  68. end
  69.  
  70. def puts_eval(s)
  71.   puts s
  72.   eval s
  73. end
  74.  
  75. # Total slides
  76. $ts = 0
  77. SLIDES = {}
  78. def slide(n, &block)
  79.   SLIDES[$ts] = n
  80.   Object.send(:define_method, "s#{$ts}", &block)
  81.   $ts += 1
  82. end
  83.  
  84. def last_slide
  85.   send("s#{$ts-1}")
  86. end
  87.  
  88. # Current slide
  89. $cs = 0
  90. def n
  91.   s = SLIDES[$cs]
  92.   puts s
  93.   puts('-' * s.length)
  94.   send("s#{$cs}")
  95.   $cs += 1
  96. end
  97.  
  98. slide("NoSequel") do
  99.   puts "Sequel = The Database Toolkit for Ruby"
  100.   puts "NoSQL = (Not at all|Not only) SQL Databases"
  101.   puts "NoSequel = Using NoSQL databases with Sequel"
  102.   puts "Me = Jeremy Evans, Sequel maintainer"
  103. end
  104.  
  105. slide("MongoDB") do
  106.   puts "MongoDB = NoSQL Document Store"
  107.   puts "sequel-mongo = MongoDB driver for Sequel"
  108. end
  109.  
  110. slide("How?") do
  111.   puts "1) Sequel uses a DSL instead of literal SQL strings."
  112.   puts "2) DSL produces objects that represent concepts."
  113.   puts "3) Treat those objects specially in the sequel-mongo driver."
  114.   puts "4) Compile filter objects to javascript instead of SQL."
  115. end
  116.  
  117. slide("Selecting Records") do
  118.   both_log "db[:t].map(:a)"
  119. end
  120.  
  121. slide("Inserting Records") do
  122.   both_log "db[:t].insert(:a=>1, :b=>2)"
  123.   both "db[:t].map(:a)"
  124. end
  125.  
  126. slide("Updating Records") do
  127.   both_log "db[:t].update(:a=>10, :b=>20)"
  128.   both "db[:t].map(:a)"
  129. end
  130.  
  131. slide("Deleting Records") do
  132.   both_log "db[:t].delete"
  133.   both "db[:t].map(:a)"
  134. end
  135.  
  136. slide("Add Some More Data") do
  137.   puts '10.times{|i| db[:t].insert(:a=>i, :b=>i * i)}'
  138.   10.times{|i| both_quiet "db[:t].insert(:a=>#{i}, :b=>#{i * i})"}
  139. end
  140.  
  141. slide("Ordering") do
  142.   both_log "db[:t].order(:b.desc).map{|x| x.values_at(:a, :b)}"
  143. end
  144.  
  145. slide("Equals") do
  146.   both_log "db[:t].filter(:a=>5).map(:b)"
  147. end
  148.  
  149. slide("Not Equals") do
  150.   both_log "db[:t].exclude(:a=>5).order(:b).map(:b)"
  151. end
  152.  
  153. slide("Inequality") do
  154.   both_log "db[:t].filter{a > 5}.order(:b).map(:b)"
  155. end
  156.  
  157. slide("IS NULL/undefined") do
  158.   both "db[:t].insert(:b=>100)"
  159.   both_log "db[:t].filter(:a=>nil).order(:b).map(:b)"
  160.   both_log "db[:t].exclude(:a=>nil).order(:b).map(:b)"
  161.   both "db[:t].filter(:b=>100).delete"
  162. end
  163.  
  164. slide("Limits") do
  165.   both_log "db[:t].filter{a < 5}.order(:b.desc).limit(2).map(:b)"
  166. end
  167.  
  168. slide("Offsets") do
  169.   both_log "db[:t].filter{a < 5}.order(:b.desc).limit(2, 1).map(:b)"
  170. end
  171.  
  172. slide("Selecting only certain columns") do
  173.   both_log "db[:t].filter{a < 5}.order(:b.desc).select(:a).all"
  174. end
  175.  
  176. slide("Counting") do
  177.   both_log "db[:t].filter{a < 5}.count"
  178. end
  179.  
  180. slide("Standard math operators") do
  181.   both_log "db[:t].filter((:a + 1) * 5 - :b > 0).order(:b).map(:b)"
  182. end
  183.  
  184. slide("Bitwise math operators") do
  185.   both_log "db[:t].filter((:a.sql_number << 2) / (:b.sql_number | 3) >= 1).order(:b).map(:b)"
  186. end
  187.  
  188. slide("Add some strings to the table") do
  189.   puts_eval "NAMES =%w'Joe Jim Bob John Pat Kat Don Paul Alex Ali'"
  190.   puts "NAMES.each_with_index{|n,i| db[:t].filter(:a=>i).update(:c=>n)"
  191.   NAMES.each_with_index{|n,i| both_quiet "db[:t].filter(:a=>#{i}).update(:c=>#{n.inspect})"}
  192.   both "db[:t].order(:a).map{|x| x.values_at(:a, :c)}"
  193. end
  194.  
  195. slide("Search with Regexps") do
  196.   both_log "db[:t].filter(:c=>/^J/).order(:c).map(:c)"
  197. end
  198.  
  199. slide("Search with LIKE") do
  200.   both_log "db[:t].filter(:c.like('J%')).order(:c).map(:c)"
  201. end
  202.  
  203. slide("String concatenation") do
  204.   both_log "db[:t].filter([:c, :c].sql_string_join(' ').like('J% Jo%')).order(:c).map(:c)"
  205. end
  206.  
  207. slide("IN/NOT IN with array") do
  208.   both_log "db[:t].filter(:c=>%w'Ali Alex').order(:c).map(:c)"
  209.   both_log "db[:t].exclude(:c=>%w'Ali Alex').order(:c).map(:c)"
  210. end
  211.  
  212. slide("Complex Expression Example 1") do
  213.   both_log "db[:t].filter{a < 3}.or{a > 6}.order(:a).map(:a)"
  214. end
  215.  
  216. slide("Complex Expression Example 2") do
  217.   both_log "db[:t].filter{((a > 3) & c.like('A%')) | {b=>[4, 9, 16]}}.order(:a).map(:a)"
  218. end
  219.  
  220. slide("Complex Expression Example 3") do
  221.   both_log "db[:t].filter{((a * 3 + (b.sql_number >> 1) > b - 10) & ~c.ilike('%a%')) | ({b=>[4, 9, 16]} & {a=>3, :b=>16}.sql_or)}.order(:a).map(:a)"
  222. end
  223.  
  224. slide("Case Statements") do
  225.   both_log "db[:t].filter({1=>9, 5=>0}.case(1, :a) * :b * 10 > 90).order(:a).map(:a)"
  226. end
  227.  
  228. slide("Casting") do
  229.   both_log "db[:t].filter(:a.cast_string + :a.cast_string => '11').order(:a).map(:a)"
  230.   both_log "db[:t].filter(\"'1'\".lit.cast_numeric + :a => 3).order(:a).map(:a)"
  231. end
  232.  
  233. slide("Models and attribute access") do
  234.   both_log "a = M[db].order(:a.desc).first; a.a"
  235. end
  236.  
  237. slide("Model associations") do
  238.   both_log "M[db].order(:a.desc).first.square_root.a"
  239. end
  240.  
  241. slide("Doesn't Work: Updating multi objects with a filter") do
  242.   both_log "db[:t].filter{a < 5}.update(:b=>30)"
  243.   both "db[:t].order(:a).map(:b)"
  244. end
  245.  
  246. slide("The necessary completely flawed benchmark") do
  247.   both_quiet "db[:t].delete"
  248.   puts "SQLite"
  249.   n = 200
  250.   Benchmark.bm(25) do |x|
  251.     x.report("SQLite #{n/10} inserts:"){(n/10).times{|i| SQLITE[:t].insert(:a=>i, :b=>n - i)}}
  252.     x.report("Mongo #{n} inserts:"){n.times{|i| MONGO[:t].insert(:a=>i, :b=>n - i)}}
  253.     x.report("SQLite #{(n/10)} lookups by id:"){(n/10).times{|i| SQLITE[:t][:a=>i]}}
  254.     x.report("Mongo #{n} lookups by id:"){n.times{|i| MONGO[:t][:a=>i]}}
  255.     x.report("SQLite #{n/10} select alls:"){(n/10).times{|i| SQLITE[:t].all}}
  256.     x.report("Mongo #{n/10} select alls:"){(n/10).times{|i| MONGO[:t].all}}
  257.     x.report("SQLite #{(n/10)} updates:"){(n/10).times{|i| SQLITE[:t].filter(:a=>i).update(:b=>i)}}
  258.     x.report("Mongo #{n} updates:"){n.times{|i| MONGO[:t].filter(:a=>i).update(:b=>i)}}
  259.     x.report("SQLite #{(n/10)} deletes:"){(n/10).times{|i| SQLITE[:t].filter(:a=>i).delete}}
  260.     x.report("Mongo #{n} deletes:"){n.times{|i| MONGO[:t].filter(:a=>i).delete}}
  261.   end
  262. end
  263.  
  264. slide("The End") do
  265.   puts "sequel-mongo: http://github.com/jeremyevans/sequel-mongo"
  266. end
RAW Paste Data
Top