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¶ ↑
-
Sequel with the mysql2 adapter sequel.rubyforge.org
-
ActiveRecord with the mysql2 Adapter github.com/brianmario/mysql2/blob/master/lib/active_record/connection_adapters/mysql2_adapter.rb
-
Datamapper with mysql2 adapter datamapper.org
-
RBatis is the port of iBatis to Ruby and Ruby on Rails. ibatis.apache.org/docs/ruby (Appears to be discontinued)
Admin¶ ↑
-
RubyTracker As soon as there is a public url I will include it in the readme under troubleshooting.
Special Thanks¶ ↑
-
Brian Lopez - Mysql2 Gem (github.com/brianmario/mysql2)
Copyright¶ ↑
Copyright © 2010 Donovan Bray “donnoman@donovanbray.com” github.com/donnoman
See MIT-LICENSE for details.