fisch/db.go

package main

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

	_ "github.com/mattn/go-sqlite3"
)

func ParseSearchString(search string) (out []any) {
	for strings.Contains(search, "  ") {
		search = strings.ReplaceAll(search, "  ", " ")
	}
	searchStrings := strings.Split(search, " ")
	out = make([]any, len(searchStrings))
	for i, s := range searchStrings {
		out[i] = "%" + s + "%"
	}
	return out
}

type Connection struct {
	DB    *sql.DB
	Error error
}

func Connect() *Connection {
	db, err := sql.Open("sqlite3", "./data/fisch.db")
	if err != nil {
		return &Connection{nil, err}
	}
	return &Connection{db, nil}
}

func (conn *Connection) InitDatabase() error {
	sql, err := os.ReadFile("./data/database.sql")
	if err != nil {
		return err
	}
	_, err = conn.DB.Exec(string(sql))
	return err
}

func (conn *Connection) SearchPart(search string) ([]*Part, error) {
	parsed := ParseSearchString(search)
	if len(parsed) == 0 || (len(parsed) == 1 && len(fmt.Sprintf("%v", parsed[0])) == 2) {
		return []*Part{}, nil
	}

	if conn.Error != nil {
		return nil, conn.Error
	}

	searchPattern := ""

	for i := 0; i < len(parsed); i++ {
		searchPattern += "tags LIKE ?"
		if i < len(parsed)-1 {
			searchPattern += " OR "
		} else {
			searchPattern += " "
		}
	}

	rows, err := conn.DB.Query("select id, name, tags, location, container from parts where "+searchPattern, parsed...)
	if err != nil {
		return nil, err
	}

	parts := make([]*Part, 0)

	for rows.Next() {
		part := Part{}

		var locationId sql.NullInt64
		var containerId sql.NullInt64

		err := rows.Scan(&part.Id, &part.Name, &part.Tags, &locationId, &containerId)
		if err != nil {
			return nil, err
		}

		var location *Location

		if locationId.Valid {
			location, err = conn.QueryLocation(locationId.Int64)
			if err != nil {
				return nil, err
			}
		}

		var container *Container

		if containerId.Valid {
			container, err = conn.QueryContainer(containerId.Int64)
			if err != nil {
				return nil, err
			}
		}

		part.Location = *location
		part.Container = *container
		part.Connection = conn

		parts = append(parts, &part)
	}

	return parts, nil
}

func (conn *Connection) QueryLocations() ([]*Location, error) {
	if conn.Error != nil {
		return nil, conn.Error
	}
	rows, err := conn.DB.Query("select id, parent, name, description from locations")
	if err != nil {
		return nil, err
	}

	locations := make([]*Location, 0)

	for rows.Next() {
		location := Location{}

		err := rows.Scan(&location.Id, &location.Parent, &location.Name, &location.Description)
		if err != nil {
			return nil, err
		}

		location.Connection = conn

		locations = append(locations, &location)
	}

	return locations, nil
}

func (conn *Connection) QueryLocation(id int64) (*Location, error) {
	if conn.Error != nil {
		return nil, conn.Error
	}
	row := conn.DB.QueryRow("select parent, name, description from locations where id=?", id)

	location := Location{}
	location.Id = sql.NullInt64{id, true}

	err := row.Scan(&location.Parent, &location.Name, &location.Description)
	if err != nil {
		return nil, err
	}

	location.Connection = conn

	return &location, nil
}

func (conn *Connection) QueryLocationFullPath(lastChildId int64) (string, error) {
	fullPath := ""

	nextId := lastChildId

	isEnd := false

	for !isEnd {
		location, err := conn.QueryLocation(nextId)
		if err != nil {
			return fullPath, err
		}
		if location.Name.Valid {
			fullPath = location.Name.String + "/" + fullPath
		} else {
			fullPath = fmt.Sprintf("%d/", nextId) + fullPath
		}
		isEnd = !location.Parent.Valid
		if !isEnd {
			nextId = location.Parent.Int64
		}
	}

	if fullPath[len(fullPath)-1] == '/' {
		fullPath = fullPath[0 : len(fullPath)-1]
	}

	return "/" + fullPath, nil
}

func (conn *Connection) QueryContainers() ([]*Container, error) {
	if conn.Error != nil {
		return nil, conn.Error
	}
	rows, err := conn.DB.Query("select id, name from containers")
	if err != nil {
		return nil, err
	}

	containers := make([]*Container, 0)

	for rows.Next() {
		container := Container{}

		err := rows.Scan(&container.Id, &container.Name)
		if err != nil {
			return nil, err
		}

		containers = append(containers, &container)
	}

	return containers, nil
}

func (conn *Connection) QueryContainer(id int64) (*Container, error) {
	if conn.Error != nil {
		return nil, conn.Error
	}
	row := conn.DB.QueryRow("select name from containers where id=?", id)

	container := Container{}
	container.Id = sql.NullInt64{id, true}

	err := row.Scan(&container.Name)
	if err != nil {
		return nil, err
	}

	return &container, nil
}

func (conn *Connection) QueryParts() ([]*Part, error) {
	if conn.Error != nil {
		return nil, conn.Error
	}
	rows, err := conn.DB.Query("select id, name, tags, location, container from parts")
	if err != nil {
		return nil, err
	}

	parts := make([]*Part, 0)

	for rows.Next() {
		part := Part{}

		var locationId sql.NullInt64
		var containerId sql.NullInt64

		err := rows.Scan(&part.Id, &part.Name, &part.Tags, &locationId, &containerId)
		if err != nil {
			return nil, err
		}

		var location *Location

		if locationId.Valid {
			location, err = conn.QueryLocation(locationId.Int64)
			if err != nil {
				return nil, err
			}
		}

		var container *Container

		if containerId.Valid {
			container, err = conn.QueryContainer(containerId.Int64)
			if err != nil {
				return nil, err
			}
		}

		part.Location = *location
		part.Container = *container
		part.Connection = conn

		parts = append(parts, &part)
	}

	return parts, nil
}

func (conn *Connection) QueryPart(id int64) (*Part, error) {
	if conn.Error != nil {
		return nil, conn.Error
	}
	row := conn.DB.QueryRow("select name, tags, location, container from parts where id=?", id)

	part := Part{}
	part.Id = sql.NullInt64{id, true}

	var locationId sql.NullInt64
	var containerId sql.NullInt64

	err := row.Scan(&part.Name, &part.Tags, &locationId, &containerId)
	if err != nil {
		return nil, err
	}

	var location *Location

	if locationId.Valid {
		location, err = conn.QueryLocation(locationId.Int64)
		if err != nil {
			return nil, err
		}
		part.Location = *location
	}

	var container *Container

	if containerId.Valid {
		container, err = conn.QueryContainer(containerId.Int64)
		if err != nil {
			return nil, err
		}
		part.Container = *container
	}
	part.Connection = conn

	return &part, nil
}