229-511 Web Application Development Technology เทคโนโลยีสำหรับการพัฒนาโปรแกรมประยุกต์เว็บ

1. 229-511Web Application Development Technologyเทคโนโลยีสำหรับการพัฒนาโปรแกรมประยุกต์เว็บ Suntorn WitosurapotPhone: 074 287369 or Email: wsuntorn@coe.psu.ac.thNovember 2009

2. Lecture 4Database’s Table Relationshipsin Rails Agile Programming, MVC Architecture & the practice with RoR

3. Outline • Review • Rails & MVC • Database & Data Modeling • Rails and Databases • Active Record Basics • Mapping Cardinalities • Migrations • Exercise Agile Programming, MVC Architecture & the practice with RoR

4. Review: Rails • Web Framework for Ruby • Designed to make it easier to develop, deploy, and maintain web applications • Rails has a benefit in productivity • Comparing with J2EE: • J2EE currently has a benefit in scalability. Ifit’s a client facing system for millions ofconcurrent users – use J2EE. • If it’s an internal web application, definitelytake a look at this technology as a possibleway of shortcutting the long developmenttime for a J2EE web app. Agile Programming, MVC Architecture & the practice with RoR

5. Review: Rails (cont.) Yes…they are fairly comparable…. Agile Programming, MVC Architecture & the practice with RoR

6. Review: Database • Migration: A powerful and flexible tool for managing database changes • Allows table, index, and data creation scripts to be run in multiple environments with a very simple syntax. • Need to revert back to a previous DB version to work on a bug? 1 command. • Then refresh it back to the current dev version?1 command. Agile Programming, MVC Architecture & the practice with RoR

7. Example Migration Agile Programming, MVC Architecture & the practice with RoR

8. Review: MVC • Model– Used for persistence & relationships • View– Used for displaying the data • Controller– The logic of the application Agile Programming, MVC Architecture & the practice with RoR

9. Rails and MVC • Two Main components in Rails • Action Pack and Active Record • Active Record • Create an idea of something in the database • Has predefined function which can be used • Don’t need to worry about underlying tech • Action Pack • Controller and View are tightly coupled • Single Rails component • View code and Controller code are separate Agile Programming, MVC Architecture & the practice with RoR

10. Database development process Here, we will look at how to design a database that couldbe implemented in a relational database product (e.g., MySQL) Agile Programming, MVC Architecture & the practice with RoR

11. Data Modeling • A data model is a collection of concepts for describing data. • A schema is a description of a particular collection of data, using a given data model. • The relational model of data is the most widely used model today. • Main concept: relation, basically a table with rows and columns. • Every relation has a schema, which describes the columns, or fields Agile Programming, MVC Architecture & the practice with RoR

12. Representing the Model Techniques to Represent Aspects of the Data Model Agile Programming, MVC Architecture & the practice with RoR

13. Representing Classes and Attributes To design a database table for each class: • Class Attributes will become the field or column names of the table • When the data is added, each row (or record) in the table will represent an object Case study: >>Want to design a database table for each class. Agile Programming, MVC Architecture & the practice with RoR

14. Representing Relationships • the relationships between objects of different classes is established using the foreign keys. Foreign key field Agile Programming, MVC Architecture & the practice with RoR

15. Association between Tables • Association represents relationship between database tables, whose relationship is constructed through foreign keys. • They express relationships like "Project has one Project Manager" or "Project belongs to a Portfolio". • Cardinality relationship • One-to-one: A person has a single primary address • One-to-many: A school has many students • Many-to-many: A course has many students and each student take many courses. Agile Programming, MVC Architecture & the practice with RoR

16. Outline • Review • Rails & MVC • Database & Data Modeling • Rails and Databases • Active Record Basics • Mapping Cardinalities • Migrations • Exercise Agile Programming, MVC Architecture & the practice with RoR

17. 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 Agile Programming, MVC Architecture & the practice with RoR

18. 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 Agile Programming, MVC Architecture & the practice with RoR

19. Active Record in Rails • Active Record is used for Model • Ex: ruby script/generate model person • Will create app/models/person.rb class Person < ActiveRecord::Base end • Maps to ‘people’ table in database • Columns automatically map to class variables of the same name Agile Programming, MVC Architecture & the practice with RoR

20. CRUD & Other Stuff • Create • Read • Update • Delete • Other ActiveRecord Functions Agile Programming, MVC Architecture & the practice with RoR

21. 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 auto-increment Agile Programming, MVC Architecture & the practice with RoR

22. 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 } ] ) Agile Programming, MVC Architecture & the practice with RoR

23. 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 Agile Programming, MVC Architecture & the practice with RoR

24. 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) Agile Programming, MVC Architecture & the practice with RoR

25. 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 • Nice for hard queries or performance Agile Programming, MVC Architecture & the practice with RoR

26. Update order = Order.find(123) order.name = “Fred” order.save • 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 Agile Programming, MVC Architecture & the practice with RoR

27. Delete • delete & delete_all • Order.delete(123) • Order.delete([1,2,3,4]) • Order.delete_all([“price > ?”, maxprice]) Agile Programming, MVC Architecture & the practice with RoR

28. 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”]) Agile Programming, MVC Architecture & the practice with RoR

29. Outline • Review • Rails & MVC • Database & Data Modeling • Rails and Databases • Active Record Basics • Mapping Cardinalities • Migrations • Exercise Agile Programming, MVC Architecture & the practice with RoR

30. 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 Agile Programming, MVC Architecture & the practice with RoR

31. 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 Agile Programming, MVC Architecture & the practice with RoR

32. Note: the model for the table that contains the foreign key *always* has the belongs_to declaration One-to-one Agile Programming, MVC Architecture & the practice with RoR

33. One-to-many Agile Programming, MVC Architecture & the practice with RoR

34. Note: Many-to-many associations are symmetrical—both of the joined tables declare their association with each other using has_and_belongs_to_many. Many-to-many Agile Programming, MVC Architecture & the practice with RoR

35. 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 Agile Programming, MVC Architecture & the practice with RoR

36. Example 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 class Product < ActiveRecord::Base has_many :line_items end class LineItem < ActiveRecord::Base belongs_to :product end Result: Current product is 1 Programming Ruby New product is 2 Rails for Java Developers Note: ActiveRecord takes care of the details. It created a new product and linked the LineItem to it via the foreign key Agile Programming, MVC Architecture & the practice with RoR

37. Q:Is it a belongs_to or has_one association? class User < ActiveRecord::Base ??????? :account end class Account < ActiveRecord::Base ?????? :user end Recall: It depends on where you place the foreign key, which goes on the table for the class declaring the belongs_to relationship. Agile Programming, MVC Architecture & the practice with RoR

38. Outline • Review • Rails & MVC • Database & Data Modeling • Rails and Databases • Active Record Basics • Mapping Cardinalities • Exercise Agile Programming, MVC Architecture & the practice with RoR

39. Application Description: A basic musician database • Each artist will be one individual with a name, age, and list of songs (not albums). • Each song will have a title, duration and fit under one genre (แนวเพลง). • This design is assumed that • an artist may consist of several individuals and may have multiple albums containing multiple songs, and • each song, artist and album can fit under a mesh of genres. • Q1: How many entities are we keeping track of ? • Q2: How many attributes are in each entity? Agile Programming, MVC Architecture & the practice with RoR

40. Identifying Entities & Attributes • Artist: Has a name, age and songs. • Song: Has a title, duration and fits under one artist and one genre. • Genre: Each has a name, and houses many songs. We have the following entities and attributes: Agile Programming, MVC Architecture & the practice with RoR

41. Creating our App (themusic) • Open Ruby console a shell window • Create an application called themusic, by typing as Rails –d mysql themusic Agile Programming, MVC Architecture & the practice with RoR

42. Files & Folders created • Many files and folders will be created for the application. • Change to our app. directory Agile Programming, MVC Architecture & the practice with RoR

43. Create the Table structures using Scaffold • ruby script/generate scaffold artist name:string age:integer • ruby script/generate scaffold genre name:string • ruby script/generate scaffold song title:string duration:integer artist_id:integer genre_id:integer Agile Programming, MVC Architecture & the practice with RoR

44. Migration files Agile Programming, MVC Architecture & the practice with RoR

45. Create database & tables • rake db:create:all • rake db:migrate Agile Programming, MVC Architecture & the practice with RoR

46. Check whether the web app is ok? • This is to make sure that everything is fine as it should be! Agile Programming, MVC Architecture & the practice with RoR

47. Adding Relationships Notice:Songs is plural Agile Programming, MVC Architecture & the practice with RoR

48. Interacting with Our App Via Console • Rather than using web interface, we will interact with our app. via the ruby console ruby script/console Agile Programming, MVC Architecture & the practice with RoR

49. Let’s begin • Creating an instance of the Artist model. Type the following and hit enter. jb = Artist.new(:name=>'Joe Bloggs', :age=>22) Simple Query: >> jb.new_record? =>true >> jb.name => "Joe Bloggs“ >> jb.age => 22 >> jb.id =>nil Agile Programming, MVC Architecture & the practice with RoR

50. Learning more Notice that the record gets an ID after it is saved: >> jb.save =>true >> jb.id =>1 >> jb.songs => [] Q: Why? Save returns true on success. >> tune = Song.new(:title => 'Love Me Three Times', :duration => 456) =>#<song:0x2b420d56ec00 @attributes={"artist_id"=>nil, "title"=>"Love Me Three Times", "genre_id"=>nil, "duration"=>456}, @new_record=true> Trying to save our song as this point gives us errors: >> tune.save ActiveRecord::StatementInvalid: Mysql::Error: Column 'artist_id' cannot be null: ... long trace omitted ... Agile Programming, MVC Architecture & the practice with RoR