Documentation Source Text

	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 -DUSE_TCL_STUBS $(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:	

clean:	
	rm -rf tclsh doc sqlite3.h

	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:	

clean:	
	rm -rf tclsh doc sqlite3.h

<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>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 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
................................................................................
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 NONE 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
................................................................................
  <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 NONE.</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>
................................................................................
  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">NONE
<td align="center">3

<tr><td align="center" valign="top">
  REAL<br>
  DOUBLE<br>
  DOUBLE PRECISION<br>
  FLOAT
................................................................................

<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 affinity of the operands.




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 is NONE if the operand is a list and is the same 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 NONE 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 NONE affinity
then NUMERIC affinity is applied to other operand.

<li><p>^If one operand has TEXT affinity and the other has NONE 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

<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
................................................................................
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
................................................................................
  <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>
................................................................................
  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
................................................................................

<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