bottin-ag/dbclient/dbclient.go

// Package dbclient provides the database client used by the API server
package dbclient

import (
	"fmt"

	"git.agecem.com/agecem/bottin-ag/dbschema"
	"git.agecem.com/agecem/bottin-ag/dbstruct"
	"github.com/jmoiron/sqlx"
)

// DBClient holds a *sqlx.DB and implements methods to manipulate the database
type DBClient struct {
	DB *sqlx.DB
}

func New(host, database, user, password string, port int, useSSL bool) (*DBClient, error) {
	var SSLMode string

	switch useSSL {
	case true:
		SSLMode = "enable"
	case false:
		SSLMode = "disable"
	}

	connectionString := fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=%s",
		user,
		password,
		host,
		port,
		database,
		SSLMode,
	)

	db, err := sqlx.Connect("pgx", connectionString)
	if err != nil {
		return nil, err
	}

	return &DBClient{DB: db}, nil
}

func (d *DBClient) CreateTablesIfNotExist() error {
	_, err := d.DB.Exec(dbschema.Schema)
	return err
}

/*
InsertPresence inserts a dbstruct.Presence into the database, returning the
values of the inserted presence and any error that occured.
*/
func (d *DBClient) InsertPresence(presence dbstruct.Presence) (dbstruct.Presence, error) {
	var insertedPresence dbstruct.Presence
	var rows *sqlx.Rows

	rows, err := d.DB.NamedQuery(`
       INSERT INTO presences (id, at, name, programme_id)
       VALUES (:id, current_timestamp, :name, :programme_id)
       RETURNING id, at, name, programme_id
       `, presence)
	if err != nil {
		return insertedPresence, err
	}
	defer rows.Close()

	for rows.Next() {
		if err := rows.Scan(&insertedPresence.ID, &insertedPresence.At, &insertedPresence.Name, &insertedPresence.ProgrammeID); err != nil {
			return insertedPresence, err
		}
	}

	return insertedPresence, nil
}

// GetDecompte returns the row count of the `presences` table and any error encountered
func (d *DBClient) GetDecompte() (decompte int, err error) {
	err = d.DB.Get(&decompte, "SELECT COUNT(*) FROM presences;")
	return decompte, err
}

/*
GetPresences returns all rows in the `presences` table and any error encountered

Can be used to export the database content at the end of the event.
*/
func (d *DBClient) GetPresences() (presences []dbstruct.Presence, err error) {
	return presences, d.DB.Select(&presences, "SELECT * FROM presences;")
}