postgres

Documentation

Overview

Package postgres connects to and interacts with a PostgreSQL database server. The functions are specific to the Postgres platform rather than more generic or interchangeable SQL statements.

The postgres/models directory is generated by SQLBoiler and should not be modified.

Index

• Constants
• Variables
• func Columns() []string
• func Connections(db *sql.DB) (int64, int64, error)
• func Open() (*sql.DB, error)
• func ScenerSQL(name string) string
• func SetFilesize0() string
• func SetUpper(column string) string
• func Stat() []string
• type Connection
  • func New() (Connection, error)
  • func (c Connection) Validate(logger *zap.SugaredLogger) error
• type Role
  • func Roles() Role
  • func (r Role) Distinct() SQL
• type SQL
  • func Artists() SQL
  • func BBSsAlphabetical() SQL
  • func BBSsOldest() SQL
  • func BBSsProlific() SQL
  • func Coders() SQL
  • func FTPsAlphabetical() SQL
  • func MagazinesAlphabetical() SQL
  • func MagazinesOldest() SQL
  • func Musicians() SQL
  • func Releasers() SQL
  • func ReleasersAlphabetical() SQL
  • func ReleasersOldest() SQL
  • func ReleasersProlific() SQL
  • func Sceners() SQL
  • func SimilarInitialism(like ...string) SQL
  • func SimilarToMagazine(like ...string) SQL
  • func SimilarToReleaser(like ...string) SQL
  • func SumGroup() SQL
  • func SumPlatform() SQL
  • func SumSection() SQL
  • func Summary() SQL
  • func Writers() SQL
• type Version
  • func (v *Version) Query(db *sql.DB) error
  • func (v *Version) String() string

Constants

const (
	// DefaultURL is an example PostgreSQL connection string, it must not be used in production.
	DefaultURL = "postgres://root:example@localhost:5432/defacto2_ps" //nolint: gosec
	// DriverName of the database.
	DriverName = "pgx"
	// Protocol of the database driver.
	Protocol = "postgres"
)

const (
	// TotalCnt is a partial SQL statement to count the number of records.
	TotalCnt = "COUNT(*) AS count_total"
	// SumSize is a partial SQL statement to sum the filesize values of multiple records.
	SumSize = "SUM(filesize) AS size_total"
	// MinYear is a partial SQL statement to select the minimum year value.
	MinYear = "MIN(date_issued_year) AS min_year"
	// MaxYear is a partial SQL statement to select the maximum year value.
	MaxYear = "MAX(date_issued_year) AS max_year"
	// Ver is a SQL statement to select the version of the PostgreSQL database server in use.
	Ver = "SELECT version();"
)

Variables

var (
	ErrDB  = errors.New("database connection is nil")
	ErrEnv = errors.New("environment variable probably contains an invalid value")
	ErrZap = errors.New("zap logger instance is nil")
)

Functions

func Columns

func Columns() []string

Columns returns a list of column selections used for filtering and statistics.

func Connections

func Connections(db *sql.DB) (int64, int64, error)

Connections returns the number of active connections and the maximum allowed connections.

func Open

func Open() (*sql.DB, error)

Opens a new connection to the PostgreSQL database. Only one connection is needed for the entire application as it is thread-safe and can be used repeatedly used.

The connection should be closed after the application exits.

func ScenerSQL

func ScenerSQL(name string) string

ScenerSQL is the SQL query for getting sceners.

func SetFilesize0

func SetFilesize0() string

SetFilesize0 is an SQL statement to update filesize column NULLs with 0 values. This is a fix for the error: failed to bind pointers to obj: sql: Scan error on column index 2, name "size_total": converting NULL to int is unsupported.

func SetUpper

func SetUpper(column string) string

SetUpper is an SQL statement to update a column with uppercase values.

func Stat

func Stat() []string

Stat returns the SumSize and TotalCnt column selections.

Types

type Connection

type Connection struct {
	URL string `env:"D2_DATABASE_URL"` // unsetting this value will cause the default to be used after a single use
}

Connection details of the PostgreSQL database connection.

func New

func New() (Connection, error)

New initializes the connection with default values or values from the environment.

func (Connection) Validate

func (c Connection) Validate(logger *zap.SugaredLogger) error

Validate the connection URL and print any issues to the logger.

type Role

type Role string // Role is a scener attribution used in the database.

const (
	Writer   Role = "(upper(credit_text))"         // Writer or author of a document.
	Artist   Role = "(upper(credit_illustration))" // Artist or illustrator of an image or artwork.
	Coder    Role = "(upper(credit_program))"      // Coder or programmer of a program or application.
	Musician Role = "(upper(credit_audio))"        // Musician or composer of a music or audio track.
)

func Roles

func Roles() Role

Roles returns all of the sceners reguardless of the attribution.

func (Role) Distinct

func (r Role) Distinct() SQL

type SQL

type SQL string // SQL is a raw query statement for PostgreSQL.

func Artists

func Artists() SQL

Artists selects a list of distinct artists.

func BBSsAlphabetical

func BBSsAlphabetical() SQL

BBSsAlphabetical selects a list of distinct BBS names.

func BBSsOldest

func BBSsOldest() SQL

BBSsOldest selects a list of distinct releasers or groups, only showing BBS sites and ordered by the file count.

func BBSsProlific

func BBSsProlific() SQL

BBSsProlific selects a list of distinct releasers or groups, only showing BBS sites and ordered by the file count.

func Coders

func Coders() SQL

Coders selects a list of distinct coders.

func FTPsAlphabetical

func FTPsAlphabetical() SQL

FTPsAlphabetical selects a list of distinct FTP site names.

func MagazinesAlphabetical

func MagazinesAlphabetical() SQL

MagazinesAlphabetical selects a list of distinct magazine titles.

func MagazinesOldest

func MagazinesOldest() SQL

MagazinesOldest selects a list of distinct releasers or groups, only showing magazines and ordered by the file count.

func Musicians

func Musicians() SQL

Musicians selects a list of distinct musicians.

func Releasers

func Releasers() SQL

func ReleasersAlphabetical

func ReleasersAlphabetical() SQL

ReleasersAlphabetical selects a list of distinct releasers or groups, excluding BBS and FTP sites.

func ReleasersOldest

func ReleasersOldest() SQL

ReleasersOldest selects a list of distinct releasers or groups, excluding BBS and FTP sites and ordered by the oldest year.

func ReleasersProlific

func ReleasersProlific() SQL

ReleasersProlific selects a list of distinct releasers or groups, excluding BBS and FTP sites and ordered by the file count.

func Sceners

func Sceners() SQL

Sceners selects a list of distinct sceners.

func SimilarInitialism

func SimilarInitialism(like ...string) SQL

SimilarInitialism selects an exact list of distinct releasers or groups, like the query strings and ordered by the file count.

func SimilarToMagazine

func SimilarToMagazine(like ...string) SQL

SimilarToMagazine selects a similar list of distinct magazine titles, like the query strings and ordered by the file count.

func SimilarToReleaser

func SimilarToReleaser(like ...string) SQL

SimilarToReleaser selects a similar list of distinct releasers or groups, like the query strings and ordered by the file count.

func SumGroup

func SumGroup() SQL

SumGroup is an SQL statement to sum the filesizes of records matching the group.

func SumPlatform

func SumPlatform() SQL

SumPlatform is an SQL statement to sum the filesizes of records matching the platform.

func SumSection

func SumSection() SQL

SumSection is an SQL statement to sum the filesizes of records matching the section.

func Summary

func Summary() SQL

Summary is an SQL statement to count the number of files, sum the filesize, select the oldest year and the newest year.

func Writers

func Writers() SQL

Writers selects a list of distinct writers.

type Version

type Version string // Version of the PostgreSQL database server in use.

func (*Version) Query

func (v *Version) Query(db *sql.DB) error

Query the database version.

func (*Version) String

func (v *Version) String() string