introduction to active record at mysql conference 2007
DESCRIPTION
An introduction to the ruby on rails Active Record library presented at the MySQL Users Conference in Santa Clara 2007.TRANSCRIPT
Introduction to Active Record
Evan ‘Rabble’ Henshaw-Plath [email protected] - Yahoo! Brickhouse
anarchogeek.com - testingrails.com
Active Record is a Design Pattern
An object that wraps a row in a database table or view, encapsulates the database access, and
adds domain logic on that data.
Active Recordthe Pattern
Active Record uses the most obvious approach,putting data access logic in the domain object.
- Martin Fowler
Person last_name first_name dependents_count
insert update
get_exemption is_flagged_for_audit? get_taxable_earnings?
One Class Per Table
CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `login` varchar(255), `email` varchar(255), `crypted_password` varchar(40), `salt` varchar(40), `created_at` datetime default NULL, `updated_at` datetime default NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
class User < ActiveRecord::Base end
The DatabaseThe Model Code
One Object Per Row
There Are Other Ways To Do it
• Table Data Gateway
• Row Data Gateway
• Data Mapper
• The Anti-Patterns
Active Record is just one ‘Data Source Architectural Pattern’
Standard Active Record
• Direct mapping to the DB
• Class to table
• Object to row
• Simple, no relationship between objects
• Just a finder method with getters and setters
ActiveRecordthe ruby library
Active Record is a library builtfor Ruby on Rails.
Makes CRUD EasyCreateReadUpdateDelete
ActiveRecordthe ruby library
I have never seen an Active Record implementation as complete
or as useful as rails. - Martin Fowler
Rails’ ActiveRecord• DRY Conventions & Assumptions
• Validations
• Before and after filters
• Database Agnostic (mostly)
• Migrations
• Model relationships
• has_many, belongs_to, etc...
What ActiveRecord Likes• mapping class names to
table names
• pluralized table names
• integer primary keys
• classname_id foreign keys
• simple schemas
• single table inheritance
Active RecordDoesn’t Like • views
• stored procedures
• foreign key constraints
• cascading commits
• split or clustered db’s
• enums
The Basics
class User < ActiveRecord::Base end
./app/models/user.rb Loading a user
>> user_obj = User.find(2)=> #<User:0x352e8bc @attributes= {"salt"=>"d9ef...", "updated_at"=>"2007-04-19 10:49:15", "crypted_password"=>"9c1...", "id"=>"2", "remember_token"=>"a8d...", "login"=>"rabble", "created_at"=>"2007-04-19 10:49:15", "email"=>"[email protected]"}>
User Load (0.003175) SELECT * FROM users WHERE (users.id = 2) LIMIT 1
The SQL Log
The Find Method
Find is the primary method of Active Record
Examples: User.find(23) User.find(:first) User.find(:all, :offset => 10, :limit => 10) User.find(:all, :include => [:account, :friends]) User.find(:all, :conditions => [“category in (?), categories, :limit => 50) User.find(:first).articles
The Four Ways of Find
Find by id: This can either be a specific id (1), a list of ids (1, 5, 6), or an array of ids ([5, 6, 10]).
Find first: This will return the first record matched by the options used.
Find all: This will return all the records matched by the options used.
Indirectly: The find method is used for AR lookups via associations.
Understanding Find
Model#find(:all, { parameters hash }
What Find Does: * generates sql * executes sql * returns an enumerable (array like object) * creates an AR model object for each row
Find with :conditions
:conditions - An SQL fragment like "administrator = 1" or [ "user_name = ?", username ].
Student.find(:all, :conditions => [‘first_name = ? and status = ?’ ‘rabble’, 1])
New Style (Edge Rails Only)Student.find(:all, :conditions => {:first_name => “rabble”, :status => 1})
SQL Executed:SELECT * FROM students WHERE (first_name = 'rabble' and status = 1);
Order By
:order - An SQL fragment like "created_at DESC, name".
Student.find(:all, :order => ‘updated_at DESC’)
SQL Executed:SELECT * FROM users ORDER BY created_at;
Group By
:group - An attribute name by which the result should be grouped. Uses the GROUP BY SQL-clause.
Student.find(:all, :group => ‘graduating_class’)
SQL Executed:SELECT * FROM users GROUP BY graduating_class;
Limit & Offset:limit - An integer determining the limit on the number of rows that should be returned.
:offset- An integer determining the offset from where the rows should be fetched. So at 5, it would skip the first 4 rows.
Student.find(:all, :limit => 10, :offset => 0)
SQL Executed:SELECT * FROM users LIMIT 0, 10;
Joins
:joins - An SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id". (Rarely needed).
Student.find(:all, :join => "LEFT JOIN comments ON comments.post_id = id")
SQL Executed:SELECT users.* FROM users, comments LEFT JOIN comments ON comments.post_id = users.id;
Returns read only objects unless you say :readonly => false
Alternative Finds
find_by_sql
find_by_attribute_and_attribute2
find_or_create
Depreciated Find’sfind_firstfind_allfind_on_conditions
class Project < ActiveRecord::Base belongs_to :portfolio has_one :project_manager has_many :milestones has_and_belongs_to_many :categoriesend
The Four Primary Associations
belongs_tohas_onehas_manyhas_and_belongs_to_many
Associations
One to One has_one & belongs_to
Many to One has_many & belongs_to
Many to Many has_and_belongs_to_many has_many :through
Associations
One to One
Use has_one in the base, and belongs_to in the associated model.
class Employee < ActiveRecord::Base has_one :office end
class Office < ActiveRecord::Base belongs_to :employee # foreign key - employee_id end
One To One Example>> joe_employee = Employee.find_by_first_name('joe')SELECT * FROM employees WHERE (employees.`first_name` = 'joe') LIMIT 1=> #<Employee:0x36beb14 @attributes={"id"=>"1", "first_name"=>"joe", "last_name"=>"schmo", "created_at"=>"2007-04-21 09:08:59"}>
>> joes_office = joe_employee.officeSELECT * FROM offices WHERE (offices.employee_id = 1) LIMIT 1=> #<Office:0x36bc06c @attributes={"employee_id"=>"1", "id"=>"1", "created_at"=>"2007-04-21 09:11:44", "location"=>"A4302"}>
>> joes_office.employeeSELECT * FROM employees WHERE (employees.`id` = 1)=> #<Employee:0x36b6ef0 @attributes={"id"=>"1", "first_name"=>"joe", "last_name"=>"schmo", "created_at"=>"2007-04-21 09:08:59"}>
belongs_toOne to One Relationship. Use belong to when the foreign key is in THIS table.
• Post#author (similar to Author.find(author_id) )
• Post#author=(author) (similar to post.author_id = author.id)
• Post#author? (similar to post.author == some_author)
• Post#author.nil?
• Post#build_author (similar to post.author = Author.new)
• Post#create_author (similar to post.author = Author; post.author.save;
Defining belongs_toclass Employee < ActiveRecord::Base belongs_to :firm, :foreign_key => "client_of"
belongs_to :author, :class_name => "Person", :foreign_key => "author_id"
belongs_to :valid_coupon, :class_name => "Coupon",
:foreign_key => "coupon_id", :conditions => 'discounts > #{payments_count}'
belongs_to :attachable, :polymorphic => trueend
has_one
• Account#beneficiary (similar to Beneficiary.find(:first, :conditions => "account_id = #{id}"))
• Account#beneficiary=(beneficiary) (similar to beneficiary.account_id = account.id; beneficiary.save)
• Account#beneficiary.nil?
• Account#build_beneficiary (similar to Beneficiary.new("account_id" => id))
• Account#create_beneficiary (similar to b = Beneficiary.new("account_id" => id); b.save; b)
One to One Relationship. Use has_one when the foreign key is in the OTHER table.
Defining has_oneclass Employee < ActiveRecord::Base
# destroys the associated credit card has_one :credit_card, :dependent => :destroy # updates the associated records foreign key value to null rather than destroying it has_one :credit_card, :dependent => :nullify has_one :last_comment, :class_name => "Comment", :order => "posted_on" has_one :project_manager, :class_name => "Person", :conditions => "role = 'project_manager'"
has_one :attachment, :as => :attachable
end
One to ManyOne-to-manyUse has_many in the base, and belongs_to in the associated model.
class Manager < ActiveRecord::Base has_many :employeesend
class Employee < ActiveRecord::Base belongs_to :manager # foreign key - manager_idend
>> benevolent_dictator = Manager.find(:first, :conditions => ['name = "DHH"']) SELECT * FROM managers WHERE (name = "DHH") LIMIT 1=> #<Manager:0x369b7b8 @attributes={"name"=>"DHH", "id"=>"1", "created_at"=>"2007-04-21 09:59:24"}>
>> minions = benevolent_dictator.employees SELECT * FROM employees WHERE (employees.manager_id = 1)=> [#<Employee:0x36926a4 @attributes={"manager_id"=>"1", "id"=>"1", "first_name"=>"joe", "last_name"=>"schmo", "created_at"=>"2007-04-21 09:08:59"}>, #<Employee:0x36925f0 @attributes={"manager_id"=>"1", "id"=>"2", "first_name"=>"funky", "last_name"=>"monkey", "created_at"=>"2007-04-21 09:58:20"}>]
One to Many
has_many
• Firm#clients (similar to Clients.find :all, :conditions => "firm_id = #{id}")
• Firm#clients<<
• Firm#clients.delete
• Firm#client_ids
• Firm#client_ids=
• Firm#clients=
Augmenting the Model
has_many
• Firm#client.clear
• Firm#clients.empty? (similar to firm.clients.size == 0)
• Firm#clients.size (similar to Client.count "firm_id = #{id}")
• Firm#clients.find (similar to Client.find(id, :conditions => "firm_id = #{id}"))
• Firm#clients.build (similar to Client.new("firm_id" => id))
• Firm#clients.create (similar to c = Client.new("firm_id" => id); c.save; c)
has_many examples
class Employee < ActiveRecord::Base has_many :comments, :order => "posted_on" has_many :comments, :include => :author has_many :people, :class_name => "Person", :conditions => "deleted = 0", :order => "name" has_many :tracks, :order => "position", :dependent => :destroy has_many :comments, :dependent => :nullify has_many :tags, :as => :taggable has_many :subscribers, :through => :subscriptions, :source => :user has_many :subscribers, :class_name => "Person", :finder_sql => 'SELECT DISTINCT people.* ' + 'FROM people p, post_subscriptions ps ' + 'WHERE ps.post_id = #{id} AND ps.person_id = p.id ' + 'ORDER BY p.first_name'end
Many to Many
Simple Joiner Tablehas_and_belongs_to_many
Joiner Modelhas_many :through
has_and_belongs_to_many
The Simple Joiner Table Way
neglected by
rails-core
has_and_belongs_to_many
has_and_belongs_to_many
• Developer#projects
• Developer#projects<<
• Developer#projects.delete
• Developer#projects=
• Developer#projects_ids
• Developer#projects_ids=
• Developer#clear
Augmenting the Model
has_and_belongs_to_many
• Developer#projects.empty?
• Developer#projects.size
• Developer#projects.find(id) # Also find(:first / :all)
• Developer#projects.build #(similar to Project.new("project_id" => id))
• Developer#projects.create (similar to c = Project.new("project_id" => id); c.save; c)
habtm example create_table :developers do |t| t.column :name, :string t.column :created_at, :datetime end
create_table :projects do |t| t.column :name, :string t.column :created_at, :datetime end
create_table(:developers_projects, :id => false) do |t| t.column :developer_id, :integer t.column :project_id, :integer end
habtm example>> d = Developer.find(1) SELECT * FROM developers WHERE (developers.`id` = 1)=> #<Developer:0x32bc7dc @attributes={"name"=>"rabble", "id"=>"1", "created_at"=>nil}>
>> d.projects SELECT * FROM projects INNER JOIN developers_projects ON projects.id = developers_projects.project_id WHERE (developers_projects.developer_id = 1 )=> [#<Project:0x3257cc4 @attributes= {"name"=>"ragi", "project_id"=>"1", "id"=>"1", "developer_id"=>"1", "created_at"=>nil}>, #<Project:0x3257c10 @attributes= {"name"=>"acts_as_autenticated", "project_id"=>"3", "id"=>"3", "developer_id"=>"1", "created_at"=>nil}>]
has_many :through
DHH’s One True Way
of Many to Many
has_many :through
Full Joiner Model
has_many :through
class Appearance < ActiveRecord::Base belongs_to :dancer belongs_to :movieend
class Dancer < ActiveRecord::Base has_many :appearances, :dependent => true has_many :movies, :through => :appearancesend
class Movie < ActiveRecord::Base has_many :appearances, :dependent => true has_many :dancers, :through => :appearancesend
Validations
class User < ActiveRecord::Base validates_confirmation_of :login, :password validates_confirmation_of :email, :message => "should match confirmation" validates_format_of :email, :with => /\A([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})\Z/i, :on => :createend
Validations
• Keeping Data Clean
• In object validation of fields, calculated validations
• Instead of key constraints
• The database is for storage, the model is for the business logic
• Kinds of validations, custom validations, etc...
But Wait?
• Aren’t format, presence, relationship validations supposed to be the database’s job?
• Traditionally, yes.
• ActiveRecord does constraints in the model, not the database
But Why?
• Validations & Constraints are Business Logic
• Business logic should be in the model
• It makes things easy
• End users can get useful error messages
• Makes the postback pattern work well
Data Integrity?
• It’s still possible to do constraints in the db
• But it’s not as necessary
• Validations are constraints which make sense in terms of functionality of the app
• The rails ways is to just use validations
• Most DBA’s insist on foreign_key constraints
What AR Returns?
• Arrays of Model Objects
• Preselects and instantiates objects
• Nifty methods: to_yaml, to_xml, to_json
Output Formats#<Employee:0x36926a4 @attributes= {"manager_id"=>"1", "id"=>"1", "first_name"=>"joe", "last_name"=>"schmo", "created_at"=>"2007-04-21 09:08:59"}>
ruby - inspect--- !ruby/object:Employee attributes: manager_id: "1" id: "1" first_name: joe last_name: schmo created_at: 2007-04-21 09:08:59
to_yaml
{attributes: {manager_id: "1", id: "1", first_name: "joe", last_name: "schmo", created_at: "2007-04-21 09:08:59"}}
to_json<?xml version="1.0" encoding="UTF-8"?><employee> <created-at type="datetime">2007-04-21T09:08:59-07:00</created-at> <first-name>joe</first-name> <id type="integer">1</id> <last-name>schmo</last-name> <manager-id type="integer">1</manager-id></employee>
to_xml
Before & After Callbacks
* (-) save * (-) valid? * (1) before_validation * (2) before_validation_on_create * (-) validate * (-) validate_on_create * (3) after_validation * (4) after_validation_on_create * (5) before_save * (6) before_create * (-) create * (7) after_create * (8) after_save
class Subscription < ActiveRecord::Base before_create :record_signup
private def record_signup self.signed_up_on = Date.today end end
class Firm < ActiveRecord::Base # Destroys the associated clients and #people when the firm is destroyed before_destroy { |record| Person.destroy_all "firm_id = #{record.id}" } before_destroy { |record| Client.destroy_all "client_of = #{record.id}" } end
Optimizing AR
• Eager Loading
• Use Memecached
• Add index to your migrations
Security
Doing it Securely
class User < ActiveRecord::Base def self.authenticate_unsafely(user_name, password) find(:first, :conditions => "user_name = '#{user_name}' AND password = '#{password}'") end
def self.authenticate_safely(user_name, password) find(:first, :conditions => [ "user_name = ? AND password = ?", user_name, password ]) end
# Edge Rails Only (Rails 2.0) def self.authenticate_safely_simply(user_name, password) find(:first, :conditions => { :user_name => user_name, :password => password }) end end
Examples Stolen From: http://www.therailsway.com/2007/3/26/association-proxies-are-your-friend
Use The ActiveRecord Relationships
Anti-Pattern #1: Manually specifying the IDs when you construct the queries;def show unless @todo_list = TodoList.find_by_id_and_user_id(params[:id], current_user.id) redirect_to '/'end
Anti-Pattern #2: Querying globally, then checking ownership after the fact;def show @todo_list = TodoList.find(params[:id]) redirect_to '/' unless @todo_list.user_id = current_user.idend
Anti-Pattern #3: Abusing with_scope for a this simple case either directly, or in an around_filter.def show with_scope(:find=>{:user_id=>current_user.id}) do @todo_list = TodoList.find(params[:id]) endend
Best Practice: The most effective way to do this is to call find on the todo_lists association.def show @todo_list = current_user.todo_lists.find(params[:id])end
Create Via Association Proxies
The Bad Waydef create @todo_list = TodoList.new(params[:todo_list]) @todo_list.user_id = current_user.id @todo_list.save! redirect_to todo_list_url(@todo_list)end
A Better Way: Use association proxies for creation.def create @todo_list = current_user.todo_lists.create! params[:todo_list] redirect_to todo_list_url(@todo_list)end
The Best Practice - Handle exceptions for the user.def create @todo_list = current_user.todo_lists.build params[:todo_list] if @todo_list.save redirect_to todo_list_url(@todo_list) else render :action=>'new' endend Examples Stolen From: http://www.therailsway.com/2007/3/26/association-proxies-are-your-friend
Special Fields * created_at * created_on * updated_at * updated_on * lock_version * type * id
* #{table_name}_count * position * parent_id * lft * rgt * quote * template
Table Inheritance
Class Table Inheritance: Represents an inheritance hierarchy of classes with one table for each class1.
Single Table Inheritance: Represents an inheritance hierarchy of classes as a single table that has columns for all the fields of the various classes2.
Concrete Table Inheritance: Represents an inheritance hierarchy of classes with one table per concrete class in the hierarchy
STI - Single Table Inheritance Represents an inheritance hierarchy of classes as a single
table that has columns for all the fields of the various classes.
STI - Single Table Inheritance
class Company < ActiveRecord::Base; end class Firm < Company; end class Client < Company; end class PriorityClient < Client; end
CREATE TABLE `companies` ( `id` int(11) default NULL, `name` varchar(255) default NULL, `type` varchar(32) default NULL)
Company.find(:first) SELECT * FROM companies LIMIT 1;
Firm.find(:first) SELECT * FROM companies WHERE type = ‘firm’ LIMIT 1;
Legacy Databases
How to do legacy databases with Active Record?
http://sl33p3r.free.fr/tutorials/rails/legacy/legacy_databases.html
class CustomerNote < ActiveRecord::Base
set_primary_key "client_comment_id" set_sequence_name "FooBarSequences"
def self.table_name() "client_comment" end def body read_attribute "client_comment_body" end
def body=(value) write_attribute "client_comment_body", value endend
Supporting Legacy DB’s
Thanks to: http://www.robbyonrails.com/articles/2005/07/25/the-legacy-of-databases-with-rails
Changing ActiveRecord
Thanks to: http://fora.pragprog.com/rails-recipes/write-your-own/post/84
Modify Active Record ActiveRecord::Base.table_name_prefix = "my_" ActiveRecord::Base.table_name_suffix = "_table" ActiveRecord::Base.pluralize_table_names = false
Fixing the Auto-Increment / Sequence Problem module ActiveRecord class Base class << self def reset_sequence_name "#{table_name}_sequence" end end end end
module ActiveRecord module ConnectionAdapters class MysqlAdapter def prefetch_primary_key?(table_name = nil) true end end end end
Changing ActiveRecord
Thanks to: http://fora.pragprog.com/rails-recipes/write-your-own/post/84
Telling ActiveRecord to fetch the primary key
module ActiveRecord module ConnectionAdapters class MysqlAdapter
def prefetch_primary_key?(table_name = nil) true end
def next_sequence_value(sequence_name) sql = "UPDATE #{ sequence_name} SET Id=LAST_INSERT_ID(Id+1);" update(sql, "#{sequence_name} Update") select_value("SELECT Id from #{ sequence_name}",'Id') end
end end
Ruby on Rails AR Alternatives
Ruby DataMapper
iBatis - rBatis
Ruby DataMapperhttp://rubyforge.org/projects/datamapper
class FasterAuthor < DataMapper::Base
set_table_name 'authors'
property :name, :string, :size => 100 property :url, :string, :size => 255 property :is_active?, :boolean property :email, :string, :size => 255 property :hashed_pass, :string, :size => 40 property :created_at, :datetime property :modified_at, :datetime
has_many :posts, :class => 'FasterPost' # :foreign_key => 'post_id'
# prepends HTTP to a URL if necessary def self.prepend_http(url = '') if url and url != '' and not(url =~ /^http/i) url = 'http://' + url end return url end
end
iBatis - rBatisiBatis for Ruby (RBatis) is a port of Apache's iBatis library to Ruby and Ruby on Rails. It is an O/R-mapper that allows for complete customization of SQL. http://ibatis.apache.org
Not Very DRY / Rails Like
Drink the Kool aid?
Flickr Photos Used:http://flickr.com/photos/brraveheart/114402291/http://flickr.com/photos/bright/253175260/http://flickr.com/photos/good_day/63617697/http://flickr.com/photos/rickharris/416150393/http://flickr.com/photos/babasteve/3322247/http://flickr.com/photos/olivander/28058685/http://flickr.com/photos/brraveheart/44052308/http://flickr.com/photos/ednothing/142393509/http://flickr.com/photos/alltheaces/87505524/http://flickr.com/photos/alfr3do/7436142/http://flickr.com/photos/gdominici/57975123/http://flickr.com/photos/josefstuefer/72512671/http://flickr.com/photos/uqbar/105440294/http://flickr.com/photos/auntiep/17135231/http://flickr.com/photos/einsame_spitze/406992131/http://flickr.com/photos/beija-flor/63758047/http://flickr.com/photos/amerune/174617912/http://flickr.com/photos/hungry_i/47938311/http://flickr.com/photos/santos/13952912/http://flickr.com/photos/supermietzi/179962496/http://flickr.com/photos/traveller2020/206931940/http://flickr.com/photos/ko_an/318906221/
http://flickr.com/photos/ryangreenberg/57722319/http://flickr.com/photos/benandliz/11065337/http://flickr.com/photos/gaspi/12944421/http://flickr.com/photos/thomashawk/221827536/http://flickr.com/photos/brianboulos/7707518/http://flickr.com/photos/ross/28330560/http://flickr.com/photos/emdot/45249090/http://flickr.com/photos/farhang/428136695/http://flickr.com/photos/belljar/67877047/http://flickr.com/photos/pulpolux/34545782/http://flickr.com/photos/monkeyc/107979135/http://flickr.com/photos/pedrosimoes7/449314732/http://flickr.com/photos/dincordero/405452471/http://flickr.com/photos/andidfl/203883534/http://flickr.com/photos/ivanomak/434387836/http://flickr.com/photos/nrvica/23858419/http://flickr.com/photos/thespeak/137012632/http://flickr.com/photos/thowi/31533027/http://flickr.com/photos/thelifeofbryan/468557520/http://flickr.com/photos/eecue/289208982/http://flickr.com/photos/estherase/14110154/http://flickr.com/photos/ehnmark/118117670/
Introduction to Active RecordEvan ‘Rabble’ Henshaw-Plath
[email protected] - Yahoo! Brickhouseanarchogeek.com - testingrails.com
Questions?