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

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 place 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("createPlacesTable", s.createPlacesTable, nil),
		dbmigration.NewMigration("createPlacesOwnedByTable", s.createPlacesOwnedByTable, nil),
	})
}

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

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

func (s *SqlStore) Create(ctx context.Context, newPlace *NewPlace) (*Place, error) {
	place := PlaceFromNewPlace(newPlace, uuid.New().String())
	_, err := s.db.NamedExecContext(ctx, `
        INSERT INTO places (
            id,
            published,
            deactivated,
            name,
            description,
            email,
            image,
            link,
            address,
            lat,
            lng
        ) VALUES (
            :id,
            :published,
            :deactivated,
            :name,
            :description,
            :email,
            :image,
            :link,
            :address,
            :lat,
            :lng
        )
    `, place)
	if err != nil {
		return nil, err
	}

	err = s.insertOwnedByForPlace(ctx, place)
	if err != nil {
		return nil, err
	}

	return place, nil
}

func (s *SqlStore) insertOwnedByForPlace(ctx context.Context, place *Place) error {
	for _, ownedBy := range place.OwnedBy {
		_, err := s.db.ExecContext(ctx, `
            INSERT INTO places_owned_by (
                place_id,
                user_id
            ) VALUES (
                $1,
                $2
            )
        `, place.ID, ownedBy)
		if err != nil {
			return err
		}
	}
	return nil
}

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

	err = s.deleteOwnedByForPlace(ctx, place.ID)
	if err != nil {
		return nil, err
	}
	err = s.insertOwnedByForPlace(ctx, place)
	if err != nil {
		return nil, err
	}

	return place, err
}

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

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

	return nil
}

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

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

	s.findOwnedByForPlace(ctx, place)
	if err != nil {
		return nil, err
	}

	return place, nil
}

func (s *SqlStore) findOwnedByForPlace(ctx context.Context, place *Place) error {
	ownedBy := []string{}
	err := s.db.SelectContext(ctx, &ownedBy, `
        SELECT user_id
        FROM places_owned_by
        WHERE place_id = $1
    `, place.ID)
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return err
	}
	place.OwnedBy = ownedBy
	return nil
}

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

func (s *SqlStore) Find(ctx context.Context, params *crud.FindParams[FindFilters]) ([]*Place, int, error) {
	query := `
        SELECT places.*
        FROM places
        `
	joinQuery := ""
	whereStatements := []string{}
	sqlParams := make(map[string]interface{})
	if params != nil {
		if params.Filters != nil {
			if params.Filters.ID != nil {
				whereStatements = append(whereStatements, "places.id=:id")
				sqlParams["id"] = params.Filters.ID
			}
			if params.Filters.NotID != nil {
				whereStatements = append(whereStatements, "places.id!=:notID")
				sqlParams["notID"] = params.Filters.NotID
			}
			if params.Filters.Deactivated != nil {
				whereStatements = append(whereStatements, "places.deactivated=:deactivated")
				sqlParams["deactivated"] = params.Filters.Deactivated
			}
			if params.Filters.Published != nil {
				whereStatements = append(whereStatements, "places.published=:published")
				sqlParams["published"] = params.Filters.Published
			}
			if params.Filters.Name != nil {
				whereStatements = append(whereStatements, "places.name=:name")
				sqlParams["name"] = params.Filters.Name
			}
			if params.Filters.LikeName != nil {
				whereStatements = append(whereStatements, "places.name LIKE :likeName")
				sqlParams["likeName"] = fmt.Sprintf("%%%s%%", *params.Filters.LikeName)
			}
			if params.Filters.Description != nil {
				whereStatements = append(whereStatements, "places.description=:description")
				sqlParams["Description"] = params.Filters.Description
			}
			if params.Filters.OwnedBy != nil {
				joinQuery = " LEFT JOIN places_owned_by ON places.id = places_owned_by.place_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("places_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 places.id"
			}
		}

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

		places = append(places, place)
	}

	for i, place := range places {
		err = s.findOwnedByForPlace(ctx, place)
		if err != nil {
			return nil, 0, err
		}
		places[i] = place
	}

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

	return places, totalPlaces.Total, nil
}