Relational Databases and Web Integration Dr. Carl Pulley
[email protected] Monday, 17 August 2009
Database Design Often re-factor models/tables into multiple tables technique can be used as a type of data compression References (cf. foreign key) allow relationship to be maintained/kept
Monday, 17 August 2009
Example Date/Time
HTTP Request
Argument
Agent
10/11/2008 00:00
GET
/login
Firefox
10/11/2008 00:00
GET
/session/new
Firefox
10/11/2008 00:10
GET
/session/new
IE 5.1
10/11/2008 00:10
POST
/session/new?session=abced2345
IE 5.1
10/11/2008 00:20
GET
/index.html
Safari
10/11/2008 00:30
GET
/account/1234/edit
IE 6
10/11/2008 00:30
POST
/account/1234
IE 6
10/11/2008 00:30
GET
/account/1234/show
IE 6
10/11/2008 00:40
GET
/index.html
Firefox
10/11/2008 00:50
GET
/
Opera
Monday, 17 August 2009
Example Notice the potential repetition in the argument and agent columns with a large table this is important each repeated column string wastes space impacts upon efficiency of database queries
Monday, 17 August 2009
Solution Date/Time
HTTP Request
Argument Agent _id _id
10/11/2008 00:00
GET
1
1
10/11/2008 00:00
GET
2
1
10/11/2008 00:10
GET
2
2
10/11/2008 00:10
POST
3
2
10/11/2008 00:20
GET
4
3
10/11/2008 00:30
GET
5
10/11/2008 00:30
POST
6
4
10/11/2008 00:30
GET
7
4
10/11/2008 00:40
GET
4
1
10/11/2008 00:50
GET
8
5
Monday, 17 August 2009
belongs to
4
Id
Agent
1
Firefox
2
IE 5.1
3
Safari
4
IE 6
5
Opera
Id
Argument
1
/login
2
/session/new
3
/session/new? session=abced2345
4
/index.html
5
/account/1234/edit
6
/account/1234
7
/account/1234/show
8
/
belongs to
Foreign Keys Table attributes that: end in _id have the name of another table Model is informed of the foreign key relationship via belongs_to
Monday, 17 August 2009
Rails Solution class Argument < ActiveRecord:Base end
create_table “arguments” do |t| t.string value ... end
class Agent < ActiveRecord:Base end
create_table “agents” do |t| t.string value ... end
class Log < ActiveRecord:Base belongs_to :argument belongs_to :agent end
create_table “logs” do |t| t.integer “argument_id” t.integer “agent_id” ... end
Rails Model
Table Schema
Monday, 17 August 2009
Rails Solution Navigate foreign key relationships as if they were an attribute! entry = Log.find(:first) puts “Request argument size: #{entry.argument.value.size}” puts “Agent string: #{entry.agent.value}”
Monday, 17 August 2009
1-to-1 and 1-to-many Sometimes we wish to constrain our relationships in some way total or partial? one-to-one (or zero!)? one-to-many? data lists many-to-many? look at these next week
Monday, 17 August 2009
Rails Relationships Can enforce relationships using keywords in Rails models these are placed at the non-foreign key side of the relationship class Argument < ActiveRecord:Base has_many :logs end
Monday, 17 August 2009
class Agent < ActiveRecord:Base has_many :logs end
Rails and SQL Rails (Abstract)
SQL (Concrete)
Instance
Row
entry = Log.new(..); ..; entry.save
INSERT (..) INTO log
Attribute
Column
entry.request
SELECT request FROM log WHERE id = ..
Relationship
Foreign Keys and Inner Join
entry.argument.value
SELECT value FROM argument a, entry e WHERE a.id = e.argument_id LIMIT 1
Searching
Query
Log.find(:all, :conditions => {..})
SELECT * FROM log WHERE ..
Monday, 17 August 2009
Change Control With multiple people working on the same piece of code it becomes essential to maintain a history of how the code has changed or altered over time ie. change management control (eg. subversion) We can also do this with databases!!
Monday, 17 August 2009
Migrations A migration documents how a database alters over time cf. svn
info
Each migration corresponds to a single database change cf. svn
commit
or svn
update
Database changes can be undone! cf. svn
Monday, 17 August 2009
revert
Example Relocate a column into another table class MoveRequestIntoArgs < ActiveRecord::Migration def self.up remove_column :logs, :request add_column :arguments, :request, :string end def self.down remove_column :arguments, :request add_column :logs, :request, :string end end
Monday, 17 August 2009