1 / 70

Advanced MySQL Functionality with ActiveRecord: A Deep Dive

Explore how to leverage advanced MySQL functionality with ActiveRecord in this insightful presentation by Blythe Dunham. This session covers the core principles of ActiveRecord, including CRUD operations, representing relationships between models, and optimally managing database migrations. Gain practical knowledge on creating, updating, and retrieving records within your database using ActiveRecord, while adhering to best practices for optimizing your database structures. Whether you're a novice or seasoned developer, this session will take your understanding of ActiveRecord to the next level.

donovan-merritt
Télécharger la présentation

Advanced MySQL Functionality with ActiveRecord: A Deep Dive

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Taking ActiveRecord to the Next Level • Blythe Dunham • blythe@snowgiraffe.com • http://snowgiraffe.com

  2. Goal • Leverage advanced MySQL functionality • with ActiveRecord

  3. Disclaimer!!!! Premature Optimization

  4. ActiveRecord 101 • What's going on under the covers?

  5. ActiveRecord 101 Active Record Model Database Table ff

  6. ActiveRecord 101 with Animals! Active Record Model Database Table ff

  7. Creating a Record • animal = Animal.new • animal.name = 'Jerry Giraffe' • animal.password = 'jerry' • animal.save! • INSERT INTO `animals` • (`name`, `updated_at`, `species_id`, • `password`, `email`, `created_at`) • VALUES('Jerry Giraffe', '2009-03-15 00:48:28', • NULL, 'jerry', NULL, '2009-03-15 00:48:28')

  8. Updating a Record • animal.name = 'Jerry G' • animal.save! • UPDATE `animals` • SET `updated_at` = '2009-03-15 03:01:06', • `name` = 'Jerry G' • WHERE `id` = 1

  9. Finding a Record • jerry = Animal.find :first, • :conditions => ['name = ?', 'Jerry G'] • SELECT * FROM `animals` • WHERE (name = 'Jerry G') LIMIT 1 #shortcut Animal.find_by_name 'Jerry G'

  10. Representing Relationships Animal name email password fav_beer updated_at created_at species_id Species name

  11. Representing Relationships(DDL) CREATE TABLE `animals` ( `id` int(11) NOT NULL auto_increment, `name` varchar(35) NOT NULL, `email` varchar(40) default NULL, `fav_beer` enum('Guiness','Pabst','Redhook','Chimay') default 'Pabst', `created_at` datetime default NULL, `updated_at` datetime default NULL, `password` varchar(25) character set latin1 collate latin1_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `species` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255), PRIMARY KEY (`id`),) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

  12. Representing Relationships (AR) class Animal < ActiveRecord::Base belongs_to :species end class Species < ActiveRecord::Base has_many :animals end

  13. Representing Relationships (AR) jerry.species SELECT * FROM `species` WHERE (`species`.`id` = 1) species.animals SELECT * FROM `animals` WHERE (`animals`.species_id = 1)

  14. Representing Relationships (AR) giraffe = Species.find_by_name 'giraffe' giraffe.animals << jerry SELECT * FROM `species` WHERE (`species`.`name` = 'giraffe' ) LIMIT 1 UPDATE `animals` SET `species_id` = 1, `updated_at` = '2009-03-19 23:15:54' WHERE `id` = 7

  15. Migration • Set limits • Set default values • Identify NOT NULL columns • Use enumerated columns • Custom DDL • Add (unique) indices • Foreign Keys are great • Primary Key Modifications

  16. Migration 101 ruby script/generate scaffold Animal name:string password:string email:string fav_beer:string class CreateAnimals < ActiveRecord::Migration def self.up create_table :animals do |t| t.string :name t.string :password t.string :email t.string :fav_beer t.timestamps end end def self.down drop_table :animals end end

  17. Set Limits Default String is VARCHAR(255) create_table :animals do |t| t.string :name, :limit => 35 t.string :password, :limit => 25 t.string :email, :limit => 40 t.string :fav_beer, :limit => 40 t.timestamps end

  18. Numeric Type Limits t.integer :mysmallint, :limit => 2 "Smart types" determines numeric type for MySQL

  19. Set columns to NOT NULL create_table :animals do |t| t.string :name, :limit => 35, :null => false t.string :password, :limit => 25, :null => false t.string :email, :limit => 40 t.string :fav_beer, :limit => 40 t.timestamps end

  20. Set default values create_table :animals do |t| t.string :name, :limit => 35, :null => false t.string :password, :limit => 25, :null => false t.string :email, :limit => 40, :default => nil t.string :fav_beer, :limit => 40 :default => 'Pabst' t.timestamps end

  21. Remove unneeded columns create_table :animals do |t| t.string :name, :limit => 35, :null => false t.string :password, :limit => 25, :null => false t.string :email, :limit => 40, :default => nil t.string :fav_beer, :limit => 40 :default => 'Pabst' t.timestamps end

  22. Enumerated Column Plugin create_table :animals do |t| t.string :name, :limit => 35, :null => false t.string :password, :limit => 25, :null => false t.string :email, :limit => 40, :default => nil t.enum :fav_beer, :default => 'Pabst' :limit => %w(Chimay Pabst Redhook) t.timestamps end

  23. Think about the table parameters create_table :animals, :options => 'ENGINE=MyISAM' do |t| t.string :name, :limit => 35, :null => false t.string :password, :limit => 25, :null => false t.string :email, :limit => 40, :default => nil t.enum :fav_beer, :default => nil :limit => %w(Chimay Pabst Redhook) t.timestamps end

  24. Custom DDL create_table :animals do |t| t.string :name, :limit => 35, :null => false t.string :email, :limit => 40, :default => nil t.enum :fav_beer, :default => nil :limit => %w(Chimay Pabst Redhook) t.timestamps end #case sensitive password (encrypted) execute "ALTER TABLE `animals` ADD `password` varchar(25) character set latin1 collate latin1_bin NOT NULL"

  25. Create (Unique) Indices create_table :species do |t| t.string :name, :null => false, :limit => 25 end add_index :species, :name, :unique => true, :name => 'uk_species_name'

  26. ActiveRecord Uniqueness • class Species < ActiveRecord::Base • validates_uniqueness_of :name • end • Doesn't Guaranty Data Integrity!

  27. I Heart Foreign Keys • Referential Integrity

  28. The AR Way: Foreign Keys • class Species < ActiveRecord::Base • has_many :animals, :dependent => :nullify • end

  29. The Rails Way: Foreign Keys • class Species < ActiveRecord::Base • has_many :animals, :dependent => :nullify • end • Dependent Value SQL Equivalent: • :nullify => ON DELETE SET NULL • :delete_all => ON DELETE CASCADE • :destroy => No SQL equivalent. Every association is instantiated and and callbacks are executed before destruction

  30. Redhills Foreign Key Migration Plugin to the rescue! • add_column :animals, :species_id, :integer, • :references => :species, • :name => 'fk_animal_species', • :on_delete => :set_null, • :on_update => :cascade • ALTER TABLE `animals` ADD `species_id` int(11); • ALTER TABLE animals ADD CONSTRAINT fk_animal_species FOREIGN KEY (species_id) REFERENCES species (id) ON UPDATE CASCADE ON DELETE SET NULL;

  31. Primary Keys CREATE TABLE `animals` ( `id` int(11) NOT NULL auto_increment, `name` varchar(35) NOT NULL, `email` varchar(40) default NULL, `fav_beer` enum('Guiness','Pabst','Redhook','Chimay') default 'Pabst', `species_id` int(11) default NULL, `created_at` datetime default NULL, `updated_at` datetime default NULL, `password` varchar(25) character set latin1 collate latin1_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `species` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255), PRIMARY KEY (`id`),) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

  32. Modify the Rails Primary Key Change type with change_column MySQL Migration Optimization Plugin create_table :animals, :primary_key => "special_key", :primary_column => { :type => :integer, :limit => 2, :precision => :unsigned, :scale => 3 } do |t| end CREATE TABLE `animals` (`special_key` smallint(3) UNSIGNED NOT NULL auto_increment PRIMARY KEY) ENGINE=InnoDB;

  33. Advanced ActiveRecord • Insert and update options • Import bulk data • Finder Options • Tools: plugin AR-Extensions

  34. ActiveRecord on Steroids: ar-extensions plugin • Additional Create and Update options • save(options={}) • save!(options={}) • create(args, options={}) • create!(args, options={}) • Options • :ignore • :on_duplicate_key_update • :keywords • :reload • :pre_sql • :post_sql

  35. :ignore • Standard ActiveRecord: • Create the record if it does not already exist • unless Animal.find_by_name('Jerry G') • Animal.create!(:name => 'Jerry G', • :password => 'jerry') • end

  36. :ignore • Ignore duplicates! One query, less code, fewer queries! • Animal.create!({:name => 'Jerry G', • :password => 'jerry'}, • {:ignore => true})

  37. :on_duplicate_key_update • Update the record if it exists, if not create a new one. • A lot of code to upsert and performs two SQL queries! • jerry = Animal.find_by_name 'Jerry G' • jerry ||= Animal.new(:name => 'Jerry G') • jerry.password = 'frenchfry' • jerry.save!

  38. :on_duplicate_key_update • jerry = Animal.new :name => 'Jerry G', • :password => 'frenchfry' • jerry.save! :on_duplicate_key_update => • [:password, :updated_at] • INSERT INTO animals • (`name`, `updated_at`, `species_id`, • `password`,`email`, `created_at`) • VALUES('Jerry G', '2009-03-15 06:17:51', NULL, • 'frenchfry', NULL, '2009-03-15 06:17:51') • ON DUPLICATE KEY UPDATE • `animals`.`password`=VALUES(`password`), • `animals`.`updated_at`=VALUES(`updated_at`)

  39. Reloading the instance • AR Data can become inconsistent with DB after an IGNORE, UPDATE, or ON DUPLICATE KEY UPDATE • reload executes more queries • For UPDATE the duplicate is automatically reloaded • jerry.email = 'jerry@snowgiraffe.com' • jerry.save! :on_duplicate_key_update => • [:password, :updated_at], • :reload => true, • :duplicate_columns => [:name]

  40. More Customization • jerry.save(:keywords => 'LOW_PRIORITY', • :pre_sql => '/*Pre comment*/', • :post_sql => • "/*#{__FILE__} #{__LINE__}*/") • /*Pre comment*/ UPDATE LOW_PRIORITY `animals` • SET `created_at` = '2009-03-15 06:13:48', • `species_id` = NULL, `email` = NULL, • `password` = 'frenchfry', • `updated_at` = '2009-03-15 06:45:38', • `name` = 'Jerry G' • WHERE `id` = 7/*animal_controller.rb 147 */

  41. Import (Bulk Insert) • Instead of one-by-one, insert a ton of records fast

  42. Import (Bulk Insert) • Standard way: Insert each animal one by one • Animal.create!(:name => 'dolly dolphin', • :password => 'dolly') • Animal.create!(:name => 'big birdie', • :password => 'birdie') • and so on….

  43. Fast Import: One INSERT • animals = [ Animal.new(:name => 'dolly dolphin', • :password => 'dolly'), • Animal.new(:name => 'big birdie', • :password => 'birdie')] • Animal.import animals • INSERT INTO `animals` • (`id`,`name`,`email`,`fav_beer`,`created_at`,`updated_at`,`password`) • VALUES • (NULL,'dolly dolphin',NULL,'Pabst', • '2009-03-20 00:17:15','2009-03-20 00:17:15','dolly'), • (NULL,'big birdie',NULL,'Pabst', • '2009-03-20 00:17:15','2009-03-20 00:17:15','birdie') • ON DUPLICATE KEY UPDATE `animals`.`updated_at`=VALUES(`updated_at`)

  44. Fastest Import: fewer columns • columns = [ :name, :password ] • values = [['dolly dolphin', 'dolly'], • ['big birdie', 'birdie']] • options = {:validate => false, • :timestamps => false} • Animal.import columns, values, options • INSERT INTO `animals` (`name`,`password`) • VALUES • ('dolly dolphin','dolly'),('big birdie','birdie')

  45. Insert Select • Standard: Query and Insert one by one • Species.find(:all).each do |s| • SpeciesZoo.create!(:species_id => s.id, • :zoo_id => zoo.id, • :extra_info => 'awesome') • end • Executes a query for each species • INSERT INTO `species_zoos` (`zoo_id`, `id`, `species_id`, `extra_info`) • VALUES (1, 3, 3, 'awesome') • INSERT INTO `species_zoos` (`zoo_id`, `id`, `species_id`, `extra_info`) • VALUES (1, 3, 2 , 'awesome') • And so on…

  46. Insert Select Import • SpeciesZoo.insert_select( • :from => :species, • :select => ['species.id, ?', zoo], • :into => [:species_id, :zoo_id]) • One INSERT statement • INSERT INTO `species_zoos` • ( `species_id`, `zoo_id` ) • SELECT species.id, 1 FROM `species`

  47. Not so good for slave replication Can be used as a sandbox then imported into a real table with ar-extensions gem Animal.create_temporary_table do |t| t.create!(:name => 'giraffe', :password => 'goo') Animal.insert_select( :from => t, :select => [:name, :password, :fav_beer], :into => [:name, :password, :fav_beer], :on_duplicate_key_update => [:password, :fav_beer]) end Temporary Tables

  48. Customizing Find • Additional finder options • :keywords • :pre_sql • :post_sql • :index_hint

  49. Customizing Find • Animal.find(:all, • :conditions => ['name = ?', 'Jerry G'], • :keywords => 'HIGH_PRIORITY', • :pre_sql => '/*Pre comment*/', • :post_sql => 'FOR UPDATE /*After the fact*/', • :index_hint => 'USE INDEX (uk_animal_name)' • ) • /*Pre comment*/ SELECT HIGH_PRIORITY * • FROM `animals` USE INDEX (uk_animal_name) • WHERE (name = 'Jerry G') FOR UPDATE • /*After the fact*/

  50. Need more? Get dirty with find_by_sql • sql = Animal.send :finder_sql_to_string, • :conditions => ['name = ?', 'Jerry G'] • sql.gsub! /WHERE/, 'where /* Dirty hand */' • Animal.find_by_sql sql

More Related