Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Update documentation to better explain the typelessness of SQLite and to describe the distinction between text and numeric data. (CVS 711) |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
4ff0f578eca4a8672cf570923e6c015c |
| User & Date: | drh 2002-08-14 00:08:13.000 |
Context
|
2002-08-14
| ||
| 00:10 | Fix for ticket #132: make the working directory the last choice for where to write temporary files, not the first choice. (CVS 712) (check-in: 26a4e7e7a3 user: drh tags: trunk) | |
| 00:08 | Update documentation to better explain the typelessness of SQLite and to describe the distinction between text and numeric data. (CVS 711) (check-in: 4ff0f578ec user: drh tags: trunk) | |
|
2002-08-13
| ||
| 23:02 | Make the distinction between text and numeric data. (CVS 710) (check-in: 310ac4fbaf user: drh tags: trunk) | |
Changes
Changes to main.mk.
| ︙ | ︙ | |||
295 296 297 298 299 300 301 302 303 304 305 306 307 308 | download.html: $(TOP)/www/download.tcl tclsh $(TOP)/www/download.tcl >download.html omitted.html: $(TOP)/www/omitted.tcl tclsh $(TOP)/www/omitted.tcl >omitted.html # Files to be published on the website. # DOC = \ index.html \ sqlite.html \ changes.html \ | > > > | 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 | download.html: $(TOP)/www/download.tcl tclsh $(TOP)/www/download.tcl >download.html omitted.html: $(TOP)/www/omitted.tcl tclsh $(TOP)/www/omitted.tcl >omitted.html datatypes.html: $(TOP)/www/datatypes.tcl tclsh $(TOP)/www/datatypes.tcl >datatypes.html # Files to be published on the website. # DOC = \ index.html \ sqlite.html \ changes.html \ |
| ︙ | ︙ | |||
316 317 318 319 320 321 322 | mingw.html \ tclsqlite.html \ download.html \ speed.html \ faq.html \ formatchng.html \ conflict.html \ | | > | 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 | mingw.html \ tclsqlite.html \ download.html \ speed.html \ faq.html \ formatchng.html \ conflict.html \ omitted.html \ datatypes.html doc: $(DOC) mkdir -p doc mv $(DOC) doc install: sqlite libsqlite.a sqlite.h mv sqlite /usr/bin |
| ︙ | ︙ |
Added www/datatypes.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 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 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 |
#
# Run this script to generated a datatypes.html output file
#
set rcsid {$Id: datatypes.tcl,v 1.1 2002/08/14 00:08:13 drh Exp $}
puts {<html>
<head>
<title>Datatypes In SQLite</title>
</head>
<body bgcolor="white">
<h1 align="center">
Datatypes In SQLite
</h1>
}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"
puts {<h2>1.0 Typelessness</h2>
<p>
SQLite is "typeless". This means that you can store any
kind of data you want in any column of any table, regardless of the
declared datatype of that column.
(See the one exception to this rule in section 2.0 below.)
This behavior is a feature, not
a bug. A database is suppose to store and retrieve data and it
should not matter to the database what format that data is in.
The strong typing system found in most other SQL engines and
codified in the SQL language spec is a misfeature -
it is an example of the implementation showing through into the
interface. SQLite seeks to overcome this misfeature by allowing
you to store any kind of data into any kind of column and by
allowing flexibility in the specification of datatypes.
</p>
<p>
A datatype to SQLite is any sequence of zero or more names
optionally followed by a parenthesized lists of one or two
signed integers. Notice in particular that a datatype may
be <em>zero</em> or more names. That means that an empty
string is a valid datatype as far as SQLite is concerned.
So you can declare tables where the datatype of each column
is left unspecified, like this:
</p>
<blockquote><pre>
CREATE TABLE ex1(a,b,c);
</pre></blockquote>
<p>
Even though SQLite allows the datatype to be omitted, it is
still a good idea to include it in your CREATE TABLE statements,
since the data type often serves as a good hint to other
programmers about what you intend to put in the column. And
if you ever port your code to another database engine, that
other engine will probably require a datatype of some kind.
SQLite accepts all the usual datatypes. For example:
</p>
<blockquote><pre>
CREATE TABLE ex2(
a VARCHAR(10),
b NVARCHAR(15),
c TEXT,
d INTEGER,
e FLOAT,
f BOOLEAN,
g CLOB,
h BLOB,
i TIMESTAMP,
j NUMERIC(10,5)
k VARYING CHARACTER (24),
l NATIVE VARYING CHAR(16)
);
</pre></blockquote>
<p>
And so forth. Basically any sequence of names optionally followed by
one or two signed integers in parentheses will do.
</p>
<h2>2.0 The INTEGER PRIMARY KEY</h2>
<p>
One exception to the typelessness of SQLite is a column whose type
is INTEGER PRIMARY KEY. (And you must use "INTEGER" not "INT".
A column of type INT PRIMARY KEY is typeless just like any other.)
INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any
attempt to insert non-integer data will result in an error.
</p>
<p>
INTEGER PRIMARY KEY columns can be used to implement the equivalent
of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY
KEY column, the column will actually be filled with a integer that is
one greater than the largest key already in the table. Or if the
largest key is 2147483647, then the column will be filled with a
random integer. Either way, the INTEGER PRIMARY KEY column will be
assigned a unique integer. You can retrieve this integer using
the <b>sqlite_last_insert_rowid()</b> API function or using the
<b>last_insert_rowid()</b> SQL function is a subsequent SELECT statement.
</p>
<h2>3.0 Comparison and Sort Order</h2>
<p>
SQLite is typeless for the purpose of deciding what data is allowed
to be stored in a column. But some notion of type comes into play
when sorting and comparing data. For these purposes, a column or
an expression can be one of two types: <b>numeric</b> and <b>text</b>.
The sort or comparison may give different results depending on which
type of data is being sorted or compared.
</p>
<p>
If data is of type <b>text</b> then the comparison is determined by
the standard C data comparison functions <b>memcmp()</b> or
<b>strcmp()</b>. The comparison looks at bytes from two inputs one
by one and returns the first non-zero difference.
String are '\000' terminated so shorter
strings sort before longer strings, as you would expect.
</p>
<p>
For numeric data, this situation is more complex. If both strings
being compared look like well-formed numbers, then they are converted
into floating point values using <b>atof()</b> and compared numerically.
If one input is not a well-formed number but the other is, then the
number is considered to be less than the non-number. If neither inputs
is a well-formed number, then <b>strcmp()</b> is used to do the
comparison.
</p>
<p>
Do not be confused by the fact that a column might have a "numeric"
datatype. This does not mean that the column can contain only numbers.
It merely means that if the column does contain a number, that number
will sort in numerical order.
</p>
<p>
For both text and numeric values, NULL sorts before any other value.
A comparison of any value against NULL using operators like "<" or
">=" is always false.
</p>
<h2>4.0 How SQLite Determines Datatypes</h2>
<p>
For SQLite version 2.6.3 and earlier, all values used the numeric datatype.
The text datatype appears in version 2.7.0 and later. In the sequel it
is assumed that you are using version 2.7.0 or later of SQLite.
</p>
<p>
For an expression, the datatype of the result is often determined by
the outermost operator. For example, arithmatic operators ("+", "*", "%")
always return a numeric results. The string concatenation operator
("||") returns a text result. And so forth. If you are ever in doubt
about the datatype of an expression you can use the special <b>typeof()</b>
SQL function to determine what the datatype is. For example:
</p>
<blockquote><pre>
sqlite> SELECT typeof('abc'+123);
numeric
sqlite> SELECT typeof('abc'||123);
text
</pre></blockquote>
<p>
For table columns, the datatype is determined by the datatype declaration
of the CREATE TABLE statement. The datatype is text if and only if
the type declaration contains one or more of the following strings:
</p>
<blockquote>
BLOB<br>
CHAR<br>
CLOB</br>
TEXT
</blockquote>
<p>
The search for these strings in the type declaration is case insensitive,
of course. If any of the above strings occur anywhere in the type
declaration, then the datatype of the column is text. Otherwise the
datatype is numeric. Note in particular that the datatype for columns
with an empty type declaration is numeric.
</p>
}
puts {
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>
</body></html>}
|
Changes to www/index.tcl.
1 2 3 | # # Run this TCL script to generate HTML for the index.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 |
#
# Run this TCL script to generate HTML for the index.html file.
#
set rcsid {$Id: index.tcl,v 1.65 2002/08/14 00:08:13 drh Exp $}
puts {<html>
<head><title>SQLite: An SQL Database Engine In A C Library</title></head>
<body bgcolor=white>
<h1 align=center>SQLite: An SQL Database Engine In A C Library</h1>
<p align=center>}
puts "This page was last modified on [lrange $rcsid 3 4] UTC<br>"
|
| ︙ | ︙ | |||
128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
<p>The following documentation is currently available:</p>
<p><ul>
<li><a href="faq.html">Frequently Asked Questions</a> are available online.</li>
<li>Information on the <a href="sqlite.html">sqlite</a>
command-line utility.</li>
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="tclsqlite.html">Tcl Binding</a> to SQLite.</li>
<li>The <a href="arch.html">Architecture of the SQLite Library</a> describes
how the library is put together.</li>
<li>A description of the <a href="opcode.html">virtual machine</a> that
SQLite uses to access the database.</li>
| > | 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 |
<p>The following documentation is currently available:</p>
<p><ul>
<li><a href="faq.html">Frequently Asked Questions</a> are available online.</li>
<li>Information on the <a href="sqlite.html">sqlite</a>
command-line utility.</li>
<li>SQLite is <a href="datatypes.html">typeless</a>.
<li>The <a href="lang.html">SQL Language</a> subset understood by SQLite.</li>
<li>The <a href="c_interface.html">C/C++ Interface</a>.</li>
<li>The <a href="tclsqlite.html">Tcl Binding</a> to SQLite.</li>
<li>The <a href="arch.html">Architecture of the SQLite Library</a> describes
how the library is put together.</li>
<li>A description of the <a href="opcode.html">virtual machine</a> that
SQLite uses to access the database.</li>
|
| ︙ | ︙ |
Changes to www/lang.tcl.
1 2 3 | # # Run this Tcl script to generate the sqlite.html file. # | | | 1 2 3 4 5 6 7 8 9 10 11 |
#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: lang.tcl,v 1.42 2002/08/14 00:08:13 drh Exp $}
puts {<html>
<head>
<title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
|
| ︙ | ︙ | |||
278 279 280 281 282 283 284 | followed by the name of a new table and a parenthesized list of column definitions and constraints. The table name can be either an identifier or a string. Tables names that begin with "<b>sqlite_</b>" are reserved for use by the engine.</p> <p>Each column definition is the name of the column followed by the datatype for that column, then one or more optional column constraints. | > | > | 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 | followed by the name of a new table and a parenthesized list of column definitions and constraints. The table name can be either an identifier or a string. Tables names that begin with "<b>sqlite_</b>" are reserved for use by the engine.</p> <p>Each column definition is the name of the column followed by the datatype for that column, then one or more optional column constraints. SQLite is <a href="datatypes.html">typeless</a>. The datatype for the column does not constraint what data may be put in that column. All information is stored as null-terminated strings. The UNIQUE constraint causes an index to be created on the specified columns. This index must contain unique keys. The DEFAULT constraint specifies a default value to use when doing an INSERT. </p> |
| ︙ | ︙ |
Added www/omitted.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
#
# Run this script to generated a omitted.html output file
#
set rcsid {$Id: omitted.tcl,v 1.1 2002/08/14 00:08:14 drh Exp $}
puts {<html>
<head>
<title>SQL Features That SQLite Does Not Implement</title>
</head>
<body bgcolor="white">
<h1 align="center">
SQL Features That SQLite Does Not Implement
</h1>
}
puts "<p align=center>
(This page was last modified on [lrange $rcsid 3 4] UTC)
</p>"
puts {
<p>
Rather than try to list all the features of SQL92 that SQLite does
support, it is much easier to list those that it does not.
The following are features of of SQL92 that SQLite does not implement.
</p>
<table cellpadding="10">
}
proc feature {name desc} {
puts "<tr><td valign=\"top\"><b><nobr>$name</nobr></b></td>"
puts "<td valign=\"top\">$desc</td></tr>"
}
feature {RIGHT and FULL OUTER JOIN} {
LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or
FULL OUTER JOIN.
}
feature {CHECK constraints} {
CHECK constraints are parsed but they are not enforced.
NOT NULL and UNIQUE constraints are enforced, however.
}
feature {FOREIGN KEY constraints} {
FOREIGN KEY constraints are parsed but are ignored.
}
feature {GRANT and REVOKE} {
Since SQLite reads and writes an ordinary disk file, the
only access permissions that can be applied are the normal
file access permissions of the underlying operating system.
The GRANT and REVOKE commands commonly found on client/server
RDBMSes are not implemented because they would be meaningless
for an embedded database engine.
}
feature {DELETE, INSERT, and UPDATE on VIEWs} {
VIEWs in SQLite are read-only. But you can create a trigger
that fires on an attempt to DELETE, INSERT, or UPDATE a view and do
what you need in the body of the trigger.
}
feature {ALTER TABLE} {
To change a table you have to delete it (saving its contents to a temporary
table) and recreate it from scratch.
}
feature {The COUNT(DISTINCT X) function} {
You can accomplish the same thing using a subquery, like this:<br />
SELECT count(x) FROM (SELECT DISTINCT x FROM tbl);
}
feature {Variable subqueries} {
Subqueries must be static. They are evaluated only once. They must not,
therefore, refer to variables in the containing query.
}
puts {
</table>
<p>
If you find other SQL92 features that SQLite does not support, please
send e-mail to <a href="mailto:drh@hwaci.com">drh@hwaci.com</a> so they
can be added to this list.
</p>
<p><hr /></p>
<p><a href="index.html"><img src="/goback.jpg" border=0 />
Back to the SQLite Home Page</a>
</p>
</body></html>}
|