Documentation Source Text

Check-in [f0275a9b47]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Updates to documentation, especially the C API introduction.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f0275a9b47a24cbeeb91b139d81c8fb37dbedae8
User & Date: drh 2013-04-27 12:01:30.311
Context
2013-04-27
18:31
More documentation tweaks. Add two new entries to the "limits" page. (check-in: 8da1a575d7 user: drh tags: trunk)
12:01
Updates to documentation, especially the C API introduction. (check-in: f0275a9b47 user: drh tags: trunk)
2013-04-26
19:35
Update the change log for 3.7.17. Fix typos in the memory-mapped I/O documentation. (check-in: c8b24bb8c6 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/cintro.in.
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
}

hd_keywords {*cintro}
HEADING 0 {An Introduction To The SQLite C/C++ Interface}
</tcl>

<p>
  This article provides an overview and roadmap to the C/C++ interface
  to SQLite.
</p>

<p>
  Early versions of SQLite were very easy to learn since they only
  supported 5 C/C++ interfaces.  But as SQLite has grown in capability,
  new C/C++ interfaces have been added so that now there
  are over 185 distinct APIs.  This can be overwhelming to a new programmer.
  Fortunately, most of the C/C++ interfaces in SQLite are very specialized
  and never need to be used.  Despite having so many
  entry points, the core API is still relatively simple and easy to code to.
  This article aims to provide all of the background information needed to
  easily understand how SQLite works.
</p>

<p>
  A separate document, [capi3ref | The SQLite C/C++ Interface],







|







|

|







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
}

hd_keywords {*cintro}
HEADING 0 {An Introduction To The SQLite C/C++ Interface}
</tcl>

<p>
  This article provides an overview to the C/C++ interface
  to SQLite.
</p>

<p>
  Early versions of SQLite were very easy to learn since they only
  supported 5 C/C++ interfaces.  But as SQLite has grown in capability,
  new C/C++ interfaces have been added so that now there
  are over 200 distinct APIs.  This can be overwhelming to a new programmer.
  Fortunately, most of the C/C++ interfaces in SQLite are very specialized
  and never need to be considered.  Despite having so many
  entry points, the core API is still relatively simple and easy to code to.
  This article aims to provide all of the background information needed to
  easily understand how SQLite works.
</p>

<p>
  A separate document, [capi3ref | The SQLite C/C++ Interface],
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
<p>
  The six C/C++ interface routines and two objects listed above form the core
  functionality of SQLite.  The developer who understands them
  will have a good foundation for using SQLite.
</p>

<p>
  Note that the list of routines is conceptual rather than actual.
  Many of these routines come in multiple versions.
  For example, the list above shows a single routine
  named [sqlite3_open()] when in fact there are three separate routines
  that accomplish the same thing in slightly different ways:
  [sqlite3_open()], [sqlite3_open16()] and [sqlite3_open_v2()].
  The list mentions [sqlite3_column_int | sqlite3_column()]
  when in fact no such routine exists.







|







105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
<p>
  The six C/C++ interface routines and two objects listed above form the core
  functionality of SQLite.  The developer who understands them
  will have a good foundation for using SQLite.
</p>

<p>
  Note that the list of routines above is conceptual rather than actual.
  Many of these routines come in multiple versions.
  For example, the list above shows a single routine
  named [sqlite3_open()] when in fact there are three separate routines
  that accomplish the same thing in slightly different ways:
  [sqlite3_open()], [sqlite3_open16()] and [sqlite3_open_v2()].
  The list mentions [sqlite3_column_int | sqlite3_column()]
  when in fact no such routine exists.
144
145
146
147
148
149
150
151





152
153
154
155
156
157
158
159
160
<td valign="top">
  This routine
  converts SQL text into a [prepared statement] object and returns a pointer
  to that object.  This interface requires a [database connection] pointer
  created by a prior call to [sqlite3_open()] and a text string containing
  the SQL statement to be prepared.  This API does not actually evaluate
  the SQL statement.  It merely prepares the SQL statement for evaluation.






  <p>Note that the use of [sqlite3_prepare()] is not recommended for new
  applications.  The alternative routine [sqlite3_prepare_v2()] should
  be used instead.</p>
</td>

<tr><td valign="top" align="right">[sqlite3_step()]</td>
<td valign="top">
  This routine is used to evaluate a [prepared statement] that has been
  previously created by the [sqlite3_prepare()] interface.  The statement








>
>
>
>
>

|







144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
<td valign="top">
  This routine
  converts SQL text into a [prepared statement] object and returns a pointer
  to that object.  This interface requires a [database connection] pointer
  created by a prior call to [sqlite3_open()] and a text string containing
  the SQL statement to be prepared.  This API does not actually evaluate
  the SQL statement.  It merely prepares the SQL statement for evaluation.

  <p>Think of each SQL statement as a small computer program.  The purpose
  of [sqlite3_prepare()] is to compile that program into object code.
  The [prepared statement] is the object code.  The [sqlite3_step()] interface
  then runs the object code to get a result.

  <p>Note that the use of [sqlite3_prepare()] is not recommended for new
  applications.  A newer alternative routine [sqlite3_prepare_v2()] should
  be used instead.</p>
</td>

<tr><td valign="top" align="right">[sqlite3_step()]</td>
<td valign="top">
  This routine is used to evaluate a [prepared statement] that has been
  previously created by the [sqlite3_prepare()] interface.  The statement
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
<td valign="top">
  This routine returns a single column from the current row of a result
  set for a [prepared statement] that is being evaluated by [sqlite3_step()].
  Each time [sqlite3_step()] stops with a new result set row, this routine
  can be called multiple times to find the values of all columns in that row.
  As noted above, there really is no such thing as a "sqlite3_column()"
  function in the SQLite API.  Instead, what we here call "sqlite3_column()"
  is really a place-holder for an entire family of functions that return
  a value from the result set in various data types.  There are also routines
  in this family that return the size of the result (if it is a string or
  BLOB) and the number of columns in the result set.  

  <p><ul>
    <li> [sqlite3_column_blob()] </li>
    <li> [sqlite3_column_bytes()] </li>







|







175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
<td valign="top">
  This routine returns a single column from the current row of a result
  set for a [prepared statement] that is being evaluated by [sqlite3_step()].
  Each time [sqlite3_step()] stops with a new result set row, this routine
  can be called multiple times to find the values of all columns in that row.
  As noted above, there really is no such thing as a "sqlite3_column()"
  function in the SQLite API.  Instead, what we here call "sqlite3_column()"
  is a place-holder for an entire family of functions that return
  a value from the result set in various data types.  There are also routines
  in this family that return the size of the result (if it is a string or
  BLOB) and the number of columns in the result set.  

  <p><ul>
    <li> [sqlite3_column_blob()] </li>
    <li> [sqlite3_column_bytes()] </li>
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
<p>
  An application is allowed to prepare multiple SQL statements in advance
  and evaluate them as needed.
  There is no arbitrary limit to the number of outstanding
  [prepared statements].
</p>



























<tcl>HEADING 1 {Extending SQLite}</tcl>

<p>
  SQLite includes interfaces that can be used to extend its functionality.
  Such routines include:
</p>

<p><ul>
  <li> [sqlite3_create_collation()] </li>
  <li> [sqlite3_create_function()] </li>
  <li> [sqlite3_create_module()] </li>

</ul></p>

<p>
  The [sqlite3_create_collation()] interface is used to create new
  collating sequences for sorting text.
  The [sqlite3_create_module()] interface is used to register new
  virtual table implementations.

</p>

<p>
  The [sqlite3_create_function()] interface creates new SQL functions - 
  either scalar or aggregate.  The new function implementation typically
  makes use of the following additional interfaces:
</p>

<p><ul>
  <li> [sqlite3_aggregate_context()] </li>
  <li> [sqlite3_result_int | sqlite3_result()] </li>
  <li> [sqlite3_user_data()] </li>
  <li> [sqlite3_value_int | sqlite3_value()] </li>
</ul></p>

<p>
  All of the built-in SQL functions of SQLite are created using exactly
  these same interfaces.  Refer to the SQLite source code, and in particular



  the <b>date.c</b> and <b>func.c</b> source files for examples.
</p>




<tcl>HEADING 1 {Other Interfaces}</tcl>

<p>
  This article only mentions the foundational SQLite interfaces.
  The SQLite library includes many other APIs implementing useful
  features that are not described here.  
  A [capi3ref_funclist | complete list of functions] that form the SQLite
  application programming interface is found at the
  [capi3ref | C/C++ Interface Specification].
  Refer to that document for complete and authoritative information about
  all SQLite interfaces.
</p>







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>











>




|

|
>


















>
>
>
|


>
>
>












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
<p>
  An application is allowed to prepare multiple SQL statements in advance
  and evaluate them as needed.
  There is no arbitrary limit to the number of outstanding
  [prepared statements].
</p>

<tcl>HEADING 1 {Configuring SQLite}</tcl>

<p>
  The default configuration for SQLite works great for most applications.
  But sometimes developers want to tweak the setup to try to squeeze out
  a little more performance, or take advantage of some obscure feature.
<p>
  The [sqlite3_config()] interface is used to make global, process-wide
  configuration changes for SQLite.  The [sqlite3_config()] interface must
  be called before any [database connections] are created.  The
  [sqlite3_config()] interface allows the programmer to do things like:
<ul>
<li>Adjust how SQLite does [memory allocation], including setting up
    alternative memory allocators appropriate for safety-critical
    real-time embedded systems and application-defind memory allocators.
<li>Set up a process-wide [error log].
<li>Specify an application-defined page cache.
<li>Adjust the use of mutexes so that they are appropriate for various
    [threading mode | threading models], or substitute an 
    application-defined mutex system.
</ul> 
<p>
  After process-wide configuration is complete and [database connections]
  have been created, individual database connections can be configured using
  calls to [sqlite3_limit()] and [sqlite3_db_config()].

<tcl>HEADING 1 {Extending SQLite}</tcl>

<p>
  SQLite includes interfaces that can be used to extend its functionality.
  Such routines include:
</p>

<p><ul>
  <li> [sqlite3_create_collation()] </li>
  <li> [sqlite3_create_function()] </li>
  <li> [sqlite3_create_module()] </li>
  <li> [sqlite3_vfs_register()] </li>
</ul></p>

<p>
  The [sqlite3_create_collation()] interface is used to create new
  [collating sequences] for sorting text.
  The [sqlite3_create_module()] interface is used to register new
  [virtual table] implementations.
  The [sqlite3_vfs_register()] interface creates new [VFSes].
</p>

<p>
  The [sqlite3_create_function()] interface creates new SQL functions - 
  either scalar or aggregate.  The new function implementation typically
  makes use of the following additional interfaces:
</p>

<p><ul>
  <li> [sqlite3_aggregate_context()] </li>
  <li> [sqlite3_result_int | sqlite3_result()] </li>
  <li> [sqlite3_user_data()] </li>
  <li> [sqlite3_value_int | sqlite3_value()] </li>
</ul></p>

<p>
  All of the built-in SQL functions of SQLite are created using exactly
  these same interfaces.  Refer to the SQLite source code, and in particular
  the 
  [http://www.sqlite.org/src/doc/trunk/src/date.c | date.c] and
  [http://www.sqlite.org/src/doc/trunk/src/func.c | func.c] source files
  for examples.
</p>

<p>
  Shared libraries or DLLs can be used as [loadable extensions] to SQLite.

<tcl>HEADING 1 {Other Interfaces}</tcl>

<p>
  This article only mentions the foundational SQLite interfaces.
  The SQLite library includes many other APIs implementing useful
  features that are not described here.  
  A [capi3ref_funclist | complete list of functions] that form the SQLite
  application programming interface is found at the
  [capi3ref | C/C++ Interface Specification].
  Refer to that document for complete and authoritative information about
  all SQLite interfaces.
</p>
Changes to pages/lang.in.
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
their result across all rows of the result set.</p>

<tcl>
##############################################################################
Section {Core Functions} corefunc {*corefunc}
proc funcdef {syntax keywords desc} {
  hd_puts {<tr>}
  regsub -all {\s+} [string trim $syntax] {<br></br>} syntax
  regsub -all {\(([^*)]+)\)} $syntax {(<i>\1</i>)} syntax
  regsub -all {,} $syntax {</i>,<i>} syntax
  regsub -all {<i>\.\.\.</i>} $syntax {...} syntax
  hd_puts "<td valign=\"top\" align=\"right\" width=\"120\">"
  if {[llength $keywords]==0} {
    regexp {[a-z_]+} $syntax name
    hd_fragment $name *$name "${name}() SQL function"







|







2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
their result across all rows of the result set.</p>

<tcl>
##############################################################################
Section {Core Functions} corefunc {*corefunc}
proc funcdef {syntax keywords desc} {
  hd_puts {<tr>}
  regsub -all {\s+} [string trim $syntax] {<br />} syntax
  regsub -all {\(([^*)]+)\)} $syntax {(<i>\1</i>)} syntax
  regsub -all {,} $syntax {</i>,<i>} syntax
  regsub -all {<i>\.\.\.</i>} $syntax {...} syntax
  hd_puts "<td valign=\"top\" align=\"right\" width=\"120\">"
  if {[llength $keywords]==0} {
    regexp {[a-z_]+} $syntax name
    hd_fragment $name *$name "${name}() SQL function"
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152



2153
2154
2155
2156
2157
2158
2159
  [LIKE] operator depending on whether or not an ESCAPE clause was 
  specified.
}

funcdef {load_extension(X) load_extension(X,Y)} {} {
  ^The load_extension(X,Y) function loads [SQLite extensions] out of the shared
  library file named X using the entry point Y.  ^The result of load_extension()
  is always a NULL.  ^If Y is omitted then the default entry point
  of <b>sqlite3_extension_init</b> is used.  ^The load_extension() function
  raises an exception if the extension fails to load or initialize correctly.

  <p>^The load_extension() function will fail if the extension attempts to 
  modify or delete an SQL function or collating sequence.  ^The
  extension can add new functions or collating sequences, but cannot
  modify or delete existing functions or collating sequences because
  those functions and/or collating sequences might be used elsewhere
  in the currently running SQL statement.  To load an extension that
  changes or deletes functions or collating sequences, use the
  [sqlite3_load_extension()] C-language API.</p>



}

funcdef {lower(X)} {} {
  ^The lower(X) function returns a copy of string X with all ASCII characters
  converted to lower case.  ^The default built-in lower() function works
  for ASCII characters only.  To do case conversions on non-ASCII
  characters, load the ICU extension.







|
|
|









>
>
>







2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
  [LIKE] operator depending on whether or not an ESCAPE clause was 
  specified.
}

funcdef {load_extension(X) load_extension(X,Y)} {} {
  ^The load_extension(X,Y) function loads [SQLite extensions] out of the shared
  library file named X using the entry point Y.  ^The result of load_extension()
  is always a NULL.  ^If Y is omitted then the default entry point name is used.
  ^The load_extension() function raises an exception if the extension fails to
  load or initialize correctly.

  <p>^The load_extension() function will fail if the extension attempts to 
  modify or delete an SQL function or collating sequence.  ^The
  extension can add new functions or collating sequences, but cannot
  modify or delete existing functions or collating sequences because
  those functions and/or collating sequences might be used elsewhere
  in the currently running SQL statement.  To load an extension that
  changes or deletes functions or collating sequences, use the
  [sqlite3_load_extension()] C-language API.</p>

  <p>For security reasons, extension loaded is turned off by default and must
  be enabled by a prior call to [sqlite3_enable_load_extension()].</p>
}

funcdef {lower(X)} {} {
  ^The lower(X) function returns a copy of string X with all ASCII characters
  converted to lower case.  ^The default built-in lower() function works
  for ASCII characters only.  To do case conversions on non-ASCII
  characters, load the ICU extension.
Changes to pages/vfs.in.
146
147
148
149
150
151
152

153
154
155
156
157
158
159

<p>
The VFS specified by a URI has the highest priority.  After that comes
a VFS specified as the fourth argument to [sqlite3_open_v2()].  The
default VFS is used if no VFS is specified otherwise.
</p>


<h2>2.2 VFS Shims</h2>

<p>
From the point of view of the uppers layers of the SQLite stack, each
open database file uses exactly one VFS.
But in practice, a particular VFS might
just be a thin wrapper around another VFS that does the real work.







>







146
147
148
149
150
151
152
153
154
155
156
157
158
159
160

<p>
The VFS specified by a URI has the highest priority.  After that comes
a VFS specified as the fourth argument to [sqlite3_open_v2()].  The
default VFS is used if no VFS is specified otherwise.
</p>

<tcl>hd_fragment shim {VFS shims} {shims}</tcl>
<h2>2.2 VFS Shims</h2>

<p>
From the point of view of the uppers layers of the SQLite stack, each
open database file uses exactly one VFS.
But in practice, a particular VFS might
just be a thin wrapper around another VFS that does the real work.