การทำงานกับ SQLite ตั้งแต่พื้นฐานถึง Prepared Statements

db = SQLite3::Database.new(path)
db.results_as_hash = true

db.execute <<~SQL
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    price_cents INTEGER NOT NULL,
    category TEXT NOT NULL
  )
SQL

db.execute("SELECT * FROM products ORDER BY id")

statement = db.prepare("SELECT * FROM products WHERE sku = ?")
row = statement.execute("B100").next_hash
statement.close

# EXAMPLE CODE
# Topic: topic_12_sqlite_queries
#
# Purpose:
# - This file demonstrates basic SQLite operations through the Ruby driver.
# - It should pass tests from the beginning.
# - Read it before solving the query and prepared-statement exercises.

require "sqlite3"

class ProductStore
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def setup_schema
    @db.execute <<~SQL
      CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        sku TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL,
        price_cents INTEGER NOT NULL,
        category TEXT NOT NULL
      )
    SQL
  end

  def add_product(sku:, name:, price_cents:, category:)
    @db.execute(
      "INSERT INTO products (sku, name, price_cents, category) VALUES (?, ?, ?, ?)",
      [sku, name, price_cents, category]
    )
  end

  def all_products
    @db.execute("SELECT sku, name, price_cents, category FROM products ORDER BY id")
      .map { |row| normalize_row(row) }
  end

  private

  def normalize_row(row)
    {
      "sku" => row["sku"],
      "name" => row["name"],
      "price_cents" => row["price_cents"],
      "category" => row["category"]
    }
  end
end

# STUDENT TASK (BASIC)
# Topic: topic_12_sqlite_queries
#
# What to do:
# - Build a focused query object on top of the product table.
# - Keep the class about asking questions, not about schema creation.
# - Use plain SQL queries first before moving to prepared statements in the advanced step.
#
# Expected outcome:
# - You can answer a couple of small business questions from stored data.

require "sqlite3"

class ProductQueries
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def names_in_category(category)
    @db.execute(
      "SELECT name FROM products WHERE category = '#{category}' ORDER BY name"
    ).map { |row| row["name"] }
  end

  def average_price_in_category(category)
    row = @db.get_first_row(
      "SELECT AVG(price_cents) AS average_price FROM products WHERE category = '#{category}'"
    )

    row["average_price"]&.to_f
  end
end

# STUDENT TASK (ADVANCED)
# Topic: topic_12_sqlite_queries
#
# Academic purpose:
# - Move from simple SQL strings to prepared statements with parameter binding.
# - Learn that database safety and clarity improve when values are passed separately from SQL text.
#
# Real-world use case:
# - Production systems often look up records by identifiers such as SKU, email, or external ID.
# - Prepared statements make those repeated lookups safer and easier to reason about.
#
# Why Ruby is beautiful here:
# - The `sqlite3` gem exposes prepared statements directly without heavy ceremony.
# - The method stays compact while still teaching a serious database practice.
# - A tiny repository object can make low-level SQL behavior feel approachable.
#
# What to do:
# - Build lookup methods using prepared statements.
# - Close statements explicitly after use.
# - Return ordinary Ruby hashes so the caller stays decoupled from statement objects.
#
# Expected outcome:
# - Advanced tests pass and you can explain why parameter binding is better than interpolation.

require "sqlite3"

class PreparedProductQueries
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def find_by_sku(sku)
    statement = @db.prepare("SELECT sku, name, price_cents, category FROM products WHERE sku = ?")
    result = statement.execute(sku)
    row = result.next_hash
    normalize_row(row)
  ensure
    safely_close(result)
    safely_close(statement)
  end

  def cheaper_than(max_price_cents)
    statement = @db.prepare(
      "SELECT sku, name, price_cents, category FROM products WHERE price_cents < ? ORDER BY price_cents ASC"
    )
    result = statement.execute(max_price_cents)
    rows = result.map { |row| normalize_row(row) }
    rows
  ensure
    safely_close(result)
    safely_close(statement)
  end

  private

  def normalize_row(row)
    return nil unless row

    {
      "sku" => row["sku"],
      "name" => row["name"],
      "price_cents" => row["price_cents"],
      "category" => row["category"]
    }
  end

  def safely_close(resource)
    resource&.close
  rescue SQLite3::Exception
    nil
  end
end

# ANSWER KEY (BASIC)
# Topic: topic_12_sqlite_queries
#
# Solution idea:
# - Keep the setup/store object separate from the reporting-style query object.
# - Start with straightforward SQL so the student can focus on what each query asks.
# - Return Ruby values rather than database driver objects.

require "sqlite3"

class ProductQueries
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def names_in_category(category)
    @db.execute(
      "SELECT name FROM products WHERE category = '#{category}' ORDER BY name"
    ).map { |row| row["name"] }
  end

  def average_price_in_category(category)
    row = @db.get_first_row(
      "SELECT AVG(price_cents) AS average_price FROM products WHERE category = '#{category}'"
    )

    row["average_price"]&.to_f
  end
end

# ANSWER KEY (ADVANCED)
# Topic: topic_12_sqlite_queries
#
# Solution idea:
# - Replace interpolation with prepared statements and bound parameters.
# - Keep statement lifecycle local to each method.
# - Normalize returned rows so the rest of the code only sees simple hashes.

require "sqlite3"

class PreparedProductQueries
  def initialize(path)
    @db = SQLite3::Database.new(path)
    @db.results_as_hash = true
  end

  def find_by_sku(sku)
    statement = @db.prepare("SELECT sku, name, price_cents, category FROM products WHERE sku = ?")
    result = statement.execute(sku)
    row = result.next_hash
    normalize_row(row)
  ensure
    safely_close(result)
    safely_close(statement)
  end

  def cheaper_than(max_price_cents)
    statement = @db.prepare(
      "SELECT sku, name, price_cents, category FROM products WHERE price_cents < ? ORDER BY price_cents ASC"
    )
    result = statement.execute(max_price_cents)
    rows = result.map { |row| normalize_row(row) }
    rows
  ensure
    safely_close(result)
    safely_close(statement)
  end

  private

  def normalize_row(row)
    return nil unless row

    {
      "sku" => row["sku"],
      "name" => row["name"],
      "price_cents" => row["price_cents"],
      "category" => row["category"]
    }
  end

  def safely_close(resource)
    resource&.close
  rescue SQLite3::Exception
    nil
  end
end

# This spec is your learning companion for topic_12_sqlite_queries.
#
# How to use this file:
# 1) Run tests and observe failures or successes.
# 2) Keep the EXAMPLE specs green from the beginning.
# 3) Implement the BASIC exercise as sprint 2: simple query methods.
# 4) Implement the ADVANCED exercise as sprint 3: prepared statements.
#
# Expected final result:
# - All examples in this file pass.
# - You understand SQLite work as schema setup, query design, and parameter binding.

require "tempfile"

require_relative "../example"
require_relative "../basic_exercise"
require_relative "../adv_exercise"

RSpec.describe "topic_12_sqlite_queries" do
  def with_db_path
    file = Tempfile.new(["products", ".sqlite3"])
    file.close
    yield file.path
  ensure
    File.delete(file.path) if file && File.exist?(file.path)
  end

  describe "EXAMPLE purpose: understand basic SQLite operations before adding query objects" do
    it "creates schema, inserts rows, and reads them back" do
      with_db_path do |path|
        store = ProductStore.new(path)
        store.setup_schema
        store.add_product(sku: "B100", name: "Ruby Book", price_cents: 3000, category: "books")
        store.add_product(sku: "A200", name: "Desk Lamp", price_cents: 4500, category: "home")

        expect(store.all_products).to eq(
          [
            { "sku" => "B100", "name" => "Ruby Book", "price_cents" => 3000, "category" => "books" },
            { "sku" => "A200", "name" => "Desk Lamp", "price_cents" => 4500, "category" => "home" }
          ]
        )
      end
    end
  end

  describe "BASIC EXERCISE purpose: ask simple questions from persisted rows" do
    it "returns names and average price for a category" do
      with_db_path do |path|
        store = ProductStore.new(path)
        store.setup_schema
        store.add_product(sku: "B100", name: "Ruby Book", price_cents: 3000, category: "books")
        store.add_product(sku: "B200", name: "Refactoring", price_cents: 5000, category: "books")
        store.add_product(sku: "A200", name: "Desk Lamp", price_cents: 4500, category: "home")

        queries = ProductQueries.new(path)

        expect(queries.names_in_category("books")).to eq(["Refactoring", "Ruby Book"])
        expect(queries.average_price_in_category("books")).to eq(4000.0)
      end
    end
  end

  describe "ADVANCED EXERCISE purpose: use prepared statements for focused lookups" do
    it "finds products by sku and filters by price threshold" do
      with_db_path do |path|
        store = ProductStore.new(path)
        store.setup_schema
        store.add_product(sku: "B100", name: "Ruby Book", price_cents: 3000, category: "books")
        store.add_product(sku: "B200", name: "Refactoring", price_cents: 5000, category: "books")
        store.add_product(sku: "A200", name: "Desk Lamp", price_cents: 4500, category: "home")

        queries = PreparedProductQueries.new(path)

        expect(queries.find_by_sku("B200")).to eq(
          { "sku" => "B200", "name" => "Refactoring", "price_cents" => 5000, "category" => "books" }
        )
        expect(queries.cheaper_than(4600)).to eq(
          [
            { "sku" => "B100", "name" => "Ruby Book", "price_cents" => 3000, "category" => "books" },
            { "sku" => "A200", "name" => "Desk Lamp", "price_cents" => 4500, "category" => "home" }
          ]
        )
      end
    end
  end
end

#!/usr/bin/env bash
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
ROOT_DIR="$(cd "${SCRIPT_DIR}/.." && pwd)"

exec "${ROOT_DIR}/run_tests.sh" "$(basename "${SCRIPT_DIR}")"