Notes/db.go

package main

import (
	// External
	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"

	// Standard
	"errors"
	"fmt"
	"io/fs"
	"regexp"
	"strconv"
)

var (
	dbConn string
	db     *sqlx.DB
)

func dbInit() (err error) { // {{{
	dbConn = fmt.Sprintf(
		"host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
		config.Database.Host,
		config.Database.Port,
		config.Database.Username,
		config.Database.Password,
		config.Database.Name,
	)

	logger.Info("db", "op", "connect", "host", config.Database.Host, "port", config.Database.Port)

	if db, err = sqlx.Connect("postgres", dbConn); err != nil {
		return
	}

	if err = dbVerifyInternals(); err != nil {
		return
	}

	err = dbUpdate()
	return
} // }}}
func dbVerifyInternals() (err error) { // {{{
	var rows *sqlx.Rows
	if rows, err = db.Queryx(
		`SELECT EXISTS (
			SELECT FROM pg_catalog.pg_class c
			JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
			WHERE  n.nspname = '_internal'
			AND    c.relname = 'db'
		)`,
	); err != nil {
		return
	}
	defer rows.Close()
	var exists bool
	rows.Next()
	if err = rows.Scan(&exists); err != nil {
		return
	}

	if !exists {
		logger.Info("db", "op", "create_db", "db", "_internal.db")
		if _, err = db.Exec(`
			CREATE SCHEMA "_internal";

			CREATE TABLE "_internal".db (
				"key" varchar NOT NULL,
				value varchar NULL,
				CONSTRAINT db_pk PRIMARY KEY (key)
			);

			INSERT INTO _internal.db("key", "value")
			VALUES('schema', '0');
			`,
		); err != nil {
			return
		}
	}
	return
} // }}}
func dbUpdate() (err error) { // {{{
	/* Current schema revision is read from database.
	 * Used to iterate through the embedded SQL updates
	 * up to the db schema version currently compiled
	 * program is made for. */
	var rows *sqlx.Rows
	var schemaStr string
	var schema int
	rows, err = db.Queryx(`SELECT value FROM _internal.db WHERE "key"='schema'`)
	if err != nil {
		return
	}
	defer rows.Close()

	if !rows.Next() {
		return errors.New("Table _interval.db missing schema row")
	}

	if err = rows.Scan(&schemaStr); err != nil {
		return
	}

	// Run updates
	schema, err = strconv.Atoi(schemaStr)
	if err != nil {
		return err
	}
	sqlSchemaVersion := sqlSchema()
	for i := (schema + 1); i <= sqlSchemaVersion; i++ {
		logger.Info("db", "op", "upgrade_schema", "schema", i)
		sql, _ := embedded.ReadFile(
			fmt.Sprintf("sql/%04d.sql", i),
		)
		_, err = db.Exec(string(sql))
		if err != nil {
			return
		}
		_, err = db.Exec(`UPDATE _internal.db SET "value"=$1 WHERE "key"='schema'`, i)
		if err != nil {
			return
		}
		logger.Info("db", "op", "upgrade_schema", "schema", i, "result", "ok")
	}

	return
} // }}}
func sqlSchema() (max int) { // {{{
	var num int

	files, _ := fs.ReadDir(embedded, "sql")
	sqlFilename := regexp.MustCompile(`^([0-9]+)\.sql$`)

	for _, file := range files {
		fname := sqlFilename.FindStringSubmatch(file.Name())
		if len(fname) != 2 {
			continue
		}
		num, _ = strconv.Atoi(fname[1])
	}

	if num > max {
		max = num
	}

	return
} // }}}

// vim: foldmethod=marker