rpi-sensors/database.go

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/Freeaqingme/golang-sql-driver-mysql"
)

var ClimateDb *sql.DB

func InitDb() error {
	db, err := sql.Open("mysql", "admin:sekna123jk@tcp(127.0.0.1:3306)/climate")
	if err != nil {
		return fmt.Errorf("error connecting to database: %w", err)
	}
	ClimateDb = db
	return nil
}

func CloseDb() {
	if ClimateDb != nil {
		err := ClimateDb.Close()
		if err != nil {
			fmt.Println("error closing database: " + err.Error())
		}
	}
}

func writeSnapshotToDb(snapshotSub *SnapshotSubmission) (int64, error) {
	queryStr := "INSERT INTO `snapshots` (`temp`, `humidity`, `co2`, `time`, `id`) VALUES (?, ?, ?, ?, NULL);"
	query, err := ClimateDb.Prepare(queryStr)
	if err != nil {
		closeErr := query.Close()
		if closeErr != nil {
			return -1, fmt.Errorf("couldn't prepare snapshot query: %w. (also failed to close: %s)", err, closeErr.Error())
		}
		return -1, fmt.Errorf("couldn't prepare snapshot query: %w", err)
	}
	result, err := query.Exec(
		snapshotSub.Temp,
		snapshotSub.Humidity,
		snapshotSub.Co2,
		snapshotSub.Timestamp,
	)
	if err != nil {
		return -1, fmt.Errorf("couldn't execute query to write snapshot to db: %w", err)
	}
	id, _ := result.LastInsertId()
	return id, nil
}

func fetchLastSnapshotWithDatetime() (*DatetimeSnapshotRecord, error) {
	var snapshotRecord DatetimeSnapshotRecord
	query := "SELECT `%s`, `%s`, `%s`, `%s`, `%s` FROM `snapshots` ORDER BY `id` DESC LIMIT 1;"
	rows, err := ClimateDb.Query(fmt.Sprintf(query, "id", "temp", "humidity", "co2", "time"))
	if err != nil {
		return nil, err
	}
	rows.Next()
	err = rows.Scan(
		&snapshotRecord.Id,
		&snapshotRecord.Temp,
		&snapshotRecord.Humidity,
		&snapshotRecord.Co2,
		&snapshotRecord.Timestamp,
	)
	if err != nil {
		return nil, fmt.Errorf("couldn't read last snapshot from the database: %w", err)
	}
	return &snapshotRecord, nil
}

func fetchUnixTimeSnapshotsSince(dateSince int) ([]*DatetimeSnapshotRecord, error) {
	snapshots := make([]*DatetimeSnapshotRecord, 0)
	query := "SELECT `%s`, `%s`, `%s`, `%s`, `%s` FROM `snapshots` WHERE UNIX(SECOND, `%s`, '%s') < 0 ORDER BY `id` DESC;"
	rows, err := ClimateDb.Query(fmt.Sprintf(query, "id", "temp", "humidity", "co2", "time", "time", dateSince))
	if err != nil {
		return nil, fmt.Errorf("couldn't execute select query: %w", err)
	}
	for rows.Next() {
		var snapshotRecord DatetimeSnapshotRecord
		err = rows.Scan(
			&snapshotRecord.Id,
			&snapshotRecord.Temp,
			&snapshotRecord.Humidity,
			&snapshotRecord.Co2,
			&snapshotRecord.Timestamp,
		)
		if err != nil {
			return nil, fmt.Errorf("error reading rows since %v with unixtime from the database: %w", dateSince, err)
		}
		snapshots = append(snapshots, &snapshotRecord)
	}
	return snapshots, nil
}

func fetchDatetimeSnapshotsSince(dateSince string) ([]*DatetimeSnapshotRecord, error) {
	snapshots := make([]*DatetimeSnapshotRecord, 0)
	query := "SELECT `%s`, `%s`, `%s`, `%s`, `%s` FROM `snapshots` WHERE TIMESTAMPDIFF(SECOND, `%s`, '%s') < 0 ORDER BY `id` DESC;"
	rows, err := ClimateDb.Query(fmt.Sprintf(query, "id", "temp", "humidity", "co2", "time", "time", dateSince))
	if err != nil {
		return nil, fmt.Errorf("couldn't execute select query: %w", err)
	}
	for rows.Next() {
		var snapshotRecord DatetimeSnapshotRecord
		err = rows.Scan(
			&snapshotRecord.Id,
			&snapshotRecord.Temp,
			&snapshotRecord.Humidity,
			&snapshotRecord.Co2,
			&snapshotRecord.Timestamp,
		)
		if err != nil {
			return nil, fmt.Errorf("error reading rows since %s with datetime from the database: %w", dateSince, err)
		}
		snapshots = append(snapshots, &snapshotRecord)
	}
	return snapshots, nil
}

func fetchUnixTimeSnapshotsBetween(startTime int, endTime int) ([]*DatetimeSnapshotRecord, error) {
	snapshots := make([]*DatetimeSnapshotRecord, 0)
	query := "SELECT `%s`, `%s`, `%s`, `%s`, `%s` FROM `snapshots` WHERE TIMESTAMPDIFF(SECOND, `%s`, '%s') < 0 ORDER BY `id` DESC;"
	rows, err := ClimateDb.Query(fmt.Sprintf(query, "id", "temp", "humidity", "co2", "time", "time", startTime, endTime))
	if err != nil {
		return nil, fmt.Errorf("couldn't execute select query: %w", err)
	}
	for rows.Next() {
		var snapshotRecord DatetimeSnapshotRecord
		err = rows.Scan(
			&snapshotRecord.Id,
			&snapshotRecord.Temp,
			&snapshotRecord.Humidity,
			&snapshotRecord.Co2,
			&snapshotRecord.Timestamp,
		)
		if err != nil {
			return nil, fmt.Errorf(
				"couldn't read rows from the database b/w %v and %v with datetime: %w", startTime, endTime, err)
		}
		snapshots = append(snapshots, &snapshotRecord)
	}
	return snapshots, nil
}

func fetchDatetimeSnapshotsBetween(startTime string, endTime string) ([]*DatetimeSnapshotRecord, error) {
	snapshots := make([]*DatetimeSnapshotRecord, 0)
	query := "SELECT `%s`, `%s`, `%s`, `%s`, `%s` FROM `snapshots` WHERE TIMESTAMPDIFF(SECOND, `%s`, '%s') < 0 ORDER BY `id` DESC;"
	rows, err := ClimateDb.Query(fmt.Sprintf(query, "id", "temp", "humidity", "co2", "time", "time", startTime, endTime))
	if err != nil {
		return nil, fmt.Errorf("couldn't execute select query: %w", err)
	}
	for rows.Next() {
		var snapshotRecord DatetimeSnapshotRecord
		err = rows.Scan(
			&snapshotRecord.Id,
			&snapshotRecord.Temp,
			&snapshotRecord.Humidity,
			&snapshotRecord.Co2,
			&snapshotRecord.Timestamp,
		)
		if err != nil {
			return nil, fmt.Errorf(
				"couldn't read rows from the database b/w %v and %v with unix time: %w", startTime, endTime, err)
		}
		snapshots = append(snapshots, &snapshotRecord)
	}
	return snapshots, nil
}