joemsak · January 26, 2024 18:59 · Jan 26, 2024 · Jan 26, 2024
diff --git a/ex6.rb b/ex6.rb
@@ -4,7 +4,7 @@
 require "active_support/core_ext/string"
 require "pry"
 
-DB = SQLite3::Database.new("./ex5.db")
+DB = SQLite3::Database.new("./ex6.db")
 
 class Pet
   include ActiveModel::Serializers::JSON

diff --git a/ex6.sql b/ex6.sql
@@ -0,0 +1,44 @@
+CREATE TABLE person (
+	id INTEGER PRIMARY KEY,
+	first_name TEXT,
+	last_name TEXT,
+	age INTEGER
+);
+
+CREATE TABLE pet (
+	id INTEGER PRIMARY KEY,
+	name TEXT,
+	breed TEXT,
+	age INTEGER,
+	dead INTEGER
+);
+
+CREATE TABLE person_pet (
+	person_id INTEGER,
+	pet_id INTEGER
+);
+
+INSERT INTO pet (id, name, breed, age, dead)
+  VALUES (0, 'Fluffy', 'Unicorn', 1000, 0);
+
+INSERT INTO pet VALUES (1, 'Gigantor', 'Robot', 1, 1);
+
+
+INSERT INTO person (id, first_name, last_name, age)
+  VALUES (1, 'Joe', 'Sak', 40);
+
+INSERT INTO pet (id, name, breed, age, dead)
+  VALUES (2, 'Fuji', 'Calico', 6, 0);
+
+INSERT INTO pet (id, name, breed, age, dead)
+  VALUES (3, 'Bizen', 'Gray tabby', 9, 0);
+
+INSERT INTO pet (id, name, breed, age, dead)
+  VALUES (4, 'Michu', 'Orange tabby', 3, 0);
+
+INSERT INTO person_pet (person_id, pet_id) VALUES (0, 0);
+INSERT INTO person_pet VALUES (0, 1);
+
+INSERT INTO person_pet (person_id, pet_id) VALUES (1, 2);
+INSERT INTO person_pet (person_id, pet_id) VALUES (1, 3);
+INSERT INTO person_pet (person_id, pet_id) VALUES (1, 4);
diff --git a/shell b/shell
@@ -0,0 +1,11 @@
+sqlite3 ex6.db < ex6.sql
+ruby ex6.rb
+
+{"id"=>0, "first_name"=>"Zed", "last_name"=>"Shaw", "age"=>37}
+{"id"=>0, "name"=>"Fluffy", "breed"=>"Unicorn", "age"=>1000, "dead"=>0}
+{"id"=>1, "name"=>"Gigantor", "breed"=>"Robot", "age"=>1, "dead"=>1}
+---------------------------------------------
+{"id"=>1, "first_name"=>"Joe", "last_name"=>"Sak", "age"=>40}
+{"id"=>2, "name"=>"Fuji", "breed"=>"Calico", "age"=>6, "dead"=>0}
+{"id"=>3, "name"=>"Bizen", "breed"=>"Gray tabby", "age"=>9, "dead"=>0}
+{"id"=>4, "name"=>"Michu", "breed"=>"Orange tabby", "age"=>3, "dead"=>0}
diff --git a/Gemfile b/Gemfile
@@ -0,0 +1,8 @@
+source "https://rubygems.org"
+
+ruby "3.3.0"
+
+gem "activemodel"
+gem "activesupport"
+gem "sqlite3"
+gem "pry"
diff --git a/ex6.rb b/ex6.rb
@@ -0,0 +1,83 @@
+require "sqlite3"
+require "active_model"
+require "active_support"
+require "active_support/core_ext/string"
+require "pry"
+
+DB = SQLite3::Database.new("./ex5.db")
+
+class Pet
+  include ActiveModel::Serializers::JSON
+
+  attr_accessor :id, :name, :breed, :age, :dead
+
+  def attributes=(hash)
+    hash.each do |key, value|
+      send("#{key}=", value)
+    end
+  end
+
+  def attributes
+    instance_values
+  end
+end
+
+class Person
+  include ActiveModel::Serializers::JSON
+
+  def self.find_by(attributes = {})
+    person = new
+    row = DB.execute(%(
+      SELECT * FROM person
+      WHERE #{attributes.map { |k, v| "#{k} = '#{v}'"}.join(" AND ")}
+      LIMIT 1
+    ).squish).last
+
+    person.from_json({
+      id: row[0],
+      first_name: row[1],
+      last_name: row[2],
+      age: row[3]
+    }.to_json)
+  end
+
+  attr_accessor :id, :first_name, :last_name, :age
+
+  def attributes=(hash)
+    hash.each do |key, value|
+      send("#{key}=", value)
+    end
+  end
+
+  def attributes
+    instance_values
+  end
+
+  def pets
+    @pets ||= DB.execute(%(
+      SELECT pet.id, pet.name, pet.breed, pet.age, pet.dead
+      FROM pet, person_pet, person
+      WHERE pet.id = person_pet.pet_id
+        AND person.id = person_pet.person_id
+        AND person.id = #{id}
+    ).squish).map do |row|
+      pet = Pet.new
+      pet.from_json({
+        id: row[0],
+        name: row[1],
+        breed: row[2],
+        age: row[3],
+        dead: row[4]
+      }.to_json)
+    end
+  end
+end
+
+zed = Person.find_by(first_name: 'Zed')
+joe = Person.find_by(first_name: 'Joe')
+
+puts zed.as_json
+zed.pets.each { |pet| puts pet.as_json }
+puts "-" * 45
+puts joe.as_json
+joe.pets.each { |pet| puts pet.as_json }