SQLite: Check-in [048b16c1]

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

Overview

Comment:	Additional test cases and documentation updates. (CVS 717)
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA1:	048b16c111693727482642e2a19a74a91458fc80
User & Date:	drh 2002-08-15 11:48:13

Context

2002-08-15
13:45		More documentation updates. (CVS 718) check-in: 9c0400aa user: drh tags: trunk
11:48		Additional test cases and documentation updates. (CVS 717) check-in: 048b16c1 user: drh tags: trunk
01:26		Fixes and test improvements resulting from code coverage testing. (CVS 716) check-in: 66a0f6a8 user: drh tags: trunk

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/misc1.test.

#
#***********************************************************************
# 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.12 2002/08/13 23:02:58 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.
#
................................................................................
} {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








|







 








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

#
#***********************************************************************
# 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.
#
................................................................................
} {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.

#
# Run this Tcl script to generate the sqlite.html file.
#
set rcsid {$Id: c_interface.tcl,v 1.33 2002/08/02 10:36:10 drh Exp $}

puts {<html>
<head>
  <title>The C language interface to the SQLite library</title>
</head>
<body bgcolor=white>
<h1 align=center>
................................................................................
<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 undo 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 write 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 names 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>
................................................................................
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 free)
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

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

#
# Run this script to generated a datatypes.html output file
#
set rcsid {$Id: datatypes.tcl,v 1.2 2002/08/14 03:03:58 drh Exp $}

puts {<html>
<head>
  <title>Datatypes In SQLite</title>
</head>
<body bgcolor="white">
<h1 align="center">
................................................................................
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>}




|







 







|
>
>
>
>
|


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

#
# 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">
................................................................................
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 &nbsp; 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.

#
# Run this script to generated a faq.html output file
#
set rcsid {$Id: faq.tcl,v 1.16 2002/08/14 12:56:56 drh Exp $}

puts {<html>
<head>
  <title>SQLite Frequently Asked Questions</title>
</head>
<body bgcolor="white">
<h1 align="center">Frequently Asked Questions</h1>
................................................................................
  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

  CREATE TABLE command is (mostly) ignored.  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>Because SQLite ignores data types, you can omit the data type definition
  from columns in CREATE TABLE statements.  For example, instead of saying
<blockquote><pre>
CREATE TABLE t1(
  f1 int,
  f2 varchar(10),
  f3 boolean
);
</pre></blockquote>
  You can save yourself a lot of typing and formatting by omitting the
  data type declarations, like this:
<blockquote><pre>
CREATE TABLE t1(f1,f2,f3);
</pre></blockquote>
  </p>





}

faq {
  Why does SQLite think that the expression '0'=='00' is TRUE?
} {
  <p>This is a consequence of SQLite being typeless.  All data is stored
  internally as a null-terminated string.  There is no concept of
  separate data types for strings and numbers.</p>



  <p>When doing a comparison, SQLite looks at the string on both sides of
  the comparison operator.  If both strings look like pure numeric

  values (with no extra punctuation or spacing) then the strings are
  converted to floating point numbers using <b>atof()</b> and the results
  are compared.  The results of <b>atof("0")</b> and <b>atof("00")</b>
  are both 0.0, so those two strings are considered to be equal.</p>


  <p>If only one string in a comparison is a pure numeric, then that string
  is assumed to be less than the other.  Of neither string is a pure numeric,
  then <b>strcmp()</b> is used for the comparison.</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>Every row much have a unique primary key.
  But 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>

  <p>You can work around this issue in two ways:</p>
  <ol>
  <li><p>Remove the <b>primary key</b> clause from the CREATE TABLE.</p></li>
  <li><p>Prepend a space to the beginning of every value you use for
      the primary key.  The initial
     space will mean that the entries are not pure numerics and hence
     will be compared as strings using <b>strcmp()</b>.</p></li>
  </ol>
}
        
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>




|







 







>
>
>









>
|





|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>





|
<
<

>
>
|
<
>
|
|
|
|
>

<
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>






>
>
>
|
|


<
<
<
<
<
<
<
<
<

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

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

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