54 lines
947 B
Go
54 lines
947 B
Go
package sites
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
)
|
|
|
|
const createSitesTable = `CREATE TABLE IF NOT EXISTS sites (
|
|
url TEXT NOT NULL PRIMARY KEY,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
name TEXT NOT NULL
|
|
);`
|
|
|
|
func CreateTable(db *sql.DB) {
|
|
if _, err := db.Exec(createSitesTable); err != nil {
|
|
fmt.Println(fmt.Errorf(err.Error()))
|
|
}
|
|
}
|
|
|
|
type Site struct {
|
|
Created_at string
|
|
Name string
|
|
Url string
|
|
}
|
|
|
|
const upsertQuery = `INSERT INTO sites (url, name) VALUES (?, ?)
|
|
ON CONFLICT (url) DO UPDATE
|
|
SET
|
|
name = excluded.name
|
|
`
|
|
|
|
func (s *Site) Save(db *sql.DB) {
|
|
db.Exec(upsertQuery, s.Url, s.Name)
|
|
}
|
|
|
|
func List(db *sql.DB) []Site {
|
|
rows, err := db.Query(`SELECT url, name, created_at FROM sites ORDER BY created_at DESC`)
|
|
|
|
if err != nil {
|
|
panic(err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
sites := make([]Site, 0)
|
|
|
|
for rows.Next() {
|
|
s := Site{}
|
|
rows.Scan(&s.Url, &s.Name, &s.Created_at)
|
|
sites = append(sites, s)
|
|
}
|
|
|
|
return sites
|
|
}
|