| url | https://chatgpt.com/c/6a786079-f408-83eb-87b1-e0d35b2804a5 |
|---|
A working template for developing a C SQLite extension, integrating it directly into a customized SQLite amalgamation, and testing both its SQLite-facing behavior and its underlying C implementation with Pytest.
The repository uses a small alphabet extension as the primary worked example. The extension is deliberately simple enough that the surrounding project structure, build integration, API design, and testing strategy remain visible.
The project combines three concerns that are often treated separately:
- SQLite extension development in ordinary C.
- Integration into an extended SQLite build rather than relying only on runtime loadable extensions.
- Direct testing of C implementation APIs from Python using CFFI and Pytest.
The result is intended as a reusable starting point for small or medium SQLite extensions whose implementation contains C logic worth testing independently of the SQL interface.
Important
AI-Assisted Development Disclosure
This project has been developed with extensive generative-AI assistance. Assistance covered project exploration, design discussion, specification development, implementation, testing, technical review, and documentation. See AI_DISCLOSURE.md for further details. Responsibility for the published software remains with the maintainer.
The central goal is to provide a practical project pattern for a SQLite extension that can be:
- developed as normal C source;
- integrated directly into SQLite;
- included in a generated SQLite amalgamation;
- automatically initialized as part of the resulting SQLite build;
- exercised through its public SQL interface;
- exposed selectively in test builds for direct C-level testing;
- called from Python through a generated CFFI API-mode wrapper;
- tested comprehensively using ordinary Pytest tests.
The project deliberately avoids treating the SQL interface as the only testable surface.
For nontrivial extensions, substantial logic may live below the sqlite3_create_function(), virtual-table, collation, or other SQLite registration layer. Testing all of that logic indirectly through SQL can make tests unnecessarily coarse and can obscure defects at the C API boundary.
This template therefore uses two complementary testing layers:
Pytest
|
+-----------+-----------+
| |
v v
sqlite3 CFFI API
| |
v v
SQLite SQL API direct C test API
| |
+-----------+-----------+
|
alphabet.c
SQL tests verify the extension as SQLite sees it.
CFFI tests verify selected implementation routines directly.
The repository's primary example extension is alphabet.
It provides the SQLite function:
alpha_string(language [, start [, length]])The function returns part or all of a predefined alphabet.
Supported language identifiers include English/Latin and Russian/Cyrillic names.
Examples:
SELECT alpha_string('en');
SELECT alpha_string('English', 3);
SELECT alpha_string('ru', -5);
SELECT alpha_string('Russian', 2, 4);The extension demonstrates several concerns that are useful beyond the specific example:
- SQLite scalar-function registration;
- UTF-8 text handling in C;
- Unicode code-point indexing;
- optional SQL arguments;
- negative indexing;
- argument validation;
- SQLite error reporting;
- static extension data;
- helper routines that are useful to test independently of SQLite.
The implementation includes lower-level routines such as UTF-8 byte counting, code-point length calculation, byte-offset calculation, and alphabet selection. These provide a useful boundary between SQLite adapter code and independently testable C logic.
The alphabet extension uses one internal registration routine and two thin entry points (defined at the very end of the main source module "alphabet.c") so that the same implementation can be built either:
- directly into SQLite and registered as a built-in/auto-extension; or
- as a conventional loadable SQLite extension.
The key design is that all SQL-visible feature registration lives in one internal function:
/*
** Register the extension's SQL functions.
**
** Three fixed arities are registered so SQLite itself rejects calls with
** zero arguments or more than three arguments.
*/
static int alphabetInit(sqlite3 *db) {
static const int flags = SQLITE_UTF8 | SQLITE_DETERMINISTIC | SQLITE_INNOCUOUS;
int rc = SQLITE_OK;
if (rc == SQLITE_OK) {
rc = sqlite3_create_function(
db, "alpha_string", 1, flags, 0,
alphabetStringFunc, 0, 0
);
}
if (rc == SQLITE_OK) {
rc = sqlite3_create_function(
db, "alpha_string", 2, flags, 0,
alphabetStringFunc, 0, 0
);
}
if (rc == SQLITE_OK) {
rc = sqlite3_create_function(
db, "alpha_string", 3, flags, 0,
alphabetStringFunc, 0, 0
);
}
return rc;
}alphabetInit() is the extension's actual SQL registration routine. It does not care how the extension entered SQLite. Its only responsibility is to register the SQL functionality exposed by the module.
For alphabet, that means registering:
alpha_string(language)
alpha_string(language, start)
alpha_string(language, start, length)as three fixed arities of the same SQL function.
Using three separate registrations rather than a variadic arity such as -1 lets SQLite itself reject calls with unsupported argument counts before alphabetStringFunc() is invoked.
The function also centralizes the SQLite function flags:
SQLITE_UTF8 | SQLITE_DETERMINISTIC | SQLITE_INNOCUOUSThese declare that alpha_string:
- uses UTF-8 text encoding;
- is deterministic for a given argument set;
- is considered innocuous and does not perform privileged or externally visible operations.
Registration is performed sequentially and stops at the first error. The first non-SQLITE_OK return code from sqlite3_create_function() is propagated to the caller.
When the source is compiled as part of SQLite itself, SQLITE_CORE is defined:
#ifdef SQLITE_CORE
int sqlite3AlphabetInit(sqlite3 *db) {
return alphabetInit(db);
}The core-facing entry point is intentionally minimal.
sqlite3AlphabetInit(sqlite3 *db)receives an already initialized SQLite database connection and delegates directly to alphabetInit().
This form is suitable for registration through SQLite's built-in auto-extension machinery. In this project, the build pipeline integrates the source through EXTRA_SRC and arranges for the initializer to participate in the generated built-in extension registry.
Conceptually:
SQLite opens database connection
|
v
built-in auto-extension registry
|
v
sqlite3AlphabetInit(db)
|
v
alphabetInit(db)
|
v
sqlite3_create_function(...)
No loadable-extension API table is required in this mode because the extension is compiled inside the SQLite core and can call the SQLite API directly.
When SQLITE_CORE is not defined, the same source instead exposes the conventional SQLite loadable-extension initializer:
#else
# if defined(_WIN32)
__declspec(dllexport)
# endif
int sqlite3_alphabet_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
) {
SQLITE_EXTENSION_INIT2(pApi);
(void)pzErrMsg; /* Unused parameter */
return alphabetInit(db);
}
#endif /* SQLITE_CORE */The exported entry point follows SQLite's loadable-extension ABI:
int sqlite3_alphabet_init(
sqlite3 *db,
char **pzErrMsg,
const sqlite3_api_routines *pApi
);Here:
dbis the target SQLite connection;pzErrMsgis available for an optional initialization error message;pApipoints to SQLite's runtime API dispatch table.
The call:
SQLITE_EXTENSION_INIT2(pApi);initializes the extension-side SQLite API indirection required by sqlite3ext.h.
After that setup, the loadable-extension entry point delegates to the same:
alphabetInit(db)routine used by the built-in form.
pzErrMsg is not needed by this extension, so it is explicitly marked unused:
(void)pzErrMsg;On Windows, the initializer is exported with:
__declspec(dllexport)so SQLite can locate it in the loadable extension DLL.
The corresponding flow is:
sqlite3_load_extension()
|
v
sqlite3_alphabet_init(db, pzErrMsg, pApi)
|
+--> SQLITE_EXTENSION_INIT2(pApi)
|
v
alphabetInit(db)
|
v
sqlite3_create_function(...)
The important part of the pattern is therefore:
alphabetInit(db)
|
+-------------+-------------+
| |
v v
sqlite3AlphabetInit() sqlite3_alphabet_init()
SQLITE_CORE loadable build
| |
v v
built into SQLite loaded at runtime
The integration-specific entry points contain only the code required by their respective SQLite build models.
The SQL feature registration itself is defined once.
This separation avoids duplicating sqlite3_create_function() calls or allowing the built-in and loadable variants to drift apart.
It also keeps the module easy to reason about:
alphabetInit()defines what the extension exposes to SQL;sqlite3AlphabetInit()defines how an embedded SQLite build enters the extension;sqlite3_alphabet_init()defines how SQLite's loadable-extension mechanism enters the extension.
This is the core SQLite interface pattern used by the template for supporting both integrated and standalone extension builds from the same C source.
A SQLite extension normally contains at least two conceptual layers:
SQLite interface layer
|
| sqlite3_value_*
| sqlite3_result_*
| sqlite3_create_function()
| extension initialization
|
v
implementation logic
The SQLite interface layer necessarily depends on SQLite's runtime API.
The underlying implementation often does not.
This project keeps useful implementation routines sufficiently separated so they can be tested directly.
The objective is not to redesign production code around Python or CFFI. The objective is to expose selected existing C interfaces in a controlled test build.
Production and test linkage can therefore differ without changing the implementation itself.
Conceptually:
#ifdef ALPHABET_TEST
## define AB_TEST_API /* exported test interface */
#else
## define AB_TEST_API static
#endifA helper can remain internal in an ordinary build while becoming externally visible in a test build.
This avoids permanently expanding the production API merely for testability.
The example extension uses a small header architecture designed to serve both the C compiler and CFFI.
Conceptually:
alphabet.c
implementation
alphabet.h
C-facing wrapper header
linkage/visibility definitions
ordinary C includes
alphabet_api.h
declaration catalogue
CFFI-compatible API declarations
alphabet_api.h acts as the declaration catalogue for the interfaces that may be tested directly.
The same declarations are ultimately consumed by:
C compiler
^
|
alphabet.h
|
alphabet_api.h
|
v
CFFI cdef preparation
This avoids maintaining an unrelated handwritten Python-side copy of every prototype.
The declaration catalogue is intentionally constrained to syntax that can be transformed safely into CFFI cdef() input.
It is not intended to become a general-purpose C parser or binding-description language.
The project uses CFFI API mode for direct C testing.
CFFI provides a useful middle ground between:
- manually implementing a Python extension;
- manually reproducing every C ABI detail with
ctypes; - introducing a large general-purpose binding generator.
The test wrapper is compiled against the real C declarations.
Conceptually:
alphabet_api.h
|
| transformed declarations
v
FFI.cdef()
|
| generated wrapper
v
_alphabet_wrapper
|
| linked against test SQLite
v
sqlite3.dll
|
v
exported alphabet test API
Python tests then import the generated wrapper:
from _alphabet_wrapper import ffi, liband call C functions through lib.
This keeps Pytest as the test runner while allowing tests to exercise native C interfaces directly.
Direct C testing requires selected implementation symbols to be externally visible.
The template therefore distinguishes between:
- production linkage, where implementation helpers may remain
static; - test linkage, where selected routines are exported from the test SQLite library.
For alphabet, this is controlled by test-build macros such as:
ALPHABET_TEST
ALPHABET_BUILD_LIB
The same pattern is demonstrated more extensively by the CTD reference module included in the project.
The principle is:
Test visibility is a build property, not a requirement that implementation helpers become permanent public production APIs.
Functions that exist in production can simply change linkage in the test build.
Interfaces that exist only for testing may additionally be enclosed in test-only conditional compilation.
The extension is not tested only as a separately loaded DLL.
The project builds a customized SQLite distribution and integrates additional C sources into SQLite's source-generation workflow.
At a high level:
SQLite source tree
+
project extension sources
+
selected SQLite ext/misc sources
|
v
source preparation
|
v
EXTRA_SRC
|
v
SQLite Makefile.msc
|
v
generated / extended amalgamation
|
v
sqlite3.dll
libsqlite3.lib
sqlite3.lib
sqlite3.exe
The project uses SQLite's existing amalgamation/build hooks rather than maintaining local patches to core SQLite build files.
Important mechanisms include:
EXTRA_SRC
SQLITE_EXTRA_AUTOEXT
Selected ordinary extensions are prepared for built-in integration and automatic registration.
The alphabet extension follows the same general integration pipeline.
The complete Windows/MSVC build design, including:
- SQLite source acquisition;
- ZLIB and ICU integration;
- FP16 staging;
EXTRA_SRC;SQLITE_EXTRA_AUTOEXT;- stock
ext/miscpreparation; - amalgamation generation;
- export generation;
- static and import libraries;
- x86/x64 handling;
- caching and incremental builds;
is documented separately in:
Field Notes — SQLite MSVC Build / Amalgamation Integration
That document should be treated as the detailed build-system reference. This README concentrates on how the build system supports the extension-development and testing workflow.
SQLite extensions are commonly developed as independent loadable libraries. That model remains useful, but direct integration provides a different set of properties.
For this template, integration allows the project to exercise:
- built-in extension registration;
- amalgamation generation with project sources;
- test-only symbol exposure from the resulting SQLite library;
- direct linkage of CFFI wrappers against the same native library used by SQLite;
- a build that can be deployed without separately loading the extension at runtime.
It also makes the test build an accurate representation of an embedded extension rather than merely a dynamically loaded plugin.
The project does not imply that amalgamation integration is preferable for every SQLite extension. It demonstrates the pattern for projects where embedding is desirable.
The SQL-facing API is tested using Python's sqlite3 module and Pytest.
These tests treat the extension as a SQLite feature and verify behavior such as:
- extension availability;
- accepted language names;
- full alphabet generation;
- start offsets;
- negative offsets;
- requested lengths;
- UTF-8 results;
- boundary conditions;
- invalid language identifiers;
- invalid argument types;
- invalid ranges;
- SQLite error behavior.
These are integration tests across:
Python sqlite3
->
SQLite
->
extension registration
->
alphabet SQL adapter
->
implementation
They answer the question:
Does the extension behave correctly through SQLite?
A second Pytest layer imports _alphabet_wrapper and tests selected C routines directly through CFFI.
Typical targets include:
int ab_utf8_byte_count(const char *zText);
int64_t ab_utf8_length(const char *zText);
int ab_utf8_byte_offset(const char *zText, int64_t i);
const char *ab_alphabet_select(const char *zLanguage);These tests can verify implementation behavior without passing through:
sqlite3_value
sqlite3_context
SQL parsing
SQLite type conversion
SQLite result handling
They answer a different question:
Does the underlying C implementation obey its own API contract?
Both layers are required for comprehensive testing.
A passing C test does not prove correct SQLite integration.
A passing SQL test does not necessarily isolate defects in the underlying C routines.
The direct-testing strategy follows the broader design developed in:
That project explores systematic testing of C APIs through CFFI, including:
- scalar values;
- enums and constants;
- global data;
- scalar pointers;
- arrays;
- byte buffers;
- strings;
- structures;
- callbacks;
- owned and borrowed memory;
- opaque handles;
- failure contracts;
- capacity/query protocols;
- test-only linkage.
This SQLite extension template applies that design to a real embedded-library context rather than attempting to reproduce the entire reference catalogue.
The alphabet API needs only a small subset of those patterns, but follows the same general rules.
Direct CFFI tests should be derived from the actual C API contract, not from superficial prototype shapes.
For every tested API, determine:
- parameter and return types;
- valid input ranges;
- NULL rules;
- pointer direction;
- pointer shape;
- string encoding;
- length/count units;
- ownership;
- lifetime;
- mutations and side effects;
- failure behavior.
A pointer declaration alone is insufficient.
For example:
const char *zTextdoes not by itself tell a test author whether the pointer is:
- nullable;
- NUL-terminated;
- UTF-8;
- borrowed;
- retained;
- copied;
- valid only for the duration of the call.
Those properties are part of the API contract.
The direct test suite should establish them from declarations, comments, and implementation before constructing test cases.
The practical testing model is intentionally conservative.
Memory created using:
ffi.new(...)belongs to Python/CFFI.
It must not be passed to a C deallocator.
The owning CData object must remain alive while C accesses it.
Memory exposed with:
ffi.from_buffer(...)remains owned by the Python object providing the buffer.
Pointers returned to static or otherwise library-owned storage are borrowed.
Python tests must not free them.
Where independent Python lifetime is useful, tests should copy the contents.
For strings:
ffi.string(ptr)For arrays:
ffi.unpack(ptr, count)If a future extension API returns allocated memory, the corresponding ownership and release routine must be part of the explicit API contract.
Python and C allocators must never be mixed.
There is no custom native test runner.
Pytest remains responsible for:
- fixture lifecycle;
- test discovery;
- parameterization;
- assertions;
- failure reporting;
- CFFI wrapper setup;
- SQL connection setup.
The native library is simply another implementation surface consumed by the tests.
This is an important project constraint:
Pytest
is the test runner.
CFFI
provides the C boundary.
SQLite
provides the SQL boundary.
A practical suite should keep SQL-facing and direct-C tests visibly separate.
Conceptually:
tests/
├─ conftest.py
│
├─ test_alphabet_sql.py
│
├─ test_alphabet_utf8.py
├─ test_alphabet_selection.py
└─ ...
The exact decomposition can evolve with the extension, but tests should be grouped according to the interface contract they verify rather than placed in one monolithic module.
Tests should be derived from the implementation contract.
For every target:
- inspect its declaration;
- inspect its implementation;
- determine its valid domain;
- determine its boundary conditions;
- determine its failure behavior;
- determine ownership and mutation semantics where relevant;
- only then design test cases.
Do not infer behavior merely from a function name.
Do not copy a superficially similar test pattern without checking whether the target has the same pointer, lifetime, size, or failure contract.
Parameterized tests should use descriptive IDs that communicate the behavioral case being exercised.
For example:
@pytest.mark.parametrize(
("text", "expected"),
[
pytest.param(b"", 0, id="empty"),
pytest.param(b"A", 1, id="ascii-single"),
pytest.param("Я".encode(), 2, id="utf8-two-byte"),
],
)is preferable to anonymous parameter sets whose meaning is visible only from the raw values.
The repository is intentionally not a generic extension generator or binding framework.
alphabet is a concrete implementation used to establish repeatable project patterns.
A new extension can replace or extend it while retaining the same broad architecture:
src/
myextension.c
myextension.h
myextension_api.h
build integration
->
extended SQLite
test build macros
->
selected C symbols exported
CFFI builder
->
_myextension_wrapper
tests/
->
SQL tests
+
direct C tests
The project favors a small number of explicit conventions over machinery intended to handle arbitrary C APIs automatically.
The testing architecture is aimed primarily at deterministic, synchronous C code such as:
- algorithms;
- parsers;
- encoding/decoding routines;
- data transformations;
- numeric routines;
- string handling;
- structured data manipulation;
- SQLite extension implementation logic.
It is not intended to provide universal automation for every possible C interface.
In particular, specialized system programming, hardware interfaces, unusual process control, arbitrary asynchronous callbacks, platform-specific kernel facilities, and highly dynamic ownership protocols may require project-specific testing approaches.
The objective is broad practical coverage, not pretend generality.
Generated and downloaded build state is kept under:
out/
The build system currently distinguishes normal and test build trees:
out/
├─ cache/
├─ sqlite/
├─ build/
├─ build_test/
├─ bin/
├─ include/
└─ lib/
├─ import/
└─ static/
Important outputs include:
out\bin\sqlite3.dll
out\bin\sqlite3.exe
out\include\sqlite3.h
out\include\sqlite3ext.h
out\include\alphabet.h
out\include\alphabet_api.h
out\lib\import\sqlite3.def
out\lib\import\sqlite3.lib
out\lib\static\libsqlite3.lib
A test build uses out\build_test and enables the extension's test API exposure.
See the dedicated build Field Note for the complete directory and build-stage description.
The primary native build is Windows/MSVC.
Run the build from an initialized Visual C++ developer command prompt matching the required architecture.
Typical invocation:
build_sqlite_msvc.batThe build configuration is controlled by environment switches including:
USE_TEST
USE_ICU
USE_ZLIB
USE_FP16
SQLITE_EXTRA
USE_EXTRAS
For the CFFI-focused test build, the important properties are:
USE_TEST=1
USE_EXTRAS=1
with project-specific test symbols exported into the resulting SQLite DLL.
The currently recommended SQLite test configuration also disables integrations that conflict with SQLite's own test build:
set USE_TEST=1
set USE_ICU=0
set SQLITE_EXTRA=0
set USE_EXTRAS=1
build_sqlite_msvc.batThe precise build machinery is intentionally not duplicated here. See:
A practical extension-development cycle is:
Work primarily under:
src/
Keep SQLite-facing adapter code and reusable implementation logic reasonably distinguishable.
Add or maintain the relevant declarations in the extension API catalogue.
Use test linkage macros for implementation routines that should remain internal in production.
Build SQLite with project extras and test API exposure enabled.
Generate/compile _alphabet_wrapper against the produced native library and the same declaration catalogue used by the C compiler.
Run both:
- SQL-level tests;
- direct CFFI tests.
A failure in a direct C test generally points toward the C implementation or its API contract.
A failure that occurs only through SQL generally points toward the SQLite adapter, registration, argument conversion, or result/error handling.
This separation is one of the principal benefits of the architecture.
The important source-level components are conceptually:
.
├─ README.md
├─ pyproject.toml
├─ build_sqlite_msvc.bat
│
├─ src/
│ ├─ alphabet.c
│ ├─ alphabet.h
│ ├─ alphabet_api.h
│ │
│ ├─ ctd.c
│ ├─ ctd.h
│ └─ ctd_api.h
│
├─ tool/
│ ├─ patch_sqlite_misc_autoext.tcl
│ └─ bundle_extra_src.tcl
│
├─ tests/
│ ├─ conftest.py
│ └─ test_*.py
│
└─ out/
└─ generated build state
ctd is a broader C interface fixture/reference used to develop and validate CFFI testing patterns.
alphabet is the actual SQLite extension template demonstrating application of those patterns.
This repository sits at the intersection of two related pieces of work.
Field Notes — SQLite MSVC Build
Covers the build side in depth:
- integrating ordinary extensions into the amalgamation;
EXTRA_SRC;SQLITE_EXTRA_AUTOEXT;- automatic extension registration;
- source patching;
- dependency bundling;
- Windows/MSVC build mechanics.
Covers the C/Python testing boundary in depth:
- CFFI API mode;
- test-only symbol exposure;
- linkage modes;
- C declaration catalogues;
- scalar/pointer/array/buffer/string interfaces;
- structures and callbacks;
- ownership and lifetime;
- direct Pytest testing patterns.
This project combines those two ideas in the context of an actual SQLite extension.
The useful result is not the alphabet function itself.
The repository demonstrates a complete path:
C extension implementation
|
v
test-aware C declaration/API design
|
v
SQLite EXTRA_SRC integration
|
v
extended SQLite amalgamation
|
v
MSVC test DLL with selected exported internals
|
+-----------------------+
| |
v v
SQLite SQL API CFFI API wrapper
| |
+-----------+-----------+
|
v
Pytest
That path makes it possible to develop an embedded SQLite extension while retaining ordinary, focused unit tests for the C implementation underneath it.
This repository should be regarded as a development template and reference implementation, not as a general C binding framework or an official SQLite build mechanism.
Its conventions are deliberately optimized for:
- small C extensions;
- explicit APIs;
- deterministic behavior;
- maintainable Pytest suites;
- direct visibility into the Python/C boundary;
- minimal permanent intrusion into production linkage;
- reproducible extension-development structure.
The project is expected to evolve as additional C interface patterns and SQLite extension designs are exercised.