From b1775c4408bb00803eba321aa66ab92d6ba45580 Mon Sep 17 00:00:00 2001 From: Elizabeth Hunt Date: Sat, 17 Aug 2024 18:29:33 -0400 Subject: kennel (#13) Reviewed-on: https://git.hatecomputers.club/hatecomputers/hatecomputers.club/pulls/13 Co-authored-by: Elizabeth Hunt Co-committed-by: Elizabeth Hunt --- database/kennel.go | 159 ++++++++++++++++++++++++++++++++++++++++++++++++++++ database/migrate.go | 21 +++++++ 2 files changed, 180 insertions(+) create mode 100644 database/kennel.go (limited to 'database') diff --git a/database/kennel.go b/database/kennel.go new file mode 100644 index 0000000..91525db --- /dev/null +++ b/database/kennel.go @@ -0,0 +1,159 @@ +package database + +import ( + "database/sql" + _ "github.com/mattn/go-sqlite3" + "log" + "time" +) + +type KennelCat struct { + ID string `json:"id"` + UserID string `json:"user_id"` + Name string `json:"name"` + Link string `json:"link"` + Description string `json:"description"` + Spritesheet string `json:"spritesheet"` + CreatedAt time.Time `json:"created_at"` +} + +type KennelState struct { + At time.Time `json:"at"` + EncodedState string `json:"state"` +} + +func CountUserKennelCats(db *sql.DB, userID string) (int, error) { + log.Println("counting kennel cats for user", userID) + + row := db.QueryRow("SELECT COUNT(*) FROM kennel_cat WHERE user_id = ?", userID) + var count int + err := row.Scan(&count) + if err != nil { + return 0, err + } + return count, nil +} + +func GetUserKennelCats(db *sql.DB, userID string) ([]KennelCat, error) { + log.Println("getting kennel cats for user", userID) + + rows, err := db.Query("SELECT * FROM kennel_cat WHERE user_id = ?", userID) + if err != nil { + return nil, err + } + defer rows.Close() + + var cats []KennelCat + for rows.Next() { + var cat KennelCat + err := rows.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt) + if err != nil { + return nil, err + } + cats = append(cats, cat) + } + + return cats, nil +} + +func SaveKennelCat(db *sql.DB, cat *KennelCat) (*KennelCat, error) { + log.Println("saving kennel cat", cat.ID) + + if (cat.CreatedAt == time.Time{}) { + cat.CreatedAt = time.Now() + } + + _, err := db.Exec("INSERT OR REPLACE INTO kennel_cat (id, user_id, name, link, description, spritesheet, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)", cat.ID, cat.UserID, cat.Name, cat.Link, cat.Description, cat.Spritesheet, cat.CreatedAt) + + if err != nil { + return nil, err + } + return cat, nil +} + +func GetKennelCat(db *sql.DB, catID string) (*KennelCat, error) { + log.Println("getting kennel cat", catID) + + row := db.QueryRow("SELECT * FROM kennel_cat WHERE id = ?", catID) + var cat KennelCat + err := row.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt) + if err != nil { + return nil, err + } + return &cat, nil +} + +func DeleteKennelCat(db *sql.DB, catID string) error { + log.Println("deleting kennel cat", catID) + + _, err := db.Exec("DELETE FROM kennel_cat WHERE id = ?", catID) + if err != nil { + return err + } + return nil +} + +func GetRandomKennelCat(dbConn *sql.DB) (*KennelCat, error) { + log.Println("getting random kennel cat") + + row := dbConn.QueryRow("SELECT * FROM kennel_cat ORDER BY RANDOM() LIMIT 1") + var cat KennelCat + err := row.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt) + if err != nil { + return nil, err + } + return &cat, nil +} + +func GetNextKennelCat(dbConn *sql.DB, lastID string, next bool) (*KennelCat, error) { + log.Println("getting next kennel cat") + + operation := ">" + sorted := "ASC" + if !next { + operation = "<" + sorted = "DESC" + } + + row := dbConn.QueryRow("SELECT * FROM kennel_cat WHERE id "+operation+" ? ORDER BY id "+sorted+" LIMIT 1", lastID) + + var cat KennelCat + err := row.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt) + if err != nil { + if next { + // loop "back" to the first in the ring + row = dbConn.QueryRow("SELECT * FROM kennel_cat ORDER BY id ASC LIMIT 1") + } else { + // loop "forward" to the first in the ring + row = dbConn.QueryRow("SELECT * FROM kennel_cat ORDER BY id DESC LIMIT 1") + } + err = row.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt) + } + if err != nil { + return nil, err + } + + return &cat, nil +} + +func GetKennel(dbConn *sql.DB) ([]KennelCat, error) { + log.Println("getting kennel") + + rows, err := dbConn.Query("SELECT * FROM kennel_cat") + if err != nil { + return nil, err + } + defer rows.Close() + + var cats []KennelCat + for rows.Next() { + var cat KennelCat + err := rows.Scan(&cat.ID, &cat.Name, &cat.UserID, &cat.Link, &cat.Description, &cat.Spritesheet, &cat.CreatedAt) + if err != nil { + return nil, err + } + cats = append(cats, cat) + } + + return cats, nil +} diff --git a/database/migrate.go b/database/migrate.go index e9e21b7..0c8318c 100644 --- a/database/migrate.go +++ b/database/migrate.go @@ -162,6 +162,26 @@ func MigrateProfiles(dbConn *sql.DB) (*sql.DB, error) { return dbConn, nil } +func MigrateKennel(dbConn *sql.DB) (*sql.DB, error) { + log.Println("migrating kennel tables") + + _, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS kennel_cat ( + id TEXT PRIMARY KEY, + name TEXT NOT NULL, + user_id INTEGER NOT NULL, + link TEXT NOT NULL, + description TEXT NOT NULL, + spritesheet TEXT NOT NULL, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE + );`) + if err != nil { + return dbConn, err + } + + return dbConn, nil +} + func Migrate(dbConn *sql.DB) (*sql.DB, error) { log.Println("migrating database") @@ -173,6 +193,7 @@ func Migrate(dbConn *sql.DB) (*sql.DB, error) { MigrateDNSRecords, MigrateGuestBook, MigrateProfiles, + MigrateKennel, } for _, migration := range migrations { -- cgit v1.2.3-70-g09d2