postgres

Documentation

Overview

Package postgres provides the Postgres data manipulation language (DML) for Vela.

Usage:

import "github.com/go-vela/server/database/dml/postgres"

Index

• Constants
• type Map
  • func NewMap() *Map
• type Service

Constants

const (
	// ListBuilds represents a query to
	// list all builds in the database.
	ListBuilds = `
SELECT *
FROM builds;
`

	// ListRepoBuilds represents a query to list
	// all builds for a repo_id in the database.
	ListRepoBuilds = `
SELECT *
FROM builds
WHERE repo_id = $1
ORDER BY id DESC
LIMIT $2
OFFSET $3;
`

	// ListRepoBuildsByEvent represents a query to select
	// a build for a repo_id with a specific event type
	// in the database.
	ListRepoBuildsByEvent = `
SELECT *
FROM builds
WHERE repo_id = $1
AND event = $2
ORDER BY number DESC
LIMIT $3
OFFSET $4;
`
	// ListOrgBuildsByEvent represents a joined query
	// between the builds & repos table to select
	// a build for an org with a specific event type
	// in the database.
	ListOrgBuildsByEvent = `` /* 150-byte string literal not displayed */

	// SelectRepoBuild represents a query to select
	// a build for a repo_id in the database.
	SelectRepoBuild = `
SELECT *
FROM builds
WHERE repo_id = $1
AND number = $2
LIMIT 1;
`

	// SelectLastRepoBuild represents a query to select
	// the last build for a repo_id in the database.
	SelectLastRepoBuild = `
SELECT *
FROM builds
WHERE repo_id = $1
ORDER BY number DESC
LIMIT 1;
`
	// ListOrgBuilds represents a joined query
	// between the builds & repos table to select
	// the last build for a org name in the database.
	ListOrgBuilds = `` /* 128-byte string literal not displayed */

	// SelectLastRepoBuildByBranch represents a query to
	// select the last build for a repo_id and branch name
	// in the database.
	SelectLastRepoBuildByBranch = `
SELECT *
FROM builds
WHERE repo_id = $1
AND branch = $2
ORDER BY number DESC
LIMIT 1;
`

	// SelectBuildsCount represents a query to select
	// the count of builds in the database.
	SelectBuildsCount = `
SELECT count(*) as count
FROM builds;
`

	// SelectRepoBuildCount represents a query to select
	// the count of builds for a repo_id in the database.
	SelectRepoBuildCount = `
SELECT count(*) as count
FROM builds
WHERE repo_id = $1;
`
	// SelectOrgBuildCount represents a joined query
	// between the builds & repos table to select
	// the count of builds for an org name in the database.
	SelectOrgBuildCount = `
SELECT count(*) as count
FROM builds JOIN repos
ON repos.id = builds.repo_id 
WHERE repos.org = $1;
`
	// SelectRepoBuildCountByEvent represents a query to select
	// the count of builds for by repo and event type in the database.
	SelectRepoBuildCountByEvent = `
SELECT count(*) as count
FROM builds
WHERE repo_id = $1
AND event = $2;
`

	// SelectOrgBuildCountByEvent represents a joined query
	// between the builds & repos table to select
	// the count of builds for by org name and event type in the database.
	SelectOrgBuildCountByEvent = `
SELECT count(*) as count
FROM builds JOIN repos
ON repos.id = builds.repo_id 
WHERE repos.org = $1
AND event = $2;
`

	// SelectBuildsCountByStatus represents a query to select
	// the count of builds for a status in the database.
	SelectBuildsCountByStatus = `
SELECT count(*) as count
FROM builds
WHERE status = $1;
`

	// DeleteBuild represents a query to
	// remove a build from the database.
	DeleteBuild = `
DELETE
FROM builds
WHERE id = $1;
`
)

const (
	// ListHooks represents a query to
	// list all webhooks in the database.
	ListHooks = `
SELECT *
FROM hooks;
`

	// ListRepoHooks represents a query to list
	// all webhooks for a repo_id in the database.
	ListRepoHooks = `
SELECT *
FROM hooks
WHERE repo_id = $1
ORDER BY id DESC
LIMIT $2
OFFSET $3;
`

	// SelectRepoHookCount represents a query to select
	// the count of webhooks for a repo_id in the database.
	SelectRepoHookCount = `
SELECT count(*) as count
FROM hooks
WHERE repo_id = $1;
`

	// SelectRepoHook represents a query to select
	// a webhook for a repo_id in the database.
	SelectRepoHook = `
SELECT *
FROM hooks
WHERE repo_id = $1
AND number = $2
LIMIT 1;
`

	// SelectLastRepoHook represents a query to select
	// the last hook for a repo_id in the database.
	SelectLastRepoHook = `
SELECT *
FROM hooks
WHERE repo_id = $1
ORDER BY number DESC
LIMIT 1;
`

	// DeleteHook represents a query to
	// remove a webhook from the database.
	DeleteHook = `
DELETE
FROM hooks
WHERE id = $1;
`
)

const (
	// ListLogs represents a query to
	// list all logs in the database.
	ListLogs = `
SELECT *
FROM logs;
`

	// ListBuildLogs represents a query to list
	// all logs for a build_id in the database.
	ListBuildLogs = `
SELECT *
FROM logs
WHERE build_id = $1
ORDER BY step_id ASC;
`

	// SelectStepLog represents a query to select
	// a log for a step_id in the database.
	SelectStepLog = `
SELECT *
FROM logs
WHERE step_id = $1
LIMIT 1;
`

	// SelectServiceLog represents a query to select
	// a log for a service_id in the database.
	SelectServiceLog = `
SELECT *
FROM logs
WHERE service_id = $1
LIMIT 1;
`

	// DeleteLog represents a query to
	// remove a log from the database.
	DeleteLog = `
DELETE
FROM logs
WHERE id = $1;
`
)

const (
	// ListRepos represents a query to
	// list all repos in the database.
	ListRepos = `
SELECT *
FROM repos;
`

	// ListUserRepos represents a query to list
	// all repos for a user_id in the database.
	ListUserRepos = `
SELECT *
FROM repos
WHERE user_id = $1
ORDER BY id DESC
LIMIT $2
OFFSET $3;
`

	// ListOrgRepos represents a query to list
	// all repos for an org in the database.
	ListOrgRepos = `
SELECT *
FROM repos
WHERE org = $1
ORDER BY id DESC
LIMIT $2
OFFSET $3;
`

	// SelectRepo represents a query to select a
	// repo for an org and name in the database.
	SelectRepo = `
SELECT *
FROM repos
WHERE org = $1
AND name = $2
LIMIT 1;
`

	// SelectUserReposCount represents a query to select
	// the count of repos for a user_id in the database.
	SelectUserReposCount = `
SELECT count(*) as count
FROM repos
WHERE user_id = $1;
`

	// SelectReposCount represents a query to select
	// the count of repos in the database.
	SelectReposCount = `
SELECT count(*) as count
FROM repos;
`

	// DeleteRepo represents a query to
	// remove a repo from the database.
	DeleteRepo = `
DELETE
FROM repos
WHERE id = $1;
`
)

const (
	// ListSecrets represents a query to
	// list all secrets in the database.
	//
	// nolint: gosec // ignore false positive
	ListSecrets = `
SELECT *
FROM secrets;
`

	// ListOrgSecrets represents a query to list all
	// secrets for a type and org in the database.
	//
	// nolint: gosec // ignore false positive
	ListOrgSecrets = `
SELECT *
FROM secrets
WHERE type = 'org'
AND org = $1
ORDER BY id DESC
LIMIT $2
OFFSET $3;
`

	// ListRepoSecrets represents a query to list all
	// secrets for a type, org and repo in the database.
	//
	// nolint: gosec // ignore false positive
	ListRepoSecrets = `
SELECT *
FROM secrets
WHERE type = 'repo'
AND org = $1
AND repo = $2
ORDER BY id DESC
LIMIT $3
OFFSET $4;
`

	// ListSharedSecrets represents a query to list all
	// secrets for a type, org and team in the database.
	//
	// nolint: gosec // ignore false positive
	ListSharedSecrets = `
SELECT *
FROM secrets
WHERE type = 'shared'
AND org = $1
AND team = $2
ORDER BY id DESC
LIMIT $3
OFFSET $4;
`

	// SelectOrgSecretsCount represents a query to select the
	// count of org secrets for an org in the database.
	//
	// nolint: gosec // ignore false positive
	SelectOrgSecretsCount = `
SELECT count(*) as count
FROM secrets
WHERE type = 'org'
AND org = $1;
`

	// SelectRepoSecretsCount represents a query to select the
	// count of repo secrets for an org and repo in the database.
	//
	// nolint: gosec // ignore false positive
	SelectRepoSecretsCount = `
SELECT count(*) as count
FROM secrets
WHERE type = 'repo'
AND org = $1
AND repo = $2;
`

	// SelectSharedSecretsCount represents a query to select the
	// count of shared secrets for an org and repo in the database.
	//
	// nolint: gosec // ignore false positive
	SelectSharedSecretsCount = `
SELECT count(*) as count
FROM secrets
WHERE type = 'shared'
AND org = $1
AND team = $2;
`

	// SelectOrgSecret represents a query to select a
	// secret for an org and name in the database.
	//
	// nolint: gosec // ignore false positive
	SelectOrgSecret = `
SELECT *
FROM secrets
WHERE type = 'org'
AND org = $1
AND name = $2
LIMIT 1;
`

	// SelectRepoSecret represents a query to select a
	// secret for an org, repo and name in the database.
	//
	// nolint: gosec // ignore false positive
	SelectRepoSecret = `
SELECT *
FROM secrets
WHERE type = 'repo'
AND org = $1
AND repo = $2
AND name = $3
LIMIT 1;
`

	// SelectSharedSecret represents a query to select a
	// secret for an org, team and name in the database.
	//
	// nolint: gosec // ignore false positive
	SelectSharedSecret = `
SELECT *
FROM secrets
WHERE type = 'shared'
AND org = $1
AND team = $2
AND name = $3
LIMIT 1;
`

	// DeleteSecret represents a query to
	// remove a secret from the database.
	//
	// nolint: gosec // ignore false positive
	DeleteSecret = `
DELETE
FROM secrets
WHERE id = $1;
`
)

const (
	// ListServices represents a query to
	// list all services in the database.
	ListServices = `
SELECT *
FROM services;
`

	// ListBuildServices represents a query to list
	// all services for a build_id in the database.
	ListBuildServices = `
SELECT *
FROM services
WHERE build_id = $1
ORDER BY id DESC
LIMIT $2
OFFSET $3;
`

	// SelectBuildServicesCount represents a query to select
	// the count of services for a build_id in the database.
	SelectBuildServicesCount = `
SELECT count(*) as count
FROM services
WHERE build_id = $1
`

	// SelectServiceImagesCount represents a query to select
	// the count of an images appearances in the database.
	SelectServiceImagesCount = `
SELECT image, count(image) as count
FROM services
GROUP BY image
`

	// SelectServiceStatusesCount represents a query to select
	// the count of service status appearances in the database.
	SelectServiceStatusesCount = `
SELECT status, count(status) as count
FROM services
GROUP BY status;
`

	// SelectBuildService represents a query to select a
	// service for a build_id and number in the database.
	SelectBuildService = `
SELECT *
FROM services
WHERE build_id = $1
AND number = $2
LIMIT 1;
`

	// DeleteService represents a query to
	// remove a service from the database.
	DeleteService = `
DELETE
FROM services
WHERE id = $1;
`
)

const (
	// ListSteps represents a query to
	// list all steps in the database.
	ListSteps = `
SELECT *
FROM steps;
`

	// ListBuildSteps represents a query to list
	// all steps for a build_id in the database.
	ListBuildSteps = `
SELECT *
FROM steps
WHERE build_id = $1
ORDER BY id DESC
LIMIT $2
OFFSET $3;
`

	// SelectBuildStepsCount represents a query to select
	// the count of steps for a build_id in the database.
	SelectBuildStepsCount = `
SELECT count(*) as count
FROM steps
WHERE build_id = $1
`

	// SelectStepImagesCount represents a query to select
	// the count of an images appearances in the database.
	SelectStepImagesCount = `
SELECT image, count(image) as count
FROM steps
GROUP BY image;
`

	// SelectStepStatusesCount represents a query to select
	// the count of a statuses appearances in the database.
	SelectStepStatusesCount = `
SELECT status, count(status) as count
FROM steps
GROUP BY status;
`

	// SelectBuildStep represents a query to select a
	// step for a build_id and number in the database.
	SelectBuildStep = `
SELECT *
FROM steps
WHERE build_id = $1
AND number = $2
LIMIT 1;
`

	// DeleteStep represents a query to
	// remove a step from the database.
	DeleteStep = `
DELETE
FROM steps
WHERE id = $1;
`
)

const (
	// ListUsers represents a query to
	// list all users in the database.
	ListUsers = `
SELECT *
FROM users;
`

	// ListLiteUsers represents a query to
	// list all lite users in the database.
	ListLiteUsers = `
SELECT id, name
FROM users
ORDER BY id DESC
LIMIT $1
OFFSET $2;
`

	// SelectUser represents a query to select
	// a user for an id in the database.
	SelectUser = `
SELECT *
FROM users
WHERE id = $1
LIMIT 1;
`

	// SelectUserName represents a query to select
	// a user for a name in the database.
	SelectUserName = `
SELECT *
FROM users
WHERE name = $1
LIMIT 1;
`

	// SelectUsersCount represents a query to select
	// the count of users in the database.
	SelectUsersCount = `
SELECT count(*) as count
FROM users;
`

	// SelectRefreshToken represents a query to select
	// a user for a refresh_token in the database.
	//
	// nolint: gosec // ignore false positive
	SelectRefreshToken = `
SELECT *
FROM users
WHERE refresh_token = $1
LIMIT 1;
`

	// DeleteUser represents a query to
	// remove a user from the database.
	DeleteUser = `
DELETE
FROM users
WHERE id = $1;
`
)

const (
	// ListWorkers represents a query to
	// list all workers in the database.
	ListWorkers = `
SELECT *
FROM workers;
`

	// SelectWorkersCount represents a query to select the
	// count of workers in the database.
	SelectWorkersCount = `
SELECT count(*) as count
FROM workers;
`

	// SelectWorker represents a query to select a
	// worker by hostname in the database.
	SelectWorker = `
SELECT *
FROM workers
WHERE hostname = $1
LIMIT 1;
`

	// SelectWorkerByAddress represents a query to select a
	// worker by address in the database.
	SelectWorkerByAddress = `
SELECT *
FROM workers
WHERE address = $1
LIMIT 1;
`

	// DeleteWorker represents a query to
	// remove a worker from the database.
	DeleteWorker = `
DELETE
FROM workers
WHERE id = $1;
`
)

Types

type Map

type Map struct {
	BuildService   *Service
	HookService    *Service
	LogService     *Service
	RepoService    *Service
	SecretService  *Service
	ServiceService *Service
	StepService    *Service
	UserService    *Service
	WorkerService  *Service
}

Map represents the Postgres DML services in a struct for lookups.

func NewMap

func NewMap() *Map

NewMap returns the Postgres Map for DML lookups.

type Service

type Service struct {
	List   map[string]string
	Select map[string]string
	Delete string
}

Service represents the Postgres DML for a table in the database.