byop-engine/dbmanager/sqlite.go

package dbmanager

import (
	"database/sql"
	"encoding/json"
	"fmt"

	"github.com/google/uuid"
	_ "github.com/mattn/go-sqlite3" // SQLite driver
)

type SQLiteManager struct {
	db *sql.DB
}

// NewSQLiteManager initializes a new SQLiteManager
func NewSQLiteManager(dataSourceName string) (*SQLiteManager, error) {
	db, err := sql.Open("sqlite3", dataSourceName)
	if err != nil {
		return nil, fmt.Errorf("failed to connect to SQLite database: %w", err)
	}

	// Enable foreign key constraints
	_, err = db.Exec("PRAGMA foreign_keys = ON;")
	if err != nil {
		return nil, fmt.Errorf("failed to enable foreign keys: %w", err)
	}

	return &SQLiteManager{db: db}, nil
}

// Connect establishes a connection to the SQLite database
func (m *SQLiteManager) Connect() error {
	// No action needed for SQLite
	return nil
}

// Disconnect closes the connection to the SQLite database
func (m *SQLiteManager) Disconnect() error {
	return m.db.Close()
}

// Create inserts a new record into the specified table
func (m *SQLiteManager) Create(table string, data interface{}) error {
	jsonData, err := json.Marshal(data)
	if err != nil {
		return fmt.Errorf("failed to marshal data: %w", err)
	}

	id := uuid.New().String()

	query := fmt.Sprintf("INSERT INTO %s (id, data) VALUES (?, ?)", table)
	_, err = m.db.Exec(query, id, jsonData)
	if err != nil {
		return fmt.Errorf("failed to insert data into table %s: %w", table, err)
	}
	return nil
}

// GetByID retrieves a record by ID from the specified table
func (m *SQLiteManager) GetByID(table string, id string) (interface{}, error) {
	query := fmt.Sprintf("SELECT data FROM %s WHERE id = ?", table)
	row := m.db.QueryRow(query, id)

	var jsonData string
	if err := row.Scan(&jsonData); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil // No record found
		}
		return nil, fmt.Errorf("failed to retrieve data from table %s: %w", table, err)
	}

	var result map[string]interface{}
	if err := json.Unmarshal([]byte(jsonData), &result); err != nil {
		return nil, fmt.Errorf("failed to unmarshal data: %w", err)
	}

	return result, nil
}

// Update updates an existing record in the specified table
func (m *SQLiteManager) Update(table string, data interface{}) error {
	jsonData, err := json.Marshal(data)
	if err != nil {
		return fmt.Errorf("failed to marshal data: %w", err)
	}

	// Assuming the data contains an "id" field
	id := data.(map[string]interface{})["id"]
	query := fmt.Sprintf("UPDATE %s SET data = ? WHERE id = ?", table)
	_, err = m.db.Exec(query, jsonData, id)
	if err != nil {
		return fmt.Errorf("failed to update data in table %s: %w", table, err)
	}

	return nil
}

// Delete removes a record by ID from the specified table
func (m *SQLiteManager) Delete(table string, id string) error {
	query := fmt.Sprintf("DELETE FROM %s WHERE id = ?", table)
	_, err := m.db.Exec(query, id)
	if err != nil {
		return fmt.Errorf("failed to delete data from table %s: %w", table, err)
	}

	return nil
}

// List retrieves all records from the specified table
func (m *SQLiteManager) List(table string, filter map[string]interface{}) ([]interface{}, error) {
	query := fmt.Sprintf("SELECT data FROM %s", table)
	rows, err := m.db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("failed to list data from table %s: %w", table, err)
	}
	defer rows.Close()

	var results []interface{}
	for rows.Next() {
		var jsonData string
		if err := rows.Scan(&jsonData); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}

		var result map[string]interface{}
		if err := json.Unmarshal([]byte(jsonData), &result); err != nil {
			return nil, fmt.Errorf("failed to unmarshal data: %w", err)
		}

		results = append(results, result)
	}

	return results, nil
}

// Exec executes a raw SQL query
func (m *SQLiteManager) Exec(query string, args ...interface{}) (interface{}, error) {
	result, err := m.db.Exec(query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to execute query: %w", err)
	}
	return result, nil
}

// Query executes a raw SQL query and returns the rows
func (m *SQLiteManager) Query(query string, args ...interface{}) (*sql.Rows, error) {
	rows, err := m.db.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to execute query: %w", err)
	}
	return rows, nil
}

// Close closes the database connection
func (m *SQLiteManager) Close() error {
	return m.db.Close()
}