Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Clarify the comparison affinity documentation. Change the name of NONE affinity to BLOB. Update the Makefile so that it works with Ubuntu 14.04LTS. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9ba9e670e1559988c843ceeae2422634 |
User & Date: | drh 2015-06-02 17:24:30.795 |
Context
2015-06-02
| ||
19:27 | Add syntax for multi-column matches to fts5.in. (check-in: ec116c4004 user: dan tags: trunk) | |
17:24 | Clarify the comparison affinity documentation. Change the name of NONE affinity to BLOB. Update the Makefile so that it works with Ubuntu 14.04LTS. (check-in: 9ba9e670e1 user: drh tags: trunk) | |
2015-05-30
| ||
20:51 | Fix a typo in the SQLITE_BUSY_SNAPSHOT documentation. (check-in: fe158a6ae2 user: drh tags: trunk) | |
Changes
Changes to main.mk.
︙ | ︙ | |||
139 140 141 142 143 144 145 | rm -rf doc/matrix/images cp -r doc/images doc/matrix ./tclsh $(DOC)/matrix.tcl # Build the fts3 database used by the search script # parsehtml.so: $(DOC)/search/parsehtml.c | | | 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 | rm -rf doc/matrix/images cp -r doc/images doc/matrix ./tclsh $(DOC)/matrix.tcl # Build the fts3 database used by the search script # parsehtml.so: $(DOC)/search/parsehtml.c gcc -g -shared -fPIC $(TCLINC) -I. -I$(SRC)/ext/fts3 $(DOC)/search/parsehtml.c $(TCLSTUBFLAGS) -o parsehtml.so searchdb: parsehtml.so tclsh ./tclsh $(DOC)/search/buildsearchdb.tcl cp $(DOC)/search/search.tcl doc/search chmod +x doc/search always: |
︙ | ︙ |
Changes to pages/datatype3.in.
︙ | ︙ | |||
105 106 107 108 109 110 111 | <p>^(Each column in an SQLite 3 database is assigned one of the following type affinities:</p> <ul> <li>TEXT</LI> <li>NUMERIC</LI> <li>INTEGER</LI> <li>REAL</li> | | > > > > | 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | <p>^(Each column in an SQLite 3 database is assigned one of the following type affinities:</p> <ul> <li>TEXT</LI> <li>NUMERIC</LI> <li>INTEGER</LI> <li>REAL</li> <li>BLOB</LI> </ul>)^ <p>(Historical note: The "BLOB" type affinity used to be called "NONE". But that term was easy to confuse with "no affinity" and so it was renamed.) <p>^A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. ^If numerical data is inserted into a column with TEXT affinity it is converted into text form before being stored.</p> <p>^A column with NUMERIC affinity may contain values using all five storage classes. ^When text data is inserted into a NUMERIC column, the |
︙ | ︙ | |||
144 145 146 147 148 149 150 | values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. ^This optimization is completely invisible at the SQL level and can only be detected by examining the raw bits of the database file.)</p> | | | | 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 | values with no fractional component and stored in columns with REAL affinity are written to disk as integers in order to take up less space and are automatically converted back into floating point as the value is read out. ^This optimization is completely invisible at the SQL level and can only 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> <h3>2.1 Determination Of Column Affinity</h3> <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> <li><p>^If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. ^Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.</p> <li><p>^If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.</p> <li><p>^If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.</p> <li><p>^Otherwise, the affinity is NUMERIC.</p> </ol> |
︙ | ︙ | |||
225 226 227 228 229 230 231 | CLOB <td align="center">TEXT <td align="center">2 <tr><td align="center" valign="top"> BLOB<br> <i>no datatype specified</i> | | | 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 | CLOB <td align="center">TEXT <td align="center">2 <tr><td align="center" valign="top"> BLOB<br> <i>no datatype specified</i> <td align="center">BLOB <td align="center">3 <tr><td align="center" valign="top"> REAL<br> DOUBLE<br> DOUBLE PRECISION<br> FLOAT |
︙ | ︙ | |||
330 331 332 333 334 335 336 | <tcl>hd_fragment expraff {expression affinity}</tcl> <h3>3.2 Affinity Of Comparison Operands</h3> <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 | | > > > > | | | > | | | | 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 | <tcl>hd_fragment expraff {expression affinity}</tcl> <h3>3.2 Affinity Of Comparison Operands</h3> <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, REAL, or NUMERIC) but expressions do no necessarily have an affinity. <p>Operand affinity is determined by the following rules: <ul> <li><p>^The affinity of the right-hand operand of an IN or NOT IN operator has no affinity if the operand is a list and has the same affinity as the affinity of the result set expression if the operand is a SELECT. <li><p>^An expression that is a simple reference to a column value has the same affinity as the column. ^(Note that if X and Y.Z are column names, then +X and +Y.Z are considered expressions for the purpose of determining affinity.)^</p> <li><p>^(An expression of the form "CAST(<i>expr</i> AS <i>type</i>)" 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> <h3>3.3 Type Conversions Prior To Comparison</h3> <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> <li><p>^If one operand has INTEGER, REAL or NUMERIC affinity and the other operand has TEXT or BLOB or no affinity then NUMERIC affinity is applied to other operand. <li><p>^If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand. <li><p>^Otherwise, no affinity is applied and both operands are compared as is.</p> </ul> <p>^(The expression "a BETWEEN b AND c" is treated as two separate |
︙ | ︙ |