diff options
| -rw-r--r-- | backend/db/migrations.go | 174 | ||||
| -rw-r--r-- | backend/db/migrations/001_initial_schema.sql | 27 | ||||
| -rw-r--r-- | backend/db/migrations/002_add_feeds_is_subscribed.sql | 3 | ||||
| -rw-r--r-- | backend/main.go | 28 | ||||
| -rw-r--r-- | justfile | 3 |
5 files changed, 224 insertions, 11 deletions
diff --git a/backend/db/migrations.go b/backend/db/migrations.go new file mode 100644 index 0000000..9a146a1 --- /dev/null +++ b/backend/db/migrations.go @@ -0,0 +1,174 @@ +package db + +import ( + "database/sql" + "embed" + "fmt" + "log" + "path/filepath" + "sort" + "strconv" + "strings" +) + +//go:embed migrations/*.sql +var migrationsFS embed.FS + +const EXPECTED_SCHEMA_VERSION = 2 + +type Migration struct { + Version int + Filename string + SQL string +} + +func initMigrationTable(db *sql.DB) error { + query := ` + CREATE TABLE IF NOT EXISTS schema_migrations ( + version INTEGER PRIMARY KEY, + applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP + );` + + _, err := db.Exec(query) + return err +} + +func getSchemaVersion(db *sql.DB) (int, error) { + var version int + err := db.QueryRow("SELECT COALESCE(MAX(version), 0) FROM schema_migrations").Scan(&version) + if err != nil { + return 0, err + } + return version, nil +} + +func ValidateSchemaVersion(db *sql.DB) error { + currentVersion, err := getSchemaVersion(db) + if err != nil { + return fmt.Errorf("failed to get schema version: %w", err) + } + + if currentVersion != EXPECTED_SCHEMA_VERSION { + return fmt.Errorf("schema version mismatch: expected %d, got %d. Run with --migrate to update schema", + EXPECTED_SCHEMA_VERSION, currentVersion) + } + + return nil +} + +func LoadMigrations() ([]Migration, error) { + entries, err := migrationsFS.ReadDir("migrations") + if err != nil { + return nil, fmt.Errorf("failed to read migrations directory: %w", err) + } + + var migrations []Migration + + for _, entry := range entries { + if entry.IsDir() || !strings.HasSuffix(entry.Name(), ".sql") { + continue + } + + // Parse version from filename (e.g., "001_initial_schema.sql" -> 1) + parts := strings.SplitN(entry.Name(), "_", 2) + if len(parts) < 2 { + continue + } + + version, err := strconv.Atoi(parts[0]) + if err != nil { + log.Printf("Warning: invalid migration filename %s, skipping", entry.Name()) + continue + } + + // Read migration SQL + sqlBytes, err := migrationsFS.ReadFile(filepath.Join("migrations", entry.Name())) + if err != nil { + return nil, fmt.Errorf("failed to read migration %s: %w", entry.Name(), err) + } + + migrations = append(migrations, Migration{ + Version: version, + Filename: entry.Name(), + SQL: string(sqlBytes), + }) + } + + // Sort migrations by version + sort.Slice(migrations, func(i, j int) bool { + return migrations[i].Version < migrations[j].Version + }) + + return migrations, nil +} + +func RunMigrations(db *sql.DB) error { + // Initialize migration table + if err := initMigrationTable(db); err != nil { + return fmt.Errorf("failed to initialize migration table: %w", err) + } + + // Get current version + currentVersion, err := getSchemaVersion(db) + if err != nil { + return fmt.Errorf("failed to get current schema version: %w", err) + } + + // Load all migrations + migrations, err := LoadMigrations() + if err != nil { + return fmt.Errorf("failed to load migrations: %w", err) + } + + // Find pending migrations + var pendingMigrations []Migration + for _, migration := range migrations { + if migration.Version > currentVersion { + pendingMigrations = append(pendingMigrations, migration) + } + } + + if len(pendingMigrations) == 0 { + log.Printf("No pending migrations. Current schema version: %d", currentVersion) + return nil + } + + log.Printf("Running %d pending migrations...", len(pendingMigrations)) + + // Execute each pending migration in a transaction + for _, migration := range pendingMigrations { + log.Printf("Applying migration %d: %s", migration.Version, migration.Filename) + + tx, err := db.Begin() + if err != nil { + return fmt.Errorf("failed to start transaction for migration %d: %w", migration.Version, err) + } + + // Execute migration SQL + _, err = tx.Exec(migration.SQL) + if err != nil { + tx.Rollback() + return fmt.Errorf("failed to execute migration %d: %w", migration.Version, err) + } + + // Record migration as applied + _, err = tx.Exec( + "INSERT INTO schema_migrations (version) VALUES (?)", + migration.Version, + ) + if err != nil { + tx.Rollback() + return fmt.Errorf("failed to record migration %d: %w", migration.Version, err) + } + + // Commit transaction + if err = tx.Commit(); err != nil { + return fmt.Errorf("failed to commit migration %d: %w", migration.Version, err) + } + + log.Printf("Successfully applied migration %d", migration.Version) + } + + log.Printf("All migrations completed. Schema version: %d", EXPECTED_SCHEMA_VERSION) + return nil +} diff --git a/backend/db/migrations/001_initial_schema.sql b/backend/db/migrations/001_initial_schema.sql new file mode 100644 index 0000000..6f0b75e --- /dev/null +++ b/backend/db/migrations/001_initial_schema.sql @@ -0,0 +1,27 @@ +-- Initial schema + +-- Feeds +CREATE TABLE IF NOT EXISTS feeds ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + url TEXT NOT NULL, + title TEXT NOT NULL, + fetched_at TEXT NOT NULL +); + +-- Articles +CREATE TABLE IF NOT EXISTS articles ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + feed_id INTEGER NOT NULL, + guid TEXT NOT NULL, + title TEXT NOT NULL, + url TEXT NOT NULL, + is_read INTEGER NOT NULL DEFAULT 0, + FOREIGN KEY (feed_id) REFERENCES feeds(id) ON DELETE CASCADE +); + +-- Indice +CREATE INDEX IF NOT EXISTS idx_articles_feed_id ON articles(feed_id); + +CREATE INDEX IF NOT EXISTS idx_articles_feed_guid ON articles(feed_id, guid); + +CREATE INDEX IF NOT EXISTS idx_articles_is_read ON articles(is_read);
\ No newline at end of file diff --git a/backend/db/migrations/002_add_feeds_is_subscribed.sql b/backend/db/migrations/002_add_feeds_is_subscribed.sql new file mode 100644 index 0000000..30bb273 --- /dev/null +++ b/backend/db/migrations/002_add_feeds_is_subscribed.sql @@ -0,0 +1,3 @@ +-- Add is_subscribed column to feeds table. + +ALTER TABLE feeds ADD COLUMN is_subscribed INTEGER NOT NULL DEFAULT 1;
\ No newline at end of file diff --git a/backend/main.go b/backend/main.go index 0efe66a..ecfcbae 100644 --- a/backend/main.go +++ b/backend/main.go @@ -4,6 +4,7 @@ import ( "context" "database/sql" "embed" + "flag" "fmt" "log" "net/http" @@ -36,15 +37,8 @@ var ( queries *db.Queries //go:embed public/* publicFS embed.FS - //go:embed db/schema.sql - dbSchema string ) -func initDB(db *sql.DB) error { - _, err := db.Exec(dbSchema) - return err -} - func fetchOneFeed(feedID int64, url string, ctx context.Context) error { log.Printf("Fetching %s...\n", url) fp := gofeed.NewParser() @@ -150,7 +144,14 @@ func scheduled(ctx context.Context, d time.Duration, fn func()) { } func main() { + // Parse command line flags + var migrate = flag.Bool("migrate", false, "Run database migrations") + flag.Parse() + port := os.Getenv("FEEDAKA_PORT") + if port == "" { + port = "8080" + } var err error database, err = sql.Open("sqlite3", "feedaka.db") @@ -159,7 +160,18 @@ func main() { } defer database.Close() - err = initDB(database) + // Migration mode + if *migrate { + log.Println("Running database migrations...") + err = db.RunMigrations(database) + if err != nil { + log.Fatalf("Migration failed: %v", err) + } + log.Println("Migrations completed successfully") + return + } + + err = db.ValidateSchemaVersion(database) if err != nil { log.Fatal(err) } @@ -15,6 +15,3 @@ fmt: check: cd frontend && npm run check cd backend && just check - -docker-build: fmt - docker build -t feedaka . |