File size: 1,843 Bytes
d6e2363 | 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 60 61 62 63 64 65 66 67 68 69 70 71 | package database
import (
"database/sql"
"fmt"
"log"
_ "modernc.org/sqlite"
)
type DB struct {
*sql.DB
}
// Connect opens (or creates) the SQLite file at dbPath.
// No external server required — file is created automatically.
func Connect(dbPath string) (*DB, error) {
db, err := sql.Open("sqlite", dbPath)
if err != nil {
return nil, fmt.Errorf("failed to open sqlite: %w", err)
}
// SQLite tuning: WAL mode for concurrent reads, foreign keys on
pragmas := []string{
"PRAGMA journal_mode=WAL;",
"PRAGMA foreign_keys=ON;",
"PRAGMA busy_timeout=5000;",
}
for _, p := range pragmas {
if _, err := db.Exec(p); err != nil {
return nil, fmt.Errorf("pragma failed (%s): %w", p, err)
}
}
if err := db.Ping(); err != nil {
return nil, fmt.Errorf("failed to ping sqlite: %w", err)
}
log.Printf("SQLite connected: %s", dbPath)
return &DB{db}, nil
}
// Migrate creates tables if they don't exist. Safe to call on every startup.
func (db *DB) Migrate() error {
query := `
CREATE TABLE IF NOT EXISTS short_links (
id INTEGER PRIMARY KEY AUTOINCREMENT,
original_url TEXT NOT NULL,
creepy_slug TEXT NOT NULL UNIQUE,
full_short_url TEXT NOT NULL,
destruction_level INTEGER NOT NULL DEFAULT 3
CHECK (destruction_level BETWEEN 1 AND 5),
click_count INTEGER NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_clicked_at DATETIME
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_short_links_slug
ON short_links(creepy_slug);
CREATE INDEX IF NOT EXISTS idx_short_links_created_at
ON short_links(created_at DESC);
`
if _, err := db.Exec(query); err != nil {
return fmt.Errorf("migration failed: %w", err)
}
log.Println("SQLite migration complete")
return nil
} |