Parent

Included Modules

Ruport::Query

Overview

Query offers a way to interact with databases via RubyDBI. It supports returning result sets in either Ruport's Data::Table, or in their raw form as DBI::Rows.

Query allows you to treat your result sets as an Enumerable data structure that plays well with the rest of Ruport.

If you are using ActiveRecord, you might prefer our acts_as_reportable extension.

Attributes

raw_data[RW]
sql[R]

The original SQL for the Query object

Public Class Methods

add_source(name,options={}) click to toggle source

Allows you to add a labeled DBI source configuration.

Query objects will use the source labeled :default, unless another source is specified.

Examples:

# a connection to a MySQL database foo with user root, pass chunkybacon
Query.add_source :default, :dsn => "dbi:mysql:foo", 
                           :user => "root",
                           :password => "chunkybacon"

# a second connection to a MySQL database bar
Query.add_source :test, :dsn => "dbi:mysql:bar",
                        :user => "tester",
                        :password => "blinky"
# File lib/ruport/query.rb, line 127
def self.add_source(name,options={})
  sources[name] = OpenStruct.new(options)
  check_source(sources[name],name)
end
default_source() click to toggle source

Returns an OpenStruct with the configuration options for the default database source.

# File lib/ruport/query.rb, line 99
def self.default_source
  sources[:default]
end
new(sql, options={}) click to toggle source

Ruport::Query provides an interface for dealing with raw SQL queries. The SQL can be single or multistatement, but the resulting Data::Table will consist only of the result of the last statement.

Available options:

:source

A source specified in Ruport::Query.sources, defaults to :default.

:dsn

If specifed, the Query object will manually override Ruport::Query.

:user

If a DSN is specified, the user can be set with this option.

:password

If a DSN is specified, the password can be set with this option.

:row_type

When set to :raw, DBI::Rows will be returned instead of a Data::Table

Examples:

# uses Ruport::Query's default source
Ruport::Query.new("select * from fo")

# uses the Ruport::Query's source labeled :my_source
Ruport::Query.new("select * from fo", :source => :my_source)

# uses a manually entered source
Ruport::Query.new("select * from fo", :dsn => "dbi:mysql:my_db",
  :user => "greg", :password => "chunky_bacon" )

# uses a SQL file stored on disk
Ruport::Query.new("my_query.sql")

# explicitly use a file, even if it doesn't end in .sql
Ruport::Query.new(:file => "foo")
# File lib/ruport/query.rb, line 70
def initialize(sql, options={})   
  if sql.kind_of?(Hash)  
    options = { :source => :default }.merge(sql)   
    sql = options[:file] || options[:string]
  else 
    options = { :source => :default, :string => sql }.merge(options)
    options[:file] = sql if sql =~ /.sql$/    
  end                                                 
  origin = options[:file] ? :file : :string      
  
  @statements = SqlSplit.new(get_query(origin,sql))
  @sql = @statements.join
  
  if options[:dsn]
    Ruport::Query.add_source :temp, :dsn      => options[:dsn],
                                    :user     => options[:user],
                                 :password => options[:password]
    options[:source] = :temp
  end
  
  select_source(options[:source])
  
  @raw_data = options[:row_type].eql?(:raw)
  @params = options[:params]
end
sources() click to toggle source

Returns a hash of database sources, keyed by label.

# File lib/ruport/query.rb, line 104
def self.sources
  @sources ||= {}
end

Public Instance Methods

each(&action) click to toggle source

Yields result set by row.

# File lib/ruport/query.rb, line 147
def each(&action)
  raise(LocalJumpError, "No block given!") unless action
  fetch(&action)
  self
end
execute() click to toggle source

Runs the query without returning its results.

# File lib/ruport/query.rb, line 157
def execute; fetch; nil; end
generator() click to toggle source

Returns a Generator object of the result set.

# File lib/ruport/query.rb, line 171
def generator
  Generator.new(fetch)
end
result() click to toggle source

Runs the SQL query and returns the result set

# File lib/ruport/query.rb, line 154
def result; fetch; end
select_source(label) click to toggle source

This will set the dsn, username, and password to one specified by a source in Ruport::Query.

# File lib/ruport/query.rb, line 140
def select_source(label)
  @dsn      = Ruport::Query.sources[label].dsn
  @user     = Ruport::Query.sources[label].user
  @password = Ruport::Query.sources[label].password
end
to_csv() click to toggle source

Returns a csv dump of the query.

# File lib/ruport/query.rb, line 166
def to_csv
  fetch.to_csv
end
to_table() click to toggle source

Returns a Data::Table, even if in raw_data mode.

# File lib/ruport/query.rb, line 160
def to_table
  data_flag, @raw_data = @raw_data, false
  data = fetch; @raw_data = data_flag; return data
end

[Validate]

Generated with the Darkfish Rdoc Generator 2.