Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Additional test cases and documentation updates. (CVS 717) |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
048b16c111693727482642e2a19a74a9 |
| User & Date: | drh 2002-08-15 11:48:13.000 |
Context
|
2002-08-15
| ||
| 13:45 | More documentation updates. (CVS 718) (check-in: 9c0400aa7a user: drh tags: trunk) | |
| 11:48 | Additional test cases and documentation updates. (CVS 717) (check-in: 048b16c111 user: drh tags: trunk) | |
| 01:26 | Fixes and test improvements resulting from code coverage testing. (CVS 716) (check-in: 66a0f6a8e2 user: drh tags: trunk) | |
Changes
Changes to test/misc1.test.
| ︙ | ︙ | |||
9 10 11 12 13 14 15 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for miscellanous features that were # left out of other test files. # | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for miscellanous features that were # left out of other test files. # # $Id: misc1.test,v 1.13 2002/08/15 11:48:13 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Test the creation and use of tables that have a large number # of columns. # |
| ︙ | ︙ | |||
315 316 317 318 319 320 321 322 323 |
} {1 {database is locked}}
do_test misc1-11.2 {
execsql {COMMIT}
set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
db2 close
lappend rc $msg
} {0 3}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 |
} {1 {database is locked}}
do_test misc1-11.2 {
execsql {COMMIT}
set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
db2 close
lappend rc $msg
} {0 3}
# Make sure string comparisons really do compare strings in format4+.
# Similar tests in the format3.test file show that for format3 and earlier
# all comparisions where numeric if either operand looked like a number.
#
do_test misc1-12.1 {
execsql {SELECT '0'=='0.0'}
} {0}
do_test misc1-12.2 {
execsql {SELECT '0'==0.0}
} {1}
do_test misc1-12.3 {
execsql {SELECT '12345678901234567890'=='12345678901234567891'}
} {0}
do_test misc1-12.4 {
execsql {
CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
INSERT INTO t6 VALUES('0','0.0');
SELECT * FROM t6;
}
} {0 0.0}
do_test misc1-12.5 {
execsql {
INSERT OR IGNORE INTO t6 VALUES(0.0,'x');
SELECT * FROM t6;
}
} {0 0.0}
do_test misc1-12.6 {
execsql {
INSERT OR IGNORE INTO t6 VALUES('y',0);
SELECT * FROM t6;
}
} {0 0.0 y 0}
do_test misc1-12.7 {
execsql {
CREATE TABLE t7(x INTEGER, y TEXT, z);
INSERT INTO t7 VALUES(0,0,1);
INSERT INTO t7 VALUES(0.0,0,2);
INSERT INTO t7 VALUES(0,0.0,3);
INSERT INTO t7 VALUES(0.0,0.0,4);
SELECT DISTINCT x, y FROM t7 ORDER BY z;
}
} {0 0 0 0.0}
finish_test
|
Changes to www/c_interface.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: c_interface.tcl,v 1.34 2002/08/15 11:48:14 drh Exp $}
puts {<html>
<head>
<title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
|
| ︙ | ︙ | |||
406 407 408 409 410 411 412 | <b>sqlite_last_insert_rowid()</b> API function.</p> <h2>The number of rows that changed</h2> <p>The <b>sqlite_changes()</b> API function returns the number of rows that were inserted, deleted, or modified during the most recent <b>sqlite_exec()</b> call. The number reported includes any changes | | | | | 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 | <b>sqlite_last_insert_rowid()</b> API function.</p> <h2>The number of rows that changed</h2> <p>The <b>sqlite_changes()</b> API function returns the number of rows that were inserted, deleted, or modified during the most recent <b>sqlite_exec()</b> call. The number reported includes any changes that were later undone by a ROLLBACK or ABORT. But rows that are deleted because of a DROP TABLE are <em>not</em> counted.</p> <p>SQLite implements the command "<b>DELETE FROM table</b>" (without a WHERE clause) by dropping the table then recreating it. This is much faster than deleting the elements of the table individually. But it also means that the value returned from <b>sqlite_changes()</b> will be zero regardless of the number of elements that were originally in the table. If an accurate count of the number of elements deleted is necessary, use "<b>DELETE FROM table WHERE 1</b>" instead.</p> <h2>Querying without using a callback function</h2> <p>The <b>sqlite_get_table()</b> function is a wrapper around <b>sqlite_exec()</b> that collects all the information from successive callbacks and writes it into memory obtained from malloc(). This is a convenience function that allows the application to get the entire result of a database query with a single function call.</p> <p>The main result from <b>sqlite_get_table()</b> is an array of pointers to strings. There is one element in this array for each column of each row in the result. NULL results are represented by a NULL pointer. In addition to the regular data, there is an added row at the beginning of the array that contains the name of each column of the result.</p> <p>As an example, consider the following query:</p> <blockquote> SELECT employee_name, login, host FROM users WHERE logic LIKE 'd%'; </blockquote> |
| ︙ | ︙ | |||
615 616 617 618 619 620 621 | be executed is generated from this format string and from whatever additional arguments are attached to the end of the function call.</p> <p>There are two advantages to using the SQLite printf functions instead of <b>sprintf()</b>. First of all, with the SQLite printf routines, there is never a danger of overflowing a static buffer as there is with <b>sprintf()</b>. The SQLite | | | 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 | be executed is generated from this format string and from whatever additional arguments are attached to the end of the function call.</p> <p>There are two advantages to using the SQLite printf functions instead of <b>sprintf()</b>. First of all, with the SQLite printf routines, there is never a danger of overflowing a static buffer as there is with <b>sprintf()</b>. The SQLite printf routines automatically allocate (and later frees) as much memory as is necessary to hold the SQL statements generated.</p> <p>The second advantage the SQLite printf routines have over <b>sprintf()</b> are two new formatting options specifically designed to support string literals in SQL. Within the format string, the %q formatting option works very much like %s in that it |
| ︙ | ︙ |
Changes to www/datatypes.tcl.
1 2 3 | # # Run this script to generated a datatypes.html output file # | | | 1 2 3 4 5 6 7 8 9 10 11 |
#
# Run this script to generated a datatypes.html output file
#
set rcsid {$Id: datatypes.tcl,v 1.3 2002/08/15 11:48:14 drh Exp $}
puts {<html>
<head>
<title>Datatypes In SQLite</title>
</head>
<body bgcolor="white">
<h1 align="center">
|
| ︙ | ︙ | |||
180 181 182 183 184 185 186 | 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 | | > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 |
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. Notice that
the type "VARCHAR" contains "CHAR" as a substring so it is considered
text.</p>
<p>If none of the strings above occur anywhere in the type declaration,
then the datatype is numeric. Note in particular that the datatype for columns
with an empty type declaration is numeric.
</p>
<h2>5.0 Examples</h2>
<p>
Consider the following two command sequences:
</p>
<blockquote><pre>
CREATE TABLE t1(a INTEGER UNIQUE); CREATE TABLE t2(b TEXT UNIQUE);
INSERT INTO t1 VALUES('0'); INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES('0.0'); INSERT INTO t2 VALUES(0.0);
</pre></blockquote>
<p>In the sequence on the left, the second insert will fail. In this case,
the strings '0' and '0.0' are treated as numbers since they are being
inserted into a numeric column but 0==0.0 which violates the uniqueness
constraint. However, the second insert in the right-hand sequence works. In
this case, the constants 0 and 0.0 are treated a strings which means that
they are distinct.</p>
<p>SQLite always converts numbers into double-precision (64-bit) floats
for comparison purposes. This means that a long sequence of digits that
differ only in digits of far to the right will compare equal if they
are in a numeric column but will compare unequal if they are in a text
column. We have:</p>
<blockquote><pre>
INSERT INTO t1 INSERT INTO t2
VALUES('12345678901234567890'); VALUES(12345678901234567890);
INSERT INTO t1 INSERT INTO t2
VALUES('12345678901234567891'); VALUES(12345678901234567891);
</pre></blockquote>
<p>As before, the second insert on the left will fail because the comparison
will convert both strings into floating-point number first and the only
difference in the strings is in the 20-th digit which exceeds the resolution
of a 64-bit float. In contrast, the second insert on the right will work
because in that case, the numbers being inserted are strings and are
compared using memcmp().</p>
<p>
Numeric and text types make a difference for the DISTINCT keyword too:
</p>
<blockquote><pre>
CREATE TABLE t3(a INTEGER); CREATE TABLE t4(b TEXT);
INSERT INTO t3 VALUES('0'); INSERT INTO t4 VALUES(0);
INSERT INTO t3 VALUES('0.0'); INSERT INTO t4 VALUES(0.0);
SELECT DISTINCT * FROM t3; SELECT DISTINCT * FROM t4;
</pre></blockquote>
<p>
The SELECT statement on the left returns a single row since '0' and '0.0'
are treated as numbers and are therefore indistinct. But the SELECT
statement on the right returns two rows since 0 and 0.0 are treated
a strings which are different.</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/faq.tcl.
1 2 3 | # # Run this script to generated a faq.html output file # | | | 1 2 3 4 5 6 7 8 9 10 11 |
#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.17 2002/08/15 11:48:14 drh Exp $}
puts {<html>
<head>
<title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
|
| ︙ | ︙ | |||
75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
statements is ignored (mostly). You can put any type of data you want
into any column, without regard to the declared datatype of that column.
</p>
<p>An exception to this rule is a column of type INTEGER PRIMARY KEY.
Such columns must hold an integer. An attempt to put a non-integer
value into an INTEGER PRIMARY KEY column will generate an error.</p>
}
faq {
SQLite lets me insert a string into a database column of type integer!
} {
<p>This is a feature, not a bug. SQLite is typeless. Any data can be
inserted into any column. You can put arbitrary length strings into
integer columns, floating point numbers in boolean columns, or dates
in character columns. The datatype you assign to a column in the
| > > > > | | > > > | | | < < < | > | > > > > > | | < < > | > | > | > | > > > > > > | | < < < | > > > | > > > | > | > > > | | < < < < < < < < < | 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 |
statements is ignored (mostly). You can put any type of data you want
into any column, without regard to the declared datatype of that column.
</p>
<p>An exception to this rule is a column of type INTEGER PRIMARY KEY.
Such columns must hold an integer. An attempt to put a non-integer
value into an INTEGER PRIMARY KEY column will generate an error.</p>
<p>There is a page on <a href="datatypes.html">datatypes in SQLite</a>
that explains this concept further.</p>
}
faq {
SQLite lets me insert a string into a database column of type integer!
} {
<p>This is a feature, not a bug. SQLite is typeless. Any data can be
inserted into any column. You can put arbitrary length strings into
integer columns, floating point numbers in boolean columns, or dates
in character columns. The datatype you assign to a column in the
CREATE TABLE command does not restrict what data can be put into
that column. Every column is able to hold
an arbitrary length string. (There is one exception: Columns of
type INTEGER PRIMARY KEY may only hold an integer. An error will result
if you try to put anything other than an integer into an
INTEGER PRIMARY KEY column.)</p>
<p>The datatype does effect how values are compared, however. For
columns with a numeric type (such as "integer") any string that looks
like a number is treated as a number for comparison and sorting purposes.
Consider these two command sequences:</p>
<blockquote><pre>
CREATE TABLE t1(a INTEGER UNIQUE); CREATE TABLE t2(b TEXT UNIQUE);
INSERT INTO t1 VALUES('0'); INSERT INTO t2 VALUES(0);
INSERT INTO t1 VALUES('0.0'); INSERT INTO t2 VALUES(0.0);
</pre></blockquote>
<p>In the sequence on the left, the second insert will fail. In this case,
the strings '0' and '0.0' are treated as numbers since they are being
inserted into a numeric column and 0==0.0 which violates the uniqueness
constraint. But the second insert in the right-hand sequence works. In
this case, the constants 0 and 0.0 are treated a strings which means that
they are distinct.</p>
<p>There is a page on <a href="datatypes.html">datatypes in SQLite</a>
that explains this concept further.</p>
}
faq {
Why does SQLite think that the expression '0'=='00' is TRUE?
} {
<p>As of version 2.7.0, it doesn't.</p>
<p>But if one of the two values being compared is stored in a column that
has a numeric type, the the other value is treated as a number, not a
string and the result succeeds. For example:</p>
<blockquote><pre>
CREATE TABLE t3(a INTEGER, b TEXT);
INSERT INTO t3 VALUES(0,0);
SELECT count(*) FROM t3 WHERE a=='00';
</pre></blockquote>
<p>The SELECT in the above series of commands returns 1. The "a" column
is numeric so in the WHERE clause the string '00' is converted into a
number for comparison against "a". 0==00 so the test is true. Now
consider a different SELECT:</p>
<blockquote><pre>
SELECT count(*) FROM t3 WHERE b=='00';
</pre></blockquote>
<p>In this case the answer is 0. B is a text column so a text comparison
is done against '00'. '0'!='00' so the WHERE clause returns FALSE and
the count is zero.</p>
<p>There is a page on <a href="datatypes.html">datatypes in SQLite</a>
that explains this concept further.</p>
}
faq {
Why doesn't SQLite allow me to use '0' and '0.0' as the primary
key on two different rows of the same table?
} {
<p>Your primary key must have a numeric type. Change the datatype of
your primary key to TEXT and it should work.</p>
<p>Every row must have a unique primary key. For a column with a
numeric type, SQLite thinks that <b>'0'</b> and <b>'0.0'</b> are the
same value because they compare equal to one another numerically.
(See the previous question.) Hence the values are not unique.</p>
}
faq {
My linux box is not able to read an SQLite database that was created
on my SparcStation.
} {
<p>You need to upgrade your SQLite library to version 2.6.3 or later.</p>
|
| ︙ | ︙ |
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.43 2002/08/15 11:48:14 drh Exp $}
puts {<html>
<head>
<title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
|
| ︙ | ︙ | |||
279 280 281 282 283 284 285 | 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>. | | | 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 | 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 restrict 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> |
| ︙ | ︙ |