db-quizz-320.md

Quiz correction - DB and ActiveRecord

Q1 - What’s a relational database?

ensemble de tables reliées par des clés primaires/étrangères

Q2 - What are the different “table relationships” you know?

• 1:N One to many
• N:N Many to many => table de jointure

Q3 - Consider this e-library service. An author, defined by his name have several books. A book, defined by its title and publishing year, has one author only. What’s this simple database scheme. Please draw it!

Q4 - A user, defined by his email, can read several books. A book (e-book!!) can be read by several user. We also want to keep track of reading dates. Improve your e-library DB scheme with relevant tables and relationships.

Q5 - What’s the language to make queries to a database?

SQL (Structured Query Language)

Q6 - What’s the query to get books written before 1985?

SELECT * FROM books WHERE publishing_year <= 1985

Q7 - What’s the query to get the 3 most recent books written by Jules Verne?

SELECT * FROM books b
JOIN authors a ON b.author_id = a.id
WHERE a.name = 'Jules Verne'
ORDER BY b.publishing_year DESC
LIMIT 3;

Q8 - What’s the purpose of ActiveRecord?

• mapping des enregistrements en BDD dans les tables avec nos modèles
• méthodes pour faire des requêtes en ruby

Q9 - What’s a migration? How do you run a migration?

script en ruby pour modifier le schéma, la structure de la BDD (pas la donnée)

rake db:migrate

Q10 - Complete migrations to create your e-library database

class CreateAuthors < ActiveRecord::Migration[5.1]
  def change
    create_table :authors do |t|
        t.string :name
        t.timestamps
    end
  end
end

class CreateBooks < ActiveRecord::Migration[5.1]
  def change
    create_table :books do |t|
        t.string :title
        t.integer :publishing_year
        t.references :author, foreign_key: true, index: true
        t.timestamps
    end
  end
end

class CreateUsers < ActiveRecord::Migration[5.1]
  def change
    create_table :users do |t|
        t.string :email
        t.timestamps
    end
  end
end

class CreateReadings < ActiveRecord::Migration[5.1]
  def change
    create_table :readings do |t|
        t.date :date
        t.references :user, foreign_key: true, index: true
        t.references :book, foreign_key: true, index: true
    end
  end
end

Q11 - Write a migration to add a category column to the books table.

class AddCategoryToBooks < ActiveRecord::Migration[5.1]
  def change
    add_column :books, :category, :string
  end
end

Q12 - Define an ActiveRecord model for each table of your DB. Add the ActiveRecord associations between models.

class Author < ActiveRecord::Base
    has_many :books
end

class Book < ActiveRecord::Base
    belongs_to :author
    has_many :readings
    # a_book.readings.map { |reading| reading.user}
    has_many :users, through: :readings
    # a_book.users
end

class User < ActiveRecord::Base
    has_many :readings
    has_many :books, through: :readings
    # a_user.books
end

class Reading < ActiveRecord::Base
    belongs_to :user
    belongs_to :book
end

Q13 - Complete the following code using the relevant ActiveRecord methods.

# 1. Add your favorite author to the DB
zola = Author.new(name: "Emile Zola")
zola.save
# Same as
Author.create(name: "Emile Zola")

# 2. Get all authors
Author.all

# 3. Get author with id=8
Author.find(8)

# 4. Get author with name="Jules Verne", store it in a variable: jules
jules = Author.find_by(name: "Jules Verne")
Author.find_by_name("Jules Verne")

# 5. Get Jules Verne's books
jules.books

# 6. Create a new book "20000 Leagues under the Seas", it's missing in DB.
# Store it in a variable: twenty_thousand
twenty_thousand = Book.new(
    title: "20000 Leagues under the Seas",
    publishing_year: 1900
)

# 7. Add Jules Verne as this book's author
twenty_thousand.author = jules

# 8. Now save this book in the DB!
twenty_thousand.save

# The end

Q14 - Add validations of your choice to the Author class. Can we save an object in DB if its validations do not pass?

class Author < ActiveRecord::Base
    has_many :books

    validates :name, presence: true
end

bad_author = Author.new
bad_author.name # => nil
bad_author.save # => false
bad_author.save! # => Error
bad_author.valid? # => false
bad_author.errors.messages # => 'name can't be blank'