Transcript RubyOnRails-Databases

Ruby on Rails & Databases
Ruby on Rails & Databases
•
•
•
•
•
•
Active Record
Active Record in Rails
CRUD & Other Stuff
Mapping Cardinalities
Migrations
Demo
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
Active Record Basics
• Create a subclass of ActiveRecord::Base
– class Employee < ActiveRecord::Base
end
• Rails assumes that
We don’t declare
the attributes
– 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
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
CRUD & Other Stuff
•
•
•
•
•
Create
Read
Update
Delete
Other ActiveRecord Functions
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
Order.new do |o|
o.name = “Dave Thomas”
o.address = “122 Main”
o.phone = 2125551212
o.save
end
an_order = Order.new(
:name => “Dave Thomas”,
:address => “122 Main”,
:phone => 2125551212 )
an_order.save
Note: We didn’t need to
set a primary key. Rails
assumes “id” is primary key
and set autoincrement
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 )
an_order = Order.create(
[ { :name => “Dave Thomas”,
:address => “122 Main”,
:phone => 2125551212
},
{ :name => “Another Name”,
:address => “blah”,
:phone => 1234567890
}])
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
Read
•
find() also has other options
– can pass :all or :first along with other parameters
• :conditions => “name = ‘Dave’”
–
corresponds to WHERE clause
–
corresponds to ORDER BY clause
–
corresponds to LIMIT
–
use in connection with :limit to step through query results
• :order => “name”
• :limit => pagesize
• :offset => pagenum * pagesize
•
•
an_order = Order.find(:first,
:conditions => “name = ‘Dave Thomas’”)
orders = Order.find(:all,
:conditions => “name = ‘Dave’”,
:order => “pay_type, shipped_at DESC”,
:limit => 10)
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
Update
• Simple
– find the row or rows using find
– update necessary fields
– save
order = Order.find(123)
order.name = “Fred”
order.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
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
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”])
Mapping Relationship
Cardinalities
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
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
One-to-one
note: the model for the table that contains the
foreign key *always* has the belongs_to declaration
One-to-many
Many-to-many
Many-to-many associations are symmetrical—both of the
joined tables declare their association with each other using
has_and_belongs_to_many.
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
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 :product
end
Example
class Product < ActiveRecord::Base
item = LineItem.find(2)
has_many :line_items
# item.product is the associated Product object
end
class LineItem < ActiveRecord::Base
belongs_to :product
end
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,
Current product is 1
Programming Ruby
New product is 2
Rails for Java Developers
:available_at => Time.now)
item.save!
# save or raise exception
puts "New product is #{item.product.id}"
puts item.product.title
ActiveRecord takes care of the details
It created a new product and linked the
LineItem to it via the foreign key
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!
Example
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
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.
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
: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).
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.
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.
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.
Migrations
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
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
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
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
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
001_create_assets.rb
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
add_column :table, :col_name, :type
remove_column :table, :col_name
Misc
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
ActiveRecord Validators
• ActiveRecord allows validation filters
• Run before saving the data
• Puts any errors in session
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"
• Can also define a validate method
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
Demo
Steps
•
•
•
•
•
•
•
Generate Model
Edit initial Migrations
Generate Controller
Generate static scaffold
Adjust
Regenerate static scaffold
Modify to fit your design