mysql2_model the QRM (Query Resource Manager)

Provides a class suitable to be used as a model, that includes connection management, variable interpolation, object coercion and helper methods to write concise MySQL statements.

This library is ideal for use when more adaptable, extensible, but perhaps less flexible ORM’s present a significant obstacle to writing and running the specific MySQL statements you need.

While the example given in this README is trivial, the most likely usage would be with particularly gnarly business logic as you might need in generating analytics where a typical ORM may yield numerous sub-optimal MySQL statements.

Inspiration

This library was conceived when I wanted to solve a performance issue in a small Sinatra app that used a well established ORM. I wanted to create a simple class that could act as a stand-in for the original object and directly utilize carefully crafted MySQL statements. The process generated a 21mb (and growing) XML file, at initially at cost of over 300k queries, and 27 minutes. The resulting Mysql2Model derived class was able to accomplish the same task in 56 seconds, with about 64 queries and dramatically reduced the memory footprint.

Usage

Install It

gem install mysql2_model

Require it

require 'mysql2_model'

Create a yml structure to point to the databases

  • See examples/repositories.yml

Config it

Mysql2Model::Config.repository_path = 'config/repositories.yml'

Create your model

class Mtdb
  include Mysql2Model::Container

  def self.all
    query "SELECT id, database_name, db_server_host, db_server_port, db_server_user, db_server_password * FROM mtdbs"
  end

  def self.count
    value("SELECT COUNT(*) FROM mtdbs")
  end

  # ? Mark substitution
  def self.find_by_database_name_and_host(name,host)
    query("SELECT * FROM mtdbs WHERE database_name = '?' and database_host = '?'",name,host)
  end

  # printf style 
  def self.find_by_host(host)
    query("SELECT * FROM mtdbs WHERE database_host = '%s'",host)
  end

  # Named Binds
  def self.arrange_by_custom_order(order,user)
    query("SELECT * FROM mtdbs WHERE db_server_user = ':user' ORDER BY database_name :order, db_server_host :order", :order => order, :user => user)
  end

  def config_name
    "mtdb#{id}".to_sym
  end

  def to_config
    {
      config_name => {
        :host => db_server_host,
        :database => database_name,
        :port => db_server_port,
        :username => db_server_user,
        :password => db_server_password_unencrypted
      }
    }
  end

  def db_server_password_unencrypted
    nil # You have to invent your own.
  end

  def self.default_repository_name # You don't need this if you want to use the default repo
    :infrastructure
  end

end

Use it

Mtdb.all.each |mtdb|
  puts "Database: #{mtdb.database_name}:" #direct method access
  puts "Host: #{mtdb[:db_server_host]}"   #direct member access
  puts "Config: {mtdb.to_config.inspect}" #model methods
end

Consume Multiple Repositories

# Assume :subscribers1 has 200 rows, :subscriber2 has 150, :subscriber3 has 50.

class Subscriber

  include Mysql2Model::Container

  def self.all
    query("SELECT * FROM subscribers") # => resultset of 400 rows
  end

  def self.count
    value_sum("SELECT COUNT(*) FROM subscribers") # => 400
  end

  def self.count_from_each
    value("SELECT COUNT(*) FROM subscribers") # => [200,150,50]
  end

  def self.default_repository_name
    [:subscribers1,:subscribers2,:subscribers3]
  end

end

Troubleshooting

Note on Patches/Pull Requests

  • Fork the project.

  • Make your feature addition or bug fix.

  • Add tests for it. This is important so I don’t break it in a future version unintentionally.

  • Commit, do not mess with rakefile, version, or changelog. (if you want to have your own version, that is fine but bump version in a commit by itself so that I can ignore it when I pull)

  • Send me a pull request. Bonus points for topic branches.

Todo

  • Improve coupling between classes

  • Time formatting when using the composing pattern to seamlessly pass time objects to mysql2

    • Support ActiveSupport::TimeWithZone?

    • Compliance with Mysql2 time handling

  • Incorporate Test,Production,Development environments into the repositories.yml

  • Improve instantiating the results so that we can regain mysql2’s lazy loading.

  • Evented Connection Pools

  • Evented Query Patterns

  • Iterate in batches to allow more efficient garbage collection of large resultsets

Similar Projects

Admin

  • RubyTracker As soon as there is a public url I will include it in the readme under troubleshooting.

  • RubyGems

Special Thanks

Copyright © 2010 Donovan Bray “[email protected]” github.com/donnoman

See MIT-LICENSE for details.