bottin/data/data.go

package data

import (
	"errors"
	"fmt"

	"git.agecem.com/agecem/bottin/v6/models"
	_ "github.com/jackc/pgx/stdlib"
	"github.com/jmoiron/sqlx"
	"github.com/spf13/viper"
)

// DataClient is a postgres client based on sqlx
type DataClient struct {
	PostgresConnection PostgresConnection
	DB                 sqlx.DB
}

type PostgresConnection struct {
	User     string
	Password string
	Database string
	Host     string
	Port     int
	SSL      bool
}

func NewDataClientFromViper() (*DataClient, error) {
	client, err := NewDataClient(
		PostgresConnection{
			User:     viper.GetString("db.user"),
			Password: viper.GetString("db.password"),
			Host:     viper.GetString("db.host"),
			Database: viper.GetString("db.database"),
			Port:     viper.GetInt("db.port"),
		})

	return client, err
}

func NewDataClient(connection PostgresConnection) (*DataClient, error) {
	client := &DataClient{PostgresConnection: connection}

	connectionString := fmt.Sprintf("postgres://%s:%s@%s:%d/%s",
		client.PostgresConnection.User,
		client.PostgresConnection.Password,
		client.PostgresConnection.Host,
		client.PostgresConnection.Port,
		client.PostgresConnection.Database,
	)

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

	client.DB = *db

	return client, nil
}

func (d *DataClient) Seed() (int64, error) {
	result, err := d.DB.Exec(models.Schema)
	if err != nil {
		return 0, err
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return rows, err
	}

	return rows, nil
}

// InsertMembres inserts a slice of Membre into a database, returning the amount inserted and any error encountered
func (d *DataClient) InsertMembres(membres []models.Membre) (int64, error) {
	var rowsInserted int64
	tx, err := d.DB.Beginx()
	if err != nil {
		tx.Rollback()
		return rowsInserted, err
	}

	for _, membre := range membres {
		if membre.ID == "" {
			tx.Rollback()
			return 0, errors.New("Cannot insert membre with no membre_id")
		}
		result, err := tx.NamedExec("INSERT INTO membres (id, last_name, first_name, prefered_name, programme_id) VALUES (:id, :last_name, :first_name, :prefered_name, :programme_id) ON CONFLICT (id) DO NOTHING;", &membre)
		if err != nil {
			tx.Rollback()
			return 0, err
		}

		rows, err := result.RowsAffected()
		if err != nil {
			tx.Rollback()
			return 0, err
		}

		rowsInserted += rows
	}

	err = tx.Commit()
	if err != nil {
		return rowsInserted, err
	}

	return rowsInserted, nil
}

func (d *DataClient) InsertProgrammes(programmes []models.Programme) (int64, error) {
	var rowsInserted int64
	tx, err := d.DB.Beginx()
	if err != nil {
		tx.Rollback()
		return rowsInserted, err
	}

	for _, programme := range programmes {
		if programme.ID == "" {
			tx.Rollback()
			return 0, errors.New("Cannot insert programme with no programme_id")
		}

		result, err := tx.NamedExec("INSERT INTO programmes (id, titre) VALUES (:id, :titre) ON CONFLICT DO NOTHING;", &programme)
		if err != nil {
			tx.Rollback()
			return 0, err
		}

		rows, err := result.RowsAffected()
		if err != nil {
			tx.Rollback()
			return 0, err
		}

		rowsInserted += rows
	}

	err = tx.Commit()
	if err != nil {
		return rowsInserted, err
	}

	return rowsInserted, nil
}

func (d *DataClient) GetMembre(membreID string) (models.Membre, error) {
	var membre models.Membre

	rows, err := d.DB.Queryx("SELECT * FROM membres WHERE id = $1 LIMIT 1;", membreID)
	if err != nil {
		return membre, err
	}

	for rows.Next() {
		err := rows.StructScan(&membre)
		if err != nil {
			return membre, err
		}
	}

	if membre.ID == "" {
		return membre, fmt.Errorf("No membre by that id was found")
	}

	return membre, nil
}

func (d *DataClient) UpdateMembreName(membreID, newName string) (int64, error) {
	result, err := d.DB.Exec("UPDATE membres SET prefered_name = $1 WHERE id = $2;", newName, membreID)
	if err != nil {
		return 0, err
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return rows, err
	}

	return rows, nil
}

func (d *DataClient) GetMembres() (membres []models.Membre, err error) {
	return membres, d.DB.Select(&membres, "SELECT * FROM membres;")
}