server/database/db.go

package database

import (
	"time"

	"git.ohea.xyz/cursorius/server/config"

	"context"
	"fmt"

	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/op/go-logging"
)

var log = logging.MustGetLogger("cursorius-server")

type Database struct {
	Conn *pgxpool.Pool
}

func LaunchDB(conf config.DBConfig) (Database, error) {

	dbURL := fmt.Sprintf(
		"postgres://%v:%v@%v:%v/%v",
		conf.Username,
		conf.Password,
		conf.Address,
		conf.Port,
		conf.Name,
	)

	dbURLNoPasswd := fmt.Sprintf(
		"postgres://%v:********@%v:%v/%v",
		conf.Username,
		conf.Address,
		conf.Port,
		conf.Name,
	)

	db := Database{}

	var err error
	log.Infof("Connecting to database with URL \"%v\"", dbURLNoPasswd)
	db.Conn, err = pgxpool.New(context.Background(), dbURL)
	if err != nil {
		return db, fmt.Errorf("could not create database pool: %w", err)
	}

	// sleep until we can sucessfully acquire a connection
	for i := 0; i < 10; i++ {
		_, err = db.Conn.Acquire(context.Background())
		if err == nil {
			break
		}
		time.Sleep(2 * time.Second)

	}
	if err != nil {
		return db, fmt.Errorf("Could not open database: %w", err)
	}

	log.Infof("Database connected sucessfully!")

	versionTableExistsQuery := `
SELECT EXISTS (
    SELECT FROM pg_tables
    WHERE tablename = 'version'
);`

	var versionTableExists bool
	err = db.Conn.QueryRow(context.Background(), versionTableExistsQuery).Scan(&versionTableExists)
	if err != nil {
		return db, fmt.Errorf("Could not check if database was initalized: %w", err)
	}

	if versionTableExists {
		// TODO: migrations
	} else {
		log.Info("New database found, initializing....")
		err = initDB(db.Conn)
		if err != nil {
			return db, fmt.Errorf("Could not initalize database: %w", err)
		}
	}

	return db, nil
}

func initDB(conn *pgxpool.Pool) error {
	createTablesQuery := `
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE version (
    version INT NOT NULL

);

CREATE TABLE clone_credentials (
	id		  UUID PRIMARY KEY,
	name      TEXT NOT NULL,
    type      TEXT NOT NULL,
	username  TEXT NOT NULL,
	secret    TEXT NOT NULL
);

CREATE TABLE pipelines (
    id                    UUID PRIMARY KEY,
    name                  TEXT NOT NULL,
    url                   TEXT NOT NULL,
    poll_interval         INTEGER,
    clone_credential      UUID DEFAULT NULL,

    CONSTRAINT fk_clone_credential
        FOREIGN KEY(clone_credential)
            REFERENCES clone_credentials(id)
);

CREATE TABLE secrets (
	id	   UUID PRIMARY KEY,
    name   TEXT NOT NULL UNIQUE,
    secret TEXT NOT NULL
);

CREATE TABLE pipeline_secret_mappings (
    pipeline    UUID NOT NULL,
	secret      UUID NOT NULL,

    CONSTRAINT fk_pipeline
        FOREIGN KEY(pipeline)
            REFERENCES pipelines(id),

    CONSTRAINT fk_secret
        FOREIGN KEY(secret)
            REFERENCES secrets(id)
);

CREATE TABLE webhooks (
    id            UUID PRIMARY KEY,
    server_type	  TEXT,
    secret        TEXT,
    pipeline      UUID,

    CONSTRAINT fk_pipeline
        FOREIGN KEY(pipeline)
            REFERENCES pipelines(id)
);

CREATE TABLE runs (
    id            UUID PRIMARY KEY,
    pipeline      UUID,
    in_progress   BOOLEAN DEFAULT NULL,
	build_output  TEXT DEFAULT NULL,
    result        BIGINT DEFAULT NULL,
    stdout        TEXT DEFAULT NULL,
    stderr        TEXT DEFAULT NULL,

    CONSTRAINT fk_pipeline
       FOREIGN KEY(pipeline)
           REFERENCES pipelines(id)
);

CREATE TABLE command_executions (
    id          UUID PRIMARY KEY,
    run_id      UUID,
    command     TEXT,
    return_code INT,
    stdout      TEXT,
    stderr      TEXT,
    start_time  TIMESTAMP,
    end_time    TIMESTAMP,

    CONSTRAINT fk_run_id
        FOREIGN KEY(run_id)
            REFERENCES runs(id)
);

CREATE TABLE runners (
    id      UUID PRIMARY KEY,
    name    TEXT NOT NULL UNIQUE,
    token   TEXT NOT NULL
);

CREATE TABLE pipeline_refs (
	name			TEXT PRIMARY KEY NOT NULL,
	pipeline_id 	UUID NOT NULL,
	hash			TEXT NOT NULL,

    CONSTRAINT fk_pipeline_id
        FOREIGN KEY(pipeline_id)
            REFERENCES pipelines(id)
);
`

	_, err := conn.Exec(context.Background(), createTablesQuery)
	if err != nil {
		return err
	}

	return nil
}