server/database/func.go

package database

import (
	"context"
	"fmt"
	"regexp"

	"github.com/google/uuid"
)

func (db *Database) GetPipelines() ([]Pipeline, error) {
	query := `
SELECT id, name, url, poll_interval, clone_credential
FROM pipelines;`

	pipelines := make([]Pipeline, 0)

	rows, err := db.Conn.Query(context.Background(), query)
	if err != nil {
		return pipelines, fmt.Errorf("Could not query database for pipelines: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var pipeline Pipeline
		var idStr string
		if err := rows.Scan(&idStr, &pipeline.Name, &pipeline.Url, &pipeline.PollInterval, &pipeline.CloneCredential); err != nil {
			return pipelines, err
		}

		pipeline.Id, err = uuid.Parse(idStr)
		if err != nil {
			return pipelines, err
		}
		pipelines = append(pipelines, pipeline)
	}

	return pipelines, nil
}

func (db *Database) GetPipelineById(id uuid.UUID) (Pipeline, error) {
	query := `
SELECT name, url, poll_interval
FROM pipelines
WHERE id=$1;`

	pipeline := Pipeline{
		Id: id,
	}

	err := db.Conn.QueryRow(context.Background(), query, id).Scan(&pipeline.Name, &pipeline.Url, &pipeline.PollInterval)
	if err != nil {
		return pipeline, fmt.Errorf("Could not query database for pipeline with id %v: %w", id.String(), err)
	}

	return pipeline, nil
}

func (db *Database) CreatePipeline(name string, url string, pollInterval int, credential *uuid.UUID) (Pipeline, error) {
	query := `
INSERT INTO pipelines (id, name, url, poll_interval, clone_credential)
VALUES (uuid_generate_v4(), $1, $2, $3, $4)
RETURNING id, name, url, poll_interval;`

	pipeline := Pipeline{}
	var idStr string
	err := db.Conn.QueryRow(context.Background(), query, name, url, pollInterval, credential).Scan(&idStr, &pipeline.Name, &pipeline.Url, &pipeline.PollInterval)
	if err != nil {
		return pipeline, fmt.Errorf("Could not create pipeline: %w", err)
	}

	pipeline.Id, err = uuid.Parse(idStr)
	if err != nil {
		return pipeline, fmt.Errorf("Could not parse UUID generated by DB: %w", err)
	}

	return pipeline, nil
}

func (db *Database) UpdatePipeline(pipelineId uuid.UUID, name *string, url *string, pollInterval *int) (Pipeline, error) {
	query := `
UPDATE pipelines
SET name=$1, url=$2, poll_interval=$3
WHERE id=$4
RETURNING name, url, poll_interval, clone_credential;`

	pipeline, err := db.GetPipelineById(pipelineId)
	if err != nil {
		return pipeline, err
	}

	var nameNew string
	var urlNew string
	var pollIntervalNew int

	if name != nil {
		nameNew = *name
	} else {
		nameNew = pipeline.Name
	}
	if url != nil {
		urlNew = *url
	} else {
		urlNew = pipeline.Url
	}
	if pollInterval != nil {
		pollIntervalNew = *pollInterval
	} else {
		pollIntervalNew = pipeline.PollInterval
	}

	err = db.Conn.QueryRow(context.Background(),
		query, nameNew, urlNew, pollIntervalNew, pipelineId).Scan(
		&pipeline.Name, &pipeline.Url, &pipeline.PollInterval, &pipeline.CloneCredential,
	)
	if err != nil {
		return pipeline, fmt.Errorf("Could not add credential to pipeline: %w", err)
	}

	return pipeline, err
}

func (db *Database) SetPipelineCloneCredential(pipelineId uuid.UUID, credentialId *uuid.UUID) (Pipeline, error) {
	query := `
UPDATE pipelines
SET clone_credential=$1
WHERE id=$2
RETURNING name, url, poll_interval, clone_credential;`

	pipeline := Pipeline{
		Id: pipelineId,
	}

	err := db.Conn.QueryRow(context.Background(),
		query, credentialId, pipelineId).Scan(
		&pipeline.Name, &pipeline.Url, &pipeline.PollInterval, &pipeline.CloneCredential,
	)
	if err != nil {
		return pipeline, fmt.Errorf("Could not add credential to pipeline: %w", err)
	}

	return pipeline, err
}

func (db *Database) RemovePipelineCredential(pipelineId uuid.UUID) (Pipeline, error) {
	query := `
UPDATE pipelines
SET credential=null
WHERE id=$1
RETURNING name, url, poll_interval, clone_credential;`

	pipeline := Pipeline{
		Id: pipelineId,
	}

	err := db.Conn.QueryRow(context.Background(),
		query, pipelineId).Scan(
		&pipeline.Name, &pipeline.Url, &pipeline.PollInterval, &pipeline.CloneCredential,
	)
	if err != nil {
		return pipeline, fmt.Errorf("Could not add credential to pipeline: %w", err)
	}

	return pipeline, err
}

func (db *Database) GetWebhooksForPipeline(id uuid.UUID) ([]Webhook, error) {
	query := `
SELECT id, server_type, secret
FROM webhooks
WHERE pipeline=$1;`

	webhooks := make([]Webhook, 0)

	rows, err := db.Conn.Query(context.Background(), query, id)
	if err != nil {
		return webhooks, fmt.Errorf("Could not get webhooks for pipeline with id \"%v\": %w", id, err)
	}
	defer rows.Close()

	for rows.Next() {
		var webhook Webhook
		var idStr string
		if err := rows.Scan(&idStr, &webhook.ServerType, &webhook.Secret); err != nil {
			return webhooks, err
		}

		webhook.Id, err = uuid.Parse(idStr)
		if err != nil {
			return webhooks, err
		}
		webhooks = append(webhooks, webhook)
	}

	return webhooks, nil
}

func (db *Database) GetWebhookById(id uuid.UUID) (Webhook, error) {
	query := `
SELECT server_type, secret, pipeline
FROM webhooks
WHERE id=$1;`

	webhook := Webhook{
		Id: id,
	}

	err := db.Conn.QueryRow(context.Background(), query, id).Scan(&webhook.ServerType, &webhook.Secret, &webhook.Pipeline)
	if err != nil {
		return webhook, fmt.Errorf("Could not query database for webhook with id %v: %w", id.String(), err)
	}

	return webhook, nil
}

func (db *Database) CreateWebhook(serverType WebhookSender, pipelineId uuid.UUID) (Webhook, error) {

	query := `
INSERT INTO webhooks (id, server_type, secret, pipeline)
VALUES (uuid_generate_v4(), $1, (select substr(md5(random()::text), 0, 50)), $2)
RETURNING id, server_type, secret, pipeline;`

	webhook := Webhook{}
	var idStr string
	err := db.Conn.QueryRow(context.Background(), query, string(serverType), pipelineId).Scan(&idStr, &webhook.ServerType, &webhook.Secret, &webhook.Pipeline)
	if err != nil {
		return webhook, err
	}

	id, err := uuid.Parse(idStr)
	if err != nil {
		return webhook, err
	}

	webhook.Id = id

	return webhook, nil
}

func (db *Database) CreateCredential(name string, credentialtype CloneCredentialType, username string, secret string) (CloneCredential, error) {
	query := `
INSERT INTO clone_credentials (id, name, type, username, secret)
VALUES(uuid_generate_v4(), $1, $2, $3, $4)
RETURNING id, name, type, username, secret;`

	credential := CloneCredential{}
	var idStr string
	err := db.Conn.QueryRow(
		context.Background(),
		query,
		name,
		string(credentialtype),
		username,
		secret,
	).Scan(&idStr, &credential.Name, &credential.Type, &credential.Username, &credential.Secret)
	if err != nil {
		return credential, err
	}

	id, err := uuid.Parse(idStr)
	if err != nil {
		return credential, err
	}

	credential.Id = id

	return credential, nil
}

func (db *Database) GetCloneCredentialById(id uuid.UUID) (CloneCredential, error) {
	query := `
SELECT name, type, username, secret
FROM clone_credentials
WHERE id=$1;`

	log.Debugf("requested credential with id %v", id)

	credential := CloneCredential{
		Id: id,
	}

	err := db.Conn.QueryRow(context.Background(), query, id).Scan(&credential.Name, &credential.Type, &credential.Username, &credential.Secret)
	if err != nil {
		return credential, fmt.Errorf("Could not query database for credential with id %v: %w", id.String(), err)
	}

	return credential, nil
}

func (db *Database) GetCredentials() ([]CloneCredential, error) {
	query := `
SELECT id, name, type, username, secret
FROM clone_credentials;`

	credentials := make([]CloneCredential, 0)

	rows, err := db.Conn.Query(context.Background(), query)
	if err != nil {
		return credentials, fmt.Errorf("Could not query database for credentials: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var credential CloneCredential
		var idStr string
		if err := rows.Scan(&idStr, &credential.Name, &credential.Type, &credential.Username, &credential.Secret); err != nil {
			return credentials, err
		}

		credential.Id, err = uuid.Parse(idStr)
		if err != nil {
			return credentials, err
		}
		credentials = append(credentials, credential)
	}

	return credentials, nil
}

func (db *Database) CreateRun(pipelineId uuid.UUID) (Run, error) {
	query := `
INSERT INTO runs (id, pipeline, in_progress)
VALUES(uuid_generate_v4(), $1, true)
RETURNING id, pipeline, in_progress;`

	run := Run{}
	var idStr string
	err := db.Conn.QueryRow(context.Background(), query, pipelineId).Scan(&idStr, &run.Pipeline, &run.InProgress)
	if err != nil {
		return run, err
	}

	run.Id, err = uuid.Parse(idStr)
	if err != nil {
		return run, err
	}

	return run, nil
}

func (db *Database) UpdateRunBuildOutput(runId uuid.UUID, buildResult string) error {
	query := `
UPDATE runs
SET build_output=$1
WHERE id=$2;`

	_, err := db.Conn.Exec(context.Background(),
		query, buildResult, runId)

	return err

}

func (db *Database) UpdateRunResult(r Run) error {
	query := `
UPDATE runs
SET in_progress=$1, result=$2, stdout=$3, stderr=$4
WHERE id=$5;`

	// TODO: does r.Result need a pointer derefrence?
	_, err := db.Conn.Exec(context.Background(),
		query, r.InProgress, r.Result, r.Stdout, r.Stderr, r.Id)

	return err
}

func (db *Database) GetRunsForPipeline(pipelineId uuid.UUID) ([]Run, error) {
	query := `
SELECT id, in_progress, result, build_output, stdout, stderr
FROM runs
WHERE pipeline=$1;`

	runs := make([]Run, 0)

	rows, err := db.Conn.Query(context.Background(), query, pipelineId)
	if err != nil {
		return runs, fmt.Errorf("Could not get runs for pipeline with id \"%v\": %w", pipelineId, err)
	}
	defer rows.Close()

	for rows.Next() {
		var run Run
		var idStr string
		if err := rows.Scan(
			&idStr,
			&run.InProgress,
			&run.Result,
			&run.BuildOutput,
			&run.Stdout,
			&run.Stderr,
		); err != nil {
			return runs, err
		}

		run.Id, err = uuid.Parse(idStr)
		if err != nil {
			return runs, err
		}
		runs = append(runs, run)
	}

	return runs, nil
}

func (db *Database) GetPipelineRefs(pipelineId uuid.UUID) (map[string]string, error) {
	query := `
SELECT name, hash
FROM pipeline_refs
WHERE pipeline_id=$1;`

	refsMap := make(map[string]string)

	refs, err := db.Conn.Query(context.Background(), query, pipelineId)
	if err != nil {
		return refsMap, fmt.Errorf("Could not get pipeline refs for pipeline with id \"%v\": %w", pipelineId, err)
	}
	defer refs.Close()

	for refs.Next() {
		var name string
		var hash string
		if err := refs.Scan(
			&name,
			&hash,
		); err != nil {
			return refsMap, err
		}

		refsMap[name] = hash
	}

	return refsMap, nil
}

func (db *Database) UpdatePipelineRefs(pipelineId uuid.UUID, refsMap map[string]string) error {

	query := `
INSERT INTO pipeline_refs(name, pipeline_id, hash)
VALUES($1, $2, $3)
ON CONFLICT (name)
DO
  UPDATE SET hash=$3;`

	for name, hash := range refsMap {
		_, err := db.Conn.Exec(context.Background(), query, name, pipelineId, hash)

		return err
	}
	return nil
}

func (db *Database) GetSecrets() ([]Secret, error) {
	query := `
SELECT id, name, secret
FROM secrets;`

	secrets := make([]Secret, 0)

	rows, err := db.Conn.Query(context.Background(), query)
	if err != nil {
		return secrets, fmt.Errorf("Could not query database for secrets: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var secret Secret
		var idStr string
		if err := rows.Scan(&idStr, &secret.Name, &secret.Secret); err != nil {
			return secrets, err
		}

		secret.Id, err = uuid.Parse(idStr)
		if err != nil {
			return secrets, err
		}
		secrets = append(secrets, secret)
	}

	return secrets, nil
}

func (db *Database) GetSecretById(id uuid.UUID) (Secret, error) {
	query := `
SELECT id, name, secret
FROM secrets
WHERE id=$1;`

	secret := Secret{
		Id: id,
	}

	err := db.Conn.QueryRow(context.Background(), query, id).Scan(&secret.Name, &secret.Secret)
	if err != nil {
		return secret, fmt.Errorf("Could not query database for secret with id %v: %w", id.String(), err)
	}

	return secret, nil
}

func (db *Database) CreateSecret(name string, secret string) (Secret, error) {
	s := Secret{}

	// validate that the secret is only A-Z or underscores and less than 256 characters
	if len(name) > 256 {
		return s, fmt.Errorf("secret name must be 256 characters or less")
	}

	validName := regexp.MustCompile(`[A-Z0-9_]+$`)
	if !validName.MatchString(name) {
		return s, fmt.Errorf("secren name must be made up of only uppercase letters, numbers, and underscores")
	}

	query := `
INSERT INTO secrets (id, name, secret)
VALUES (uuid_generate_v4(), $1, $2)
RETURNING id, name, secret;`

	var idStr string
	err := db.Conn.QueryRow(context.Background(), query, name, secret).Scan(&idStr, &s.Name, &s.Secret)
	if err != nil {
		return s, fmt.Errorf("Could not create secret: %w", err)
	}

	s.Id, err = uuid.Parse(idStr)
	if err != nil {
		return s, fmt.Errorf("Could not parse UUID generated by DB: %w", err)
	}

	return s, nil
}

func (db *Database) AssignSecretToPipeline(pipelineId uuid.UUID, secretId uuid.UUID) error {
	query := `
INSERT INTO pipeline_secret_mappings (pipeline, secret)
VALUES ($1, $2);`

	_, err := db.Conn.Exec(context.Background(), query, pipelineId, secretId)

	return err
}

func (db *Database) RemoveSecretFromPipeline(pipelineId uuid.UUID, secretId uuid.UUID) error {
	// TODO: implement this
	return fmt.Errorf("Not implemented")
}

func (db *Database) GetSecretsForPipeline(pipelineId uuid.UUID) ([]Secret, error) {
	query := `
SELECT
    secrets.id, secrets.name, secrets.secret
FROM
    secrets INNER JOIN pipeline_secret_mappings
    ON secrets.id = pipeline_secret_mappings.secret
WHERE
    pipeline_secret_mappings.pipeline=$1
;`

	secrets := make([]Secret, 0)

	rows, err := db.Conn.Query(context.Background(), query, pipelineId)
	if err != nil {
		return secrets, fmt.Errorf("Could not get secrets for pipeline with id \"%v\": %w", pipelineId, err)
	}
	defer rows.Close()

	for rows.Next() {
		var secret Secret
		var idStr string
		if err := rows.Scan(
			&idStr,
			&secret.Name,
			&secret.Secret,
		); err != nil {
			return secrets, err
		}

		secret.Id, err = uuid.Parse(idStr)
		if err != nil {
			return secrets, err
		}
		secrets = append(secrets, secret)
	}

	return secrets, nil
}

func (db *Database) GetRunners() ([]Runner, error) {
	query := `
SELECT id, name, token
FROM runners;`

	runners := make([]Runner, 0)

	rows, err := db.Conn.Query(context.Background(), query)
	if err != nil {
		return runners, fmt.Errorf("Could not query database for runners: %w", err)
	}
	defer rows.Close()

	for rows.Next() {
		var runner Runner
		var idStr string
		if err := rows.Scan(&idStr, &runner.Name, &runner.Token); err != nil {
			return runners, err
		}

		runner.Id, err = uuid.Parse(idStr)
		if err != nil {
			return runners, err
		}
		runners = append(runners, runner)
	}

	return runners, nil
}

func (db *Database) GetRunnerById(id uuid.UUID) (Runner, error) {
	query := `
SELECT name, token
FROM runners
WHERE id=$1;`

	runner := Runner{
		Id: id,
	}

	err := db.Conn.QueryRow(context.Background(), query, id).Scan(&runner.Name, &runner.Token)
	if err != nil {
		return runner, fmt.Errorf("Could not query database for runner with id %v: %w", id.String(), err)
	}

	return runner, nil
}

func (db *Database) CreateRunner(name string) (Runner, error) {
	s := Runner{}

	// validate that the runner name is only A-Z or underscores and less than 256 characters
	if len(name) > 256 {
		return s, fmt.Errorf("runner name must be 256 characters or less")
	}

	validName := regexp.MustCompile(`[A-Z0-9_]+$`)
	if !validName.MatchString(name) {
		return s, fmt.Errorf("runner name must be made up of only uppercase letters, numbers, and underscores")
	}

	query := `
INSERT INTO runners (id, name, token)
VALUES
  (
    uuid_generate_v4(),
    $1,
    (
      SELECT md5(random()::text)
    )
)
RETURNING id, name, token;`

	var idStr string
	err := db.Conn.QueryRow(context.Background(), query, name).Scan(&idStr, &s.Name, &s.Token)
	if err != nil {
		return s, fmt.Errorf("Could not create runner: %w", err)
	}

	s.Id, err = uuid.Parse(idStr)
	if err != nil {
		return s, fmt.Errorf("Could not parse UUID generated by DB: %w", err)
	}

	return s, nil
}