Ruby on Rails

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 ActiveRecordBase
• class Employee lt ActiveRecordBase 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 dont 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 lt ActiveRecordBase end
• Maps to people table in database
• can be changed
• class Person lt ActiveRecordBase set_table_na
  me 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 [phone] an_order.save
an_order Order.new( name gt Dave
Thomas, address gt 122 Main, phone
gt 2125551212 ) an_order.save
Order.new do o o.name Dave Thomas
o.address 122 Main o.phone [phone]
o.save end
Note We didnt 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 gt Dave
Thomas, address gt 122 Main,
phone gt 2125551212 , name gt
Another Name, address gt blah,
phone gt 1234567890 )
an_order Order.create( name gt Dave
Thomas, address gt 122 Main, phone
gt 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(paramsproduct_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 gt name Dave
• corresponds to WHERE clause
• order gt name
• corresponds to ORDER BY clause
• limit gt pagesize
• corresponds to LIMIT
• offset gt pagenum pagesize
• use in connection with limit to step through
  query results
• an_order Order.find(first, conditions
  gt name Dave Thomas)
• orders Order.find(all, conditions gt
  name Dave, order gt pay_type,
  shipped_at DESC, limit gt 10)

11
Read

• Allowing for externally generated parameters
• pname paramsname
• orders Order.find(all, conditions gt
  name ?, pname)
• orders Order.find(all, conditions gt
  name name, name gt 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 gt name
  like Dave)
• orders0.name Fred
• etc.
• May also use update() or update_all()
• order Order.update(123, name gt F, address
  gt 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 gt ?, maxprice)
• destroy destroy_all
• order.find(123)
• order.destroy
• Order.destroy_all(price gt ?, 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(paramsemail)

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
symmetricalboth 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_reloadfalse)
• 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.
• productobj
• Associate this line item with the given product,
  setting the product_id column in this line item
  to the products 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 lt ActiveRecordBase
belongs_to productend
23
Example

• class Product lt ActiveRecordBase
• has_many line_items
• end
• class LineItem lt ActiveRecordBase
• 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 ite
m.product Product.new(title gt "Rails for Java
Developers" , description gt "..."
, image_url gt "http//....jpg" , price gt
34.95, available_at gt 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
• paramsproducts_to_buy.each do prd_id, qty
• product Product.find(prd_id)
• order.line_items ltlt LineItem.new(product gt
  product, quantity gt 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 (ltlt) 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 gt destroy traverses child table
  calling destroy on each rows with a foreign key
  reference.
• dependant gt 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 methodsnew..

• orders(force_reloadfalse)
• 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 ltltorder
• 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 gt destroy
• or delete_all, each child is destroyed.
  Otherwise it sets their customer_id
• foreign keys to null, breaking their association.

31
has_many methodsnew..

• orders.destroy_all
• Invokes the associations 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 methodsnew

• 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 youve 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 doesnt 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)

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 dbmigrate
• 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 dbmigrate VERSION12

36
Migration Files

• Migrations are subclasses of ActiveRecordMigrati
  on
• Contains at least up and down class methods
• up apply changes
• down undo changes

class CreateOrders lt ActiveRecordMigration 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 lt ActiveRecordMigration
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 lt ActiveRecordMigration
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 gt
r\.(gifjpgpng)i, message gt "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 gt loginlogout, login,
logout def authenticate if
(sessionloggedin true sessionloginrole
"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