Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add automatically generated tables of contents to rbu.html, cli.html and datatype3.html. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
bd775e82aaa9677c3e3970abcbea48a0 |
User & Date: | dan 2016-04-26 17:59:52.884 |
Context
2016-04-26
| ||
19:37 | Also add a table of contents to fileformat2.html. (check-in: e9007b6030 user: dan tags: trunk) | |
17:59 | Add automatically generated tables of contents to rbu.html, cli.html and datatype3.html. (check-in: bd775e82aa user: dan tags: trunk) | |
15:37 | Add documentation for RBU vacuum to rbu.in. (check-in: 2906c27967 user: dan tags: trunk) | |
Changes
Changes to pages/cli.in.
1 2 3 4 | <title>Command Line Shell For SQLite</title> <tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \ {command-line interface} </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 | <title>Command Line Shell For SQLite</title> <tcl>hd_keywords {CLI} {Command Line Interface} {command-line shell} \ {command-line interface} </tcl> <table_of_contents> <p>The SQLite project provides a simple command-line utility named <b>sqlite3</b> (or <b>sqlite3.exe</b> on windows) that allows the user to manually enter and execute SQL statements against an SQLite database. This document provides a brief introduction on how to use the <b>sqlite3</b> program. <tcl>hd_fragment intro</tcl> <h1>Getting Started</h1> <p>To start the <b>sqlite3</b> program, just type "sqlite3" optionally followed by the name the file that holds the SQLite database. If the file does not exist, a new database file with the given name will be created automatically. If no database file is specified, a temporary database is created, then deleted when the "sqlite3" program exits. <p>When started, the <b>sqlite3</b> program will show a brief banner message then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.</p> <p>For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:</p> <tcl> proc DisplayCode {body} { regsub -all {&} [string trim $body] {\&} body regsub -all {>} $body {\>} body regsub -all {<} $body {\<} body regsub -all {\(\(\(} $body {<b>} body regsub -all {\)\)\)} $body {</b>} body regsub -all {\[\[\[} $body {<i>} body regsub -all {\]\]\]} $body {</i>} body #regsub -all { } $body {\ } body #regsub -all \n $body <br>\n body #hd_puts {<blockquote><pre>} #hd_puts $body #hd_puts {</pre></blockquote>} hd_puts [CodeBlock $body] } DisplayCode { $ (((sqlite3 ex1))) SQLite version 3.8.5 2014-05-29 12:36:14 Enter ".help" for usage hints. sqlite> (((create table tbl1(one varchar(10), two smallint);))) |
︙ | ︙ | |||
76 77 78 79 80 81 82 | ...> ((( f2 text,))) ...> ((( f3 real))) ...> ((();))) sqlite> }</tcl> <tcl>hd_fragment dblclick</tcl> | | | 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | ...> ((( f2 text,))) ...> ((( f3 real))) ...> ((();))) sqlite> }</tcl> <tcl>hd_fragment dblclick</tcl> <h1>Double-click Startup On Windows</h1> <p>Windows users can double-click on the <b>sqlite3.exe</b> icon to cause the command-line shell to pop-up a terminal window running SQLite. Note, however, that by default this SQLite session is using an in-memory database, not a file on disk, and so all changes will be lost when the session exits. To use a persistent disk file as the database, enter the ".open" command immediately after the terminal window starts up: |
︙ | ︙ | |||
119 120 121 122 123 124 125 | <p>Be careful when using the ".save" command as it will overwrite any preexisting database files having the same name without prompting for confirmation. As with the ".open" command, you might want to use a full pathname with forward-slash directory separators to avoid ambiguity. <tcl>hd_fragment dotcmd {dot-commands}</tcl> | | | 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 | <p>Be careful when using the ".save" command as it will overwrite any preexisting database files having the same name without prompting for confirmation. As with the ".open" command, you might want to use a full pathname with forward-slash directory separators to avoid ambiguity. <tcl>hd_fragment dotcmd {dot-commands}</tcl> <h1>Special commands to sqlite3 (dot-commands)</h1> <p> Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot ("."), then that line is intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format |
︙ | ︙ | |||
208 209 210 211 212 213 214 | .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set column widths for "column" mode Negative values right-justify sqlite> }</tcl> <tcl>hd_fragment dotrules</tcl> | | | 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 | .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set column widths for "column" mode Negative values right-justify sqlite> }</tcl> <tcl>hd_fragment dotrules</tcl> <h1>Rules for "dot-commands"</h1> <p>Ordinary SQL statements are free-form, and can be spread across multiple lines, and can have whitespace and comments anywhere. But dot-commands are more restrictive: <ul> |
︙ | ︙ | |||
231 232 233 234 235 236 237 | <p>And, of course, it is important to remember that the dot-commands are interpreted by the sqlite3.exe command-line program, not by SQLite itself. So none of the dot-commands will work as an argument to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()]. <tcl>hd_fragment dotmode</tcl> | | | 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 | <p>And, of course, it is important to remember that the dot-commands are interpreted by the sqlite3.exe command-line program, not by SQLite itself. So none of the dot-commands will work as an argument to SQLite interfaces like [sqlite3_prepare()] or [sqlite3_exec()]. <tcl>hd_fragment dotmode</tcl> <h1>Changing Output Formats</h1> <p>The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.</p> |
︙ | ︙ | |||
375 376 377 378 379 380 381 | <p>Beginning with [Version 3.11.0], the command-line shell defaults to "auto-explain" mode, in which the EXPLAIN commands are automatically detected and the output is automatically formatted. So the ".explain" command has become superfluous. <tcl>hd_fragment dotoutput</tcl> | | | 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 | <p>Beginning with [Version 3.11.0], the command-line shell defaults to "auto-explain" mode, in which the EXPLAIN commands are automatically detected and the output is automatically formatted. So the ".explain" command has become superfluous. <tcl>hd_fragment dotoutput</tcl> <h1>Writing results to a file</h1> <p>By default, sqlite3 sends query results to standard output. You can change this using the ".output" and ".once" commands. Just put the name of an output file as an argument to .output and all subsequent query results will be written to that file. Or use the .once command instead of .output and output will only be redirected for the single next command before returning the console. Use .output with no arguments to |
︙ | ︙ | |||
411 412 413 414 415 416 417 | <tcl>DisplayCode { sqlite3> (((.once '|open -f'))) sqlite3> (((SELECT * FROM bigTable;))) }</tcl> <tcl>hd_fragment fileio {file I/O functions}</tcl> | | | 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 | <tcl>DisplayCode { sqlite3> (((.once '|open -f'))) sqlite3> (((SELECT * FROM bigTable;))) }</tcl> <tcl>hd_fragment fileio {file I/O functions}</tcl> <h2>File I/O Functions</h2> <p>The command-line shell adds two [application-defined SQL functions] that facilitate read content from a file into an table column, and writing the content of a column into a file, respectively. <p>The readfile(X) SQL function reads the entire content of the file named X and returns that content as a BLOB. This can be used to load content into |
︙ | ︙ | |||
442 443 444 445 446 447 448 | <p>Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a [loadable extension] in the [http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c] source file in the [SQLite source code repositories]. <tcl>hd_fragment schema</tcl> | | | 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 | <p>Note that the readfile(X) and writefile(X,Y) functions are extension functions and are not built into the core SQLite library. These routines are available as a [loadable extension] in the [http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c|ext/misc/fileio.c] source file in the [SQLite source code repositories]. <tcl>hd_fragment schema</tcl> <h1>Querying the database schema</h1> <p>The sqlite3 program provides several convenience commands that are useful for looking at the schema of the database. There is nothing that these commands do that cannot be done by some other means. These commands are provided purely as a shortcut.</p> <p>For example, to see a list of the tables in the database, you |
︙ | ︙ | |||
464 465 466 467 468 469 470 | sqlite> }</tcl> <p>The ".tables" command is similar to setting list mode then executing the following query:</p> | | | | 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 | sqlite> }</tcl> <p>The ".tables" command is similar to setting list mode then executing the following query:</p> <tcl>DisplayCode { SELECT name FROM sqlite_master WHERE type IN ('table','view') AND name NOT LIKE 'sqlite_%' UNION ALL SELECT name FROM sqlite_temp_master WHERE type IN ('table','view') ORDER BY 1 } </tcl> <p>In fact, if you look at the source code to the sqlite3 program (found in the source tree in the file [https://www.sqlite.org/src/artifact?ci=trunk&filename=src/shell.c|src/shell.c]) you'll find a query very much like the above.</p> <p>The ".indices" command works in a similar way to list all of |
︙ | ︙ | |||
509 510 511 512 513 514 515 | sqlite> }</tcl> <p>The ".schema" command accomplishes the same thing as setting list mode, then entering the following query:</p> | | | | | | | | 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 | sqlite> }</tcl> <p>The ".schema" command accomplishes the same thing as setting list mode, then entering the following query:</p> <tcl>DisplayCode { SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type!='meta' ORDER BY tbl_name, type DESC, name } </tcl> <p>Or, if you give an argument to ".schema" because you only want the schema for a single table, the query looks like this:</p> <tcl>DisplayCode { SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' ORDER BY substr(type,2,1), name } </tcl> <p> You can supply an argument to the .schema command. If you do, the query looks like this: </p> <tcl>DisplayCode { SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE tbl_name LIKE '%s' AND type!='meta' AND sql NOT NULL AND name NOT LIKE 'sqlite_%' ORDER BY substr(type,2,1), name } </tcl> <p>The "%s" in the query is replace by your argument. This allows you to view the schema for some subset of the database.</p> <tcl>DisplayCode { sqlite> (((.schema %abc%))) |
︙ | ︙ | |||
586 587 588 589 590 591 592 | are requested to provide the complete ".fullschema" output as part of the trouble report. Note that the sqlite_stat3 and sqlite_stat4 tables contain samples of index entries and so might contain sensitive data, so do not send the ".fullschema" output of a proprietary database over a public channel.</p> <tcl>hd_fragment csv {CSV import}</tcl> | | | 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 | are requested to provide the complete ".fullschema" output as part of the trouble report. Note that the sqlite_stat3 and sqlite_stat4 tables contain samples of index entries and so might contain sensitive data, so do not send the ".fullschema" output of a proprietary database over a public channel.</p> <tcl>hd_fragment csv {CSV import}</tcl> <h1>CSV Import</h1> <p>Use the ".import" command to import CSV (comma separated value) data into an SQLite table. The ".import" command takes two arguments which are the name of the disk file from which CSV data is to be read and the name of the SQLite table into which the CSV data is to be inserted. <p>Note that it is important to set the "mode" to "csv" before running the |
︙ | ︙ | |||
619 620 621 622 623 624 625 | <p>For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist. <tcl>hd_fragment csvout {CSV export}</tcl> | | | 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 | <p>For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist. <tcl>hd_fragment csvout {CSV export}</tcl> <h1>CSV Export</h1> <p>To export an SQLite table (or part of a table) as CSV, simply set the "mode" to "csv" and then run a query to extract the desired rows of the table. <tcl>DisplayCode { sqlite> (((.header on))) |
︙ | ︙ | |||
655 656 657 658 659 660 661 | the CSV file. That command only works as shown on Windows. The equivalent line on a Mac would be ".system open /work/dataout.csv". On Linux and other unix systems you will need to enter something like ".system libreoffice /work/dataout.csv", substituting your preferred CSV viewing program for "libreoffice". <tcl>hd_fragment dump</tcl> | | | 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 | the CSV file. That command only works as shown on Windows. The equivalent line on a Mac would be ".system open /work/dataout.csv". On Linux and other unix systems you will need to enter something like ".system libreoffice /work/dataout.csv", substituting your preferred CSV viewing program for "libreoffice". <tcl>hd_fragment dump</tcl> <h1>Converting An Entire Database To An ASCII Text File</h1> <p>Use the ".dump" command to convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into <b>sqlite3</b>.</p> <p>A good way to make an archival copy of a database is this:</p> |
︙ | ︙ | |||
690 691 692 693 694 695 696 | <tcl>DisplayCode { $ (((createdb ex2))) $ (((sqlite3 ex1 .dump | psql ex2))) }</tcl> <tcl>hd_fragment dotload</tcl> | | | 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 | <tcl>DisplayCode { $ (((createdb ex2))) $ (((sqlite3 ex1 .dump | psql ex2))) }</tcl> <tcl>hd_fragment dotload</tcl> <h1>Loading Extensions</h1> <p>You can add new custom [application-defined SQL functions], [collating sequences], [virtual tables], and [VFSes] to the command-line shell at run-time using the ".load" command. First, convert the extension in to a DLL or shared library (as described in the [Run-Time Loadable Extensions] document) then type: |
︙ | ︙ | |||
718 719 720 721 722 723 724 | <p>Source code for several useful extensions can be found in the <a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a> subdirectory of the SQLite source tree. You can use these extensions as-is, or as a basis for creating your own custom extensions to address your own particular needs. <tcl>hd_fragment dotother</tcl> | | | | 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 | <p>Source code for several useful extensions can be found in the <a href="http://www.sqlite.org/src/tree?name=ext/misc&ci=trunk">ext/misc</a> subdirectory of the SQLite source tree. You can use these extensions as-is, or as a basis for creating your own custom extensions to address your own particular needs. <tcl>hd_fragment dotother</tcl> <h1>Other Dot Commands</h1> <p>There are many other dot-commands available in the command-line shell. See the ".help" command for a complete list for any particular version and build of SQLite. <tcl>hd_fragment insh</tcl> <h1>Using sqlite3 in a shell script</h1> <p> One way to use sqlite3 in a shell script is to use "echo" or "cat" to generate a sequence of commands in a file, then invoke sqlite3 while redirecting input from the generated command file. This works fine and is appropriate in many circumstances. But as an added convenience, sqlite3 allows a single SQL command to be |
︙ | ︙ | |||
750 751 752 753 754 755 756 | > ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'))) <tr><td>hello<td>10 <tr><td>goodbye<td>20 $ }</tcl> <tcl>hd_fragment endsh</tcl> | | | | | | 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 | > ((( awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'))) <tr><td>hello<td>10 <tr><td>goodbye<td>20 $ }</tcl> <tcl>hd_fragment endsh</tcl> <h1>Ending shell commands</h1> <p> SQLite commands are normally terminated by a semicolon. In the shell you can also use the word "GO" (case-insensitive) or a slash character "/" on a line by itself to end a command. These are used by SQL Server and Oracle, respectively. These won't work in <b>sqlite3_exec()</b>, because the shell translates these into a semicolon before passing them to that function.</p> <tcl>hd_fragment compiling</tcl> <h1>Compiling the sqlite3 program from sources</h1> <p> The source code to the sqlite3 command line interface is in a single file named "shell.c" which you can <a href="http://www.sqlite.org/src/finfo?name=src/shell.c"> download</a> from the SQLite website. [how to compile|Compile] this file (together with the [amalgamation | sqlite3 library source code]) to generate the executable. For example:</p> <tcl>DisplayCode { gcc -o sqlite3 shell.c sqlite3.c -ldl -lpthread } </tcl> |
Changes to pages/datatype3.in.
1 2 3 | <title>Datatypes In SQLite Version 3</title> <tcl>hd_keywords {datatype} {dynamic typing}</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 | <title>Datatypes In SQLite Version 3</title> <tcl>hd_keywords {datatype} {dynamic typing}</tcl> <table_of_contents> <h1>Datatypes In SQLite</h1> <p>Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.</p> <p>SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.</p> <tcl>hd_fragment storageclasses {storage class}</tcl> <h1>Storage Classes and Datatypes</h1> <p>Each value stored in an SQLite database (or manipulated by the database engine) has one of the following storage classes:</p> <ul> <li><p><B>NULL</B>. The value is a NULL value.</p> |
︙ | ︙ | |||
57 58 59 60 61 62 63 | have an implicit storage class. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution. </p> <tcl>hd_fragment boolean {boolean datatype}</tcl> | | | | 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | have an implicit storage class. Under circumstances described below, the database engine may convert values between numeric storage classes (INTEGER and REAL) and TEXT during query execution. </p> <tcl>hd_fragment boolean {boolean datatype}</tcl> <h2>Boolean Datatype</h2> <p>SQLite does not have a separate Boolean storage class. Instead, ^Boolean values are stored as integers 0 (false) and 1 (true).</p> <tcl>hd_fragment datetime {date and time datatype}</tcl> <h2>Date and Time Datatype</h2> <p>SQLite does not have a storage class set aside for storing dates and/or times. ^(Instead, the built-in [Date And Time Functions] of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:</p> <ul> |
︙ | ︙ | |||
85 86 87 88 89 90 91 | <p>Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.</p> <tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl> | | | 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 | <p>Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.</p> <tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl> <h1>Type Affinity</h1> <p> In order to maximize compatibility between SQLite and other database engines, SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data. |
︙ | ︙ | |||
153 154 155 156 157 158 159 | be detected by examining the raw bits of the database file.)</p> <p>^A column with affinity BLOB does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.</p> <tcl>hd_fragment affname {rules for determining column affinity}</tcl> | | | 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 | be detected by examining the raw bits of the database file.)</p> <p>^A column with affinity BLOB does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another.</p> <tcl>hd_fragment affname {rules for determining column affinity}</tcl> <h2>Determination Of Column Affinity</h2> <p>^(The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:)^</p> <ol> <li><p>^If the declared type contains the string "INT" then it is assigned INTEGER affinity.</p> |
︙ | ︙ | |||
183 184 185 186 187 188 189 | </ol> <p>^Note that the order of the rules for determining column affinity is important. ^A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.</p> | | | 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 | </ol> <p>^Note that the order of the rules for determining column affinity is important. ^A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.</p> <h2>Affinity Name Examples</h2> <p>The following table shows how many common datatype names from more traditional SQL implementations are converted into affinities by the five rules of the previous section. This table shows only a small subset of the datatype names that SQLite will accept. Note that ^(numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored)^ by SQLite - SQLite does not impose any length restrictions |
︙ | ︙ | |||
255 256 257 258 259 260 261 | </table>)^ </blockquote> <p>^Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". ^And the declared type of "STRING" has an affinity of NUMERIC, not TEXT. | | | 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 | </table>)^ </blockquote> <p>^Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". ^And the declared type of "STRING" has an affinity of NUMERIC, not TEXT. <h2>Column Affinity Behavior Example</h2> <p>The following SQL demonstrates how SQLite uses column affinity to do type conversions when values are inserted into a table.</p> ^(<blockquote> <pre> CREATE TABLE t1( |
︙ | ︙ | |||
302 303 304 305 306 307 308 | INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; null|null|null|null|null </pre> </blockquote>)^ <tcl>hd_fragment comparisons {comparison expressions}</tcl> | | | | | 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 | INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL); SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; null|null|null|null|null </pre> </blockquote>)^ <tcl>hd_fragment comparisons {comparison expressions}</tcl> <h1>Comparison Expressions</h1> <p>SQLite version 3 has the usual set of SQL comparison operators including "=", "==", "<", "<=", ">", ">=", "!=", "<>", "IN", "NOT IN", "BETWEEN", "IS", and "IS NOT", . <h2>Sort Order</h2> <p>The results of a comparison depend on the storage classes of the operands, according to the following rules:</p> <ul> <li><p>^A value with storage class NULL is considered less than any other value (including another value with storage class NULL).</p> <li><p>^An INTEGER or REAL value is less than any TEXT or BLOB value. ^When an INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison is performed.</p> <li><p>^A TEXT value is less than a BLOB value. ^When two TEXT values are compared an appropriate collating sequence is used to determine the result. </p> <li><p>^When two BLOB values are compared, the result is determined using memcmp().</p> </ul> <tcl>hd_fragment expraff {expression affinity}</tcl> <h2>Affinity Of Comparison Operands</h2> <p>^SQLite may attempt to convert values between the storage classes INTEGER, REAL, and/or TEXT before performing a comparison. ^Whether or not any conversions are attempted before the comparison takes place depends on the type affinity of the operands. <p>Note that every table column as a type affinity (one of BLOB, TEXT, INTEGER, |
︙ | ︙ | |||
361 362 363 364 365 366 367 | has an affinity that is the same as a column with a declared type of "<i>type</i>".)^ <li><p>^Otherwise, an expression has no affinity. </ul> <tcl>hd_fragment compaff {comparison affinity rules}</tcl> | | | 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 | has an affinity that is the same as a column with a declared type of "<i>type</i>".)^ <li><p>^Otherwise, an expression has no affinity. </ul> <tcl>hd_fragment compaff {comparison affinity rules}</tcl> <h2>Type Conversions Prior To Comparison</h2> <p>To "apply affinity" means to convert an operand to a particular storage class if and only if the conversion is lossless and reversible. ^(Affinity is applied to operands of a comparison operator prior to the comparison according to the following rules in the order shown:)^</p> <ul> |
︙ | ︙ | |||
393 394 395 396 397 398 399 | ^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR a = +y OR a = +z OR ...".)^ ^In other words, the values to the right of the IN operator (the "x", "y", and "z" values in this example) are considered to have no affinity, even if they happen to be column values or CAST expressions. </p> | | | 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 | ^(The expression "a IN (x, y, z, ...)" is equivalent to "a = +x OR a = +y OR a = +z OR ...".)^ ^In other words, the values to the right of the IN operator (the "x", "y", and "z" values in this example) are considered to have no affinity, even if they happen to be column values or CAST expressions. </p> <h2>Comparison Example</h2> ^(<blockquote> <pre> CREATE TABLE t1( a TEXT, -- text affinity b NUMERIC, -- numeric affinity c BLOB, -- no affinity |
︙ | ︙ | |||
459 460 461 462 463 464 465 | </pre> </blockquote>)^ <p>^All of the result in the example are the same if the comparisons are commuted - if expressions of the form "a<40" are rewritten as "40>a". | | | | 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 | </pre> </blockquote>)^ <p>^All of the result in the example are the same if the comparisons are commuted - if expressions of the form "a<40" are rewritten as "40>a". <h1>Operators</h1> <p>^(All mathematical operators (+, -, *, /, %, <<, >>, &, and |) cast both operands to the NUMERIC storage class prior to being carried out.)^ ^The cast is carried through even if it is lossy and irreversible. ^A NULL operand on a mathematical operator yields a NULL result. ^(An operand on a mathematical operator that does not look in any way numeric and is not NULL is converted to 0 or 0.0.)^ </p> <h1>Sorting, Grouping and Compound SELECTs</h1> <p>^When query results are sorted by an ORDER BY clause, values with storage class NULL come first, followed by INTEGER and REAL values interspersed in numeric order, followed by TEXT values in collating sequence order, and finally BLOB values in memcmp() order. ^No storage class conversions occur before the sort.</p> |
︙ | ︙ | |||
495 496 497 498 499 500 501 | are compared as is.</p> <tcl>hd_fragment collation {*collating sequence} {*collating sequences}\ {collating function} *collation *BINARY *NOCASE *RTRIM \ {BINARY collating function} \ {NOCASE collating function} \ {RTRIM collating function}</tcl> | | | 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 | are compared as is.</p> <tcl>hd_fragment collation {*collating sequence} {*collating sequences}\ {collating function} *collation *BINARY *NOCASE *RTRIM \ {BINARY collating function} \ {NOCASE collating function} \ {RTRIM collating function}</tcl> <h1>Collating Sequences</h1> <p>^When SQLite compares two strings, it uses a collating sequence or collating function (two words for the same thing) to determine which string is greater or if the two strings are equal. ^SQLite has three built-in collating functions: BINARY, NOCASE, and RTRIM.</p> |
︙ | ︙ | |||
519 520 521 522 523 524 525 | <li>^(<b>RTRIM</b> - The same as binary, except that trailing space characters are ignored.)^</li> </ul> <p>An application can register additional collating functions using the [sqlite3_create_collation()] interface.</p> | | | 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 | <li>^(<b>RTRIM</b> - The same as binary, except that trailing space characters are ignored.)^</li> </ul> <p>An application can register additional collating functions using the [sqlite3_create_collation()] interface.</p> <h2>Assigning Collating Sequences from SQL</h2> <p> ^Every column of every table has an associated collating function. ^If no collating function is explicitly defined, then the collating function defaults to BINARY. ^The COLLATE clause of the [column definition] is used to define alternative collating functions for a column. |
︙ | ︙ | |||
585 586 587 588 589 590 591 | used for sorting. ^Otherwise, if the expression sorted by an ORDER BY clause is a column, then the collating sequence of the column is used to determine sort order. ^If the expression is not a column and has no COLLATE clause, then the BINARY collating sequence is used. </p> | | | 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 | used for sorting. ^Otherwise, if the expression sorted by an ORDER BY clause is a column, then the collating sequence of the column is used to determine sort order. ^If the expression is not a column and has no COLLATE clause, then the BINARY collating sequence is used. </p> <h2>Collation Sequence Examples</h2> <p> The examples below identify the collating sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required, and no collating sequence used, in the case of numeric, blob or NULL values. </p> ^(<blockquote> |
︙ | ︙ |
Changes to pages/rbu.in.
1 2 3 4 | <title>The RBU Extension</title> <tcl> hd_keywords {RBU} {RBU extension} proc CODE {text} { | | | | | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <title>The RBU Extension</title> <tcl> hd_keywords {RBU} {RBU extension} proc CODE {text} { #hd_puts "<blockquote><pre>" #hd_puts $text #hd_puts "</pre></blockquote>" hd_puts [CodeBlock $text] } </tcl> <table_of_contents> <h1 align='center'>The RBU Extension</h1> <p>The RBU extension is an add-on for SQLite designed for use with large SQLite database files on low-power devices at the edge of a network. RBU may be used for two separate tasks: <ul> |
︙ | ︙ |
Changes to search/parsehtml.c.
︙ | ︙ | |||
114 115 116 117 118 119 120 121 122 123 124 125 126 127 | rc = TCL_OK; } tokenizerClose(&sToken); return rc; } /* ** Tcl command: parsehtml HTML SCRIPT */ static int parsehtmlcmd( ClientData clientData, Tcl_Interp *interp, | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | rc = TCL_OK; } tokenizerClose(&sToken); return rc; } static int doTextCallback( Tcl_Interp *interp, Tcl_Obj **aCall, int nElem, const char *zText, int nText ){ int rc; Tcl_IncrRefCount( aCall[nElem] = Tcl_NewObj() ); Tcl_IncrRefCount( aCall[nElem+1] = Tcl_NewStringObj(zText, nText) ); rc = Tcl_EvalObjv(interp, nElem+2, aCall, 0); Tcl_DecrRefCount( aCall[nElem] ); Tcl_DecrRefCount( aCall[nElem+1] ); return rc; } static int doTagCallback( Tcl_Interp *interp, Tcl_Obj **aCall, int nElem, const char *zTag, int nTag, Tcl_Obj *pParam ){ int rc; Tcl_Obj *pArg = pParam; if( pArg==0 ) pArg = Tcl_NewObj(); Tcl_IncrRefCount( aCall[nElem] = Tcl_NewStringObj(zTag, nTag) ); Tcl_IncrRefCount( aCall[nElem+1] = pArg ); rc = Tcl_EvalObjv(interp, nElem+2, aCall, 0); Tcl_DecrRefCount( aCall[nElem] ); Tcl_DecrRefCount( aCall[nElem+1] ); return rc; } /* ** Tcl command: parsehtml HTML SCRIPT */ static int parsehtmlcmd( ClientData clientData, Tcl_Interp *interp, |
︙ | ︙ | |||
150 151 152 153 154 155 156 | z = zHtml; while( *z ){ char *zText = z; while( *z && *z!='<' ) z++; /* Invoke the callback script for the chunk of text just parsed. */ | < | < < < | 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 | z = zHtml; while( *z ){ char *zText = z; while( *z && *z!='<' ) z++; /* Invoke the callback script for the chunk of text just parsed. */ rc = doTextCallback(interp, aCall, nElem, zText, z-zText); if( rc!=TCL_OK ) return rc; /* Unless is at the end of the document, z now points to the start of a ** markup tag. Either an opening or a closing tag. Parse it up and ** invoke the callback script. */ if( *z ){ int nTag; |
︙ | ︙ | |||
220 221 222 223 224 225 226 | } Tcl_ListObjAppendElement(interp,pParam,Tcl_NewStringObj(zVal,nVal)); }else if( zAttr ){ Tcl_ListObjAppendElement(interp, pParam, Tcl_NewIntObj(1)); } } | | | | | | > > | > > > > | 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 | } Tcl_ListObjAppendElement(interp,pParam,Tcl_NewStringObj(zVal,nVal)); }else if( zAttr ){ Tcl_ListObjAppendElement(interp, pParam, Tcl_NewIntObj(1)); } } rc = doTagCallback(interp, aCall, nElem, zTag, nTag, pParam); if( rc!=TCL_OK ) return rc; if( nTag==3 && memcmp(zTag, "tcl", 3)==0 ){ const char *zText = &z[1]; while( *z && strncasecmp("</tcl>", z, 6) ) z++; rc = doTextCallback(interp, aCall, nElem, zText, z-zText); if( rc!=TCL_OK ) return rc; rc = doTagCallback(interp, aCall, nElem, "/tcl", 4, 0); if( rc!=TCL_OK ) return rc; if( *z ) z++; } } while( *z && !ISSPACE(*z) && *z!='>' ) z++; if( *z ) z++; } } |
︙ | ︙ |