128 lines
2.9 KiB
Go
128 lines
2.9 KiB
Go
package quotedb
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
_ "github.com/mattn/go-sqlite3"
|
|
"log"
|
|
"strings"
|
|
"time"
|
|
)
|
|
|
|
type QuoteDB struct {
|
|
DB *sql.DB
|
|
}
|
|
|
|
func New(filename string) (*QuoteDB, error) {
|
|
dbh, err := sql.Open("sqlite3", filename)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
if _, err := dbh.Exec(SCHEMA); err != nil {
|
|
dbh.Close()
|
|
return nil, err
|
|
}
|
|
|
|
return &QuoteDB{DB: dbh}, nil
|
|
}
|
|
|
|
func (q *QuoteDB) AddQuote(channel string, data string, author string) (*Quote, error) {
|
|
now := time.Now().UTC()
|
|
|
|
result, insertErr := q.DB.Exec(insertQuote, channel, data, author, now)
|
|
if insertErr != nil {
|
|
log.Printf("Error inserting a quote:", insertErr)
|
|
return nil, insertErr
|
|
}
|
|
|
|
id, idErr := result.LastInsertId()
|
|
if idErr != nil {
|
|
log.Printf("InsertIdUnknown while adding a quote")
|
|
return nil, errInsertIdUnknown
|
|
}
|
|
|
|
return q.findQuoteById(id)
|
|
}
|
|
|
|
func (q *QuoteDB) findQuoteById(id int64) (*Quote, error) {
|
|
quote, err := scanQuotesRow(q.DB.QueryRow(findQuoteById, id))
|
|
|
|
if err != nil && err != sql.ErrNoRows {
|
|
log.Println("Encountered", err, "looking up quote with rowid", id)
|
|
}
|
|
|
|
return quote, err
|
|
}
|
|
|
|
func (q *QuoteDB) FindQuoteByQuoteId(id int, channel string) (*Quote, error) {
|
|
quote, err := scanQuotesRow(q.DB.QueryRow(findQuoteByQuoteId, id, channel))
|
|
|
|
if err != nil && err != sql.ErrNoRows {
|
|
log.Println("Encountered", err, "looking up quote", id, "on channel", channel)
|
|
}
|
|
|
|
return quote, err
|
|
}
|
|
|
|
func (q *QuoteDB) FindRandomQuote(channel string) (*Quote, error) {
|
|
quote, err := scanQuotesRow(q.DB.QueryRow(findRandomQuote, channel))
|
|
|
|
if err != nil && err != sql.ErrNoRows {
|
|
log.Println("Encountered", err, "looking up random quote on channel", channel)
|
|
}
|
|
|
|
return quote, err
|
|
|
|
}
|
|
|
|
func (q *QuoteDB) FindLastQuote(channel string) (*Quote, error) {
|
|
quote, err := scanQuotesRow(q.DB.QueryRow(findLastQuote, channel))
|
|
|
|
if err != nil && err != sql.ErrNoRows {
|
|
log.Println("Encountered", err, "looking up last quote on channel", channel)
|
|
}
|
|
|
|
return quote, err
|
|
}
|
|
|
|
func escapeLike(text string) string {
|
|
text = strings.ReplaceAll(text, `\`, ``)
|
|
text = strings.ReplaceAll(text, `%`, `\%`)
|
|
text = strings.ReplaceAll(text, `_`, `\_`)
|
|
|
|
return text
|
|
}
|
|
|
|
func (q QuoteDB) FindQuotesByString(search string, channel string) ([]*Quote, error) {
|
|
term := fmt.Sprintf("%%%s%%", escapeLike(search))
|
|
|
|
rows, err := q.DB.Query(findQuotesByTerm, channel, term)
|
|
|
|
if err != nil {
|
|
log.Println("Encountered", err, "searching for quotes on channel", channel, "with term", term)
|
|
return nil, err
|
|
}
|
|
|
|
quotes := make([]*Quote, 0)
|
|
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
quote, err := scanQuotesRows(rows)
|
|
if err != nil {
|
|
log.Println("Encountered", err, "searching for quotes on channel", channel, "with term", term)
|
|
return nil, err
|
|
}
|
|
|
|
quotes = append(quotes, quote)
|
|
}
|
|
|
|
err = rows.Err()
|
|
if err != nil {
|
|
log.Println("Encountered", err, "searching for quotes on channel", channel, "with term", term)
|
|
return nil, err
|
|
}
|
|
|
|
return quotes, nil
|
|
}
|