bottin-agenda/data/dataclient.go

package data

import (
	"errors"
	"fmt"

	"git.agecem.com/agecem/bottin-agenda/models"
	"git.agecem.com/agecem/bottin-agenda/sql"
	_ "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"),
		Port:     viper.GetInt("db.port"),
		Database: viper.GetString("db.database"),
	})

	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(sql.Schema())
	if err != nil {
		return 0, err
	}

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

	return rows, nil
}

// InsertTransactions inserts a slice of Transaction into a database, returning the transactions inserted and any error encountered
func (d *DataClient) InsertTransactions(transactions []models.Transaction) ([]models.Transaction, error) {
	var rowsInserted []models.Transaction

	// Start transaction
	tx, err := d.DB.Beginx()
	if err != nil {
		return rowsInserted, err
	}
	defer tx.Rollback()

	for _, transaction := range transactions {
		// Check values
		if transaction.MembreID == "" {
			return rowsInserted, errors.New("Impossible d'insérer une transaction sans membre_id")
		}

		rows, err := tx.NamedQuery(`
INSERT INTO
    transactions (
	membre_id,
	given_at,
	is_perpetual
    )
VALUES
    (
	:membre_id,
	current_timestamp,
	:is_perpetual
    )
RETURNING
    id,
    membre_id,
    is_perpetual;
`, &transaction)
		if err != nil {
			return rowsInserted, err
		}
		defer rows.Close()

		for rows.Next() {
			var transactionRow models.Transaction
			if err := rows.Scan(&transactionRow.ID, &transactionRow.MembreID, &transactionRow.IsPerpetual); err != nil {
				return rowsInserted, err
			}

			rowsInserted = append(rowsInserted, transactionRow)
		}
	}

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

	return rowsInserted, nil
}

func (d *DataClient) GetTransaction(membreID string, is_perpetual bool) (models.Transaction, error) {
	var transaction models.Transaction

	err := d.DB.Get(&transaction, `
SELECT
    *
FROM
    transactions
WHERE
    membre_id = $1 AND
    is_perpetual = $2
LIMIT 1;
`, membreID, is_perpetual)
	if err != nil {
		return transaction, err
	}

	if transaction.ID == "" {
		return transaction, fmt.Errorf("Aucune transaction trouvée")
	}

	return transaction, nil
}

func (d *DataClient) ListTransactions() ([]models.Transaction, error) {
	var transactions []models.Transaction
	if err := d.DB.Select(&transactions, `
SELECT
    *
FROM
    transactions
LIMIT 20000;
`); err != nil {
		return transactions, err
	}

	return transactions, nil
}