SQL CRUD Lecture Code

doctor.rb

# Our Model will have to "talk" to the DB
# It will send SQL queries

# DB - we have access to this constant

class Doctor
  attr_reader :id, :name, :age, :specialty

  def initialize(attributes = {})
    @id = attributes[:id]
    @name = attributes[:name]
    @age = attributes[:age]
    @specialty = attributes[:specialty]
  end

  # READ - Fetch all doctors
  # SQL - SELECT * FROM doctors
  # Class method:
  def self.all # should return an array of Doctor instances
    rows = DB.execute("SELECT * FROM doctors")
    rows.map do |row|
      # converting row keys from Strings into Symbols
      # row.transform_keys!(&:to_sym)
      new(row.transform_keys!(&:to_sym))
    end
  end

  # READ - Fetch a specific doctor
  # SQL - SELECT * FROM doctors WHERE id = ?
  def self.find(id)
    row = DB.execute("SELECT * FROM doctors WHERE id = #{id}")
            .first

    # if the id doesn't exist we want to return nil
    return nil if row.nil?

    new(row.transform_keys!(&:to_sym))
  end

  def self.create(attributes)
    # new (instance)
    # save
  end

  def self.update(attributes)
    # new (instance)
    # save
  end

  # I already have an instance
  # instance method
  def save
    # 2 scenarios
    # It's either a Create or an Update
    if id.nil?
      # it's a CREATE
      # INSERT INTO doctors (name, age, specialty)
      # VALUES (#{@name}, #{@age}, #{@specialty})
    else
      # it's an UPDATE
      # UPDATE doctors SET name=#{@name}, ...
    end
  end
end

test.rb

# Gem/Library to allow Ruby to interact with the SQL Lite DB
require 'sqlite3'
require 'byebug'

# Creating a new instance of SQLITE3::Database
# This creates a connection to the DB
DB = SQLite3::Database.new("doctors.db")

# [[10000, "Greg", 56, "MD"], [10001, "Doogie Houser", 14, "Know-it-all"]]
DB.results_as_hash = true

require_relative 'doctor'

doctors = Doctor.all

last_doctor = Doctor.find(doctors.last.id) # => instance of a Doctor

# Create
puts "What's the name of the doctor?"
name = gets.chomp

puts "How old is the doctor?"
age = gets.chomp.to_i

puts "What's the specialty of the doctor"
specialty = gets.chomp

# Create
doctor = Doctor.create(
  name: name,
  age: age,
  specialty: specialty
)

p doctor



puts "End"