| 1 | package store |
| 2 | |
| 3 | import ( |
| 4 | "database/sql" |
| 5 | "errors" |
| 6 | |
| 7 | "github.com/stevedylandev/andromeda/pkg/auth" |
| 8 | "github.com/stevedylandev/andromeda/pkg/sqlite" |
| 9 | ) |
| 10 | |
| 11 | type Note struct { |
| 12 | ID int64 `json:"id"` |
| 13 | ShortID string `json:"short_id"` |
| 14 | Title string `json:"title"` |
| 15 | Content string `json:"content"` |
| 16 | CreatedAt string `json:"created_at"` |
| 17 | UpdatedAt string `json:"updated_at"` |
| 18 | } |
| 19 | |
| 20 | type NoteInput struct { |
| 21 | Title string `json:"title"` |
| 22 | Content string `json:"content"` |
| 23 | } |
| 24 | |
| 25 | const noteColumns = `id, short_id, title, content, created_at, updated_at` |
| 26 | |
| 27 | const schema = ` |
| 28 | CREATE TABLE IF NOT EXISTS notes ( |
| 29 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 30 | short_id TEXT NOT NULL UNIQUE, |
| 31 | title TEXT NOT NULL, |
| 32 | content TEXT NOT NULL, |
| 33 | created_at TEXT NOT NULL DEFAULT (datetime('now')), |
| 34 | updated_at TEXT NOT NULL DEFAULT (datetime('now')) |
| 35 | ); |
| 36 | ` |
| 37 | |
| 38 | func Open(path string) (*sql.DB, error) { |
| 39 | return sqlite.Open(path, schema) |
| 40 | } |
| 41 | |
| 42 | func scanNote(scanner interface{ Scan(dest ...any) error }) (*Note, error) { |
| 43 | var n Note |
| 44 | err := scanner.Scan(&n.ID, &n.ShortID, &n.Title, &n.Content, &n.CreatedAt, &n.UpdatedAt) |
| 45 | if errors.Is(err, sql.ErrNoRows) { |
| 46 | return nil, nil |
| 47 | } |
| 48 | if err != nil { |
| 49 | return nil, err |
| 50 | } |
| 51 | return &n, nil |
| 52 | } |
| 53 | |
| 54 | func Create(db *sql.DB, title, content string) (*Note, error) { |
| 55 | shortID, err := auth.GenerateShortID(10) |
| 56 | if err != nil { |
| 57 | return nil, err |
| 58 | } |
| 59 | res, err := db.Exec(`INSERT INTO notes (short_id, title, content) VALUES (?, ?, ?)`, shortID, title, content) |
| 60 | if err != nil { |
| 61 | return nil, err |
| 62 | } |
| 63 | id, err := res.LastInsertId() |
| 64 | if err != nil { |
| 65 | return nil, err |
| 66 | } |
| 67 | return scanNote(db.QueryRow(`SELECT `+noteColumns+` FROM notes WHERE id = ?`, id)) |
| 68 | } |
| 69 | |
| 70 | func GetByShortID(db *sql.DB, shortID string) (*Note, error) { |
| 71 | return scanNote(db.QueryRow(`SELECT `+noteColumns+` FROM notes WHERE short_id = ?`, shortID)) |
| 72 | } |
| 73 | |
| 74 | func List(db *sql.DB) ([]Note, error) { |
| 75 | rows, err := db.Query(`SELECT ` + noteColumns + ` FROM notes ORDER BY id DESC`) |
| 76 | if err != nil { |
| 77 | return nil, err |
| 78 | } |
| 79 | defer rows.Close() |
| 80 | var out []Note |
| 81 | for rows.Next() { |
| 82 | var n Note |
| 83 | if err := rows.Scan(&n.ID, &n.ShortID, &n.Title, &n.Content, &n.CreatedAt, &n.UpdatedAt); err != nil { |
| 84 | return nil, err |
| 85 | } |
| 86 | out = append(out, n) |
| 87 | } |
| 88 | return out, rows.Err() |
| 89 | } |
| 90 | |
| 91 | func UpdateByShortID(db *sql.DB, shortID, title, content string) (*Note, error) { |
| 92 | res, err := db.Exec(`UPDATE notes SET title = ?, content = ?, updated_at = datetime('now') WHERE short_id = ?`, title, content, shortID) |
| 93 | if err != nil { |
| 94 | return nil, err |
| 95 | } |
| 96 | n, _ := res.RowsAffected() |
| 97 | if n == 0 { |
| 98 | return nil, nil |
| 99 | } |
| 100 | return GetByShortID(db, shortID) |
| 101 | } |
| 102 | |
| 103 | func DeleteByShortID(db *sql.DB, shortID string) (bool, error) { |
| 104 | res, err := db.Exec(`DELETE FROM notes WHERE short_id = ?`, shortID) |
| 105 | if err != nil { |
| 106 | return false, err |
| 107 | } |
| 108 | n, _ := res.RowsAffected() |
| 109 | return n > 0, nil |
| 110 | } |