Issue Overview: SQLITE_OMIT_xxx Flags Not Working as Expected After Upgrading to SQLite 3.36

The core problem revolves around the perceived failure of SQLITE_OMIT_xxx compile-time options when upgrading an Android application’s SQLite dependency from version 3.6 to 3.36. The user aims to reduce binary size by omitting unused features (e.g., triggers, views, virtual tables). Initial testing suggested that many SQLITE_OMIT_xxx flags no longer function, but further investigation revealed discrepancies between builds targeting the standard SQLite shell (sqlite3) and those incorporating the SQLite Encryption Extension (SEE). Key observations include:

Selective Failure of OMIT Flags: Flags such as SQLITE_OMIT_ATTACH and SQLITE_OMIT_ALTERTABLE failed to disable corresponding features in some build configurations.
Build Artifact Contamination: Residual files from prior builds (e.g., parse.c) caused features to remain enabled despite OMIT flags.
Compiler Flag Misconfiguration: Debug symbols (-g) and optimization levels (-O2) contributed to unexpectedly large binaries, overshadowing the impact of OMIT flags.
Extension Compatibility Issues: The SEE extension introduced dependencies that conflicted with OMIT flags, rendering some ineffective.

This issue is critical for Android development, where binary size constraints demand aggressive optimization. The problem is exacerbated by incomplete documentation on OMIT flag behavior in newer SQLite versions and SEE integration nuances.

Possible Causes: Build Process Flaws, Extension Conflicts, and Compiler Misconfigurations

1. Incomplete Build Cleanup

SQLite’s build system generates intermediate files (e.g., parse.c from parse.y) during compilation. If these files are not regenerated after modifying OMIT flags, features intended for omission may persist. For example:

The parse.c file contains SQL syntax definitions derived from the parse.y grammar. If a prior build included CREATE VIEW support, a subsequent build with SQLITE_OMIT_VIEW but without make clean will retain view-related syntax in parse.c.
This issue is especially prevalent in projects using custom build scripts that skip cleanup steps.

2. SEE Extension Dependencies

The SQLite Encryption Extension (SEE) adds cryptographic features but may implicitly enable SQLite components that conflict with OMIT flags. For instance:

SEE relies on the sqlite3_key and sqlite3_rekey functions, which require the Virtual File System (VFS) layer. Disabling SQLITE_OMIT_VIRTUALTABLE might be necessary for SEE to function, even if the OMIT flag is set.
Extensions like SEE often modify SQLite’s preprocessing logic, bypassing OMIT flags that would otherwise disable features.

3. Compiler Flag Inefficiencies

Debug symbols (-g) and suboptimal optimization flags (-O2 instead of -Os) inflate binary size independently of OMIT flags:

The -g flag embeds debugging information (e.g., symbol tables), increasing file size without affecting code functionality.
-O2 optimizes for speed, often expanding code size via loop unrolling and inlining, whereas -Os prioritizes size reduction.

4. Misunderstanding OMIT Flag Scope

Not all OMIT flags are compatible with every SQLite configuration:

Flags like SQLITE_OMIT_ALTERTABLE may conflict with schema migration tools or ORM libraries that assume ALTER TABLE support.
Some flags (e.g., SQLITE_OMIT_ATTACH) disable features required by Android’s SQLite integration layer, leading to runtime errors.

Troubleshooting Steps, Solutions & Fixes: Validating OMIT Flags, Optimizing Builds, and Resolving Conflicts

Step 1: Validate OMIT Flags in Isolation

A. Test Without SEE or Extensions

Build SQLite Shell Only:

make clean  
make sqlite3 OPTS="-DSQLITE_OMIT_VIEW -DSQLITE_OMIT_TRIGGER"

Launch the shell and verify omitted features:

CREATE VIEW test AS SELECT 1; -- Should fail with "syntax error"

Compare With SEE Build:
Repeat the build with SEE enabled. If OMIT flags fail, SEE is likely overriding them.

B. Inspect Generated Code

Check parse.c for Omitted Features:
Search for keywords related to OMIT flags (e.g., "VIEW"). If present, rebuild with make clean.
Preprocess Source Manually:
Use gcc -E to expand macros and confirm #ifdef SQLITE_OMIT_XXX blocks exclude target code.

Step 2: Optimize Compiler Flags for Size

A. Replace Debug Symbols With Stripping

Remove -g and Add -s:
Modify compiler flags in CFLAGS or BCC:
```
CFLAGS = -Os -s  
```
This reduces file size by stripping debug symbols and optimizing for space.

B. Prioritize `-Os` Over `-O2`

Benchmark Size Impact:
Compare binary sizes using:
```
make clean && make sqlite3 OPTS="-Os"  
ls -lh sqlite3  
```
-Os typically reduces code size by 10–20% compared to -O2.

Step 3: Address SEE Extension Conflicts

A. Identify SEE Dependencies

Review SEE Documentation:
Determine if SEE requires specific features (e.g., virtual tables, triggers) that conflict with OMIT flags.
Selectively Re-enable Features:
If SEE requires SQLITE_OMIT_VIRTUALTABLE, remove that flag and compensate with other optimizations.

B. Patch SEE Source Code

Modify SEE Preprocessor Directives:
Edit SEE headers to honor OMIT flags. For example:
```
#ifndef SQLITE_OMIT_VIRTUALTABLE  
#include "see_vfs.c"  
#endif  
```
Caution: This may break SEE functionality if done incorrectly.

Step 4: Update Build Process for Consistency

A. Enforce Clean Builds

Automate Cleanup:
Add make clean to build scripts before compilation.
Use Version-Controlled Sources:
Ensure parse.y and other generated files are rebuilt from scratch after OMIT flag changes.

B. Switch to Amalgamation Builds

Download Amalgamation:
Use the single-file sqlite3.c amalgamation, which is pre-optimized and less prone to build artifacts.
Verify OMIT Compatibility:
Some OMIT flags are only effective in canonical source builds. Test critical flags with amalgamation.

Step 5: Fallback Strategies for Size Reduction

A. Use SQLite’s Built-in Options

Enable SQLITE_DEFAULT_AUTOMATIC_INDEX=0:
Disables automatic indexing, saving ~4KB.
Set SQLITE_MAX_ATTACHED=0:
Reduces memory overhead if ATTACH DATABASE is unused.

B. Link-Time Optimization (LTO)

Add -flto to Compiler Flags:
Allows the linker to eliminate unused code across object files.
```
CFLAGS += -flto  
```

C. Manual Feature Removal

Comment Out Unused Modules:
For advanced users: Remove functions like sqlite3_analyzer or sqlite3_rtree from sqlite3.c.
Use nm to Identify Bloat:
Analyze the binary for large functions and disable their dependencies via OMIT flags.

Final Recommendations

Prioritize Compiler Flags: Use -Os -s before relying on OMIT flags for size reduction.
Isolate SEE Builds: Test OMIT flags without SEE first, then reintroduce extensions incrementally.
Monitor SQLite Documentation: Check SQLite Compile-Time Options for version-specific OMIT flag changes.

By methodically validating build steps, optimizing compiler settings, and resolving extension conflicts, developers can achieve significant size reductions in SQLite 3.36 while retaining essential functionality.