Transaction ID generator experiment

go.mod

module go.zikani.me/labs/transactionizer

go 1.21.1

require (
	github.com/jmoiron/sqlx v1.3.5
	github.com/lib/pq v1.3.0
	github.com/speps/go-hashids/v2 v2.0.1
	github.com/zikani03/pgadvisorylock v0.2.0
)

require (
	github.com/zeebo/xxh3 v0.13.0 // indirect
	golang.org/x/sys v0.0.0-20201119102817-f84b799fce68 // indirect
)

main.go

package main

import (
	"database/sql"
	"encoding/base32"
	"errors"
	"fmt"
	"log"
	"strings"
	"time"

	"context"

	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
	"github.com/speps/go-hashids/v2"
	"github.com/zikani03/pgadvisorylock"
)

func main() {
	// this Pings the database trying to connect
	conn, err := sqlx.Connect("postgres", "user=user dbname=testdb password=password123 sslmode=disable")
	if err != nil {
		log.Fatalln(err)
	}
	// conn is *sql.DB wherever you get your flavour from
	ctx := context.Background()
	ok, id, err := pgadvisorylock.AcquireLock(conn.DB, ctx, "person:1")
	if !ok {
		panic("Failed to acquire lock")
	}

	ok, err = pgadvisorylock.ReleaseLock(conn.DB, ctx, id)
	if !ok {
		panic("Failed to release lock")
	}

	ok, id, err = pgadvisorylock.AcquireSharedLock(conn.DB, ctx, "person:1")
	if !ok {
		panic("Failed to acquire lock")
	}

	advisoryLocks, err := pgadvisorylock.FetchAdvisoryLocks(conn.DB, ctx)
	if err != nil {
		panic("Failed to fetch locks")
	}

	for _, l := range advisoryLocks {
		fmt.Printf("LockID:%d, ClassID:%d, PID:%d\n", l.ObjectID, l.ClassID, l.Pid)
	}

	ok, err = pgadvisorylock.ReleaseSharedLock(conn.DB, ctx, id)
	if !ok {
		panic("Failed to release lock")
	}

	gen := NewGenerator(conn.DB)

	err = gen.CreateTable()
	if err != nil {
		panic(err)
	}
	var curDay = time.Now()
	for days := 1; days < 7; days++ {
		oneDay, _ := time.ParseDuration("24h")
		generateForDay := curDay.Add(oneDay)
		fmt.Println("Generatig transactions for ", generateForDay)
		for i := 0; i < 10; i++ {
			v := generateForDay
			txnNo, err := gen.Generate(v)
			if err != nil {
				panic(err)
			}
			fmt.Println("Generate Transaction Number ", txnNo)
		}
		curDay = generateForDay
	}

}

type TransactionNumberGenerator interface {
	CreateTable() error
	Generate(date time.Time) (string, error)
}

const sqlCreateTable = `
CREATE TABLE IF NOT EXISTS daily_txns (
	day date not null primary key,
	txn_no integer not null,
	last_txn_generated_at timestamptz
);
`

type generator struct {
	startForNewTxns int64
	conn            *sql.DB
	base32Encoder   *base32.Encoding
	hd              *hashids.HashID
}

func NewGenerator(conn *sql.DB) TransactionNumberGenerator {

	hd := hashids.NewData()
	hd.Salt = "this is my salt"
	hd.MinLength = 10
	h, _ := hashids.NewWithData(hd)

	return &generator{
		startForNewTxns: 1001,
		conn:            conn,
		base32Encoder:   base32.NewEncoding("ABCEFGHJKLMNPQRSTUVWY123456789-_"),
		hd:              h,
	}
}

type DayTransactionCounter struct {
	Day     time.Time
	Counter int64
}

func (gen *generator) FormatTransaction(row *DayTransactionCounter) string {
	encodedID := gen.base32Encoder.EncodeToString([]byte(fmt.Sprint(row.Counter)))
	//encodedID, _ = gen.hd.EncodeInt64([]int64{row.Counter})
	transactionNumber := fmt.Sprintf("TXN.%s%1d.%s", fmt.Sprint(row.Day.Year())[2:], row.Day.Month(), encodedID)

	return strings.ToUpper(strings.ReplaceAll(transactionNumber, "=", ""))
}

func (gen *generator) CreateTable() error {
	_, err := gen.conn.ExecContext(context.Background(), sqlCreateTable)
	if err != nil {
		return err
	}
	return nil
}

func (gen *generator) Generate(date time.Time) (string, error) {

	ctx := context.Background()
	tx, err := gen.conn.BeginTx(ctx, &sql.TxOptions{})
	if err != nil {
		return "", fmt.Errorf("failed create database transaction")
	}
	row := tx.QueryRow("SELECT day, txn_no FROM daily_txns WHERE day = $1 FOR UPDATE LIMIT 1;", date)
	var txnRow = new(DayTransactionCounter)
	err = row.Scan(&txnRow.Day, &txnRow.Counter)
	if err != nil {
		if !errors.Is(err, sql.ErrNoRows) {
			return "", fmt.Errorf("failed scan sql result to *DayTransactionCounter: %v", err)
		}
	}
	//if row.Err() != nil {
	if errors.Is(err, sql.ErrNoRows) {
		fmt.Println("attempting to create new transaction")
		// first transaction, create it here
		_, err := tx.ExecContext(ctx, "INSERT INTO daily_txns(day, txn_no) VALUES ($1, $2);", date, gen.startForNewTxns)
		if err != nil {
			_ = tx.Rollback()
			return "", fmt.Errorf("failed create new txn for the given day: %w", row.Err())
		}

		err = tx.Commit()
		if err != nil {
			return "", fmt.Errorf("failed to commit transaction to create new txn id: %w", row.Err())
		}

		return gen.FormatTransaction(&DayTransactionCounter{Day: date, Counter: gen.startForNewTxns}), nil
	}
	// 	_ = tx.Rollback()
	// 	return "", fmt.Errorf("failed find transaction for the given day: %w", row.Err())
	// }

	txnRow.Counter = txnRow.Counter + 1
	res, err := tx.ExecContext(ctx, "UPDATE daily_txns SET txn_no = $1, last_txn_generated_at = now() WHERE day = $2", txnRow.Counter, txnRow.Day)
	if err != nil {
		_ = tx.Rollback()
		return "", err
	}
	if affected, err := res.RowsAffected(); affected != 1 || err != nil {
		return "", fmt.Errorf("affected %d rows and failed to generate Transaction ID: %v", affected, err)
	}
	err = tx.Commit()
	if err != nil {
		return "", err
	}

	return gen.FormatTransaction(txnRow), nil
}