I’m trying to write a class that performs a similar set of
calculations across a group of SQLite database files for an arbitrary
query. The query will always perform a series of numbers on which to
perform statistical calculations. The main loop in the class will look
like:
run_ids.each.with_index do |run_id, idx|
db_filename = run_id_to_filename(run_id)
db = Sequel.connect(db_filename)
result[idx] = db[<:table>].<query>
db.close
end
Including <:table> in the constructor is easy enough, but I can’t
figure out how to inject since it’s a method call like
select(:Time, :Delay) but could be more complex (tack on multiple
exclude() statements), so I can’t just do select(<:param1>, <:param2>)
and include :param1 and :param2 in the constructor.
I think that question boils down to:
How can I execute an arbitrary sequel query (made up of select()'s and
exclude()'s) on multiple tables, in order to perform some
calculations on the results.
Both select() and exclude() can be supplied with a block when calling
them, for example:
require 'sequel'
require 'sqlite3'
DB = Sequel.sqlite('my_db1.db')
dataset = DB[:numbers]
dataset = dataset.select { [:x, :y] } #SELECT x, y from numbers
So if you create a proc:
my_select = Proc.new {[:x, :y]}
You can use & to turn that into a block for the select() call:
dataset = dataset.select &my_select
If you create several select procs and several exclude procs, you can
apply them all to a dataset, like this:
require 'sequel'
require 'sqlite3'
DB = Sequel.sqlite('my_db1.db')
class Calculator
attr_accessor :results
def initialize(tables)
@tables = tables
end
def calculate(selects, excludes)
@results = []
@tables.each do |table|
dataset = DB[table]
total = 0
selects.each_with_index do |proc, i|
if i == 0
dataset = dataset.select &proc
else
dataset = dataset.select_append &proc
end
end
excludes.each do |proc|
dataset = dataset.exclude &proc
end
puts "Data selected from table #{table}:"
dataset.each do |row|
puts "\t #{row.inspect}"
row.values.each do |val|
total += val
end
end
puts
results << total
end
end
end
c = Calculator.new(
[
:numbers,
:other_numbers,
]
)
#select columns x and y:
selects = [
Proc.new{[:x]},
Proc.new{[:y]},
]
#exclude rows where y==3 OR z==2:
excludes = [
Proc.new { {:y => 3} },
Proc.new { {:z => 2} },
]
=begin
#exclude rows where y==3 AND z==2
excludes = [
Proc.new do |row|
row.&(
{:y => 3},
{:z => 2},
)
end
]
=end
c.calculate selects, excludes
puts "Results: #{c.results.inspect}"
--output:--
Data selected from table numbers:
{:x=>1, :y=>1}
Data selected from table other_numbers:
{:x=>1, :y=>2}
{:x=>4, :y=>5}
Results: [2, 12]
table numbers:
x y z
+--------------
1 | 1 1 1
2 | 1 2 2
3 | 1 3 3
table other_numbers:
x y z
+--------------
1 | 1 2 3
2 | 4 5 6