Database Articles

  Home arrow Database Articles arrow Book Inventory Application: Publishers...
DATABASE ARTICLES

Book Inventory Application: Publishers and Books
By: Apress Publishing
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating:  stars stars stars stars stars / 0
    2011-10-12

    Table of Contents:
  • Book Inventory Application: Publishers and Books
  • Creating the Book Model

  •  
     

    SEARCH CODEWALKERS

    Book Inventory Application: Publishers and Books


    (Page 1 of 2 )

    In this fourth part of an article series on the scaffolding feature of Ruby on Rails, you'll learn how to implement the Edit Publisher user story for our book inventory management application, and how to create the book model. This article is excerpted from chapter three of the book Practical Rails Projects, written by Eldon Alameda (Apress; ISBN: 1590597818).

    Completing the Edit Publisher User Story

    The scaffold implementation of the Edit Publisher user story’s test suffers from the same problem as the Add Publisher implementation. The test doesn’t provide any parameters to the action other than the id of the publisher. This means that no data is updated when the test is run, but in this case, there is no error. This is okay, because Rails uses update_attributesto update only attributes that are included as request parameters.

    We do want to verify that the editing is successful, so opentest/functional/admin/ publisher_controller_test.rband change thetest_updatemethod as shown in the following code snippet:

    def test_update
    post :update, :id => 1, :publisher => { :name => 'Apress.com' }
    assert_response :redirect
    assert_redirected_to :action => 'show', :id => 1
    assert_equal 'Apress.com', Publisher.find(1).name
    end

    Note that we have added a new parameter to thepostmethod call. This will update the name of the publisher toApress.comin the database. At the end of the test, we verify that this really is the case withassert_equal. Execute the functional tests again, withrake test:functionals. Accesshttp://localhost:3000/admin/publisher/listand click the Edit link next to the publisher you created. You should see a page that looks like Figure 3-4.


    Figure 3-4. Testing the Edit Publisher user story

    You now have a functioning system for maintaining the publishers. We call George over and show him the user interface. He does a quick acceptance test and tells us that he has no complaints, so let’s continue implementing the user stories for book management.

    Implementing the Book Administration Interface

    Now we implement the administrator interface for managing books. For this, we need to first create a table for storing books in the database, and then create the ActiveRecord model for books. Although we found some issues with scaffolding while implementing the publisher administration functionality, it saved us some time and George was happy, so we’ll continue using scaffolding to implement the book management administration functionality.

    Updating the Schema with the Books Table

    The first thing we need to do is to create a table for storing books in the database. Following the Rails naming conventions, we name the table books, and as usual, we’ll update the database schema using ActiveRecord migrations.

    As a recap, the Emporium database schema already has theauthors table we created in the previous chapter and thepublisherstable we created earlier in this chapter. Now, we’ll add thebookstable, as well as thebooks_authorstable, which will be used to linkauthorstopublishers.

    George is still with us, at least physically. He asks us why we have to use so many tables. “You consultants, you always want to build these fancy systems. I could do this with just one Excel sheet!” We don’t know if he’s kidding or not, but we’ll try to get George to understand when we show him a picture and explain how mapping works to link the tables and get the data we want. We’ll do that after we add the new tables and create theBookmodel.

    To start, create the migration file for adding thebookstable to the database schema:

    $ script/generate migration create_books_and_authors_books

    --------------------------------------------
    exists db/migrate
    create db/migrate/003_create_books_and_authors_books.rb
    --------------------------------------------

    Opendb/migrate/003_create_books_and_authors_books.rbin your editor and add the code in Listing 3-1 to it.

    Listing 3-1. ActiveRecord Migration for the books and authors_books Tables and Foreign Keys

    class CreateBooksAndAuthorsBooks < ActiveRecord::Migration
    def self.up
    create_table :books do |table|
    table.column :title, :string, :limit => 255, :null => false
    table.column :publisher_id, :integer, :null => false
    table.column :published_at, :datetime
    table.column :isbn, :string, :limit => 13, :unique => true
    table.column :blurb, :text
    table.column :page_count, :integer
    table.column :price, :float
    table.column :created_at, :timestamp
    table.column :updated_at, :timestamp
    end

    create_table :authors_books, :id => false do |table|
    table.column :author_id, :integer, :null => false
    table.column :book_id, :integer, :null => false
    end

    say_with_time 'Adding foreign keys' do
    # Add foreign key reference to books_authors table
    execute 'ALTER TABLE authors_books ADD CONSTRAINT fk_bk_authors➥
    FOREIGN KEY ( author_id ) REFERENCES authors( id ) ON DELETE CASCADE'
    execute 'ALTER TABLE authors_books ADD CONSTRAINT fk_bk_books➥
    FOREIGN KEY ( book_id ) REFERENCES books( id ) ON DELETE CASCADE'
    # Add foreign key reference to publishers table
    execute 'ALTER TABLE books ADD CONSTRAINT fk_books_publishers➥
    FOREIGN KEY ( publisher_id ) REFERENCES publishers( id ) ON DELETE CASCADE'
    end
    end

    def self.down
    drop_table :authors_books
    drop_table :books
    end
    end


    Note This migration in Listing 3-1 uses MySQL-specific SQL. This means that you would need to change the code in order to run it on other databases.


    The migration file creates two new tables,booksandauthors_books(authors_booksin the join table, as explained in the “Many-to-Many Relationship” section later in this chapter). To ensure data integrity, we also add foreign key constraints to the tables. ActiveRecord doesn’t support adding foreign keys constraints to tables. You need to add foreign keys using theALTER TABLE SQL command and the ActiveRecordexecute method, which can execute raw SQL on the database. Thesay_with_timemethod is used to print out the time it takes to execute the commands that add foreign keys to the database schema. Also note that ISBN numbers must be unique and that this is ensured by setting the:uniqueoption totrue.


    Tip ActiveRecord has a built-in time stamping behavior that is triggered for database columns namedcreated_at/created_onandupdated_at/updated_on. When ActiveRecord finds one of these columns in a database schema, it will automatically set the creation timestamp when a new object is created and the modification time when the object is updated. ActiveRecord also has other behaviors that are triggered for other column names. For example, thelock_versioncolumn name enables optimistic locking.


    You are now ready to upgrade the Emporium database to its third version. Execute the migration script with therake db:migratecommand:

    $ rake db:migrate

    --------------------------------------------
    (in /home/george/projects/emporium)
    == CreateBooksAndAuthorsBooks: migrating ======================================
    -- create_table(:books)
    -> 0.1410s
    -- create_table(:authors_books, {:id=>false})
    -> 0.1400s
    -- Adding foreign keys
    -- execute("ALTER TABLE authors_books ADD CONSTRAINT fk_bk_authors➥
    FOREIGN KEY ( author_id ) REFERENCES authors( id ) ON
    DELETE CASCADE")
    -> 0.3440s
    -- execute("ALTER TABLE authors_books ADD CONSTRAINT fk_bk_books➥
    FOREIGN KEY ( book_id ) REFERENCES books( id ) ON DELETE CASCADE")
    -> 0.3280s
    -- execute("ALTER TABLE books ADD CONSTRAINT fk_books_publishers➥
    FOREIGN KEY ( publisher_id ) REFERENCES publishers( id
    ) ON DELETE CASCADE")
    -> 0.3440s
    -> 1.0160s
    == CreateBooksAndAuthorsBooks: migrated (1.2970s) =============================
    --------------------------------------------

    You should see all commands run without any errors. If you connect to MySQL with the command-line client, you can see the two new tables that were created by the migration:

    $ mysql -uemporium -phacked

    --------------------------------------------
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 1 to server version: 5.0.20-community

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql> use emporium_development;
    Database changed
    mysql> show tables;

     

    Tables_in_emporium_development

     

    authors

     

    authors_books

     

    books

     

    publishers

     

    schema_info

     

     

    5 rows in set (0.08 sec)
    --------------------------------------------

    Theschema_infotable is where ActiveRecord stores the current version of the database schema, as explained in the previous chapter. Runningselect * from schema_info;should print3, which is the current version of our database schema.


    Tip If you want to go back to a previous version of your database model, just specify the version number as a parameter to the migrate script, as inrake migrate VERSION=0.


    More Database Articles Articles
    More By Apress Publishing

    blog comments powered by Disqus

    DATABASE ARTICLES ARTICLES

    - MariaDB, a MySQL Alternative, Opens Foundati...
    - Oracle Supporting HP Itanium Again, Finally
    - Completing a Book Inventory Management System
    - Uploading Images for a Book Inventory Manage...
    - Finishing the Add Book Story for a Book Inve...
    - Integration Testing for a Book Inventory Man...
    - User Stories for a Book Inventory Management...
    - Unit Testing a Book Inventory Management Sys...
    - Testing a Book Inventory Management System
    - Implementing Models for a Book Inventory Man...
    - Book Inventory Application: Publishers and B...
    - Handling Publishers in a Book Inventory Mana...
    - Publisher Administration for Book Inventory ...
    - Book Inventory Management
    - Using the SQL Reference Manual

    Developer Shed Affiliates

     



    © 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap