Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Fix some typos in datatype3.html. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
3da8351ec80491d921a3a657e8f38494 |
| User & Date: | dan 2009-12-30 15:32:56 |
|
2009-12-30
| ||
| 18:17 | The FLOATING POINT datatype has INTEGER affinity due to the "INT" at the end of "POINT". Make this clear in the datatype3.html documentation file. (check-in: 5c8750d0ff user: drh tags: trunk) | |
| 15:32 | Fix some typos in datatype3.html. (check-in: 3da8351ec8 user: dan tags: trunk) | |
| 14:53 | Update the documentation in preparation for the release of 3.6.22. (check-in: 758f10c6da user: drh tags: trunk) | |
Changes to pages/datatype3.in.
9 9 which the value is stored.</p> 10 10 11 11 <p>SQLite uses a more general dynamic type system. In SQLite, the datatype 12 12 of a value is associated with the value itself, not with its container. 13 13 The dynamic type system of SQLite is backwards 14 14 compatible with the more common static type systems of other database engines 15 15 in the sense that SQL statement that work on statically typed databases should 16 -would the same way in SQLite. However, the dynamic typing in SQLite allowed 16 +work the same way in SQLite. However, the dynamic typing in SQLite allows 17 17 it to do things which are not possible in traditional rigidly typed 18 18 databases.</p> 19 19 20 20 <h2>1.0 Storage Classes and Datatypes</h2> 21 21 22 22 <p>Each value stored in an SQLite database (or manipulated by the 23 23 database engine) has one of the following storage classes:</p> ................................................................................ 28 28 <li><p><B>INTEGER</B>. The value is a signed integer, stored in 1, 29 29 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.</p> 30 30 31 31 <li><p><B>REAL</B>. The value is a floating point value, stored as 32 32 an 8-byte IEEE floating point number.</p> 33 33 34 34 <li><p><B>TEXT</B>. The value is a text string, stored using the 35 - database encoding (UTF-8, UTF-16BE or UTF-16-LE).</p> 35 + database encoding (UTF-8, UTF-16BE or UTF-16LE).</p> 36 36 37 37 <li><p><B>BLOB</B>. The value is a blob of data, stored exactly as 38 38 it was input.</p> 39 39 </ul> 40 40 41 41 <p>Note that a storage class is slightly more general than a datatype. 42 42 The INTEGER storage class, for example, includes 6 different integer 43 -datatypes of different lengths. This makes a difference on-disk. But 43 +datatypes of different lengths. This makes a difference on disk. But 44 44 as soon as INTEGER values are read off of disk and into memory for processing, 45 45 they are converted to the most general datatype (8-byte signed integer). 46 46 And so for the most part, "storage class" is indistinguishable from 47 47 "datatype" and the two terms can be used interchangably.</p> 48 48 49 49 <p>^Any column in an SQLite version 3 database, 50 50 except an [INTEGER PRIMARY KEY] column, may be used to store a value 51 51 of any storage class.</p> 52 52 53 -<p>All values in SQL statements, whether as literals embedded in SQL 53 +<p>All values in SQL statements, whether they are literals embedded in SQL 54 54 statement text or [parameters] bound to 55 55 [prepared statements | pre-compiled SQL statements] 56 56 have an implicit storage class. 57 57 Under circumstances described below, the 58 58 database engine may convert values between numeric storage classes 59 59 (INTEGER and REAL) and TEXT during query execution. 60 60 </p> ................................................................................ 90 90 <tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl> 91 91 <h2>2.0 Type Affinity</h2> 92 92 93 93 <p> 94 94 In order to maximize compatibility between SQLite and other database 95 95 engines, SQLite supports the concept of "type affinity" on columns. 96 96 The type affinity of a column is the recommended type for data stored 97 -in that column. The important ideay here is that the type is recommended, not 98 -required. Any column can still store any type of data, in theory. 97 +in that column. The important idea here is that the type is recommended, not 98 +required. Any column can still store any type of data. 99 99 It is just that some columns, given the choice, will prefer to use 100 100 one storage class over another. The preferred storage class for 101 101 a column is called its "affinity". 102 102 </p> 103 103 104 104 <p>^(Each column in an SQLite 3 database is assigned one of the 105 105 following type affinities:</p> ................................................................................ 153 153 <tcl>hd_fragment affname {rules for determining column affinity}</tcl> 154 154 <h3>2.1 Determination Of Column Affinity</h3> 155 155 156 156 <p>^(The affinity of a column is determined by the declared type 157 157 of the column, according to the following rules in the order shown:)^</p> 158 158 159 159 <ol> 160 - <li><p>^If the datatype contains the string "INT" then it 160 + <li><p>^If the declared type contains the string "INT" then it 161 161 is assigned INTEGER affinity.</p> 162 162 163 - <li><p>^If the datatype of the column contains any of the strings 163 + <li><p>^If the declared type of the column contains any of the strings 164 164 "CHAR", "CLOB", or "TEXT" then that 165 165 column has TEXT affinity. ^Notice that the type VARCHAR contains the 166 166 string "CHAR" and is thus assigned TEXT affinity.</p> 167 167 168 - <li><p>^If the datatype for a column 168 + <li><p>^If the declared type for a column 169 169 contains the string "BLOB" or if 170 - no datatype is specified then the column has affinity NONE.</p> 170 + no type is specified then the column has affinity NONE.</p> 171 171 172 - <li><p>^If the datatype for a column 172 + <li><p>^If the declared type for a column 173 173 contains any of the strings "REAL", "FLOA", 174 174 or "DOUB" then the column has REAL affinity.</p> 175 175 176 176 <li><p>^Otherwise, the affinity is NUMERIC.</p> 177 177 </ol> 178 178 179 179 <p>^Note that the order of the rules for determining column affinity 180 -is important. ^A column whose declared datatype is "CHARINT" will match 180 +is important. ^A column whose declared type is "CHARINT" will match 181 181 both rules 1 and 2 but the first rule takes precedence and so the 182 182 column affinity will be INTEGER.</p> 183 183 184 184 <h3>2.2 Affinity Name Examples</h3> 185 185 186 -<p>The following table shows how many common data type names from 187 -legacy SQL are converted into affinities by the five rules of the 186 +<p>The following table shows how many common datatype names from 187 +more traditional SQL implementations are converted into affinities by the five rules of the 188 188 previous section. This table shows only a small subset of the 189 -data type names that SQLite will accept. Note that ^(numeric arguments 189 +datatype names that SQLite will accept. Note that ^(numeric arguments 190 190 in parentheses that following the type name (ex: "VARCHAR(255)") are 191 191 ignored)^ by SQLite - SQLite does not impose any length restrictions 192 192 (other than the large global [SQLITE_MAX_LENGTH] limit) on the length of 193 -strings or BLOBs or numeric values.</p> 193 +strings, BLOBs or numeric values.</p> 194 194 195 195 <blockquote> ^( 196 196 <table border="1" cellpadding="5"> 197 197 <tr><th>Example Typenames From The<br>CREATE TABLE Statement<br> 198 198 or CAST Expression 199 199 <th>Resulting Affinity 200 200 <th>Rule Used To Determine Affinity ................................................................................ 221 221 NVARCHAR(100)<br> 222 222 TEXT<br> 223 223 CLOB 224 224 <td align="center">TEXT 225 225 <td align="center">2 226 226 227 227 <tr><td align="center" valign="top"> 228 - BLOB 228 + BLOB<br> 229 + <i>no datatype specified</i> 229 230 <td align="center">NONE 230 231 <td align="center">3 231 232 232 233 <tr><td align="center" valign="top"> 233 234 REAL<br> 234 235 DOUBLE<br> 235 236 DOUBLE PRECISION<br> ................................................................................ 243 244 DECIMAL(10,5)<br> 244 245 BOOLEAN<br> 245 246 DATE<br> 246 247 DATETIME 247 248 <td align="center">NUMERIC 248 249 <td align="center">5 249 250 </table>)^ 250 -</blockquote>> 251 +</blockquote> 251 252 252 253 <h3>2.3 Column Affinity Behavior Example</h3> 253 254 254 255 <p>The following SQL demonstrates how SQLite uses column affinity 255 256 to do type conversions when values are inserted into a table.</p> 256 257 257 258 ^(<blockquote> ................................................................................ 269 270 SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; 270 271 text|integer|integer|real|text 271 272 272 273 -- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL. 273 274 DELETE FROM t1; 274 275 INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0); 275 276 SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; 276 -text|integer|integer|real|text 277 +text|integer|integer|real|real 277 278 278 279 -- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER. 279 280 DELETE FROM t1; 280 281 INSERT INTO t1 VALUES(500, 500, 500, 500, 500); 281 282 SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; 282 283 text|integer|integer|real|integer 283 284 ................................................................................ 347 348 <tcl>hd_fragment compaff {comparison affinity rules}</tcl> 348 349 <h3>3.3 Type Conversions Prior To Comparison</h3> 349 350 350 351 <p>Storage class conversions of operands prior to a binary comparison 351 352 operation occur according to the following rules in the order shown:</p> 352 353 353 354 <ul> 354 -<li><p>^If either operand has INTEGER or REAL or NUMERIC affinity and the 355 +<li><p>^If either operand has INTEGER, REAL or NUMERIC affinity and the 355 356 other operand does not, then NUMERIC 356 357 affinity is applied to the other operand. 357 358 358 -<li><p>^If either operand has TEXT affinity and the other does not 359 +<li><p>^If either operand has TEXT affinity and the other has no affinity, 359 360 then TEXT affinity is applied to the other operand. 360 361 361 362 <li><p>^Otherwise, no affinity is applied and both operands are compared 362 363 as is.</p> 363 364 </ul> 364 365 365 366 <p>In the comparison affinity rules above, to "apply affinity" means to ................................................................................ 412 413 -- Column "b" has numeric affinity and so numeric affinity is applied 413 414 -- to the operands on the right. Since the operands are already numeric, 414 415 -- the application of affinity is a no-op; no conversions occur. All 415 416 -- values are compared numerically. 416 417 SELECT b < 40, b < 60, b < 600 FROM t1; 417 418 0|0|1 418 419 419 --- Numeric affinity is applied to operands no the right, converting them 420 +-- Numeric affinity is applied to operands on the right, converting them 420 421 -- from text to integers. Then a numeric comparison occurs. 421 422 SELECT b < '40', b < '60', b < '600' FROM t1; 422 423 0|0|1 423 424 424 425 -- No affinity conversions occur. Right-hand side values all have 425 426 -- storage class INTEGER which are always less than the TEXT values 426 427 -- on the left.