Class: Ruport::Query

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

Overview

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

It offers basic caching support, the ability to instantiate a generator for a result set, and the ability to quickly and easily swap between data sources.

Defined Under Namespace

Classes: SqlSplit

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

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

Queries are initialized with some SQL and a number of options that effect their operation. They are NOT executed at initialization.

This is important to note as they will not query the database until either Query#result, Query#execute, Query#generator, or an enumerable method is called on them.

This kind of laziness is supposed to be A Good Thing, and as long as you keep it in mind, it should not cause any problems.

The SQL can be single or multistatement, but the resulting DataSet will consist only of the result of the last statement which returns something.

Options:

:source

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

:origin

query origin, default to :string, but can be
set to :file, loading the path specified by the sql parameter

:dsn

If specifed, the query object will manually override Ruport::Config

:user

If a DSN is specified, a user can be set by this option

:password

If a DSN is specified, a password can be set by this option

:raw_data

When set to true, DBI::Rows will be returned

:cache_enabled

When set to true, Query will download results only once, and then return
cached values until cache has been cleared.

Examples:

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

# uses the Ruport::Config'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",:origin => :file)



68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/ruport/query.rb', line 68

def initialize(sql, options={})
  options[:source] ||= :default
  options[:origin] ||= :string
  @sql = sql
  @statements = SqlSplit.new(get_query(options[:origin],sql))
  
  if options[:dsn]
    Ruport::Config.source :temp, :dsn      => options[:dsn],
                                 :user     => options[:user],
                                 :password => options[:password]
    options[:source] = :temp
  end
  
  select_source(options[:source])
  
  @raw_data = options[:raw_data]
  @cache_enabled  = options[:cache_enabled]
  @cached_data = nil
end

Instance Attribute Details

#cached_data ⇒ Object

modifying this might be useful for testing, this is the data stored by ruport when caching



93
94
95
 
# File 'lib/ruport/query.rb', line 93

def cached_data
  @cached_data
end

#raw_data ⇒ Object

set to true to get DBI:Rows, false to get Ruport constructs



89
90
91
 
# File 'lib/ruport/query.rb', line 89

def raw_data
  @raw_data
end

#sql ⇒ Object (readonly)

this is the original SQL for the Query object



96
97
98
 
# File 'lib/ruport/query.rb', line 96

def sql
  @sql
end

Instance Method Details

#clear_cache ⇒ Object

clears the contents of the cache



122
123
124
 
# File 'lib/ruport/query.rb', line 122

def clear_cache
  @cached_data = nil
end

#disable_caching ⇒ Object

Turns off caching and flushes the cached data



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

def disable_caching
  @cached_data   = nil
  @cache_enabled = false
end

#each(&action) ⇒ Object

Standard each iterator, iterates through result set row by row.



107
108
109
110
111
112
 
# File 'lib/ruport/query.rb', line 107

def each(&action) 
  Ruport::complain(
    "no block given!", :status => :fatal, :exception => LocalJumpError 
  ) unless action
  fetch &action
end

#enable_caching ⇒ Object

Turns on caching. New data will not be loaded until cache is clear or caching is disabled.



136
137
138
 
# File 'lib/ruport/query.rb', line 136

def enable_caching
  @cache_enabled = true
end

#execute ⇒ Object

Runs the query without returning it’s results.



119
 
# File 'lib/ruport/query.rb', line 119

def execute; fetch; nil; end

#generator ⇒ Object

Returns a Generator object of the result set



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

def generator
  Generator.new(fetch)
end

#result ⇒ Object

Grabs the result set as a DataSet or if in raw_data mode, an array of DBI:Row objects



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

def result; fetch; end

#select_source(label) ⇒ Object

This will set the dsn, username, and password to one specified by a label that corresponds to a source in Ruport::Config



100
101
102
103
104
 
# File 'lib/ruport/query.rb', line 100

def select_source(label)
  @dsn      = Ruport::Config.sources[label].dsn
  @user     = Ruport::Config.sources[label].user
  @password = Ruport::Config.sources[label].password
end

#to_csv ⇒ Object

Returns a csv dump of the query



153
154
155
 
# File 'lib/ruport/query.rb', line 153

def to_csv
  to_dataset.to_csv
end

#to_dataset ⇒ Object

Returns a DataSet, even if in raw_data mode



147
148
149
150
 
# File 'lib/ruport/query.rb', line 147

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

#update_cache ⇒ Object

clears the contents of the cache and then runs the query, filling the cache with the new result



128
129
130
131
132
 
# File 'lib/ruport/query.rb', line 128

def update_cache
  clear_cache
  caching_flag,@cache_enabled = @cache_enabled, true
  fetch; @cache_enabled = caching_flag
end