Skip to content

Instantly share code, notes, and snippets.

@kaizhu256

kaizhu256/sqlite3_auto_extension.md

Created August 8, 2021 15:04
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kaizhu256/1e63e8119f8f580e11de2918938a6624 to your computer and use it in GitHub Desktop.
Save kaizhu256/1e63e8119f8f580e11de2918938a6624 to your computer and use it in GitHub Desktop.
Download ZIP
[sqlite] Example Showing ACTUAL Use of sqlite3_auto_extension()
Raw
sqlite3_auto_extension.md

[sqlite] Example Showing ACTUAL Use of sqlite3_auto_extension() Steve and Amy Tue, 07 Jun 2011 05:53:21 -0700

Would someone, please, show me an example (several actual, successive lines of code) in C (or C++) showing a proper use of

sqlite3_auto_extension()? I have searched the web looking for examples, but there are none that I can tell. I have also, to the best of my understanding, readhttp://www.sqlite.org/c3ref/auto_extension.html numerous times and I have had no success. I am trying to use SQLite with extensionfunctions.c, but extensionfunctions.c lacks a function called "xEntryPoint()". I do not know if I am actually supposed to pass a pointer to a function called "xEntryPoint()" or not. I am leaning toward 'no' and in reality I would think "xEntryPoint()" is merely to represent some function found in extensionfunctions.c.

Below is code I have tried: if (sqlite3Database) sqlite3_close(sqlite3Database); iCommandResult = sqlite3_open(":memory:",&sqlite3Database); bContinue = (iCommandResult == 0); if (bContinue) { iCommandResult = sqlite3_exec(sqlite3Database, "CREATE TABLE data_table (DataID INT, RandomData INT, V_In INT);", &GitData, 0, &cErrorMessage); ... Some Code to Fill the sqlite3Database in memory with data bContinue = (iCommandResult == 0); } if (bContinue) { iCommandResult = sqlite3_exec(sqlite3Database, "SELECT DataID, RandomData, V_In FROM data_table", &GitData, this, &cErrorMessage); }

    if (sqlite3Database)
    {
            iCommandResult = sqlite3_auto_extension(0);
            if (iCommandResult == 0)
                    iCommandResult = sqlite3_exec(sqlite3Database,
                                                    "SELECT

STDEV(RandomData) AS RandomData_STDEV, V_In FROM data_table GROUP BY V_In", &GitData, this, &cErrorMessage); }

Since I cannot find a function called "xEntryPoint()", I tried zero as an argument to sqlite3_auto_extension(). Not surprisingly, that did not work.

Here is some more code I tried ('adapted' from the commented out help section at the top of extensionfunctions.c): if (sqlite3Database) sqlite3_close(sqlite3Database); iCommandResult = sqlite3_open(":memory:",&sqlite3Database); iCommandResult = sqlite3_enable_load_extension(sqlite3Database, 1); bContinue = (iCommandResult == 0); if (bContinue) { iCommandResult = sqlite3_exec(sqlite3Database, "CREATE TABLE data_table (DataID INT, RandomData INT, V_In INT);", &GitData, 0, &cErrorMessage); ... Some Code to Fill the sqlite3Database in memory with data bContinue = (iCommandResult == 0); } if (bContinue) { iCommandResult = sqlite3_exec(sqlite3Database, "SELECT DataID, RandomData, V_In FROM data_table", &GitData, this, &cErrorMessage); }

    if (sqlite3Database)
    {
            iCommandResult = sqlite3_load_extension(sqlite3Database, 0, 0,

ppcLoadExtErrorMessage); if (iCommandResult == 0) iCommandResult = sqlite3_exec(sqlite3Database, "SELECT STDEV(RandomData) AS RandomData_STDEV, V_In FROM data_table GROUP BY V_In", &GitData, this, &cErrorMessage); } After executing sqlite3_load_extension, iCommandResult was set to SQLITE_ERROR (exactly what I expected), but ppcLoadExtErrorMessage was NULL, so I could not tell what the error was about.

Usage instructions from top of extensionfunctions.c: Usage instructions for applications calling the sqlite3 API functions: In your application, call sqlite3_enable_load_extension(db,1) to allow loading external libraries. Then load the library libsqlitefunctions using sqlite3_load_extension; the third argument should be 0. See http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions. Select statements may now use these functions, as in SELECT cos(radians(inclination)) FROM satsum WHERE satnum = 25544;

Alterations: The instructions are for Linux, Mac OS X, and Windows; users of other OSes may need to modify this procedure. In particular, if your math library lacks one or more of the needed trig or log functions, comment out the appropriate HAVE_ #define at the top of file. If you do not wish to make a loadable module, comment out the define for COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE. If you are using a version of SQLite without the trim functions and replace, comment out the HAVE_TRIM #define.

I tried the above code both with and without COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE commented out. Based on reading http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions, I am confident COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE should be commented out to force the compiler to use "sqlite3.h" instead of "sqlite3ext.h".

By the way, what does "libsqlitefunctions" mean? Is it supposed to be a generic description for an external library? Or is it a specific .h file or .c file?

Anybody who is willing to share some simple lines of code would be much appreciated. ultimately, not being able to use extensionfunctions.c will not be the end of the world.

Thanks Very Much! Steve.

sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Example Showing ACTUAL Use of sqlite3_auto_extension() Jay A. Kreibich Tue, 07 Jun 2011 07:56:12 -0700

On Tue, Jun 07, 2011 at 07:52:37AM -0500, Steve and Amy scratched on the wall:

Would someone, please, show me an example (several actual, successive lines of code) in C (or C++) showing a proper use of sqlite3_auto_extension()?

"Using SQLite", p209-211. http://oreilly.com/catalog/9780596521196

I am trying to use SQLite with extensionfunctions.c, but extensionfunctions.c lacks a function called "xEntryPoint()". I do not know if I am actually supposed to pass a pointer to a function called "xEntryPoint()" or not. I am leaning toward 'no' and in reality I would think "xEntryPoint()" is merely to represent some function found in extensionfunctions.c.

You need to pass a pointer to the extension's entry point function, which is not actually named "xEntryPoint"... that's just the name of the variable that accepts the function pointer (generally, any variable that starts with "x" in the SQLite source is a function pointer). The entry point function is the same function name that is passed to sqlite3_load_extension().

Also note that the typing on sqlite3_auto_extension() pointer is wrong. You must cast the function pointer. Don't ask; I have no idea.

By default, most extensions use an entry point function called "sqlite3_extension_init()". This allows sqlite3_load_extension() to automatically find the entry point when the module is loaded. You'll notice that extension-functions.c has this function, but only if COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE is defined.

The issue is that if you want to compile a module in statically, all the function names need to be unique. While the dynamic library system behind sqlite3_load_extension() can deal with multiple .DLL, .so, or .dylib files that all have unique functions with the same name, the static linker cannot. You cannot build a stand-alone executable with multiple, global functions that have the same name. This is why extension-functions.c omits the default named entry point if you're trying to build it for static usage.

In the case of extension-functions.c, it looks like you should just call RegisterExtensionFunctions() directly. Unfortunately, you'd have to do that every time you open a database-- which is exactly the issue sqlite3_auto_extension() is trying to avoid.

If you want to use extension-functions.c with sqlite3_auto_extension(), we need to get a bit hacky with the code.

First, we will want to build it for static use. To do this, comment out this line (line 111 in the current code):

#define COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE 1

Second, the entry point functions are designed to use the external linkages, no matter how they're built. While this isn't strictly required for a static build, I think it keeps it cleaner. So we need to be sure the external linkages are always used. To do that turn this (like 123):

#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 #else #include "sqlite3.h" #endif

Into this:

#include "sqlite3ext.h" SQLITE_EXTENSION_INIT1

Last, we need to be sure the entry point function is always built, but we need to define a globally unique name for when the module is used statically. To do that, we'll make sure the entry point function is always built, but we'll build it with a different name depending on how the module will be used. Turn this (line 1837 in the original file):

#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE int sqlite3_extension_init( sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines pApi){ SQLITE_EXTENSION_INIT2(pApi); RegisterExtensionFunctions(db); return 0; } #endif / COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE */

Into this:

#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE int sqlite3_extension_init( #else /* COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE / int extension_functions_init( #endif / COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE */ sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){ SQLITE_EXTENSION_INIT2(pApi); RegisterExtensionFunctions(db); return 0; }

Finally, you can call this in your application:

sqlite3_auto_extension( (void(*)(void))extension_functions_init );

With that done, any database you open with that application should have the extension-functions loaded and available. Just make sure you build your application, sqlite3.c and extension-functions.c all into one big executable. // -j

-- Jay A. Kreibich < J A Y @ K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson

sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-#define COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE 1
+// hack-sqlite
+// #define COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE 1

-#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
-#include "sqlite3ext.h"
-SQLITE_EXTENSION_INIT1
-#else
-#include "sqlite3.h"
-#endif
+// hack-sqlite
+#include "sqlite3ext.h"

-#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
-int sqlite3_extension_init(
-    sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
-  SQLITE_EXTENSION_INIT2(pApi);
-  RegisterExtensionFunctions(db);
-  return 0;
-}
-#endif /\\* COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE *\\/
+// hack-sqlite
+#ifdef COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE
+int sqlite3_extension_init(
+#else /\\* COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE *\\/
+int extension_functions_init(
+#endif /\\* COMPILE_SQLITE_EXTENSIONS_AS_LOADABLE_MODULE *\\/
+    sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi){
+  SQLITE_EXTENSION_INIT2(pApi);
+  RegisterExtensionFunctions(db);
+  return 0;
+}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment