How to type attributes with ruby and mysql ruby gem

Tonight, I’m working on a new version of our TwitterGrab program. Essentially, we’re moving our program out of rails because it no longer makes sense to rely on that crutch just for it’s solid MySQL connection back to the objects we work with. In doing this, I realized that we had to start re-typing our variables when we pulled them out of the database; by default, everything is an integer, but if we want to automatically re-type them as they roll in, then we need to take another step:

1. Given a result from the database, fetch the details about the given field, then type the variable correctly according to those details
2. return this as an ordered hash, which is then instantiated as an object back in the actual Ruby program, abstracted from the MySQL database.

First, we make our connection to the database, and grab an overview of what it looks like so we know what we’re dealing with. This whole section is from this website :

require ‘rubygems’

require ‘mysql’

dbh = Mysql.real_connect(“localhost”, “testuser”, “testpass”, “test”)

stmt = “select * from table where id = 1”

res = dbh.query(stmt)

puts “Statement: #{stmt}”
if res.nil? then
puts “Statement has no result set”
printf “Number of rows affected: %d\n”, dbh.affected_rows
else
puts “Statement has a result set”
printf “Number of rows: %d\n”, res.num_rows
printf “Number of columns: %d\n”, res.num_fields
res.fetch_fields.each_with_index do |info, i|
printf “- Column %d (%s) -\n”, i, info.name
printf “table:            %s\n”, info.table
printf “def:              %s\n”, info.def
printf “type:             %s\n”, info.type
printf “length:           %s\n”, info.length
printf “max_length:       %s\n”, info.max_length
printf “flags:            %s\n”, info.flags
printf “decimals:         %s\n”, info.decimals
end
res.free
end

This will hopefully produce something like this:

Statement has a result set
Number of rows: 1
Number of columns: 14
--- Column 0 (id) ---
table:            scrapes
def:              
type:             3
length:           11
max_length:       1
flags:            49667
decimals:         0
--- Column 1 (researcher_id) ---
table:            scrapes
def:              
type:             3
length:           11
max_length:       1
flags:            32768
decimals:         0
--- Column 2 (term) ---
table:            scrapes
def:              
type:             253
length:           255
max_length:       3
flags:            0
decimals:         0
--- Column 3 (length) ---
table:            scrapes
def:              
type:             3
length:           11
max_length:       4
flags:            32768
decimals:         0
--- Column 4 (created_at) ---
table:            scrapes
def:              
type:             12
length:           19
max_length:       19
flags:            128
decimals:         0
--- Column 5 (updated_at) ---
table:            scrapes
def:              
type:             12
length:           19
max_length:       19
flags:            128
decimals:         0
--- Column 6 (finished) ---
table:            scrapes
def:              
type:             1
length:           1
max_length:       1
flags:            32768
decimals:         0
--- Column 7 (notified) ---
table:            scrapes
def:              
type:             1
length:           1
max_length:       1
flags:            32768
decimals:         0
--- Column 8 (sanitized_term) ---
table:            scrapes
def:              
type:             253
length:           255
max_length:       3
flags:            0
decimals:         0
--- Column 9 (scrape_finished) ---
table:            scrapes
def:              
type:             1
length:           1
max_length:       1
flags:            32768
decimals:         0
--- Column 10 (folder_name) ---
table:            scrapes
def:              
type:             253
length:           255
max_length:       8
flags:            0
decimals:         0
--- Column 11 (lock) ---
table:            scrapes
def:              
type:             253
length:           255
max_length:       0
flags:            0
decimals:         0
--- Column 12 (flagged) ---
table:            scrapes
def:              
type:             1
length:           1
max_length:       1
flags:            32768
decimals:         0
--- Column 13 (scrape_type) ---
table:            scrapes
def:              
type:             253
length:           255
max_length:       6
flags:            0
decimals:         0
=> nil

So, you can clearly see that there doesn’t appear to be any straightforward way to figure out what the actual type is for the data; this is a problem. It’s ok though, because we can use type to do something else. Type, which you would expect to be some string like “INT”, “TINYINT”, “VARCHAR”, “TEXT”, “DATETIME”, etc is actually just some number; its not any number, however: it’s the number of bytes required to store any particular entry in that row. So, assuming we’re not going to do anything super crazy (figure out down to the byte what everything is worth, but even then this would work so long as two different things need to be typed differently but have the same byte value), we can just use this byte value as an equivalency to types: 1 is a boolean, 12 is a datetime, and anything 253 or over is a string! Let’s do it.

  def self.type_attributes(row, query_result)
    map_hash = {}
    final_row = {}
    debugger    
    type_map = query_result.fetch_fields.collect{|field| map_hash[field.name] = field.type}
    row.each_pair do |k,v|
      if map_hash[k] == 1
        final_row[k] = true
      elsif map_hash[k] == 3
        final_row[k] = v.to_i
      elsif map_hash[k] == 12
        final_row[k] = Time.parse(v)
      elsif map_hash[k] >= 253
        final_row[k] = v
      end
    end
    return final_row
  end

Where the string method to_bool is an extension as such:

class String
  def to_bool
    if self == "1"
      return true
    elsif self == "0"
      return false
    else return nil
    end
  end
end

So, try that? It should work appropriately and re-type all variables accordingly.