Skip to main content

SQLDialect

@webda/postgres

Interface: SQLDialect

Defined in: sql/lib/dialect.d.ts:34

Engine syntax for the "one JSON document per row" model of SQLRepository: a uuid key column and a data JSON column. Paths handed to a dialect are validated ([A-Za-z0-9_]+ segments) and safe to interpolate.

Methods taking args push the parameters they bind and refer to them by args.length: the caller appends its own parameters afterwards.

Properties​

binaryCollation​

readonly binaryCollation: string

Defined in: sql/lib/dialect.d.ts:36

Clause ordering text in code unit order: COLLATE "C" / COLLATE BINARY

Methods​

asNumber()​

asNumber(json): string

Defined in: sql/lib/dialect.d.ts:85

Comparable number of a JSON value known to be a number

Parameters​

json​

string

a JSON value expression

Returns​

string

the expression

asString()​

asString(json): string

Defined in: sql/lib/dialect.d.ts:91

Text of a JSON value known to be a string

Parameters​

json​

string

a JSON value expression

Returns​

string

the expression

backfillTypes()​

backfillTypes(table): string

Defined in: sql/lib/dialect.d.ts:220

Statement stamping __type = parameter 1 on the untyped rows of the table

Parameters​

table​

string

the table name

Returns​

string

the statement

bulkAssign()​

bulkAssign(table, assignments, args): string

Defined in: sql/lib/dialect.d.ts:211

data with the UPDATE SET assignments applied: missing parents created, existing arrays indexed; binds values

Parameters​

table​

string

the table name

assignments​

object[]

the SET assignments

args​

any[]

the statement parameters

Returns​

string

the expression

collectionItemCondition()​

collectionItemCondition(attr, index, field, valueParam): string

Defined in: sql/lib/dialect.d.ts:203

Condition: the field of the item at index of the top-level array equals the parameter

Parameters​

attr​

string

the array attribute

index​

number | undefined

the index

field​

string

the item field

valueParam​

number

position of the bound value

Returns​

string

the condition

contains()​

contains(json, value): string

Defined in: sql/lib/dialect.d.ts:113

Condition: the JSON value is an array holding the value (a literal, inlined)

Parameters​

json​

string

a JSON value expression

value​

string | number | boolean

the value

Returns​

string

the condition

countStoredTypes()​

countStoredTypes(table): string

Defined in: sql/lib/dialect.d.ts:226

Statement returning type, n per stored type (parameter 1 = unreadable label, 2 = untyped label)

Parameters​

table​

string

the table name

Returns​

string

the statement

createTable()​

createTable(table): string

Defined in: sql/lib/dialect.d.ts:126

CREATE TABLE IF NOT EXISTS statement of a document table

Parameters​

table​

string

the table name

Returns​

string

the statement

decode()​

decode(value): any

Defined in: sql/lib/dialect.d.ts:237

Decode a JSON column of a result row: the object (pg parses jsonb), parsed text otherwise

Parameters​

value​

any

the column value

Returns​

any

the decoded value

distinctFrom()​

distinctFrom(left, right): string

Defined in: sql/lib/dialect.d.ts:106

Null-safe !=: IS DISTINCT FROM / IS NOT

Parameters​

left​

string

left operand

string

right operand

Returns​

string

the condition

hasStoredType()​

hasStoredType(): string

Defined in: sql/lib/dialect.d.ts:137

Condition: the row has a __type key

Returns​

string

the condition

increment()​

increment(entries, updateDate, args): string

Defined in: sql/lib/dialect.d.ts:173

data with the counters incremented and _lastUpdate set to the ISO date; binds the increments

Parameters​

entries​

object[]

the counters

updateDate​

string

ISO date

args​

any[]

the statement parameters

Returns​

string

the expression

isObjectRow()​

isObjectRow(): string

Defined in: sql/lib/dialect.d.ts:231

Condition: the data column is a JSON object

Returns​

string

the condition

isType()​

isType(json, type): string

Defined in: sql/lib/dialect.d.ts:79

Condition: the JSON value has the type

Parameters​

json​

string

a JSON value expression

type​

JSONType

the type

Returns​

string

the condition

isUniqueViolation()​

isUniqueViolation(err): boolean

Defined in: sql/lib/dialect.d.ts:132

Whether the error is a primary key / unique violation on insert

Parameters​

err​

unknown

the error

Returns​

boolean

true for a unique violation

jsonParam()​

jsonParam(i): string

Defined in: sql/lib/dialect.d.ts:48

The i-th parameter, a JSON text, as a JSON value: $1::jsonb / json(?1)

Parameters​

i​

number

the parameter position

Returns​

string

the expression

jsonText()​

jsonText(path): string

Defined in: sql/lib/dialect.d.ts:54

SQL text of the value at a path, NULL for a missing field and a JSON null

Parameters​

path​

string[]

validated segments

Returns​

string

the expression

jsonValue()​

jsonValue(path): string

Defined in: sql/lib/dialect.d.ts:60

JSON value at a path

Parameters​

path​

string[]

validated segments

Returns​

string

the expression

jsonValueOrNull()​

jsonValueOrNull(path): string

Defined in: sql/lib/dialect.d.ts:66

JSON value at a path, a JSON null folded into SQL NULL (aggregations)

Parameters​

path​

string[]

validated segments

Returns​

string

the expression

keptType()​

keptType(mapParam): string

Defined in: sql/lib/dialect.d.ts:143

The stored type of the row, rewritten through the legacy type map bound at mapParam (JSON text)

Parameters​

mapParam​

number

position of the bound map parameter

Returns​

string

the expression

keyText()​

keyText(key): string

Defined in: sql/lib/dialect.d.ts:72

SQL text of a top-level key: data->>'k'

Parameters​

key​

string

the key

Returns​

string

the expression

like()​

like(text, pattern): string

Defined in: sql/lib/dialect.d.ts:120

Case-sensitive LIKE condition on the text (% / _, \% / \_ literal)

Parameters​

text​

string

the text expression

pattern​

string

the LIKE pattern

Returns​

string

the condition

placeholder()​

placeholder(i): string

Defined in: sql/lib/dialect.d.ts:42

Positional placeholder of the i-th (1-based) parameter: $1 / ?1

Parameters​

i​

number

the parameter position

Returns​

string

the placeholder

removeCollectionItem()​

removeCollectionItem(attr, index, updateDate): string

Defined in: sql/lib/dialect.d.ts:194

data with the item at index removed from the top-level array, _lastUpdate set

Parameters​

attr​

string

the array attribute

index​

number

the index

updateDate​

string

ISO date

Returns​

string

the expression

removeKey()​

removeKey(attribute, args): string

Defined in: sql/lib/dialect.d.ts:165

data without the top-level attribute; binds what it needs

Parameters​

attribute​

string

the attribute

args​

any[]

the statement parameters

Returns​

string

the expression

setCollectionItem()​

setCollectionItem(attr, itemParam, index, updateDate): string

Defined in: sql/lib/dialect.d.ts:186

data with the item bound at itemParam appended to (index undefined) or set at index of the top-level array, _lastUpdate set

Parameters​

attr​

string

the array attribute

itemParam​

number

position of the bound item (JSON text)

index​

number | undefined

the index, undefined to append

updateDate​

string

ISO date

Returns​

string

the expression

shallowMerge()​

shallowMerge(json, patch, args): string

Defined in: sql/lib/dialect.d.ts:158

The JSON value shallow-merged with the patch (top-level keys replaced); binds what it needs

Parameters​

json​

string

a JSON value expression

patch​

Record<string, any>

the patch

args​

any[]

the statement parameters

Returns​

string

the expression

typedAttribute()​

typedAttribute(text, json, type): string

Defined in: sql/lib/dialect.d.ts:99

Attribute of a typed comparison: the number or boolean, NULL when the field has another type

Parameters​

text​

string

the text expression of the attribute

json​

string

the JSON value expression of the attribute

type​

"number" | "boolean"

the type of the compared value

Returns​

string

the expression

withStoredType()​

withStoredType(json, type): string

Defined in: sql/lib/dialect.d.ts:150

The JSON value with __type set to the type expression

Parameters​

json​

string

a JSON value expression

type​

string

the type expression

Returns​

string

the expression