Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates to the documentation. Changed version number to 2.4.0-beta1 (CVS 417) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
36a8fe0ad0ee2a67afafc04125dcc085 |
User & Date: | drh 2002-03-04 02:26:16.000 |
Context
2002-03-05
| ||
01:11 | Add the ability to turn of calls to fsync() using the "synchronous" pragma. Increased the default cache size from 100 to 2000 and made the "cache_size" pragma persistent. (CVS 418) (check-in: 414da4af1f user: drh tags: trunk) | |
2002-03-04
| ||
02:26 | Updates to the documentation. Changed version number to 2.4.0-beta1 (CVS 417) (check-in: 36a8fe0ad0 user: drh tags: trunk) | |
2002-03-03
| ||
23:06 | More bugs fixed for views. (CVS 416) (check-in: 8130776230 user: drh tags: trunk) | |
Changes
Changes to VERSION.
|
| | | 1 | 2.4.0-beta1 |
Changes to src/build.c.
︙ | ︙ | |||
21 22 23 24 25 26 27 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** | | | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** ** $Id: build.c,v 1.84 2002/03/04 02:26:16 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called after a single SQL statement has been ** parsed and we want to execute the VDBE code to implement |
︙ | ︙ | |||
1504 1505 1506 1507 1508 1509 1510 | Vdbe *v; int addr, end; Index *pIdx; sqlite *db = pParse->db; zTab = sqliteTableNameFromToken(pTableName); if( sqlite_malloc_failed || zTab==0 ) goto copy_cleanup; | | | < < < < < < < < < < < | 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 | Vdbe *v; int addr, end; Index *pIdx; sqlite *db = pParse->db; zTab = sqliteTableNameFromToken(pTableName); if( sqlite_malloc_failed || zTab==0 ) goto copy_cleanup; pTab = sqliteTableNameToTable(pParse, zTab); sqliteFree(zTab); if( pTab==0 ) goto copy_cleanup; v = sqliteGetVdbe(pParse); if( v ){ int openOp; sqliteBeginMultiWriteOperation(pParse); addr = sqliteVdbeAddOp(v, OP_FileOpen, 0, 0); sqliteVdbeChangeP3(v, addr, pFilename->z, pFilename->n); sqliteVdbeDequoteP3(v, addr); |
︙ | ︙ |
Changes to src/func.c.
︙ | ︙ | |||
12 13 14 15 16 17 18 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** | | | 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | ** This file contains the C functions that implement various SQL ** functions of SQLite. ** ** There is only one exported symbol in this file - the function ** sqliteRegisterBuildinFunctions() found at the bottom of the file. ** All other code has file scope. ** ** $Id: func.c,v 1.14 2002/03/04 02:26:16 drh Exp $ */ #include <ctype.h> #include <math.h> #include <stdlib.h> #include <assert.h> #include "sqliteInt.h" |
︙ | ︙ | |||
238 239 240 241 242 243 244 245 246 247 248 249 250 251 | */ typedef struct StdDevCtx StdDevCtx; struct StdDevCtx { double sum; /* Sum of terms */ double sum2; /* Sum of the squares of terms */ }; /* ** Routines used to compute the standard deviation as an aggregate. */ static void stdDevStep(sqlite_func *context, int argc, const char **argv){ StdDevCtx *p; double x; if( argc<1 ) return; | > | 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 | */ typedef struct StdDevCtx StdDevCtx; struct StdDevCtx { double sum; /* Sum of terms */ double sum2; /* Sum of the squares of terms */ }; #if 0 /* Omit because math library is required */ /* ** Routines used to compute the standard deviation as an aggregate. */ static void stdDevStep(sqlite_func *context, int argc, const char **argv){ StdDevCtx *p; double x; if( argc<1 ) return; |
︙ | ︙ | |||
259 260 261 262 263 264 265 266 267 268 269 270 271 272 | double rN = sqlite_aggregate_count(context); StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p)); if( p && rN>1.0 ){ sqlite_set_result_double(context, sqrt((p->sum2 - p->sum*p->sum/rN)/(rN-1.0))); } } /* ** The following structure keeps track of state information for the ** count() aggregate function. */ typedef struct CountCtx CountCtx; struct CountCtx { | > | 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 | double rN = sqlite_aggregate_count(context); StdDevCtx *p = sqlite_aggregate_context(context, sizeof(*p)); if( p && rN>1.0 ){ sqlite_set_result_double(context, sqrt((p->sum2 - p->sum*p->sum/rN)/(rN-1.0))); } } #endif /* ** The following structure keeps track of state information for the ** count() aggregate function. */ typedef struct CountCtx CountCtx; struct CountCtx { |
︙ | ︙ | |||
392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 | } aAggs[] = { { "min", 1, minStep, minMaxFinalize }, { "max", 1, maxStep, minMaxFinalize }, { "sum", 1, sumStep, sumFinalize }, { "avg", 1, sumStep, avgFinalize }, { "count", 0, countStep, countFinalize }, { "count", 1, countStep, countFinalize }, { "stddev", 1, stdDevStep, stdDevFinalize }, }; int i; for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ sqlite_create_function(db, aFuncs[i].zName, aFuncs[i].nArg, aFuncs[i].xFunc, 0); } for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){ sqlite_create_aggregate(db, aAggs[i].zName, aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0); } } | > > | 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 | } aAggs[] = { { "min", 1, minStep, minMaxFinalize }, { "max", 1, maxStep, minMaxFinalize }, { "sum", 1, sumStep, sumFinalize }, { "avg", 1, sumStep, avgFinalize }, { "count", 0, countStep, countFinalize }, { "count", 1, countStep, countFinalize }, #if 0 { "stddev", 1, stdDevStep, stdDevFinalize }, #endif }; int i; for(i=0; i<sizeof(aFuncs)/sizeof(aFuncs[0]); i++){ sqlite_create_function(db, aFuncs[i].zName, aFuncs[i].nArg, aFuncs[i].xFunc, 0); } for(i=0; i<sizeof(aAggs)/sizeof(aAggs[0]); i++){ sqlite_create_aggregate(db, aAggs[i].zName, aAggs[i].nArg, aAggs[i].xStep, aAggs[i].xFinalize, 0); } } |
Changes to www/c_interface.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: c_interface.tcl,v 1.25 2002/03/04 02:26:17 drh Exp $} puts {<html> <head> <title>The C language interface to the SQLite library</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
637 638 639 640 641 642 643 644 645 646 647 648 649 650 | </pre></blockquote> <p>Here the apostrophy has been escaped and the SQL statement is well-formed. When generating SQL on-the-fly from data that might contain a single-quote character ('), it is always a good idea to use the SQLite printf routines and the %q formatting option instead of <b>sprintf</b>. </p> <h2>Usage Examples</h2> <p>For examples of how the SQLite C/C++ interface can be used, refer to the source code for the <b>sqlite</b> program in the file <b>src/shell.c</b> of the source tree. Additional information about sqlite is available at | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 | </pre></blockquote> <p>Here the apostrophy has been escaped and the SQL statement is well-formed. When generating SQL on-the-fly from data that might contain a single-quote character ('), it is always a good idea to use the SQLite printf routines and the %q formatting option instead of <b>sprintf</b>. </p> <h2>Adding New SQL Functions</h2> <p>Beginning with version 2.4.0, SQLite allows the SQL language to be extended with new functions implemented as C code. The following interface is used: </p> <blockquote><pre> typedef struct sqlite_func sqlite_func; int sqlite_create_function( sqlite *db, const char *zName, int nArg, void (*xFunc)(sqlite_func*,int,const char**), void *pUserData ); int sqlite_create_aggregate( sqlite *db, const char *zName, int nArg, void (*xStep)(sqlite_func*,int,const char**), void (*xFinalize)(sqlite_func*), void *pUserData ); char *sqlite_set_result_string(sqlite_func*,const char*,int); void sqlite_set_result_int(sqlite_func*,int); void sqlite_set_result_double(sqlite_func*,double); void sqlite_set_result_error(sqlite_func*,const char*,int); void *sqlite_user_data(sqlite_func*); void *sqlite_aggregate_context(sqlite_func*, int nBytes); int sqlite_aggregate_count(sqlite_func*); </pre></blockquote> <p> The <b>sqlite_create_function()</b> interface is used to create regular functions and <b>sqlite_create_aggregate()</b> is used to create new aggregate functions. In both cases, the <b>db</b> parameter is an open SQLite database on which the functions should be registered, <b>zName</b> is the name of the new function, <b>nArg</b> is the number of arguments, and <b>pUserData</b> is a pointer which is passed through unchanged to the C implementation of the function. </p> <p> For regular functions, the <b>xFunc</b> callback is invoked once for each function call. The implementation of xFunc should call one of the <b>sqlite_set_result_...</b> interfaces to return its result. The <b>sqlite_user_data()</b> routine can be used to retrieve the <b>pUserData</b> pointer that was passed in when the function was registered. </p> <p> For aggregate functions, the <b>xStep</b> callback is invoked once for each row in the result and then <b>xFinalize</b> is invoked at the end to compute a final answer. The xStep routine can use the <b>sqlite_aggregate_context()</b> interface to allocate memory that will be unique to that particular instance of the SQL function. This memory will be automatically deleted after xFinalize is called. The <b>sqlite_aggregate_count()</b> routine can be used to find out how many rows of data were passed to the aggregate. The xFinalize callback should invoke one of the <b>sqlite_set_result_...</b> interfaces to set the final result of the aggregate. </p> <p> SQLite now implements all of its built-in functions using this interface. For additional information and examples on how to create new SQL functions, review the SQLite source code in the file <b>func.c</b>. </p> <h2>Usage Examples</h2> <p>For examples of how the SQLite C/C++ interface can be used, refer to the source code for the <b>sqlite</b> program in the file <b>src/shell.c</b> of the source tree. Additional information about sqlite is available at |
︙ | ︙ |
Changes to www/changes.tcl.
︙ | ︙ | |||
13 14 15 16 17 18 19 | proc chng {date desc} { puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } | | > > > > > > > > | 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 | proc chng {date desc} { puts "<DT><B>$date</B></DT>" puts "<DD><P><UL>$desc</UL></P></DD>" } chng {2002 Mar * (2.4.0)} { <li>Change the name of the sanity_check PRAGMA to <b>integrity_check</b> and make it available in all compiles.</li> <li>SELECT min() or max() of an indexed column with no WHERE or GROUP BY clause is handled as a special case which avoids a complete table scan.</li> <li>Automatically generated ROWIDs are now sequential.</li> <li>Do not allow dot-commands of the command-line shell to occur in the middle of a real SQL command.</li> <li>Modifications to the "lemon" parser generator so that the parser tables are 4 times smaller.</li> <li>Added support for user-defined functions implemented in C.</li> <li>Added support for new functions: <b>coalesce()</b>, <b>lower()</b>, <b>upper()</b>, and <b>random()</b> <li>Added support for VIEWs.</li> <li>Added the subquery flattening optimizer.</li> <li>Modified the B-Tree and Pager modules so that disk pages that do not contain real data (free pages) are not journalled and are not written from memory back to the disk when they change. This does not impact database integrity, since the pages contain no real data, but it does make large INSERT operations about 2.5 times faster and large DELETEs about 5 times faster.</li> } chng {2002 Feb 18 (2.3.3)} { <li>Allow identifiers to be quoted in square brackets, for compatibility with MS-Access.</li> <li>Added support for sub-queries in the FROM clause of a SELECT.</li> <li>More efficient implementation of sqliteFileExists() under Windows. |
︙ | ︙ |
Changes to www/formatchng.tcl.
1 2 3 | # # Run this Tcl script to generate the formatchng.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 | # # Run this Tcl script to generate the formatchng.html file. # set rcsid {$Id: formatchng.tcl,v 1.3 2002/03/04 02:26:17 drh Exp $ } puts {<html> <head> <title>File Format Changes in SQLite</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
85 86 87 88 89 90 91 | <td valign="top">2.2.5 to 2.3.0</td> <td valign="top">2002-Jan-30</td> <td>Beginning with version 2.3.0, SQLite supports some additional syntax (the "ON CONFLICT" clause) in the CREATE TABLE and CREATE INDEX statements that are stored in the SQLITE_MASTER table. If you create a database that contains this new syntax, then try to read that database using version 2.2.5 or earlier, the parser will not understand the new syntax and you will get | | > > > > > > > > > > > > > > | 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 | <td valign="top">2.2.5 to 2.3.0</td> <td valign="top">2002-Jan-30</td> <td>Beginning with version 2.3.0, SQLite supports some additional syntax (the "ON CONFLICT" clause) in the CREATE TABLE and CREATE INDEX statements that are stored in the SQLITE_MASTER table. If you create a database that contains this new syntax, then try to read that database using version 2.2.5 or earlier, the parser will not understand the new syntax and you will get an error. Otherwise, databases for 2.2.x and 2.3.x are interchangeable.</td> </tr> <tr> <td valign="top">2.3.3 to 2.4.0</td> <td valign="top">2002-Mar-?</td> <td>Beginning with version 2.4.0, SQLite added support for views. Information about views is stored in the SQLITE_MASTER table. If an older version of SQLite attempts to read a database that contains VIEW information in the SQLITE_MASTER table, the parser will not understand the new syntax and you will get an error. Also, the way SQLite keeps track of unused disk blocks in the database file changed slightly. If an older version of SQLite attempts to write a database that was previously written by version 2.4.0 or later, then it may leak disk blocks.</td> </tr> </table> </blockquote> <p> To perform a database reload, have ready versions of the <b>sqlite</b> command-line utility for both the old and new |
︙ | ︙ |
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.27 2002/03/04 02:26:17 drh Exp $} puts {<html> <head> <title>Query Language Understood By SQLite</title> </head> <body bgcolor=white> <h1 align=center> |
︙ | ︙ | |||
48 49 50 51 52 53 54 55 56 57 58 59 60 61 | {SELECT select} {COPY copy} {EXPLAIN explain} {expression expr} {{BEGIN TRANSACTION} transaction} {PRAGMA pragma} {{ON CONFLICT clause} conflict} }] { puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>" } puts {</ul></p> <p>Details on the implementation of each command are provided in the sequel.</p> | > > | 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | {SELECT select} {COPY copy} {EXPLAIN explain} {expression expr} {{BEGIN TRANSACTION} transaction} {PRAGMA pragma} {{ON CONFLICT clause} conflict} {{CREATE VIEW} createview} {{DROP VIEW} dropview} }] { puts "<li><a href=\"#[lindex $section 1]\">[lindex $section 0]</a></li>" } puts {</ul></p> <p>Details on the implementation of each command are provided in the sequel.</p> |
︙ | ︙ | |||
337 338 339 340 341 342 343 344 345 346 347 348 349 350 | are read from the <b>sqlite_master</b> table and used to regenerate SQLite's internal representation of the table layout. If the original command was a CREATE TABLE AS then then an equivalent CREATE TABLE statement is synthesized and store in <b>sqlite_master</b> in place of the original command. </p> } Section DELETE delete Syntax {sql-statement} { DELETE FROM <table-name> [WHERE <expression>] } | > > > > > > > > > > > > > > > | 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 | are read from the <b>sqlite_master</b> table and used to regenerate SQLite's internal representation of the table layout. If the original command was a CREATE TABLE AS then then an equivalent CREATE TABLE statement is synthesized and store in <b>sqlite_master</b> in place of the original command. </p> } Section {CREATE VIEW} {createview} Syntax {sql-command} { CREATE VIEW <view-name> AS <select-statement> } puts { <p>The CREATE VIEW command assigns a name to a pre-packaged SELECT statement. Once the view is created, it can be used in the FROM clause of another SELECT in place of a table name. </p> <p>You cannot COPY, INSERT or UPDATE a view. Views are read-only.</p> } Section DELETE delete Syntax {sql-statement} { DELETE FROM <table-name> [WHERE <expression>] } |
︙ | ︙ | |||
381 382 383 384 385 386 387 388 389 390 391 392 393 394 | puts { <p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed by the name of the table. The table named is completely removed from the disk. The table can not be recovered. All indices associated with the table are also deleted.</p>} Section EXPLAIN explain Syntax {sql-statement} { EXPLAIN <sql-statement> } puts { | > > > > > > > > > > > | 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 | puts { <p>The DROP TABLE statement consists of the keywords "DROP TABLE" followed by the name of the table. The table named is completely removed from the disk. The table can not be recovered. All indices associated with the table are also deleted.</p>} Section {DROP VIEW} dropview Syntax {sql-command} { DROP VIEW <view-name> } puts { <p>The DROP VIEW statement consists of the keywords "DROP TABLE" followed by the name of the view. The view named is removed from the database. But no actual data is modified.</p>} Section EXPLAIN explain Syntax {sql-statement} { EXPLAIN <sql-statement> } puts { |
︙ | ︙ | |||
416 417 418 419 420 421 422 | <table-name> . <column-name> | <literal-value> | <function-name> ( <expr-list> | STAR ) | <expression> ISNULL | <expression> NOTNULL | <expression> [NOT] BETWEEN <expression> AND <expression> | <expression> [NOT] IN ( <value-list> ) | | | | | 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 | <table-name> . <column-name> | <literal-value> | <function-name> ( <expr-list> | STAR ) | <expression> ISNULL | <expression> NOTNULL | <expression> [NOT] BETWEEN <expression> AND <expression> | <expression> [NOT] IN ( <value-list> ) | <expression> [NOT] IN ( <select-statement> ) | ( <select-statement> ) } {like-op} { LIKE | GLOB | NOT LIKE | NOT GLOB } puts { <p>This section is different from the others. Most other sections of this document talks about a particular SQL command. This section does |
︙ | ︙ | |||
514 515 516 517 518 519 520 | <p>When a SELECT appears within an expression but is not the right operand of an IN operator, then the first row of the result of the SELECT becomes the value used in the expression. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yeilds no rows, then the value of the SELECT is NULL.</p> | > | | > > > | > > > > > | > > > > > | > > > > > | | > > > > > > | > > > > > > | > | > > > > | | > > | < > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > > > | > > | | > > | < > > > > | | > > > > | < > > > | > | 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 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 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 | <p>When a SELECT appears within an expression but is not the right operand of an IN operator, then the first row of the result of the SELECT becomes the value used in the expression. If the SELECT yields more than one result row, all rows after the first are ignored. If the SELECT yeilds no rows, then the value of the SELECT is NULL.</p> <p>Both simple and aggregate functions are supported. A simple function can be used in any expression. Simple functions return a result immediately based on their inputs. Aggregate functions may only be used in a SELECT statement. Aggregate functions compute their result across all rows of the result set.</p> <p>The following simple functions are currently supported:</p> <table border=0 cellpadding=10> <tr> <td valign="top" align="right" width=120>abs(<i>X</i>)</td> <td valign="top">Return the absolute value of argument <i>X</i>.</td> </tr> <tr> <td valign="top" align="right">coelasce(<i>X</i>,<i>Y</i>,...)</td> <td valign="top">Return a copy of the first non-NULL argument. If all arguments are NULL then NULL is returned.</td> </tr> <tr> <td valign="top" align="right">length(<i>X</i>)</td> <td valign="top">Return the string length of <i>X</i> in characters. If SQLite is configured to support UTF-8, then the number of UTF-8 characters is returned, not the number of bytes.</td> </tr> <tr> <td valign="top" align="right">lower(<i>X</i>)</td> <td valign="top">Return a copy of string <i>X</i> will all characters converted to lower case. The C library <b>tolower()</b> routine is used for the conversion, which means that this function might not work correctly on UTF-8 characters.</td> </tr> <tr> <td valign="top" align="right">max(<i>X</i>,<i>Y</i>,...)</td> <td valign="top">Return the argument with the maximum value. Arguments may be strings in addition to numbers. The maximum value is determined by the usual sort order. Note that <b>max()</b> is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.</td> </tr> <tr> <td valign="top" align="right">min(<i>X</i>,<i>Y</i>,...)</td> <td valign="top">Return the argument with the minimum value. Arguments may be strings in addition to numbers. The mminimum value is determined by the usual sort order. Note that <b>min()</b> is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.</td> </tr> <tr> <td valign="top" align="right">random(*)</td> <td valign="top">Return a random integer between -2147483648 and +2147483647.</td> </tr> <tr> <td valign="top" align="right">round(<i>X</i>)<br>round(<i>X</i>,<i>Y</i>)</td> <td valign="top">Round off the number <i>X</i> to <i>Y</i> digits to the right of the decimal point. If the <i>Y</i> argument is omitted, 0 is assumed.</td> </tr> <tr> <td valign="top" align="right">subst(<i>X</i>,<i>Y</i>,<i>Z</i>)</td> <td valign="top">Return a substring of input string <i>X</i> that begins with the <i>Y</i>-th character and which is <i>Z</i> characters long. The left-most character of <i>X</i> is number 1. If <i>Y</i> is negative the the first character of the substring is found by counting from the right rather than the left. If SQLite is configured to support UTF-8, then characters indices refer to actual UTF-8 characters, not bytes.</td> </tr> <tr> <td valign="top" align="right">upper(<i>X</i>)</td> <td valign="top">Return a copy of input string <i>X</i> converted to all upper-case letters. The implementation of this function uses the C library routine <b>toupper()</b> which means it may not work correctly on UTF-8 strings.</td> </tr> </table> <p> The following aggregate functions are supported: </p> <table border=0 cellpadding=10> <tr> <td valign="top" align="right" width=120>avg(<i>X</i>)</td> <td valign="top">Return the average value of all <i>X</i> within a group.</td> </tr> <tr> <td valign="top" align="right">count(<i>X</i>)<br>count(*)</td> <td valign="top">The first form return a count of the number of times that <i>X</i> is not NULL in a group. The second form (with no argument) returns the total number of rows in the group.</td> </tr> <tr> <td valign="top" align="right">max(<i>X</i>)</td> <td valign="top">Return the maximum value of all values in the group. The usual sort order is used to determine the maximum.</td> </tr> <tr> <td valign="top" align="right">min(<i>X</i>)</td> <td valign="top">Return the minimum value of all values in the group. The usual sort order is used to determine the minimum.</td> </tr> <tr> <td valign="top" align="right">sum(<i>X</i>)</td> <td valign="top">Return the numeric sum of all values in the group.</td> </tr> </table> } Section INSERT insert Syntax {sql-statement} { INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] VALUES(<value-list>) | INSERT [OR <conflict-algorithm>] INTO <table-name> [(<column-list>)] <select-statement> |
︙ | ︙ |