Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Documentation updates in preparation for the release of version 3.0.0. (CVS 1613) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9fb29f7331254b601b71f0fe7d77f919 |
User & Date: | drh 2004-06-17 19:04:17.000 |
Context
2004-06-18
| ||
04:24 | Optimisation for unicode encoding conversion routines. (CVS 1614) (check-in: 39a415eaa6 user: danielk1977 tags: trunk) | |
2004-06-17
| ||
19:04 | Documentation updates in preparation for the release of version 3.0.0. (CVS 1613) (check-in: 9fb29f7331 user: drh tags: trunk) | |
07:53 | Remove redundant opcodes OP_MakeKey and OP_MakeIdxKey. (CVS 1612) (check-in: a71a9ff114 user: danielk1977 tags: trunk) | |
Changes
Changes to main.mk.
︙ | ︙ | |||
454 455 456 457 458 459 460 461 462 463 464 465 466 467 | tclsqlite.html: $(TOP)/www/tclsqlite.tcl tclsh $(TOP)/www/tclsqlite.tcl >tclsqlite.html vdbe.html: $(TOP)/www/vdbe.tcl tclsh $(TOP)/www/vdbe.tcl >vdbe.html # Files to be published on the website. # DOC = \ arch.html \ arch.png \ c_interface.html \ | > > > | 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 | tclsqlite.html: $(TOP)/www/tclsqlite.tcl tclsh $(TOP)/www/tclsqlite.tcl >tclsqlite.html vdbe.html: $(TOP)/www/vdbe.tcl tclsh $(TOP)/www/vdbe.tcl >vdbe.html version3.html: $(TOP)/www/version3.tcl tclsh $(TOP)/www/version3.tcl >version3.html # Files to be published on the website. # DOC = \ arch.html \ arch.png \ c_interface.html \ |
︙ | ︙ | |||
489 490 491 492 493 494 495 | opcode.html \ quickstart.html \ speed.html \ sqlite.gif \ sqlite.html \ support.html \ tclsqlite.html \ | | > | 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 | opcode.html \ quickstart.html \ speed.html \ sqlite.gif \ sqlite.html \ support.html \ tclsqlite.html \ vdbe.html \ version3.html doc: common.tcl $(DOC) mkdir -p doc mv $(DOC) doc # Standard install and cleanup targets # |
︙ | ︙ |
Changes to src/os_unix.c.
︙ | ︙ | |||
603 604 605 606 607 608 609 610 611 | ** SQLite to access the file will not know that the journal exists (because ** the directory entry for the journal was never created) and the transaction ** will not roll back - possibly leading to database corruption. */ int sqlite3OsSync(OsFile *id){ SimulateIOError(SQLITE_IOERR); TRACE2("SYNC %-3d\n", id->h); if( fsync(id->h) ){ return SQLITE_IOERR; | > > > > > < > | | | | | | > | < | 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 | ** SQLite to access the file will not know that the journal exists (because ** the directory entry for the journal was never created) and the transaction ** will not roll back - possibly leading to database corruption. */ int sqlite3OsSync(OsFile *id){ SimulateIOError(SQLITE_IOERR); TRACE2("SYNC %-3d\n", id->h); { off_t sz; sqlite3OsFileSize(id, &sz); fprintf(stderr,"SYNC %d size=%lld... ", id->h, sz); } if( fsync(id->h) ){ return SQLITE_IOERR; } if( id->dirfd>=0 ){ TRACE2("DIRSYNC %-3d\n", id->dirfd); fsync(id->dirfd); close(id->dirfd); /* Only need to sync once, so close the directory */ id->dirfd = -1; /* when we are done. */ } fprintf(stderr,"DONE\n"); return SQLITE_OK; } /* ** Sync the directory zDirname. This is a no-op on operating systems other ** than UNIX. */ int sqlite3OsSyncDirectory(const char *zDirname){ |
︙ | ︙ |
Changes to src/pragma.c.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2003 April 6 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains code used to implement the PRAGMA command. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2003 April 6 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains code used to implement the PRAGMA command. ** ** $Id: pragma.c,v 1.46 2004/06/17 19:04:17 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> #ifdef SQLITE_DEBUG # include "pager.h" # include "btree.h" |
︙ | ︙ | |||
120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 | static const struct { const char *zName; /* Name of the pragma */ int mask; /* Mask for the db->flags value */ } aPragma[] = { { "vdbe_trace", SQLITE_VdbeTrace }, { "sql_trace", SQLITE_SqlTrace }, { "vdbe_listing", SQLITE_VdbeListing }, { "full_column_names", SQLITE_FullColNames }, { "short_column_names", SQLITE_ShortColNames }, { "count_changes", SQLITE_CountRows }, { "empty_result_callbacks", SQLITE_NullCallback }, }; int i; for(i=0; i<sizeof(aPragma)/sizeof(aPragma[0]); i++){ if( sqlite3StrICmp(zLeft, aPragma[i].zName)==0 ){ sqlite *db = pParse->db; Vdbe *v; if( strcmp(zLeft,zRight)==0 && (v = sqlite3GetVdbe(pParse))!=0 ){ | > > | 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | static const struct { const char *zName; /* Name of the pragma */ int mask; /* Mask for the db->flags value */ } aPragma[] = { { "vdbe_trace", SQLITE_VdbeTrace }, { "sql_trace", SQLITE_SqlTrace }, { "vdbe_listing", SQLITE_VdbeListing }, #if 1 /* FIX ME: Remove the following pragmas */ { "full_column_names", SQLITE_FullColNames }, { "short_column_names", SQLITE_ShortColNames }, { "count_changes", SQLITE_CountRows }, { "empty_result_callbacks", SQLITE_NullCallback }, #endif }; int i; for(i=0; i<sizeof(aPragma)/sizeof(aPragma[0]); i++){ if( sqlite3StrICmp(zLeft, aPragma[i].zName)==0 ){ sqlite *db = pParse->db; Vdbe *v; if( strcmp(zLeft,zRight)==0 && (v = sqlite3GetVdbe(pParse))!=0 ){ |
︙ | ︙ |
Changes to src/sqlite.h.in.
︙ | ︙ | |||
8 9 10 11 12 13 14 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This header file defines the interface that the SQLite library ** presents to client programs. ** | | | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This header file defines the interface that the SQLite library ** presents to client programs. ** ** @(#) $Id: sqlite.h.in,v 1.102 2004/06/17 19:04:17 drh Exp $ */ #ifndef _SQLITE_H_ #define _SQLITE_H_ #include <stdarg.h> /* Needed for the definition of va_list */ /* ** Make sure we can call this stuff from C++. |
︙ | ︙ | |||
946 947 948 949 950 951 952 953 954 955 956 957 958 959 | ** In practice, meta-data is preserved between function calls for ** expressions that are constant at compile time. This includes literal ** values and SQL variables. */ void *sqlite3_get_auxdata(sqlite3_context*, int); void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*)); #define SQLITE_STATIC ((void(*)(void *))0) #define SQLITE_TRANSIENT ((void(*)(void *))-1) /* ** User-defined functions invoke the following routines in order to ** set their return value. */ | > > > > > > > > > > | 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 | ** In practice, meta-data is preserved between function calls for ** expressions that are constant at compile time. This includes literal ** values and SQL variables. */ void *sqlite3_get_auxdata(sqlite3_context*, int); void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*)); /* ** These are special value for the destructor that is passed in as the ** final argument to routines like sqlite3_result_blob(). If the destructor ** argument is SQLITE_STATIC, it means that the content pointer is constant ** and will never change. It does not need to be destroyed. The ** SQLITE_TRANSIENT value means that the content will likely change in ** the near future and that SQLite should make its own private copy of ** the content before returning. */ #define SQLITE_STATIC ((void(*)(void *))0) #define SQLITE_TRANSIENT ((void(*)(void *))-1) /* ** User-defined functions invoke the following routines in order to ** set their return value. */ |
︙ | ︙ |
Changes to www/capi3.tcl.
|
| | | 1 2 3 4 5 6 7 8 | set rcsid {$Id: capi3.tcl,v 1.4 2004/06/17 19:04:17 drh Exp $} source common.tcl header {C/C++ Interface For SQLite Version 3} puts { <h2>C/C++ Interface For SQLite Version 3</h2> <h3>1.0 Overview</h3> |
︙ | ︙ | |||
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 | <p> It became necessary to move to version 3.0 to implement these features because each requires incompatible changes to the database file format. Other incompatible changes, such as a cleanup of the API, were introduced at the same time under the theory that it is best to get your incompatible changes out of the way all at once. <p> The API for version 3.0 is similar to the version 2.X API, but with some important changes. Most noticeably, the "<tt>sqlite_</tt>" prefix that occurs on the beginning of all API functions and data structures will be changed to "<tt>sqlite3_</tt>". This will avoid confusion between the two APIs and allow linking against both SQLite 2.X and SQLite 3.0 at the same time, if desired. </p> <p> There is no agreement on what the C datatype for a UTF-16 string should be. Therefore, SQLite uses a generic type of void* to refer to UTF-16 strings. Client software can cast the void* to whatever datatype is appropriate for their system. </p> <h3>2.0 C/C++ Interface</h3> <h4>2.1 Opening and closing a database</h4> <blockquote><pre> typedef struct sqlite3 sqlite3; | > > > > > > > > > > > > > > > > > > > > > > > > > > | | | > | | | | < < | 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 | <p> It became necessary to move to version 3.0 to implement these features because each requires incompatible changes to the database file format. Other incompatible changes, such as a cleanup of the API, were introduced at the same time under the theory that it is best to get your incompatible changes out of the way all at once. </p> <p> The API for version 3.0 is similar to the version 2.X API, but with some important changes. Most noticeably, the "<tt>sqlite_</tt>" prefix that occurs on the beginning of all API functions and data structures will be changed to "<tt>sqlite3_</tt>". This will avoid confusion between the two APIs and allow linking against both SQLite 2.X and SQLite 3.0 at the same time, if desired. </p> <p> There is no agreement on what the C datatype for a UTF-16 string should be. Therefore, SQLite uses a generic type of void* to refer to UTF-16 strings. Client software can cast the void* to whatever datatype is appropriate for their system. </p> <h3>2.0 C/C++ Interface</h3> <p> The API for SQLite 3.0 three includes 83 separate functions in addition to several data structures and #defines. (A complete <a href="capi3ref.html">API reference</a> is provided as a separate document.) Fortunately, the interface is not nearly as complex as its size implies. Simple programs can still make due with only 3 functions: <a href="capi3ref.html#sqlite3_open">sqlite3_open()</a>, <a href="capi3ref.html#sqlite3_exec">sqlite3_exec()</a>, and <a href="capi3ref.html#sqlite3_close">sqlite3_close()</a>. More control over the execution of the database engine is provided using <a href="capi3ref.html#sqlite3_prepare">sqlite3_prepare()</a> to compile an SQLite statement into byte code and <a href="capi3ref.html#sqlite3_prepare">sqlite3_step()</a> to execute that bytecode. A family of routines with names beginning with <a href="capi3ref.html#sqlite3_column_blob">sqlite3_column_</a> is used to extract informatiom about the result set of a query. Many interface functions come in pairs, with both a UTF-8 and and UTF-16 version. And there is a collection of routines used to implement user-defined SQL functions and user-defined text collating sequences. </p> <h4>2.1 Opening and closing a database</h4> <blockquote><pre> typedef struct sqlite3 sqlite3; int sqlite3_open(const char*, sqlite3**); int sqlite3_open16(const void*, sqlite3**); int sqlite3_close(sqlite3*); const char *sqlite3_errmsg(sqlite3*); const void *sqlite3_errmsg16(sqlite3*); int sqlite3_errcode(sqlite3*); </pre></blockquote> <p> The sqlite3_open() routine returns an integer error code rather than a pointer to the sqlite3 structure as the version 2 interface did. The difference between sqlite3_open() and sqlite3_open16() is that sqlite3_open16() takes UTF-16 (in host native byte order) for the name of the database file. If a new database file needs to be created, then sqlite3_open16() will set the internal text representation to UTF-16 whereas sqlite3_open() will set the text representation to UTF-8. </p> <p> The opening and/or creating of the database file is deferred until the file is actually needed. This allows options and parameters, such as the native text representation and default page size, to be set using PRAGMA statements. </p> <p> The sqlite3_errcode() routine will return the result code for the most recent major API call. sqlite3_errmsg() will return an English-language text error message for the most recent error. The error message will be represented in UTF-8 and will be ephemeral - it could disappear on |
︙ | ︙ | |||
120 121 122 123 124 125 126 127 128 129 | #define SQLITE_NOLFS 22 /* Uses OS features not supported on host */ #define SQLITE_AUTH 23 /* Authorization denied */ #define SQLITE_ROW 100 /* sqlite_step() has another row ready */ #define SQLITE_DONE 101 /* sqlite_step() has finished executing */ </pre></blockquote> <h4>2.2 Executing SQL statements</h4> <blockquote><pre> typedef struct sqlite3_stmt sqlite3_stmt; | > > > > > > > > > > > > > > > > > > | | > > | > | > > > > > > > > > | > > < | | > < < < < < < < < < < < < | | | | 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 | #define SQLITE_NOLFS 22 /* Uses OS features not supported on host */ #define SQLITE_AUTH 23 /* Authorization denied */ #define SQLITE_ROW 100 /* sqlite_step() has another row ready */ #define SQLITE_DONE 101 /* sqlite_step() has finished executing */ </pre></blockquote> <h4>2.2 Executing SQL statements</h4> <blockquote><pre> typedef int (*sqlite_callback)(void*,int,char**, char**); int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**); </pre></blockquote> <p> The sqlite3_exec function works much as it did in SQLite version 2. Zero or more SQL statements specified in the second parameter are compiled and executed. Query results are returned to a callback routine. See the <a href="capi3ref.html#sqlite3_exec">API reference</a> for additional information. </p> <p> In SQLite version 3, the sqlite3_exec routine is just a wrapper around calls to the prepared statement interface. </p> <blockquote><pre> typedef struct sqlite3_stmt sqlite3_stmt; int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**); int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**); int sqlite3_finalize(sqlite3_stmt*); int sqlite3_reset(sqlite3_stmt*); </pre></blockquote> <p> The sqlite3_prepare interface compiles a single SQL statement into byte code for later execution. This interface is now the preferred way of accessing the database. </p> <p> The SQL statement is a UTF-8 string for sqlite3_prepare(). The sqlite3_prepare16() works the same way except that it expects a UTF-16 string as SQL input. Only the first SQL statement in the input string is compiled. The fourth parameter is filled in with a pointer to the next (uncompiled) SQLite statement in the input string, if any. The sqlite3_finalize() routine deallocates a prepared SQL statement. All prepared statements must be finalized before the database can be closed. The sqlite3_reset() routine resets a prepared SQL statement so that it can be executed again. </p> <p> The SQL statement may contain tokens of the form "?" or "?nnn" or ":nnn:" where "nnn" is an integer. Such tokens represent unspecified literal values (or wildcards) to be filled in later by the <a href="capi3ref.html#sqlite3_bind_blob">sqlite3_bind</a> interface. Each wildcard as an associated number given by the "nnn" that follows the "?". If the "?" is not followed by an integer, then its number one more than the number of prior wildcards in the same SQL statement. It is allowed for the same wildcard to occur more than once in the same SQL statement, in which case all instance of that wildcard will be filled in with the same value. Unbound wildcards have a value of NULL. </p> <blockquote><pre> int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, long long int); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*); </pre></blockquote> <p> There is an assortment of sqlite3_bind routines used to assign values to wildcards in a prepared SQL statement. Unbound wildcards are interpreted as NULLs. Bindings are not reset by sqlite3_reset(). |
︙ | ︙ | |||
198 199 200 201 202 203 204 | normally or due to an error. It might also return SQLITE3_BUSY if it is unable to open the database file. If the return value is SQLITE3_ROW, then the following routines can be used to extract information about that row of the result set: </p> <blockquote><pre> | < < < < < < > > > > > > > | > > > > > > > > > > > | | 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 | normally or due to an error. It might also return SQLITE3_BUSY if it is unable to open the database file. If the return value is SQLITE3_ROW, then the following routines can be used to extract information about that row of the result set: </p> <blockquote><pre> const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); int sqlite3_column_count(sqlite3_stmt*); const char *sqlite3_column_decltype(sqlite3_stmt *, int iCol); const void *sqlite3_column_decltype16(sqlite3_stmt *, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); long long int sqlite3_column_int64(sqlite3_stmt*, int iCol); const char *sqlite3_column_name(sqlite3_stmt*, int iCol); const void *sqlite3_column_name16(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol); </pre></blockquote> <p> The <a href="capi3ref.html#sqlite3_column_count">sqlite3_column_count()</a> function returns the number of columns in the results set. sqlite3_column_count() can be called at any time after sqlite3_prepare(). <a href="capi3ref.html#sqlite3_data_count">sqlite3_data_count()</a> works similarly to sqlite3_column_count() except that it only works following sqlite3_step(). If the previous call to sqlite3_step() returned SQLITE_DONE or an error code, then sqlite3_data_count() will return 0 whereas sqlite3_column_count() will continue to return the number of columns in the result set. </p> <p> The sqlite3_column_type() function returns the datatype for the value in the Nth column. The return value is one of these: </p> <blockquote><pre> #define SQLITE_INTEGER 1 #define SQLITE_FLOAT 2 |
︙ | ︙ | |||
248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 | sqlite3_column_text16() return TEXT data as UTF-16. sqlite3_column_int() return INTEGER data in the host machines native integer format. sqlite3_column_int64() returns 64-bit INTEGER data. Finally, sqlite3_column_double() return floating point data. </p> <h4>2.3 User-defined functions</h4> <p> User defined functions can be created using the following routine: </p> <blockquote><pre> typedef struct sqlite3_value sqlite3_value; int sqlite3_create_function( sqlite3 *, const char *zFunctionName, int nArg, int eTextRep, | > > > > > > < < | > | < < < | 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 | sqlite3_column_text16() return TEXT data as UTF-16. sqlite3_column_int() return INTEGER data in the host machines native integer format. sqlite3_column_int64() returns 64-bit INTEGER data. Finally, sqlite3_column_double() return floating point data. </p> <p> It is not necessary to retrieve data in the format specify by sqlite3_column_type(). If a different format is requested, the data is converted automatically. </p> <h4>2.3 User-defined functions</h4> <p> User defined functions can be created using the following routine: </p> <blockquote><pre> typedef struct sqlite3_value sqlite3_value; int sqlite3_create_function( sqlite3 *, const char *zFunctionName, int nArg, int eTextRep, void*, void (*xFunc)(sqlite3_context*,int,sqlite3_value**), void (*xStep)(sqlite3_context*,int,sqlite3_value**), void (*xFinal)(sqlite3_context*) ); int sqlite3_create_function16( sqlite3*, const void *zFunctionName, int nArg, int eTextRep, void*, void (*xFunc)(sqlite3_context*,int,sqlite3_value**), void (*xStep)(sqlite3_context*,int,sqlite3_value**), void (*xFinal)(sqlite3_context*) ); #define SQLITE3_UTF8 1 #define SQLITE3_UTF16 2 #define SQLITE3_UTF16BE 3 #define SQLITE3_UTF16LE 4 #define SQLITE3_ANY 5 </pre></blockquote> <p> The nArg parameter specifies the number of arguments to the function. A value of 0 indicates that any number of arguments is allowed. The eTextRep parameter specifies what representation text values are expected to be in for arguments to this function. The value of this parameter should be one of the parameters defined above. SQLite version 3 allows multiple implementations of the same function using different text representations. The database engine chooses the function that minimization the number of text conversions required. </p> <p> Normal functions specify only xFunc and leave xStep and xFinal set to NULL. Aggregate functions specify xStep and xFinal and leave xFunc set to NULL. There is no separate sqlite3_create_aggregate() API. </p> |
︙ | ︙ | |||
337 338 339 340 341 342 343 | Function implementations use the following APIs to acquire context and to report results: </p> <blockquote><pre> void *sqlite3_aggregate_context(sqlite3_context*, int nbyte); void *sqlite3_user_data(sqlite3_context*); | | | | | 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 | Function implementations use the following APIs to acquire context and to report results: </p> <blockquote><pre> void *sqlite3_aggregate_context(sqlite3_context*, int nbyte); void *sqlite3_user_data(sqlite3_context*); void sqlite3_result_blob(sqlite3_context*, const void*, int n, void(*)(void*)); void sqlite3_result_double(sqlite3_context*, double); void sqlite3_result_error(sqlite3_context*, const char*, int); void sqlite3_result_error16(sqlite3_context*, const void*, int); void sqlite3_result_int(sqlite3_context*, int); void sqlite3_result_int64(sqlite3_context*, long long int); void sqlite3_result_null(sqlite3_context*); void sqlite3_result_text(sqlite3_context*, const char*, int n, void(*)(void*)); void sqlite3_result_text16(sqlite3_context*, const void*, int n, void(*)(void*)); void sqlite3_result_value(sqlite3_context*, sqlite3_value*); void *sqlite3_get_auxdata(sqlite3_context*, int); void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*)); </pre></blockquote> <h4>2.4 User-defined collating sequences</h4> |
︙ | ︙ |
Changes to www/capi3ref.tcl.
|
| | | 1 2 3 4 5 6 7 8 | set rcsid {$Id: capi3ref.tcl,v 1.4 2004/06/17 19:04:17 drh Exp $} source common.tcl header {C/C++ Interface For SQLite Version 3} puts { <h2>C/C++ Interface For SQLite Version 3</h2> } proc api {name prototype desc {notused x}} { |
︙ | ︙ | |||
39 40 41 42 43 44 45 | #define SQLITE_MISMATCH 20 /* Data type mismatch */ #define SQLITE_MISUSE 21 /* Library used incorrectly */ #define SQLITE_NOLFS 22 /* Uses OS features not supported on host */ #define SQLITE_AUTH 23 /* Authorization denied */ #define SQLITE_ROW 100 /* sqlite_step() has another row ready */ #define SQLITE_DONE 101 /* sqlite_step() has finished executing */ } { | | | | 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | #define SQLITE_MISMATCH 20 /* Data type mismatch */ #define SQLITE_MISUSE 21 /* Library used incorrectly */ #define SQLITE_NOLFS 22 /* Uses OS features not supported on host */ #define SQLITE_AUTH 23 /* Authorization denied */ #define SQLITE_ROW 100 /* sqlite_step() has another row ready */ #define SQLITE_DONE 101 /* sqlite_step() has finished executing */ } { Many SQLite functions return an integer result code from the set shown above in order to indicates success or failure. } api {} { void *sqlite3_aggregate_context(sqlite3_context*, int nBytes); } { Aggregate functions use the following routine to allocate a structure for storing their state. The first time this routine |
︙ | ︙ | |||
72 73 74 75 76 77 78 | int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, long long int); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); | | | | | | 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 | int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, long long int); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); #define SQLITE_STATIC ((void(*)(void *))0) #define SQLITE_TRANSIENT ((void(*)(void *))-1) } { In the SQL strings input to sqlite3_prepare() and sqlite3_prepare16(), one or more literals can be replace by a wildcard "?" or ":N:" where N is an integer. The value of these wildcard literals can be set using these routines. The first parameter is a pointer to the sqlite3_stmt structure returned from sqlite3_prepare(). The second parameter is the index of the wildcard. The first "?" has an index of 1. ":N:" wildcards use the index N. The fifth parameter to sqlite3_bind_blob(), sqlite3_bind_text(), and sqlite3_bind_text16() is a destructor used to dispose of the BLOB or text after SQLite has finished with it. If the fifth argument is the special value SQLITE_STATIC, then the library assumes that the information is in static, unmanaged space and does not need to be freed. If the fifth argument has the value SQLITE_TRANSIENT, then SQLite makes its on private copy of the data. The sqlite3_bind_*() routine must be called after sqlite3_prepare() or sqlite3_reset() and before sqlite3_step(). Bindings are not reset by the sqlite3_reset() routine. Unbound wildcards are interpreted as NULL. } api {} { int sqlite3_busy_handler(sqlite*, int(*)(void*,int), void*); } { This routine identifies a callback function that is invoked whenever an attempt is made to open a database table that is currently locked by another process or thread. If the busy callback is NULL, then sqlite3_exec() returns SQLITE_BUSY immediately if it finds a locked table. If the busy callback is not NULL, then sqlite3_exec() invokes the callback with two arguments. The |
︙ | ︙ | |||
125 126 127 128 129 130 131 | is allowed, in theory.) But the busy handler may not close the database. Closing the database from a busy handler will delete data structures out from under the executing query and will probably result in a coredump. } api {} { | | | | | | | | > | < > | > > > > > > > > > > > > > > > > > > > > > > | | | | > > > > > > | 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 | is allowed, in theory.) But the busy handler may not close the database. Closing the database from a busy handler will delete data structures out from under the executing query and will probably result in a coredump. } api {} { int sqlite3_busy_timeout(sqlite*, int ms); } { This routine sets a busy handler that sleeps for a while when a table is locked. The handler will sleep multiple times until at least "ms" milleseconds of sleeping have been done. After "ms" milleseconds of sleeping, the handler returns 0 which causes sqlite3_exec() to return SQLITE_BUSY. Calling this routine with an argument less than or equal to zero turns off all busy handlers. } api {} { int sqlite3_changes(sqlite*); } { This function returns the number of database rows that were changed (or inserted or deleted) by the most recently completed INSERT, UPDATE, or DELETE statement. Only changes that are directly specified by the INSERT, UPDATE, or DELETE statement are counted. Auxiliary changes caused by triggers are not counted. Use the sqlite3_total_changes() function to find the total number of changes including changes caused by triggers. Within the body of a trigger, the sqlite3_changes() function does work to report the number of rows that were changed for the most recently completed INSERT, UPDATE, or DELETE statement within the trigger body. SQLite implements the command "DELETE FROM table" without a WHERE clause by dropping and recreating the table. (This is much faster than going through and deleting individual elements form the table.) Because of this optimization, the change count for "DELETE FROM table" will be zero regardless of the number of elements that were originally in the table. To get an accurate count of the number of rows deleted, use "DELETE FROM table WHERE 1" instead. } api {} { int sqlite3_total_changes(sqlite*); } { This function returns the total number of database rows that have be modified, inserted, or deleted since the database connection was created using sqlite3_open(). All changes are counted, including changes by triggers and changes to TEMP and auxiliary databases. Except, changes to the SQLITE_MASTER table (caused by statements such as CREATE TABLE) are not counted. Nor are changes counted when an entire table is deleted using DROP TABLE. See also the sqlite3_changes() API. SQLite implements the command "DELETE FROM table" without a WHERE clause by dropping and recreating the table. (This is much faster than going through and deleting individual elements form the table.) Because of this optimization, the change count for "DELETE FROM table" will be zero regardless of the number of elements that were originally in the table. To get an accurate count of the number of rows deleted, use "DELETE FROM table WHERE 1" instead. } api {} { int sqlite3_close(sqlite *); } { Call this function with a pointer to a structure that was previously returned from sqlite3_open() or sqlite3_open16() and the corresponding database will by closed. SQLITE_OK is returned if the close is successful. If there are prepared statements that have not been finalized, then SQLITE_BUSY is returned. SQLITE_ERROR might be returned if the argument is not a valid connection pointer returned by sqlite3_open() or if the connection pointer has been closed previously. } api {} { const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); |
︙ | ︙ | |||
225 226 227 228 229 230 231 | <tr><td> BLOB </td><td> TEXT </td><td> Add a \000 terminator if needed</td></tr> </table> } api {} { int sqlite3_column_count(sqlite3_stmt *pStmt); } { | | > > | | 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 | <tr><td> BLOB </td><td> TEXT </td><td> Add a \000 terminator if needed</td></tr> </table> } api {} { int sqlite3_column_count(sqlite3_stmt *pStmt); } { Return the number of columns in the result set returned by the prepared SQL statement. This routine returns 0 if pStmt is an SQL statement that does not return data (for example an UPDATE). See also sqlite3_data_count(). } api {} { const char *sqlite3_column_decltype(sqlite3_stmt *, int i); const void *sqlite3_column_decltype16(sqlite3_stmt*,int); } { The first parameter is a prepared SQL statement. If this statement is a SELECT statement, the Nth column of the returned result set of the SELECT is a table column then the declared type of the table column is returned. If the Nth column of the result set is not at table column, then a NULL pointer is returned. The returned string is UTF-8 encoded for sqlite3_column_decltype() and UTF-16 encoded for sqlite3_column_decltype16(). For example, in the database schema: |
︙ | ︙ | |||
262 263 264 265 266 267 268 | (i==0). } api {} { const char *sqlite3_column_name(sqlite3_stmt*,int); const void *sqlite3_column_name16(sqlite3_stmt*,int); } { | | | | < < < < | | | | < < < > | | < < | | < < < | 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 | (i==0). } api {} { const char *sqlite3_column_name(sqlite3_stmt*,int); const void *sqlite3_column_name16(sqlite3_stmt*,int); } { The first parameter is a prepared SQL statement. This function returns the column heading for the Nth column of that statement, where N is the second function parameter. The string returned is UTF-8 for sqlite3_column_name() and UTF-16 for sqlite3_column_name16(). } api {} { void *sqlite3_commit_hook(sqlite*, int(*xCallback)(void*), void *pArg); } { <i>Experimental</i> Register a callback function to be invoked whenever a new transaction is committed. The pArg argument is passed through to the callback. callback. If the callback function returns non-zero, then the commit is converted into a rollback. If another function was previously registered, its pArg value is returned. Otherwise NULL is returned. Registering a NULL function disables the callback. Only a single commit hook callback can be registered at a time. } api {} { int sqlite3_complete(const char *sql); int sqlite3_complete16(const void *sql); } { These functions return true if the given input string comprises |
︙ | ︙ | |||
341 342 343 344 345 346 347 | for sqlite3_create_collation() and a UTF-16 string for sqlite3_create_collation16(). In both cases the name is passed as the second function argument. The third argument must be one of the constants SQLITE_UTF8, SQLITE_UTF16LE or SQLITE_UTF16BE, indicating that the user-supplied routine expects to be passed pointers to strings encoded using UTF-8, | | > > | 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 | for sqlite3_create_collation() and a UTF-16 string for sqlite3_create_collation16(). In both cases the name is passed as the second function argument. The third argument must be one of the constants SQLITE_UTF8, SQLITE_UTF16LE or SQLITE_UTF16BE, indicating that the user-supplied routine expects to be passed pointers to strings encoded using UTF-8, UTF-16 little-endian or UTF-16 big-endian respectively. The SQLITE_UTF16 constant indicates that text strings are expected in UTF-16 in the native byte order of the host machine. A pointer to the user supplied routine must be passed as the fifth argument. If it is NULL, this is the same as deleting the collation sequence (so that SQLite cannot call it anymore). Each time the user supplied function is invoked, it is passed a copy of the void* passed as the fourth argument to sqlite3_create_collation() or sqlite3_create_collation16() as its first parameter. |
︙ | ︙ | |||
400 401 402 403 404 405 406 | api {} { int sqlite3_create_function( sqlite3 *, const char *zFunctionName, int nArg, int eTextRep, | < < | 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 | api {} { int sqlite3_create_function( sqlite3 *, const char *zFunctionName, int nArg, int eTextRep, void*, void (*xFunc)(sqlite3_context*,int,sqlite3_value**), void (*xStep)(sqlite3_context*,int,sqlite3_value**), void (*xFinal)(sqlite3_context*) ); int sqlite3_create_function16( sqlite3*, const void *zFunctionName, int nArg, int eTextRep, void*, void (*xFunc)(sqlite3_context*,int,sqlite3_value**), void (*xStep)(sqlite3_context*,int,sqlite3_value**), void (*xFinal)(sqlite3_context*) ); #define SQLITE_UTF8 1 #define SQLITE_UTF16 2 |
︙ | ︙ | |||
439 440 441 442 443 444 445 | be added individually to each database handle with which they will be used. The third parameter is the number of arguments that the function or aggregate takes. If this parameter is negative, then the function or aggregate may take any number of arguments. | | | | 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 | be added individually to each database handle with which they will be used. The third parameter is the number of arguments that the function or aggregate takes. If this parameter is negative, then the function or aggregate may take any number of arguments. The sixth, seventh and eighth, xFunc, xStep and xFinal, are pointers to user implemented C functions that implement the user function or aggregate. A scalar function requires an implementation of the xFunc callback only, NULL pointers should be passed as the xStep and xFinal parameters. An aggregate function requires an implementation of xStep and xFinal, but NULL should be passed for xFunc. To delete an existing user function or aggregate, pass NULL for all three function callback. Specifying an inconstent set of callback values, such as an xFunc and an xFinal, or an xStep but no xFinal, SQLITE_ERROR is returned. } api {} { int sqlite3_data_count(sqlite3_stmt *pStmt); } { Return the number of values in the current row of the result set. After a call to sqlite3_step() that returns SQLITE_ROW, this routine will return the same value as the sqlite3_column_count() function. After sqlite3_step() has returned an SQLITE_DONE, SQLITE_BUSY or error code, or before sqlite3_step() has been called on a prepared SQL statement, this routine returns zero. } api {} { int sqlite3_errcode(sqlite3 *db); } { Return the error code for the most recent sqlite3_* API call associated with sqlite3 handle 'db'. SQLITE_OK is returned if the most recent |
︙ | ︙ | |||
546 547 548 549 550 551 552 | behavior can be modified somewhat using the sqlite3_busy_handler() and sqlite3_busy_timeout() functions.) } {} api {} { int sqlite3_finalize(sqlite3_stmt *pStmt); } { | | > > > | 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 | behavior can be modified somewhat using the sqlite3_busy_handler() and sqlite3_busy_timeout() functions.) } {} api {} { int sqlite3_finalize(sqlite3_stmt *pStmt); } { The sqlite3_finalize() function is called to delete a prepared SQL statement obtained by a previous call to sqlite3_prepare() or sqlite3_prepare16(). If the statement was executed successfully, or not executed at all, then SQLITE_OK is returned. If execution of the statement failed then an error code is returned. All prepared statements must finalized before sqlite3_close() is called or else the close will fail with a return code of SQLITE_BUSY. This routine can be called at any point during the execution of the virtual machine. If the virtual machine has not completed execution when this routine is called, that is like encountering an error or an interrupt. (See sqlite3_interrupt().) Incomplete updates may be rolled back and transactions cancelled, depending on the circumstances, and the result code returned will be SQLITE_ABORT. } |
︙ | ︙ | |||
633 634 635 636 637 638 639 | This function causes any pending database operation to abort and return at its earliest opportunity. This routine is typically called in response to a user action such as pressing "Cancel" or Ctrl-C where the user wants a long query operation to halt immediately. } {} | < < < < < < | 656 657 658 659 660 661 662 663 664 665 666 667 668 669 | This function causes any pending database operation to abort and return at its earliest opportunity. This routine is typically called in response to a user action such as pressing "Cancel" or Ctrl-C where the user wants a long query operation to halt immediately. } {} api {} { long long int sqlite3_last_insert_rowid(sqlite*); } { Each entry in an SQLite table has a unique integer key. (The key is the value of the INTEGER PRIMARY KEY column if there is such a column, otherwise the key is generated at random. The unique key is always available as the ROWID, OID, or _ROWID_ column.) The following routine |
︙ | ︙ | |||
806 807 808 809 810 811 812 | back and remains active. The sqlite3_exec() call returns SQLITE_ABORT. } api {} { int sqlite3_reset(sqlite3_stmt *pStmt); } { | | | 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 | back and remains active. The sqlite3_exec() call returns SQLITE_ABORT. } api {} { int sqlite3_reset(sqlite3_stmt *pStmt); } { The sqlite3_reset() function is called to reset a prepared SQL statement obtained by a previous call to sqlite3_prepare() or sqlite3_prepare16() back to it's initial state, ready to be re-executed. Any SQL statement variables that had values bound to them using the sqlite3_bind_*() API retain their values. } api {} { |
︙ | ︙ | |||
830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 | void sqlite3_result_text16be(sqlite3_context*, const void*, int n, void(*)(void*)); void sqlite3_result_text16le(sqlite3_context*, const void*, int n, void(*)(void*)); void sqlite3_result_value(sqlite3_context*, sqlite3_value*); } { User-defined functions invoke the following routines in order to set their return value. The sqlite3_result_value() routine is used to return an exact copy of one of the parameters to the function. } api {} { int sqlite3_set_authorizer( sqlite*, int (*xAuth)(void*,int,const char*,const char*,const char*,const char*), void *pUserData ); | > > > > < | 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 | void sqlite3_result_text16be(sqlite3_context*, const void*, int n, void(*)(void*)); void sqlite3_result_text16le(sqlite3_context*, const void*, int n, void(*)(void*)); void sqlite3_result_value(sqlite3_context*, sqlite3_value*); } { User-defined functions invoke the following routines in order to set their return value. The sqlite3_result_value() routine is used to return an exact copy of one of the parameters to the function. The operation of these routines is very similar to the operation of sqlite3_bind_blob() and its cousins. Refer to the documentation there for additional information. } api {} { int sqlite3_set_authorizer( sqlite*, int (*xAuth)(void*,int,const char*,const char*,const char*,const char*), void *pUserData ); #define SQLITE_CREATE_INDEX 1 /* Index Name Table Name */ #define SQLITE_CREATE_TABLE 2 /* Table Name NULL */ #define SQLITE_CREATE_TEMP_INDEX 3 /* Index Name Table Name */ #define SQLITE_CREATE_TEMP_TABLE 4 /* Table Name NULL */ #define SQLITE_CREATE_TEMP_TRIGGER 5 /* Trigger Name Table Name */ #define SQLITE_CREATE_TEMP_VIEW 6 /* View Name NULL */ #define SQLITE_CREATE_TRIGGER 7 /* Trigger Name Table Name */ |
︙ | ︙ | |||
892 893 894 895 896 897 898 | The return value of the authorization function should be one of the constants SQLITE_DENY or SQLITE_IGNORE. } api {} { int sqlite3_step(sqlite3_stmt*); } { | | | 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 | The return value of the authorization function should be one of the constants SQLITE_DENY or SQLITE_IGNORE. } api {} { int sqlite3_step(sqlite3_stmt*); } { After an SQL query has been prepared with a call to either sqlite3_prepare() or sqlite3_prepare16(), then this function must be called one or more times to execute the statement. The return value will be either SQLITE_BUSY, SQLITE_DONE, SQLITE_ROW, SQLITE_ERROR, or SQLITE_MISUSE. SQLITE_BUSY means that the database engine attempted to open |
︙ | ︙ | |||
990 991 992 993 994 995 996 | for {set j 0} {$j<3} {incr j} { if {$j>0} {puts {<td width="10"></td>}} puts {<td valign="top">} set limit [expr {$i+$nrow}] puts {<ul>} while {$i<$limit && $i<$n} { set name $sname($i) | > | > | 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 | for {set j 0} {$j<3} {incr j} { if {$j>0} {puts {<td width="10"></td>}} puts {<td valign="top">} set limit [expr {$i+$nrow}] puts {<ul>} while {$i<$limit && $i<$n} { set name $sname($i) if {[regexp {^sqlite} $name]} {set display $name} {set display <i>$name</i>} puts "<li><a href=\"#$name\">$display</a></li>" incr i } puts {</ul></td>} } puts "</table>" puts "<!-- $n entries. $nrow rows in 3 columns -->" proc resolve_name {ignore_list name} { global name_to_idx if {![info exists name_to_idx($name)] || [lsearch $ignore_list $name]>=0} { return $name } else { return "<a href=\"#$name\">$name</a>" |
︙ | ︙ |
Changes to www/datatype3.tcl.
|
| | | 1 2 3 4 5 6 7 8 | set rcsid {$Id: datatype3.tcl,v 1.6 2004/06/17 19:04:17 drh Exp $} source common.tcl header {Datatypes In SQLite Version 3} puts { <h2>Datatypes In SQLite Version 3</h2> <h3>1. Storage Classes</h3> |
︙ | ︙ | |||
305 306 307 308 309 310 311 | comparison is determined using memcmp(), regardless of the encoding of the string. SQLite v3 provides the ability for users to supply arbitrary comparison functions, known as user-defined collation sequences, to be used instead of memcmp(). </p> <p> Aside from the default collation sequence BINARY, implemented using | | | | 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 | comparison is determined using memcmp(), regardless of the encoding of the string. SQLite v3 provides the ability for users to supply arbitrary comparison functions, known as user-defined collation sequences, to be used instead of memcmp(). </p> <p> Aside from the default collation sequence BINARY, implemented using memcmp(), SQLite features two extra built-in collation sequences intended for testing purposes, NOCASE and REVERSE: </p> <UL> <LI><b>BINARY</b> - Compares string data using memcmp(), regardless of text encoding.</LI> <LI><b>REVERSE</b> - Collate in the reverse order to BINARY. </LI> <LI><b>NOCASE</b> - The same as binary, except the 26 upper case characters used by the English language are |
︙ | ︙ |
Changes to www/docs.tcl.
1 2 3 | # This script generates the "docs.html" page that describes various # sources of documentation available for SQLite. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # This script generates the "docs.html" page that describes various # sources of documentation available for SQLite. # set rcsid {$Id: docs.tcl,v 1.5 2004/06/17 19:04:17 drh Exp $} source common.tcl header {SQLite Documentation} puts { <h2>Available Documentation</h2> <table width="100%" cellpadding="5"> } |
︙ | ︙ | |||
24 25 26 27 28 29 30 31 32 33 34 35 36 37 | SQLite. } doc {Version 2 C/C++ API} {c_interface.html} { A description of the C/C++ interface bindings for SQLite through version 2.8 } doc {Version 3 C/C++ API} {capi3.html} { A description of the C/C++ interface bindings for SQLite version 3.0.0 and following. } doc {Version 3 C/C++ API<br>Reference} {capi3ref.html} { This document describes each API function separately. } | > > > | 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | SQLite. } doc {Version 2 C/C++ API} {c_interface.html} { A description of the C/C++ interface bindings for SQLite through version 2.8 } doc {SQLite Version 3} {version3.html} { A summary of of the changes between SQLite version 2.8 and SQLite version 3.0. } doc {Version 3 C/C++ API} {capi3.html} { A description of the C/C++ interface bindings for SQLite version 3.0.0 and following. } doc {Version 3 C/C++ API<br>Reference} {capi3ref.html} { This document describes each API function separately. } |
︙ | ︙ |
Changes to www/index.tcl.
︙ | ︙ | |||
56 57 58 59 60 61 62 | regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt puts "<p>$txt</p>" puts "<hr width=\"50%\">" } newsitem {2004-Jun-18} {Version 3.0.0 (alpha) Released} { The first alpha release of SQLite version 3.0 is available for | | | > | | 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 | regsub -all "\n( *\n)+" $text "</p>\n\n<p>" txt puts "<p>$txt</p>" puts "<hr width=\"50%\">" } newsitem {2004-Jun-18} {Version 3.0.0 (alpha) Released} { The first alpha release of SQLite version 3.0 is available for public review and comment. Version 3.0 enhances internationalization support through the use of UTF-16 and user-defined text collating sequences. BLOBs can now be stored directly, without encoding. A new file format results in databases that are 25% smaller (depending on content). The code is also a little faster. In spite of the many new features, the library footprint is still less than 240KB (x86, gcc -O1). <a href="version3.html">Additional information</a>. Our intent is to freeze the file format and API on 2004-Jul-01. Users are encouraged to review and evaluate this alpha release carefully and submit any feedback prior to that date. The 2.8 series of SQLite will continue to be supported with bug fixes for the foreseeable future. } newsitem {2004-Jun-09} {Version 2.8.14 Released} { SQLite version 2.8.14 is a patch release to the stable 2.8 series. There is no reason to upgrade if 2.8.13 is working ok for you. This is only a bug-fix release. Most developement effort is going into version 3.0.0 which is due out soon. } puts { <p align="right"><a href="oldnews.html">Old news...</a></p> </td></tr></table> } footer {$Id: index.tcl,v 1.88 2004/06/17 19:04:17 drh Exp $} |
Changes to www/lang.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the sqlite.html file. # set rcsid {$Id: lang.tcl,v 1.69 2004/06/17 19:04:17 drh Exp $} source common.tcl header {Query Language Understood by SQLite} puts { <h2>SQL As Understood By SQLite</h2> <p>The SQLite library understands most of the standard SQL language. But it does <a href="omitted.html">omit some features</a> |
︙ | ︙ | |||
125 126 127 128 129 130 131 | file to the current database connection. If the filename contains punctuation characters it must be quoted. The names 'main' and 'temp' refer to the main database and the database used for temporary tables. These cannot be detached. Attached databases are removed using the <a href="#detach">DETACH DATABASE</a> statement.</p> | | | > | > | > > | | | | | > > > > < < < < | | < | 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 | file to the current database connection. If the filename contains punctuation characters it must be quoted. The names 'main' and 'temp' refer to the main database and the database used for temporary tables. These cannot be detached. Attached databases are removed using the <a href="#detach">DETACH DATABASE</a> statement.</p> <p>You can read from and write to an attached database and you can modify the schema of the attached database. This is a new feature of SQLite version 3.0. In SQLite 2.8, schema changes to attached databases were not allows.</p> <p>You cannot create a new table with the same name as a table in an attached database, but you can attach a database which contains tables whose names are duplicates of tables in the main database. It is also permissible to attach the same database file multiple times.</p> <p>Tables in an attached database can be referred to using the syntax <i>database-name.table-name</i>. If an attached table doesn't have a duplicate table name in the main database, it doesn't require a database name prefix. When a database is attached, all of its tables which don't have duplicate names become the 'default' table of that name. Any tables of that name attached afterwards require the table prefix. If the 'default' table of a given name is detached, then the last table of that name attached becomes the new default.</p> <p> Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:". If the main database is ":memory:" then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not. Atomic commit of attached databases is a new feature of SQLite version 3.0. In SQLite version 2.8, all commits to attached databases behaved as if the main database were ":memory:". </p> <p>There is a compile-time limit of 10 attached database files.</p> } Section {BEGIN TRANSACTION} transaction Syntax {sql-statement} { BEGIN [TRANSACTION [<name>]] } Syntax {sql-statement} { END [TRANSACTION [<name>]] } Syntax {sql-statement} { COMMIT [TRANSACTION [<name>]] } Syntax {sql-statement} { ROLLBACK [TRANSACTION [<name>]] } puts { <p>Beginning in version 2.0, SQLite supports transactions with rollback and atomic commit. See <a href="#attach">ATTACH</a> for an exception when there are attached databases.</p> <p>The optional transaction name is ignored. SQLite currently does not allow nested transactions.</p> <p> No changes can be made to the database except within a transaction. Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed at the conclusion of the command. |
︙ | ︙ | |||
202 203 204 205 206 207 208 | and the ROLLBACK conflict resolution algorithm is specified. See the documention on the <a href="#conflict">ON CONFLICT</a> clause for additional information about the ROLLBACK conflict resolution algorithm. </p> <p> | > > > | > | < < > > > > | < | < | 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 | and the ROLLBACK conflict resolution algorithm is specified. See the documention on the <a href="#conflict">ON CONFLICT</a> clause for additional information about the ROLLBACK conflict resolution algorithm. </p> <p> The COMMIT command does not actually perform a commit until all pending SQL commands finish. Thus if two or more SELECT statements are in the middle of processing and a COMMIT is executed, the commit will not actually occur until all SELECT statements finish. </p> <p> An attempt to execute COMMIT might result in an SQLITE_BUSY return code. This indicates that another thread or process had a read lock on the database that prevented the database from being updated. When COMMIT fails in this way, the transaction remains active and the COMMIT can be retried later after the reader has had a chance to clear. </p> } Section comment comment Syntax {comment} {<SQL-comment> | <C-comment> |
︙ | ︙ | |||
247 248 249 250 251 252 253 254 255 256 257 258 259 260 | Syntax {sql-statement} { COPY [ OR <conflict-algorithm> ] [<database-name> .] <table-name> FROM <filename> [ USING DELIMITERS <delim> ] } puts { <p>The COPY command is an extension used to load large amounts of data into a table. It is modeled after a similar command found in PostgreSQL. In fact, the SQLite COPY command is specifically designed to be able to read the output of the PostgreSQL dump utility <b>pg_dump</b> so that data can be easily transferred from PostgreSQL into SQLite.</p> | > > > > > | 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 | Syntax {sql-statement} { COPY [ OR <conflict-algorithm> ] [<database-name> .] <table-name> FROM <filename> [ USING DELIMITERS <delim> ] } puts { <p>The COPY command is available in SQLite version 2.8 and earlier. The COPY command has been removed from SQLite version 3.0 due to complications in trying to support it in a mixed UTF-8/16 environment. </p> <p>The COPY command is an extension used to load large amounts of data into a table. It is modeled after a similar command found in PostgreSQL. In fact, the SQLite COPY command is specifically designed to be able to read the output of the PostgreSQL dump utility <b>pg_dump</b> so that data can be easily transferred from PostgreSQL into SQLite.</p> |
︙ | ︙ | |||
286 287 288 289 290 291 292 | Section {CREATE INDEX} createindex Syntax {sql-statement} { CREATE [UNIQUE] INDEX <index-name> ON [<database-name> .] <table-name> ( <column-name> [, <column-name>]* ) [ ON CONFLICT <conflict-algorithm> ] } {column-name} { | | > > > > > > | 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 | Section {CREATE INDEX} createindex Syntax {sql-statement} { CREATE [UNIQUE] INDEX <index-name> ON [<database-name> .] <table-name> ( <column-name> [, <column-name>]* ) [ ON CONFLICT <conflict-algorithm> ] } {column-name} { <name> [ COLLATE <collation-name>] [ ASC | DESC ] } puts { <p>The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of names of columns in the table that are used for the index key. Each column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order, but the sort order is ignored in the current implementation. Sorting is always done in ascending order.</p> <p>The COLLATE clause following each column name defines a collating sequence used for text entires in that column. The default collating sequence is the collating sequence defined for that column in the CREATE TABLE statement. Or if no collating sequence is otherwise defined, the built-in BINARY collating sequence is used.</p> <p>There are no arbitrary limits on the number of indices that can be attached to a single table, nor on the number of columns in an index.</p> <p>If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error.</p> |
︙ | ︙ | |||
349 350 351 352 353 354 355 | <typename> ( <number> ) | <typename> ( <number> , <number> ) } {column-constraint} { NOT NULL [ <conflict-clause> ] | PRIMARY KEY [<sort-order>] [ <conflict-clause> ] | UNIQUE [ <conflict-clause> ] | CHECK ( <expr> ) [ <conflict-clause> ] | | | > | 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 | <typename> ( <number> ) | <typename> ( <number> , <number> ) } {column-constraint} { NOT NULL [ <conflict-clause> ] | PRIMARY KEY [<sort-order>] [ <conflict-clause> ] | UNIQUE [ <conflict-clause> ] | CHECK ( <expr> ) [ <conflict-clause> ] | DEFAULT <value> | COLLATE <collation-name> } {constraint} { PRIMARY KEY ( <column-list> ) [ <conflict-clause> ] | UNIQUE ( <column-list> ) [ <conflict-clause> ] | CHECK ( <expr> ) [ <conflict-clause> ] } {conflict-clause} { ON CONFLICT <conflict-algorithm> } |
︙ | ︙ | |||
375 376 377 378 379 380 381 382 383 384 385 386 387 388 | The datatype for the column does not restrict what data may be put in that column. All information is stored as null-terminated strings. The UNIQUE constraint causes an index to be created on the specified columns. This index must contain unique keys. The DEFAULT constraint specifies a default value to use when doing an INSERT. </p> <p>Specifying a PRIMARY KEY normally just creates a UNIQUE index on the primary key. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, | > > > | 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 | The datatype for the column does not restrict what data may be put in that column. All information is stored as null-terminated strings. The UNIQUE constraint causes an index to be created on the specified columns. This index must contain unique keys. The DEFAULT constraint specifies a default value to use when doing an INSERT. The COLLATE clause specifies what text collating function to use when comparing text entries for the column. The built-in BINARY collating function is used by default. </p> <p>Specifying a PRIMARY KEY normally just creates a UNIQUE index on the primary key. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, |
︙ | ︙ | |||
1242 1243 1244 1245 1246 1247 1248 | improvement.</p> <p>When you change the cache size using the cache_size pragma, the change only endures for the current session. The cache size reverts to the default value when the database is closed and reopened. Use the <a href="#pragma_default_cache_size"><b>default_cache_size</b></a> pragma to check the cache size permanently.</p></li> | < < < < < < < < | 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 | improvement.</p> <p>When you change the cache size using the cache_size pragma, the change only endures for the current session. The cache size reverts to the default value when the database is closed and reopened. Use the <a href="#pragma_default_cache_size"><b>default_cache_size</b></a> pragma to check the cache size permanently.</p></li> <li><p><b>PRAGMA database_list;</b></p> <p>For each open database, invoke the callback function once with information about that database. Arguments include the index and the name the datbase was attached with. The first row will be for the main database. The second row will be for the database used to store temporary tables.</p></li> |
︙ | ︙ | |||
1332 1333 1334 1335 1336 1337 1338 | <p>This pragma changes the temp_store mode for whenever the database is opened in the future. The temp_store mode for the current session is unchanged. Use the <a href="#pragma_temp_store"><b>temp_store</b></a> pragma to change the temp_store mode for the current session.</p></li> | < < < < < < < < < < < < < < < < < < | 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 | <p>This pragma changes the temp_store mode for whenever the database is opened in the future. The temp_store mode for the current session is unchanged. Use the <a href="#pragma_temp_store"><b>temp_store</b></a> pragma to change the temp_store mode for the current session.</p></li> <li><p><b>PRAGMA foreign_key_list(</b><i>table-name</i><b>);</b></p> <p>For each foreign key that references a column in the argument table, invoke the callback function with information about that foreign key. The callback function will be invoked once for each column in each foreign key.</p></li> <li><p><b>PRAGMA index_info(</b><i>index-name</i><b>);</b></p> <p>For each column that the named index references, invoke the callback function once with information about that column, including the column name, and the column number.</p></li> <li><p><b>PRAGMA index_list(</b><i>table-name</i><b>);</b></p> |
︙ | ︙ | |||
1383 1384 1385 1386 1387 1388 1389 | <li><p><b>PRAGMA parser_trace = ON; </b>(1)<b> <br>PRAGMA parser_trace = OFF;</b> (0)</p> <p>Turn tracing of the SQL parser inside of the SQLite library on and off. This is used for debugging. This only works if the library is compiled without the NDEBUG macro. </p></li> | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 | <li><p><b>PRAGMA parser_trace = ON; </b>(1)<b> <br>PRAGMA parser_trace = OFF;</b> (0)</p> <p>Turn tracing of the SQL parser inside of the SQLite library on and off. This is used for debugging. This only works if the library is compiled without the NDEBUG macro. </p></li> <a name="pragma_synchronous"></a> <li><p><b>PRAGMA synchronous; <br>PRAGMA synchronous = FULL; </b>(2)<b> <br>PRAGMA synchronous = NORMAL; </b>(1)<b> <br>PRAGMA synchronous = OFF;</b> (0)</p> <p>Query or change the setting of the "synchronous" flag affecting the database for the duration of the current database connection. |
︙ | ︙ | |||
1509 1510 1511 1512 1513 1514 1515 | } {join-op} { , | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN } {join-args} { [ON <expr>] [USING ( <id-list> )] } {sort-expr-list} { <expr> [<sort-order>] [, <expr> [<sort-order>]]* } {sort-order} { | | | 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 | } {join-op} { , | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN } {join-args} { [ON <expr>] [USING ( <id-list> )] } {sort-expr-list} { <expr> [<sort-order>] [, <expr> [<sort-order>]]* } {sort-order} { [ COLLATE <collation-name> ] [ ASC | DESC ] } {compound_op} { UNION | UNION ALL | INTERSECT | EXCEPT } puts { <p>The SELECT statement is used to query the database. The result of a SELECT is zero or more rows of data where each row |
︙ | ︙ | |||
1557 1558 1559 1560 1561 1562 1563 | may refer to values, even aggregate functions, that are not in the result.</p> <p>The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY is a list of expressions that are used as the key for the sort. The expressions do not have to be part of the result for a simple SELECT, but in a compound SELECT each sort expression must exactly match one of the result columns. Each | | > | | 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 | may refer to values, even aggregate functions, that are not in the result.</p> <p>The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY is a list of expressions that are used as the key for the sort. The expressions do not have to be part of the result for a simple SELECT, but in a compound SELECT each sort expression must exactly match one of the result columns. Each sort expression may be optionally followed by a COLLATE keyword and the name of a collating function used for ordering text and/or keywords ASC or DESC to specify the sort order.</p> <p>The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. |
︙ | ︙ |
Added www/version3.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | #!/usr/bin/tclsh source common.tcl header {SQLite Version 3 Overview} puts { <h2>SQLite Version 3 Overview</h2> <p> SQLite version 3.0 introduces important changes to the library, including: </p> <ul> <li>A more compact format for database files.</li> <li>Manifest typing and BLOB support.</li> <li>Support for both UTF-8 and UTF-16 text.</li> <li>User-defined text collating sequences.</li> <li>64-bit ROWIDs.</li> <li>Improved Concurrency.</li> </ul> <p> This document is a quick introduction to the changes for SQLite 3.0 for users who are already familiar with SQLite version 2.8. </p> <h3>Naming Changes</h3> <p> SQLite version 2.8 will continue to be supported with bug fixes for the foreseeable future. In order to allow SQLite version 2.8 and SQLite version 3.0 to peacefully coexist, the names of key files and APIs in SQLite version 3.0 have been changed to include the character "3". For example, the include file used by C programs has been changed from "sqlite.h" to "sqlite3.h". And the name of the shell program used to interact with databases has been changed from "sqlite.exe" to "sqlite3.exe". With these changes, it is possible to have both SQLite 2.8 and SQLite 3.0 installed on the same system at the same time. And it is possible for the same C program to link against both SQLite 2.8 and SQLite 3.0 at the same time and to use both libraries at the same time. </p> <h3>New File Format</h3> <p> The format used by SQLite database files has been completely revised. The old version 2.1 format and the new 3.0 format are incompatible with one another. Version 2.8 of SQLite will not read and version 3.0 database files and version 3.0 of SQLite will not read a version 2.8 database file. </p> <p> To convert an SQLite 2.8 database into an SQLite 3.0 database, have ready the command-line shells for both version 2.8 and 3.0. Then enter a command like the following: </p> <blockquote><pre> sqlite OLD.DB .dump | sqlite3 NEW.DB </pre></blockquote> <p> The new database file format uses B+Trees for tables. In a B+Tree, all data is stored in the leaves of the tree instead of in both the leaves and the intermediate branch nodes. The use of B+Trees for tables allows for better scalability and the storage larger data fields without the use of overflow pages. Traditional B-Trees are still used for indices.</p> <p> The new file format also supports variable pages sizes between 512 and 65536 bytes. The size of a page is stored in the file header so the same library can read databases with different pages sizes, in theory, though this feature has not yet been implemented in practice. </p> <p> The new file format omits unused fields from its disk images. For example, indices use only the key part of a B-Tree record and not the data. So for indices, the field that records the length of the data is omitted. Integer values such as the length of key and data are stored using a variable-length encoding so that only one or two bytes are required to store the most common cases but up to 64-bits of information can be encoded if needed. Integer and floating point data is stored on the disk in binary rather than being converted into ASCII as in SQLite version 2.8. These changes taken together result in database files that are typically 25% to 35% smaller than the equivalent files in SQLite version 2.8. </p> <p> Details of the low-level B-Tree format used in SQLite version 3.0 can be found in header comments to the <a href="http://www.sqlite.org/cvstrac/getfile/sqlite/src/btree.c">btree.c</a> source file. </p> <h3>Manifest Typing and BLOB Support</h3> <p> SQLite version 2.8 will deal with data in various formats internally, but when writing to the disk or interacting through its API, SQLite 2.8 always converts data into ASCII text. SQLite 3.0, in contrast, exposes its internal data representations to the user and stores binary representations to disk when appropriate. The exposing of non-ASCII representations was added in order to support BLOBs. </p> <p> SQLite version 2.8 had the feature that any type of data could be stored in any table column regardless of the declared type of that column. This feature is retained in version 3.0, though in a slightly modified form. Each table column will store any type of data, though columns have an affinity for the format of data defined by their declared datatype. When data is inserted into a column, that column will make at attempt to convert the data format into the columns declared type. All SQL database engines do this. The difference is that SQLite 3.0 will still store the data even if a format conversion is not possible. </p> <p> For example, if you have a table column declared to be of type "INTEGER" and you try to insert a string, the column will look at the text string and see if it looks like a number. If the string does look like a number it is converted into a number and into an integer if the number does not have a fractional part, and stored that way. But if the string is not a well-formed number it is still stored as a string. A column with a type of "TEXT" tries to convert numbers into an ASCII-Text representation before storing them. But BLOBs are stored in TEXT columns as BLOBs because you cannot in general convert a BLOB into text. </p> <p> In most other SQL database engines the datatype is associated with the table column the holds the data - with the data container. In SQLite 3.0, the datatype is associated with the data itself, not with its container. <a href="http://www.paulgraham.com/">Paul Graham</a> in his book <a href="http://www.paulgraham.com/acl.html"><i>ANSI Common Lisp</i></a> calls this property "Manifest Typing". Other writers have other definitions for the term "manifest typing", so beware of confusion. But by whatever name, that is the datatype model supported by SQLite 3.0. </p> <p> Additional information about datatypes in SQLite version 3.0 is available <a href="datatype3.html">separately</a>. </p> <h3>Support for UTF-8 and UTF-16</h3> <p> The new API for SQLite 3.0 contains routines that accept text as both UTF-8 and UTF-16 in the native byte order of the host machine. Each database file manages text as either UTF-8, UTF-16BE (big-endian), or UTF-16LE (little-endian). Internally and in the disk file, the same text representation is used everywhere. If the text representation specified by the database file (in the file header) does not match the text representation required by the interface routines, then text is converted on-the-fly. Constantly converting text from one representation to another can be computationally expensive, so it is suggested that programmers choose a single representation and stick with it throughout their application. </p> <p> In the current implementation of SQLite, the SQL parser only works with UTF-8 text. So if you supply UTF-16 text it will be converted. This is just an implementation issue and there is nothing to prevent future versions of SQLite from parsing UTF-16 encoded SQL natively. </p> <p> When creating new user-defined SQL functions and collating sequences, each function or collating sequence can specify it if works with UTF-8, UTF-16be, or UTF-16le. Separate implementations can be registered for each encoding. If an SQL function or collating sequences is required but a version for the current text encoding is not available, then the text is automatically converted. As before, this conversion takes computation time, so programmers are advised to pick a single encoding and stick with it in order to minimize the amount of unnecessary format juggling. </p> <p> SQLite is not particular about the text it receives and is more than happen to process text strings that are not normalized or even well-formed UTF-8 or UTF-16. Thus, programmers who want to store IS08859 data can do so using the UTF-8 interfaces. As long as no attempts are made to use a UTF-16 collating sequence or SQL function, the byte sequence of the text will not be modified in any way. </p> <h3>User-defined Collating Sequences</h3> <p> A collating sequence is just a defined order for text. When SQLite 3.0 sorts (or uses a comparison operator like "<" or ">=") the sort order is first determined by the data type. </p> <ul> <li>NULLs sort first</li> <li>Numeric values sort next in numerical order</li> <li>Text values come after numerics</li> <li>BLOBs sort last</li> </ul> <p> Collating sequences are used for comparing two text strings. The collating sequence does not change the ordering of NULLs, numbers, or BLOBs, only text. </p> <p> A collating sequence is implemented as a function that takes the two strings being compared as inputs and returns negative, zero, or positive if the first string is less than, equal to, or greater than the first. SQLite 3.0 comes with a single built-in collating sequence named "BINARY" which is implemented using the memcmp() routine from the standard C library. The BINARY collating sequence works well for English text. For other languages or locales, alternative collating sequences may be preferred. </p> <p> The decision of which collating sequence to use is controlled by the COLLATE clause in SQL. A COLLATE clause can occur on a table definition, to define a default collating sequence to a table column, or on field of an index, or in the ORDER BY clause of a SELECT statement. Planned enhancements to SQLite are to include standard CAST() syntax to allow the collating sequence of an expression to be defined. </p> <h3>64-bit ROWIDs</h3> <p> Every row of a table has a unique rowid. If the table defines a column with the type "INTEGER PRIMARY KEY" then that column becomes an alias for the rowid. But with or without an INTEGER PRIMARY KEY column, every row still has a rowid. </p> <p> In SQLite version 3.0, the rowid is a 64-bit signed integer. This is an expansion of SQLite version 2.8 which only permitted rowids of 32-bits. </p> <p> To minimize storage space, the 64-bit rowid is stored as a variable length integer. Rowids between 0 and 127 use only a single byte. Rowids between 0 and 16383 use just 2 bytes. Up to 2097152 uses three bytes. And so forth. Negative rowids are allowed but they always use nine bytes of storage and so their use is discouraged. When rowids are generated automatically by SQLite, they will always be non-negative. </p> <h3>Improved Concurrency</h3> <p> SQLite version 2.8 allowed multiple simultaneous readers or a single writer but not both. SQLite version 3.0 allows one process to begin writing the database while other processes continue to read. The writer must still obtain an exclusive lock on the database for a brief interval in order to commit its changes, but the exclusive lock is no longer required for the entire write operation. A <a href="lockingv3.html">more detailed report</a> on the locking behavior of SQLite version 3.0 is available separately. </p> <p> A limited form of table-level locking is now also available in SQLite. If each table is stored in a separate database file, those separate files can be attached to the main database (using the ATTACH command) and the combined databases will function as one. But locks will only be acquired on individual files as needed. So if you redefine "database" to mean two or more database files, then it is entirely possible for two processes to be writing to the same database at the same time. To further support this capability, commits of transactions involving two or more ATTACHed database are now atomic. </p> } footer {$Id: version3.tcl,v 1.1 2004/06/17 19:04:17 drh Exp $} |