Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 5e5b063

Browse filesBrowse files
llimlliblovasoa
llimllib
and
lovasoa
authored
Enable custom aggregate functions (take 2) (#529)
* initial commit. * documentation * remove no-longer-valid type * close over state initialization for performance * link documentation in comment * more testing * run tests if they're main * accept a single arg * this kind of works but I'm abandoning this branch Basically it seems that the sqlite extension pattern of 'allocate a struct and stick it in the context pointer' is not going to work for us here. I wonder if using the id of the pointer returned by sqlite3_aggregate_context would be enough? Since no two functions could use the same pointer, per https://www.sqlite.org/c3ref/aggregate_context.html ? * a middle road sqlite3_agg_context solution * try out auto-updating state * improve quantile test, add multiple agg test * add a null to the test * acorn fails to parse ||=, whatever * make eslint happy * make initial_value an argument * test step and finalize exceptions * add memory leak test * update docs to current interface * delete state in exception handlers * remove null state * return init function and document object * more tests and update back to init function * update redefinition test for new interface * update README to match fixed signature * more consistent test formatting * Update README.md Co-authored-by: Ophir LOJKINE <contact@ophir.dev> * clarify what exactly the result will contain * Update README.md * Update README.md * Update README.md * Update README.md * Update README.md * Improve documentation and type annotations * ignore documentation in eslintrc * reduce code size Thanks a lot, @llimllib ! Co-authored-by: dogquery <> Co-authored-by: Ophir LOJKINE <contact@ophir.dev>
1 parent 577056b commit 5e5b063
Copy full SHA for 5e5b063

6 files changed

+451
-62
lines changed

‎.eslintrc.js

Copy file name to clipboardExpand all lines: .eslintrc.js
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -16,6 +16,7 @@ module.exports = {
1616
ignorePatterns: [
1717
"/dist/",
1818
"/examples/",
19+
"/documentation/",
1920
"/node_modules/",
2021
"/out/",
2122
"/src/shell-post.js",

‎README.md

Copy file name to clipboardExpand all lines: README.md
+27Lines changed: 27 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -75,6 +75,33 @@ db.create_function("add_js", add);
7575
// Run a query in which the function is used
7676
db.run("INSERT INTO hello VALUES (add_js(7, 3), add_js('Hello ', 'world'));"); // Inserts 10 and 'Hello world'
7777

78+
// You can create custom aggregation functions, by passing a name
79+
// and a set of functions to `db.create_aggregate`:
80+
//
81+
// - an `init` function. This function receives no argument and returns
82+
// the initial value for the state of the aggregate function.
83+
// - a `step` function. This function takes two arguments
84+
// - the current state of the aggregation
85+
// - a new value to aggregate to the state
86+
// It should return a new value for the state.
87+
// - a `finalize` function. This function receives a state object, and
88+
// returns the final value of the aggregate. It can be omitted, in which case
89+
// the final value of the state will be returned directly by the aggregate function.
90+
//
91+
// Here is an example aggregation function, `json_agg`, which will collect all
92+
// input values and return them as a JSON array:
93+
db.create_aggregate(
94+
"json_agg",
95+
{
96+
init: () => [],
97+
step: (state, val) => [...state, val],
98+
finalize: (state) => JSON.stringify(state),
99+
}
100+
);
101+
102+
db.exec("SELECT json_agg(column1) FROM (VALUES ('hello'), ('world'))");
103+
// -> The result of the query is the string '["hello","world"]'
104+
78105
// Export the database to an Uint8Array containing the SQLite database file
79106
const binaryArray = db.export();
80107
```

‎src/api.js

Copy file name to clipboardExpand all lines: src/api.js
+210-62Lines changed: 210 additions & 62 deletions
Original file line numberDiff line numberDiff line change
@@ -225,6 +225,14 @@ Module["onRuntimeInitialized"] = function onRuntimeInitialized() {
225225
"",
226226
["number", "string", "number"]
227227
);
228+
229+
// https://www.sqlite.org/c3ref/aggregate_context.html
230+
// void *sqlite3_aggregate_context(sqlite3_context*, int nBytes)
231+
var sqlite3_aggregate_context = cwrap(
232+
"sqlite3_aggregate_context",
233+
"number",
234+
["number", "number"]
235+
);
228236
var registerExtensionFunctions = cwrap(
229237
"RegisterExtensionFunctions",
230238
"number",
@@ -1131,81 +1139,90 @@ Module["onRuntimeInitialized"] = function onRuntimeInitialized() {
11311139
return sqlite3_changes(this.db);
11321140
};
11331141

1134-
/** Register a custom function with SQLite
1135-
@example Register a simple function
1136-
db.create_function("addOne", function (x) {return x+1;})
1137-
db.exec("SELECT addOne(1)") // = 2
1142+
var extract_blob = function extract_blob(ptr) {
1143+
var size = sqlite3_value_bytes(ptr);
1144+
var blob_ptr = sqlite3_value_blob(ptr);
1145+
var blob_arg = new Uint8Array(size);
1146+
for (var j = 0; j < size; j += 1) {
1147+
blob_arg[j] = HEAP8[blob_ptr + j];
1148+
}
1149+
return blob_arg;
1150+
};
11381151

1139-
@param {string} name the name of the function as referenced in
1140-
SQL statements.
1141-
@param {function} func the actual function to be executed.
1142-
@return {Database} The database object. Useful for method chaining
1143-
*/
1152+
var parseFunctionArguments = function parseFunctionArguments(argc, argv) {
1153+
var args = [];
1154+
for (var i = 0; i < argc; i += 1) {
1155+
var value_ptr = getValue(argv + (4 * i), "i32");
1156+
var value_type = sqlite3_value_type(value_ptr);
1157+
var arg;
1158+
if (
1159+
value_type === SQLITE_INTEGER
1160+
|| value_type === SQLITE_FLOAT
1161+
) {
1162+
arg = sqlite3_value_double(value_ptr);
1163+
} else if (value_type === SQLITE_TEXT) {
1164+
arg = sqlite3_value_text(value_ptr);
1165+
} else if (value_type === SQLITE_BLOB) {
1166+
arg = extract_blob(value_ptr);
1167+
} else arg = null;
1168+
args.push(arg);
1169+
}
1170+
return args;
1171+
};
1172+
var setFunctionResult = function setFunctionResult(cx, result) {
1173+
switch (typeof result) {
1174+
case "boolean":
1175+
sqlite3_result_int(cx, result ? 1 : 0);
1176+
break;
1177+
case "number":
1178+
sqlite3_result_double(cx, result);
1179+
break;
1180+
case "string":
1181+
sqlite3_result_text(cx, result, -1, -1);
1182+
break;
1183+
case "object":
1184+
if (result === null) {
1185+
sqlite3_result_null(cx);
1186+
} else if (result.length != null) {
1187+
var blobptr = allocate(result, ALLOC_NORMAL);
1188+
sqlite3_result_blob(cx, blobptr, result.length, -1);
1189+
_free(blobptr);
1190+
} else {
1191+
sqlite3_result_error(cx, (
1192+
"Wrong API use : tried to return a value "
1193+
+ "of an unknown type (" + result + ")."
1194+
), -1);
1195+
}
1196+
break;
1197+
default:
1198+
sqlite3_result_null(cx);
1199+
}
1200+
};
1201+
1202+
/** Register a custom function with SQLite
1203+
@example <caption>Register a simple function</caption>
1204+
db.create_function("addOne", function (x) {return x+1;})
1205+
db.exec("SELECT addOne(1)") // = 2
1206+
1207+
@param {string} name the name of the function as referenced in
1208+
SQL statements.
1209+
@param {function} func the actual function to be executed.
1210+
@return {Database} The database object. Useful for method chaining
1211+
*/
11441212
Database.prototype["create_function"] = function create_function(
11451213
name,
11461214
func
11471215
) {
11481216
function wrapped_func(cx, argc, argv) {
1217+
var args = parseFunctionArguments(argc, argv);
11491218
var result;
1150-
function extract_blob(ptr) {
1151-
var size = sqlite3_value_bytes(ptr);
1152-
var blob_ptr = sqlite3_value_blob(ptr);
1153-
var blob_arg = new Uint8Array(size);
1154-
for (var j = 0; j < size; j += 1) {
1155-
blob_arg[j] = HEAP8[blob_ptr + j];
1156-
}
1157-
return blob_arg;
1158-
}
1159-
var args = [];
1160-
for (var i = 0; i < argc; i += 1) {
1161-
var value_ptr = getValue(argv + (4 * i), "i32");
1162-
var value_type = sqlite3_value_type(value_ptr);
1163-
var arg;
1164-
if (
1165-
value_type === SQLITE_INTEGER
1166-
|| value_type === SQLITE_FLOAT
1167-
) {
1168-
arg = sqlite3_value_double(value_ptr);
1169-
} else if (value_type === SQLITE_TEXT) {
1170-
arg = sqlite3_value_text(value_ptr);
1171-
} else if (value_type === SQLITE_BLOB) {
1172-
arg = extract_blob(value_ptr);
1173-
} else arg = null;
1174-
args.push(arg);
1175-
}
11761219
try {
11771220
result = func.apply(null, args);
11781221
} catch (error) {
11791222
sqlite3_result_error(cx, error, -1);
11801223
return;
11811224
}
1182-
switch (typeof result) {
1183-
case "boolean":
1184-
sqlite3_result_int(cx, result ? 1 : 0);
1185-
break;
1186-
case "number":
1187-
sqlite3_result_double(cx, result);
1188-
break;
1189-
case "string":
1190-
sqlite3_result_text(cx, result, -1, -1);
1191-
break;
1192-
case "object":
1193-
if (result === null) {
1194-
sqlite3_result_null(cx);
1195-
} else if (result.length != null) {
1196-
var blobptr = allocate(result, ALLOC_NORMAL);
1197-
sqlite3_result_blob(cx, blobptr, result.length, -1);
1198-
_free(blobptr);
1199-
} else {
1200-
sqlite3_result_error(cx, (
1201-
"Wrong API use : tried to return a value "
1202-
+ "of an unknown type (" + result + ")."
1203-
), -1);
1204-
}
1205-
break;
1206-
default:
1207-
sqlite3_result_null(cx);
1208-
}
1225+
setFunctionResult(cx, result);
12091226
}
12101227
if (Object.prototype.hasOwnProperty.call(this.functions, name)) {
12111228
removeFunction(this.functions[name]);
@@ -1229,6 +1246,137 @@ Module["onRuntimeInitialized"] = function onRuntimeInitialized() {
12291246
return this;
12301247
};
12311248

1249+
/** Register a custom aggregate with SQLite
1250+
@example <caption>Register a custom sum function</caption>
1251+
db.create_aggregate("js_sum", {
1252+
init: () => 0,
1253+
step: (state, value) => state + value,
1254+
finalize: state => state
1255+
});
1256+
db.exec("SELECT js_sum(column1) FROM (VALUES (1), (2))"); // = 3
1257+
1258+
@param {string} name the name of the aggregate as referenced in
1259+
SQL statements.
1260+
@param {object} aggregateFunctions
1261+
object containing at least a step function.
1262+
@param {function(): T} [aggregateFunctions.init = ()=>null]
1263+
a function receiving no arguments and returning an initial
1264+
value for the aggregate function. The initial value will be
1265+
null if this key is omitted.
1266+
@param {function(T, any) : T} aggregateFunctions.step
1267+
a function receiving the current state and a value to aggregate
1268+
and returning a new state.
1269+
Will receive the value from init for the first step.
1270+
@param {function(T): any} [aggregateFunctions.finalize = (state)=>state]
1271+
a function returning the result of the aggregate function
1272+
given its final state.
1273+
If omitted, the value returned by the last step
1274+
will be used as the final value.
1275+
@return {Database} The database object. Useful for method chaining
1276+
@template T
1277+
*/
1278+
Database.prototype["create_aggregate"] = function create_aggregate(
1279+
name,
1280+
aggregateFunctions
1281+
) {
1282+
// Default initializer and finalizer
1283+
var init = aggregateFunctions["init"]
1284+
|| function init() { return null; };
1285+
var finalize = aggregateFunctions["finalize"]
1286+
|| function finalize(state) { return state; };
1287+
var step = aggregateFunctions["step"];
1288+
1289+
if (!step) {
1290+
throw "An aggregate function must have a step function in " + name;
1291+
}
1292+
1293+
// state is a state object; we'll use the pointer p to serve as the
1294+
// key for where we hold our state so that multiple invocations of
1295+
// this function never step on each other
1296+
var state = {};
1297+
1298+
function wrapped_step(cx, argc, argv) {
1299+
// > The first time the sqlite3_aggregate_context(C,N) routine is
1300+
// > called for a particular aggregate function, SQLite allocates N
1301+
// > bytes of memory, zeroes out that memory, and returns a pointer
1302+
// > to the new memory.
1303+
//
1304+
// We're going to use that pointer as a key to our state array,
1305+
// since using sqlite3_aggregate_context as it's meant to be used
1306+
// through webassembly seems to be very difficult. Just allocate
1307+
// one byte.
1308+
var p = sqlite3_aggregate_context(cx, 1);
1309+
1310+
// If this is the first invocation of wrapped_step, call `init`
1311+
//
1312+
// Make sure that every path through the step and finalize
1313+
// functions deletes the value state[p] when it's done so we don't
1314+
// leak memory and possibly stomp the init value of future calls
1315+
if (!Object.hasOwnProperty.call(state, p)) state[p] = init();
1316+
1317+
var args = parseFunctionArguments(argc, argv);
1318+
var mergedArgs = [state[p]].concat(args);
1319+
try {
1320+
state[p] = step.apply(null, mergedArgs);
1321+
} catch (error) {
1322+
delete state[p];
1323+
sqlite3_result_error(cx, error, -1);
1324+
}
1325+
}
1326+
1327+
function wrapped_finalize(cx) {
1328+
var result;
1329+
var p = sqlite3_aggregate_context(cx, 1);
1330+
try {
1331+
result = finalize(state[p]);
1332+
} catch (error) {
1333+
delete state[p];
1334+
sqlite3_result_error(cx, error, -1);
1335+
return;
1336+
}
1337+
setFunctionResult(cx, result);
1338+
delete state[p];
1339+
}
1340+
1341+
if (Object.hasOwnProperty.call(this.functions, name)) {
1342+
removeFunction(this.functions[name]);
1343+
delete this.functions[name];
1344+
}
1345+
var finalize_name = name + "__finalize";
1346+
if (Object.hasOwnProperty.call(this.functions, finalize_name)) {
1347+
removeFunction(this.functions[finalize_name]);
1348+
delete this.functions[finalize_name];
1349+
}
1350+
// The signature of the wrapped function is :
1351+
// void wrapped(sqlite3_context *db, int argc, sqlite3_value **argv)
1352+
var step_ptr = addFunction(wrapped_step, "viii");
1353+
1354+
// The signature of the wrapped function is :
1355+
// void wrapped(sqlite3_context *db)
1356+
var finalize_ptr = addFunction(wrapped_finalize, "vi");
1357+
this.functions[name] = step_ptr;
1358+
this.functions[finalize_name] = finalize_ptr;
1359+
1360+
// passing null to the sixth parameter defines this as an aggregate
1361+
// function
1362+
//
1363+
// > An aggregate SQL function requires an implementation of xStep and
1364+
// > xFinal and NULL pointer must be passed for xFunc.
1365+
// - http://www.sqlite.org/c3ref/create_function.html
1366+
this.handleError(sqlite3_create_function_v2(
1367+
this.db,
1368+
name,
1369+
step.length - 1,
1370+
SQLITE_UTF8,
1371+
0,
1372+
0,
1373+
step_ptr,
1374+
finalize_ptr,
1375+
0
1376+
));
1377+
return this;
1378+
};
1379+
12321380
// export Database to Module
12331381
Module.Database = Database;
12341382
};

‎src/exported_functions.json

Copy file name to clipboardExpand all lines: src/exported_functions.json
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,5 +41,6 @@
4141
"_sqlite3_result_int",
4242
"_sqlite3_result_int64",
4343
"_sqlite3_result_error",
44+
"_sqlite3_aggregate_context",
4445
"_RegisterExtensionFunctions"
4546
]

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.