speed-tester/database/database.go

package database

import (
	"context"
	"database/sql"
	"log"
	"time"

	"gitea.zokki.net/zokki/speed-tester/models"
	_ "github.com/mattn/go-sqlite3" // Import the SQLite driver
	"github.com/showwin/speedtest-go/speedtest"
)

var DB *sql.DB

func init() {
	var err error
	DB, err = sql.Open("sqlite3", "./speed-data.db")
	if err != nil {
		panic(err)
	}

	if err := DB.Ping(); err != nil {
		panic(err)
	}

	DB.SetConnMaxLifetime(time.Minute * 1)
	DB.SetConnMaxIdleTime(time.Minute * 1)
	DB.SetMaxOpenConns(1)
	DB.SetMaxIdleConns(1)

	_, err = DB.Exec(`CREATE TABLE IF NOT EXISTS error (
		ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
		CreationDate TEXT NOT NULL DEFAULT current_timestamp,
		Detail TEXT NOT NULL,
		Error TEXT NOT NULL
	);`)
	if err != nil {
		panic(err)
	}

	_, err = DB.Exec(`CREATE TABLE IF NOT EXISTS internetData (
		ID INTEGER PRIMARY KEY AUTOINCREMENT,
		CreationDate TEXT NOT NULL DEFAULT current_timestamp,
		URL TEXT,
		Lat TEXT,
		Lon TEXT,
		Name TEXT,
		Country TEXT,
		Sponsor TEXT,
		Host TEXT,
		Distance REAL,
		Latency INTEGER,
		MaxLatency INTEGER,
		MinLatency INTEGER,
		Jitter INTEGER,
		DLSpeed REAL,
		ULSpeed REAL,
		TestDuration INTEGER,
		PacketLoss REAL
	);`)
	if CheckError("create speedData table", err) {
		panic("int error")
	}
}

func CheckError(detail string, err error) bool {
	if err == nil {
		return false
	}
	defer log.Println("[ERROR]", detail, err)
	if DB == nil {
		return false
	}

	_, dbErr := DB.Exec("INSERT INTO error (Detail, Error) VALUES(?, ?);", detail, err.Error())
	if dbErr != nil {
		log.Println("[ERROR]", "after "+detail, dbErr)
		return true
	}
	return false
}

func InsertServer(server *speedtest.Server) {
	_, err := DB.Exec(`INSERT INTO internetData (
		URL,
		Lat,
		Lon,
		Name,
		Country,
		Sponsor,
		Host,
		Distance,
		Latency,
		MaxLatency,
		MinLatency,
		Jitter,
		DLSpeed,
		ULSpeed,
		TestDuration,
		PacketLoss
	) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);`,
		server.URL,
		server.Lat,
		server.Lon,
		server.Name,
		server.Country,
		server.Sponsor,
		server.Host,
		server.Distance,
		server.Latency,
		server.MaxLatency,
		server.MinLatency,
		server.Jitter,
		server.DLSpeed,
		server.ULSpeed,
		server.TestDuration.Total,
		server.PacketLoss.LossPercent(),
	)
	CheckError("insert into speedData", err)
}

func GetAllData(ctx context.Context, from string, to string) ([]models.InternetData, error) {
	whereFilter, whereValues := whereStatement("CreationDate", from, to)
	rows, err := DB.QueryContext(ctx, `SELECT
		ID,
		CreationDate,
		URL,
		Lat,
		Lon,
		Name,
		Country,
		Sponsor,
		Host,
		Distance,
		Latency,
		MaxLatency,
		MinLatency,
		Jitter,
		DLSpeed,
		ULSpeed,
		TestDuration,
		PacketLoss
	FROM internetData `+whereFilter, whereValues...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	data := []models.InternetData{}
	for rows.Next() {
		internetData := models.InternetData{}

		err = rows.Scan(&internetData.ID, &internetData.CreationDate, &internetData.URL, &internetData.Lat, &internetData.Lon, &internetData.Name, &internetData.Country, &internetData.Sponsor, &internetData.Host, &internetData.Distance, &internetData.Latency, &internetData.MaxLatency, &internetData.MinLatency, &internetData.Jitter, &internetData.DLSpeed, &internetData.ULSpeed, &internetData.TestDuration, &internetData.PacketLoss)
		if err != nil {
			return nil, err
		}

		data = append(data, internetData)
	}

	return data, nil
}

func GetSpeedData(ctx context.Context, from string, to string) ([]models.InternetData, error) {
	whereFilter, whereValues := whereStatement("CreationDate", from, to)
	rows, err := DB.QueryContext(ctx, `SELECT
		ID,
		CreationDate,
		DLSpeed,
		ULSpeed
	FROM internetData `+whereFilter, whereValues...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	data := []models.InternetData{}
	for rows.Next() {
		internetData := models.InternetData{}

		err = rows.Scan(&internetData.ID, &internetData.CreationDate, &internetData.DLSpeed, &internetData.ULSpeed)
		if err != nil {
			return nil, err
		}

		data = append(data, internetData)
	}

	return data, nil
}

func GetLatencyData(ctx context.Context, from string, to string) ([]models.InternetData, error) {
	whereFilter, whereValues := whereStatement("CreationDate", from, to)
	rows, err := DB.QueryContext(ctx, `SELECT
		ID,
		CreationDate,
		Latency,
		MaxLatency,
		MinLatency,
		Jitter
	FROM internetData `+whereFilter, whereValues...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	data := []models.InternetData{}
	for rows.Next() {
		internetData := models.InternetData{}

		err = rows.Scan(&internetData.ID, &internetData.CreationDate, &internetData.Latency, &internetData.MaxLatency, &internetData.MinLatency, &internetData.Jitter)
		if err != nil {
			return nil, err
		}

		data = append(data, internetData)
	}

	return data, nil
}

func GetDurationData(ctx context.Context, from string, to string) ([]models.InternetData, error) {
	whereFilter, whereValues := whereStatement("CreationDate", from, to)
	rows, err := DB.QueryContext(ctx, `SELECT
		ID,
		CreationDate,
		TestDuration
	FROM internetData `+whereFilter, whereValues...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	data := []models.InternetData{}
	for rows.Next() {
		internetData := models.InternetData{}

		err = rows.Scan(&internetData.ID, &internetData.CreationDate, &internetData.TestDuration)
		if err != nil {
			return nil, err
		}

		data = append(data, internetData)
	}

	return data, nil
}

func GetAllErrors(ctx context.Context) ([]models.Error, error) {
	rows, err := DB.QueryContext(ctx, `SELECT
		ID,
		CreationDate,
		Detail,
		Error
	FROM error;`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	data := []models.Error{}
	for rows.Next() {
		error := models.Error{}

		err = rows.Scan(&error.ID, &error.CreationDate, &error.Detail, &error.Error)
		if err != nil {
			return nil, err
		}

		data = append(data, error)
	}

	return data, nil
}