Class: Ruport::Query
- Inherits:
-
Object
- Object
- Ruport::Query
- 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
-
#raw_data ⇒ Object
Returns the value of attribute raw_data.
-
#sql ⇒ Object
readonly
The original SQL for the Query object.
Class Method Summary collapse
-
.add_source(name, options = {}) ⇒ Object
Allows you to add a labeled DBI source configuration.
-
.default_source ⇒ Object
Returns an OpenStruct with the configuration options for the default database source.
-
.sources ⇒ Object
Returns a hash of database sources, keyed by label.
Instance Method Summary collapse
-
#each(&action) ⇒ Object
Yields result set by row.
-
#execute ⇒ Object
Runs the query without returning its results.
-
#generator ⇒ Object
Returns a Generator object of the result set.
-
#initialize(sql, options = {}) ⇒ Query
constructor
Ruport::Query provides an interface for dealing with raw SQL queries.
-
#result ⇒ Object
Runs the SQL query and returns the result set.
-
#select_source(label) ⇒ Object
This will set the
dsn
,username
, andpassword
to one specified by a source in Ruport::Query. -
#to_csv ⇒ Object
Returns a csv dump of the query.
-
#to_table ⇒ Object
Returns a Data::Table, even if in
raw_data
mode.
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")
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
# File 'lib/ruport/util/query.rb', line 69 def initialize(sql, ={}) if sql.kind_of?(Hash) = { :source => :default }.merge(sql) sql = [:file] || [:string] else = { :source => :default, :string => sql }.merge() [:file] = sql if sql =~ /.sql$/ end origin = [:file] ? :file : :string @statements = SqlSplit.new(get_query(origin,sql)) @sql = @statements.join if [:dsn] Ruport::Query.add_source :temp, :dsn => [:dsn], :user => [:user], :password => [:password] [:source] = :temp end select_source([:source]) @raw_data = [:row_type].eql?(:raw) @params = [:params] end |
Instance Attribute Details
#raw_data ⇒ Object
Returns the value of attribute raw_data.
131 132 133 |
# File 'lib/ruport/util/query.rb', line 131 def raw_data @raw_data end |
#sql ⇒ Object (readonly)
The original SQL for the Query object
134 135 136 |
# File 'lib/ruport/util/query.rb', line 134 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"
126 127 128 129 |
# File 'lib/ruport/util/query.rb', line 126 def self.add_source(name,={}) sources[name] = OpenStruct.new() check_source(sources[name],name) end |
.default_source ⇒ Object
Returns an OpenStruct with the configuration options for the default database source.
98 99 100 |
# File 'lib/ruport/util/query.rb', line 98 def self.default_source sources[:default] end |
.sources ⇒ Object
Returns a hash of database sources, keyed by label.
103 104 105 |
# File 'lib/ruport/util/query.rb', line 103 def self.sources @sources ||= {} end |
Instance Method Details
#each(&action) ⇒ Object
Yields result set by row.
146 147 148 149 150 |
# File 'lib/ruport/util/query.rb', line 146 def each(&action) raise(LocalJumpError, "No block given!") unless action fetch(&action) self end |
#execute ⇒ Object
Runs the query without returning its results.
156 |
# File 'lib/ruport/util/query.rb', line 156 def execute; fetch; nil; end |
#generator ⇒ Object
Returns a Generator object of the result set.
170 171 172 |
# File 'lib/ruport/util/query.rb', line 170 def generator Generator.new(fetch) end |
#result ⇒ Object
Runs the SQL query and returns the result set
153 |
# File 'lib/ruport/util/query.rb', line 153 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.
139 140 141 142 143 |
# File 'lib/ruport/util/query.rb', line 139 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.
165 166 167 |
# File 'lib/ruport/util/query.rb', line 165 def to_csv fetch.to_csv end |
#to_table ⇒ Object
Returns a Data::Table, even if in raw_data
mode.
159 160 161 162 |
# File 'lib/ruport/util/query.rb', line 159 def to_table data_flag, @raw_data = @raw_data, false data = fetch; @raw_data = data_flag; return data end |