Steps
- Pick a
SQL functionsin COMPAT.md file with a No (not implemented yet) status. - Create an issue for that function.
- Implement the function in a feature branch.
- Push it as a Merge Request, get it review.
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.
sql function: string
--Parser-->
enum Func
--translate-->
Instruction
--VDBE-->
Result
TODO for implementing the function:
- analysis
- read and try out how the function works in SQLite.
- compare
explainoutput of SQLite and Limbo.
- add/ update the function definition in
functions.rs. - add/ update how to function is translated from
definitiontoinstructionin virtual machine layer VDBE. - add/ update the function Rust execution code and tests in vdbe layer.
- add/ update how the bytecode
Programexecutes when steps into the function. - add/ update TCL tests for this function in limbo/testing.
- update doc for function compatibility.
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 0Comparing 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 dateWe 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.
- we only need to pay attention to opcode
Functionat addr 2. The rest is already set up in limbo. - we have up to 5 registers p1 to p5 for each opcode.
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
- add to ScalarFunc as
dateis a scalar function.
// file core/functions.rs
pub enum ScalarFunc {
// other funcs...
Soundex,
+ Date,
Time,
// other funcs...
}- add to Display to show the function as string in our program.
// 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(),
// ...
}- add to
fn resolve_function(..)ofimpl Functo 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?
datefunction can have zero to many arguments.- in case there are arguments, we loop through the args and allocate a register
let target_reg = program.alloc_register();for each argument expression. - then we emit the bytecode instruction for Function
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: &[OwnedValue]) -> OwnedValue {
+ // ... 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
- tests for Rust code
- TCL tests for executing the sql function
One test for the Rust code is shown as example below https://github.com/tursodatabase/limbo/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.
- https://github.com/sqlite/sqlite/blob/f2b21a5f57e1a1db1a286c42af40563077635c3d/test/date3.test#L36
- https://github.com/sqlite/sqlite/blob/f2b21a5f57e1a1db1a286c42af40563077635c3d/test/date.test#L611C1-L652C73
Update the COMPAT.md file to mark this function as implemented. Change Status to
Yesif it is fully supported,Partialif supported but not fully yet compared to SQLite.
An example:
// file COMPAT.md
| Function | Status | Comment |
|------------------------------|---------|------------------------------|
- | date() | No | |
+ | date() | Yes | partially supports modifiers |
...