lysenko/handlers/quotedb/dao.go

87 lines
2.3 KiB
Go

package quotedb
import (
"database/sql"
"errors"
)
// TODO(lupine): Much of the contents here can be replaced with sqlx, in time
type Quote struct {
Id int // Unique among all quotes
QuoteId int // Unique within a particular channel
Channel string
Data string
Author string
CreatedAt NullTime
DeletedAt NullTime
DeletedBy sql.NullString
}
var (
SCHEMA = `
create table if not exists quotes(
id integer not null primary key,
quote_id integer not null,
channel string not null,
data text not null,
author text not null,
created_at timestamp,
deleted_at timestamp,
deleted_by string
);
create index if not exists quotes_by_channel on quotes(channel);
create index if not exists quotes_by_deleted_at on quotes(deleted_at);
create index if not exists quotes_by_data on quotes(data);
create unique index if not exists unique_quotes_by_channel_and_quote_id on quotes(channel, quote_id);
`
quoteCols = `id,quote_id,channel,data,author,created_at,deleted_at,deleted_by`
findQuoteById = `SELECT ` + quoteCols + ` FROM quotes WHERE id = ? LIMIT 1`
findQuoteByQuoteId = `SELECT ` + quoteCols + ` FROM quotes WHERE quote_id = ? AND channel = ? LIMIT 1`
findLastQuote = `SELECT ` + quoteCols + ` FROM quotes WHERE channel = ? ORDER BY quote_id DESC LIMIT 1`
findQuotesByTerm = `SELECT ` + quoteCols + ` FROM quotes WHERE channel = ? AND data LIKE ? ESCAPE '\' ORDER BY quote_id DESC`
findRandomQuote = `SELECT ` + quoteCols + ` FROM quotes WHERE deleted_at IS NULL AND channel = ? ORDER BY RANDOM() LIMIT 1`
insertQuote = `
INSERT INTO quotes (
channel,
quote_id,
data,
author,
created_at
) SELECT
$1,
ifnull(max(quote_id),0)+1,
$2,
$3,
$4
FROM quotes WHERE channel = $1;
`
errInsertIdUnknown = errors.New("SQL: Couldn't determine insert ID")
)
// urgh. Code reuse. Not.
func scanQuotesRows(rows *sql.Rows) (*Quote, error) {
var quote Quote
err := rows.Scan(
&quote.Id, &quote.QuoteId, &quote.Channel, &quote.Data, &quote.Author,
&quote.CreatedAt, &quote.DeletedAt, &quote.DeletedBy,
)
return &quote, err
}
func scanQuotesRow(row *sql.Row) (*Quote, error) {
var quote Quote
err := row.Scan(
&quote.Id, &quote.QuoteId, &quote.Channel, &quote.Data, &quote.Author,
&quote.CreatedAt, &quote.DeletedAt, &quote.DeletedBy)
return &quote, err
}