Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Additional work on the C/C++ interface roadmap. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
85e3a7396830bb33147eb8dfac10ea79 |
User & Date: | drh 2008-05-10 15:45:30.000 |
Context
2008-05-10
| ||
16:22 | Better hyperlinks. (check-in: 32b6f9cdf7 user: drh tags: trunk) | |
15:45 | Additional work on the C/C++ interface roadmap. (check-in: 85e3a73968 user: drh tags: trunk) | |
2008-05-09
| ||
17:46 | Begin working on an article that contains a introduction and roadmap for the C/C++ interface. (check-in: 3cd85c12aa user: drh tags: trunk) | |
Changes
Changes to pages/capi3ref.in.
︙ | ︙ | |||
334 335 336 337 338 339 340 341 342 343 344 345 346 347 | if {[regexp {^sqlite} $kw]} { lappend funclist [list $k $kw] } } } hd_open_aux c3ref/funclist.html hd_header {List Of SQLite Functions} hd_enable_main 0 hd_puts {<a href="intro.html"><h2>SQLite C Interface</h2></a>} hd_enable_main 1 hd_puts {<h2>Functions:</h2>} set funclist [lsort -index 1 $funclist] output_list 3 $funclist hd_enable_main 0 | > | 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 | if {[regexp {^sqlite} $kw]} { lappend funclist [list $k $kw] } } } hd_open_aux c3ref/funclist.html hd_header {List Of SQLite Functions} hd_keywords {capi3ref_funclist} hd_enable_main 0 hd_puts {<a href="intro.html"><h2>SQLite C Interface</h2></a>} hd_enable_main 1 hd_puts {<h2>Functions:</h2>} set funclist [lsort -index 1 $funclist] output_list 3 $funclist hd_enable_main 0 |
︙ | ︙ |
Changes to pages/cintro.in.
︙ | ︙ | |||
54 55 56 57 58 59 60 | 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], provides detailed | | > | | | | | | | | | | < < | < < < < | < < | | | | | | | | | < < < < | | | > | | > | > > > > | | | 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 | 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], provides detailed specifications for all of the various C/C++ APIs for SQLite. Once the reader understands the basic principles of operation for SQLite, [capi3ref | that document] should be used as a reference guide. This article is intended as introduction only and is neither a complete or authoritative reference for the SQLite API. </p> <tcl>HEADING 1 {Core Objects And Interfaces}</tcl> <p> The principal function of an SQL database engine is to evaluate statements of SQL. In order to accomplish this in SQLite, the developer needs to know about two objects: </p> <p><ul> <li> The [database connection] object: sqlite3 </li> <li> The [prepared statement] object: sqlite3_stmt </li> </ul></p> <p> Strictly speaking, the [prepared statement] object is not required since the convenience wrapper interfaces, [sqlite3_exec] or [sqlite3_get_table], that encapsulate and hide the prepared statement can be used instead. Nevertheless, and understanding of [prepared statements] is needed to make full use of SQLite. </p> <p> The [database connection] and [prepared statement] objects are controlled by a small set of C/C++ interface routine listed below. </p> <p><ul> <li> [sqlite3_open()] </li> <li> [sqlite3_prepare()] </li> <li> [sqlite3_step()] </li> <li> [sqlite3_column_int | sqlite3_column()] </li> <li> [sqlite3_finalize()] </li> <li> [sqlite3_close()] </li> </ul></p> <p> The six C/C++ interface routines and two objects listed above form the core functionality of SQLite. The developer who understands what these APIs and objects do 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. The "sqlite3_column()" shown in the list is place holders for an entire families of routines to be used for extracting column data in various datatypes. </p> <p> Here is a summary of what the core interfaces do: </p> <table border="0" cellspacing="15"> <tr><td valign="top" align="right">[sqlite3_open()]</td> <td valign="top"> This routine opens a connection to an SQLite database file and return a [database connection] object. This is often the first SQLite API call that an application makes and is a prerequisite for most other SQLite APIs. </td> <tr><td valign="top" align="right">[sqlite3_prepare()]</td> <td valign="top"> This routine converts SQL text into a [prepared statement] object and return 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 is evaluated up to the point where the first row of results are available. To advance to the second row of results, invoke [sqlite3_step()] again. Continue invoking [sqlite3_step()] until the statement is complete. Statements that do not return results (ex: INSERT, UPDATE, or DELETE statements) run to completion on a single call to [sqlite3_step()]. </td> <tr><td valign="top" align="right">[sqlite3_column_int | sqlite3_column()]</td> <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()" |
︙ | ︙ | |||
191 192 193 194 195 196 197 198 199 | <li> [sqlite3_column_int64()] </li> <li> [sqlite3_column_text()] </li> <li> [sqlite3_column_text16()] </li> <li> [sqlite3_column_type()] </li> <li> [sqlite3_column_value()] </li> </ul></p> </td> </table> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | <li> [sqlite3_column_int64()] </li> <li> [sqlite3_column_text()] </li> <li> [sqlite3_column_text16()] </li> <li> [sqlite3_column_type()] </li> <li> [sqlite3_column_value()] </li> </ul></p> </td> <tr><td valign="top" align="right">[sqlite3_finalize()]</td> <td valign="top"> This routine destroys a [prepared statement] created by a prior call to [sqlite3_prepare()]. Every prepared statement must be destroyed using a call to this routine in order to avoid memory leaks. </td> <tr><td valign="top" align="right">[sqlite3_close()]</td> <td valign="top"> This routine closes a [database connection] previously opened by a call to [sqlite3_open()]. All [prepared statements] associated with the connection should be [sqlite3_finalize | finalized] prior to closing the connection. </td> </table> <tcl>HEADING 2 {Typical Usage Of Core Routines And Objects}</tcl> <p> An application that wants to use SQLite will typically use [sqlite3_open()] to create a single [database connection] during initialization. Note that [sqlite3_open()] can be used to either open existing database files or to create and open new database files. While many applications use only a single [database connection], there is no reason why an application cannot call [sqlite3_open()] multiple times in order to open multiple [database connections] - either to the same database or to different databases. Sometimes a multi-threaded application will create separate [database connections] for each threads. Note too that is not necessary to open separate database connections in order to access two or more databases. A single [database connection] can be made to access two or more databases at one time using the [ATTACH] SQL command. </p> <p> Many applications destroy their [database connections] using calls to [sqlite3_close()] at shutdown. Or, for example, an application might open [database connections] in response to a File->Open menu action and then destroy the corresponding [database connection] in response to the File->Close menu. </p> <p> To run an SQL statement, the application follows these steps: </p> <p><ol> <li> Create a [prepared statement] using [sqlite3_prepare()]. </li> <li> Evaluate the [prepared statement] by calling [sqlite3_step()] one or more times. </li> <li> For queries, extract results by calling [sqlite3_column_int | sqlite3_column()] in between two calls to [sqlite3_step()]. </li> <li> Destroy the [prepared statement] using [sqlite3_finalize()]. </li> </ol></p> <p> The foregoing is all one really needs to know in order to use SQLite effectively. All the rest is just ornimentation and detail. </p> <tcl>HEADING 1 {Convenience Wrappers Around Core Routines}</tcl> <p> The [sqlite3_exec()] interface is a convenience wrapper that carries out all four of the above steps with a single function call. A callback function passed into [sqlite3_exec()] is used to process each row of the result set. The [sqlite3_get_table()] is another convenience wrapper that does all four of the above steps. The [sqlite3_get_table()] interface differs from [sqlite3_get_table()] in that it stores the results of queries in help memory rather than invoking a callback. </p> <p> It is important to realize that neither [sqlite3_exec()] nor [sqlite3_get_table()] do anything that cannot be accomplished using the core routines. In fact, these wrappers are implemented purely in terms of the core routines. </p> <tcl>HEADING 1 {Binding Parameters and Reusing Prepared Statements}</tcl> <p> In prior discussion, it was assumed that each SQL statement is prepared onces, evaluated, then destroyed. However, the SQLite allows the same [prepared statement] to evaluated multiple times. These is accomplished using the following routines: </p> <p><ul> <li> [sqlite3_reset()] </li> <li> [sqlite3_bind_int | sqlite3_bind()] </li> </ul></p> <p> After a [prepared statement] has been evaluated by one or more calls to [sqlite3_step()], it can be reset in order to be evaluted again by a single call to [sqlite3_reset()]. Using [sqlite3_reset()] on an existing [prepared statement] rather creating a new [prepared statement] avoids unnecessary calls to [sqlite3_prepare()]. In many SQL statements, the time needed to run [sqlite3_prepare()] equals or exceeds the time needed by [sqlite3_step()]. So avoiding calls to [sqlite3_prepare()] can result in a significant performance improvement. </p> <p> Usually, though, it is not useful to evaluate exactly the same SQL statement more than once. More often, one wants to evalute similar statements. For example, you might want to evaluate an INSERT statement multiple times though with different values to insert. To accommodate this kind of flexibility, SQLite allows SQL statements to contain parameters which are "bound" to values prior to being evaluated. These values can later be changed and the same [prepared statement] can be evaluated a second time using the new values. </p> <p> In SQLite, whereever it is valid to include a string literal, one can use a parameter in one of the following forms: </p> <p><ul> <li> <b>?</b> </li> <li> <b>?</b><i>NNN</i> </li> <li> <b>:</b><i>AAA</i> </li> <li> <b>$</b><i>AAA</i> </li> <li> <b>@</b><i>AAA</i> </li> </ul></p> <p> In the examples above, <i>NNN</i> is an integer value and <i>AAA</i> is an identifier. A parameter initially has a value of NULL. Prior to calling [sqlite3_step()] for the first time or immediately after [sqlite3_reset()], the application can invoke one of the [sqlite3_bind_int | sqlite3_bind()] interfaces to attach values to the parameters. Each call to [sqlite3_bind_int | sqlite3_bind()] overrides prior bindings on the same parameter. </p> <p> An application is allows 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 interface 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 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 make 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 program interface is found at the [capi3ref | C/C++ Interface Specification]. Refer to that document for complete and authoritative information about all SQLite interfaces. </p> |