playback-device-server/data/device_database.go

package data

import (
	"database/sql"
	"fmt"
	"path/filepath"

	gonanoid "github.com/matoous/go-nanoid"
	_ "github.com/mattn/go-sqlite3"
)

type DeviceDatabase struct {
	Connection        *sql.DB
	databaseDirectory string
}

func (db *DeviceDatabase) Initialize() error {
	connection, error := sql.Open("sqlite3", filepath.Join(db.databaseDirectory, "devices.db"))
	if error != nil {
		return error
	}
	db.Connection = connection

	_, error = db.Connection.Exec(`CREATE TABLE IF NOT EXISTS devices (
        id TEXT PRIMARY KEY,
        device_name TEXT,
		description TEXT
    )`)
	if error != nil {
		return fmt.Errorf("error creating devices table: %s", error)
	}

	_, error = db.Connection.Exec(`CREATE TABLE IF NOT EXISTS integrations (
        id TEXT PRIMARY KEY,
        name TEXT UNIQUE,
        token TEXT
    )`)
	if error != nil {
		return fmt.Errorf("error creating device sessions table: %s", error)
	}
	return nil
}

func (db *DeviceDatabase) Close() error {
	return db.Connection.Close()
}
func (db *DeviceDatabase) CreateDevice(device_name string, description string) (string, error) {
	deviceID, err := gonanoid.Nanoid(8)
	if err != nil {
		return "", err
	}
	_, err = db.Connection.Exec("INSERT INTO devices (id, device_name, description) VALUES (?, ?, ?)", deviceID, device_name, description)
	return deviceID, err
}

func (db *DeviceDatabase) GetDeviceById(id string) (*PlaybackDevice, error) {
	var device PlaybackDevice
	err := db.Connection.QueryRow("SELECT id, device_name, description FROM devices WHERE id = ?", id).Scan(&device.ID, &device.Name, &device.Description)
	if err != nil {
		return nil, err
	}
	return &device, nil
}

func (db *DeviceDatabase) DeviceIdExists(id string) (bool, error) {
	var exists bool
	err := db.Connection.QueryRow("SELECT EXISTS(SELECT 1 FROM devices WHERE id = ?)", id).Scan(&exists)
	if err != nil {
		return false, err
	}
	return exists, nil
}

func (db *DeviceDatabase) UpdateDevice(device *PlaybackDevice) error {
	_, err := db.Connection.Exec("UPDATE devices SET name = ?, description = ? WHERE id = ?", device.Name, device.Description, device.ID)
	return err
}

func (db *DeviceDatabase) GetDevices() (*[]PlaybackDevice, error) {
	var devices []PlaybackDevice

	rows, err := db.Connection.Query("SELECT id, device_name, description FROM devices")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var device PlaybackDevice
		err := rows.Scan(&device.ID, &device.Name, &device.Description)
		if err != nil {
			if err == sql.ErrNoRows {
				return nil, nil
			}
			return nil, err
		}
		devices = append(devices, device)
	}
	return &devices, nil
}

func (db *DeviceDatabase) DeleteDevice(ID string) error {
	_, err := db.Connection.Exec("DELETE FROM devices WHERE id = ?", ID)
	return err
}

func (db *DeviceDatabase) CreateIntegration(name, token string) (string, error) {
	id, err := gonanoid.Nanoid(8)
	if err != nil {
		return "", err
	}

	_, err = db.Connection.Exec("INSERT INTO integrations (id, name, token) VALUES (?, ?, ?)", id, name, token)
	if err != nil {
		return "", err
	}
	return id, nil
}

func (db *DeviceDatabase) IntegrationIdExists(id string) (bool, error) {
	var exists bool
	err := db.Connection.QueryRow("SELECT EXISTS(SELECT 1 FROM integrations WHERE id = ?)", id).Scan(&exists)
	if err != nil {
		return false, err
	}
	return exists, nil
}

func (db *DeviceDatabase) GetIntegration(id string) (*Integration, error) {
	var integration Integration
	err := db.Connection.QueryRow("SELECT id, name FROM integrations WHERE id = ?", id).Scan(&integration.ID, &integration.Name)
	if err != nil {
		return nil, err
	}
	return &integration, nil
}

func (db *DeviceDatabase) GetIntegrations() ([]Integration, error) {
	var integrations []Integration

	rows, err := db.Connection.Query("SELECT id, name FROM integrations")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var integration Integration
		err := rows.Scan(&integration.ID, &integration.Name)
		if err != nil {
			if err == sql.ErrNoRows {
				return nil, nil
			}
			return nil, err
		}
		integrations = append(integrations, integration)
	}
	return integrations, nil
}

func (db *DeviceDatabase) GetIntegrationByToken(token string) (*Integration, error) {
	exists, err := db.IntegrationTokenExists(token)
	if err != nil {
		return nil, err
	}
	if !exists {
		return nil, nil
	}
	var integration Integration
	err = db.Connection.QueryRow("SELECT id, name FROM integrations WHERE token = ?", token).Scan(&integration.ID, &integration.Name)
	if err != nil {
		return nil, err
	}
	return &integration, nil
}

func (db *DeviceDatabase) DeleteIntegration(id string) error {
	_, err := db.Connection.Exec("DELETE FROM integrations WHERE id = ?", id)
	return err
}

func (db *DeviceDatabase) IntegrationNameExists(name string) (bool, error) {
	var exists bool
	err := db.Connection.QueryRow("SELECT EXISTS(SELECT 1 FROM integrations WHERE name = ?)", name).Scan(&exists)
	if err != nil {
		return false, err
	}
	return exists, nil
}

func (db *DeviceDatabase) IntegrationTokenExists(token string) (bool, error) {
	var exists bool
	err := db.Connection.QueryRow("SELECT EXISTS(SELECT 1 FROM integrations WHERE token = ?)", token).Scan(&exists)
	if err != nil {
		return false, err
	}
	return exists, nil
}

func (db *DeviceDatabase) SetDirectory(directory string) {
	db.databaseDirectory = directory
}