Skip to content

Latest commit

 

History

History
60 lines (43 loc) · 2.66 KB

DESIGN.md

File metadata and controls

60 lines (43 loc) · 2.66 KB

SqlNotebookDb Design

SqlNotebookDb is a Visual C++ project that compiles SQLite into a native Windows DLL (sqlite3.dll) with a custom configuration tailored for SQL Notebook.

What It Builds

A single source file (db.c) that #includes:

  1. The SQLite amalgamation (ext/sqlite/sqlite3.c - ~267,000 lines)
  2. SQLite miscellaneous extensions: UUID and Series modules
  3. A custom SxGetToken() function exported for the managed scripting engine's tokenizer

Output: sqlite3.dll for x64 and ARM64 platforms.

SQLite Compile-Time Configuration

Key defines in the .vcxproj:

Flag Effect
SQLITE_DQS=0 Disables double-quoted strings as string literals
SQLITE_THREADSAFE=0 Single-threaded mode (thread safety handled at managed layer)
SQLITE_ENABLE_FTS5 Full-text search engine
SQLITE_ENABLE_MATH_FUNCTIONS Built-in math functions
SQLITE_ENABLE_STAT4 Advanced query planner statistics
SQLITE_ENABLE_JSON1 JSON functions
SQLITE_ENABLE_PERCENTILE Percentile aggregate function
SQLITE_OMIT_DESERIALIZE Omit deserialization (not needed)
SQLITE_USE_SEH Windows Structured Exception Handling
SQLITE_WIN32_MALLOC Windows memory allocator

Companion Native DLLs

Three additional native DLLs are built from the sqlean library. Each is a separate .vcxproj under src/:

crypto.dll (src/crypto/)

Cryptographic hash and encoding functions:

  • Hash: SHA1, SHA256, SHA384, SHA512, MD5, BLAKE3
  • Encoding: Base32, Base64, Base85, Hex, URL encoding/decoding

fuzzy.dll (src/fuzzy/)

String distance and phonetic matching:

  • Distance: Damerau-Levenshtein, Hamming, Jaro-Winkler, Levenshtein, Optimal String Alignment, Edit Distance
  • Phonetic: Soundex, Refined Soundex, Caverphone
  • Utility: Transliteration, script code detection

stats.dll (src/stats/)

Statistical aggregate functions:

  • Standard deviation, variance (using Welford's algorithm for numerical stability)
  • Percentile calculations
  • Series generation

Build Process

The .vcxproj files for crypto, fuzzy, and stats are auto-generated by ps1/Update-Deps.ps1 from a template (src/sqlean.vcxproj.template). The script scans the sqlean source directory for .c files and populates the template.

All four native DLLs are built by scripts/build.sh before the managed projects, using MSBuild with the v143 (VS 2022) platform toolset.

Extension Loading

At runtime, Notebook.InitSqlite() in SqlNotebookScript loads the three extension DLLs using SQLite's sqlite3_load_extension() API. The extensions register their functions and become available to all SQL queries.