87 lines
2.3 KiB
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(
|
|
"e.Id, "e.QuoteId, "e.Channel, "e.Data, "e.Author,
|
|
"e.CreatedAt, "e.DeletedAt, "e.DeletedBy,
|
|
)
|
|
|
|
return "e, err
|
|
}
|
|
|
|
func scanQuotesRow(row *sql.Row) (*Quote, error) {
|
|
var quote Quote
|
|
|
|
err := row.Scan(
|
|
"e.Id, "e.QuoteId, "e.Channel, "e.Data, "e.Author,
|
|
"e.CreatedAt, "e.DeletedAt, "e.DeletedBy)
|
|
|
|
return "e, err
|
|
}
|