eintopf.info@v0.13.16/service/invitation/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 invitation

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

	"github.com/google/uuid"
	"github.com/jmoiron/sqlx"
)

// NewSqlStore returns a new sql db invitation store.
func NewSqlStore(db *sqlx.DB) *SqlStore {
	return &SqlStore{db: db}
}

type SqlStore struct {
	db *sqlx.DB
}

func (s *SqlStore) RunMigrations(ctx context.Context) error {
	_, err := s.db.ExecContext(ctx, `
        CREATE TABLE IF NOT EXISTS invitations (
            id varchar(32) NOT NULL PRIMARY KEY UNIQUE,
            token varchar(128) NOT NULL,
            created_at timestamp,
            created_by VARCHAR(32),
            used_by VARCHAR(32)
        );
    `)
	return err
}

func (s *SqlStore) Create(ctx context.Context, newInvitation *NewInvitation) (*Invitation, error) {
	invitation := InvitationFromNewInvitation(newInvitation, uuid.New().String())
	_, err := s.db.NamedExecContext(ctx, `
        INSERT INTO invitations (
            id,
            token,
            created_at,
            created_by,
            used_by
        ) VALUES (
            :id,
            :token,
            :created_at,
            :created_by,
            :used_by
        )
    `, invitation)
	if err != nil {
		return nil, err
	}

	return invitation, nil
}

func (s *SqlStore) Update(ctx context.Context, invitation *Invitation) (*Invitation, error) {
	_, err := s.db.NamedExecContext(ctx, `
        UPDATE
            invitations
        SET
            token=:token,
            created_at=:created_at,
            created_by=:created_by,
            used_by=:used_by
        WHERE
            id=:id
    `, invitation)
	if err != nil {
		return nil, err
	}

	return invitation, err
}

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

	return nil
}

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

	return invitation, nil
}

var sortableFields = map[string]string{
	"id":        "id",
	"token":     "token",
	"createdAt": "created_at",
	"createdBy": "created_by",
	"usedBy":    "used_by",
}

func (s *SqlStore) Find(ctx context.Context, params *FindParams) ([]Invitation, int, error) {
	query := `SELECT invitations.* FROM invitations`
	whereStatements := []string{}
	sqlParams := make(map[string]interface{})
	if params != nil {
		if params.Filters != nil {
			if params.Filters.ID != nil {
				whereStatements = append(whereStatements, "invitations.id=:id")
				sqlParams["id"] = params.Filters.ID
			}
			if params.Filters.Token != nil {
				whereStatements = append(whereStatements, "invitations.token=:token")
				sqlParams["token"] = params.Filters.Token
			}
			if params.Filters.CreatedAt != nil {
				whereStatements = append(whereStatements, "invitations.created_at=:created_at")
				sqlParams["created_at"] = params.Filters.CreatedAt
			}
			if params.Filters.CreatedBy != nil {
				whereStatements = append(whereStatements, "invitations.created_by=:created_by")
				sqlParams["created_by"] = params.Filters.CreatedBy
			}
			if params.Filters.UsedBy != nil {
				whereStatements = append(whereStatements, "invitations.used_by=:used_by")
				sqlParams["used_by"] = params.Filters.UsedBy
			}
			if len(whereStatements) > 0 {
				query += " WHERE " + strings.Join(whereStatements, " AND ")
			}
		}

		if params.Sort != "" {
			sort, ok := sortableFields[params.Sort]
			if !ok {
				return nil, 0, fmt.Errorf("find invitations: 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 invitations: %s", err)
	}
	defer rows.Close()
	invitations := make([]Invitation, 0)
	for rows.Next() {
		invitation := Invitation{}
		rows.StructScan(&invitation)

		invitations = append(invitations, invitation)
	}

	totalQuery := `SELECT COUNT(*) as total FROM invitations`
	if len(whereStatements) > 0 {
		totalQuery += " WHERE " + strings.Join(whereStatements, " AND ")
	}
	totalInvitations := 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 invitations: %s", err)
	}
	defer rows2.Close()
	if rows2.Next() {
		rows2.StructScan(&totalInvitations)
	}

	return invitations, totalInvitations.Total, nil
}