docs/contributing/contributing_functions.md
Steps
SQL functions in COMPAT.md file with a No (not implemented yet) status.Sample Pull Requests of function contributing
date(..)Note that the files, code location, steps might be not exactly the same because of refactor but the idea of the changes needed in each layer stays.
Issue #158 was created for it. Refer to commit 4ff7058.
To add a function we generally need to touch at least the following modules
SQL Command Processor module is responsible for turning sql function string into a sequence of instructions to be executed by the Virtual Machine module.bytecode generator in core/translate generates bytecode program for this function to be executed.core/vdbe
vdbe should execute the logic of this function in Rust and write result to destination register of the vm.SQL function string
--Tokenizer and Parser-->
AST (enum Func)
--Bytecode Generator (core/translate)-->
Bytecode Instructions
--Virtual Machine-->
Result
TODO for implementing the function:
explain output of SQLite and Limbo.functions.rs.definition to instruction in virtual machine layer VDBE.Program executes when steps into the function.How date works in SQLite?
> sqlite3
sqlite> explain select date('now');
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 6 0 0 Start at 6
1 Once 0 3 0 0
2 Function 0 0 2 date(-1) 0 r[2]=func()
3 Copy 2 1 0 0 r[1]=r[2]
4 ResultRow 1 1 0 0 output=r[1]
5 Halt 0 0 0 0
6 Goto 0 1 0 0
Comparing that with Limbo:
# created a sqlite database file database.db
# or cargo run to use the memory mode if it is already available.
> cargo run database.db
Enter ".help" for usage hints.
limbo> explain select date('now');
Parse error: unknown function date
We can see that the function is not implemented yet so the Parser did not understand it and throw an error Parse error: unknown function date.
Function at addr 2. The rest is already set up in limbo.For limbo to understand the meaning of date, we need to define it as a Function somewhere.
That place can be found currently in core/functions.rs. We need to edit 3 places
date is a scalar function.// file core/functions.rs
pub enum ScalarFunc {
// other funcs...
Soundex,
+ Date,
Time,
// other funcs...
}
// file core/functions.rs
impl Display for ScalarFunc {
fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
let str = match self {
// ...
ScalarFunc::Soundex => "soundex".to_string(),
+ ScalarFunc::Date => "date".to_string(),
ScalarFunc::Time => "time".to_string(),
// ...
}
fn resolve_function(..) of impl Func to enable parsing from str to this function.// file core/functions.rs
impl Func {
pub fn resolve_function(name: &str, arg_count: usize) -> Result<Func, ()> {
match name {
// ...
+ "date" => Ok(Func::Scalar(ScalarFunc::Date)),
// ...
}
How to translate the function into bytecode Instruction?
date function can have zero to many arguments.let target_reg = program.alloc_register();
for each argument expression.program.emit_insn(Insn::Function {...})// file core/translate/expr.rs
pub fn translate_expr(...) -> Result<usize> {
// ...
match expr {
// ..
ast::Expr::FunctionCall {
// ...
match &func_ctx.func {
// ...
Func::Scalar(srf) => {
// ...
+ ScalarFunc::Date => {
+ if let Some(args) = args {
+ for arg in args.iter() {
+ // register containing result of each argument expression
+ let target_reg = program.alloc_register();
+ _ = translate_expr(
+ program,
+ referenced_tables,
+ arg,
+ target_reg,
+ precomputed_exprs_to_registers,
+ )?;
+ }
+ }
+ program.emit_insn(Insn::Function {
+ constant_mask: 0,
+ start_reg: target_register + 1,
+ dest: target_register,
+ func: func_ctx,
+ });
+ Ok(target_register)
+ }
// ...
The function execution code is implemented in vdbe/datetime.rs file here as we already implemented the datetime features in this file.
Note that for other functions it might be implemented in other location in vdbe module.
// file vdbe/datetime.rs
// ...
+ pub fn exec_date(values: &[Value]) -> Value {
+ // ... implementation
+ }
// ...
Next step is to implement how the virtual machine (VDBE layer) executes the bytecode Program when the program step into the function instruction Insn::Function date ScalarFunc::Date.
Per SQLite spec if there is no time value (no start register) , we want to execute the function with default param 'now'.
In all functions other than timediff(), the time-value (and all modifiers) may be omitted, in which case a time value of 'now' is assumed.
// file vdbe/mod.rs
impl Program {
pub fn step<'a>(...) {
loop {
// ...
match isin {
// ...
Insn::Function {
// ...
+ ScalarFunc::Date => {
+ let result =
+ exec_date(&state.registers[*start_reg..*start_reg + arg_count]);
+ state.registers[*dest] = result;
+ }
// ...
There are 2 kind of tests we need to add
One test for the Rust code is shown as example below https://github.com/tursodatabase/turso/blob/69e3dd28f77e59927da4313e517b2b428ede480d/core/vdbe/datetime.rs#L620C1-L661C1
TCL tests for date functions can be referenced from SQLite source code which is already very comprehensive.
Update the COMPAT.md file to mark this function as implemented. Change Status to
Yes if it is fully supported,Partial if supported but not fully yet compared to SQLite.An example:
// file COMPAT.md
| Function | Status | Comment |
|------------------------------|---------|------------------------------|
- | date() | No | |
+ | date() | Yes | partially supports modifiers |
...