Skip to content
Bmg documentation

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:

Terminal window
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:

my_bmg_script.rb
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_sql
puts "\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.

Terminal window
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>})