Documentation Source Text

Check-in [3da8351ec8]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix some typos in datatype3.html.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3da8351ec80491d921a3a657e8f384941795bba3
User & Date: dan 2009-12-30 15:32:56
Context
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
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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 &lt; 40,   b &lt; 60,   b &lt; 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 &lt; '40', b &lt; '60', b &lt; '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.