With a SQL database backend
To start experimenting with Bmg as an interface to a SQL database, fetch the Suppliers and parts example database in SQLite format:
curl -O https://raw.githubusercontent.com/enspirit/bmg/master/spec/suppliers-and-parts.db
(Consult the Operations overview for the database schema.)
Then simply open the file and use it as a source for relations:
require 'bundler/inline'
gemfile do source 'https://rubygems.org' gem 'bmg' gem 'json' gem 'sequel' gem 'sqlite3'end
require 'bmg/sequel'
db = Bmg::Database.sequel("sqlite://./suppliers-and-parts.db")
parts_in_paris = db.suppliers .restrict(Predicate.eq(:city, "Paris")) .project([:sid]) .join(db.supplies, [:sid]) .join(db.parts, [:pid]) .project([:name, :weight, :qty])
puts "SQL:"puts parts_in_paris.to_sqlputs "\nParts in Paris:"puts JSON.pretty_generate(parts_in_paris)
total_weights_in_paris = parts_in_paris .extend(total_weight: -> (t) { t[:qty] * t[:weight] } ) .summarize([:name], total_weight: :sum)
puts "\nTotal weights in Paris:"puts JSON.pretty_generate(total_weights_in_paris)
Output:
SQL:(Line breaks inserted for readability!)SELECT DISTINCT `t2`.`qty`, `t3`.`name`, `t3`.`weight`FROM `suppliers` AS 't1'INNER JOIN `supplies` AS 't2' ON (`t1`.`sid` = `t2`.`sid`)INNER JOIN `parts` AS 't3' ON (`t2`.`pid` = `t3`.`pid`)WHERE (`t1`.`city` = 'Paris')
Parts in Paris:[ { "qty": 300, "name": "Nut", "weight": 12.0 }, { "qty": 400, "name": "Bolt", "weight": 17.0 }, { "qty": 200, "name": "Bolt", "weight": 17.0 }]
Total weights in Paris:[ { "total_weight": 3600.0, "name": "Nut" }, { "total_weight": 10200.0, "name": "Bolt" }
If you want to understand exactly what this query does, just try removing one line at a time from the bottom (starting with summarize
) and re-run the script!
Understanding SQL Compilation
In the example above, we can’t run to_sql
on the second relation (total_weights_in_paris
) because the extend
uses a native Ruby function, which prevents both extend
and summarize
to be translated to SQL.
with-rdbms.rb:24:in `<main>': undefined method `to_sql' for (summarize (NoMethodError)
To better understand why the query works (JSON.pretty_generate
returns the expected result), let’s debug the query tree (.debug
can always be chained, it returns the relation itself):
total_weights_in_paris.debug
(summarize (extend (sequel SELECT DISTINCT `t2`.`qty`, `t3`.`name`, `t3`.`weight` FROM `suppliers` AS 't1' INNER JOIN `supplies` AS 't2' ON (`t1`.`sid` = `t2`.`sid`) INNER JOIN `parts` AS 't3' ON (`t2`.`pid` = `t3`.`pid`) WHERE (`t1`.`city` = 'Paris')) {:total_weight=>#<Proc:0x0000000103809798 with-rdbms.rb:19 (lambda)>}) [:name] {:total_weight=>#<Bmg::Summarizer::Sum:0x0000000103808c08 @options={}, @functor=:total_weight>})
As you can see, extend
and summarize
enclose the SQL query and will be executed by Bmg itself, after the SQL part has been executed and a result obtained from the database. This does not prevent chaining further, Bmg will even optimize subsequent chained operations and push everything it can to the SQL engine.
In the example below, observe how the restriction on “Smith” yields a WHERE
clause in SQL, although the restrict
operation is chained after extend
and summarize
:
total_weights_in_paris .restrict(name: 'Smith') .debug
(summarize (extend (sequel SELECT DISTINCT `t2`.`qty`, `t3`.`name`, `t3`.`weight` FROM `suppliers` AS 't1' INNER JOIN `supplies` AS 't2' ON (`t1`.`sid` = `t2`.`sid`) INNER JOIN `parts` AS 't3' ON (`t2`.`pid` = `t3`.`pid`) WHERE ((`t1`.`city` = 'Paris') AND (`t3`.`name` = 'Smith'))) {:total_weight=>#<Proc:0x0000000107d289c8 with-rdbms.rb:19 (lambda)>}) [:name] {:total_weight=>#<Bmg::Summarizer::Sum:0x0000000107d27e38 @options={}, @functor=:total_weight>})