/ Check-in [915cba57]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix typos in version 3 documents. (CVS 1518)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 915cba57123f859c7f128bd52580573877c502ba
User & Date: drh 2004-06-01 10:01:25
Context
2004-06-01
12:59
Fix typo on homepage (CVS 1519) check-in: a84ece25 user: drh tags: trunk
10:01
Fix typos in version 3 documents. (CVS 1518) check-in: 915cba57 user: drh tags: trunk
01:45
Makefile changes for building windows DLLs. (CVS 1517) check-in: 11c5d6ab user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to www/capi3.tcl.

1
2
3
4
5
6
7
8
...
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
set rcsid {$Id: capi3.tcl,v 1.2 2004/05/31 18:51:59 drh Exp $}
source common.tcl
header {C/C++ Interface For SQLite Version 3}
puts {
<h2>C/C++ Interface For SQLite Version 3</h2>

<h3>1.0 Overview</h3>

................................................................................
</pre></blockquote>

<p>
The sqlite3_create_collation() function specifies a collating sequence name
and a comparison function to implement that collating sequence.  The
comparison function is only used for comparing text values.  The eTextRep
parameter is one of SQLITE3_UTF8, SQLITE3_UTF16LE, SQLITE3_UTF16BE, or
SQLITE3_ANY to specify which text representation to comparison function works
with.  Separate comparison functions can exist for the same collating
sequence for each of the UTF-8, UTF-16LE and UTF-16BE text representations.
The sqlite3_create_collation16() works like sqlite3_create_collation() except
that the collation name is specified in UTF-16 host byte order instead of
in UTF-8.
</p>

|







 







|







1
2
3
4
5
6
7
8
...
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
set rcsid {$Id: capi3.tcl,v 1.3 2004/06/01 10:01:25 drh Exp $}
source common.tcl
header {C/C++ Interface For SQLite Version 3}
puts {
<h2>C/C++ Interface For SQLite Version 3</h2>

<h3>1.0 Overview</h3>

................................................................................
</pre></blockquote>

<p>
The sqlite3_create_collation() function specifies a collating sequence name
and a comparison function to implement that collating sequence.  The
comparison function is only used for comparing text values.  The eTextRep
parameter is one of SQLITE3_UTF8, SQLITE3_UTF16LE, SQLITE3_UTF16BE, or
SQLITE3_ANY to specify which text representation the comparison function works
with.  Separate comparison functions can exist for the same collating
sequence for each of the UTF-8, UTF-16LE and UTF-16BE text representations.
The sqlite3_create_collation16() works like sqlite3_create_collation() except
that the collation name is specified in UTF-16 host byte order instead of
in UTF-8.
</p>

Changes to www/datatype3.tcl.

1
2
3
4
5
6
7
8
..
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
..
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
...
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
...
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
set rcsid {$Id: datatype3.tcl,v 1.2 2004/05/31 18:51:59 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

................................................................................

<h3>2. Column Affinity</h3>

<p>
In SQLite version 3, the type of a value is associated with the value
itself, not with the column or variable in which the value is stored.
(This is sometimes called
<a href="http://www.cliki.net/manifest%20type%50system">
manifest typing</a>.)
All other SQL databases engines that we are aware of use the more
restrict system of static typing where the type is associated with
the container, not the value.
</p>

<p>
In order to maximize compatibility between SQLite and other database
engines, SQLite support the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
................................................................................
one storage class over another.  The preferred storage class for
a column is called its "affinity".
</p>

<P>Each column in an SQLite 3 database is assigned one of the
following type affinities:</P>
<UL>
	<LI><P>TEXT.</P>
	<LI><P>NUMERIC.</P>
	<LI><P>INTEGER.</P>
	<LI><P>NONE.</P>
</UL>

<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 to text form before being stored.</P>

<P>A column with NUMERIC affinity may contain values using all five
................................................................................
	<LI><P>If the datatype of the column contains any of the strings
	&quot;CHAR&quot;, &quot;CLOB&quot;, or &quot;TEXT&quot; then that
	column has TEXT affinity. Notice that the type VARCHAR contains the
	string &quot;CHAR&quot; and is thus assigned TEXT affinity.</P>

	<LI><P>If the datatype for a column
         contains the string &quot;BLOB&quot; or if
        not datatype is specified then the column has affinity NONE.</P>

	<LI><P>Otherwise, the affinity is NUMERIC.  Notice that a column
        where no datatype is specified is given affinity NUMERIC.</P>
</OL>

<P>If a table is created using a "CREATE TABLE &lt;table&gt; AS
SELECT..." statement, then all columns have no datatype specified
and they are given no affinity.</P>

<h4>2.2 Column Affinity Example</h4>
................................................................................
	<LI><P>When two BLOB values are compared, the result is always
	determined using memcmp().</P>
</UL>

<P>SQLite may attempt to convert values between the numeric storage
classes (INTEGER and REAL) and TEXT before performing a comparison.
For binary comparisons, this is done in the cases enumerated below.
The term expression used in the bullet points below means any
SQL scalar expression or literal other than a column value.</P>
<UL>
	<LI><P>When a column value is compared to the result of an
	expression, the affinity of the column is applied to the result of
	the expression before the comparison takes place.</P>

	<LI><P>When two column values are compared, if one column has
|







 







|


|







 







|
|
|
|







 







|

|
<







 







|







1
2
3
4
5
6
7
8
..
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
..
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
...
121
122
123
124
125
126
127
128
129
130

131
132
133
134
135
136
137
...
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
set rcsid {$Id: datatype3.tcl,v 1.3 2004/06/01 10:01:25 drh Exp $}
source common.tcl
header {Datatypes In SQLite Version 3}
puts {
<h2>Datatypes In SQLite Version 3</h2>

<h3>1. Storage Classes</h3>

................................................................................

<h3>2. Column Affinity</h3>

<p>
In SQLite version 3, the type of a value is associated with the value
itself, not with the column or variable in which the value is stored.
(This is sometimes called
<a href="http://www.cliki.net/manifest%20type%20system">
manifest typing</a>.)
All other SQL databases engines that we are aware of use the more
restrictive system of static typing where the type is associated with
the container, not the value.
</p>

<p>
In order to maximize compatibility between SQLite and other database
engines, SQLite support the concept of "type affinity" on columns.
The type affinity of a column is the recommended type for data stored
................................................................................
one storage class over another.  The preferred storage class for
a column is called its "affinity".
</p>

<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>NONE</LI>
</UL>

<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 to text form before being stored.</P>

<P>A column with NUMERIC affinity may contain values using all five
................................................................................
	<LI><P>If the datatype of the column contains any of the strings
	&quot;CHAR&quot;, &quot;CLOB&quot;, or &quot;TEXT&quot; then that
	column has TEXT affinity. Notice that the type VARCHAR contains the
	string &quot;CHAR&quot; and is thus assigned TEXT affinity.</P>

	<LI><P>If the datatype for a column
         contains the string &quot;BLOB&quot; or if
        no datatype is specified then the column has affinity NONE.</P>

	<LI><P>Otherwise, the affinity is NUMERIC.</P>

</OL>

<P>If a table is created using a "CREATE TABLE &lt;table&gt; AS
SELECT..." statement, then all columns have no datatype specified
and they are given no affinity.</P>

<h4>2.2 Column Affinity Example</h4>
................................................................................
	<LI><P>When two BLOB values are compared, the result is always
	determined using memcmp().</P>
</UL>

<P>SQLite may attempt to convert values between the numeric storage
classes (INTEGER and REAL) and TEXT before performing a comparison.
For binary comparisons, this is done in the cases enumerated below.
The term "expression" used in the bullet points below means any
SQL scalar expression or literal other than a column value.</P>
<UL>
	<LI><P>When a column value is compared to the result of an
	expression, the affinity of the column is applied to the result of
	the expression before the comparison takes place.</P>

	<LI><P>When two column values are compared, if one column has