@terascope/sql-builder / dialects/base / BaseSQLDialect

Abstract Class: BaseSQLDialect

Defined in: dialects/base.ts:51

The SQL that every supported engine writes the same way.

A dialect that extends this implements only what its engine does differently: casting, regular expressions, IP helpers and the spatial predicates.

Extended by

• DuckDBDialect
• PostgresDialect

Implements

• SQLDialect

Constructors

Constructor

new BaseSQLDialect(): BaseSQLDialect

Returns

BaseSQLDialect

Properties

Property	Modifier	Type	Defined in
name	abstract	string	dialects/base.ts:52

Methods

and()

and(expressions): string

Defined in: dialects/base.ts:189

All of the expressions.

Parameters

Parameter	Type
expressions	string[]

Returns

string

Implementation of

SQLDialect.and

---

blockEnd()

protected blockEnd(fieldExpr): string

Defined in: dialects/base.ts:340

Parameters

Parameter	Type
fieldExpr	string

Returns

string

---

blockStart()

protected blockStart(fieldExpr): string

Defined in: dialects/base.ts:336

The first and last addresses of the block an ip_range column holds.

host() strips the mask. Comparing a masked INET with a bare one also compares the masks, which gives the wrong answer for overlapping blocks.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

---

compare()

compare(fieldExpr, operator, value, fieldType?): string

Defined in: dialects/base.ts:144

One side of a range.

Parameters

Parameter	Type
fieldExpr	string
operator	"gt" | "gte" | "lt" | "lte"
value	unknown
fieldType?	xLuceneFieldType

Returns

string

Implementation of

SQLDialect.compare

---

equals()

equals(fieldExpr, value, fieldType?): string

Defined in: dialects/base.ts:128

Equality against a single value.

Parameters

Parameter	Type
fieldExpr	string
value	unknown
fieldType?	xLuceneFieldType

Returns

string

Implementation of

SQLDialect.equals

---

exists()

exists(fieldExpr): string

Defined in: dialects/base.ts:174

The field has a value.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Implementation of

SQLDialect.exists

---

fieldRef()

abstract fieldRef(field): string

Defined in: dialects/base.ts:58

The column expression for an xLucene field path.

A dotted field can be a column named a.b or member b of a struct column a, depending on how the engine stores nested data, so each dialect decides.

Parameters

Parameter	Type
field	string

Returns

string

Implementation of

SQLDialect.fieldRef

---

geoContainsPoint()

abstract geoContainsPoint(fieldExpr, point, isPointColumn): string

Defined in: dialects/base.ts:282

A geo shape field that contains a point.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint
isPointColumn	boolean

Returns

string

Implementation of

SQLDialect.geoContainsPoint

---

geoPointDistance()

abstract geoPointDistance(fieldExpr, point): string

Defined in: dialects/base.ts:269

The distance in meters from a geo-point field to a point, for sorting.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint

Returns

string

Implementation of

SQLDialect.geoPointDistance

---

geoPointEquals()

protected geoPointEquals(fieldExpr, point): string

Defined in: dialects/base.ts:287

A geo-point column equal to one point, which is what a bare term on it means.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint

Returns

string

---

geoPointInBoundingBox()

abstract geoPointInBoundingBox(fieldExpr, topLeft, bottomRight): string

Defined in: dialects/base.ts:271

A geo-point field inside an axis-aligned bounding box.

Parameters

Parameter	Type
fieldExpr	string
topLeft	GeoPoint
bottomRight	GeoPoint

Returns

string

Implementation of

SQLDialect.geoPointInBoundingBox

---

geoPointParts()

abstract geoPointParts(fieldExpr): SQLGeoPointColumn

Defined in: dialects/base.ts:80

The lat and lon members of a geo-point column.

Parameters

Parameter	Type
fieldExpr	string

Returns

SQLGeoPointColumn

Implementation of

SQLDialect.geoPointParts

---

geoPointWithinDistance()

abstract geoPointWithinDistance(fieldExpr, point, meters): string

Defined in: dialects/base.ts:265

A geo-point field within distance meters of a point.

Parameters

Parameter	Type
fieldExpr	string
point	GeoPoint
meters	number

Returns

string

Implementation of

SQLDialect.geoPointWithinDistance

---

geoRelation()

abstract geoRelation(fieldExpr, shape, relation, isPointColumn): string

Defined in: dialects/base.ts:275

A geo field related to a shape.

isPointColumn says whether the field holds a point or a shape, which decides how the column becomes a geometry.

Parameters

Parameter	Type
fieldExpr	string
shape	GeoShape
relation	GeoShapeRelation
isPointColumn	boolean

Returns

string

Implementation of

SQLDialect.geoRelation

---

inetLiteral()

abstract protected inetLiteral(value): string

Defined in: dialects/base.ts:348

A literal IP address or CIDR block.

Parameters

Parameter	Type
value	string

Returns

string

---

ipComparison()

protected ipComparison(fieldExpr, operator, value): string

Defined in: dialects/base.ts:293

One side of a range on an ip field, compared as an address.

Parameters

Parameter	Type
fieldExpr	string
operator	string
value	string

Returns

string

---

ipEquals()

ipEquals(fieldExpr, address): string

Defined in: dialects/base.ts:205

Equality over the mapped form, so ::ffff:8.8.8.8 and 8.8.8.8 are one address.

Parameters

Parameter	Type
fieldExpr	string
address	string

Returns

string

Implementation of

SQLDialect.ipEquals

---

ipInCIDR()

ipInCIDR(fieldExpr, cidr): string

Defined in: dialects/base.ts:198

CIDR containment, comparing both sides in IPv4-mapped IPv6 form (see mappedInet).

Parameters

Parameter	Type
fieldExpr	string
cidr	string

Returns

string

Implementation of

SQLDialect.ipInCIDR

---

ipRangeContains()

ipRangeContains(fieldExpr, value): string

Defined in: dialects/base.ts:220

An ip_range column containing a queried address or block.

Here the column holds the block and the query holds the address, the reverse of ipInCIDR. It is written as an overlap check against the block's endpoints rather than with <<=, because <<= returns false across address families ('::ffff:10.0.0.1'::INET <<= '10.0.0.0/30'::INET is FALSE), while the mapped endpoints compare the way Elasticsearch does.

Parameters

Parameter	Type
fieldExpr	string
value	string

Returns

string

Implementation of

SQLDialect.ipRangeContains

---

ipRangeIntersects()

ipRangeIntersects(fieldExpr, start?, end?): string

Defined in: dialects/base.ts:240

Two blocks overlap when each one starts at or before the other one ends.

An exclusive bound changes the operator, not the value: {a TO b} overlaps a stored block only if the block starts before b and ends after a, so a block that only touches a bound does not match.

Parameters

Parameter	Type
fieldExpr	string
start?	SQLIPRangeBound
end?	SQLIPRangeBound

Returns

string

Implementation of

SQLDialect.ipRangeIntersects

---

join()

protected join(expressions, operator): string

Defined in: dialects/base.ts:361

Parameters

Parameter	Type
expressions	string[]
operator	"AND" | "OR"

Returns

string

---

limitOffset()

limitOffset(size?, offset?): string

Defined in: dialects/base.ts:101

LIMIT/OFFSET, which DuckDB and PostgreSQL write the same way.

Both numbers come from the request and are written into the statement, so they are validated as non-negative integers to prevent SQL injection.

Parameters

Parameter	Type
size?	number
offset?	number

Returns

string

Implementation of

SQLDialect.limitOffset

---

literal()

literal(value, fieldType?): string

Defined in: dialects/base.ts:64

A value rendered as a literal of the given xLucene field type.

Parameters

Parameter	Type
value	unknown
fieldType?	xLuceneFieldType

Returns

string

Implementation of

SQLDialect.literal

---

mappedInet()

protected mappedInet(fieldExpr): string

Defined in: dialects/base.ts:306

The column as an address that compares correctly with any other address.

Both engines order INET by family first, so every IPv4 address sorts before every IPv6 one. Elasticsearch stores every ip as 128 bits with IPv4 mapped into IPv6 and orders numerically. Mapping IPv4 values into ::ffff: form puts everything in one family, so ranges compare numerically and ::ffff:8.8.8.8 matches a stored 8.8.8.8, as in Elasticsearch.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

---

mappedInetLiteral()

protected mappedInetLiteral(value): string

Defined in: dialects/base.ts:319

The same mapping for a literal.

A CIDR prefix length grows by 96 bits with the mapping, so 8.8.8.0/24 becomes ::ffff:8.8.8.0/120.

Parameters

Parameter	Type
value	string

Returns

string

---

matchAll()

matchAll(): string

Defined in: dialects/base.ts:120

TRUE - an empty query matches everything.

Returns

string

Implementation of

SQLDialect.matchAll

---

matchNone()

matchNone(): string

Defined in: dialects/base.ts:124

FALSE - a query that cannot match anything.

Returns

string

Implementation of

SQLDialect.matchNone

---

not()

not(expression): string

Defined in: dialects/base.ts:185

Negate an expression so a missing field matches, as in Elasticsearch.

Elasticsearch's must_not matches a document whose field is absent. In SQL, NOT (col = 'x') is NULL when col is NULL, and WHERE drops that row, so the COALESCE turns the NULL into TRUE.

Parameters

Parameter	Type
expression	string

Returns

string

Implementation of

SQLDialect.not

---

or()

or(expressions): string

Defined in: dialects/base.ts:193

Any of the expressions.

Parameters

Parameter	Type
expressions	string[]

Returns

string

Implementation of

SQLDialect.or

---

orderBy()

orderBy(sort?): string

Defined in: dialects/base.ts:116

The ORDER BY terms, with direction and null placement both written out.

NULLS FIRST / NULLS LAST is standard SQL, so this is shared. It is always written because no engine's default matches DataFrame's null order (see defaultNullOrder).

Parameters

Parameter	Type
sort?	readonly SQLSort[]

Returns

string

Implementation of

SQLDialect.orderBy

---

projection()

projection(readable, _all): string

Defined in: dialects/base.ts:89

One column per readable path.

This is correct when a dotted field is a single flat column. An engine that stores dotted fields as nested structs overrides it, so a partly readable parent is rebuilt rather than dropped.

Parameters

Parameter	Type
readable	readonly string[]
_all	readonly string[]

Returns

string

Implementation of

SQLDialect.projection

---

quoteIdentifier()

quoteIdentifier(name): string

Defined in: dialects/base.ts:54

Quote an identifier for use as a column reference.

Parameters

Parameter	Type
name	string

Returns

string

Implementation of

SQLDialect.quoteIdentifier

---

regexp()

regexp(fieldExpr, value, fieldType?): string

Defined in: dialects/base.ts:169

An anchored regular expression match, as the Elasticsearch regexp query is.

Parameters

Parameter	Type
fieldExpr	string
value	string
fieldType?	xLuceneFieldType

Returns

string

Implementation of

SQLDialect.regexp

---

regexpMatch()

abstract protected regexpMatch(expr, value): string

Defined in: dialects/base.ts:354

An anchored regular expression match against an already-textual expression.

Parameters

Parameter	Type
expr	string
value	string

Returns

string

---

stringLiteral()

stringLiteral(value): string

Defined in: dialects/base.ts:60

A SQL string literal, escaped for this engine.

Parameters

Parameter	Type
value	string

Returns

string

Implementation of

SQLDialect.stringLiteral

---

textContains()

abstract protected textContains(expr, substring): string

Defined in: dialects/base.ts:351

Whether a text expression contains a substring; the engines write this differently.

Parameters

Parameter	Type
expr	string
substring	string

Returns

string

---

timestampLiteral()

protected timestampLiteral(value): string

Defined in: dialects/base.ts:357

A literal timestamp, always UTC.

Parameters

Parameter	Type
value	unknown

Returns

string

---

toInet()

abstract protected toInet(fieldExpr): string

Defined in: dialects/base.ts:345

The column as an IP address value.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

---

toText()

abstract toText(fieldExpr): string

Defined in: dialects/base.ts:263

The value cast to text, for a query with no field.

Parameters

Parameter	Type
fieldExpr	string

Returns

string

Implementation of

SQLDialect.toText

---

validCIDR()

protected validCIDR(value): string

Defined in: dialects/base.ts:383

Parameters

Parameter	Type
value	unknown

Returns

string

---

validIP()

protected validIP(value): string

Defined in: dialects/base.ts:372

Validate an IP address or CIDR block before it reaches the engine.

Otherwise the engine's parse error would refer to generated SQL the caller never wrote.

Parameters

Parameter	Type
value	unknown

Returns

string

---

wildcard()

wildcard(fieldExpr, value, fieldType?): string

Defined in: dialects/base.ts:159

A wildcard match, given an xLucene wildcard value (* and ?).

Parameters

Parameter	Type
fieldExpr	string
value	string
fieldType?	xLuceneFieldType

Returns

string

Implementation of

SQLDialect.wildcard