querygen

func CursorCondition ¶

func CursorCondition(table string) string

CursorCondition renders the keyset predicate: rows strictly after the cursor.

An absent cursor coalesces to the empty string rather than being handled by a second query, which is what keeps the first page and the fiftieth page the same statement. It works because id sorts by creation time and no id is empty.

func CursorLimitClause ¶

func CursorLimitClause(table string) string

CursorLimitClause renders the ordering and page size a keyset walk needs.

The ORDER BY is not decoration. A cursor names a position in an order, so a paginated query without the matching ORDER BY returns rows in whatever order the planner found convenient, and the next page's cursor names a position in an order that no longer holds — pages that skip rows and repeat others, with nothing reporting an error.

func CursorPaginationFragment ¶

func CursorPaginationFragment(table string) string

CursorPaginationFragment renders the cursor predicate and the ordering together, for a query that does its own filtering and only wants the keyset half.

The predicate arrives prefixed with AND, because the only place it belongs is the tail of a WHERE clause that already has one.

func FilterConditions ¶

func FilterConditions(table string, columns []string, conditions ...string) string

FilterConditions renders a filtered list query's WHERE clause: the filtering.QueryFilter window over whichever of the convention columns the table has, then any conditions the caller adds, then the cursor predicate.

It is the whole clause, not an addendum. A caller that opens its own WHERE with archived_at IS NULL and appends this one gets a query where include_archived cannot do anything, since the first predicate has already excluded every row the flag would admit — and nothing about such a query looks wrong. Owning the clause is what keeps that from being expressible.

conditions are rendered verbatim, one per line. They are the caller's SQL: this package does not parse them and cannot vet them.

package main

import (
	"fmt"

	"github.com/primandproper/platform-go/v10/database/querygen"
)

func main() {
	columns := []string{querygen.IDColumn, querygen.CreatedAtColumn, querygen.ArchivedAtColumn}

	fmt.Println(querygen.FilterConditions("things", columns, "things.name "+querygen.ILIKECondition("name_query")))

}

Output:
things.created_at > COALESCE(sqlc.narg(created_after), (SELECT NOW() - '999 years'::INTERVAL))
	AND things.created_at < COALESCE(sqlc.narg(created_before), (SELECT NOW() + '999 years'::INTERVAL))
	AND (COALESCE(sqlc.narg(include_archived), false)::boolean OR things.archived_at IS NULL)
	AND things.name ILIKE '%' || sqlc.arg(name_query)::text || '%'
	AND things.id > COALESCE(sqlc.narg(cursor), '')

func FilterCountSelect ¶

func FilterCountSelect(table string, columns, joins []string, conditions ...string) string

FilterCountSelect renders the scalar subquery counting the rows the same filter matches, aliased filtered_count.

It is a subquery in the SELECT list rather than a second round trip because filtering.QueryFilteredResult wants the page and its counts together, and a count issued separately counts a table that has moved on since the page was read.

The cursor predicate is deliberately absent: filtered_count answers "how many rows match this filter", which does not change as the caller walks through them. Including it would count the rows remaining after the cursor, and a total that shrinks with every page is a progress bar that never fills.

Because the count rides on the rows, a page with no rows carries no count. A caller reporting counts for an empty page has to supply the zero itself, which is what filtering.NewQueryFilteredResult taking them as arguments allows.

func ForInsert ¶

func ForInsert(columns []string, exceptions ...string) []string

ForInsert returns the columns an INSERT takes values for: everything but the database-owned ones, and anything else the caller names.

Order is preserved, because an INSERT's column list and its VALUES list are positional and have to be rendered from the same slice.

func ForUpdate ¶

func ForUpdate(columns []string, exceptions ...string) []string

ForUpdate returns the columns an UPDATE assigns: ForInsert's set, less the id.

The id is excluded because an UPDATE keys on it. A SET that assigns the column the WHERE matches on is a row that changes its own identity mid-statement, which is legal SQL and never what anyone meant.

func ILIKECondition ¶

func ILIKECondition(argument string) string

ILIKECondition renders a case-insensitive substring match against a bound argument, for a search query's own WHERE predicate.

The argument is bound and the wildcards are concatenated around it, rather than the caller passing '%term%' — a caller assembling the pattern is a caller who can forget to escape a literal '%' in a user's search term, which turns a search for "50%" into a search for everything.

func Qualify ¶

func Qualify(table, column string) string

Qualify renders a column as table.column.

func QualifyAll ¶

func QualifyAll(table string, columns []string) []string

QualifyAll renders every column as table.column, preserving order.

func ReindexScanQuery ¶

func ReindexScanQuery(table string) string

ReindexScanQuery builds the keyset walk a search reindex reads its source through.

It returns IDs rather than rows on purpose. A Scanner and a Fetcher both have to produce the same document for the same row, and the cheapest way to guarantee that is to have one of them call the other: the scan names the next page of IDs and the fetch — the same one the change feed uses — turns them into documents. Selecting rows here would be a second row-to-document transform, and two transforms that are supposed to agree are two transforms that can drift.

The ordering is a byte comparison, COLLATE "C", not the database's default collation. search/sync requires ascending byte order because the pruning half of a reindex merges this stream against the index's own stream of IDs, and Postgres's en_US.UTF-8 sorts case-insensitively and ignores punctuation — a different order. Two ordered streams merged under disagreeing orders do not fail; they conclude that live documents are absent from the source and delete them. The Reindexer verifies the order it is given for the same reason.

func RenderFile ¶

func RenderFile(queries []*Query) string

RenderFile assembles queries into the byte-exact contents of one sqlc input file: each rendered query, one blank line between them, one trailing newline.

Trailing whitespace is stripped from every line. That is not cosmetic. A generator is usually run twice — once to write the files and once, in CI, to check that the committed files still match — and the check is a byte comparison. Composing fragments into a statement is exactly the operation that leaves a stray space at the end of a line, so normalizing here is what keeps the check answering a question about SQL rather than about whitespace.

package main

import (
	"fmt"
	"strings"

	"github.com/primandproper/platform-go/v10/database/querygen"
)

func main() {
	queries := querygen.StandardCRUD("things", []string{querygen.IDColumn, querygen.ArchivedAtColumn},
		querygen.WithEntity("Thing", "Things"))

	file := querygen.RenderFile(queries)

	// Just the annotations, to keep the example short.
	for line := range strings.SplitSeq(file, "\n") {
		if strings.HasPrefix(line, "-- name:") {
			fmt.Println(line)
		}
	}

}

Output:
-- name: CreateThing :exec
-- name: GetThing :one
-- name: CheckThingExistence :one
-- name: ListThings :many
-- name: ArchiveThing :execrows

func TotalCountSelect ¶

func TotalCountSelect(table string, columns, joins []string, conditions ...string) string

TotalCountSelect renders the scalar subquery counting the rows in scope regardless of the filter window, aliased total_count.

It applies the same archived handling as the filter — not an unconditional archived_at IS NULL — so that filtered_count can never exceed total_count. A pair of counts where the subset is larger than the set is the kind of number that gets noticed a week later by whoever is reconciling them.