= 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

• {Issue tracker}[http://github.com/donnoman/mysql2_model/issues/]
• {Documentation}[http://rubydoc.info/github/donnoman/mysql2_model/master/frames]
• {[email protected]}[http://groups.google.com/group/mysql2_model]

== 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

• Sequel with the mysql2 adapter http://sequel.rubyforge.org
• ActiveRecord with the mysql2 Adapter http://github.com/brianmario/mysql2/blob/master/lib/active_record/connection_adapters/mysql2_adapter.rb
• Datamapper with mysql2 adapter http://datamapper.org
• RBatis is the port of iBatis to Ruby and Ruby on Rails. http://ibatis.apache.org/docs/ruby (Appears to be discontinued)

== Admin

• {RubyTracker}[http://rubytracker.com/projects/425] As soon as there is a public url I will include it in the readme under troubleshooting.
• {RubyGems}[http://rubygems.org/gems/mysql2_model]

== Special Thanks

• Brian Lopez - Mysql2 Gem (http://github.com/brianmario/mysql2)

== Copyright

Copyright (c) 2010 Donovan Bray "[email protected]" http://github.com/donnoman

See MIT-LICENSE for details.