Documentation
¶
Overview ¶
Package dialect isolates the SQL differences between the database engines the CMS supports.
Stores write Postgres-flavoured SQL — $1 placeholders, ON CONFLICT ... DO UPDATE, RETURNING — because it is the more expressive of the two dialects and reads the same as the schema. A Dialect translates that canonical form on its way to the driver, so nothing above this package needs to know which engine it is talking to.
Index ¶
- type Dialect
- type Execer
- type MySQL
- func (MySQL) CaseInsensitiveLike(col, placeholder string) string
- func (MySQL) Distinct(a, b string) string
- func (m MySQL) InsertID(ctx context.Context, ex Execer, query string, args ...any) (int64, error)
- func (MySQL) JSONText(col string) string
- func (MySQL) Lock(ctx context.Context, ex Execer, key string) (func(), error)
- func (MySQL) MigrationDir() string
- func (MySQL) Name() string
- func (MySQL) Quote(ident string) string
- func (MySQL) Rewrite(query string, args []any) (string, []any)
- func (MySQL) SearchConfig(locale string) string
- func (MySQL) SearchIndexWrite(cfg, title, summary, body string) (string, string)
- func (MySQL) SearchMatch(cfg, q string) (string, string)
- func (MySQL) SearchMinWordLen() int
- func (MySQL) SearchQuery(terms []SearchTerm) string
- func (MySQL) SplitStatements(script string) []string
- type Postgres
- func (Postgres) CaseInsensitiveLike(col, placeholder string) string
- func (Postgres) Distinct(a, b string) string
- func (Postgres) InsertID(ctx context.Context, ex Execer, query string, args ...any) (int64, error)
- func (Postgres) JSONText(col string) string
- func (Postgres) Lock(ctx context.Context, ex Execer, key string) (func(), error)
- func (Postgres) MigrationDir() string
- func (Postgres) Name() string
- func (Postgres) Quote(ident string) string
- func (Postgres) Rewrite(query string, args []any) (string, []any)
- func (Postgres) SearchConfig(locale string) string
- func (Postgres) SearchIndexWrite(cfg, title, summary, body string) (string, string)
- func (Postgres) SearchMatch(cfg, q string) (string, string)
- func (Postgres) SearchMinWordLen() int
- func (Postgres) SearchQuery(terms []SearchTerm) string
- func (Postgres) SplitStatements(script string) []string
- type Result
- type Row
- type SearchTerm
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
This section is empty.
Types ¶
type Dialect ¶
type Dialect interface {
// Name identifies the engine, e.g. "postgres" or "mysql".
Name() string
// Rewrite translates a canonical statement and its arguments into the
// form this engine accepts. It is applied to every statement on its way
// to the driver, so it must be safe on SQL that needs no translation.
Rewrite(query string, args []any) (string, []any)
// InsertID runs an INSERT that has no RETURNING clause and reports the
// generated primary key. Postgres appends RETURNING id and reads the
// result; MySQL uses LastInsertId, which Postgres does not support.
InsertID(ctx context.Context, ex Execer, query string, args ...any) (int64, error)
// CaseInsensitiveLike renders a case-insensitive LIKE comparison of col
// against an already-rendered placeholder.
CaseInsensitiveLike(col, placeholder string) string
// JSONText renders a JSON column as text, for the comparisons that treat
// a JSON value as an opaque string.
JSONText(col string) string
// Quote escapes an identifier that collides with a reserved word —
// cms_settings.key, which MySQL reserves. The two engines disagree on
// the quoting character, so there is no shared spelling.
Quote(ident string) string
// Distinct renders a NULL-safe inequality between two columns —
// Postgres's IS DISTINCT FROM, which MySQL and MariaDB lack.
Distinct(a, b string) string
// SplitStatements splits a migration file into individually executable
// statements. Postgres can take a whole file at once; MySQL's driver
// rejects multiple statements per Exec.
SplitStatements(script string) []string
// Lock takes an advisory lock serializing concurrent migration runs, and
// returns the function that releases it.
Lock(ctx context.Context, ex Execer, key string) (unlock func(), err error)
// MigrationDir is the subdirectory of migrations/sql holding this
// engine's schema.
MigrationDir() string
// SearchIndexWrite renders the extra column cms_search_docs needs when
// a document is written, and the expression that fills it, given the
// placeholders holding the text search configuration and the
// document's three indexed fields. An engine whose full-text index
// reads those columns directly — MySQL's does — returns two empty
// strings, and the INSERT is built without them.
SearchIndexWrite(cfg, title, summary, body string) (col, expr string)
// SearchMatch renders the WHERE condition that selects the documents
// matching a query, and the expression that scores them, given the
// placeholders holding the configuration and the rendered query.
// Higher scores are better matches on both engines.
SearchMatch(cfg, q string) (where, rank string)
// SearchQuery renders parsed query terms into the string this engine's
// match expression takes. The parsing happens once, above this
// package (content.ParseSearchQuery), so the two engines are asked the
// same question in their own words rather than each being handed
// whatever the visitor typed.
SearchQuery(terms []SearchTerm) string
// SearchConfig names the text search configuration to index and query
// a locale's documents with. Only Postgres has such a thing; MySQL
// returns "" and ignores the parameter it is passed.
SearchConfig(locale string) string
// SearchMinWordLen is the shortest word this engine's full-text index
// holds. A term below it is in no index and so matches nothing, which
// is why content.Store.Search matches short terms with LIKE instead —
// searching a site that writes about AI or 3D printing should not
// depend on which database is underneath.
SearchMinWordLen() int
}
Dialect translates canonical (Postgres-flavoured) SQL for one engine.
type Execer ¶
type Execer interface {
ExecContext(ctx context.Context, query string, args ...any) (Result, error)
QueryRowContext(ctx context.Context, query string, args ...any) Row
}
Execer is the subset of a database handle the dialect helpers need. Both *sql.DB and *sql.Tx satisfy it, so an insert works the same inside and outside a transaction.
type MySQL ¶
type MySQL struct{}
MySQL is the dialect for MySQL 8.0.31+ and MariaDB 10.6+.
The 8.0.31 floor comes from EXCEPT, which the change-detection query in content/block.go uses and which MySQL only gained in that release. MariaDB has had it since 10.3.
func (MySQL) CaseInsensitiveLike ¶
CaseInsensitiveLike is a plain LIKE: the default collations on both engines compare case-insensitively.
func (MySQL) Distinct ¶
Distinct uses the NULL-safe equality operator negated, since neither engine has IS DISTINCT FROM.
func (MySQL) InsertID ¶
InsertID uses LastInsertId, since neither engine supports RETURNING on every insert shape the CMS needs.
func (MySQL) JSONText ¶
JSONText returns the column as-is. MySQL's JSON and MariaDB's LONGTEXT alias both compare as text without a cast.
func (MySQL) Lock ¶
Lock takes a named advisory lock with GET_LOCK. The timeout is generous: it only has to outlast another instance applying the same migrations.
func (MySQL) MigrationDir ¶
func (MySQL) Quote ¶
Quote wraps an identifier in backticks. MySQL only accepts double quotes as identifier quoting under ANSI_QUOTES, which is not the default.
func (MySQL) SearchConfig ¶ added in v1.2.1
SearchConfig returns "": neither engine has per-language text search configurations, so the parameter the query passes is unused.
func (MySQL) SearchIndexWrite ¶ added in v1.2.1
SearchIndexWrite adds nothing to the insert: the FULLTEXT indexes on cms_search_docs read title, summary and body directly, so there is no derived column to keep in step with them.
func (MySQL) SearchMatch ¶ added in v1.2.1
SearchMatch scores a document twice: once across all three indexed fields, and once against the title alone, which is why the table carries a second FULLTEXT index over that column by itself. Adding the two is how the title outranks the body here — Postgres does the same job with setweight, but MATCH() can only name the columns some one index was built on, so the weighting has to happen in the query.
The multiplier is chosen for the same reason Postgres's weights are: a page *about* the thing should beat a page that mentions it, by a margin that a longer body cannot close.
Boolean mode rather than natural language mode, for two reasons. Natural language mode drops any word appearing in more than half the rows — which on a small site is most of its vocabulary, and produces the baffling result that the more a site writes about something the less findable it is. And boolean mode is the only one that can express the AND-by-default and exclusion that SearchQuery renders.
func (MySQL) SearchMinWordLen ¶ added in v1.2.1
SearchMinWordLen is 3, the innodb_ft_min_token_size default on both engines: shorter words are never put in the index in the first place. The variable is the server operator's and can be lowered, but a module that ships a schema cannot count on that having been done, and reading it per query to find out would cost more than the LIKE it saves.
func (MySQL) SearchQuery ¶ added in v1.2.1
func (MySQL) SearchQuery(terms []SearchTerm) string
SearchQuery renders terms in boolean-mode syntax. Every included term gets a "+": without one, boolean mode treats a word as optional, so searching for two words would return the pages holding either. AND is what a search box means and what Postgres's websearch does.
Nothing else reaches the engine. The terms arrive already stripped of boolean mode's operator characters (see content.ParseSearchQuery), so a visitor cannot type a "(" and get a syntax error, or a "*" and get a prefix search they did not ask for.
func (MySQL) SplitStatements ¶
SplitStatements breaks a migration into single statements, which the driver requires unless multiStatements is enabled — and that conflicts with prepared statements.
type Postgres ¶
type Postgres struct{}
Postgres is the canonical dialect: the SQL stores write is already Postgres SQL, so nearly every method here is the identity.
func (Postgres) CaseInsensitiveLike ¶
CaseInsensitiveLike uses ILIKE, which Postgres provides directly.
func (Postgres) InsertID ¶
InsertID appends RETURNING id and reads the generated key back, because the Postgres driver does not implement LastInsertId.
func (Postgres) Lock ¶
Lock takes a session-level advisory lock. The key is hashed to the int64 pg_advisory_lock wants.
func (Postgres) MigrationDir ¶
func (Postgres) SearchConfig ¶ added in v1.2.1
SearchConfig maps a locale to the text search configuration that stems it. Postgres ships a dictionary for a couple of dozen languages under their English names; a locale with none — or none installed — falls back to "simple", which does no stemming and no stop words. That is a worse search, not a broken one: exact words still match.
Only the language part of the tag is consulted, so "pt-br" and "pt" both reach Portuguese.
func (Postgres) SearchIndexWrite ¶ added in v1.2.1
SearchIndexWrite fills cms_search_docs.tsv: one weighted vector built from the three indexed fields, so a hit in the title outranks a hit in the body. The weights are Postgres's own A/B/D labels, whose numeric values ts_rank_cd supplies (1.0, 0.4, 0.1 by default) — D rather than C for the body so the gap between a title match and a passing mention in the prose is wide.
The configuration arrives as a parameter cast to regconfig rather than being written into the SQL, which is what lets one statement index every locale.
func (Postgres) SearchMatch ¶ added in v1.2.1
SearchMatch matches against the stored vector and scores with ts_rank_cd, which — unlike ts_rank — accounts for how close the matched words are to each other. On a page that mentions two search words in the same sentence rather than ten paragraphs apart, that is the difference between the right result and a plausible one.
func (Postgres) SearchMinWordLen ¶ added in v1.2.1
SearchMinWordLen is 1: Postgres indexes every word it is given. Stop words are dropped by the language's dictionary, but that is a decision about "the" and "and", not about length.
func (Postgres) SearchQuery ¶ added in v1.2.1
func (Postgres) SearchQuery(terms []SearchTerm) string
SearchQuery renders terms in websearch_to_tsquery's syntax — the one Postgres provides precisely so that a string from a search box cannot raise a syntax error. Bare words are AND-ed, quotes make a phrase, and a leading "-" excludes.
func (Postgres) SplitStatements ¶
SplitStatements returns the script whole: Postgres executes a multi-statement string in one round trip, inside the caller's transaction.
type SearchTerm ¶ added in v1.2.1
type SearchTerm struct {
Text string
Phrase bool // several words that must appear together, in order
Exclude bool // the visitor wrote "-word": documents holding it are out
}
SearchTerm is one unit of a parsed search query: a word, or a quoted phrase, either of which the visitor may have prefixed with "-" to mean "and not this".
Queries are parsed rather than passed through because both engines read punctuation in the query as operators, and they do not agree on which. A visitor typing an unbalanced quote or a stray "*" would get a syntax error from one engine and something surprising from the other. Parsing to this and rendering back out means the same typed words mean the same thing on both, and nothing a visitor types is ever read as an operator by accident.
Source Files