bottin-agenda/data/data.go

package data

import (
	"errors"
	"fmt"

	"git.agecem.com/agecem/bottin-agenda/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"),
		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(models.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 {
		tx.Rollback()
		return rowsInserted, err
	}

	for _, transaction := range transactions {
		// Check values
		if transaction.MembreID == "" {
			tx.Rollback()
			return rowsInserted, errors.New("Cannot insert transaction with no 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 {
			tx.Rollback()
			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 {
				tx.Rollback()
				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.NamedQuery("SELECT * FROM transactions WHERE membre_id=:membre_id AND is_perpetual=:is_perpetual LIMIT 1;"
	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("No transaction found")
	}

	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
}

/*
// 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);", &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);", &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) Insert(assets []models.Asset) (id int64, err error) {
	// Check for minimal required info
	for _, asset := range assets {
		if asset.Description == "" {
			err = errors.New("Cannot insert: At least one asset has no `description` set.")
			return
		}
	}

	tx := d.DB.MustBegin()

	for _, asset := range assets {
		_, err = tx.NamedExec("INSERT INTO assets (description, status, created_at) VALUES (:description, :status, current_timestamp)", asset)
		if err != nil {
			return
		}
	}

	err = tx.Commit()

	return
}

func (d *DataClient) List() ([]models.Asset, error) {
	// Query the database, storing results in a []Person (wrapped in []interface{})
	assets := []models.Asset{}

	err := d.DB.Select(&assets, "SELECT * FROM assets WHERE deleted_at IS NULL LIMIT 1000")
	if err != nil {
		return nil, err
	}

	return assets, nil
}

// RecordEvent allows inserting into events when an asset or a tag is modified
// or deleted.
func (d *DataClient) RecordEvent(assetID, tagID int64, content string) error {
	event := models.Event{
		AssetID: assetID,
		TagID:   tagID,
		Content: content,
	}
	_, err := d.DB.NamedExec("INSERT INTO events (asset_id, tag_id, at, content) VALUES (:asset_id, :tag_id, current_timestamp, :content);", event)
	if err != nil {
		return err
	}

	return nil
}

func (d *DataClient) Delete(assetIDs []int64) ([]int64, error) {
	var rows []int64

	tx := d.DB.MustBegin()

	for _, assetID := range assetIDs {
		result, err := d.DB.Exec("UPDATE assets SET deleted_at = current_timestamp WHERE id = $1 AND deleted_at IS NULL;", assetID)
		if err != nil {
			return rows, err
		}

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

		if rowsAffected != 0 {
			rows = append(rows, assetID)
		}
	}

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

	for _, assetID := range assetIDs {
		d.RecordEvent(assetID, -1, fmt.Sprintf("Asset %d deleted.", assetID))
	}

	return rows, nil
}

func (d *DataClient) UpdateAssetDescription(assetID int64, description string) (int64, error) {
	result, err := d.DB.Exec("UPDATE assets SET description = $1 WHERE id = $2", description, assetID)
	if err != nil {
		return 0, err
	}

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

	if rowsAffected != 0 {
		return 0, errors.New("Nothing to do")
	}

	return rowsAffected, nil
}

func (d *DataClient) UpdateAssetStatus(assetID int64, status string) (int64, error) {
	result, err := d.DB.Exec("UPDATE assets SET status = $1 WHERE id = $2", status, assetID)
	if err != nil {
		return 0, err
	}

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

	if rowsAffected != 0 {
		return 0, errors.New("Nothing to do")
	}

	return rowsAffected, nil
}
*/