Workshop 3 - Online Taxi Booking System: MySQL and Database design

To do:

1. Set up the MySQL tools on your computer as described in section 6 above.
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).
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.
4. Generate the Passenger model by creating the MySQL database and ‘passengers’ table from the information above.
5. Further work on understanding MySQL under Rails by David Mertz:
1. See “Fast-track your Web apps with Ruby on Rails” at http://www-128.ibm.com/developerworks/linux/library/l-rubyrails
   
2. 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.htm

1)
I have set up the MySQL tools on my virtual machine.

2)
I have set up Instant Rails on my virtual machine. I am using the web server called Mongrel.

3)
Updated the config/database.yml file as follows:
development:
adapter: mysql
database: otbs
host:localhost
username:root

This caused problems with Mongrel and it wouldn't start. Worked out that it was because there was no space between username: and root. Fixed as per the following and I was able to start the taxi application with Mongrel.

adapter: mysql
database: otbs
host: localhost
username: root

4) Realised that the database design has changed. Dropped the passenger_origin and passenger_destination tables. Dropped the taxi database and started again.

Created the database as follows:
Create database OTBS

Created the Passengers table with the following command:

CREATE TABLE `OTBS`.`Passengers` (
`Id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(40) NOT NULL,
`contact_number` VARCHAR(10) NOT NULL,
`suburb_origin` VARCHAR(40) NOT NULL,
`street` VARCHAR(30) NOT NULL,
`street_number` VARCHAR(10) NOT NULL,
`building` VARCHAR(10) NOT NULL,
`suburb_destination` VARCHAR(40) NOT NULL,
`passenger_number` TINYINT UNSIGNED,
`taxi_type` VARCHAR(15) NOT NULL,
`date` DATETIME NOT NULL,
`time_required` TIME NOT NULL,
PRIMARY KEY (`Id`)
)

Generated the Passenger model in Rails as follows:
ruby script/generate model Passenger

Generated the Passenger controller in Rails as follows:
ruby script/generate controller Passenger

Generated the scaffold as follows
ruby script/generate scaffold passenger

This took me a while to work out because the web sites listed above are out of date and the instructions were not right. Apparently scaffold :passenger is no longer used and you have to generate the scaffold. I have just found out that this is called dynamic scaffolding and it is no longer supported as of Rails 2.0.

I'll know better next time...

Added some data into the database with the following script from the workshop document: ( I had to change it to get it to work)

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');
(note the use of single quotes for strings, round brackets for the values and a semi-colon on the end)

Now discovered that there was no job_id specified for the database so I have added that field in as follows:

ALTER TABLE 'OTBS' passengers' ADD COLUMN 'job_id' INTEGER UNSIGNED NOT NULL

I have also added my own id, created_on and updated_on fields.

I have now gone into the application in the browser but the data is missing. I worked out it was because my scaffold command was wrong. Fixed it as follows:

ruby script/generate scaffold passenger name:string job_id:string contact_number:string suburb_origin:string street:string street_number:string building:string suburb_destination:string passenger_number:number taxi_type:string date:date time_required:time

I thought that since I had already created the database I didn't have to put the fields in the scaffold command but now that I have done that the correct 001_create_passengers.rb file has been created. I am happy now :-)

The screens look like the following:



They may not be pretty, but I will look at a cascading style sheet later on.