Student Roster DB from Schema

gistfile1.txt

require 'sqlite3'

$db = SQLite3::Database.new "students.db"

module DBManipulate
  def self.add(first_name, last_name, gender, birthday, email, phone)
    $db.execute(
      <<-SQL
        INSERT INTO students
          (first_name, last_name, gender, birthday, email, phone, created_at, updated_at)
        VALUES
          ("#{first_name}", "#{last_name}", "#{gender}", "#{birthday}", "#{email}", "#{phone}", DATETIME('now'), DATETIME('now'));
      SQL
    )
  end

  def self.delete(id)
    $db.execute(
      "
        DELETE FROM students
        WHERE ID = ?
      ",[id]
    )
  end

  def self.show
    list = $db.execute(
      "
        SELECT * FROM students;
      "
      )
    list.each{ |line| puts line.join(" ")}
  end

  def self.show_by_first_name(first_name)
    list = $db.execute(
      "
        SELECT * FROM students WHERE first_name = ?;",
      [first_name])
    list.each{ |line| puts line.join(" ")}
  end

  def self.show_by_attribute(attribute, value)
    raise("Invalid attribute") unless check_if_attribute(attribute)
    list = $db.execute(
      "
        SELECT * 
        FROM students 
        WHERE #{attribute} = ?;
      ", [value]
      )
    list.each{ |line| puts line.join(" ")}
  end
  
  def self.check_by_birthday_month(month)
    list = $db.execute(
      "
        SELECT * 
        FROM students 
        WHERE strftime('%m', birthday) = ?;
      ", [month]
      )
    list.each{ |line| puts line.join(" ")}
  end

  def self.sorted_list_by_attribute(attribute, order)
    raise("Invalid attribute") unless check_if_attribute(attribute)
    raise("Invalid order") unless ["DESC", "ASC"].include?order
    list = $db.execute(
      "
        SELECT * 
        FROM students 
        ORDER BY #{attribute} #{order};
      "
      )
    list.each{ |line| puts line.join(" ")}
  end

  def self.check_if_attribute(input)
    rows = $db.prepare( "select * from students" )
    rows.columns.include?input
  end
end

### show list of students who have birthday this month
# DBManipulate.check_by_birthday_month("01")

## list students by birthday
# DBManipulate.sorted_list_by_attribute("birthday", "DESC")