Database - Books

app.rb

require "sqlite3"
require_relative "book.rb"
require_relative "genre.rb"
require_relative "location.rb"
require_relative "module.rb"
require_relative "instance_module.rb"

CONNECTION = SQLite3::Database.new("inventory.db")

CONNECTION.execute("CREATE TABLE IF NOT EXISTS books (id INTEGER PRIMARY KEY, name TEXT NOT NULL, genre_id INTEGER NOT NULL, location_id INTEGER NOT NULL, quantity INTEGER NOT NULL, FOREIGN KEY(location_id) REFERENCES locations(id), FOREIGN KEY(genre_id) REFERENCES genres(id));")
CONNECTION.execute("CREATE TABLE IF NOT EXISTS genres (id INTEGER PRIMARY KEY, name TEXT NOT NULL);")
CONNECTION.execute("CREATE TABLE IF NOT EXISTS locations (id INTEGER PRIMARY KEY, name TEXT NOT NULL);")

# Get results as an Array of Hashes.
CONNECTION.results_as_hash = true

# ------------------------------------------------------------

continue = "y"

while continue != "n"
  puts "Hello! What would you like to work with today?"
  puts "Books? (1)"
  puts "Genres? (2)"
  puts "Locations? (3)"
  puts "Search? (4)"

  table_answer = gets.chomp.to_i

  # - Start of Books
  if table_answer == 1
    puts "What would you like to do with Books?"
    puts "Add a book? (1)"
    puts "Edit a book? (2)"
    puts "View book information? (3)"
    puts "Delete a book? (4)"
  
    book_answer = gets.chomp.to_i
  
    # - Add a book
    if book_answer == 1
      puts "What is the name of the book?"
      book_name = gets.chomp
    
      puts "What genre is the book?"
      Genre.genre_table
      book_genre = gets.chomp
      while book_genre.empty?
        puts "Sorry, please enter a genre"
        book_genre = gets.chomp
      end
        book_genre.to_i
    
      puts "Where is the book?"
      Location.location_table
      book_location = gets.chomp
      while book_location.empty?
        puts "Sorry, please enter a location"
        book_location = gets.chomp
      end
      book_location.to_i
      
      puts "How many are there?"
      book_quantity = gets.chomp
      while book_quantity.empty?
        puts "Sorry, please enter a quantity"
        book_quantity = gets.chomp
      end
        book_quantity.to_i
      
      book_object = Book.new({"name" => book_name, "genre_id" => book_genre, "location_id" => book_location, "quantity" => book_quantity})
      
      if book_object.valid?
        Book.add({"name" => book_name, "genre_id" => book_genre, "location_id" => book_location, "quantity" => book_quantity})
      else
        puts "Sorry, wasn't able to add to database at this time."
      end
    end
    
    # - Edit a book
    if book_answer == 2
      puts "What book would you like to edit?"
      Book.book_table
      book_id = gets.chomp.to_i
      
      book_object = Book.find(book_id)
      
      puts "What would you like to edit?"
      puts "Book name? (1)"
      puts "Book genre? (2)"
      puts "Book location? (3)"
      puts "Book quantity? (4)"
      
      book_edit = gets.chomp.to_i
      
      # - Edit Book name
      if book_edit == 1
        puts "What is the new book name?"
        book_object.name = gets.chomp.to_s
        book_object.save
      end
      
      # - Edit Book genre
      if book_edit == 2
        puts "What is the new book genre?"
        Genre.genre_table
        book_object.genre_id = gets.chomp.to_i
        book_object.save
      end
      
      # - Edit Book location
      if book_edit == 3
        puts "What is the new book location?"
        Location.location_table
        book_object.location_id = gets.chomp.to_i
        book_object.save
      end
      
      # - Edit Book quantity
      if book_edit == 4
        puts "What is the new quantity?"
        book_object.quantity = gets.chomp.to_i
        book_object.save
      end
    end
    
    # - View book information
    if book_answer == 3
      puts "What book would you like to view?"
      Book.book_table
      book_id = gets.chomp.to_i
      
      this_book = Book.find(book_id)
      
      puts "#{this_book.id} - #{this_book.name} - #{this_book.genre_id} - #{this_book.location_id} - #{this_book.quantity}"
    end
    
    # - Delete Book
    if book_answer == 4
      puts "What book would you like to delete?"
      Book.book_table
      book_id = gets.chomp.to_i
      
      this_book = Book.find(book_id)
      
      this_book.delete
    end
  end
  
  # - Start of genres
  
  if table_answer == 2
    puts "What would you like to do with genres?"
    puts "Add a genre? (1)"
    puts "Delete a genre? (2)"
    
    genre_answer = gets.chomp.to_i
    
    # - Add a genre
    if genre_answer == 1
      puts "What is the genre name?"
      genre_name = gets.chomp
      
      genre_object = Genre.new({"name" => genre_name})
      if genre_object.valid?
        Genre.add({"name" => genre_name})
      else
        puts "Sorry, we weren't able to add that genre at this time."
      end
    end
    
    # - Delete a genre
    if genre_answer == 2
      puts "Which genre would you like to delete?"
      Genre.genre_table
      genre_id = gets.chomp.to_i
      
      genre_object = Genre.find(genre_id)
      
      # - Checks to see if genre is empty before deleting.
      if genre_object.delete_category("genre") == false
        puts "Sorry, that genre contains books. You may only delete an empty genre."
      end
    end
  end
  
  # - Start location
  if table_answer == 3
    puts "What would you like to do with locations?"
    puts "Add a location? (1)"
    puts "Delete a location? (2)"
    
    location_answer = gets.chomp.to_i
    
    # - Add location
    if location_answer == 1
      puts "What is the location name?"
      location_name = gets.chomp
      
      location_object = Location.new({"name" => location_name})
      if location_object.valid?
        Location.add({"name" => location_name})
      else
        puts "Sorry, we weren't able to add that genre at this time."
      end
    end
    
    # - Delete location
    if location_answer == 2
      puts "Which location would you like to delete?"
      Location.location_table
      location_id = gets.chomp.to_i
      
      location_object = Location.find(location_id)
      
      # - Checks to see if any books have that location. If true, delete. If false, return that can't delete.
      if location_object.delete_category("location") == false
        puts "Sorry, that location contains books. You may only delete an empty genre."
      end
    end
  end
  
  # - Start search
  if table_answer == 4
    puts "Search for Book by:"
    puts "Location (1)"
    puts "Genre (2)"
    search_answer = gets.chomp.to_i
    
    # - Search by location
    if search_answer == 1
      puts "Which location?"
      Location.location_table
      location = gets.chomp
      
      Book.where_location(location).each do |object|
        puts "#{object.name}"
      end
    end
    
    # - Search by genre
    if search_answer == 2
      puts "Which genre?"
      Genre.genre_table
      genre = gets.chomp
      
      Book.where_genre(genre).each do |object|
        puts "#{object.name}"
      end
    end
  end
    
  puts "Would you like to do something else? (Y/N)"
  continue = gets.chomp
end

book.rb

require_relative "module.rb"
require_relative "instance_module.rb"

class Book
  
  extend ClassModule
  include InstanceModule
  
  attr_reader :id
  attr_accessor :name, :genre_id, :location_id, :quantity
  
  # - Initializes a new Book object.
  #
  # - options - HASH containing the various properties to be set as attributes.
  #
  # - id - The Primary Key for a row in the database - INTEGER
  # - name - Name of the book - STRING
  # - genre_id - The Primary Key for the genre the book belongs to - INTEGER
  # - location_id - The Primary Key for the location the book belongs to - INTEGER
  # - quantity - How many books of this name are in a location
  #
  # - Example:
  #
  #      options = {"name" => "The Hobbit", "genre_id" => 1, "location_id" => 1, "quantity" => 3}
  #
  def initialize(options={})
    @id = options["id"]
    @name = options["name"]
    @genre_id = options["genre_id"]
    @location_id = options["location_id"]
    @quantity = options["quantity"]
  end

  # - Updates the assosiated row in the book table with the new values for a book object.
  #
  # - TODO - Needs to return something better
  def save
    CONNECTION.execute("UPDATE books SET name = '#{@name}', genre_id = #{@genre_id}, location_id = #{@location_id}, quantity = #{@quantity} WHERE id = #{id};")
  end
  
  # - Delete's a book from table
  #
  # - TODO - Needs to return something better
  def delete
    CONNECTION.execute("DELETE FROM books WHERE id = #{@id};")
  end
    
    
  # - Checks each attribute to make sure it doesn't equal nil or an empty string
  def valid?
    if @name == nil || @name == ""
      false
    elsif @genre_id == nil
      false
    elsif @location_id == nil
      false
    elsif @quantity == nil
      false
    else
      true
    end
  end

  # - Finds all books assosiated with that location. Creates book objects and inserts them into an array.
  #
  # - location = location_id paramater to search - INTEGER
  #
  # - Returns an array with the book objects that are in that location.
  def self.where_location(location)
    results = CONNECTION.execute("SELECT * FROM books WHERE location_id = #{location}")

    results_as_objects = []
    
    results.each do |result_hash|
      results_as_objects << self.new(result_hash)
    end
    
    return results_as_objects
  end
  
  # - Finds all books assosiated with that genre. Creates book objects and inserts them into an array.
  #
  # - genre = genre_id paramater to search - INTEGER
  #
  # - Returns an array with the book objects that are in that genre.
  def self.where_genre(genre)
    results = CONNECTION.execute("SELECT name FROM books WHERE genre_id = #{genre}")

    results_as_objects = []
    
    results.each do |result_hash|
      results_as_objects << self.new(result_hash)
    end
    
    return results_as_objects
  end
end

genre.rb

require_relative "module.rb"
require_relative "instance_module.rb"

class Genre
  
  extend ClassModule
  include InstanceModule
  
  attr_reader :id
  attr_accessor :name
  
  # - Initializes a new genre object. Takes its arguements in a hash. 
  #
  # - Example:
  #       options = {"name" = "Fantasy"}
  def initialize(options={})
    @id = options["id"]
    @name = options["name"]
  end
 
  # - Updates the assosiated row in the book table with the new values for a book object.
  #
  # - TODO - Needs to return something better
  def save
    CONNECTION.execute("UPDATE genres SET name = '#{@name}' WHERE id = #{id};")
  end
  
  # - Checks to make sure the genre name is not nil or an empty string.
  #
  # - Returns T/F
  def valid?
    if @name == nil || @name == ""
      false
    else
      true
    end
  end
end

instance_module.rb

module InstanceModule
  
  require "active_support"
  require "active_support/inflector"
  
  # - First sets x to equal the array returned containing hashes of objects that have that location
  # - Checks to see if that returned array is empty. If empty - True, else False
  # - Delete's the location if x == True and returns an empty array. Returns false if x == False.
  def delete_category(class_name)
    table_name = class_name.pluralize
    table_id = class_name + "_id"
    
    x = CONNECTION.execute("SELECT * FROM books WHERE #{table_id} = #{@id};")
    if x == []
      CONNECTION.execute("DELETE FROM '#{table_name}' WHERE id = #{@id};")
    else
      false
    end
  end
end

location.rb

require_relative "module.rb"
require_relative "instance_module.rb"

class Location
  
  extend ClassModule
  include InstanceModule
  
  attr_reader :id
  attr_accessor :name
  
  # - Initializes a new location object. Takes its arguements in a hash. 
  #
  # - Example:
  #       options = {"name" = "Iowa"}
  def initialize(options={})
    @id = options["id"]
    @name = options["name"]
  end
 
  # - Updates the assosiated row in the book table with the new values for a book object.
  #
  # - TODO - Needs to return something better
  def save
    CONNECTION.execute("UPDATE locations SET name = '#{@name}' WHERE id = #{id};")
  end
  
  # - Checks to make sure location name is not nil or an empty string.
  #
  # - Returns T/F
  def valid?
    if @name == nil || @name == ""
      false
    else
      true
    end
  end

end

module.rb

module ClassModule
  
  require "active_support"
  require "active_support/inflector"
  
  # - Runs a loop for each row in table.
  # - Returns a hash that is split into a readable form.
  def book_table
    puts "ID - NAME - GENRE_ID - LOCATION_ID - QUANTITY"
    self.all.each do |book_hash|
      puts "#{book_hash.id} - #{book_hash.name} - #{book_hash.genre_id} - #{book_hash.location_id} - #{book_hash.quantity}"
    end
  end
  
  # - Returns all columns for every genre in table
  # - Returns a hash that is split into a readable form.
  def genre_table
    puts "ID - Name"
    self.all.each do |genre_hash|
      puts "#{genre_hash.id} - #{genre_hash.name}"
    end
  end
  
  # - Returns all columns for every location in table.
  # - Returns a hash that is split into a readable form.
  def location_table
    puts "ID - Name"
    
    self.all.each do |location_hash|
      puts "#{location_hash.id} - #{location_hash.name}"
    end
  end

  # - Class Method - Gathers all information from the table that is assosiated with the class this is being called on.
  #
  # - Creates new objects for all selected rows and puts them into an array.
  #
  # - Returns the array with objects inside.
  def all
    table_name = self.to_s.pluralize.underscore
    
    results = CONNECTION.execute("SELECT * FROM '#{table_name}';")

    results_as_objects = []
    
    results.each do |result_hash|
      results_as_objects << self.new(result_hash)
    end
    
    return results_as_objects
  end

  # - Retrieves information from a table with the row id == to arguement id
  #
  # - Creates a new object
  #
  # - Returns object
  def find(id)
    table_name = self.to_s.pluralize.underscore
    
    result = CONNECTION.execute("SELECT * FROM '#{table_name}' WHERE id = #{id};")
    result = result.first
    
    self.new(result)
  end
  
  # - Adds a new row into a table with the values taken in the arguement.
  # - Seperates values and keys, puts them into arrays to be put into SQL.
  #
  # - options - Hash
  #
  # - Returns an object
  def add(options={})
    
    table_name = self.to_s.pluralize.underscore
    
    column_names = options.keys
    values = options.values
    
    column_names = column_names.join(", ")
    
    converted_values = []
    
    values.each do |value|
      if value.is_a?(String)
        converted_values << "'#{value}'"
      else
        converted_values << value
      end
    end
    
    converted_values = converted_values.join(", ")
    
    CONNECTION.execute("INSERT INTO #{table_name} (#{column_names}) VALUES (#{converted_values});")
    
    id = CONNECTION.last_insert_row_id
    options["id"] = id
    
    self.new(options)
  end
end

Definitely better. But I'd like even better documentation :)

First, you don't need to have documentation like @book_id = book_id of current object. You can, if you like having them. But it's not necessary as far as I'm concerned, because instance variables don't have different meaning between methods – e.g. @book_id = book_id of current object. is always the "book_id of current object". But your documentation for parameters is appropriate.

The biggest thing missing from your documentation is the kind of Ruby object that a parameter is, or the kind of Ruby object that a method returns. E.g. Returns all columns for every location in table. should be Returns an Array of Hashes representing the columns for every location in table.

Looking good. One improvement would be changing methods like Book.where_genre to return an array of objects instead of an array of hashes.

For sure, I'll go back and change that, and fix some of the documentation.