diff options
Diffstat (limited to 'database')
| -rw-r--r-- | database/dns.go | 14 | ||||
| -rw-r--r-- | database/guestbook.go | 50 | ||||
| -rw-r--r-- | database/migrate.go | 19 | ||||
| -rw-r--r-- | database/users.go | 33 |
4 files changed, 115 insertions, 1 deletions
diff --git a/database/dns.go b/database/dns.go index 568653d..fc01347 100644 --- a/database/dns.go +++ b/database/dns.go @@ -20,6 +20,18 @@ type DNSRecord struct { CreatedAt time.Time `json:"created_at"` } +func CountUserDNSRecords(db *sql.DB, userID string) (int, error) { + log.Println("counting dns records for user", userID) + + row := db.QueryRow("SELECT COUNT(*) FROM dns_records WHERE user_id = ?", userID) + var count int + err := row.Scan(&count) + if err != nil { + return 0, err + } + return count, nil +} + func GetUserDNSRecords(db *sql.DB, userID string) ([]DNSRecord, error) { log.Println("getting dns records for user", userID) @@ -43,7 +55,7 @@ func GetUserDNSRecords(db *sql.DB, userID string) ([]DNSRecord, error) { } func SaveDNSRecord(db *sql.DB, record *DNSRecord) (*DNSRecord, error) { - log.Println("saving dns record", record) + log.Println("saving dns record", record.ID) record.CreatedAt = time.Now() _, err := db.Exec("INSERT OR REPLACE INTO dns_records (id, user_id, name, type, content, ttl, internal, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", record.ID, record.UserID, record.Name, record.Type, record.Content, record.TTL, record.Internal, record.CreatedAt) diff --git a/database/guestbook.go b/database/guestbook.go new file mode 100644 index 0000000..2d4d8c9 --- /dev/null +++ b/database/guestbook.go @@ -0,0 +1,50 @@ +package database + +import ( + "database/sql" + "log" + "time" + + _ "github.com/mattn/go-sqlite3" +) + +type GuestbookEntry struct { + ID string `json:"id"` + Name string `json:"name"` + Message string `json:"message"` + CreatedAt time.Time `json:"created_at"` +} + +func GetGuestbookEntries(db *sql.DB) ([]GuestbookEntry, error) { + log.Println("getting guest_book entries") + + rows, err := db.Query("SELECT * FROM guest_book ORDER BY created_at DESC LIMIT 200") + if err != nil { + return nil, err + } + defer rows.Close() + + var entries []GuestbookEntry + for rows.Next() { + var entry GuestbookEntry + err := rows.Scan(&entry.ID, &entry.Name, &entry.Message, &entry.CreatedAt) + if err != nil { + return nil, err + } + entries = append(entries, entry) + } + + return entries, nil +} + +func SaveGuestbookEntry(db *sql.DB, entry *GuestbookEntry) (*GuestbookEntry, error) { + log.Println("saving guest_book entry", entry.ID) + + entry.CreatedAt = time.Now() + _, err := db.Exec("INSERT OR REPLACE INTO guest_book (id, name, message, created_at) VALUES (?, ?, ?, ?)", entry.ID, entry.Name, entry.Message, entry.CreatedAt) + + if err != nil { + return nil, err + } + return entry, nil +} diff --git a/database/migrate.go b/database/migrate.go index de1db4c..1609bc2 100644 --- a/database/migrate.go +++ b/database/migrate.go @@ -102,6 +102,24 @@ func MigrateUserSessions(dbConn *sql.DB) (*sql.DB, error) { return dbConn, nil } +func MigrateGuestBook(dbConn *sql.DB) (*sql.DB, error) { + log.Println("migrating guest_book table") + + _, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS guest_book ( + id TEXT PRIMARY KEY, + name TEXT NOT NULL, + message TEXT NOT NULL, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP + );`) + if err != nil { + return dbConn, err + } + + _, err = dbConn.Exec(`CREATE INDEX IF NOT EXISTS idx_guest_book_created_at ON guest_book (created_at);`) + + return dbConn, nil +} + func Migrate(dbConn *sql.DB) (*sql.DB, error) { log.Println("migrating database") @@ -111,6 +129,7 @@ func Migrate(dbConn *sql.DB) (*sql.DB, error) { MigrateApiKeys, MigrateDomainOwners, MigrateDNSRecords, + MigrateGuestBook, } for _, migration := range migrations { diff --git a/database/users.go b/database/users.go index f9e4436..ab48699 100644 --- a/database/users.go +++ b/database/users.go @@ -33,6 +33,8 @@ type UserSession struct { } func GetUser(dbConn *sql.DB, id string) (*User, error) { + log.Println("getting user", id) + row := dbConn.QueryRow(`SELECT id, mail, username, display_name, created_at FROM users WHERE id = ?;`, id) var user User @@ -46,6 +48,8 @@ func GetUser(dbConn *sql.DB, id string) (*User, error) { } func FindOrSaveUser(dbConn *sql.DB, user *User) (*User, error) { + log.Println("finding or saving user", user.ID) + _, err := dbConn.Exec(`INSERT OR REPLACE INTO users (id, mail, username, display_name) VALUES (?, ?, ?, ?);`, user.ID, user.Mail, user.Username, user.DisplayName) if err != nil { return nil, err @@ -55,6 +59,8 @@ func FindOrSaveUser(dbConn *sql.DB, user *User) (*User, error) { } func MakeUserSessionFor(dbConn *sql.DB, user *User) (*UserSession, error) { + log.Println("making session for user", user.ID) + expireAt := time.Now().Add(time.Hour * 12) _, err := dbConn.Exec(`INSERT OR REPLACE INTO user_sessions (id, user_id, expire_at) VALUES (?, ?, ?);`, user.ID, user.ID, time.Now().Add(ExpiryDuration)) @@ -72,6 +78,8 @@ func MakeUserSessionFor(dbConn *sql.DB, user *User) (*UserSession, error) { } func GetSession(dbConn *sql.DB, sessionId string) (*UserSession, error) { + log.Println("getting session", sessionId) + row := dbConn.QueryRow(`SELECT id, user_id, expire_at FROM user_sessions WHERE id = ?;`, sessionId) var id, userId string @@ -90,6 +98,8 @@ func GetSession(dbConn *sql.DB, sessionId string) (*UserSession, error) { } func DeleteSession(dbConn *sql.DB, sessionId string) error { + log.Println("deleting session", sessionId) + _, err := dbConn.Exec(`DELETE FROM user_sessions WHERE id = ?;`, sessionId) if err != nil { log.Println(err) @@ -126,7 +136,24 @@ func DeleteExpiredSessions(dbConn *sql.DB) error { return nil } +func CountUserAPIKeys(dbConn *sql.DB, userId string) (int, error) { + log.Println("counting api keys for user", userId) + + row := dbConn.QueryRow(`SELECT COUNT(*) FROM api_keys WHERE user_id = ?;`, userId) + + var count int + err := row.Scan(&count) + if err != nil { + log.Println(err) + return 0, err + } + + return count, nil +} + func ListUserAPIKeys(dbConn *sql.DB, userId string) ([]*UserApiKey, error) { + log.Println("listing api keys for user", userId) + rows, err := dbConn.Query(`SELECT key, user_id, created_at FROM api_keys WHERE user_id = ?;`, userId) if err != nil { log.Println(err) @@ -150,6 +177,8 @@ func ListUserAPIKeys(dbConn *sql.DB, userId string) ([]*UserApiKey, error) { } func SaveAPIKey(dbConn *sql.DB, apiKey *UserApiKey) (*UserApiKey, error) { + log.Println("saving api key", apiKey.Key) + _, err := dbConn.Exec(`INSERT OR REPLACE INTO api_keys (key, user_id) VALUES (?, ?);`, apiKey.Key, apiKey.UserID) if err != nil { log.Println(err) @@ -161,6 +190,8 @@ func SaveAPIKey(dbConn *sql.DB, apiKey *UserApiKey) (*UserApiKey, error) { } func GetAPIKey(dbConn *sql.DB, key string) (*UserApiKey, error) { + log.Println("getting api key", key) + row := dbConn.QueryRow(`SELECT key, user_id, created_at FROM api_keys WHERE key = ?;`, key) var apiKey UserApiKey @@ -174,6 +205,8 @@ func GetAPIKey(dbConn *sql.DB, key string) (*UserApiKey, error) { } func DeleteAPIKey(dbConn *sql.DB, key string) error { + log.Println("deleting api key", key) + _, err := dbConn.Exec(`DELETE FROM api_keys WHERE key = ?;`, key) if err != nil { log.Println(err) |