Building SQLite with Windows 10 ICU Library Integration

Windows 10 ICU Library Integration in SQLite

Issue Overview

The integration of the ICU (International Components for Unicode) library into SQLite on Windows 10 RS3 and later versions presents a unique set of challenges and opportunities. The ICU library, which is built into Windows 10 starting with RS3, provides robust support for Unicode, including collation, string comparison, and text processing. However, leveraging this built-in ICU library in SQLite requires careful handling of compiler directives, header inclusions, and library linking, especially when using different compilers like MSVC, MinGW, or Cygwin.

The core issue revolves around correctly detecting the presence of the Windows 10 ICU library and ensuring that the appropriate headers and libraries are used during the compilation of SQLite. This involves modifying the SQLite source code to conditionally include the correct ICU headers based on the target platform and SDK version. Additionally, the process requires setting specific compiler flags and ensuring that the correct paths to the ICU headers and libraries are included in the build process.

The discussion highlights the need for precise conditional compilation directives to differentiate between the built-in ICU library on Windows and the standalone ICU library used on other platforms. It also emphasizes the importance of correctly setting the Windows SDK version and ensuring that the necessary symbols are defined before including the ICU headers. The process is further complicated by the need to handle different compilers and their specific requirements for include paths and library linking.

Possible Causes

The primary cause of the issue is the conditional inclusion of ICU headers based on the target platform and SDK version. The SQLite source code must be modified to correctly detect whether the built-in ICU library on Windows 10 RS3 and later is available and should be used. This requires setting specific compiler flags and ensuring that the correct paths to the ICU headers and libraries are included in the build process.

One of the key challenges is ensuring that the correct version of the Windows SDK is installed and that the necessary headers and libraries are accessible to the compiler. The discussion mentions that the Windows 10 SDK version 10.0.19041.0 or later is required, and the ICU headers and libraries must be located in the appropriate directories within the SDK. If these files are not present or the SDK version is incorrect, the build process will fail.

Another potential cause of the issue is the incorrect setting of compiler flags and include paths. The discussion highlights the need to define specific symbols such as _WIN32_WINNT, WINVER, and NTDDI_VERSION before including the ICU headers. If these symbols are not defined correctly, the conditional compilation directives will not work as intended, leading to the inclusion of the wrong headers or compilation errors.

The use of different compilers, such as MSVC, MinGW, or Cygwin, introduces additional complexity. Each compiler has its own requirements for setting include paths and linking libraries. For example, MinGW requires the use of the -idirafter flag to append the SDK include path without disrupting the system search path. If these compiler-specific requirements are not met, the build process will fail.

Finally, the issue is compounded by the need to handle loadable extensions in SQLite. The discussion mentions that the ICU extension cannot be named icu.dll if it is intended to load the built-in ICU library, as this would cause a conflict. Instead, the extension must be renamed, and the initialization function must be modified accordingly. This adds an additional layer of complexity to the build process.

Troubleshooting Steps, Solutions & Fixes

To successfully integrate the Windows 10 ICU library into SQLite, follow these detailed troubleshooting steps, solutions, and fixes:

1. Verify Windows 10 SDK Installation:
Ensure that the Windows 10 SDK version 10.0.19041.0 or later is installed on your system. The SDK should be located in the default installation directory, typically C:\Program Files (x86)\Windows Kits\10\. Verify that the following files exist:

• C:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\um\icu.h
• C:\Program Files (x86)\Windows Kits\10\Lib\10.0.19041.0\um\<arch>\icu.lib (where <arch> is arm, arm64, x64, or x86)
• C:\Windows\System32\icu.dll or C:\Windows\SysWOW64\icu.dll

If these files are not present, reinstall the Windows 10 SDK or update it to the required version.

2. Modify SQLite Source Code:
Update the SQLite source code to conditionally include the correct ICU headers based on the target platform and SDK version. Replace the ICU header inclusion code in ext/icu/icu.c, ext/fts2/fts2_icu.c, and ext/fts3/fts3_icu.c with the following:

/* Include ICU headers */
#if !defined(SQLITE_OS_WIN)
# if defined(_WIN32) || defined(WIN32) || defined(__CYGWIN__) || defined(__MINGW32__) || defined(__BORLANDC__)
# define SQLITE_OS_WIN 1
# else
# define SQLITE_OS_WIN 0
# endif
#endif
#if SQLITE_OS_WIN && (!defined(NTDDI_WIN10_RS3) || !defined(NTDDI_VERSION))
# include <sdkddkver.h>
#endif
#ifndef NTDDI_WIN10_RS3
# define NTDDI_WIN10_RS3 0x0A000004
#endif
#ifndef NTDDI_VERSION
# define NTDDI_VERSION 0x0000000
#endif
#if SQLITE_OS_WIN && (NTDDI_VERSION >= NTDDI_WIN10_RS3)
# include <icu.h>
#else
#include <unicode/utypes.h>
#include <unicode/uregex.h>
#include <unicode/ustring.h>
#include <unicode/ucol.h>
#endif

This code ensures that the correct ICU headers are included based on the target platform and SDK version.

3. Set Compiler Flags:
Define the necessary symbols before including the ICU headers. Add the following compiler flags to your build command:

-D_WIN32_WINNT=0xA000 -DWINVER=0xA00 -DNTDDI_VERSION=0xA000004 -DNTDDI_WIN10_RS3=0xA000004

These flags ensure that the correct Windows SDK version is targeted and that the necessary symbols are defined for conditional compilation.

4. Append SDK Include Path:
Append the SDK include path to your compiler’s include search path. For GCC-based compilers like MinGW, use the -idirafter flag:

-idirafter "C:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\um"

This ensures that the SDK headers are included without disrupting the system search path.

5. Link Against ICU Library:
Link against the appropriate ICU library for your target architecture. Use the -L flag to specify the library search path and the -l flag to link against the ICU library:

-L"C:\Program Files (x86)\Windows Kits\10\Lib\10.0.19041.0\um\<arch>" -licu

Replace <arch> with the appropriate architecture (arm, arm64, x64, or x86).

6. Rename Loadable Extension:
If you are building the ICU extension as a loadable module, rename the extension and modify the initialization function to avoid conflicts with the built-in ICU library. For example, rename icu.c to icuwin.c and update the initialization function:

#ifdef _WIN32
int sqlite3_icuwin_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
#else
int sqlite3_icu_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
#endif

This ensures that the loadable extension does not conflict with the built-in ICU library.

7. Test the ICU Extension:
After building the ICU extension, load it into SQLite and test its functionality. Use the following SQL commands to verify that the ICU collation is working correctly:

.load icuwin
select icu_load_extension('und-u-ks-level1-kc-false', 'Folded');
select char(0xE9)=='E', char(0xE9)=='E' collate Folded;

The expected output should be:

sqlite> select char(0xE9)=='E', char(0xE9)=='E' collate Folded;
┌─────────────────┬────────────────────────────────┐
│ char(0xE9)=='E' │ char(0xE9)=='E' collate Folded │
├─────────────────┼────────────────────────────────┤
│ 0               │ 1                              │
└─────────────────┴────────────────────────────────┘

This confirms that the ICU extension is correctly loaded and functioning.

8. Reflect Changes in SQLite Build Process:
Once the ICU extension is working correctly, reflect the same changes in your SQLite build process. Ensure that the conditional compilation directives, compiler flags, and include paths are correctly set in your build configuration.

By following these steps, you can successfully integrate the Windows 10 ICU library into SQLite, enabling robust Unicode support and ensuring compatibility with the built-in ICU library on Windows 10 RS3 and later versions.