Online Taxi Booking System: MySQL and Database design
Topic objectives
• Develop a database object design for an online taxi booking system (OTBS);
• Revise database techniques with Rails and SQL
• Describe how to use the MVC “push-based architecture” in the Ruby on Rails development environment
Topic readings
Various websites as listed.
Discussion
1. Introduction
Beginning with a single model, single view and single controller classes will assist with development of the OTBS and its SQL database design. Developed and tested on your local computer using Ruby on rails and browser before migration to the production site.
In the Rails environment, applications are developed in the MVC pattern in a DRY (Don’t Repeat Yourself) manner with a strategy of CoC (Conventions over Configuration) and the whole Rails framework gives you the locus of control plus access to class and utility libraries where the:
Model is ActiveRecord as Class ActiveRecord::Base to work SQL tables.
View is ActionView template files Class ActionView::Base
Controller is ActionController
In the previous workshop you were asked to consider the OTBS as our project vehicle for learning about Ruby on Rails as a Web application framework as well as examine the directories where Rails is located. By now you should also have looked at many online tutorials and videos about developing a simple database application with Ruby on Rails.
2. HelloWorld application in Ruby on Rails
Consider any discussion on how the Hello World application is made, as described by Armando Fox at the UC Berkeley RAD lab’s Ruby on Rails Short course at
http://youtube.com/watch?v=LADHwoN2LMM.
In the Hello World application there is NO model, just a single controller called hello_controller.rb with only two controller methods and the view. Each controller method has its corresponding view, which can be called in a URL.
3. The SQL Model and Rails
In order to simplify our project we have combined the two tables (passenger_origin and passenger_destination), into a single table called passengers for these workshops. Now each table row will represent an object instance and each of the twelve (12) columns is an object attribute.
The simplest database operations are often described as CRUD (Create, Read, Update and Destroy). In a single table MVC example there are three main steps with you MySQL tool are:
1. Design the passenger model and attributes;
2. Instantiate the model with a database table and Ruby code
3. Develop a basic controller to do CRUD operations on the Passenger model objects.
If the simplified data model for our project is called Passenger (it is convention to name the model beginning with an upper case letter). The model is then a Ruby class located in app/models/passenger.rb. The SQL table is passengers – the pluralisation of the model.
The controller methods live in app/controllers/passenger_controller.rb
Each controller can access templates to display the input screen and methods for action.
The views are kept is app/views/passenger/*.rhtml, where each *.rhtml maps to a controller method. In Rails, the view is rendered using Ruby-generated RHTML or RXML.
4. More clues on the OTBS database design
OTBS called passenger has a single table called passengers as below:
passengers
name, contact_number, suburb_origin, street, street_number, building, (Unit, house or business) suburb_destination, passenger_number, (1 to 4) taxi_type (sedan, wagon, disabled, maxi), date, time_required
The SQL model is based on the SELECT, PROJECT, JOIN principles of relational databases, with a twist that we now think in terms of a database object via Ruby classes. Let us revise how the four CRUD operations are handled with SQL.
CREATE INSERT INTO passengers
(name, job_id, contact_number, suburb_origin, street, street_number, building, suburb_destination, passenger_number, taxi_type, date, time_required)
VALUES {“Sherlock”, 32832, “0078699”, “Kowloon”, “Baker St”, “42”, “Unit”, “Hawke Bay”, “3”, “wagon”, “2009-01-01”, “0730”}
{“Watson”, 32833, “1234456”, “Pinewood”, “Elm St”, “71”, “Business”, “Hawke Bay”, “1”, “sedan”,”2009-01-01”, “0730”}
READ SELECT * FROM passengers
WHERE (time_required = “0730”
UPDATE UPDATE passengers
SET date = “2009-01-02”
WHERE (name = “Watson”)
DESTROY DELETE FROM passengers WHERE contact-number = “0078699”
Review what was described earlier that the Model is ActiveRecord as Class ActiveRecord::Base to work SQL tables; View is ActionView template files Class ActionView::Base and the Controller is ActionController.
Thus the SQL model becomes a Rails ActiveRecord model and this is a major component of Rails in storing SQL tables and commands which can also be manipulated by collection of Ruby objects. When using SQL JOIN later with other tables, this process is called an object relationship graph abstraction.
5. Scaffolding and the concept of metaprogramming: doing more with less
Scaffolding is a key process during rapid prototyping of your Ruby on Rails project. It uses “metaprogramming” to generate a set of controller methods for the CRUD operations. After defining your model, its attributes and creation of the database table, the basic controller may be called passenger_controller.rb. which can use the scaffold command to release a set of methods to list, show, edit and destroy the database object.
Where do the views come from? Like the old days of “bare bones” programming, scaffolding provides a default set of “undecorated rhtml templates as the views.
There are two ways to scaffold with a simple controller: inline or by passing the model name to a script which can override controller methods and views inside the scripts folder in Rails.
This occurs due to a technique called metaprogramming to create the controller methods which call the method that renders the generic views which can be overridden by later template designs or new SQL schema versions using the Rails migration folder. The scaffold rendering methods in metaprogramming will respect any existing rhtml templates.
In summary try to remember that:
• Rails scaffolding via CoC is a common model of a Web front end to CRUD operations on database objects for a business application;
• Rails uses ActiveRecord database abstraction classes to work with SQL tables, beginng with a class called ActiveRecord::Base
• Metaprogramming creates scaffolding by mapping between instance methods and table columns (attributes)
• Scaffolding gets your application started quickly (rapid prototyping) then you can selectively replace it with later versions;
6. MySQL tools
Many GUI tools exist to support MySQL, such as MySQL Administrator, Migration kit and Query browser (http://www.mysql.com/products/tools/) for Windows, MacOS and Linux. CocoaMySQL is an application used to manage MySQL databases for MacOS users (http://cocoamysql.sourceforge.net/).
At one stage we will have to deal with migration of your prototype to the production site. To get a glimpse of that process in action, see the short screencast at
http://www.mysql.com/products/tools/migration-toolkit/tutorials/migrate-sql-server.html
To do:
1. Set up the MySQL tools on your computer as described in section 6 above.
I am using the phpMyAdmin as the MySQL tools on my computer.
2. Rails will setup a new application directory for each of your Web application projects. Get InstantRails (Windows) or Locomotive (MacOS) running on your machine. Both packages install Ruby, Rails, a Web server or one called ‘Mongrel’ or another small Ruby Web server called ‘WEBrick’, and MySQL “inside a bubble” as I call it so that others parts of your system are not modified (Similarly ZOPE does with installing its own Web server and Python versions).
As described in Exercise 5, I am using the XAMPP 1.7.3 for the MySQL, Apache and the phpMyAdmin tools. Moreover, I have download the one-click windows installer "ruby186.27_rc2.exe" from the rubyforge.org which included the Ruby, popular extensions, editor and RubyGems. Afterward, I have setup the Ruby rails by the command "ruby gem install rails --include-dependencies" and morever update the whole system by "ruby gem update --system".
3. Once Rails is running you at http://localhost:3000, you need to configure database access. Connection to the database is specified in the config/database.yml file.
I have created a project call test by "ruby rails test". Then I use the command "ruby rails test -d mysql to change the database.yml file configuration. The content of the database.yml and the localhost:3000 screen are as follow:
4. Generate the Passenger model by creating the MySQL database and ‘passengers’ table from the information above.
I had created the passengers table from the otbs databse and insert two passengers according to the data mention in section 4.
Create the model by running the "ruby script/generate model Passenger" and the controller by "ruby Script/generate controller Passenger". A passenger.rb file is created under the "app/models" directory and a passenger_controller file is created under the "app/controllers" directory accordingly.
5. Further work on understanding MySQL under Rails by David Mertz:
a. See “Fast-track your Web apps with Ruby on Rails” at
http://www-128.ibm.com/developerworks/linux/library/l-rubyrails/
Done.
b. The “Rolling with Ruby on Rails” series and “Cookbook recipes by Curt Hibbs and others beginning at
http://www.onlamp.com/pub/a/onlamp/2005/01/20/rails.html
Done.
