# SQLite Compatibility

This document describes the SQLite compatibility status of Limbo:

- [SQLite Compatibility](#sqlite-compatibility)
    - [Limitations](#limitations)
    - [SQL statements](#sql-statements)
        - [SELECT Expressions](#select-expressions)
    - [SQL functions](#sql-functions)
        - [Scalar functions](#scalar-functions)
        - [Aggregate functions](#aggregate-functions)
        - [Date and time functions](#date-and-time-functions)
        - [JSON functions](#json-functions)
    - [SQLite API](#sqlite-api)
    - [SQLite VDBE opcodes](#sqlite-vdbe-opcodes)

## Limitations

* Limbo does not support database access from multiple processes.

## SQL statements

| Statement                 | Status  | Comment |
| ------------------------- | ------- | ------- |
| ALTER TABLE               | No      |         |
| ANALYZE                   | No      |         |
| ATTACH DATABASE           | No      |         |
| BEGIN TRANSACTION         | No      |         |
| COMMIT TRANSACTION        | No      |         |
| CREATE INDEX              | No      |         |
| CREATE TABLE              | Partial |         |
| CREATE TRIGGER            | No      |         |
| CREATE VIEW               | No      |         |
| CREATE VIRTUAL TABLE      | No      |         |
| DELETE                    | No      |         |
| DETACH DATABASE           | No      |         |
| DROP INDEX                | No      |         |
| DROP TABLE                | No      |         |
| DROP TRIGGER              | No      |         |
| DROP VIEW                 | No      |         |
| END TRANSACTION           | No      |         |
| EXPLAIN                   | Yes     |         |
| INDEXED BY                | No      |         |
| INSERT                    | Partial |         |
| ON CONFLICT clause        | No      |         |
| PRAGMA                    | Partial |         |
| PRAGMA cache_size         | Yes     |         |
| REINDEX                   | No      |         |
| RELEASE SAVEPOINT         | No      |         |
| REPLACE                   | No      |         |
| RETURNING clause          | No      |         |
| ROLLBACK TRANSACTION      | No      |         |
| SAVEPOINT                 | No      |         |
| SELECT                    | Yes     |         |
| SELECT ... WHERE          | Yes     |         |
| SELECT ... WHERE ... LIKE | Yes     |         |
| SELECT ... LIMIT          | Yes     |         |
| SELECT ... ORDER BY       | Yes     |         |
| SELECT ... GROUP BY       | Yes     |         |
| SELECT ... HAVING         | Yes     |         |
| SELECT ... JOIN           | Yes     |         |
| SELECT ... CROSS JOIN     | Yes     | SQLite CROSS JOIN means "do not reorder joins". We don't support that yet anyway. |
| SELECT ... INNER JOIN     | Yes     |         |
| SELECT ... OUTER JOIN     | Partial | no RIGHT JOIN |
| SELECT ... JOIN USING     | Yes     |         |
| SELECT ... NATURAL JOIN   | Yes     |         |
| UPDATE                    | No      |         |
| UPSERT                    | No      |         |
| VACUUM                    | No      |         |
| WITH clause               | No      |         |

### SELECT Expressions

Feature support of [sqlite expr syntax](https://www.sqlite.org/lang_expr.html).

| Syntax                       | Status  | Comment |
|------------------------------|---------|---------|
| literals                     | Yes     |         |
| schema.table.column          | Partial | Schemas aren't supported |
| unary operator               | Yes     | |
| binary operator              | Partial | Only `%`, `!<`, and `!>` are unsupported |
| agg() FILTER (WHERE ...)     | No      | Is incorrectly ignored |
| ... OVER (...)               | No      | Is incorrectly ignored |
| (expr)                       | Yes     |         |
| CAST (expr AS type)          | Yes     |         |
| COLLATE                      | No      |         |
| (NOT) LIKE                   | No      |         |
| (NOT) GLOB                   | No      |         |
| (NOT) REGEXP                 | No      |         |
| (NOT) MATCH                  | No      |         |
| IS (NOT)                     | No      |         |
| IS (NOT) DISTINCT FROM       | No      |         |
| (NOT) BETWEEN ... AND ...    | No      |         |
| (NOT) IN (subquery)          | No      |         |
| (NOT) EXISTS (subquery)      | No      |         |
| CASE WHEN THEN ELSE END      | Yes     |         |
| RAISE                        | No      |         |

## SQL functions

### Scalar functions

| Function                     | Status | Comment |
|------------------------------|--------|---------|
| abs(X)                       | Yes    |         |
| changes()                    | No     |         |
| char(X1,X2,...,XN)           | Yes    |         |
| coalesce(X,Y,...)            | Yes    |         |
| concat(X,...)                | Yes    |         |
| concat_ws(SEP,X,...)         | Yes    |         |
| format(FORMAT,...)           | No     |         |
| glob(X,Y)                    | Yes    |         |
| hex(X)                       | Yes    |         |
| ifnull(X,Y)                  | Yes    |         |
| iif(X,Y,Z)                   | Yes    |         |
| instr(X,Y)                   | Yes    |         |
| last_insert_rowid()          | Yes    |         |
| length(X)                    | Yes    |         |
| like(X,Y)                    | Yes    |         |
| like(X,Y,Z)                  | Yes    |         |
| likelihood(X,Y)              | No     |         |
| likely(X)                    | No     |         |
| load_extension(X)            | No     |         |
| load_extension(X,Y)          | No     |         |
| lower(X)                     | Yes    |         |
| ltrim(X)                     | Yes    |         |
| ltrim(X,Y)                   | Yes    |         |
| max(X,Y,...)                 | Yes    |         |
| min(X,Y,...)                 | Yes    |         |
| nullif(X,Y)                  | Yes    |         |
| octet_length(X)              | Yes    |         |
| printf(FORMAT,...)           | No     |         |
| quote(X)                     | Yes    |         |
| random()                     | Yes    |         |
| randomblob(N)                | Yes    |         |
| replace(X,Y,Z)               | Yes    |         |
| round(X)                     | Yes    |         |
| round(X,Y)                   | Yes    |         |
| rtrim(X)                     | Yes    |         |
| rtrim(X,Y)                   | Yes    |         |
| sign(X)                      | Yes    |         |
| soundex(X)                   | Yes    |         |
| sqlite_compileoption_get(N)  | No     |         |
| sqlite_compileoption_used(X) | No     |         |
| sqlite_offset(X)             | No     |         |
| sqlite_source_id()           | No     |         |
| sqlite_version()             | Yes    |         |
| substr(X,Y,Z)                | Yes    |         |
| substr(X,Y)                  | Yes    |         |
| substring(X,Y,Z)             | Yes    |         |
| substring(X,Y)               | Yes    |         |
| total_changes()              | No     |         |
| trim(X)                      | Yes    |         |
| trim(X,Y)                    | Yes    |         |
| typeof(X)                    | Yes    |         |
| unhex(X)                     | Yes    |         |
| unhex(X,Y)                   | Yes    |         |
| unicode(X)                   | Yes    |         |
| unlikely(X)                  | No     |         |
| upper(X)                     | Yes    |         |
| zeroblob(N)                  | Yes    |         |





### Mathematical functions

| Function   | Status | Comment |
| ---------- | ------ | ------- |
| acos(X)    | Yes    |         |
| acosh(X)   | Yes    |         |
| asin(X)    | Yes    |         |
| asinh(X)   | Yes    |         |
| atan(X)    | Yes    |         |
| atan2(Y,X) | Yes    |         |
| atanh(X)   | Yes    |         |
| ceil(X)    | Yes    |         |
| ceiling(X) | Yes    |         |
| cos(X)     | Yes    |         |
| cosh(X)    | Yes    |         |
| degrees(X) | Yes    |         |
| exp(X)     | Yes    |         |
| floor(X)   | Yes    |         |
| ln(X)      | Yes    |         |
| log(B,X)   | Yes    |         |
| log(X)     | Yes    |         |
| log10(X)   | Yes    |         |
| log2(X)    | Yes    |         |
| mod(X,Y)   | Yes    |         |
| pi()       | Yes    |         |
| pow(X,Y)   | Yes    |         |
| power(X,Y) | Yes    |         |
| radians(X) | Yes    |         |
| sin(X)     | Yes    |         |
| sinh(X)    | Yes    |         |
| sqrt(X)    | Yes    |         |
| tan(X)     | Yes    |         |
| tanh(X)    | Yes    |         |
| trunc(X)   | Yes    |         |

### Aggregate functions

| Function                     | Status  | Comment |
|------------------------------|---------|---------|
| avg(X)                       | Yes     |         |
| count()                      | Yes     |         |
| count(*)                     | Yes     |         |
| group_concat(X)              | Yes     |         |
| group_concat(X,Y)            | Yes     |         |
| string_agg(X,Y)              | Yes     |         |
| max(X)                       | Yes     |         |
| min(X)                       | Yes     |         |
| sum(X)                       | Yes     |         |
| total(X)                     | Yes     |         |

### Date and time functions

| Function    | Status  | Comment                      |
|-------------|---------|------------------------------|
| date()      | Yes     | partially supports modifiers |
| time()      | Yes     | partially supports modifiers |
| datetime()  | Yes     | partially supports modifiers |
| julianday() | Partial | does not support modifiers   |
| unixepoch() | Partial | does not support modifiers   |
| strftime()  | No      |                              |
| timediff()  | No      |                              |

### Date and Time Modifiers
|  Modifier      | Status|  Comment                        |
|----------------|-------|---------------------------------|
| Days           | Yes 	 |                                 | 
| Hours          | Yes	 |                                 |
| Minutes        | Yes	 |                                 |
| Seconds        | Yes	 |                                 |
| Months         | Yes	 |                                 |
| Years          | Yes	 |                                 |
| TimeOffset     | Yes	 |                                 |
| DateOffset	 | Yes   |                                 |
| DateTimeOffset | Yes   |                                 |
| Ceiling	     | No    |                                 |
| Floor          | No    |                                 |
| StartOfMonth	 | Yes	 |                                 |
| StartOfYear	 | Yes	 |                                 |
| StartOfDay	 | Yes	 |                                 |
| Weekday(N)	 | Yes   |                                 |
| Auto           | No    |                                 |
| UnixEpoch      | No    |                                 |
| JulianDay      | No    |                                 |
| Localtime      |Partial| requires fixes to avoid double conversions.|
| Utc            |Partial| requires fixes to avoid double conversions.|
| Subsec         | Yes   |                                  |



### JSON functions

| Function                           | Status  | Comment                                                                                                                                      |
|------------------------------------|---------|----------------------------------------------------------------------------------------------------------------------------------------------|
| json(json)                         | Partial |                                                                                                                                              |
| jsonb(json)                        |         |                                                                                                                                              |
| json_array(value1,value2,...)      | Yes     |                                                                                                                                              |
| jsonb_array(value1,value2,...)     |         |                                                                                                                                              |
| json_array_length(json)            | Yes     |                                                                                                                                              |
| json_array_length(json,path)       | Yes     |                                                                                                                                              |
| json_error_position(json)          |         |                                                                                                                                              |
| json_extract(json,path,...)        | Partial | Does not fully support unicode literal syntax and does not allow numbers > 2^127 - 1 (which SQLite truncates to i32), does not support BLOBs |
| jsonb_extract(json,path,...)       |         |                                                                                                                                              |
| json -> path                       |         |                                                                                                                                              |
| json ->> path                      |         |                                                                                                                                              |
| json_insert(json,path,value,...)   |         |                                                                                                                                              |
| jsonb_insert(json,path,value,...)  |         |                                                                                                                                              |
| json_object(label1,value1,...)     |         |                                                                                                                                              |
| jsonb_object(label1,value1,...)    |         |                                                                                                                                              |
| json_patch(json1,json2)            |         |                                                                                                                                              |
| jsonb_patch(json1,json2)           |         |                                                                                                                                              |
| json_pretty(json)                  |         |                                                                                                                                              |
| json_remove(json,path,...)         |         |                                                                                                                                              |
| jsonb_remove(json,path,...)        |         |                                                                                                                                              |
| json_replace(json,path,value,...)  |         |                                                                                                                                              |
| jsonb_replace(json,path,value,...) |         |                                                                                                                                              |
| json_set(json,path,value,...)      |         |                                                                                                                                              |
| jsonb_set(json,path,value,...)     |         |                                                                                                                                              |
| json_type(json)                    | Yes     |                                                                                                                                              |
| json_type(json,path)               | Yes     |                                                                                                                                              |
| json_valid(json)                   |         |                                                                                                                                              |
| json_valid(json,flags)             |         |                                                                                                                                              |
| json_quote(value)                  |         |                                                                                                                                              |
| json_group_array(value)            |         |                                                                                                                                              |
| jsonb_group_array(value)           |         |                                                                                                                                              |
| json_group_object(label,value)     |         |                                                                                                                                              |
| jsonb_group_object(name,value)     |         |                                                                                                                                              |
| json_each(json)                    |         |                                                                                                                                              |
| json_each(json,path)               |         |                                                                                                                                              |
| json_tree(json)                    |         |                                                                                                                                              |
| json_tree(json,path)               |         |                                                                                                                                              |

## SQLite API

| Interface           | Status  | Comment |
|---------------------|---------|---------|
| sqlite3_open        | Partial |         |
| sqlite3_close       | Yes     |         |
| sqlite3_prepare     | Partial |         |
| sqlite3_finalize    | Yes     |         |
| sqlite3_step        | Yes     |         |
| sqlite3_column_text | Yes     |         |

## SQLite VDBE opcodes

| Opcode         | Status |
|----------------|--------|
| Add            | Yes    |
| AddImm         | No     |
| Affinity       | No     |
| AggFinal       | Yes    |
| AggStep        | Yes    |
| AggStep        | Yes    |
| And            | No     |
| AutoCommit     | No     |
| BitAnd         | Yes    |
| BitNot         | Yes    |
| BitOr          | Yes    |
| Blob           | Yes    |
| Checkpoint     | No     |
| Clear          | No     |
| Close          | No     |
| CollSeq        | No     |
| Column         | Yes    |
| Compare        | Yes    |
| Concat         | No     |
| Copy           | Yes    |
| Count          | No     |
| CreateIndex    | No     |
| CreateTable    | No     |
| DecrJumpZero   | Yes    |
| Delete         | No     |
| Destroy        | No     |
| Divide         | Yes    |
| DropIndex      | No     |
| DropTable      | No     |
| DropTrigger    | No     |
| EndCoroutine   | Yes    |
| Eq             | Yes    |
| Expire         | No     |
| Explain        | No     |
| FkCounter      | No     |
| FkIfZero       | No     |
| Found          | No     |
| Function       | Yes    |
| Ge             | Yes    |
| Gosub          | Yes    |
| Goto           | Yes    |
| Gt             | Yes    |
| Halt           | Yes    |
| HaltIfNull     | No     |
| IdxDelete      | No     |
| IdxGE          | Yes    |
| IdxInsert      | No     |
| IdxLT          | No     |
| IdxRowid       | No     |
| If             | Yes    |
| IfNeg          | No     |
| IfNot          | Yes    |
| IfPos          | Yes    |
| IfZero         | No     |
| IncrVacuum     | No     |
| Init           | Yes    |
| InitCoroutine  | Yes    |
| Insert         | No     |
| InsertAsync    | Yes    |
| InsertAwait    | Yes    |
| InsertInt      | No     |
| Int64          | No     |
| Integer        | Yes    |
| IntegrityCk    | No     |
| IsNull         | Yes    |
| IsUnique       | No     |
| JournalMode    | No     |
| Jump           | Yes    |
| Last           | No     |
| Le             | Yes    |
| LoadAnalysis   | No     |
| Lt             | Yes    |
| MakeRecord     | Yes    |
| MaxPgcnt       | No     |
| MemMax         | No     |
| Move           | No     |
| Multiply       | Yes    |
| MustBeInt      | Yes    |
| Ne             | Yes    |
| NewRowid       | Yes    |
| Next           | No     |
| NextAsync      | Yes    |
| NextAwait      | Yes    |
| Noop           | No     |
| Not            | No     |
| NotExists      | Yes    |
| NotFound       | No     |
| NotNull        | Yes    |
| Null           | Yes    |
| NullRow        | Yes    |
| Once           | No     |
| OpenAutoindex  | No     |
| OpenEphemeral  | No     |
| OpenPseudo     | Yes    |
| OpenRead       | Yes    |
| OpenReadAsync  | Yes    |
| OpenWrite      | No     |
| OpenWriteAsync | Yes    |
| OpenWriteAwait | Yes    |
| Or             | No     |
| Pagecount      | No     |
| Param          | No     |
| ParseSchema    | No     |
| Permutation    | No     |
| Prev           | No     |
| PrevAsync      | Yes    |
| PrevAwait      | Yes    |
| Program        | No     |
| ReadCookie     | No     |
| Real           | Yes    |
| RealAffinity   | Yes    |
| Remainder      | Yes    |
| ResetCount     | No     |
| ResultRow      | Yes    |
| Return         | Yes    |
| Rewind         | Yes    |
| RewindAsync    | Yes    |
| RewindAwait    | Yes    |
| RowData        | No     |
| RowId          | Yes    |
| RowKey         | No     |
| RowSetAdd      | No     |
| RowSetRead     | No     |
| RowSetTest     | No     |
| Rowid          | Yes    |
| SCopy          | No     |
| Savepoint      | No     |
| Seek           | No     |
| SeekGe         | Yes    |
| SeekGt         | Yes    |
| SeekLe         | No     |
| SeekLt         | No     |
| SeekRowid      | Yes    |
| Sequence       | No     |
| SetCookie      | No     |
| ShiftLeft      | No     |
| ShiftRight     | No     |
| SoftNull       | Yes    |
| Sort           | No     |
| SorterCompare  | No     |
| SorterData     | Yes    |
| SorterInsert   | Yes    |
| SorterNext     | Yes    |
| SorterOpen     | Yes    |
| SorterSort     | Yes    |
| String         | No     |
| String8        | Yes    |
| Subtract       | Yes    |
| TableLock      | No     |
| ToBlob         | No     |
| ToInt          | No     |
| ToNumeric      | No     |
| ToReal         | No     |
| ToText         | No     |
| Trace          | No     |
| Transaction    | Yes    |
| VBegin         | No     |
| VColumn        | No     |
| VCreate        | No     |
| VDestroy       | No     |
| VFilter        | No     |
| VNext          | No     |
| VOpen          | No     |
| VRename        | No     |
| VUpdate        | No     |
| Vacuum         | No     |
| Variable       | No     |
| VerifyCookie   | No     |
| Yield          | Yes    |

| LibSql Compatibility / Extensions |     |                                                               |
|-----------------------------------|-----|---------------------------------------------------------------|
| **UUID**                          |     | UUID's in limbo are `blobs` by default                        |
| uuid4()                           | Yes | uuid version 4                                                |
| uuid4_str()                       | Yes | uuid v4 string alias `gen_random_uuid()` for PG compatibility |
| uuid7(X?)                         | Yes | uuid version 7, Optional arg for seconds since epoch          |
| uuid7_timestamp_ms(X)             | Yes | Convert a uuid v7 to milliseconds since epoch                 |
| uuid_str(X)                       | Yes | Convert a valid uuid to string                                |
| uuid_blob(X)                      | Yes | Convert a valid uuid to blob                                  |
