Class: RSQL::MySQLResults

Inherits:
Object
  • Object
show all
Defined in:
lib/rsql/mysql_results.rb

Overview

A wrapper to make it easier to work with MySQL results (and prettier).

Defined Under Namespace

Classes: MaxRowsException

Constant Summary collapse

HEX_RANGE = 
[
 Mysql::Field::TYPE_BLOB,
 Mysql::Field::TYPE_STRING,
]
@@conn = 
nil
@@field_separator = 
' '
@@max_rows = 
1000
@@database_name = 
nil
@@name_cache = 
{}
@@history = 
[]
@@max_history = 
10

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(fields, table, affected_rows = nil, sql = nil, elapsed = nil, field_separator = @@field_separator) ⇒ MySQLResults

Returns a new instance of MySQLResults.



297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
 
# File 'lib/rsql/mysql_results.rb', line 297

def initialize(fields, table, affected_rows=nil, sql=nil, elapsed=nil,
               field_separator=@@field_separator)

    @fields          = fields
    @table           = table
    @affected_rows   = affected_rows || table.size
    @sql             = sql
    @elapsed         = elapsed
    @field_separator = field_separator

    # we set this here so that it occurs _after_ we are successful and
    # so we can show an appropriate messge in a displayer
    if @sql && @sql.match(/use\s+(\S+)/i)
        @database_changed = true
        @@database_name = $1
    end

    # if we're not given fields from a query we need to find the column
    # widths
    if @fields && @table && @fields.size > 0 &&
            !@fields.first.respond_to?(:longest_length)
        self.class.send(:extend_fields!, @fields)
        @table.each do |row|
            @fields.each_with_index do |field, i|
                self.class.send(:update_longest!, field, row[i])
            end
        end
    end
end

Instance Attribute Details

#affected_rows ⇒ Object (readonly)

Get the number of rows that were affected by the query.



333
334
335
 
# File 'lib/rsql/mysql_results.rb', line 333

def affected_rows
  @affected_rows
end

#elapsed ⇒ Object (readonly)

Get the amount of elapsed time taken by the query.



337
338
339
 
# File 'lib/rsql/mysql_results.rb', line 337

def elapsed
  @elapsed
end

#sql ⇒ Object (readonly)

Get the query associated with these results.



329
330
331
 
# File 'lib/rsql/mysql_results.rb', line 329

def sql
  @sql
end

Class Method Details

.complete(str) ⇒ Object

Provide a list of tab completions given the prompted case-insensitive value.



161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
 
# File 'lib/rsql/mysql_results.rb', line 161

def complete(str)
    return [] unless @@conn

    ret = []

    # offer table names from a specific database
    if str =~ /^([^.]+)\.(.*)$/
        db = $1
        tb = $2
        @@name_cache.each do |db_name, tnames|
            if db.casecmp(db_name) == 0
                tnames.each do |n|
                    if m = n.match(/^(#{tb})/i)
                        ret << "#{db_name}.#{n}"
                    end
                end
                break
            end
        end
    else
        @@name_cache.each do |db_name, tnames|
            if db_name == @@database_name
                tnames.each do |n|
                    if m = n.match(/^(#{str})/i)
                        ret << n
                    end
                end
            elsif m = db_name.match(/^(#{str})/i)
                ret << db_name
            end
        end
    end

    return ret.sort
end

.conn ⇒ Object

Get the underlying MySQL connection object in use.



64
 
# File 'lib/rsql/mysql_results.rb', line 64

def conn; @@conn; end

.conn=(conn) ⇒ Object

Set the underlying MySQL connection object to use which implicitly resets the name cache.



69
70
71
72
73
74
 
# File 'lib/rsql/mysql_results.rb', line 69

def conn=(conn)
    if @@conn = conn
        @@conn.reconnect = true
    end
    reset_cache
end

.database_name ⇒ Object

Get the name of the current database in use.



96
 
# File 'lib/rsql/mysql_results.rb', line 96

def database_name; @@database_name; end

.database_name=(database) ⇒ Object

Set the name of the current database in use.



100
 
# File 'lib/rsql/mysql_results.rb', line 100

def database_name=(database); @@database_name = database; end

.databases ⇒ Object

Get the list of databases available.



128
129
130
 
# File 'lib/rsql/mysql_results.rb', line 128

def databases
    @@name_cache.keys.sort
end

.extend_fields!(fields) ⇒ Object (private) 



259
260
261
262
263
264
265
266
267
 
# File 'lib/rsql/mysql_results.rb', line 259

def extend_fields!(fields)
    fields.collect! do |field|
        def field.name; to_s; end unless field.respond_to?(:name)
        def field.longest_length=(len); @longest_length = len; end
        def field.longest_length; @longest_length; end
        field.longest_length = field.name.length
        field
    end
end

.field_separator ⇒ Object

Get the field separator to use when writing rows in columns.



78
 
# File 'lib/rsql/mysql_results.rb', line 78

def field_separator; @@field_separator; end

.field_separator=(sep) ⇒ Object

Set the field separator to use when writing rows in columns.



82
 
# File 'lib/rsql/mysql_results.rb', line 82

def field_separator=(sep); @@field_separator = sep; end

.get_max_history ⇒ Object

Get the maximum number of historical entries to retain.



120
 
# File 'lib/rsql/mysql_results.rb', line 120

def get_max_history; @@max_history; end

.history(cnt = :all) ⇒ Object

Get a list of the most recent query strings.



104
105
106
107
108
109
110
 
# File 'lib/rsql/mysql_results.rb', line 104

def history(cnt=:all)
    if Integer === cnt && cnt < @@history.size
        @@history[-cnt,cnt]
    else
        @@history
    end
end

.max_rows ⇒ Object

Get the maximum number of rows to process before throwing a MaxRowsException.



87
 
# File 'lib/rsql/mysql_results.rb', line 87

def max_rows; @@max_rows; end

.max_rows=(cnt) ⇒ Object

Set the maximum number of rows to process before throwing a MaxRowsException.



92
 
# File 'lib/rsql/mysql_results.rb', line 92

def max_rows=(cnt); @@max_rows = cnt; end

.query(sql, eval_context, raw = false, max_rows = @@max_rows) ⇒ Object

Get results from a query.



199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
 
# File 'lib/rsql/mysql_results.rb', line 199

def query(sql, eval_context, raw=false, max_rows=@@max_rows)
    if @@conn.reconnected?
        # make sure we stick with the user's last database in case
        # we had to reconnect (probably because the query thread was
        # killed
        @@conn.select_db(@@database_name) if @@database_name
    end

    @@history.shift if @@max_history <= @@history.size
    @@history << sql

    start   = Time.now.to_f
    results = @@conn.query(sql)
    elapsed = Time.now.to_f - start.to_f

    affected_rows = @@conn.affected_rows
    unless results && 0 < results.num_rows
        return new(nil, nil, affected_rows, sql, elapsed)
    end

    if max_rows < results.num_rows
        raise MaxRowsException.new(results.num_rows, max_rows)
    end

    # extract mysql results into our own table so we can
    # predetermine the lengths of columns and give users a chance to
    # reformat column data before it's displayed (via the bang maps)

    fields = results.fetch_fields
    extend_fields!(fields)

    results_table = []
    while vals = results.fetch_row
        row = []
        fields.each_with_index do |field, i|
            val = vals[i]
            orig_vlen = val.respond_to?(:length) ? val.length : 0
            if val && field.is_num?
                val = field.decimals == 0 ? val.to_i : val.to_f
            end
            unless raw
                val = eval_context.bang_eval(field.name, val)
                if val.nil?
                    val = 'NULL'
                elsif HEX_RANGE.include?(field.type) && val =~ /[^[:print:]\s]/
                    val = eval_context.to_hexstr(val)
                end
            end
            update_longest!(field, val, orig_vlen)
            row << val
        end
        results_table << row
    end

    return new(fields, results_table, affected_rows, sql, elapsed)
end

.reset_cache ⇒ Object

Force the database and table names cache to be (re)loaded.



141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
 
# File 'lib/rsql/mysql_results.rb', line 141

def reset_cache
    @@name_cache = {}
    begin
        if @@conn
            @@conn.list_dbs.each do |db_name|
                @@conn.select_db(db_name)
                @@name_cache[db_name] = @@conn.list_tables.sort
            end
        end
    rescue Mysql::Error => ex
    ensure
        if @@conn && @@database_name
            @@conn.select_db(@@database_name)
        end
    end
end

.reset_history ⇒ Object

Reset the history to an empty list.



114
115
116
 
# File 'lib/rsql/mysql_results.rb', line 114

def reset_history
    @@history = []
end

.set_max_history=(count) ⇒ Object

Set the maximum number of historical entries to retain.



124
 
# File 'lib/rsql/mysql_results.rb', line 124

def set_max_history=(count); @@max_history = count; end

.tables(database = @@database_name) ⇒ Object

Get the list of tables available for the current database or a specific one.



135
136
137
 
# File 'lib/rsql/mysql_results.rb', line 135

def tables(database=@@database_name)
    @@name_cache[database] || []
end

.update_longest!(field, val, default_vlen = nil) ⇒ Object (private) 



269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
 
# File 'lib/rsql/mysql_results.rb', line 269

def update_longest!(field, val, default_vlen=nil)
    vlen = if val.respond_to?(:length)
               val.length
           elsif default_vlen
               default_vlen
           else
               val.to_s.length
           end

    if field.longest_length < vlen
        if String === val
            # consider only the longest line length since some
            # output contains multiple lines like "show create
            # table"
            longest_line = val.split(/\r?\n/).collect{|l|l.length}.max
            if field.longest_length < longest_line
                field.longest_length = longest_line
            end
        else
            field.longest_length = vlen
        end
    end
end

Instance Method Details

#[](row_i, row_j = nil) ⇒ Object

Get an entire row as a hash or a single value from the table of results. The value of row_j may be an integer index or a string column name.



368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
 
# File 'lib/rsql/mysql_results.rb', line 368

def [](row_i, row_j=nil)
    if @table
        if row = @table[row_i]
            if row_j
                return row[row_j] if Integer === row_j
                if row_j = @fields.index{|f| f.name == row_j}
                    return row[row_j]
                end
            else
                hash = {}
                @fields.each_with_index{|f,i| hash[f.name] = row[i]}
                return hash
            end
        end
    end
    return nil
end

#any? ⇒ Boolean

Determine if there are any results.

Returns:

  • (Boolean) 


341
342
343
 
# File 'lib/rsql/mysql_results.rb', line 341

def any?
    !@table.nil?
end

#delete_if(opts = nil, &block) ⇒ Object

Conditionally delete rows from the results.



400
401
402
403
404
405
406
407
408
409
410
411
412
413
 
# File 'lib/rsql/mysql_results.rb', line 400

def delete_if(opts=nil, &block)
    if @table
        @table.delete_if do |row|
            if opts == :row_hash
                hash = {}
                @fields.each_with_index{|f,i| hash[f.name] = row[i]}
                yield(hash)
            else
                yield(row)
            end
        end
    end
    self
end

#display_by_batch(io = $stdout) ⇒ Object

Show a set of results with a single character separation.



440
441
442
443
444
445
 
# File 'lib/rsql/mysql_results.rb', line 440

def display_by_batch(io=$stdout)
    if @fields && @table
        fmt = (['%s'] * @fields.size).join(@field_separator)
        @table.each{|row| io.puts(fmt % row)}
    end
end

#display_by_column(io = $stdout) ⇒ Object

Show a set of results in a decent fashion.



417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
 
# File 'lib/rsql/mysql_results.rb', line 417

def display_by_column(io=$stdout)
    if @fields && @table
        fmts = []
        names = []
        len = 0
        @fields.each do |field|
            fmts << "%-#{field.longest_length}s"
            names << field.name
            len += field.longest_length
        end

        fmt = fmts.join(@field_separator)
        sep = '-' * (len + fmts.length)
        io.puts(fmt % names, sep)
        @table.each{|row| io.puts(fmt % row)}
        display_stats(io, sep)
    else
        display_stats(io)
    end
end

#display_by_line(io = $stdout) ⇒ Object

Show a set of results line separated.



449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
 
# File 'lib/rsql/mysql_results.rb', line 449

def display_by_line(io=$stdout)
    if @fields && @table
        namelen = 0
        @fields.each do |field|
            namelen = field.name.length if namelen < field.name.length
        end
        namelen += 1

        @table.each_with_index do |row, i|
            io.puts("#{'*'*30} #{i+1}. row #{'*'*30}")
            row.each_with_index do |val, vi|
                io.printf("%#{namelen}s #{val}#{$/}", @fields[vi].name + ':')
            end
        end
    end
    display_stats(io)
end

#display_stats(io = $stdout, hdr = '') ⇒ Object

Show a summary line of the results.



469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
 
# File 'lib/rsql/mysql_results.rb', line 469

def display_stats(io=$stdout, hdr='')
    estr = @elapsed ? " (#{'%0.2f'%@elapsed} sec)" : ''
    if @table
        if @database_changed
            io.puts(hdr, "Database changed");
            hdr = ''
        end
        s = 1 == @table.size ? 'row' : 'rows'
        io.puts(hdr, "#{@table.size} #{s} in set#{estr}")
    else
        if @database_changed
            io.puts(hdr, "Database changed");
        else
            s = 1 == @affected_rows ? 'row' : 'rows'
            io.puts(hdr, "Query OK, #{@affected_rows} #{s} affected#{estr}")
        end
    end
end

#each_hash(&block) ⇒ Object

Iterate through each row of the table hashed with the field names.



388
389
390
391
392
393
394
395
396
 
# File 'lib/rsql/mysql_results.rb', line 388

def each_hash(&block)
    if @table
        @table.each do |row|
            hash = {}
            @fields.each_with_index {|f,i| hash[f.name] = row[i]}
            yield(hash)
        end
    end
end

#empty? ⇒ Boolean

Determine if there are no results.

Returns:

  • (Boolean) 


347
348
349
 
# File 'lib/rsql/mysql_results.rb', line 347

def empty?
    @table.nil?
end

#num_rows ⇒ Object

Get the number of rows available in the results.



353
354
355
 
# File 'lib/rsql/mysql_results.rb', line 353

def num_rows
    @table ? @table.size : 0
end

#scalar ⇒ Object

Convenience helper to grab the value in the very first entry for those times when the query only has one expected value returned.



360
361
362
 
# File 'lib/rsql/mysql_results.rb', line 360

def scalar
    self[0,0]
end