sql-masterclass/Makefile at main · learnwithparam/sql-masterclass · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
.PHONY: help install setup-data up down init-db verify clean test

export PGPASSWORD=masterclass

help: ## Show this help message
	@grep -E '^[a-zA-Z_-]+:.*?## .*$$' $(MAKEFILE_LIST) | sort | awk 'BEGIN {FS = ":.*?## "}; {printf "\033[36m%-30s\033[0m %s\n", $$1, $$2}'

install: ## Install dependencies using uv
	@echo "📦 Installing dependencies..."
	@uv sync

setup: install setup-data ## Install deps, download data, and init db

setup-data: ## Download data
	@echo "⬇️ Downloading data..."
	@uv run python download_data.py

up: ## Start PostgreSQL via Docker Compose
	@echo "🐳 Starting PostgreSQL container..."
	@docker compose up -d
	@echo "⏳ Waiting for database to be ready..."
	@sleep 5

down: ## Stop PostgreSQL container
	@echo "🛑 Stopping PostgreSQL container..."
	@docker compose down

init-db: ## Load CSV data into PostgreSQL (Requires 'up')
	@echo "🗄️ Loading data into PostgreSQL..."
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -f /app/00-setup/create_tables_postgres.sql
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -c "\copy customers FROM '/app/data/olist_customers_dataset.csv' WITH (FORMAT csv, HEADER true, QUOTE '\"');"
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -c "\copy sellers FROM '/app/data/olist_sellers_dataset.csv' WITH (FORMAT csv, HEADER true, QUOTE '\"');"
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -c "\copy products FROM '/app/data/olist_products_dataset.csv' WITH (FORMAT csv, HEADER true, QUOTE '\"');"
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -c "\copy product_category_name_translation FROM '/app/data/product_category_name_translation.csv' WITH (FORMAT csv, HEADER true);"
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -c "\copy orders FROM '/app/data/olist_orders_dataset.csv' WITH (FORMAT csv, HEADER true, QUOTE '\"');"
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -c "\copy order_items FROM '/app/data/olist_order_items_dataset.csv' WITH (FORMAT csv, HEADER true, QUOTE '\"');"
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -c "\copy order_payments FROM '/app/data/olist_order_payments_dataset.csv' WITH (FORMAT csv, HEADER true, QUOTE '\"');"
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -c "\copy order_reviews FROM '/app/data/olist_order_reviews_dataset.csv' WITH (FORMAT csv, HEADER true, QUOTE '\"');"
	@docker exec -i sql-masterclass-db psql -U postgres -d ecommerce -c "\copy geolocation FROM '/app/data/olist_geolocation_dataset.csv' WITH (FORMAT csv, HEADER true, QUOTE '\"');"
	@echo "✅ Data load complete."

init-sqlite: ## Initialize SQLite fallback database
	@echo "🗄️ Initializing SQLite database..."
	@uv run python 00-setup/init_sqlite.py

verify: ## Run verification script across all SQL chapters
	@echo "🧪 Verifying SQL queries against database..."
	@uv run python test.py

test: ## Run test script across all SQL chapters
	@echo "🧪 Running test script..."
	@uv run python test.py

clean: ## Remove databases and environments
	@echo "🧹 Cleaning up..."
	@rm -rf .venv
	@rm -f ecommerce.db
	@docker compose down -v
	@rm -rf data