Ruby on Rails & Databases

bricklayerbelchedInternet and Web Development

Feb 5, 2013 (4 years and 9 months ago)

140 views

Data Modeling

Data Modeling


A
database

can “model” a “world” which is seen as:


a collection of entities,


relationships among entities.


An
entity(
-
instance)

is an individual “object” that exists and
is distinguishable from other individuals.



Example: specific person, company, event, plant


Entities have
attributes


Example: people have
names
and
addresses


An
entity set (
also
entity type)

is a set of entities of the
same type that share the same properties.



Example: set of all persons, companies, trees, holidays

Entity Sets
customer

and
loan

customer
-
id customer
-

customer
-

customer
-

loan
-

amount


name street city number

Attributes


An entity is represented by a set of attributes,


i.e. descriptive properties possessed by all members of an entity
set.


Example:



customer = (customer
-
id, customer
-
name,






customer
-
street, customer
-
city)


loan = (loan
-
number, amount)


Domain


the set of permitted values for each attribute


Attribute types:


Simple

and
composite

attributes.


Single
-
valued

and
multi
-
valued

attributes


E.g. multivalued attribute:
phone
-
numbers


Derived

attributes


Can be computed from other attributes


E.g.
age
, given date of birth

Composite Attributes

Relationship Sets


A relationship (
-
instance) is an association among several entities

Example:


Hayes


depositor

A
-
102


customer

entity

relationship [set]

account

entity


A
relationship
set is a mathematical relation among
n



2 entities,
each taken from entity sets


{(
e
1
,
e
2
, …
e
n
) |
e
1



E
1
,
e
2



E
2
, …,
e
n



E
n
}


where (
e
1
,
e
2
, …,
e
n
) is a relationship


Example:




(Hayes, A
-
102)


depositor

Relationship Set
borrower

Relationship Sets (Cont.)


An
attribute

can also
be property of a
relationship set.


For instance, the
depositor
relationship set
between entity sets
customer
and
account
may have
the attribute
access
-
date

Degree of a Relationship Set


Refers to number of entity sets that participate in a
relationship set.


Relationship sets that involve two entity sets are
binary

(or
degree two). Generally, most relationship sets in an E
-
R
schema are binary.


Relationship sets may involve more than two entity sets.


E.g. Suppose employees of a bank may have jobs (responsibilities)
at multiple branches, with different jobs at different branches.
Then there is a ternary relationship set between entity sets
employee, job and branch


Relationships between more than two entity sets are relatively
rare. Most relationships are binary.

Mapping Cardinalities


Express the number of entities to which another
entity can be associated via a relationship set.


Most useful for binary relationship sets.


For a binary relationship set, the mapping
cardinality must be one of the following types:


One
-
to
-
one


One
-
to
-
many


Many
-
to
-
one


Many
-
to
-
many

One
-
to
-
one

One
-
to
-
many

Note: Some elements in A and B may not be mapped to any

elements in the other set

Many
-
to
-
one

Many
-
to
-
many

Note: Some elements in A and B may not be mapped to any

elements in the other set

Cardinalities affect ER
Design


Can make
access
-
date
an
attribute of account, instead
of a relationship attribute, if
each account can have only
one customer


I.e., the relationship from
account

to
customer

is
many
-
to
-
one, or
equivalently,
customer

to
account

is one
-
to
-
many

E
-
R Diagrams


Rectangles

represent entity sets.


Diamonds

represent relationship sets.


Lines

link attributes to entity sets and entity sets to relationship sets.


Ellipses

represent attributes


Double ellipses

represent
multivalued

attributes.


Dashed ellipses

denote
derived

attributes.


Underline

indicates primary key attributes

E
-
R Diagram With Composite,
Multivalued, and Derived Attributes

multi
-
valued

derived

composite

primary key

Relationship Sets with
Attributes

Roles


Roles are indicated in E
-
R diagrams by labeling the lines
that connect diamonds to rectangles.


Role labels are optional, and are used to clarify semantics of
the relationship

Cardinality Constraints


We express cardinality constraints by drawing either a
directed line (

), signifying “one,” or an undirected line (

),
signifying “many,” between the relationship set and the entity
set.


E.g.: One
-
to
-
one relationship:


A customer is associated with at most one loan via the
relationship
borrower


A loan is associated with at most one customer via
borrower



One
-
To
-
Many Relationship


In the one
-
to
-
many relationship


a loan is associated with at most one
customer via
borrower
,


a customer is associated with several (>=
0) loans via
borrower

Many
-
To
-
Many Relationship


A customer is associated with several
(possibly 0) loans via borrower


A loan is associated with several (possibly 0)
customers via borrower

Participation


Total participation (indicated by double line): every entity in the
entity set participates in at least one relationship in the relationship
set


E.g. participation of loan in borrower is total



every loan must have a customer associated to it via borrower


Partial participation: some entities may not participate in any
relationship in the set


E.g. participation of customer in borrower is partial

Alternative Notation for
Cardinality Limits


Cardinality limits can also express
participation constraints

Specialization &
Generalization


Similar concept to inheritance


Designated by the ISA relationship


If A ISA B then every A entity is also considered to be a
B entity


Depicted by a triangle labeled with ISA



Can be done top
-
down (specialization) or bottom
-
up (generalization)


Specialization and Generalization are simple inverses the
the design process

ER Design


ER design is
subjective
. There are
often many ways to model a given
scenario!


Analyzing alternatives can be tricky,
especially for a large enterprise.


Ensuring good database design:


resulting relational schema should be
analyzed and refined further
(normalization).

ER Design to DB Schema


Entities become tables with the given
attributes


Relationships become foreign keys


Many to One and One to Many


Foreign key to the One generally goes in the Many


Many to Many


Create a relationship table with foreign keys to both
sides of the relationship


Also add in any attributes of the relationship

ER Design to DB Schema


3 tables


customer
-

id, name, street, city


account
-

number, balance


depositor


primarykey, customer_id, account_id, access
-
date

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


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

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


: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)


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


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

Delete


delete & delete_all


Order.delete(123)


Order.delete([1,2,3,4])


Order.delete_all([“price > ?”, maxprice])


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


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

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)

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


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.column "kind", :string


t.column "description", :string


t.column "quantity", :integer


t.column "asset_id", :integer


t.column "condition_id", :integer


t.column "location_id", :integer


t.column "arrival_time", :integer

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


001_create_assets.rb

002_create_assets.rb

Misc

Scaffolding


Auto
-
generated framework for manipulating a model


Can be created dynamically at runtime or statically


class AdminController < ApplicationController



scaffold :product


end


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







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"


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