© 2019 Martin J. Dürst 青山学院大学

ミニテスト

• デスクトップPC (強く推薦) 又はノートPCで https://moo.sw.it.aoyama.ac.jp/ にログイン済み
• ナビゲーションは左に畳み、ブラウザは全画面に拡大
• もう片方のマシーンで電源を切る (ノートPCの場合、鞄に)
• 授業開始まで教科書、資料、筆箱、財布などを鞄に入れ、鞄は床に
• テスト終了後その場で待つ

Today's Schedule

• Minitest
• Last week's exercises and homework
• Databases
• Active Record
• Today's exercises

Last Lecture's Exercises

Important:
Always start ERB templates with <!DOCTYPE,
not with <%... Ruby code

Course Overview

• Last lecture:
  • ERB templates
  • Web forms
  • Literal data in template
• This lecture:
  • Use data from database
  • Map data from database to objects (and back) with Object Relational Mapping (ORM)
  • Use Ruby on Rails' ORM: ActiveRecord

Database

• Purpose: Long-term storage of structured data
• Main database type: Relational database with tables, columns, and cells (attributes/properties).
• Four main operations: Create, read, update, delete (CRUD)
• Example: Library database:
  
  • A table for books with columns (attributes) for title, authors, isbn, price,...
  • A table for users with columns for name, address, age,...
  • A table for loans with columns for book, user, return date,...
  • A table for employees with columns for name,...

Database Engines

• A database engine is a separate program that handles accesses to the database.
• There are many well-known database engines.
• Main commercial database engines:
  • Oracle Database 12c
  • Microsoft SQL server
  • IBM DB2
• Main open source database engines:
  • PostgreSQL
  • MySQL
  • SQLite (does not run as separate program)

Object-Relational Mapper/Mapping (ORM)

• A relational database consists of tables with rows and columns.
• Object-oriented programming languages use classes and objects.
• One database table ⇔ One class.
• One table row ⇔ One object (instance).
• One table entry ⇔ One object property (accessible with getters/setters).
• When reading/writing from/to a database, conversion between these concepts is necessary.
• Conversion can be done 'by hand' or automatically. This is called Object-Relational Mapping.
• Many different ORMs exist different programming languages.
• Most ORMs can also handle different database engines.
• Active Record is the powerful Object-Relational Mapper used in Ruby on Rails.

How to Use Active Record (Indepedently)

(this week, we are using Active Record independently of Rails)

• Require Active Record:
  require 'active_record'
• Establish a connection to your database (example for library.sqlite3):
  

  ActiveRecord::Base.establish_connection(
    :adapter => "sqlite3",
    :database  => "library"
  )

• Make your class a subclass of ActiveRecord::Base:
  class Book < ActiveRecord::Base
end
• That's all you need, the rest is done automatically

What Active Record Does

Active Record does the following automatically:

• Access the database and get the schema information for table books.
• Create a getter and a setter for each field (column) in the table (e.g. title, price, isbn,...)
• Access the database for retrieval (e.g. Book.find(...)) and storage (e.g. my_book.save())
• Convert fields according to the type declared in the schema.

Configuration or Convention

• Table names and class names, column names and property names may be different.
• Traditionally, a configuration file is needed for the mapping (e.g. table books ⇔ class Book).
• Active Record (and Rails in general) uses conventions instead of configuration:
  

  	class name	table name
  initial casing	Upper	lower
  multi-word	CamelCase	under_score
  number	singular	plural
  examples	Book	books
  	Library	libraries
  	Person	people
  	LineItem	line_items

Convention over Configuration

• Preferring conventions over configurations is called Convention over Configuration.
• Convention over Configuration is an important design principle of Rails.
• Convention over Configuration is used in many different places in Rails.
• If necessary (e.g. to access an old database), it is still possible to use other table names.

How to Create a New Database Entry

(examples for table books)

• Create a new item:
  my_book = Book.new
• Set properties:
  my_book.title = 'Advanced Rails'
...
• Alternative:
  my_book = Book.new(title: 'Advanced Rails', authors: ...)
• Save object to database:
  my_book.save

How to Retrieve Entries from the Database

• Find by number:
  book_three = Book.find(3)
• Find with condition:
  books = Book.where("title = 'Programming Ruby'")
• There are many other finder methods, such as select, order, group, offset, limit,...
• To check out these other finder methods on the Web, query e.g. with
  Rails ActiveRecord order

Access to Objects

• After retrieving objects, the result can be accessed with all, first, each, to_a or some other methods.
• Actually, the query to the database is only executed when the data is accessed.
• This makes queries more efficient.

Column Statistics

• Average of a column value can be obtained as follows:
  average_price = Book.average(:price)
• Same for maximum, minimum, sum, and count
• This can be combined with other functions, e.g.
  manga_average_price = Book.where("type = 'Manga').average(:price)

Database used for Exercises

• Because there is currently a Sumo tournament, we will use a database of Sumo wrestlers.
• Download the database (sumo.sqlite3) from Moodle.
• Place the database file into the same directory as your exercise programs.
• Have a look at the database with SQLiteStudio to find out what relations and attributes it has.
  (open the database file with Add a database in the Database menu)

Exercise 6a: Calculate Wrestler Statistics

• Write a program in a file wrestler_statistics.rb to output the following wrestler statistics:
  
  • Overall number (count), average, maximum, minimum, and sum of wrestler height and weight
  • Same statistics per division (序の口,...,幕の内; you can treat 横綱、大関、小結 as separate devisions)
  • Same statistics per stable (部屋)
• Hints:
  
  • It is difficult to take the average of 0 items. Use count first, and skip the other calculations if count is 0.
  • To get all stables, try select(:stable).uniq. You can then use this as a base for a loop on stables.
  • On windows, run this program with:
    ruby -EWindows-31J:UTF-8 wrestler_statistics.rb
    if you have problems with character encoding (文字化け)
• Submit the final program wrestler_statistics.rb to Moodle (deadline: 18:00).

Exercise 6b: Web Page with Wrestler Table

• Create an ERB template in a file 6b_wrestler_table.html.erb that outputs a table of wrestlers from the database.
• Start webrick and test your Web page while you develop it.
• Use a query parameter (sort=stable, sort=division, sort=weight, sort=height) to be able to select the sort order.
• Use links on the page to select sort orders with a single click (e.g. <a href='?sort=weight'>Sort by weight</a>)
• For sorting, do not use the Ruby sort method as in exercise 5c, but use the ActiveRecord order method.
• A page with all wrestlers will be quite long. You can limit the output to the first 100 wrestlers with the ActiveRecord limit method.
• Save the source (HTML only) of your Web page and validate it. Fix any validation errors.
• Submit the program 6b_wrestler_table.html.erb to Moodle (deadline: May 22 (Wednesday), 19:00).

Exercise 6c: Advanced Exercise (発展問題):

• Copy the template from exercise 6b to a file 6c_improved_table.html.erb. Improve your Web page with new and convenient features.
• In the Web page, make it easy to understand what the new features are.
• If you don't have time to work on the actual program, at least think about what improvements you would want to make. (no need to submit)
• Create a template in a file 5c_student_table.html.erb that outputs this student data in a table, including the grade average
• Use a parameter (sort=familyname, sort=givenname, sort=english, sort=math, sort=average) to indicate sort order.
• Use the Ruby sort or sort_by method to sort the array before starting with the ouput
  (hint: check out these methods on the Web)
• As usual, check your template using webrick and validate it.
• Submit the program 6c_improved_table.html.erb to Moodle (deadline: May 22 (Wednesday), 19:00)

Homework

• Create a Ruby on Rails test application, as follows:
  • In "Start Command Prompt with Ruby", cd to a directory where you want to create the application in a subdirectory.
  • Execute the command rails new my_test. This may take some time, and will produce a lot of output.
  • Creation is successful if the output ends with something like:
    Bundle complete! 16 Gemfile dependencies, 76 gems now installed.
Use `bundle info [gemname]` to see where a bundled gem is installed.
• If this does not work, come to my lab this week to fix your installation.
• Have a look at the subdirectories and files that have been generated in the directory my_test. Do not worry that this may be too complicated.