Ruby on Rails & Databases: Active Record Basics & CRUD Operations

1. Ruby on Rails & Databases

2. Ruby on Rails & Databases • Active Record • Active Record in Rails • CRUD & Other Stuff • Mapping Cardinalities • Migrations • Demo

3. Active Record • Object Relational Mapping (ORM) tool supplied with Rails • Maps • Tables to classes • Rows to objects • Columns to object attributes • determined at run time

4. Active Record Basics • Create a subclass of ActiveRecord::Base • class Employee < ActiveRecord::Base end • Rails assumes that • the name of the table is the plural form of the class name • if the name contains multiple camel-case words, the table name has underscores between the words We don’t declare the attributes

5. Active Record in Rails • Active Record is used for Model • script/generate model person • Will create app/models/person.rb • class Person < ActiveRecord::Base end • Maps to ‘people’ table in database • can be changed • class Person < ActiveRecord::Base set_table_name “blah” end • Columns automatically map to class variables of the same name

6. CRUD & Other Stuff • Create • Read • Update • Delete • Other ActiveRecord Functions

7. Create • Create row by creating object an_order = Order.new an_order.name = “Dave Thomas” an_order.address = “122 Main” an_order.phone = 2125551212 an_order.save an_order = Order.new( :name => “Dave Thomas”, :address => “122 Main”, :phone => 2125551212 ) an_order.save Order.new do |o| o.name = “Dave Thomas” o.address = “122 Main” o.phone = 2125551212 o.save end Note: We didn’t need to set a primary key. Rails assumes “id” is primary key and set autoincrement

8. Create • Can also use create method • Creates a new object and saves it • Takes a hash or an array of hashes an_order = Order.create( [ { :name => “Dave Thomas”, :address => “122 Main”, :phone => 2125551212 }, { :name => “Another Name”, :address => “blah”, :phone => 1234567890 } ] ) an_order = Order.create( :name => “Dave Thomas”, :address => “122 Main”, :phone => 2125551212 )

9. Read • We need to specify which rows we want • Rails will return objects containing the data from those rows in the database • Use the find method with one or more primary keys • an_order = Order.find(27) • product_list = Order.find(params[“product_list”]) • find() will throw a RecordNotFound exception if any of the requested primary keys cannot be found

10. Read • find() also has other options • can pass :all or :first along with other parameters • :conditions => “name = ‘Dave’” • corresponds to WHERE clause • :order => “name” • corresponds to ORDER BY clause • :limit => pagesize • corresponds to LIMIT • :offset => pagenum * pagesize • use in connection with :limit to step through query results • an_order = Order.find(:first, :conditions => “name = ‘Dave Thomas’”) • orders = Order.find(:all, :conditions => “name = ‘Dave’”, :order => “pay_type, shipped_at DESC”, :limit => 10)

11. Read • Allowing for externally generated parameters • pname = params[:name] orders = Order.find(:all, :conditions => [“name = ?”, pname]) • orders = Order.find(:all, :conditions => [“name = :name”, {:name => pname}]) • Can also write your own SQL • orders = Orders.find_by_sql(“select * from orders”) • single parameter - SQL string • May also be an array where first element is SQL with place holders. The rest is a list of values or hash • Nice for hard queries or performance

12. Update • Simple • find the row or rows using find • update necessary fields • save • Also works with an array for multiple update • orders = Order.find(:all, :conditions => “name like ‘Dave%’”) orders[0].name = “Fred” etc. • May also use update() or update_all() • order = Order.update(123, :name => “F”, :address => “blah”) • finds, updates, saves, and returns object • result = Order.update_all(“set clause”, “where clause”) • returns number of rows updated order = Order.find(123) order.name = “Fred” order.save

13. Delete • delete & delete_all • Order.delete(123) • Order.delete([1,2,3,4]) • Order.delete_all([“price > ?”, maxprice]) • destroy & destroy_all • order.find(123) • order.destroy • Order.destroy_all([“price > ?”, maxprice]) • destroy and destroy_all ensure that ActiveRecord callback and validation functions are invoked • preferred methods

14. Other ActiveRecord Stuff • Magic column names • id • primary key • created_at, created_on, updated_at, updated_on • automatically updated with timestamps • xxx_id • foreign key • Find by value of a particular column • Dynamically associates a find_by and find_all_by method with each column • order = Order.find_by_name(“Dave Thomas”) • order = Order.find_by_address(“123 Main”) • orders = Order.find_all_by_email(params[“email”])

15. Mapping Relationship Cardinalities

16. Relationships between Tables • Relationships are established using foreign keys • Foreign key columns should be • named using the singular form of the table name with _id appended • example: a foreign key for the table products should be product_id • This expresses relationship, but not the cardinality of the relationship

17. Specifying Relationships • Relationships are specified by adding declarations to models • has_one, has_many, belongs_to, has_and_belongs_to_many • Rule of thumb • Foreign key always has the belongs_to declaration

18. One-to-onenote: the model for the table that contains the foreign key *always* has the belongs_to declaration

19. One-to-many

20. Many-to-manyMany-to-many associations are symmetrical—both of the joined tables declare their association with each other using has_and_belongs_to_many.

21. Relationship methods • Relationship declarations also introduce methods to the associated objects. • dynamically created • named using the table that it refers to • Help navigate between the linked objects

22. belongs_to methods • product(force_reload=false) Return the associated product (or nil if no associated product exists) The result is cached, and the database will not be queried again when this association is subsequently used unless true is passed as a parameter. • product=obj Associate this line item with the given product, setting the product_id column in this line item to the product’s primary key. If the product has not been saved, it will be when the line item is saved, and the keys will be linked at that time. • build_product(attributes={}) Construct a new product object, initialized using the given attributes. This line item will be linked to it. The product will not yet have been saved. • create_product(attributes={}) Build a new product object, link this line item to it, and save the product. class LineItem < ActiveRecord::Base belongs_to :productend

23. Example class Product < ActiveRecord::Base has_many :line_items end class LineItem < ActiveRecord::Base belongs_to :product end item = LineItem.find(2) # item.product is the associated Product object puts "Current product is #{item.product.id}" puts item.product.title item.product = Product.new(:title => "Rails for Java Developers" , :description => "..." , :image_url => "http://....jpg" , :price => 34.95, :available_at => Time.now) item.save! # save or raise exception puts "New product is #{item.product.id}" puts item.product.title Current product is 1 Programming Ruby New product is 2 Rails for Java Developers ActiveRecord takes care of the details It created a new product and linked the LineItem to it via the foreign key

24. has_one • has_one is paired with belongs_to • expresses a one-to-one relationship • Creates the same methods as belongs_to • named appropriately to reflect related table • Be careful of orphans • If no child exists for a parent, the has_one association will be set to nil • If you assign a new object to a has_one association, the existing object will be updated to remove its foreign key association with the parent (key set to nill). This orphans records!

25. Example

26. has_many • Defines an attribute that behaves like a collection of the child objects. • You can access the children as an array, find particular children, and add new children. order = Order.new params[:products_to_buy].each do |prd_id, qty| product = Product.find(prd_id) order.line_items << LineItem.new(:product => product, :quantity => qty) # This added a new LineItem to the line_items table and linked it up end order.save

27. Something to note… • The append operator (<<) normally just appends a new item to an array. • Thanks to Active Record – it also arranges to link the line item back to the order by setting the foreign key to the order_id. • Also the line items will be saved when the parent order is saved.

28. has_many cont… • You can also iterate over children of a has_many relationship. order = Order.find(123) total = 0.0 order.line_items.each do |li| total += li.quantity * li.unit_price end

29. :destroy vs :delete_all • :dependant => :destroy traverses child table calling destroy on each rows with a foreign key reference. • :dependant => :delete_all will cause child rows to be deleted in a single SQL statement – faster! • However - :delete_all is only appropriate if the child table is only used by the parent table, and has no hooks that it uses to perform any actions on deletion (more on these later).

30. has_many methods…new.. orders(force_reload=false) Returns an array of orders associated with this customer (which may be empty if there is none). The result is cached, and the database will not be queried again if orders had previously been fetched unless true is passed as a parameter. orders <<order Adds order to the list of orders associated with this customer. orders.push(order1, ...) Adds one or more order objects to the list of orders associated with this customer. concat is an alias for this method. orders.replace(order1, ...) Replaces the set of orders associated with this customer with the new set. Detects the differences between the current set of children and the new set, optimizing the database changes accordingly. orders.delete(order1, ...) Removes one or more order objects from the list of orders associated with this customer. If the association is flagged as :dependent => :destroy or :delete_all, each child is destroyed. Otherwise it sets their customer_id foreign keys to null, breaking their association. orders.delete_all Invokes the association’s delete method on all the child rows.

31. has_many methods…new.. orders.destroy_all Invokes the association’s destroy method on all the child rows. orders.clear Disassociates all orders from this customer. Like delete, this breaks the association but deletes the orders from the database only if they were marked as :dependent. orders.find(options...) Issues a regular find call, but the results are constrained to return only orders associated with this customer. Works with the id, the :all, and the :first forms. orders.length Forces the association to be reloaded and then returns its size.

32. has_many methods…new orders.count(options...) Returns the count of children. If you specified custom finder or count SQL, that SQL is used. Otherwise a standard Active Record count is used, constrained to child rows with an appropriate foreign key. Any of the optional arguments to count can be supplied. orders.size If you’ve already loaded the association (by accessing it), returns the size of that collection. Otherwise returns a count by querying the database. Unlike count, the size method honors any :limit option passed to has_many and doesn’t use finder_sql. orders.empty? Equivalent to orders.size.zero?. orders.sum(options...) Equivalent to calling the regular Active Record sum method on the rows in the association. Note that this works using SQL functions on rows in the database and not by iterating over the in-memory collection. orders.uniq Returns an array of the children with unique ids. orders.build(attributes={}) Constructs a new order object, initialized using the given attributes and linked to the customer. It is not saved. orders.create(attributes={}) Constructs and saves a new order object, initialized using the given attributes and linked to the customer.

33. Migrations

34. Migrations • Rails is set up to encourage agile development • always making changes • even to the database • To support this, Rails provides a mechanism to set up and modify the database • Goal 1: Apply only those changes necessary to move a database from version x to version y • Goal 2: Shield the developer from the specific implementation details of the underlying database

35. Migrations • Migration skeleton files are created every time you generate a model • contained in db/migrate • Run the migration using rake • rake db:migrate • Migration files have a sequence number • acts as a version number • apply all migrations with sequence number greater than the database version • Can pick a specific version • rake db:migrate VERSION=12

36. Migration Files • Migrations are subclasses of ActiveRecord::Migration • Contains at least up and down class methods • up = apply changes • down = undo changes class CreateOrders < ActiveRecord::Migration def self.up end def self.down end end

37. Migration Methods • create_table • accepts a table name and a ruby block • add_column and remove_column • accepts table name and column name • and column type if adding a column • rename_column • accepts table name, column name, new column name • change_column • accepts table name, column name, new type • drop_table • accepts table name

38. Migration Examples class CreateAssets < ActiveRecord::Migration def self.up create_table :assets do |t| t.string :kind t.string :description t.integer :quantity t.integer :asset_id t.integer :condition_id t.integer :location_id t.integer :arrival_time end end def self.down drop_table :assets end end class CreateAssets < ActiveRecord::Migration def self.up add_column :assets, :updated_at, :timestamp end end def self.down remove_column :assets, :updated_at end end 002_create_assets.rb 001_create_assets.rb add_column :table, :col_name, :type remove_column :table, :col_name

39. Misc

40. Scaffolding • Auto-generated framework for manipulating a model • Created statically • script/generate scaffold product admin • accepts model name and controller name as parameters • generates static view files and controller methods • Good starting point, but it will need to be replaced

41. ActiveRecord Validators • ActiveRecord allows validation filters • Run before saving the data • Puts any errors in session • Can also define a validate method validates_presence_of :title, :description, :image_url validates_numericality_of :price validates_uniqueness_of :title validates_format_of :image_url, :with => %r{\.(gif|jpg|png)$}i, :message => "must be a URL for a GIF, JPG, or PNG image"

42. Filters • Intercept calls to action methods • before they are invoked • after they return • Example class admin_controller before_filter :authenticate, :except => [:loginlogout, :login, :logout] def authenticate if (session[:loggedin] == true && session[:loginrole] == "admin") return true else return false end end

43. Demo

44. Steps • Generate Model • Edit initial Migrations • Generate Controller • Generate static scaffold • Adjust • Regenerate static scaffold • Modify to fit your design