Viewing:
package graph
import (
"context"
"database/sql"
"fmt"
"strconv"
"strings"
"git.allthings.red/~neallred/allredlib/graph/model"
"git.allthings.red/~neallred/allredlib/logging"
)
const (
tCreator = "creator"
tSeries = "series"
tGenre = "genre"
tSubseries = "subseries"
tTitle = "title"
)
func getUser(db *sql.DB, userId string) (*model.User, error) {
query := "SELECT id,username,email FROM users WHERE id=?;"
var user model.User
queryRow := db.QueryRow(query, userId)
if err := queryRow.Err(); err != nil {
return nil, err
}
err := queryRow.Scan(&user.ID, &user.Username, &user.Email)
return &user, err
}
func getUserRoles(ctx context.Context, db *sql.DB, userId string) ([]model.Role, error) {
query := "SELECT r.role FROM role as r INNER JOIN users_role AS ur ON ur.role_id = r.id AND ur.users_id = ?;"
var roles []model.Role
rows, err := db.Query(query, userId)
if err != nil {
return nil, err
}
for rows.Next() {
var role model.Role
err = rows.Scan(&role)
if err != nil {
return nil, err
}
roles = append(roles, role)
}
return roles, nil
}
func getGenres(ctx context.Context, db *sql.DB, first *int, after string) (*model.GenresConnection, error) {
connection := model.GenresConnection{
Edges: []*model.GenreEdge{},
PageInfo: &model.PageInfo{},
}
limit := 10
if first != nil {
limit = max(1, min(*first, maxGenresQuery))
}
lookaheadLimit := limit + 1
var queryBuilder strings.Builder
args := []any{}
queryBuilder.WriteString("SELECT id,genre,enum FROM genre")
if after != "" {
id, err := strconv.Atoi(after)
if err != nil {
logging.FromCtx(ctx).Error("error converting id", "err", err)
return nil, err
}
args = append(args, id)
queryBuilder.WriteString(" WHERE id > ?")
}
args = append(args, uint64(lookaheadLimit))
queryBuilder.WriteString(" LIMIT ?;")
rows, err := db.Query(queryBuilder.String(), args...)
if err != nil {
return nil, err
}
i := 0
for rows.Next() {
if i == limit {
// This iteration is the "extra" result
// that was selected only to see if there was a next page
connection.PageInfo.HasNextPage = true
break
}
genre := model.Genre{}
err = rows.Scan(&genre.ID, &genre.Genre, &genre.Enum)
if err != nil {
return nil, err
}
connection.Edges = append(connection.Edges, &model.GenreEdge{
Cursor: genre.ID,
Node: &genre,
})
i++
}
connection.PageInfo.StartCursor = true
return &connection, nil
}
func getPendingUserRoles(db *sql.DB, userId string) ([]*model.RoleRequest, error) {
query := `SELECT r.role, urr.reason, urr.denied, urr.deny_reason, urr.created
FROM users_role_request AS urr
INNER JOIN role as r ON urr.role_id = r.id").
WHERE urr.users_id = ?;
`
var roleRequests []*model.RoleRequest
rows, err := db.Query(query, userId)
if err != nil {
return nil, err
}
for rows.Next() {
var rr model.RoleRequest
err = rows.Scan(&rr.Role, &rr.Reason, &rr.Denied, &rr.DenyReason, &rr.Created)
if err != nil {
return nil, err
}
roleRequests = append(roleRequests, &rr)
}
return roleRequests, nil
}
const maxBooksQuery = 100
const maxGenresQuery = 10000
func getBooks(ctx context.Context, db *sql.DB, first *int, after string) (*model.BooksConnection, error) {
connection := model.BooksConnection{
Edges: []*model.BookEdge{},
PageInfo: &model.PageInfo{},
}
limit := 10
if first != nil {
limit = max(1, min(*first, maxBooksQuery))
}
lookaheadLimit := limit + 1
var queryBuilder strings.Builder
args := []any{}
queryBuilder.WriteString("SELECT id,title,synopsis,year FROM title")
if after != "" {
bookId, err := strconv.Atoi(after)
if err != nil {
logging.FromCtx(ctx).Error("error converting book id", "err", err)
return nil, err
}
args = append(args, bookId)
queryBuilder.WriteString(" WHERE id > ?")
}
args = append(args, uint64(lookaheadLimit))
queryBuilder.WriteString(" LIMIT ?;")
rows, err := db.Query(queryBuilder.String(), args...)
if err != nil {
return nil, err
}
i := 0
for rows.Next() {
if i == limit {
// This iteration is the "extra" result
// that was selected only to see if there was a next page
connection.PageInfo.HasNextPage = true
break
}
book := model.Book{}
err = rows.Scan(&book.ID, &book.Title, &book.Synopsis, &book.Year)
if err != nil {
return nil, err
}
connection.Edges = append(connection.Edges, &model.BookEdge{
Cursor: book.ID,
Node: &book,
})
i++
}
connection.PageInfo.StartCursor = true
return &connection, nil
}
const maxCreatorsQuery = 1000
func getCreators(ctx context.Context, db *sql.DB, first *int, after string) (*model.CreatorsConnection, error) {
connection := model.CreatorsConnection{
Edges: []*model.CreatorEdge{},
PageInfo: &model.PageInfo{},
}
limit := 10
if first != nil {
limit = max(1, min(*first, maxBooksQuery))
}
lookaheadLimit := limit + 1
var queryBuilder strings.Builder
args := []any{}
queryBuilder.WriteString("SELECT c.id, c.firstname, c.lastname FROM creator c")
if after != "" {
bookId, err := strconv.Atoi(after)
if err != nil {
logging.FromCtx(ctx).Error("error converting creator id", "err", err)
return nil, err
}
args = append(args, bookId)
queryBuilder.WriteString(" WHERE id > ?")
}
args = append(args, uint64(lookaheadLimit))
queryBuilder.WriteString(" LIMIT ?;")
rows, err := db.Query(queryBuilder.String(), args...)
if err != nil {
return nil, err
}
i := 0
for rows.Next() {
if i == limit {
// This iteration is the "extra" result
// that was selected only to see if there was a next page
connection.PageInfo.HasNextPage = true
break
}
creator := model.Creator{}
err = rows.Scan(&creator.ID, &creator.Firstname, &creator.Lastname)
if err != nil {
return nil, err
}
connection.Edges = append(connection.Edges, &model.CreatorEdge{
Cursor: creator.ID,
Node: &creator,
})
i++
}
connection.PageInfo.StartCursor = true
return &connection, nil
}
func getCreatorDetails(ctx context.Context, db *sql.DB, creatorId string) (*model.CreatorDetails, error) {
query := `SELECT
c.id,
c.firstname,
c.lastname,
c.birth,
c.death,
c.bio
FROM creator c
WHERE c.id = ?;
`
creator := model.CreatorDetails{}
row := db.QueryRow(query, creatorId)
if err := row.Err(); err != nil {
logging.FromCtx(ctx).Error("error getting creator", "id", creatorId, "err", err)
return nil, err
}
err := row.Scan(
&creator.ID,
&creator.Firstname,
&creator.Lastname,
&creator.Birth,
&creator.Death,
&creator.Bio,
)
return &creator, err
}
func buildSimpleQuery(cols []string, vals []any, table string) string {
var queryBuilder strings.Builder
queryBuilder.WriteString("INSERT INTO ")
queryBuilder.WriteString(table)
queryBuilder.WriteString("(")
queryBuilder.WriteString(strings.Join(cols, ","))
queryBuilder.WriteString(") VALUES (")
queryBuilder.WriteString(strings.Join(strings.Split(strings.Repeat("?", len(vals)), ""), ","))
queryBuilder.WriteString(") RETURNING id,created,updated;")
query := queryBuilder.String()
return query
}
func doSimpleCreateQuery(ctx context.Context, db *sql.DB, cols []string, vals []any, table string) *sql.Row {
query := buildSimpleQuery(cols, vals, table)
logging.FromCtx(ctx).Info(
"create record",
"sql", query,
"vals", vals,
)
return db.QueryRow(query, vals...)
}
func getCreatorInputNaturalKey(creatorInput model.CreatorCreateInput) string {
if creatorInput.Firstname != nil && *creatorInput.Firstname != "" {
return *creatorInput.Firstname
}
if creatorInput.Lastname != nil && *creatorInput.Lastname != "" {
return *creatorInput.Lastname
}
return ""
}
func newCreator(ctx context.Context, db *sql.DB, newEntity model.CreatorCreateInput) (*model.CreatorDetails, error) {
cols := make([]string, 5)
vals := make([]any, 5)
hasSomeName := false
if newEntity.Firstname != nil && *newEntity.Firstname != "" {
hasSomeName = true
cols = append(cols, "firstname")
vals = append(vals, *newEntity.Firstname)
}
if newEntity.Lastname != nil && *newEntity.Lastname != "" {
hasSomeName = true
cols = append(cols, "lastname")
vals = append(vals, *newEntity.Lastname)
}
if !hasSomeName {
return nil, fmt.Errorf("New author must have a name")
}
if newEntity.Birth != nil && *newEntity.Birth != 0 {
cols = append(cols, "birth")
vals = append(vals, *newEntity.Birth)
}
if newEntity.Death != nil && *newEntity.Death != 0 {
cols = append(cols, "death")
vals = append(vals, *newEntity.Death)
}
if newEntity.Bio != nil && *newEntity.Bio != "" {
cols = append(cols, "bio")
vals = append(vals, *newEntity.Bio)
}
created := model.CreatorDetails{
Firstname: newEntity.Firstname,
Lastname: newEntity.Lastname,
Birth: newEntity.Birth,
Death: newEntity.Death,
}
row := doSimpleCreateQuery(ctx, db, cols, vals, tCreator)
if err := handleRowCreateError(ctx, row, newEntity, getCreatorInputNaturalKey(newEntity)); err != nil {
return nil, err
}
err := row.Scan(
&created.ID, &created.Created, &created.Updated,
)
return &created, err
}
func newSeries(ctx context.Context, db *sql.DB, newEntity model.SeriesInput) (*model.Series, error) {
cols := []string{}
vals := []any{}
if newEntity.Title == nil || *newEntity.Title == "" {
return nil, fmt.Errorf("New series must have a title")
}
cols = append(cols, "title")
vals = append(vals, *newEntity.Title)
if newEntity.Synopsis != nil && *newEntity.Synopsis != "" {
cols = append(cols, "synopsis")
vals = append(vals, *newEntity.Synopsis)
}
if newEntity.TotalBookMembers != nil && *newEntity.TotalBookMembers != 0 {
cols = append(cols, "total_book_members")
vals = append(vals, *newEntity.TotalBookMembers)
}
if newEntity.TotalSubseries != nil && *newEntity.TotalSubseries != 0 {
cols = append(cols, "total_subseries")
vals = append(vals, *newEntity.TotalSubseries)
}
row := doSimpleCreateQuery(ctx, db, cols, vals, tSeries)
if err := handleRowCreateError(ctx, row, newEntity, *newEntity.Title); err != nil {
return nil, err
}
created := model.Series{
Title: newEntity.Title,
Synopsis: newEntity.Synopsis,
TotalBookMembers: newEntity.TotalBookMembers,
TotalSubseries: newEntity.TotalSubseries,
}
err := row.Scan(
&created.ID, &created.Created, &created.Updated,
)
if err != nil {
return nil, err
}
return &created, err
}
func newSubseries(ctx context.Context, db *sql.DB, newEntity model.SubseriesInput) (*model.Subseries, error) {
cols := []string{}
vals := []any{}
if newEntity.Title == nil || *newEntity.Title == "" {
return nil, fmt.Errorf("New series must have a title")
}
cols = append(cols, "title")
vals = append(vals, *newEntity.Title)
if newEntity.Synopsis != nil && *newEntity.Synopsis != "" {
cols = append(cols, "synopsis")
vals = append(vals, *newEntity.Synopsis)
}
if newEntity.TotalBookMembers != nil && *newEntity.TotalBookMembers != 0 {
cols = append(cols, "total_book_members")
vals = append(vals, *newEntity.TotalBookMembers)
}
row := doSimpleCreateQuery(ctx, db, cols, vals, tSubseries)
if err := handleRowCreateError(ctx, row, newEntity, *newEntity.Title); err != nil {
return nil, err
}
created := model.Subseries{
Title: newEntity.Title,
Synopsis: newEntity.Synopsis,
TotalBookMembers: newEntity.TotalBookMembers,
}
err := row.Scan(
&created.ID, &created.Created, &created.Updated,
)
if err != nil {
return nil, err
}
return &created, err
}
func handleRowCreateError(ctx context.Context, row *sql.Row, newEntity any, naturalKey string) error {
if err := row.Err(); err != nil {
logging.FromCtx(ctx).Error(
"error adding record",
"type", fmt.Sprintf("%T", newEntity),
"key", naturalKey,
"err", err,
)
return err
}
return nil
}
func newGenre(ctx context.Context, db *sql.DB, newEntity model.GenreInput) (*model.Genre, error) {
if newEntity.Genre == nil || *newEntity.Genre == "" {
return nil, fmt.Errorf("Must add genre")
}
replacer := strings.NewReplacer(
".", "_",
" ", "_",
"\"", "",
"#", "",
)
enum := replacer.Replace(strings.ToUpper(*newEntity.Genre))
row := db.QueryRow("INSERT INTO genre (genre,enum) VALUES (?,?) RETURNING id;", *newEntity.Genre, enum)
if err := handleRowCreateError(ctx, row, newEntity, *newEntity.Genre); err != nil {
return nil, err
}
created := model.Genre{
Genre: newEntity.Genre,
Enum: &enum,
}
err := row.Scan(&created.ID)
if err != nil {
return nil, err
}
return &created, err
}
func getBookDetails(ctx context.Context, db *sql.DB, bookId string) (*model.BookDetails, error) {
query := `SELECT
t.id,
t.title,
t.year,
t.synopsis,
t.series_part,
t.subseries_part,
group_concat(DISTINCT g.enum),
group_concat(DISTINCT c.firstname || ' ' || c.lastname),
group_concat(DISTINCT c.id)
FROM title t
INNER JOIN creator_title ct ON t.id = ct.title_id
INNER JOIN creator c ON ct.creator_id = c.id
LEFT JOIN title_genre tg ON t.id = tg.title_id
LEFT JOIN genre g ON tg.genre_id = g.id
WHERE t.id = ?
GROUP BY t.id;
`
book := model.BookDetails{}
row := db.QueryRow(query, bookId)
if err := row.Err(); err != nil {
logging.FromCtx(ctx).Error("error getting book", "id", bookId, "err", err)
return nil, err
}
genresStr := new(string)
creatorNamesStr := ""
creatorIdsStr := ""
err := row.Scan(
&book.ID,
&book.Title,
&book.Year,
&book.Synopsis,
&book.SeriesPart,
&book.SubseriesPart,
&genresStr,
&creatorNamesStr,
&creatorIdsStr,
)
if genresStr != nil {
book.Genre = strings.Split(*genresStr, "}")
}
book.CreatorNames = strings.Split(creatorNamesStr, "}")
book.CreatorIds = strings.Split(creatorIdsStr, "}")
return &book, err
}
func newTitle(ctx context.Context, db *sql.DB, newEntity model.TitleInput) (*model.BookDetails, error) {
cols := []string{}
vals := []any{}
if newEntity.Title == nil || *newEntity.Title == "" {
return nil, fmt.Errorf("New book must have a title")
}
if newEntity.CreatorIds == nil || len(newEntity.CreatorIds) == 0 {
return nil, fmt.Errorf("New book must have at least on author")
}
cols = append(cols, "title")
vals = append(vals, *newEntity.Title)
if newEntity.Year != nil {
cols = append(cols, "year")
vals = append(vals, *newEntity.Year)
}
if newEntity.Synopsis != nil && *newEntity.Synopsis != "" {
cols = append(cols, "synopsis")
vals = append(vals, *newEntity.Synopsis)
}
if newEntity.SeriesPart != nil && *newEntity.SeriesPart != "" {
cols = append(cols, "series_part")
vals = append(vals, *newEntity.SeriesPart)
}
if newEntity.SubseriesPart != nil && *newEntity.SubseriesPart != "" {
cols = append(cols, "subseries_part")
vals = append(vals, *newEntity.SubseriesPart)
}
bookQuery := buildSimpleQuery(cols, vals, tTitle)
tx, err := db.Begin()
defer tx.Rollback()
if err != nil {
return nil, err
}
row := tx.QueryRow(bookQuery, vals...)
if err := handleRowCreateError(ctx, row, newEntity, *newEntity.Title); err != nil {
return nil, err
}
created := model.BookDetails{
Title: newEntity.Title,
Year: newEntity.Year,
Synopsis: newEntity.Synopsis,
SeriesPart: newEntity.SeriesPart,
SubseriesPart: newEntity.SubseriesPart,
}
err = row.Scan(
&created.ID, &created.Created, &created.Updated,
)
creatorTitles := []string{}
creatorTitleValues := []any{}
for _, creatorId := range newEntity.CreatorIds {
creatorTitles = append(creatorTitles, "(?,?)")
creatorTitleValues = append(creatorTitleValues, created.ID, creatorId)
}
tx.Exec("INSERT INTO creator_title (title_id, creator_id) VALUES "+strings.Join(creatorTitles, ",")+";", creatorTitleValues...)
if err != nil {
return nil, err
}
if len(newEntity.GenreIds) > 0 {
titleGenres := []string{}
titleGenreValues := []any{}
for _, id := range newEntity.GenreIds {
titleGenres = append(titleGenres, "(?,?)")
titleGenreValues = append(titleGenreValues, created.ID, id)
}
tx.Exec("INSERT INTO title_genre (title_id, creator_id) VALUES "+strings.Join(titleGenres, ",")+";", titleGenreValues...)
if err != nil {
return nil, err
}
}
tx.Commit()
return &created, nil
}