Sequel: executing an arbitrary query on multiple tables

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

Some improvements:

require ‘sequel’
require ‘sqlite3’

DB = Sequel.sqlite(‘mydb1.db’)

class Calculator
attr_accessor :results

def initialize(tables)
@tables = tables
end

def calculate(selects: nil, excludes: nil)
@results = []

@tables.each do |table|

  dataset = DB[table]
  total = 0

  if selects
    selects.each_with_index do |proc, i|
      if i == 0
        dataset = dataset.select &proc
      else
        dataset = dataset.select_append &proc
      end
    end
  end

  if excludes
    excludes.each do |proc|
      dataset = dataset.exclude &proc
    end
  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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs