go-menuserver/dbconnection/sqlite_repository.go

package dbconnection

import (
	"context"
	"database/sql"
	"log"
	"strconv"
)

type SQLiteRepository struct {
	db *sql.DB
}

func NewSQLiteRepository(db *sql.DB) *SQLiteRepository {
	return &SQLiteRepository{
		db: db,
	}
}

func (r *SQLiteRepository) All() ([]Menu, error) {
	rows, err := r.db.Query("SELECT _id, short, hash, name, meat, mealtype FROM menu")

	if err != nil {
		return nil, err
	}

	defer rows.Close()

	var all []Menu

	for rows.Next() {
		var menu Menu
		if err := rows.Scan(&menu._ID, &menu.Short, &menu.Hash, &menu.Name, &menu.Meat, &menu.Mealtype); err != nil {
			return nil, err
		}

		all = append(all, menu)
	}
	return all, nil
}

func (r *SQLiteRepository) GetOneShort(ShortCode string) (Menu, error) {
	rows, err := r.db.Query("select md from menu where short = ?", ShortCode)

	var empty Menu

	if err != nil {
		return empty, err
	}

	defer rows.Close()

	var item Menu

	for rows.Next() {
		var menu Menu
		if err := rows.Scan(&menu.Md); err != nil {
			return empty, err
		}

		item = menu
	}

	return item, nil
}

func (r *SQLiteRepository) GetOneHash(Hash string) (Menu, error) {
	log.Printf("Get one hash: %s\n", Hash)

	rows, err := r.db.Query("select * from menu where hash = ?", Hash)

	var empty Menu

	if err != nil {
		log.Println("GetOneHash query error")
		return empty, err

	}

	defer rows.Close()

	var item Menu

	for rows.Next() {
		var menu Menu
		if err := rows.Scan(&menu._ID, &menu.Name, &menu.Url, &menu.Md, &menu.Short, &menu.Hash, &menu.Meat, &menu.Mealtype, &menu.Lastused); err != nil {
			return empty, err
		}

		item = menu
	}

	log.Printf("%+v\n", item)
	return item, nil
}

func (r *SQLiteRepository) InsertOne(newitem Menu) (InsertedType, error) {
	log.Printf("Insert One: %s\n", newitem)

	stmt, err := r.db.Prepare("INSERT INTO menu(name, url, md, short, hash, meat, mealtype, lastused)  VALUES (?,?,?,?,?,?,?,?)")

	if err != nil {
		log.Fatal(err)
	}

	defer stmt.Close()
	
	b, err := stmt.Exec(newitem.Name, newitem.Url, newitem.Md, newitem.Short, newitem.Hash, newitem.Meat, newitem.Mealtype, newitem.Lastused)

	if err != nil {
		log.Println(err)
	}

	var returnVal InsertedType

	lastID, err := b.LastInsertId()

	if err == nil {
		returnVal = InsertedType{
			Msg: "Row inserted",
			_ID: strconv.FormatInt(lastID, 10),
		}

		log.Printf("B:%+v\n", b)

	}
	return returnVal, err
}

func (r *SQLiteRepository) GetRandom(timestamp int64) ([]Menu, error) {

	ts := strconv.FormatInt(timestamp, 10)

	rows, err := r.db.Query("SELECT _id, short, hash, name, meat, mealtype FROM menu where mealtype =1 and lastused<? order by RANDOM()", ts)

	if err != nil {
		return nil, err
	}

	defer rows.Close()

	var all []Menu

	for rows.Next() {
		var menu Menu
		if err := rows.Scan(&menu._ID, &menu.Short, &menu.Hash, &menu.Name, &menu.Meat, &menu.Mealtype); err != nil {
			return nil, err
		}

		all = append(all, menu)
	}
	return all, nil
}

func (r *SQLiteRepository) GetRandomSoup(timestamp int64) (Menu, error) {
	var empty Menu

	ts := strconv.FormatInt(timestamp, 10)

	rows, err := r.db.Query("SELECT _id, short, hash, name, meat, mealtype FROM menu where mealtype =2 and lastused<? order by RANDOM() LIMIT 1", ts)

	if err != nil {
		return empty, err
	}

	defer rows.Close()

	var item Menu

	for rows.Next() {
		var menu Menu
		if err := rows.Scan(&menu._ID, &menu.Short, &menu.Hash, &menu.Name, &menu.Meat, &menu.Mealtype); err != nil {
			return empty, err
		}

		item = menu
	}

	return item, nil
}

func (r *SQLiteRepository) UpdateTimeStamps(updateRecs []Menu, timestamp int64) error {

	log.Printf("Updating timestamps to : %+v", timestamp)

	stmt, err := r.db.Prepare("UPDATE menu SET lastused = ? WHERE _id = ?")

	if err != nil {
		log.Fatal(err)
	}

	defer stmt.Close()

	for _, item := range updateRecs {

		b, err := stmt.Exec(timestamp, item._ID)

		if err != nil {
			log.Println(err)
		}

		rowsAffected, err := b.RowsAffected()

		if err != nil {

			log.Printf("Rows affected:%+v\n", rowsAffected)

		}

	}

	return nil
}

func (r *SQLiteRepository) UpdateTimeStampsTransaction(updateRecs []Menu, timestamp int64) error {

	log.Printf("Update timestamps to : %+v", timestamp)
	ctx := context.Background()

	tx, err := r.db.BeginTx(ctx, nil)

	if err != nil {
		log.Fatal(err)
	}

	for _, item := range updateRecs {

		log.Println(item)

		_, err = tx.ExecContext(ctx, "UPDATE menu SET lastused = ? WHERE _id = ?", item._ID, timestamp)
		if err != nil {
			log.Fatal(err)
			// Incase we find any error in the query execution, rollback the transaction
			tx.Rollback()
			return err
		}

	}

	err = tx.Commit()
	if err != nil {
		log.Fatal(err)
	} else {
		log.Println("Records updated")
	}
	// UPDATE menu SET lastused = $lastused WHERE _id = $in

	return err
}