Movie Store Database Program

dbscript.rb

#!/usr/bin/ruby

require 'sqlite3'

begin

    db = SQLite3::Database.open "database.db"
    db.execute "CREATE TABLE IF NOT EXISTS dvds(sku INTEGER PRIMARY KEY,
        movieName VARCHAR(45), releaseYear VARCHAR(4), numInInventory INT, minutes INT, price DECIMAL)"
    db.execute "INSERT INTO dvds VALUES(12345,'Braveheart', '1995', 6, 182, 9.99)"
    db.execute "INSERT INTO dvds VALUES(23456,'Zoolander', '2001', 5, 105, 8.99)"
    db.execute "INSERT INTO dvds VALUES(34567,'Anchorman', '2004', 7, 104, 7.99)"
    db.execute "INSERT INTO dvds VALUES(45678,'Troy', '2004', 3, 196, 9.99)"
    db.execute "INSERT INTO dvds VALUES(56789,'Lethal Weapon', '1987', 2, 117, 5.99)"
    db.execute "INSERT INTO dvds VALUES(98765,'Avatar', '2009', 4, 178, 13.99)"
    db.execute "INSERT INTO dvds VALUES(87654,'Enter the Dragon', '1973', 1, 110, 4.99)"
    db.execute "INSERT INTO dvds VALUES(76543,'Kill Bill', '2003', 1, 112, 6.99)"


    db.execute "CREATE TABLE IF NOT EXISTS employees(employeeID INTEGER PRIMARY KEY, storeNum INTEGER,
        firstName VARCHAR(45), lastName VARCHAR(45), password VARCHAR(20), FOREIGN KEY (storeNum) REFERENCES stores(storeNum))"
    db.execute "INSERT INTO employees VALUES(1, 01, 'Corey', 'LeBlanc', 'password1')"
    db.execute "INSERT INTO employees VALUES(2, 02, 'Jane', 'Doe', 'password2')"
    db.execute "INSERT INTO employees VALUES(3, 03, 'Batman', 'Robin', 'password3')"


    db.execute "CREATE TABLE IF NOT EXISTS stores(storeNum INTEGER PRIMARY KEY,
        address VARCHAR(45))"
    db.execute "INSERT INTO stores VALUES(01, '1 Main Street, Framingham, MA 01701')"
    db.execute "INSERT INTO stores VALUES(02, '1 Random Road, Boston, MA 02115')"
    db.execute "INSERT INTO stores VALUES(03, '1 Fake Terrace, Malden, MA 02145')"


    db.execute "CREATE TABLE IF NOT EXISTS customers(customerID INTEGER PRIMARY KEY, address VARCHAR(45),
        fName VARCHAR(45), lName VARCHAR(45))"
    db.execute "INSERT INTO customers VALUES(4321, '1 Blah Street', 'James', 'Smith')"


    db.execute "CREATE TABLE IF NOT EXISTS salesHistory(sku INTEGER, customerID INTEGER,
        price DECIMAL, storeNum INTEGER, PRIMARY KEY (sku, customerID), FOREIGN KEY (sku) REFERENCES dvds(sku),
        FOREIGN KEY (customerID) REFERENCES customers(customerID), FOREIGN KEY (storeNum) REFERENCES stores(storeNum))"

rescue SQLite3::Exception => e

    puts "Exception occured"
    puts e

ensure
    db.close if db
end

program.rb

#!/usr/bin/ruby

require 'sqlite3'

begin

    def menu
        i = 0
        loop do
            puts "1. Find a dvd.","2. Display all current inventory.","3. Enter a sale.","4. Add a DVD to inventory.",
            "5. Update inventory.","6. Quit."

            input = gets.chomp

            case input
            when "1"
                puts "Enter sku"
                skuInput = gets.chomp
                    puts "Sku     Title            Year    Inventory    Minutes    Price"
                    puts "---     -----            ----    ---------    -------    -----"
                @db.execute("SELECT * FROM dvds WHERE sku = #{skuInput}") do |row|
                    puts "#{row[0]}   #{row[1]}       #{row[2]}    #{row[3]}           #{row[4]}        #{row[5]}"
                end


            when "2"
                puts "Inventory      Sku     Title"
                puts "---------     -----    ------"
                @db.execute("SELECT numInInventory, sku, movieName FROM dvds WHERE numInInventory > 0") do |row|
                    puts "#{row[0]}             #{row[1]}    #{row[2]}"

                end


            when "3"
                puts "Enter/Scan DVD Sku."
                saleSku = gets.chomp
                @db.execute "UPDATE dvds SET numInInventory = numInInventory - 1 WHERE sku = #{saleSku}"

            when "4"
                puts "Enter Sku."
                skuInsert = gets.chomp
                puts "Enter DVD title."
                titleInsert = gets.chomp
                puts "Enter release year."
                yearInsert = gets.chomp
                puts "Enter length of film in minutes."
                lengthInsert = gets.chomp
                puts "Enter price."
                priceInsert=gets.chomp

                #isThereInventory = @db.execute "SELECT sku FROM dvds WHERE sku = #{skuInsert}"
                @db.execute "INSERT INTO dvds VALUES(#{skuInsert}, '#{titleInsert}', '#{yearInsert}', 1, #{lengthInsert}, #{priceInsert})"

            when "5"
                puts "Enter sku of the DVD you wish to update."
                updateSku = gets.chomp
                puts "Enter the number of copies you are adding to inventory."
                updateQuantity = gets.chomp
                @db.execute "UPDATE dvds SET numInInventory = numInInventory + #{updateQuantity} WHERE sku = #{updateSku}"

            when "6"
                puts "Goodbye!"
                i = 1
            else
                puts "Invalid option: #{input}"
            end

            break if i == 1
        end
    end

	@db = SQLite3::Database.open "database.db"
	puts "Enter Employee ID:"
	empID = gets.chomp
	puts "Enter Password:"
	passInput = gets.chomp

	empPass = @db.execute "SELECT password FROM employees WHERE employeeID = #{empID}"

	if empPass.flatten.first == passInput
	   menu
	end

	rescue SQLite3::Exception => e

    	puts "Exception occured"
    	puts e
    ensure
        @db.close if @db
end