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.
import * as Dialect from "gizmodel/sql/types/dialect";Types
Dialect
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:stringemptyIsNull:boolaffinity:boolexactDivision:boolplusConcats:booldateIsTimestamp:booldateIntervalTimestamp:boolboolWords:boolnullsLargest:boollikeCaseless:boolbareGroupColumns:boolabortsOnError:boolisolation:stringschema:stringzone:stringexprLabels:booloraErrors:boolbareFraction:boolimplicitCommit:booltrimsDecimals:booldateOrder: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
function sameDialect(a: Dialect, b: Dialect): boolNames do not certify customized dialect flags; prepared sources must agree on all semantics.
dialectName
function dialectName(name: string): stringOne 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
function warehouse(name: string): boolWhether NAME is an analytical warehouse reached over ODBC: DuckDB, ClickHouse, Snowflake or BigQuery.
booleans
function booleans(d: string): boolWhether the dialect D has true and false values that a condition is: not SQLite, SQL Server or Oracle, which read 1 and 0.
dialectOf
function dialectOf(name: string): DialectThe dialect named name (case ignored); PostgreSQL's for a name it does not know.
Effects: fail, alloc.
knownDialect
function knownDialect(name: string): boolWhether name names a dialect dialectOf knows.
Effects: fail, alloc.
quotedName
function quotedName(d: string, name: string): string throws stringNAME 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
function quotedPath(d: string, path: string): string throws stringA dotted PATH (sales, public.sales) as names of the dialect D, each part delimited (quotedName).
Effects: fail, alloc.
quotedText
function quotedText(d: string, text: string): stringTEXT 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
function window(d: string, count: i64, from: i64, ordered: bool): stringThe 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
function castWord(ty: Type): stringThe 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
function castType(d: string, ty: Type): stringThe 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
function boolLiteral(d: string, x: bool): stringA boolean literal of the dialect D: TRUE and FALSE where it has the words, else 1 and 0.
boolText
function boolText(d: Dialect, x: bool): stringA boolean as a client of the dialect prints it: t and f (PostgreSQL), TRUE and FALSE (Oracle), else 1 and 0.
quotedArrayWord
function quotedArrayWord(s: string): stringQuotes an element of a PostgreSQL array literal in double quotes with backslash escaping.
Effects: fail, alloc.
arrayWord
function arrayWord(s: string): stringOne 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
function Dialect.identityType(self: Dialect): stringThe 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
function Dialect.storageType(self: Dialect, ty: Type, width: i64): stringDDL 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
function Dialect.castType(self: Dialect, ty: string): string throws stringThe 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.