lucifer/database/sqlite/group-repository.go

package sqlite

import (
	"context"
	"database/sql"

	"golang.org/x/sync/errgroup"

	"git.aiterp.net/lucifer/lucifer/models"
)

type groupRepository struct{}

// GroupRepository is a sqlite datbase repository for the Group model.
var GroupRepository = &groupRepository{}

func (r *groupRepository) FindByID(ctx context.Context, id int) (models.Group, error) {
	group := models.Group{}
	err := db.GetContext(ctx, &group, "SELECT * FROM 'group' WHERE id=?", id)
	if err != nil {
		return models.Group{}, err
	}

	err = db.SelectContext(ctx, &group.Permissions, "SELECT * FROM group_permission WHERE group_id=?", group.ID)
	if err != nil {
		return models.Group{}, err
	}

	return group, err
}

func (r *groupRepository) FindByLight(ctx context.Context, light models.Light) (models.Group, error) {
	const query = `
		SELECT g.id, g.name FROM 'light' l 
		JOIN 'group' g ON g.id = l.group_id 
		WHERE l.id = ?
	`

	group := models.Group{}
	err := db.GetContext(ctx, &group, query, light.ID)
	if err != nil && err != sql.ErrNoRows {
		return models.Group{}, err
	}

	err = db.SelectContext(ctx, &group.Permissions, "SELECT * FROM group_permission WHERE group_id=?", group.ID)
	if err != nil && err != sql.ErrNoRows {
		return models.Group{}, err
	}

	return group, nil
}

func (r *groupRepository) List(ctx context.Context) ([]models.Group, error) {
	groups := make([]models.Group, 0, 16)
	err := db.SelectContext(ctx, &groups, "SELECT * FROM `group`")
	if err != nil {
		return nil, err
	}

	eg, egCtx := errgroup.WithContext(ctx)
	for i := range groups {
		group := &groups[i]

		eg.Go(func() error {
			return db.SelectContext(egCtx, &group.Permissions, "SELECT * FROM group_permission WHERE group_id=?", group.ID)
		})
	}

	err = eg.Wait()
	if err != nil && err != sql.ErrNoRows {
		return nil, err
	}

	return groups, nil
}

func (r *groupRepository) ListByUser(ctx context.Context, user models.User) ([]models.Group, error) {
	const query = `
		SELECT g.id, g.name FROM group_permission AS p
		JOIN 'group' AS g ON p.group_id=g.id
		WHERE p.user_id=? AND p.read=true;
	`

	groups := make([]models.Group, 0, 16)
	err := db.SelectContext(ctx, &groups, query, user.ID)
	if err != nil && err != sql.ErrNoRows {
		return nil, err
	}

	eg, egCtx := errgroup.WithContext(ctx)
	for i := range groups {
		group := &groups[i]

		eg.Go(func() error {
			err := db.SelectContext(egCtx, &group.Permissions, "SELECT * FROM group_permission WHERE group_id=?", group.ID)
			if err != nil && err != sql.ErrNoRows {
				return err
			}

			return nil
		})
	}

	err = eg.Wait()
	if err != nil {
		return nil, err
	}

	return groups, nil
}

func (r *groupRepository) Insert(ctx context.Context, group models.Group) (models.Group, error) {
	res, err := db.NamedExecContext(ctx, "INSERT INTO `group` (name) VALUES(:name)", group)
	if err != nil {
		return models.Group{}, err
	}

	id, err := res.LastInsertId()
	if err != nil {
		return models.Group{}, err
	}

	group.ID = int(id)
	return group, nil
}

func (r *groupRepository) Update(ctx context.Context, group models.Group) error {
	_, err := db.NamedExecContext(ctx, "UPDATE `group` SET name=:name WHERE id=:id", group)
	return err
}

func (r *groupRepository) UpdatePermissions(ctx context.Context, permission models.GroupPermission) error {
	_, err := db.NamedExecContext(ctx, "REPLACE INTO group_permission(group_id, user_id, read, write, 'create', 'delete', manage) VALUES (:group_id, :user_id, :read, :write, :create, :delete, :manage)", permission)
	return err
}

func (r *groupRepository) Remove(ctx context.Context, group models.Group) error {
	_, err := db.NamedExecContext(ctx, "DELETE FROM `group` WHERE id=:id", group)
	return err
}

/*

SELECT g.id, g.name FROM group_permission AS p
JOIN 'group' AS g ON p.group_id=g.id
WHERE p.user_id=? AND p.read=true;

*/