eintopf.info@v0.13.16/service/group/store_sql.go

// Copyright (C) 2022 The Eintopf authors
//
// This program is free software: you can redistribute it and/or modify
// it under the terms of the GNU Affero General Public License as
// published by the Free Software Foundation, either version 3 of the
// License, or (at your option) any later version.
//
// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Affero General Public License for more details.
//
// You should have received a copy of the GNU Affero General Public License
// along with this program.  If not, see <https://www.gnu.org/licenses/>.

package group

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"eintopf.info/internal/crud"
	"eintopf.info/service/dbmigration"
	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"
)

// NewSqlStore returns a new sql db group store.
func NewSqlStore(db *sqlx.DB, migrationService dbmigration.Service) (*SqlStore, error) {
	store := &SqlStore{db: db, migrationService: migrationService}
	if err := store.runMigrations(context.Background()); err != nil {
		return nil, err
	}
	return store, nil
}

type SqlStore struct {
	db               *sqlx.DB
	migrationService dbmigration.Service
}

func (s *SqlStore) runMigrations(ctx context.Context) error {
	return s.migrationService.RunMigrations(ctx, []dbmigration.Migration{
		dbmigration.NewMigration("createGroupsTable", s.createGroupsTable, nil),
		dbmigration.NewMigration("createGroupsOwnedByTable", s.createGroupsOwnedByTable, nil),
	})
}

func (s *SqlStore) createGroupsTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS groups (
            id varchar(32) NOT NULL PRIMARY KEY UNIQUE,
            deactivated boolean DEFAULT FALSE,
            published boolean DEFAULT FALSE,
            name varchar(64) NOT NULL UNIQUE,
            link varchar(64) DEFAULT "",
            email varchar(64) DEFAULT "",
            description varchar(512),
            image varchar(128)
        );
    `)
	return err
}

func (s *SqlStore) createGroupsOwnedByTable(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS groups_owned_by (
            ID INTEGER PRIMARY KEY AUTOINCREMENT,
            group_id varchar(32),
            user_id varchar(32)
        );
    `)
	return err
}

func (s *SqlStore) Create(ctx context.Context, newGroup *NewGroup) (*Group, error) {
	group := GroupFromNewGroup(newGroup, uuid.New().String())
	_, err := s.db.NamedExecContext(ctx, `
        INSERT INTO groups (
            id,
            published,
            deactivated,
            name,
            link,
            email,
            description,
            image
        ) VALUES (
            :id,
            :published,
            :deactivated,
            :name,
            :link,
            :email,
            :description,
            :image
        )
    `, group)
	if err != nil {
		return nil, err
	}

	err = s.insertOwnedByForGroup(ctx, group)
	if err != nil {
		return nil, err
	}

	return group, nil
}

func (s *SqlStore) insertOwnedByForGroup(ctx context.Context, group *Group) error {
	for _, ownedBy := range group.OwnedBy {
		_, err := s.db.ExecContext(ctx, `
            INSERT INTO groups_owned_by (
                group_id,
                user_id
            ) VALUES (
                $1,
                $2
            )
        `, group.ID, ownedBy)
		if err != nil {
			return err
		}
	}
	return nil
}

func (s *SqlStore) Update(ctx context.Context, group *Group) (*Group, error) {
	_, err := s.db.NamedExecContext(ctx, `
        UPDATE
            groups
        SET
            published=:published,
            deactivated=:deactivated,
            name=:name,
            link=:link,
            email=:email,
            description=:description,
            image=:image
        WHERE
            id=:id
    `, group)
	if err != nil {
		return nil, err
	}

	err = s.deleteOwnedByForGroup(ctx, group.ID)
	if err != nil {
		return nil, err
	}
	err = s.insertOwnedByForGroup(ctx, group)
	if err != nil {
		return nil, err
	}

	return group, err
}

func (s *SqlStore) Delete(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx, "DELETE FROM groups WHERE id = $1", id)
	if err != nil {
		return err
	}

	err = s.deleteOwnedByForGroup(ctx, id)
	if err != nil {
		return err
	}

	return nil
}

func (s *SqlStore) deleteOwnedByForGroup(ctx context.Context, id string) error {
	_, err := s.db.ExecContext(ctx, "DELETE FROM groups_owned_by WHERE group_id = $1", id)
	if err != nil {
		return err
	}
	return nil
}

func (s *SqlStore) FindByID(ctx context.Context, id string) (*Group, error) {
	group := &Group{}
	err := s.db.GetContext(ctx, group, `
        SELECT *
        FROM groups
        WHERE groups.id = $1
    `, id)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}

	s.findOwnedByForGroup(ctx, group)
	if err != nil {
		return nil, err
	}

	return group, nil
}

func (s *SqlStore) findOwnedByForGroup(ctx context.Context, group *Group) error {
	ownedBy := []string{}
	err := s.db.SelectContext(ctx, &ownedBy, `
        SELECT user_id
        FROM groups_owned_by
        WHERE group_id = $1
    `, group.ID)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return err
	}
	group.OwnedBy = ownedBy
	return nil
}

var sortableFields = map[string]string{
	"id":          "id",
	"deactivated": "deactivated",
	"published":   "published",
	"name":        "name",
	"link":        "link",
	"email":       "email",
}

func (s *SqlStore) Find(ctx context.Context, params *crud.FindParams[FindFilters]) ([]*Group, int, error) {
	query := `
        SELECT groups.*
        FROM groups
    `
	joinQuery := ""
	whereStatements := []string{}
	sqlParams := make(map[string]interface{})
	if params != nil {
		if params.Filters != nil {
			if params.Filters.ID != nil {
				whereStatements = append(whereStatements, "groups.id=:id")
				sqlParams["id"] = params.Filters.ID
			}
			if params.Filters.NotID != nil {
				whereStatements = append(whereStatements, "groups.id!=:notID")
				sqlParams["notID"] = params.Filters.NotID
			}
			if params.Filters.Deactivated != nil {
				whereStatements = append(whereStatements, "groups.deactivated=:deactivated")
				sqlParams["deactivated"] = params.Filters.Deactivated
			}
			if params.Filters.Published != nil {
				whereStatements = append(whereStatements, "groups.published=:published")
				sqlParams["published"] = params.Filters.Published
			}
			if params.Filters.Name != nil {
				whereStatements = append(whereStatements, "groups.name=:name")
				sqlParams["name"] = params.Filters.Name
			}
			if params.Filters.LikeName != nil {
				whereStatements = append(whereStatements, "groups.name LIKE :likeName")
				sqlParams["likeName"] = fmt.Sprintf("%%%s%%", *params.Filters.LikeName)
			}
			if params.Filters.Link != nil {
				whereStatements = append(whereStatements, "groups.link=:link")
				sqlParams["link"] = params.Filters.Link
			}
			if params.Filters.Email != nil {
				whereStatements = append(whereStatements, "groups.email=:email")
				sqlParams["email"] = params.Filters.Email
			}
			if params.Filters.Description != nil {
				whereStatements = append(whereStatements, "groups.description=:description")
				sqlParams["Description"] = params.Filters.Description
			}
			if params.Filters.OwnedBy != nil {
				joinQuery += " JOIN groups_owned_by ON groups.id = groups_owned_by.group_id"
				ownedByStatements := make([]string, len(params.Filters.OwnedBy))
				for i, ownedBy := range params.Filters.OwnedBy {
					ownedByRef := fmt.Sprintf("ownedBy%d", i)
					ownedByStatements[i] = fmt.Sprintf("groups_owned_by.user_id=:%s", ownedByRef)
					sqlParams[ownedByRef] = ownedBy
				}
				whereStatements = append(whereStatements, fmt.Sprintf("(%s)", strings.Join(ownedByStatements, " OR ")))
			}

			if joinQuery != "" {
				query += " " + joinQuery
			}
			if len(whereStatements) > 0 {
				query += " WHERE " + strings.Join(whereStatements, " AND ")
			}
			if joinQuery != "" {
				query += " GROUP BY groups.id"
			}
		}

		if params.Sort != "" {
			sort, ok := sortableFields[params.Sort]
			if !ok {
				return nil, 0, fmt.Errorf("find groups: invalid sort field: %s", params.Sort)
			}
			order := "ASC"
			if params.Order == "DESC" {
				order = "DESC"
			}
			query += fmt.Sprintf(" ORDER BY %s %s", sort, order)
		}
		if params.Limit > 0 {
			query += fmt.Sprintf(" LIMIT %d", params.Limit)
		}
		if params.Offset > 0 {
			query += fmt.Sprintf(" OFFSET %d", params.Offset)
		}
	}
	rows, err := s.db.NamedQueryContext(ctx, query, sqlParams)
	if err != nil && err != sql.ErrNoRows {
		return nil, 0, fmt.Errorf("find groups: %s", params.Sort)
	}
	defer rows.Close()
	groups := make([]*Group, 0)
	for rows.Next() {
		group := &Group{}
		rows.StructScan(group)

		groups = append(groups, group)
	}

	for i, group := range groups {
		err = s.findOwnedByForGroup(ctx, group)
		if err != nil {
			return nil, 0, err
		}
		groups[i] = group
	}

	totalQuery := `
        SELECT COUNT(*) as total
        FROM groups
    `
	if len(whereStatements) > 0 {
		if joinQuery != "" {
			totalQuery += " " + joinQuery
		}
		totalQuery += " WHERE " + strings.Join(whereStatements, " AND ")
		if joinQuery != "" {
			totalQuery += " GROUP BY groups.id"
		}
	}
	totalGroups := struct {
		Total int `db:"total"`
	}{}
	rows2, err := s.db.NamedQueryContext(ctx, totalQuery, sqlParams)
	if err != nil && err != sql.ErrNoRows {
		return nil, 0, fmt.Errorf("find groups: total: %s", params.Sort)
	}
	defer rows2.Close()
	if rows2.Next() {
		rows2.StructScan(&totalGroups)
	}

	return groups, totalGroups.Total, nil
}