summaryrefslogtreecommitdiffstats
path: root/www/loadext.html
blob: daa2912aab8de42d686d6aebcc60ac39e9628a18 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
<!DOCTYPE html>
<html><head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<link href="sqlite.css" rel="stylesheet">
<title>Run-Time Loadable Extensions</title>
<!-- path= -->
</head>
<body>
<div class=nosearch>
<a href="index.html">
<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0">
</a>
<div><!-- IE hack to prevent disappearing logo --></div>
<div class="tagline desktoponly">
Small. Fast. Reliable.<br>Choose any three.
</div>
<div class="menu mainmenu">
<ul>
<li><a href="index.html">Home</a>
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
<li class='wideonly'><a href='about.html'>About</a>
<li class='desktoponly'><a href="docs.html">Documentation</a>
<li class='desktoponly'><a href="download.html">Download</a>
<li class='wideonly'><a href='copyright.html'>License</a>
<li class='desktoponly'><a href="support.html">Support</a>
<li class='desktoponly'><a href="prosupport.html">Purchase</a>
<li class='search' id='search_menubutton'>
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
</ul>
</div>
<div class="menu submenu" id="submenu">
<ul>
<li><a href='about.html'>About</a>
<li><a href='docs.html'>Documentation</a>
<li><a href='download.html'>Download</a>
<li><a href='support.html'>Support</a>
<li><a href='prosupport.html'>Purchase</a>
</ul>
</div>
<div class="searchmenu" id="searchmenu">
<form method="GET" action="search">
<select name="s" id="searchtype">
<option value="d">Search Documentation</option>
<option value="c">Search Changelog</option>
</select>
<input type="text" name="q" id="searchbox" value="">
<input type="submit" value="Go">
</form>
</div>
</div>
<script>
function toggle_div(nm) {
var w = document.getElementById(nm);
if( w.style.display=="block" ){
w.style.display = "none";
}else{
w.style.display = "block";
}
}
function toggle_search() {
var w = document.getElementById("searchmenu");
if( w.style.display=="block" ){
w.style.display = "none";
} else {
w.style.display = "block";
setTimeout(function(){
document.getElementById("searchbox").focus()
}, 30);
}
}
function div_off(nm){document.getElementById(nm).style.display="none";}
window.onbeforeunload = function(e){div_off("submenu");}
/* Disable the Search feature if we are not operating from CGI, since */
/* Search is accomplished using CGI and will not work without it. */
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
document.getElementById("search_menubutton").style.display = "none";
}
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */
function hideorshow(btn,obj){
var x = document.getElementById(obj);
var b = document.getElementById(btn);
if( x.style.display!='none' ){
x.style.display = 'none';
b.innerHTML='show';
}else{
x.style.display = '';
b.innerHTML='hide';
}
return false;
}
var antiRobot = 0;
function antiRobotGo(){
if( antiRobot!=3 ) return;
antiRobot = 7;
var j = document.getElementById("mtimelink");
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
}
function antiRobotDefense(){
document.body.onmousedown=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousedown=null;
}
document.body.onmousemove=function(){
antiRobot |= 2;
antiRobotGo();
document.body.onmousemove=null;
}
setTimeout(function(){
antiRobot |= 1;
antiRobotGo();
}, 100)
antiRobotGo();
}
antiRobotDefense();
</script>
<div class=fancy>
<div class=nosearch>
<div class="fancy_title">
Run-Time Loadable Extensions
</div>
<div class="fancy_toc">
<a onclick="toggle_toc()">
<span class="fancy_toc_mark" id="toc_mk">&#x25ba;</span>
Table Of Contents
</a>
<div id="toc_sub"><div class="fancy-toc1"><a href="#overview">1. Overview</a></div>
<div class="fancy-toc1"><a href="#loading_an_extension">2. Loading An Extension</a></div>
<div class="fancy-toc1"><a href="#compiling_a_loadable_extension">3. Compiling A Loadable Extension</a></div>
<div class="fancy-toc1"><a href="#programming_loadable_extensions">4. Programming Loadable Extensions</a></div>
<div class="fancy-toc2"><a href="#example_extensions">4.1. Example Extensions</a></div>
<div class="fancy-toc1"><a href="#persistent_loadable_extensions">5. Persistent Loadable Extensions</a></div>
<div class="fancy-toc1"><a href="#statically_linking_a_run_time_loadable_extension">6. Statically Linking A Run-Time Loadable Extension</a></div>
<div class="fancy-toc1"><a href="#implementation_details">7. Implementation Details</a></div>
</div>
</div>
<script>
function toggle_toc(){
var sub = document.getElementById("toc_sub")
var mk = document.getElementById("toc_mk")
if( sub.style.display!="block" ){
sub.style.display = "block";
mk.innerHTML = "&#x25bc;";
} else {
sub.style.display = "none";
mk.innerHTML = "&#x25ba;";
}
}
</script>
</div>





<h1 id="overview"><span>1. </span>Overview</h1>

<p>SQLite has the ability to load extensions (including new
<a href="appfunc.html">application-defined SQL functions</a>,
<a href="datatype3.html#collation">collating sequences</a>, <a href="vtab.html">virtual tables</a>, and <a href="vfs.html">VFSes</a>) at run-time.
This feature allows the code for extensions to be developed and
tested separately from the application and then loaded
on an as-needed basis.</p>

<p>Extensions can also be statically linked with the application.
The code template shown below will work just as well as a statically
linked extension as it does as a run-time loadable extension except that
you should give the entry point function ("sqlite3_extension_init")
a different name to avoid name collisions if your application contains
two or more extensions.</p>

<h1 id="loading_an_extension"><span>2. </span>Loading An Extension</h1>

<p>An SQLite extension is a shared library or DLL.  To load it, you
need to supply SQLite with the name of the file containing the
shared library or DLL and an entry point to initialize the extension.
In C code, this information is supplied using the
<a href="c3ref/load_extension.html">sqlite3_load_extension()</a> API.  See the documentation on that
routine for additional information.</p>

<p>Note that different operating systems use different filename
suffixes for their shared libraries.  Windows uses ".dll", Mac uses
".dylib", and most unixes other than mac use ".so".  If you want to
make your code portable, you can omit the suffix from the shared
library filename and the appropriate suffix will be added automatically
by the <a href="c3ref/load_extension.html">sqlite3_load_extension()</a> interface.</p>

<p>There is also an SQL function that can be used to load extensions:
<a href="lang_corefunc.html#load_extension">load_extension(X,Y)</a>.  It works just like the <a href="c3ref/load_extension.html">sqlite3_load_extension()</a>
C interface.</p>

<p>Both methods for loading an extension allow you to specify
the name of an entry point for the extension.  
You can leave this argument blank - passing in
a NULL pointer for the <a href="c3ref/load_extension.html">sqlite3_load_extension()</a> C-language interface
or omitting the second argument for the <a href="lang_corefunc.html#load_extension">load_extension()</a> SQL interface -
and the extension loader logic will attempt to figure out the entry point
on its own.  It will first try the generic extension name
"sqlite3_extension_init".  If that does not work, it constructs a
entry point using the template "sqlite3_X_init" where the X is replaced
by the lowercase equivalent of every ASCII character in the filename
after the last "/" and before the first following "." omitting the
first three characters if they happen to be "lib".  So, for example,
if the filename is "/usr/lib/libmathfunc-4.8.so" the entry point name
would be "sqlite3_mathfunc_init".  Or if the filename is 
"./SpellFixExt.dll" then the entry point would be called
"sqlite3_spellfixext_init".

</p><p>For security reasons, extension loading is turned off by default.
In order to use either the C-language or SQL extension loading functions,
one must first enable extension loading using the
<a href="c3ref/db_config.html">sqlite3_db_config</a>(db,<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigenableloadextension">SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION</a>,1,NULL)
C-language API in your application.</p>

<p>From the <a href="cli.html">command-line shell</a>, extensions can be loaded using the
".load" dot-command.  For example:

</p><blockquote><pre>
.load ./YourCode
</pre></blockquote>

<p>Note that the command-line shell program has already enabled
extension loading for you (by calling the <a href="c3ref/enable_load_extension.html">sqlite3_enable_load_extension()</a>
interface as part of its setup) so the command above works without
any special switches, setup, or other complications.</p>

<p>The ".load" command with one argument invokes sqlite3_load_extension()
with the zProc parameter set to NULL, causing SQLite to first look for
an entry point named "sqlite3_extension_init" and then "sqlite3_X_init"
where "X" is derived from the filename.  If your extension has an entry
point with a different name, simply supply that name as the second
argument.  For example:</p>

<blockquote><pre>
.load ./YourCode nonstandard_entry_point
</pre></blockquote>

<a name="build"></a>

<h1 id="compiling_a_loadable_extension"><span>3. </span>Compiling A Loadable Extension</h1>

<p>Loadable extensions are C-code.  To compile them on
most unix-like operating
systems, the usual command is something like this:</p>

<blockquote><pre>
gcc -g -fPIC -shared YourCode.c -o YourCode.so
</pre></blockquote>

<p>Macs are unix-like, but they do not follow the usual shared library
conventions.  To compile a shared library on a Mac, use a command like
this:</p>

<blockquote><pre>
gcc -g -fPIC -dynamiclib YourCode.c -o YourCode.dylib
</pre></blockquote>

<p>If when you try to load your library you get back an error message
that says "mach-o, but wrong architecture" then you might need to add
command-line options "-arch i386" or "arch x86_64" to gcc, depending
on how your application is built.</p>

<p>To compile on Windows using MSVC, a command similar to the following
will usually work:</p>

<blockquote><pre>
cl YourCode.c -link -dll -out:YourCode.dll
</pre></blockquote>

<p>To compile for Windows using MinGW, the command line is just like it
is for unix except that the output file suffix is changed to ".dll" and
the -fPIC argument is omitted:</p>

<blockquote><pre>
gcc -g -shared YourCode.c -o YourCode.dll
</pre></blockquote>

<a name="write"></a>

<h1 id="programming_loadable_extensions"><span>4. </span>Programming Loadable Extensions</h1>

<p>A template loadable extension contains the following three elements:</p>

<ol>
<li><p>
Use "<tt>#include &lt;sqlite3ext.h&gt;</tt>" at the top of your source
code files instead of "<tt>#include &lt;sqlite3.h&gt;</tt>".
</p>

</li><li><p>
Put the macro "<tt>SQLITE_EXTENSION_INIT1</tt>" on a line by itself 
right after the "<tt>#include &lt;sqlite3ext.h&gt;</tt>" line.
</p>

</li><li><p>
Add an extension loading entry point routine that looks like 
something the following:

</p><div class="codeblock"><pre>#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_extension_init( /* &lt;== Change this name, maybe */
  sqlite3 *db, 
  char **pzErrMsg, 
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  /* insert code to initialize your extension here */
  return rc;
}
</pre></div>

<p>You will do well to customize the name of your entry point to
correspond to the name of the shared library you will be generating,
rather than using the generic "sqlite3_extension_init" name.  Giving
your extension a custom entry point name will enable you to statically
link two or more extensions into the same program without a linker
conflict, if you later decide to use static linking rather than run-time
linking.
If your shared library ends up being named "YourCode.so" or
"YourCode.dll" or "YourCode.dylib" as shown in the compiler examples
above, then the correct entry point name would be
"sqlite3_yourcode_init".
</p></li></ol>

<p>Here is a complete template extension that you can copy/paste 
to get started:</p>

<div class="codeblock"><pre>/* Add your header comment here */
#include &lt;sqlite3ext.h&gt; /* Do not use &lt;sqlite3.h&gt;! */
SQLITE_EXTENSION_INIT1

/* Insert your extension code here */

#ifdef _WIN32
__declspec(dllexport)
#endif
/* TODO: Change the entry point name so that "extension" is replaced by
** text derived from the shared library filename as follows:  Copy every
** ASCII alphabetic character from the filename after the last "/" through
** the next following ".", converting each character to lowercase, and
** discarding the first three characters if they are "lib".
*/
int sqlite3_extension_init(
  sqlite3 *db, 
  char **pzErrMsg, 
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  /* Insert here calls to
  **     sqlite3_create_function_v2(),
  **     sqlite3_create_collation_v2(),
  **     sqlite3_create_module_v2(), and/or
  **     sqlite3_vfs_register()
  ** to register the new features that your extension adds.
  */
  return rc;
}
</pre></div>

<h2 id="example_extensions"><span>4.1. </span>Example Extensions</h2>

<p>Many examples of complete and working loadable extensions can be 
seen in the SQLite source tree in the
<a href="https://www.sqlite.org/src/file/ext/misc">ext/misc</a> subdirectory.
Each file in that directory is a separate extension.  Documentation
is provided by a header comment on the file.
Here are brief notes on a few of the extensions in 
the <a href="https://www.sqlite.org/src/file/ext/misc">ext/misc</a> subdirectory:

</p><ul>
<li><p>
<a href="https://www.sqlite.org/src/file/ext/misc/carray.c">carray.c</a> &mdash;
Implementation of the <a href="carray.html">carray table-valued function</a>.
</p></li><li><p>
<a href="https://www.sqlite.org/src/file/ext/misc/compress.c">compress.c</a> &mdash;
Implementation of <a href="appfunc.html">application-defined SQL functions</a> compress() and
uncompress() that do zLib compression of text or blob content.
</p></li><li><p>
<a href="https://www.sqlite.org/src/file/ext/misc/json1.c">json1.c</a> &mdash;
Implementation of <a href="json1.html">JSON SQL functions</a> and <a href="vtab.html#tabfunc2">table-valued functions</a>.
This is a larger and more complex extension.
</p></li><li><p>
<a href="https://www.sqlite.org/src/file/ext/misc/memvfs.c">memvfs.c</a> &mdash;
Implementation of a new <a href="vfs.html">VFS</a> that stores all content in-memory.
</p></li><li><p>
<a href="https://www.sqlite.org/src/file/ext/misc/rot13.c">rot13.c</a> &mdash;
Implementation of a <a href="https://en.wikipedia.org/wiki/ROT13">rot13()</a> 
SQL function.  This is a very simple example of an extension function
and is useful as a template for creating new extensions.
</p></li><li><p>
<a href="https://www.sqlite.org/src/file/ext/misc/series.c">series.c</a> &mdash;
Implementation of the generate_series <a href="vtab.html">virtual table</a> and
<a href="vtab.html#tabfunc2">table-valued function</a>.  This is a relatively simple example of a
virtual table implementation which can serve as a template for writing
new virtual tables.
</p></li></ul>

<p>Other and more complex extensions can be found in subfolders
under <a href="https://www.sqlite.org/src/file/ext">ext/</a> other than ext/misc/.

<a name="persist"></a>

</p><h1 id="persistent_loadable_extensions"><span>5. </span>Persistent Loadable Extensions</h1>

<p>The default behavior for a loadable extension is that it is unloaded
from process memory when the database connection that originally invoked
<a href="c3ref/load_extension.html">sqlite3_load_extension()</a> closes.  (In other words, the xDlClose method
of the <a href="c3ref/vfs.html">sqlite3_vfs</a> object is called for all extensions when a database
connection closes.)  However, if the initialization procedure returns
<a href="rescode.html#ok_load_permanently">SQLITE_OK_LOAD_PERMANENTLY</a> instead of SQLITE_OK, then the extension will
not be unloaded (xDlClose will not be invoked) and the extension will remain
in process memory indefinitely.  The SQLITE_OK_LOAD_PERMANENTLY return
value is useful for extensions that want to register new <a href="vfs.html">VFSes</a>.

</p><p>To clarify: an extension for which the initialization function returns
SQLITE_OK_LOAD_PERMANENTLY continues to exist in memory after the database
connection closes.  However, the extension is <em>not</em> automatically
registered with subsequent database connections.  This makes it possible
to load extensions that implement new <a href="vfs.html">VFSes</a>.
To persistently load and register an extension that implements new SQL
functions, collating sequences, and/or virtual tables, such that those
added capabilities are available to all subsequent database connections,
then the initialization routine should also invoke <a href="c3ref/auto_extension.html">sqlite3_auto_extension()</a>
on a subfunction that will register those services.

</p><p>The <a href="https://sqlite.org/src/file/ext/misc/vfsstat.c">vfsstat.c</a> extension
show an example of a loadable extension that persistently registers both
a new VFS and a new virtual table.  The
<a href="https://sqlite.org/src/info/77b5b4235c9f7f11?ln=801-819">sqlite3_vfsstat_init()</a>
initialization routine in that extension is called only once, when the
extension is first loaded.  It registers the new "vfslog" VFS just that
one time, and it returns SQLITE_OK_LOAD_PERMANENTLY so that the code used
to implement the "vfslog" VFS will remain in memory. The initialization routine
also invokes <a href="c3ref/auto_extension.html">sqlite3_auto_extension()</a> on a pointer to the "vstatRegister()"
function so that all subsequent database connections will invoke the
"vstatRegister()" function as they start up, and hence register the
"vfsstat" virtual table.

<a name="statext"></a>

</p><h1 id="statically_linking_a_run_time_loadable_extension"><span>6. </span>Statically Linking A Run-Time Loadable Extension</h1>

<p>The exact same source code can be used for both a run-time loadable
shared library or DLL and as a module that is statically linked with your
application.  This provides flexibility and allows you to reuse the same
code in different ways.</p>

<p>To statically link your extension, simply add the -DSQLITE_CORE
compile-time option.  The SQLITE_CORE macro causes the SQLITE_EXTENSION_INIT1
and SQLITE_EXTENSION_INIT2 macros to become no-ops.  Then modify your
application to invoke the entry point directly, passing in a NULL pointer
as the third "pApi" parameter.</p>

<p>It is particularly important to use an entry point name that is
based on the extension filename, rather than the generic
"sqlite3_extension_init" entry point name, if you will be statically
linking two or more extensions.  If you use the generic name, there
will be multiple definitions of the same symbol and the link will fail.</p>

<p>If you will be opening multiple database connections in your application,
rather than invoking the extension entry points for each database
connection separately, you might want to consider using the
<a href="c3ref/auto_extension.html">sqlite3_auto_extension()</a> interface to register your extensions and
to cause them to be automatically started as each database connection
is opened.  You only have to register each extension once, and you can
do so near the beginning of your main() routine.  Using the
<a href="c3ref/auto_extension.html">sqlite3_auto_extension()</a> interface to register your extensions makes
your extensions work as if they were built into the core SQLite - they
automatically exist whenever you open a new database connection
without needing to be initialized.  Just be sure to complete any
configuration you need to accomplish using <a href="c3ref/config.html">sqlite3_config()</a> before
registering your extensions, since the <a href="c3ref/auto_extension.html">sqlite3_auto_extension()</a>
interface implicitly calls <a href="c3ref/initialize.html">sqlite3_initialize()</a>.</p>

<h1 id="implementation_details"><span>7. </span>Implementation Details</h1>

<p>SQLite implements run-time extension loading using the
xDlOpen(), xDlError(), xDlSym(), and xDlClose() methods of the
<a href="c3ref/vfs.html">sqlite3_vfs</a> object.  These methods are implemented using
the dlopen() library on unix (which explains why SQLite commonly
needs to be linked against the "-ldl" library on unix systems)
and using LoadLibrary() API on Windows.  In a custom <a href="vfs.html">VFS</a> for
unusual systems, these methods can all be omitted, in which case
the run-time extension loading mechanism will not work (though
you will still be able to statically link the extension code, assuming
the entry pointers are uniquely named).
SQLite can be compiled with
<a href="compile.html#omit_load_extension">SQLITE_OMIT_LOAD_EXTENSION</a> to omit the extension loading code
from the build.
</p><p align="center"><small><i>This page last modified on  <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink"  data-href="https://sqlite.org/docsrc/finfo/pages/loadext.in?m=685a124ac3">2024-01-31 23:45:50</a> UTC </small></i></p>