Hands-on lab — Day 39: Data Storage: Files, Databases, Object Storage, and Caches

#!/usr/bin/env bash
# Day 039 lab — "Store Data Four Ways" (completed reference implementation).
#
# Stores the SAME small dataset four ways and shows what each is good for:
#   1) FILE            — structured data as a CSV file you read back
#   2) DATABASE        — a SQLite database you QUERY (beats grepping a file)
#   3) OBJECT STORAGE  — a blob filed by a content-key (hash) in a bucket dir
#   4) CACHE           — a key->value file cache showing a HIT vs a recompute
#
# Offline, no network, no sudo. Requires: bash and sqlite3 (preinstalled on
# macOS and most Linux). Everything is created under a temporary work dir and
# cleaned up on exit.
set -euo pipefail

# --- Setup: a private scratch directory, removed automatically on exit -------
# The test harness may set STORAGE_DEMO_WORKDIR to a directory it wants to
# inspect afterward; in that case we use it and do NOT delete it. Normal runs
# get a fresh temp dir that is cleaned up on exit.
if [ -n "${STORAGE_DEMO_WORKDIR:-}" ]; then
  work="${STORAGE_DEMO_WORKDIR}"
  mkdir -p "${work}"
  trap - EXIT
else
  work="$(mktemp -d "${TMPDIR:-/tmp}/storage_demo.XXXXXX")"
  cleanup() { rm -rf "${work}"; }
  trap cleanup EXIT
fi

# Pick a SHA-256 tool that exists on both macOS and Linux.
if command -v shasum >/dev/null 2>&1; then
  sha256() { shasum -a 256 "$1" | awk '{print $1}'; }
elif command -v sha256sum >/dev/null 2>&1; then
  sha256() { sha256sum "$1" | awk '{print $1}'; }
else
  echo "Need shasum or sha256sum (both are standard on macOS/Linux)." >&2
  exit 1
fi

echo "=== Store Data Four Ways ==="
echo "Work dir: ${work}"
echo

# --- 1) FILE: structured data as CSV -----------------------------------------
# A file is the simplest store: named bytes on disk. Perfect for a small,
# single-writer dataset like this. We write it, then read it back.
echo "[1/4] FILE (structured CSV)"
orders_csv="${work}/orders.csv"
cat > "${orders_csv}" <<'CSV'
id,customer,state,amount
1,ada,OH,120.0
2,grace,OH,90.0
3,linus,CA,200.0
4,ada,OH,60.0
CSV
echo "  Wrote ${orders_csv}:"
sed 's/^/    /' "${orders_csv}"
echo "  -> A file holds the raw rows, but can only be read whole or grepped."
echo

# --- 2) DATABASE: SQLite you can QUERY ---------------------------------------
# The same data in a real relational database. We create a table, insert the
# rows, then run a SELECT with a WHERE and an aggregate (SUM ... GROUP BY) —
# a precise question a file could never answer without scanning everything.
echo "[2/4] DATABASE (SQLite)"
db="${work}/storage_demo.db"
sqlite3 "${db}" <<'SQL'
CREATE TABLE orders (
  id       INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  state    TEXT NOT NULL,
  amount   REAL NOT NULL
);
INSERT INTO orders (customer, state, amount) VALUES
  ('ada',   'OH', 120.0),
  ('grace', 'OH',  90.0),
  ('linus', 'CA', 200.0),
  ('ada',   'OH',  60.0);
CREATE INDEX idx_orders_state ON orders(state);
SQL
echo "  Created table 'orders' and inserted 4 rows (with an index on state)."
echo "  Query: total revenue per customer in state 'OH'"
sqlite3 "${db}" \
  "SELECT customer, SUM(amount) AS total
     FROM orders
    WHERE state = 'OH'
    GROUP BY customer
    ORDER BY customer;" | sed 's/^/  /'
echo "  -> A SQL query answered a precise question that a file could only grep."
echo

# --- 3) OBJECT STORAGE: a blob filed by content-key --------------------------
# Object storage keeps whole blobs in a flat "bucket", each under a key. Here
# the key is the blob's own SHA-256 hash (content-addressed): identical bytes
# always get the same key, and you fetch the blob back by that key.
echo "[3/4] OBJECT STORAGE (bucket + content key)"
bucket="${work}/bucket"
mkdir -p "${bucket}"
blob_src="${work}/report.txt"
printf 'quarterly report: revenue up, storage bill down\n' > "${blob_src}"
key="$(sha256 "${blob_src}")"
cp "${blob_src}" "${bucket}/${key}"
echo "  PUT object under key: ${key}"
echo "  GET object back by key:"
cat "${bucket}/${key}" | sed 's/^/    /'
echo "  -> The bucket is flat; the key names the blob; we fetch it whole."
echo

# --- 4) CACHE: key -> value with a HIT vs recompute --------------------------
# A cache is a fast, disposable copy in front of slow work. compute() pretends
# to be expensive. The cache stores results by key; a second call for the same
# key is served from the cache (a HIT) instead of recomputing.
echo "[4/4] CACHE (key -> value with timestamp)"
cache_dir="${work}/cache"
mkdir -p "${cache_dir}"

expensive_total_for_state() {
  # Pretend this is slow (e.g. a big scan). It queries the DB for a state total.
  sqlite3 "${db}" "SELECT COALESCE(SUM(amount),0) FROM orders WHERE state='$1';"
}

cached_total_for_state() {
  local state="$1"
  local cache_file="${cache_dir}/total_${state}"
  if [ -f "${cache_file}" ]; then
    echo "HIT  -> served from cache (no recompute): $(cat "${cache_file}")"
  else
    local value; value="$(expensive_total_for_state "${state}")"
    printf '%s' "${value}" > "${cache_file}"
    echo "MISS -> computed and stored: ${value} (at $(date '+%H:%M:%S'))"
  fi
}

echo "  First call:  $(cached_total_for_state OH)"
echo "  Second call: $(cached_total_for_state OH)"
echo "  -> The second call skipped the work. Delete the cache and nothing is"
echo "     lost — the real data still lives in the file, database, and bucket."
echo

echo "=== Done. Each store fit a different job. Scratch dir will be cleaned up. ==="

lesson_id: D039
day: 39
kind: command-line-inspection
languages: [bash]
setup_commands:
  - cd labs/sections/computing-foundations/day-039-data-storage-files-databases-object-storage
run_commands:
  - bash examples/storage_demo.sh
  - bash starter/storage_demo.sh
test_commands:
  - bash tests/run_tests.sh
cleanup_commands:
  - '# nothing to clean: scripts use a temp dir removed automatically on exit'
requires_network: false
requires_api_key: false
estimated_minutes: 30
last_executed: '2026-07-12'
executed_on: 'macOS (Apple Silicon), sqlite3 3.51.0, bash tests/run_tests.sh → 7 checks, 0 failure(s), exit 0'

# Dependencies — Day 039 lab

This lab needs only a POSIX shell and the `sqlite3` command-line tool.

- **`bash`** ≥ 3.2 — preinstalled on macOS and every mainstream Linux
  distribution.
- **`sqlite3`** — the embedded relational database used in step 2.
  - **macOS:** preinstalled. Confirm with `sqlite3 --version`.
  - **Debian/Ubuntu:** `sudo apt install sqlite3` if it is missing.
  - **Fedora/RHEL:** `sudo dnf install sqlite`.
  - **Windows:** use WSL and install `sqlite3` inside it as on Linux.
- **`shasum` or `sha256sum`** — for the object-storage content key. One of
  these is always present on macOS and Linux; the scripts pick whichever
  exists, and both produce the same SHA-256 value.

There is deliberately no `requirements.txt`/`package.json`: the lab installs
nothing beyond the `sqlite3` package above and runs fully offline. No network
access and no API keys are required.

#!/usr/bin/env bash
# Day 039 lab — "Store Data Four Ways" (YOUR working file).
#
# Complete the FOUR numbered exercises below. Each one names the exact command
# to use. The finished reference is in examples/storage_demo.sh — try to do it
# yourself first, then compare. Run this file with:  bash starter/storage_demo.sh
#
# Offline, no network, no sudo. Requires: bash and sqlite3.
set -euo pipefail

work="$(mktemp -d "${TMPDIR:-/tmp}/storage_demo.XXXXXX")"
cleanup() { rm -rf "${work}"; }
trap cleanup EXIT

if command -v shasum >/dev/null 2>&1; then
  sha256() { shasum -a 256 "$1" | awk '{print $1}'; }
elif command -v sha256sum >/dev/null 2>&1; then
  sha256() { sha256sum "$1" | awk '{print $1}'; }
else
  echo "Need shasum or sha256sum." >&2; exit 1
fi

echo "=== Store Data Four Ways (starter) ==="
echo "Work dir: ${work}"
echo

# --- 1) FILE (given, already works) ------------------------------------------
echo "[1/4] FILE (structured CSV)"
orders_csv="${work}/orders.csv"
cat > "${orders_csv}" <<'CSV'
id,customer,state,amount
1,ada,OH,120.0
2,grace,OH,90.0
3,linus,CA,200.0
4,ada,OH,60.0
CSV
echo "  Wrote ${orders_csv}"
echo

# --- 2) DATABASE -------------------------------------------------------------
echo "[2/4] DATABASE (SQLite)"
db="${work}/storage_demo.db"

# Exercise 1: CREATE the 'orders' table and INSERT the four rows.
# Use sqlite3 with a heredoc. Columns: id INTEGER PRIMARY KEY, customer TEXT,
# state TEXT, amount REAL. Insert the same four rows as the CSV above.
# Replace the line below with your sqlite3 command.
#   sqlite3 "${db}" <<'SQL'  ... SQL
echo "  (exercise 1: create table + insert rows)"

# Exercise 2: run a SELECT with a WHERE and an aggregate. Print total revenue
# per customer in state 'OH', grouped by customer, ordered by customer:
#   sqlite3 "${db}" "SELECT customer, SUM(amount) AS total FROM orders
#                    WHERE state='OH' GROUP BY customer ORDER BY customer;"
echo "  Query result (exercise 2):"
echo "  (exercise 2: run the SELECT here)"
echo

# --- 3) OBJECT STORAGE -------------------------------------------------------
echo "[3/4] OBJECT STORAGE (bucket + content key)"
bucket="${work}/bucket"; mkdir -p "${bucket}"
blob_src="${work}/report.txt"
printf 'quarterly report: revenue up, storage bill down\n' > "${blob_src}"

# Exercise 3: store the blob under a content key (its SHA-256 hash), then fetch
# it back by key. Compute the key with:  key="$(sha256 "${blob_src}")"
# Then copy the blob into the bucket:     cp "${blob_src}" "${bucket}/${key}"
# Then read it back:                      cat "${bucket}/${key}"
echo "  (exercise 3: put blob by key, then get it back)"
echo

# --- 4) CACHE ----------------------------------------------------------------
echo "[4/4] CACHE (key -> value)"
cache_dir="${work}/cache"; mkdir -p "${cache_dir}"

expensive_total_for_state() {
  sqlite3 "${db}" "SELECT COALESCE(SUM(amount),0) FROM orders WHERE state='$1';"
}

cached_total_for_state() {
  local state="$1"
  local cache_file="${cache_dir}/total_${state}"
  # Exercise 4: implement the cache. If "${cache_file}" exists, print
  #   "HIT  -> $(cat "${cache_file}")"
  # otherwise compute value="$(expensive_total_for_state "${state}")", save it
  # to "${cache_file}", and print "MISS -> ${value}".
  echo "MISS -> (exercise 4: implement HIT/MISS)"
}

echo "  First call:  $(cached_total_for_state OH)"
echo "  Second call: $(cached_total_for_state OH)"
echo

echo "=== Done. Compare with examples/storage_demo.sh. ==="

# Storage worksheet — Day 039

Fill this in as you complete `starter/storage_demo.sh` and read the lesson.

## 1. Your database query and its result

Write the exact `SELECT` you ran in Exercise 2 (the OH revenue-per-customer query):

```sql
-- paste your query here
```

Paste its result (one row per Ohio customer):

```text
-- paste the output rows here (e.g. ada|180.0)
```

One sentence: why can the database answer this precise question faster than
grepping the CSV file, once the table has many rows?

> _your answer_

## 2. The object key you generated

Paste the content key (SHA-256 hash) your blob was stored under in Exercise 3:

```text
-- paste the key here
```

One sentence: if you store the exact same bytes again, what key do they get,
and why does that give you deduplication for free?

> _your answer_

## 3. Which store would you pick, and why?

| Workload | Store you'd pick | Why (one sentence) |
| --- | --- | --- |
| A 5 GB training dataset | | |
| A user profile record (name, email, settings) | | |
| A hot counter incremented thousands of times per second | | |

## 4. One thing that surprised you

> _your answer_

#!/usr/bin/env bash
# Tests for the Day 039 lab. Run from the lab directory:
#   bash tests/run_tests.sh
#
# Drives the completed reference demo into an inspectable work directory and
# checks real behavior of all four stores:
#   - the SQLite database file is created
#   - the SELECT ... WHERE ... GROUP BY returns the expected OH rows
#   - the object blob is retrievable from the bucket by its content key
#   - the cache returns a HIT on the second call for the same key
# No network. Exits 0 on success, non-zero on any failure.
set -u

lab_dir="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
demo="${lab_dir}/examples/storage_demo.sh"
failures=0
checks=0

check() {
  local label="$1" ok="$2"
  checks=$((checks + 1))
  if [ "${ok}" = "yes" ]; then
    echo "  ok: ${label}"
  else
    echo "  FAIL: ${label}"
    failures=$((failures + 1))
  fi
}

# Pre-flight: sqlite3 must exist (the lab's whole point).
if command -v sqlite3 >/dev/null 2>&1; then
  check "sqlite3 is installed" "yes"
else
  check "sqlite3 is installed" "no"
  echo
  echo "${checks} checks, ${failures} failure(s)."
  echo "Install sqlite3 first (macOS: preinstalled; Debian/Ubuntu: apt install sqlite3)."
  exit 1
fi

# Run the reference demo into a known, inspectable work dir (no network).
work="$(mktemp -d "${TMPDIR:-/tmp}/storage_demo_test.XXXXXX")"
trap 'rm -rf "${work}"' EXIT
output="$(STORAGE_DEMO_WORKDIR="${work}" bash "${demo}" 2>&1)"
rc=$?
check "demo script exits successfully" "$([ ${rc} -eq 0 ] && echo yes || echo no)"

# 1) DATABASE: file created and the query returns the expected aggregated rows.
check "SQLite database file was created" "$([ -f "${work}/storage_demo.db" ] && echo yes || echo no)"

query_out="$(sqlite3 "${work}/storage_demo.db" \
  "SELECT customer, SUM(amount) FROM orders WHERE state='OH' GROUP BY customer ORDER BY customer;" 2>/dev/null)"
expected_query="ada|180.0
grace|90.0"
check "query returns expected OH rows (ada|180.0, grace|90.0)" \
  "$([ "${query_out}" = "${expected_query}" ] && echo yes || echo no)"

# 2) OBJECT STORAGE: the blob is retrievable from the bucket by its key, and
#    re-hashing the retrieved bytes reproduces that same key (content-addressed).
if command -v shasum >/dev/null 2>&1; then
  hasher() { shasum -a 256 "$1" | awk '{print $1}'; }
else
  hasher() { sha256sum "$1" | awk '{print $1}'; }
fi
stored_key="$(ls "${work}/bucket" 2>/dev/null | head -1)"
if [ -n "${stored_key}" ] && [ -f "${work}/bucket/${stored_key}" ]; then
  rehash="$(hasher "${work}/bucket/${stored_key}")"
  check "blob is retrievable by key and key matches its content hash" \
    "$([ "${rehash}" = "${stored_key}" ] && echo yes || echo no)"
else
  check "blob is retrievable by key and key matches its content hash" "no"
fi

# 3) CACHE: the printed output shows a MISS then a HIT for the same key.
echo "${output}" | grep -q "First call:  MISS" && miss=yes || miss=no
echo "${output}" | grep -q "Second call: HIT"  && hit=yes  || hit=no
check "cache prints MISS on the first call" "${miss}"
check "cache prints HIT on the second call (no recompute)" "${hit}"

echo
echo "${checks} checks, ${failures} failure(s)."
[ "${failures}" -eq 0 ]