GizStack
View .mdOpen in ChatGPTOpen in Claude

gizmodel/sql/types/dialect

A SQL dialect as a value: its name and the rules where the engines it imitates differ in meaning, not spelling. A rule that many modules read is a field (d.emptyIsNull), so a new dialect is a new value here. A quirk of one function reads name in that function (Oracle's substr of position 0, SQL Server's natural log), and so do PostgreSQL's own types, catalogs and errors (xid, inet, pg_lsn, timetz, pg_catalog, 42P01). Spellings that mean the same in every dialect (lcase, getdate, nvl) are one vocabulary the parser and function library accept everywhere.

gizlang
import * as Dialect from "gizmodel/sql/types/dialect";

Types

Dialect

gizlang
type Dialect = { name: string; emptyIsNull: bool; affinity: bool; exactDivision: bool; plusConcats: bool; dateIsTimestamp: bool; dateIntervalTimestamp: bool; boolWords: bool; nullsLargest: bool; likeCaseless: bool; bareGroupColumns: bool; abortsOnError: bool; isolation: string; schema: string; zone: string; exprLabels: bool; oraErrors: bool; bareFraction: bool; implicitCommit: bool; trimsDecimals: bool; dateOrder: i64; clock: i64; }

name: postgres, oracle, mssql, sqlite, mysql, duckdb, clickhouse, snowflake or bigquery.

  • emptyIsNull: '' is NULL (Oracle): a text function's empty result is NULL, || skips NULL.

  • affinity: SQLite's dynamic typing: casts take a numeric prefix and truncate, a column keeps what does not convert, dates are plain text, reals print with 15 digits, x/0 is NULL.

  • exactDivision: integer / integer is exact when it does not divide evenly (Oracle's NUMBER).

  • plusConcats: + of text joins it (SQL Server).

  • dateIsTimestamp: DATE holds a time of day too (Oracle); date - date is a number of days.

  • dateIntervalTimestamp: a date plus an interval of whole days is a timestamp (PostgreSQL).

  • boolWords: a boolean as text is true/false (PostgreSQL), else 1/0.

  • nullsLargest: NULL sorts after every value ascending (PostgreSQL, Oracle).

  • likeCaseless: LIKE ignores ASCII case (SQLite, MySQL).

  • bareGroupColumns: a grouped query may name a column outside GROUP BY (SQLite, MySQL).

  • abortsOnError: an error inside BEGIN fails the transaction until ROLLBACK, and BEGIN or COMMIT out of place only warn (PostgreSQL).

  • isolation: what BEGIN starts (Tx.isolation).

  • schema: the default schema's name.

  • zone: the session's TimeZone as SET gave it ("" is UTC): timestamptz values print and read in it.

  • bareFraction: a number as text has no 0 before its point, .4 and -.4 (Oracle's TO_CHAR and implicit conversion).

  • trimsDecimals: a decimal keeps no scale of its own, 2.50 is 2.5 (Oracle's NUMBER).

  • implicitCommit: DDL commits the open transaction, then itself at once, and COMMIT or ROLLBACK with none open does nothing (Oracle).

  • oraErrors: an error's message starts with Oracle's number, ORA-00001: ... (the PG server).

  • exprLabels: a result column is labeled as Oracle labels it: identifiers in uppercase, an unaliased expression by its text, uppercased without whitespace (NVL(A,'X')).

  • name: string

  • emptyIsNull: bool

  • affinity: bool

  • exactDivision: bool

  • plusConcats: bool

  • dateIsTimestamp: bool

  • dateIntervalTimestamp: bool

  • boolWords: bool

  • nullsLargest: bool

  • likeCaseless: bool

  • bareGroupColumns: bool

  • abortsOnError: bool

  • isolation: string

  • schema: string

  • zone: string

  • exprLabels: bool

  • oraErrors: bool

  • bareFraction: bool

  • implicitCommit: bool

  • trimsDecimals: bool

  • dateOrder: i64 — The order of a date's numbers in input (DateStyle): 0 month-day-year, 1 day-month-year, 2 year-month-day.

  • clock: i64 — The statement's wall clock in microseconds (0 when unknown): PostgreSQL's 'now', 'today', 'tomorrow' and 'yesterday' inputs read it.

Functions

sameDialect

gizlang
function sameDialect(a: Dialect, b: Dialect): bool

Names do not certify customized dialect flags; prepared sources must agree on all semantics.

dialectName

gizlang
function dialectName(name: string): string

One owner of supported dialect names and their aliases. An unknown name gives "". Dialect selection still defaults to PostgreSQL for an unknown input.

Effects: fail, alloc.

warehouse

gizlang
function warehouse(name: string): bool

Whether NAME is an analytical warehouse reached over ODBC: DuckDB, ClickHouse, Snowflake or BigQuery.

booleans

gizlang
function booleans(d: string): bool

Whether the dialect D has true and false values that a condition is: not SQLite, SQL Server or Oracle, which read 1 and 0.

dialectOf

gizlang
function dialectOf(name: string): Dialect

The dialect named name (case ignored); PostgreSQL's for a name it does not know.

Effects: fail, alloc.

knownDialect

gizlang
function knownDialect(name: string): bool

Whether name names a dialect dialectOf knows.

Effects: fail, alloc.

quotedName

gizlang
function quotedName(d: string, name: string): string throws string

NAME as a delimited identifier of the dialect D. Delimiting makes the first character, a reserved word and any Unicode letter safe. A name that the target cannot hold as written is refused, and never changed: an empty name or NUL (every dialect), a name past the length the target keeps (PostgreSQL truncates past 63 bytes, which can name another object), a double quote (Oracle cannot escape it), a code point past the Basic Multilingual Plane or a trailing space (MySQL). SQL Server brackets do not depend on the QUOTED_IDENTIFIER setting.

Effects: fail, alloc.

quotedPath

gizlang
function quotedPath(d: string, path: string): string throws string

A dotted PATH (sales, public.sales) as names of the dialect D, each part delimited (quotedName).

Effects: fail, alloc.

quotedText

gizlang
function quotedText(d: string, text: string): string

TEXT as a string literal of the dialect D. MySQL reads a backslash in a literal as an escape, so it is doubled; SQL Server keeps text past Latin-1 only in an N literal.

Effects: fail, alloc.

window

gizlang
function window(d: string, count: i64, from: i64, ordered: bool): string

The clause that keeps COUNT rows after the first FROM, in the dialect D. SQL Server pages only an ordered result: when ORDERED is false, the clause orders by nothing first.

Effects: fail, alloc.

castWord

gizlang
function castWord(ty: Type): string

The portable CAST target of TY (render.giz checkedType's vocabulary): the one word the typed lowering writes for each logical type, that every dialect maps to its own (castType). An unscaled decimal is NUMERIC(38,18), the shape the dialects agree on; "" for a type no dialect casts to.

Effects: fail, alloc.

castType

gizlang
function castType(d: string, ty: Type): string

The type that CAST names in the dialect D for a whole number, a float or text (TY); "" for another type. SQL Server keeps text past Latin-1 only in NVARCHAR; Oracle keeps at most 4000 bytes in VARCHAR2.

boolLiteral

gizlang
function boolLiteral(d: string, x: bool): string

A boolean literal of the dialect D: TRUE and FALSE where it has the words, else 1 and 0.

boolText

gizlang
function boolText(d: Dialect, x: bool): string

A boolean as a client of the dialect prints it: t and f (PostgreSQL), TRUE and FALSE (Oracle), else 1 and 0.

quotedArrayWord

gizlang
function quotedArrayWord(s: string): string

Quotes an element of a PostgreSQL array literal in double quotes with backslash escaping.

Effects: fail, alloc.

arrayWord

gizlang
function arrayWord(s: string): string

One element of an array's text as PostgreSQL writes it: in double quotes, with a backslash before each quote and backslash, when it is empty, spells NULL, or holds a delimiter, a brace, a quote, a backslash or a space.

Effects: fail, alloc.

Methods

Dialect.identityType

gizlang
function Dialect.identityType(self: Dialect): string

The DDL spelling of an i64 key that the database numbers upward on each INSERT (Model's @Identity). SQLite numbers an INTEGER PRIMARY KEY itself.

Dialect.storageType

gizlang
function Dialect.storageType(self: Dialect, ty: Type, width: i64): string

DDL spelling for the typed physical representation. Logical widths enter here once; callers do not maintain their own storage type registries.

Effects: fail, alloc.

Dialect.castType

gizlang
function Dialect.castType(self: Dialect, ty: string): string throws string

The type a CAST names in this dialect for TY, one of the portable cast types BIGINT, TEXT, BOOLEAN, DOUBLE PRECISION, DATE, TIMESTAMP, TIME and NUMERIC(p,s) (render.giz checks it first). A type the dialect cannot cast to is refused.

Effects: fail, alloc.