Class: Ruport::Query

Inherits:
Object
  • Object
show all
Includes:
Enumerable
Defined in:
lib/ruport/util/query.rb

Overview

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.

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(sql, options = {}) ⇒ Query

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")

# query with parameter substitution
Ruport::Query.new("select * from fo where bar=?", :params => [1234])
Ruport::Query.new(:file => "foo", :params => [1234])

# query with parameter substitution (ActiveRecord style)
Ruport::Query.new(["select * from fo where bar=?", 1234])



76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/ruport/util/query.rb', line 76

def initialize(sql, options={})   
  if sql.kind_of?(Hash)  
    options = { :source => :default }.merge(sql)   
    sql = options[:file] || options[:string]
  else 
    if sql.kind_of?(Array)
      options[:params] = sql[1..-1]
      sql = sql.first
    end
    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

Instance Attribute Details

#raw_data ⇒ Object

Returns the value of attribute raw_data.



142
143
144
 
# File 'lib/ruport/util/query.rb', line 142

def raw_data
  @raw_data
end

#sql ⇒ Object (readonly)

The original SQL for the Query object



145
146
147
 
# File 'lib/ruport/util/query.rb', line 145

def sql
  @sql
end

Class Method Details

.add_source(name, options = {}) ⇒ Object

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"



137
138
139
140
 
# File 'lib/ruport/util/query.rb', line 137

def self.add_source(name,options={})
  sources[name] = OpenStruct.new(options)
  check_source(sources[name],name)
end

.default_source ⇒ Object

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



109
110
111
 
# File 'lib/ruport/util/query.rb', line 109

def self.default_source
  sources[:default]
end

.sources ⇒ Object

Returns a hash of database sources, keyed by label.



114
115
116
 
# File 'lib/ruport/util/query.rb', line 114

def self.sources
  @sources ||= {}
end

Instance Method Details

#each(&action) ⇒ Object

Yields result set by row.

Raises:

  • (LocalJumpError) 


157
158
159
160
161
 
# File 'lib/ruport/util/query.rb', line 157

def each(&action)
  raise(LocalJumpError, "No block given!") unless action
  fetch(&action)
  self
end

#execute ⇒ Object

Runs the query without returning its results.



167
 
# File 'lib/ruport/util/query.rb', line 167

def execute; fetch; nil; end

#generator ⇒ Object

Returns a Generator object of the result set.



181
182
183
 
# File 'lib/ruport/util/query.rb', line 181

def generator
  Generator.new(fetch)
end

#result ⇒ Object

Runs the SQL query and returns the result set



164
 
# File 'lib/ruport/util/query.rb', line 164

def result; fetch; end

#select_source(label) ⇒ Object

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



150
151
152
153
154
 
# File 'lib/ruport/util/query.rb', line 150

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 ⇒ Object

Returns a csv dump of the query.



176
177
178
 
# File 'lib/ruport/util/query.rb', line 176

def to_csv
  fetch.to_csv
end

#to_table ⇒ Object

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



170
171
172
173
 
# File 'lib/ruport/util/query.rb', line 170

def to_table
  data_flag, @raw_data = @raw_data, false
  data = fetch; @raw_data = data_flag; return data
end