oppi.li
1package norm
2
3import (
4 "database/sql"
5 "testing"
6
7 _ "github.com/mattn/go-sqlite3"
8)
9
10// setupTestDB creates an in-memory SQLite database with test data
11func setupTestDB(t *testing.T) *sql.DB {
12 db, err := sql.Open("sqlite3", ":memory:")
13 if err != nil {
14 t.Fatalf("Failed to open database: %v", err)
15 }
16
17 // Create users table
18 _, err = db.Exec(`
19 CREATE TABLE users (
20 id INTEGER PRIMARY KEY AUTOINCREMENT,
21 name TEXT NOT NULL,
22 age INTEGER NOT NULL,
23 email TEXT UNIQUE NOT NULL,
24 department TEXT,
25 active BOOLEAN DEFAULT TRUE,
26 salary REAL,
27 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
28 )
29 `)
30 if err != nil {
31 t.Fatalf("Failed to create users table: %v", err)
32 }
33
34 // Create departments table
35 _, err = db.Exec(`
36 CREATE TABLE departments (
37 id INTEGER PRIMARY KEY AUTOINCREMENT,
38 name TEXT NOT NULL,
39 budget REAL
40 )
41 `)
42 if err != nil {
43 t.Fatalf("Failed to create departments table: %v", err)
44 }
45
46 // Insert test data
47 users := []struct {
48 name, email, department string
49 age int
50 active bool
51 salary float64
52 }{
53 {"John Doe", "john@example.com", "Engineering", 30, true, 75000.0},
54 {"Jane Smith", "jane@example.com", "Marketing", 25, true, 65000.0},
55 {"Bob Johnson", "bob@example.com", "Engineering", 35, false, 80000.0},
56 {"Alice Brown", "alice@example.com", "Sales", 28, true, 70000.0},
57 {"Charlie Wilson", "charlie@example.com", "Engineering", 32, true, 85000.0},
58 {"Diana Prince", "diana@example.com", "Marketing", 29, false, 68000.0},
59 }
60
61 for _, user := range users {
62 _, err = db.Exec(`
63 INSERT INTO users (name, email, department, age, active, salary)
64 VALUES (?, ?, ?, ?, ?, ?)
65 `, user.name, user.email, user.department, user.age, user.active, user.salary)
66 if err != nil {
67 t.Fatalf("Failed to insert user %s: %v", user.name, err)
68 }
69 }
70
71 // Insert departments
72 departments := []struct {
73 name string
74 budget float64
75 }{
76 {"Engineering", 500000.0},
77 {"Marketing", 300000.0},
78 {"Sales", 400000.0},
79 }
80
81 for _, dept := range departments {
82 _, err = db.Exec(`
83 INSERT INTO departments (name, budget)
84 VALUES (?, ?)
85 `, dept.name, dept.budget)
86 if err != nil {
87 t.Fatalf("Failed to insert department %s: %v", dept.name, err)
88 }
89 }
90
91 return db
92}