@terascope/data-mate / function-configs/ip/sql-utils

function-configs/ip/sql-utils

Functions

asInet()

asInet(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:25

The value as an INET, or null. Any scope ID is stripped first.

Parameters

Parameter	Type
value	string

Returns

string

---

cidrBroadcast()

cidrBroadcast(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:137

The broadcast address of a CIDR block, also as a bare INET.

Parameters

Parameter	Type
value	string

Returns

string

---

cidrNetwork()

cidrNetwork(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:132

The network address of a CIDR block, as a bare INET with the prefix dropped.

The prefix must go before any arithmetic. DuckDB refuses arithmetic that leaves the subnet: network('10.0.0.0/8'::INET) - 1 fails with Out of Range Error, and the first and last usable addresses need exactly that step. On a bare address it is plain address arithmetic, as in IPAddress.offset.

Parameters

Parameter	Type
value	string

Returns

string

---

firstUsableHitsTopOfV4()

firstUsableHitsTopOfV4(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:169

True for the one block whose first usable address DuckDB cannot reach by addition.

INET '255.255.255.254' + 1 raises Out of Range Error. Subtracting from 255.255.255.255 works and IPv6 has no such limit, so only the first usable address is affected.

Parameters

Parameter	Type
value	string

Returns

string

---

intToIPv4Sql()

intToIPv4Sql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:190

An integer as a dotted-quad IPv4 address: the exact inverse of ipv4ToIntSql.

Parameters

Parameter	Type
value	string

Returns

string

---

ipv4ToIntSql()

ipv4ToIntSql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:184

An IPv4 address as its integer value.

DuckDB has no inet_aton and no cast from INET to a number, so this weights the four dotted parts. Each part is cast to HUGEINT.

Parameters

Parameter	Type
value	string

Returns

string

---

isCIDRSql()

isCIDRSql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:59

CIDR notation: a prefix is present and the value casts to INET.

Parameters

Parameter	Type
value	string

Returns

string

---

isIPSql()

isIPSql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:54

Either family, as IPAddress.isValid accepts.

Parameters

Parameter	Type
value	string

Returns

string

---

isIPv4CIDRSql()

isIPv4CIDRSql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:174

An IPv4 CIDR, which is all getCIDRNetwork and getCIDRBroadcast accept.

Parameters

Parameter	Type
value	string

Returns

string

---

isIPv4Sql()

isIPv4Sql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:36

Dotted-quad IPv4, matched by regex rather than TRY_CAST.

INET accepts more than data-mate does: 01.02.03.04 casts to 1.2.3.4, while IPV4_RE in ip-utils rejects leading zeros, and 1.2.3.4/24 casts as an address, while data-mate calls it a CIDR. This regex is IPV4_RE, so it rejects both.

Parameters

Parameter	Type
value	string

Returns

string

---

isIPv6Sql()

isIPv6Sql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:48

IPv6: a successful cast, plus two checks the cast alone would miss.

IPAddress.isIPv6 requires a colon, and a /prefix makes the value a CIDR rather than an address.

Parameters

Parameter	Type
value	string

Returns

string

---

isMappedIPv4Sql()

isMappedIPv4Sql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:71

An IPv4 address mapped into IPv6: ::ffff:a.b.c.d, or the deprecated ::a.b.c.d.

The second form is matched on the text. isMappedIPv4 returns true for ::0.0.0.0 and false for ::, which are the same 128 bits, because ip-utils matches IPV4_COMPAT_RE against the input string. No containment test can tell them apart, and testing ::/96 would wrongly match :: and ::1.

Parameters

Parameter	Type
value	string

Returns

string

---

isMappedResult()

isMappedResult(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:159

True for a value whose result host() and ip-utils would print differently, so the UDF handles it.

host() prints an IPv4-mapped address with a dotted tail (::ffff:0.0.0.0), while intToIPv6String prints ::ffff:0:0. Other IPv6 blocks print the same on both paths, so only the mapped range is excluded.

Parameters

Parameter	Type
value	string

Returns

string

---

isRoutableSql()

isRoutableSql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:102

Routability, as a set of containment tests.

The inet extension has no family() and no reserved-range function. The three branches follow IPAddress.isRoutable in order: IPv4 against the v4 table, a mapped address against the lifted v4 table, and any other IPv6 against the v6 table. A value that is not an IP is not routable.

Parameters

Parameter	Type
value	string

Returns

string

---

isSingleAddress()

isSingleAddress(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:147

True when the block holds a single address.

The ends are compared with host(a) = host(b) rather than a = b, because INET equality does not return true for a /32 whose two ends print the same.

Parameters

Parameter	Type
value	string

Returns

string

---

plainIntegerSql()

plainIntegerSql(value): string

Defined in: data-mate/src/function-configs/ip/sql-utils.ts:205

The input as a HUGEINT when it is a plain run of digits, otherwise null.

The check is a digit regex, deliberately narrower than a cast. intToIP calls BigInt(input), and BigInt and TRY_CAST(x AS HUGEINT) disagree on several inputs: SQL accepts '12.0', '12.5', '1e3' and '1_000' where BigInt throws, and BigInt accepts '0x10', '0b11' and '' where the cast gives null. Both accept ^[0-9]+$, so every disputed input keeps the UDF.

Parameters

Parameter	Type
value	string

Returns

string