Implementation
final Map<String, ScalarFn> kScalarFunctions = <String, ScalarFn>{
'UPPER': (a) => _propagateNull(a, () => a[0].toString().toUpperCase()),
'LOWER': (a) => _propagateNull(a, () => a[0].toString().toLowerCase()),
'LENGTH': (a) => _propagateNull(a, () {
final v = a[0]!;
// SQLite: LENGTH on a BLOB is the byte count, on TEXT the
// character count.
if (v is List<int>) return v.length;
return v.toString().length;
}),
'CHAR_LENGTH': (a) => kScalarFunctions['LENGTH']!(a),
'CHARACTER_LENGTH': (a) => kScalarFunctions['LENGTH']!(a),
// OCTET_LENGTH: UTF-8 byte length for text, byte length for blobs.
'OCTET_LENGTH': (a) => _propagateNull(a, () {
final v = a[0]!;
if (v is List<int>) return v.length;
return utf8.encode(v.toString()).length;
}),
// BIT_LENGTH: OCTET_LENGTH * 8.
'BIT_LENGTH': (a) => _propagateNull(a, () {
final v = a[0]!;
if (v is List<int>) return v.length * 8;
return utf8.encode(v.toString()).length * 8;
}),
// LEAST(a, b, ...) -- smallest non-NULL value; returns NULL if all NULL.
'LEAST': (a) {
Object? best;
for (final v in a) {
if (v == null) continue;
if (best == null || sqlCompare(v, best) < 0) best = v;
}
return best;
},
// GREATEST(a, b, ...) -- largest non-NULL value.
'GREATEST': (a) {
Object? best;
for (final v in a) {
if (v == null) continue;
if (best == null || sqlCompare(v, best) > 0) best = v;
}
return best;
},
'TRIM': (a) => _propagateNull(a, () => a[0].toString().trim()),
'LTRIM': (a) => _propagateNull(
a,
() => a[0].toString().replaceFirst(RegExp(r'^\s+'), ''),
),
'RTRIM': (a) => _propagateNull(
a,
() => a[0].toString().replaceFirst(RegExp(r'\s+$'), ''),
),
'SUBSTR': (a) {
if (a.isEmpty || a[0] == null) return null;
final s = a[0].toString();
if (a.length < 2) return s;
final start = (a[1] as num).toInt();
// SQL is 1-based; negative offsets count from the end.
var idx = start > 0 ? start - 1 : (s.length + start).clamp(0, s.length);
if (idx < 0) idx = 0;
if (idx >= s.length) return '';
if (a.length >= 3 && a[2] != null) {
var len = (a[2] as num).toInt();
if (len < 0) len = 0;
final end = (idx + len).clamp(0, s.length);
return s.substring(idx, end);
}
return s.substring(idx);
},
'SUBSTRING': (a) => kScalarFunctions['SUBSTR']!(a),
// LEFT(s, n) -- first n characters.
'LEFT': (a) => _propagateNull(a, () {
final s = a[0].toString();
final n = (a[1] as num).toInt();
if (n <= 0) return '';
return n >= s.length ? s : s.substring(0, n);
}),
// RIGHT(s, n) -- last n characters.
'RIGHT': (a) => _propagateNull(a, () {
final s = a[0].toString();
final n = (a[1] as num).toInt();
if (n <= 0) return '';
return n >= s.length ? s : s.substring(s.length - n);
}),
// POSITION(needle IN haystack) is parsed as POSITION(needle, haystack)
// here; returns 1-based index, 0 when not found, NULL on NULL input.
'POSITION': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return null;
final needle = a[0].toString();
final hay = a[1].toString();
if (needle.isEmpty) return 1;
return hay.indexOf(needle) + 1;
},
// OVERLAY(s, replacement, start [, length]) -- replace `length`
// characters of `s` starting at 1-based `start` with `replacement`.
// When length is omitted it defaults to the length of `replacement`.
'OVERLAY': (a) {
if (a.length < 3 || a[0] == null || a[1] == null || a[2] == null) {
return null;
}
final s = a[0].toString();
final repl = a[1].toString();
final start = (a[2] as num).toInt();
final length =
a.length >= 4 && a[3] != null ? (a[3] as num).toInt() : repl.length;
final idx = (start - 1).clamp(0, s.length);
final endIdx = (idx + length).clamp(0, s.length);
return s.substring(0, idx) + repl + s.substring(endIdx);
},
// REVERSE(s) -- reverses a string by Unicode code points.
'REVERSE': (a) => _propagateNull(a, () {
final s = a[0].toString();
return String.fromCharCodes(s.runes.toList().reversed);
}),
// REPEAT(s, n) -- string repetition; negative or zero count returns ''.
'REPEAT': (a) => _propagateNull(a, () {
final s = a[0].toString();
final n = (a[1] as num).toInt();
if (n <= 0) return '';
return s * n;
}),
// ASCII(s) -- code point of the first character. SQLite returns the
// codepoint of the first UTF-8 byte in the input; we mirror Dart's
// String.codeUnitAt(0) which is the UTF-16 code unit, equivalent for
// ASCII inputs.
'ASCII': (a) => _propagateNull(a, () {
final s = a[0].toString();
return s.isEmpty ? null : s.codeUnitAt(0);
}),
// CHR(n) -- single-character string from a Unicode codepoint.
'CHR': (a) => _propagateNull(a, () {
return String.fromCharCode((a[0] as num).toInt());
}),
// SPACE(n) -- string of n spaces.
'SPACE': (a) => _propagateNull(a, () {
final n = (a[0] as num).toInt();
return n <= 0 ? '' : ' ' * n;
}),
// INITCAP(s) -- title-case each whitespace-separated word.
'INITCAP': (a) => _propagateNull(a, () {
final s = a[0].toString();
if (s.isEmpty) return s;
final out = StringBuffer();
var nextUpper = true;
for (var i = 0; i < s.length; i++) {
final c = s[i];
if (c == ' ' || c == '\t' || c == '\n' || c == '\r') {
out.write(c);
nextUpper = true;
continue;
}
out.write(nextUpper ? c.toUpperCase() : c.toLowerCase());
nextUpper = false;
}
return out.toString();
}),
'REPLACE': (a) => _propagateNull(
a,
() => a[0].toString().replaceAll(a[1].toString(), a[2].toString()),
),
'CONCAT': (a) => a.map((v) => v ?? '').join(),
// CONCAT_WS(sep, args...) — joins non-NULL args with the given separator.
// If sep is NULL, returns NULL (matches MySQL/PostgreSQL semantics).
'CONCAT_WS': (a) {
if (a.isEmpty || a[0] == null) return null;
final sep = a[0].toString();
final parts = <String>[];
for (var i = 1; i < a.length; i++) {
final v = a[i];
if (v == null) continue;
parts.add(v.toString());
}
return parts.join(sep);
},
'COALESCE': (a) {
for (final v in a) {
if (v != null) return v;
}
return null;
},
'IFNULL': (a) => a[0] ?? (a.length > 1 ? a[1] : null),
'NVL': (a) => a[0] ?? (a.length > 1 ? a[1] : null),
'NVL2': (a) {
if (a.length < 3) return null;
return a[0] != null ? a[1] : a[2];
},
'DECODE': (a) {
// DECODE(expr, search1, result1, [search2, result2, ...] [, default])
if (a.isEmpty) return null;
final expr = a[0];
var i = 1;
while (i + 1 < a.length) {
final s = a[i];
if ((expr == null && s == null) ||
(expr != null && s != null && sqlEq(expr, s))) {
return a[i + 1];
}
i += 2;
}
return i < a.length ? a[i] : null;
},
'NULLIF': (a) {
if (a.length < 2 || a[0] == null) return a.isEmpty ? null : a[0];
if (a[1] == null) return a[0];
return sqlEq(a[0]!, a[1]!) ? null : a[0];
},
'ABS': (a) => _propagateNull(a, () => (a[0] as num).abs()),
'ROUND': (a) {
if (a.isEmpty || a[0] == null) return null;
final v = (a[0] as num).toDouble();
final digits = a.length > 1 && a[1] != null ? (a[1] as num).toInt() : 0;
final p = _pow10(digits);
return (v * p).round() / p;
},
'MOD': (a) => _propagateNull(a, () => (a[0] as num) % (a[1] as num)),
'FLOOR': (a) => _propagateNull(a, () => (a[0] as num).floor()),
'CEIL': (a) => _propagateNull(a, () => (a[0] as num).ceil()),
'CEILING': (a) => _propagateNull(a, () => (a[0] as num).ceil()),
'SQRT': (a) => _propagateNull(a, () {
final v = (a[0] as num).toDouble();
if (v < 0) return null;
return _sqrt(v);
}),
'POWER': (a) => _propagateNull(
a,
() => _intPow((a[0] as num).toDouble(), (a[1] as num).toDouble()),
),
'POW': (a) => kScalarFunctions['POWER']!(a),
'SIGN': (a) => _propagateNull(a, () {
final v = (a[0] as num).toDouble();
if (v == 0) return 0;
return v > 0 ? 1 : -1;
}),
// SQLite 3.35+ math functions. All operate in double precision and
// return NULL on NULL input. Domain errors (e.g. LN(0), SQRT(-1))
// return NULL to match SQLite.
'PI': (a) => math.pi,
// GROUPING(expr) — context-sensitive. The select executor rewrites
// results from GROUPING SETS / ROLLUP / CUBE so this default 0
// applies only when the expression IS in the current grouping set.
'GROUPING': (a) => 0,
'IIF': (a) {
if (a.length < 3) return null;
final c = a[0];
final truthy = c is bool
? c
: c is num
? c != 0
: c != null;
return truthy ? a[1] : a[2];
},
'CBRT': (a) => _propagateNull(a, () {
final v = (a[0] as num).toDouble();
if (v < 0) return -math.pow(-v, 1 / 3).toDouble();
return math.pow(v, 1 / 3).toDouble();
}),
'EXP': (a) => _propagateNull(a, () => math.exp((a[0] as num).toDouble())),
'LN': (a) => _propagateNull(a, () {
final v = (a[0] as num).toDouble();
if (v <= 0) return null;
return math.log(v);
}),
'LOG10': (a) => _propagateNull(a, () {
final v = (a[0] as num).toDouble();
if (v <= 0) return null;
return math.log(v) / math.ln10;
}),
'LOG2': (a) => _propagateNull(a, () {
final v = (a[0] as num).toDouble();
if (v <= 0) return null;
return math.log(v) / math.ln2;
}),
// LOG(x) == LOG10(x); LOG(b, x) == log base b of x (SQLite semantics).
'LOG': (a) {
if (a.isEmpty || a[0] == null) return null;
if (a.length == 1) {
final v = (a[0] as num).toDouble();
if (v <= 0) return null;
return math.log(v) / math.ln10;
}
if (a[1] == null) return null;
final b = (a[0] as num).toDouble();
final v = (a[1] as num).toDouble();
if (b <= 0 || b == 1 || v <= 0) return null;
return math.log(v) / math.log(b);
},
'SIN': (a) => _propagateNull(a, () => math.sin((a[0] as num).toDouble())),
'COS': (a) => _propagateNull(a, () => math.cos((a[0] as num).toDouble())),
'TAN': (a) => _propagateNull(a, () => math.tan((a[0] as num).toDouble())),
'COT': (a) => _propagateNull(a, () {
final t = math.tan((a[0] as num).toDouble());
if (t == 0) return null;
return 1.0 / t;
}),
'ACOT': (a) => _propagateNull(a, () {
final v = (a[0] as num).toDouble();
if (v == 0) return math.pi / 2;
return math.atan(1.0 / v);
}),
'ASIN': (a) => _propagateNull(a, () {
final v = (a[0] as num).toDouble();
if (v < -1 || v > 1) return null;
return math.asin(v);
}),
'ACOS': (a) => _propagateNull(a, () {
final v = (a[0] as num).toDouble();
if (v < -1 || v > 1) return null;
return math.acos(v);
}),
'ATAN': (a) => _propagateNull(a, () => math.atan((a[0] as num).toDouble())),
'ATAN2': (a) => _propagateNull(
a,
() => math.atan2((a[0] as num).toDouble(), (a[1] as num).toDouble()),
),
'SINH': (a) => _propagateNull(a, () {
final x = (a[0] as num).toDouble();
return (math.exp(x) - math.exp(-x)) / 2;
}),
'COSH': (a) => _propagateNull(a, () {
final x = (a[0] as num).toDouble();
return (math.exp(x) + math.exp(-x)) / 2;
}),
'TANH': (a) => _propagateNull(a, () {
final x = (a[0] as num).toDouble();
if (x > 20) return 1.0;
if (x < -20) return -1.0;
final ep = math.exp(x);
final en = math.exp(-x);
return (ep - en) / (ep + en);
}),
'ASINH': (a) => _propagateNull(a, () {
final x = (a[0] as num).toDouble();
return math.log(x + math.sqrt(x * x + 1));
}),
'ACOSH': (a) => _propagateNull(a, () {
final x = (a[0] as num).toDouble();
if (x < 1) return null;
return math.log(x + math.sqrt(x * x - 1));
}),
'ATANH': (a) => _propagateNull(a, () {
final x = (a[0] as num).toDouble();
if (x <= -1 || x >= 1) return null;
return 0.5 * math.log((1 + x) / (1 - x));
}),
'RADIANS': (a) =>
_propagateNull(a, () => (a[0] as num).toDouble() * math.pi / 180),
'DEGREES': (a) =>
_propagateNull(a, () => (a[0] as num).toDouble() * 180 / math.pi),
'TRUNC': (a) => _propagateNull(a, () => (a[0] as num).truncate()),
// REGEXP family. SQLite's REGEXP operator desugars to regexp(pat, val);
// we accept both forms. Pattern is Dart's RegExp (PCRE-like).
'REGEXP': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return null;
return RegExp(a[0].toString()).hasMatch(a[1].toString());
},
'REGEXP_LIKE': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return null;
return RegExp(a[1].toString()).hasMatch(a[0].toString());
},
'REGEXP_SUBSTR': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return null;
final m = RegExp(a[1].toString()).firstMatch(a[0].toString());
return m?.group(0);
},
'REGEXP_REPLACE': (a) {
if (a.length < 3 || a[0] == null || a[1] == null || a[2] == null) {
return null;
}
return a[0].toString().replaceAll(RegExp(a[1].toString()), a[2].toString());
},
'RANDOM': (a) => _rng.nextInt(1 << 31),
'INSTR': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return null;
final hay = a[0].toString();
final needle = a[1].toString();
if (needle.isEmpty) return 1;
return hay.indexOf(needle) + 1; // 1-based; 0 == not found
},
'LPAD': (a) {
if (a.isEmpty || a[0] == null) return null;
final s = a[0].toString();
final n = a.length > 1 && a[1] != null ? (a[1] as num).toInt() : s.length;
final pad = a.length > 2 && a[2] != null ? a[2].toString() : ' ';
if (s.length >= n || pad.isEmpty) {
return s.length > n ? s.substring(0, n) : s;
}
final buf = StringBuffer();
while (buf.length + s.length < n) {
buf.write(pad);
}
final padded = buf.toString();
return padded.substring(0, n - s.length) + s;
},
'RPAD': (a) {
if (a.isEmpty || a[0] == null) return null;
final s = a[0].toString();
final n = a.length > 1 && a[1] != null ? (a[1] as num).toInt() : s.length;
final pad = a.length > 2 && a[2] != null ? a[2].toString() : ' ';
if (s.length >= n || pad.isEmpty) {
return s.length > n ? s.substring(0, n) : s;
}
final buf = StringBuffer(s);
while (buf.length < n) {
buf.write(pad);
}
return buf.toString().substring(0, n);
},
// --- Encoding / codepoint helpers ------------------------------------
// HEX(X) — uppercase hex of a BLOB or string's UTF-8 bytes.
'HEX': (a) {
if (a.isEmpty || a[0] == null) return null;
final v = a[0]!;
final bytes = v is List<int> ? v : utf8.encode(v.toString());
final sb = StringBuffer();
for (final b in bytes) {
sb.write((b & 0xFF).toRadixString(16).padLeft(2, '0').toUpperCase());
}
return sb.toString();
},
// ZEROBLOB(n) -- BLOB of n zero bytes.
'ZEROBLOB': (a) {
if (a.isEmpty || a[0] == null) return null;
final n = (a[0] as num).toInt();
if (n < 0) return Uint8List(0);
return Uint8List(n);
},
// RANDOMBLOB(n) -- BLOB of n random bytes.
'RANDOMBLOB': (a) {
if (a.isEmpty || a[0] == null) return null;
final n = (a[0] as num).toInt();
if (n <= 0) return <int>[];
return List<int>.generate(n, (_) => _rng.nextInt(256));
},
// QUOTE(X) -- SQL literal form of X. Strings become 'escaped',
// BLOBs become X'hex', NULL becomes 'NULL', numbers stringified.
'QUOTE': (a) {
if (a.isEmpty) return null;
final v = a[0];
if (v == null) return 'NULL';
if (v is List<int>) {
final sb = StringBuffer("X'");
for (final b in v) {
sb.write((b & 0xFF).toRadixString(16).padLeft(2, '0').toUpperCase());
}
sb.write("'");
return sb.toString();
}
if (v is num || v is bool) return v.toString();
return "'${v.toString().replaceAll("'", "''")}'";
},
// LIKELY(X), UNLIKELY(X), LIKELIHOOD(X, _) -- optimizer hints; we just
// return X. The constant probability arg to LIKELIHOOD is ignored.
'LIKELY': (a) => a.isEmpty ? null : a[0],
'UNLIKELY': (a) => a.isEmpty ? null : a[0],
'LIKELIHOOD': (a) => a.isEmpty ? null : a[0],
// SOUNDEX(s) -- classic Soundex algorithm; returns 4-char code.
// NULL or empty string returns '?000' (matches SQLite's odd convention).
'SOUNDEX': (a) {
if (a.isEmpty || a[0] == null) return '?000';
final s = a[0].toString();
if (s.isEmpty) return '?000';
int upper(int cu) => (cu >= 0x61 && cu <= 0x7A) ? cu - 0x20 : cu;
String code(int cu) {
switch (upper(cu)) {
case 0x42:
case 0x46:
case 0x50:
case 0x56:
return '1';
case 0x43:
case 0x47:
case 0x4A:
case 0x4B:
case 0x51:
case 0x53:
case 0x58:
case 0x5A:
return '2';
case 0x44:
case 0x54:
return '3';
case 0x4C:
return '4';
case 0x4D:
case 0x4E:
return '5';
case 0x52:
return '6';
default:
return '';
}
}
bool isHW(int cu) {
final u = upper(cu);
return u == 0x48 || u == 0x57; // H or W
}
final out = StringBuffer(String.fromCharCode(upper(s.codeUnitAt(0))));
var prev = code(s.codeUnitAt(0));
for (var i = 1; i < s.length && out.length < 4; i++) {
final cu = s.codeUnitAt(i);
if (isHW(cu)) {
// H and W are transparent: skip without resetting prev.
continue;
}
final c = code(cu);
if (c.isEmpty) {
// Vowel or other separator -- don't emit, but reset prev so the
// next consonant can repeat its code.
prev = '';
continue;
}
if (c != prev) out.write(c);
prev = c;
}
while (out.length < 4) {
out.write('0');
}
return out.toString();
},
// BASE64(blob_or_text) -- standard Base64 encoding.
'BASE64': (a) {
if (a.isEmpty || a[0] == null) return null;
final v = a[0]!;
final bytes = v is List<int> ? v : utf8.encode(v.toString());
return base64.encode(bytes);
},
// UNBASE64(text) -- decodes Base64 string back to BLOB; returns NULL
// on malformed input.
'UNBASE64': (a) {
if (a.isEmpty || a[0] == null) return null;
try {
return base64.decode(a[0].toString());
} catch (_) {
return null;
}
},
// UNHEX(X[, ignored]) — inverse of HEX. Returns NULL on malformed input
// (matches SQLite). Optional second arg lists characters to skip; we
// honor the SQLite default of allowing whitespace.
'UNHEX': (a) {
if (a.isEmpty || a[0] == null) return null;
final s = a[0].toString();
final ignore = a.length > 1 && a[1] != null ? a[1].toString() : '';
final bytes = <int>[];
int? pending;
for (final cu in s.codeUnits) {
final ch = String.fromCharCode(cu);
if (ignore.contains(ch)) continue;
final d = _hexDigit(cu);
if (d < 0) return null;
if (pending == null) {
pending = d;
} else {
bytes.add((pending << 4) | d);
pending = null;
}
}
if (pending != null) return null;
return bytes;
},
// UNICODE(X) — codepoint of the first character of X, or NULL if X is
// null/empty.
'UNICODE': (a) {
if (a.isEmpty || a[0] == null) return null;
final s = a[0].toString();
if (s.isEmpty) return null;
return s.runes.first;
},
// CHAR(X1, X2, ...) — string formed from the given Unicode codepoints.
'CHAR': (a) {
final cps = <int>[];
for (final v in a) {
if (v == null) continue;
cps.add((v as num).toInt());
}
return String.fromCharCodes(cps);
},
// PRINTF(format, args...) and its alias FORMAT(...). Implements the
// SQLite printf subset: %d %i %u %x %X %o %c %s %f %e %g %p %% %q %Q
// with optional flags (`-+ 0#`), width, and precision. NULL format
// returns NULL; NULL substitution args are rendered as 'NULL' for %s
// and 0 for numerics, matching SQLite.
'PRINTF': (a) {
if (a.isEmpty || a[0] == null) return null;
return _sqlitePrintf(a[0].toString(), a.sublist(1));
},
'FORMAT': (a) => kScalarFunctions['PRINTF']!(a),
// --- Datetime --------------------------------------------------------
'CURRENT_TIMESTAMP': (a) => _fmtDateTime(DateTime.now().toUtc(), full: true),
'CURRENT_DATE': (a) => _fmtDate(DateTime.now().toUtc()),
'CURRENT_TIME': (a) => _fmtTime(DateTime.now().toUtc()),
'DATE': (a) => _datetimeFn(a, kind: _DTKind.date),
'TIME': (a) => _datetimeFn(a, kind: _DTKind.time),
'DATETIME': (a) => _datetimeFn(a, kind: _DTKind.full),
'STRFTIME': (a) {
if (a.isEmpty || a[0] == null) return null;
final fmt = a[0].toString();
final dt = _resolveDateTime(a.sublist(1));
if (dt == null) return null;
return _strftime(fmt, dt);
},
// TIMEDIFF(a, b) -- SQLite 3.43+. Returns the difference (a - b) as a
// datetime string with leading sign: '+/-YYYY-MM-DD HH:MM:SS.SSS'.
'TIMEDIFF': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return null;
final da = _resolveDateTime([a[0]]);
final db = _resolveDateTime([a[1]]);
if (da == null || db == null) return null;
final diffMicros = da.difference(db).inMicroseconds;
final neg = diffMicros < 0;
var abs = diffMicros.abs();
final days = abs ~/ Duration.microsecondsPerDay;
abs -= days * Duration.microsecondsPerDay;
final hours = abs ~/ Duration.microsecondsPerHour;
abs -= hours * Duration.microsecondsPerHour;
final mins = abs ~/ Duration.microsecondsPerMinute;
abs -= mins * Duration.microsecondsPerMinute;
final secs = abs ~/ Duration.microsecondsPerSecond;
abs -= secs * Duration.microsecondsPerSecond;
final ms = abs ~/ 1000;
String two(int v) => v.toString().padLeft(2, '0');
String three(int v) => v.toString().padLeft(3, '0');
final yearDays = days; // expressed as days only; year/month not split.
return '${neg ? '-' : '+'}0000-00-${two(yearDays)} '
'${two(hours)}:${two(mins)}:${two(secs)}.${three(ms)}';
},
'JULIANDAY': (a) {
final dt = _resolveDateTime(a);
if (dt == null) return null;
return _toJulianDay(dt);
},
'UNIXEPOCH': (a) {
final dt = _resolveDateTime(a);
if (dt == null) return null;
return dt.millisecondsSinceEpoch ~/ 1000;
},
'TYPEOF': (a) {
final v = a.isEmpty ? null : a[0];
if (v == null) return 'null';
if (v is int) return 'integer';
if (v is double) return 'real';
if (v is bool) return 'integer';
if (v is String) return 'text';
return 'blob';
},
// ---- JSON1 (minimal) -----------------------------------------------------
'JSON': (a) => _propagateNull(a, () {
// Validate + reformat (canonical JSON encoding).
final v = jsonDecode(a[0].toString());
return jsonEncode(v);
}),
'JSON_VALID': (a) {
if (a.isEmpty || a[0] == null) return 0;
try {
jsonDecode(a[0].toString());
return 1;
} catch (_) {
return 0;
}
},
'JSON_TYPE': (a) {
if (a.isEmpty || a[0] == null) return null;
Object? v;
try {
v = jsonDecode(a[0].toString());
} catch (_) {
return null;
}
if (a.length >= 2) {
v = jsonPathLookup(v, a[1].toString());
}
if (v == null) return 'null';
if (v is bool) return v ? 'true' : 'false';
if (v is int) return 'integer';
if (v is double) return 'real';
if (v is String) return 'text';
if (v is List) return 'array';
if (v is Map) return 'object';
return null;
},
'JSON_EXTRACT': (a) {
if (a.isEmpty || a[0] == null) return null;
Object? root;
try {
root = jsonDecode(a[0].toString());
} catch (_) {
return null;
}
// SQLite: with one path returns the SQL value (JSON unwrapped for
// scalars, JSON text for arrays/objects). With multiple paths returns
// a JSON array of values.
if (a.length == 2) {
final v = jsonPathLookup(root, a[1].toString());
return _jsonScalarOrText(v);
}
final out = <Object?>[];
for (var i = 1; i < a.length; i++) {
out.add(jsonPathLookup(root, a[i].toString()));
}
return jsonEncode(out);
},
'JSON_ARRAY': (a) => jsonEncode(a.map(_jsonValueOf).toList()),
'JSON_OBJECT': (a) {
if (a.length.isOdd) {
throw StateError('json_object requires an even number of arguments');
}
final m = <String, Object?>{};
for (var i = 0; i < a.length; i += 2) {
m[a[i].toString()] = _jsonValueOf(a[i + 1]);
}
return jsonEncode(m);
},
'JSON_ARRAY_LENGTH': (a) {
if (a.isEmpty || a[0] == null) return null;
Object? v;
try {
v = jsonDecode(a[0].toString());
} catch (_) {
return null;
}
if (a.length >= 2) v = jsonPathLookup(v, a[1].toString());
return v is List ? v.length : 0;
},
'JSON_QUOTE': (a) {
if (a.isEmpty) return 'null';
return jsonEncode(a[0]);
},
// RAISE(IGNORE) / RAISE(ABORT|FAIL|ROLLBACK, 'msg'). Used in trigger
// bodies to abort the host operation. Implemented by throwing a typed
// exception that the trigger executor recognises.
'RAISE': (a) {
final action = (a.isEmpty ? 'ABORT' : a[0].toString()).toUpperCase();
final msg = a.length >= 2 ? a[1]?.toString() ?? '' : '';
throw RaiseException(action, msg);
},
// json_set / json_insert / json_replace / json_remove / json_patch take
// (json, path, value, path, value, ...). Differences:
// - set: overwrite if exists, create if not
// - insert: create if not, never overwrite existing
// - replace: overwrite if exists, never create
// - remove: delete each path
'JSON_SET': (a) => _jsonMutate(a, overwrite: true, createMissing: true),
'JSON_INSERT': (a) => _jsonMutate(a, overwrite: false, createMissing: true),
'JSON_REPLACE': (a) => _jsonMutate(a, overwrite: true, createMissing: false),
'JSON_REMOVE': (a) {
if (a.isEmpty || a[0] == null) return null;
Object? root;
try {
root = jsonDecode(a[0].toString());
} catch (_) {
return null;
}
for (var i = 1; i < a.length; i++) {
if (a[i] == null) continue;
root = jsonPathRemove(root, a[i].toString());
}
return jsonEncode(root);
},
'JSON_PATCH': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return null;
Object? base;
Object? patch;
try {
base = jsonDecode(a[0].toString());
patch = jsonDecode(a[1].toString());
} catch (_) {
return null;
}
return jsonEncode(_rfc7396Merge(base, patch));
},
// Pretty-print JSON with 2-space indent (or a caller-supplied indent
// string). Returns NULL on invalid JSON. SQLite 3.46+.
'JSON_PRETTY': (a) {
if (a.isEmpty || a[0] == null) return null;
Object? v;
try {
v = jsonDecode(a[0].toString());
} catch (_) {
return null;
}
final indent = a.length >= 2 && a[1] != null ? a[1].toString() : ' ';
return JsonEncoder.withIndent(indent).convert(v);
},
// -- JSONB family (SQLite 3.45+). The reference engine returns a
// compact binary representation; our pure-Dart engine has no separate
// JSONB encoder, so jsonb(x) returns canonical text JSON. Functions
// that consume "JSONB" accept text JSON transparently because every
// JSON parser here uses jsonDecode. This keeps round-trips identical
// to SQLite for all values that are valid in either form.
'JSONB': (a) {
if (a.isEmpty || a[0] == null) return null;
try {
return jsonEncode(jsonDecode(a[0].toString()));
} catch (_) {
return jsonEncode(_jsonValueOf(a[0]));
}
},
'JSONB_EXTRACT': (a) => kScalarFunctions['JSON_EXTRACT']!(a),
'JSONB_ARRAY': (a) => kScalarFunctions['JSON_ARRAY']!(a),
'JSONB_OBJECT': (a) => kScalarFunctions['JSON_OBJECT']!(a),
'JSONB_SET': (a) => kScalarFunctions['JSON_SET']!(a),
'JSONB_INSERT': (a) => kScalarFunctions['JSON_INSERT']!(a),
'JSONB_REPLACE': (a) => kScalarFunctions['JSON_REPLACE']!(a),
'JSONB_REMOVE': (a) => kScalarFunctions['JSON_REMOVE']!(a),
'JSONB_PATCH': (a) => kScalarFunctions['JSON_PATCH']!(a),
'JSONB_QUOTE': (a) => kScalarFunctions['JSON_QUOTE']!(a),
'JSONB_TYPE': (a) => kScalarFunctions['JSON_TYPE']!(a),
'JSONB_VALID': (a) => kScalarFunctions['JSON_VALID']!(a),
'JSONB_ARRAY_LENGTH': (a) => kScalarFunctions['JSON_ARRAY_LENGTH']!(a),
// ---- SQLite introspection / connection-state scalars -----------------
// last_insert_rowid(): the ROWID of the most recent successful INSERT.
// Returns 0 before any insert has occurred.
'LAST_INSERT_ROWID': (a) => connStateLookup?.call('last_insert_rowid') ?? 0,
// changes(): number of rows modified by the most recently completed
// INSERT, UPDATE or DELETE statement.
'CHANGES': (a) => connStateLookup?.call('changes') ?? 0,
// total_changes(): total rows modified since the connection opened.
'TOTAL_CHANGES': (a) => connStateLookup?.call('total_changes') ?? 0,
// bit_count(x): population count (number of set bits in integer x).
'BIT_COUNT': (a) => _propagateNull(a, () {
var v = (a[0] as num).toInt();
var c = 0;
while (v != 0) {
c += v & 1;
v = v >>> 1;
}
return c;
}),
// load_extension/sqlite_log: present for compatibility but no-op.
'LOAD_EXTENSION': (a) => null,
'SQLITE_LOG': (a) => null,
// database()/schema(): SQLite always reports 'main' for the default
// schema; we don't track ATTACH-time DB names through statements yet.
'DATABASE': (a) => 'main',
'SCHEMA': (a) => 'main',
// sqlite_version(): version string of the SQLite release this engine
// targets for feature parity.
'SQLITE_VERSION': (a) => kSqliteVersionString,
// sqlite_source_id(): in real SQLite this is a build/source hash. We
// return a stable identifier that names this engine.
'SQLITE_SOURCE_ID': (a) =>
'dart-db-server (parity target $kSqliteVersionString)',
// sqlite_compileoption_used(opt): always 0 — no compile options.
'SQLITE_COMPILEOPTION_USED': (a) => 0,
// sqlite_compileoption_get(n): always NULL — no compile options.
'SQLITE_COMPILEOPTION_GET': (a) => null,
// sqlite_offset(col): SQLite returns the byte offset of the column's
// value in the DB file, or NULL when not on disk. We have no stable
// mapping for the in-memory engine; return NULL unconditionally.
'SQLITE_OFFSET': (a) => null,
// subtype(value): SQLite 3.45+. Returns the application-defined
// subtype of an SQL value. We have no subtype machinery, so 0.
'SUBTYPE': (a) => 0,
// json_error_position(json): SQLite 3.42+. Returns 0 when the input
// is a valid JSON value, otherwise the 1-based character position of
// the first parse error. NULL input yields NULL.
'JSON_ERROR_POSITION': (a) {
if (a.isEmpty || a[0] == null) return null;
final s = a[0].toString();
try {
jsonDecode(s);
return 0;
} on FormatException catch (e) {
final off = e.offset;
return (off == null || off < 0) ? 1 : off + 1;
}
},
// ---- FTS5 ranking ------------------------------------------------------
'FTS5_TF': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return 0;
return fts5TermFrequency(a[0].toString(), a[1].toString());
},
'BM25': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return 0;
final k1 = a.length > 2 && a[2] != null ? (a[2] as num).toDouble() : 1.2;
final b = a.length > 3 && a[3] != null ? (a[3] as num).toDouble() : 0.75;
return fts5Bm25(a[0].toString(), a[1].toString(), k1: k1, b: b);
},
// Corpus-aware BM25: `BM25_CORPUS(text, query, 'table', 'column'[, k1[, b]])`.
// Looks up the cached Fts5Index for the named table/column on the
// active database (Database.current) and computes a properly
// IDF-weighted, length-normalised BM25 score for [text]. Useful in
// ORDER BY on fts5 virtual tables, e.g.:
// SELECT body FROM docs WHERE body MATCH 'cat'
// ORDER BY bm25_corpus(body, 'cat', 'docs', 'body') DESC
'BM25_CORPUS': (a) {
if (a.length < 4 ||
a[0] == null ||
a[1] == null ||
a[2] == null ||
a[3] == null) {
return 0;
}
final lookup = fts5CorpusLookup;
if (lookup == null) {
// No active database context — fall back to single-doc BM25 so
// the function is still useful at the Dart-API layer.
return fts5Bm25(a[0].toString(), a[1].toString());
}
final k1 = a.length > 4 && a[4] != null ? (a[4] as num).toDouble() : 1.2;
final b = a.length > 5 && a[5] != null ? (a[5] as num).toDouble() : 0.75;
final idx = lookup(a[2].toString(), a[3].toString());
if (idx == null) return 0;
return idx.bm25Text(a[0].toString(), a[1].toString(), k1: k1, b: b);
},
// --- MySQL datetime aliases ----------------------------------------
'NOW': (a) => _fmtDateTime(DateTime.now().toUtc(), full: true),
'SYSDATE': (a) => _fmtDateTime(DateTime.now().toUtc(), full: true),
'CURDATE': (a) => _fmtDate(DateTime.now().toUtc()),
'CURTIME': (a) => _fmtTime(DateTime.now().toUtc()),
'UTC_TIMESTAMP': (a) => _fmtDateTime(DateTime.now().toUtc(), full: true),
'UTC_DATE': (a) => _fmtDate(DateTime.now().toUtc()),
'UTC_TIME': (a) => _fmtTime(DateTime.now().toUtc()),
'UNIX_TIMESTAMP': (a) {
final dt = a.isEmpty ? DateTime.now().toUtc() : _resolveDateTime(a);
if (dt == null) return null;
return dt.millisecondsSinceEpoch ~/ 1000;
},
'FROM_UNIXTIME': (a) {
if (a.isEmpty || a[0] == null) return null;
final secs = (a[0] as num).toInt();
final dt = DateTime.fromMillisecondsSinceEpoch(secs * 1000, isUtc: true);
if (a.length > 1 && a[1] != null) {
return _strftime(_mysqlFmtToStrftime(a[1].toString()), dt);
}
return _fmtDateTime(dt, full: true);
},
'YEAR': (a) {
final dt = _resolveDateTime(a);
return dt?.year;
},
'MONTH': (a) {
final dt = _resolveDateTime(a);
return dt?.month;
},
'DAY': (a) {
final dt = _resolveDateTime(a);
return dt?.day;
},
'DAYOFMONTH': (a) {
final dt = _resolveDateTime(a);
return dt?.day;
},
'HOUR': (a) {
final dt = _resolveDateTime(a);
return dt?.hour;
},
'MINUTE': (a) {
final dt = _resolveDateTime(a);
return dt?.minute;
},
'SECOND': (a) {
final dt = _resolveDateTime(a);
return dt?.second;
},
'MICROSECOND': (a) {
final dt = _resolveDateTime(a);
return dt == null ? null : dt.millisecond * 1000 + dt.microsecond;
},
'DAYOFWEEK': (a) {
// MySQL: 1=Sunday..7=Saturday.
final dt = _resolveDateTime(a);
if (dt == null) return null;
return (dt.weekday % 7) + 1;
},
'WEEKDAY': (a) {
// MySQL: 0=Monday..6=Sunday.
final dt = _resolveDateTime(a);
if (dt == null) return null;
return dt.weekday - 1;
},
'DAYOFYEAR': (a) {
final dt = _resolveDateTime(a);
return dt == null ? null : _dayOfYear(dt);
},
'DAYNAME': (a) {
final dt = _resolveDateTime(a);
if (dt == null) return null;
const names = [
'Monday',
'Tuesday',
'Wednesday',
'Thursday',
'Friday',
'Saturday',
'Sunday',
];
return names[dt.weekday - 1];
},
'MONTHNAME': (a) {
final dt = _resolveDateTime(a);
if (dt == null) return null;
const names = [
'January',
'February',
'March',
'April',
'May',
'June',
'July',
'August',
'September',
'October',
'November',
'December',
];
return names[dt.month - 1];
},
'LAST_DAY': (a) {
final dt = _resolveDateTime(a);
if (dt == null) return null;
final nextMonth = dt.month == 12
? DateTime.utc(dt.year + 1, 1, 1)
: DateTime.utc(dt.year, dt.month + 1, 1);
final last = nextMonth.subtract(const Duration(days: 1));
return _fmtDate(last);
},
'DATEDIFF': (a) {
if (a.length < 2) return null;
final da = _resolveDateTime([a[0]]);
final db = _resolveDateTime([a[1]]);
if (da == null || db == null) return null;
final aMid = DateTime.utc(da.year, da.month, da.day);
final bMid = DateTime.utc(db.year, db.month, db.day);
return aMid.difference(bMid).inDays;
},
'DATE_FORMAT': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return null;
final dt = _resolveDateTime([a[0]]);
if (dt == null) return null;
return _mysqlDateFormat(a[1].toString(), dt);
},
'TIME_FORMAT': (a) {
if (a.length < 2 || a[0] == null || a[1] == null) return null;
final dt = _resolveDateTime([a[0]]);
if (dt == null) return null;
return _mysqlDateFormat(a[1].toString(), dt);
},
'STR_TO_DATE': (a) {
if (a.length < 2 || a[0] == null) return null;
// We do not implement a full MySQL parser; defer to ISO parsing of
// the input string and ignore the format. Good enough for typical
// 'YYYY-MM-DD' / 'YYYY-MM-DD HH:MM:SS' inputs.
return _resolveDateTime([a[0]]) == null
? null
: _fmtDateTime(_resolveDateTime([a[0]])!, full: true);
},
// ---- Vector / embedding functions (FAISS-style) -----------------------
// Constructor: TEXT '[1,2,3]' or BLOB / List -> canonical vector BLOB.
'VEC': (a) {
if (a.isEmpty || a[0] == null) return null;
final v = coerceVector(a[0]);
return v == null ? null : encodeVectorBlob(v);
},
'VEC_F32': (a) => kScalarFunctions['VEC']!(a),
// VEC_DIM(v) -> INTEGER dimension.
'VEC_DIM': (a) {
if (a.isEmpty || a[0] == null) return null;
return coerceVector(a[0])!.dim;
},
// V32 sqlite-vec compat: vec_length returns BYTES, not dimensions.
// Matches sqlite-vec: `vec_length(v) = dim * 4` for float32 vectors.
'VEC_LENGTH': (a) {
if (a.isEmpty || a[0] == null) return null;
return coerceVector(a[0])!.dim * 4;
},
// V32 sqlite-vec compat: vec_type always returns 'float32' — the
// only vector element type we support.
'VEC_TYPE': (a) {
if (a.isEmpty || a[0] == null) return null;
return 'float32';
},
// V32 sqlite-vec compat: vec_slice(v, start, end) returns a new
// vector containing elements [start, end). Negative or out-of-range
// indices are clamped.
'VEC_SLICE': (a) => _propagateNull(a, () {
final v = coerceVector(a[0])!;
final start = (a[1] as num).toInt().clamp(0, v.dim);
final end = (a[2] as num).toInt().clamp(start, v.dim);
return encodeVectorBlob(
Vector.fromList(v.values.sublist(start, end)),
);
}),
// VEC_TO_JSON(v) -> TEXT '[...]' for debug / export.
'VEC_TO_JSON': (a) {
if (a.isEmpty || a[0] == null) return null;
return coerceVector(a[0])!.toString();
},
// Distance metrics.
'VEC_L2SQ': (a) => _propagateNull(
a,
() => vecL2Sq(coerceVector(a[0])!, coerceVector(a[1])!),
),
'VEC_L2': (a) => _propagateNull(
a,
() => vecL2(coerceVector(a[0])!, coerceVector(a[1])!),
),
'VEC_DISTANCE_L2': (a) => kScalarFunctions['VEC_L2']!(a),
'VEC_IP': (a) => _propagateNull(
a,
() => vecInnerProduct(coerceVector(a[0])!, coerceVector(a[1])!),
),
'VEC_DOT': (a) => kScalarFunctions['VEC_IP']!(a),
'VEC_COSINE': (a) => _propagateNull(
a,
() => vecCosineDistance(coerceVector(a[0])!, coerceVector(a[1])!),
),
'VEC_DISTANCE_COSINE': (a) => kScalarFunctions['VEC_COSINE']!(a),
'VEC_COSINE_SIM': (a) => _propagateNull(
a,
() => vecCosineSimilarity(coerceVector(a[0])!, coerceVector(a[1])!),
),
// Norm and normalization.
'VEC_NORM': (a) {
if (a.isEmpty || a[0] == null) return null;
return vecNorm(coerceVector(a[0])!);
},
'VEC_NORMALIZE': (a) {
if (a.isEmpty || a[0] == null) return null;
return encodeVectorBlob(vecNormalize(coerceVector(a[0])!));
},
// Element-wise arithmetic (useful for centroid math and tests).
'VEC_ADD': (a) => _propagateNull(
a,
() =>
encodeVectorBlob(vecAdd(coerceVector(a[0])!, coerceVector(a[1])!)),
),
'VEC_SUB': (a) => _propagateNull(
a,
() =>
encodeVectorBlob(vecSub(coerceVector(a[0])!, coerceVector(a[1])!)),
),
// ---- Hybrid retrieval / rank fusion ----------------------------------
// RRF_SCORE(rank[, k]) — one term of Reciprocal Rank Fusion. Returns
// `1 / (k + rank)` for a non-NULL rank, or 0 when the row didn't
// appear in the ranker's list. `k` defaults to 60 (the FAISS /
// Cormack & Buettcher canonical value).
'RRF_SCORE': (a) {
if (a.isEmpty || a[0] == null) return 0.0;
final rank = (a[0] as num).toDouble();
final k = a.length >= 2 && a[1] != null ? (a[1] as num).toDouble() : 60.0;
return 1.0 / (k + rank);
},
// RRF(rank1, rank2, ...) — sum of `1 / (60 + rank_i)` over non-NULL
// ranks. Missing / NULL ranks contribute 0. Convenience for the
// canonical two- or three-way fusion `RRF(fts_rank, vec_rank)`.
'RRF': (a) {
var s = 0.0;
for (final v in a) {
if (v == null) continue;
s += 1.0 / (60.0 + (v as num).toDouble());
}
return s;
},
// HYBRID_SCORE(vec_distance, fts_score, alpha) — convex-combination
// hybrid score. Higher = more relevant.
// result = alpha * (1 / (1 + vec_distance)) + (1 - alpha) * fts_score
// Vector distance is converted to a similarity in [0, 1] via
// `1 / (1 + d)` (works for L2, L2SQ, and cosine distance). NULLs on
// either side contribute 0 for that term. `alpha` in [0, 1] controls
// the mix (0 = FTS only, 1 = vector only).
'HYBRID_SCORE': (a) {
if (a.length < 3) return null;
final vec = a[0];
final fts = a[1];
final alpha = a[2] == null ? 0.5 : (a[2] as num).toDouble();
final vecSim = vec == null ? 0.0 : 1.0 / (1.0 + (vec as num).toDouble());
final ftsScore = fts == null ? 0.0 : (fts as num).toDouble();
return alpha * vecSim + (1.0 - alpha) * ftsScore;
},
};
