/ Check-in [048b16c1]
Login

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 Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/misc1.test.

     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for miscellanous features that were
    14     14   # left out of other test files.
    15     15   #
    16         -# $Id: misc1.test,v 1.12 2002/08/13 23:02:58 drh Exp $
           16  +# $Id: misc1.test,v 1.13 2002/08/15 11:48:13 drh Exp $
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
    20     20   
    21     21   # Test the creation and use of tables that have a large number
    22     22   # of columns.
    23     23   #
................................................................................
   315    315   } {1 {database is locked}}
   316    316   do_test misc1-11.2 {
   317    317     execsql {COMMIT}
   318    318     set rc [catch {db2 eval {SELECT count(*) FROM t1}} msg]
   319    319     db2 close
   320    320     lappend rc $msg
   321    321   } {0 3}
          322  +
          323  +# Make sure string comparisons really do compare strings in format4+.
          324  +# Similar tests in the format3.test file show that for format3 and earlier
          325  +# all comparisions where numeric if either operand looked like a number.
          326  +#
          327  +do_test misc1-12.1 {
          328  +  execsql {SELECT '0'=='0.0'}
          329  +} {0}
          330  +do_test misc1-12.2 {
          331  +  execsql {SELECT '0'==0.0}
          332  +} {1}
          333  +do_test misc1-12.3 {
          334  +  execsql {SELECT '12345678901234567890'=='12345678901234567891'}
          335  +} {0}
          336  +do_test misc1-12.4 {
          337  +  execsql {
          338  +    CREATE TABLE t6(a INT UNIQUE, b TEXT UNIQUE);
          339  +    INSERT INTO t6 VALUES('0','0.0');
          340  +    SELECT * FROM t6;
          341  +  }
          342  +} {0 0.0}
          343  +do_test misc1-12.5 {
          344  +  execsql {
          345  +    INSERT OR IGNORE INTO t6 VALUES(0.0,'x');
          346  +    SELECT * FROM t6;
          347  +  }
          348  +} {0 0.0}
          349  +do_test misc1-12.6 {
          350  +  execsql {
          351  +    INSERT OR IGNORE INTO t6 VALUES('y',0);
          352  +    SELECT * FROM t6;
          353  +  }
          354  +} {0 0.0 y 0}
          355  +do_test misc1-12.7 {
          356  +  execsql {
          357  +    CREATE TABLE t7(x INTEGER, y TEXT, z);
          358  +    INSERT INTO t7 VALUES(0,0,1);
          359  +    INSERT INTO t7 VALUES(0.0,0,2);
          360  +    INSERT INTO t7 VALUES(0,0.0,3);
          361  +    INSERT INTO t7 VALUES(0.0,0.0,4);
          362  +    SELECT DISTINCT x, y FROM t7 ORDER BY z;
          363  +  }
          364  +} {0 0 0 0.0}
   322    365   
   323    366   finish_test

Changes to www/c_interface.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: c_interface.tcl,v 1.33 2002/08/02 10:36:10 drh Exp $}
            4  +set rcsid {$Id: c_interface.tcl,v 1.34 2002/08/15 11:48:14 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>The C language interface to the SQLite library</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
   406    406   <b>sqlite_last_insert_rowid()</b> API function.</p>
   407    407   
   408    408   <h2>The number of rows that changed</h2>
   409    409   
   410    410   <p>The <b>sqlite_changes()</b> API function returns the number of rows
   411    411   that were inserted, deleted, or modified during the most recent
   412    412   <b>sqlite_exec()</b> call.  The number reported includes any changes
   413         -that were later undo by a ROLLBACK or ABORT.  But rows that are
          413  +that were later undone by a ROLLBACK or ABORT.  But rows that are
   414    414   deleted because of a DROP TABLE are <em>not</em> counted.</p>
   415    415   
   416    416   <p>SQLite implements the command "<b>DELETE FROM table</b>" (without
   417    417   a WHERE clause) by dropping the table then recreating it.  
   418    418   This is much faster than deleting the elements of the table individually.
   419    419   But it also means that the value returned from <b>sqlite_changes()</b>
   420    420   will be zero regardless of the number of elements that were originally
................................................................................
   421    421   in the table.  If an accurate count of the number of elements deleted
   422    422   is necessary, use "<b>DELETE FROM table WHERE 1</b>" instead.</p>
   423    423   
   424    424   <h2>Querying without using a callback function</h2>
   425    425   
   426    426   <p>The <b>sqlite_get_table()</b> function is a wrapper around
   427    427   <b>sqlite_exec()</b> that collects all the information from successive
   428         -callbacks and write it into memory obtained from malloc().  This
          428  +callbacks and writes it into memory obtained from malloc().  This
   429    429   is a convenience function that allows the application to get the
   430    430   entire result of a database query with a single function call.</p>
   431    431   
   432    432   <p>The main result from <b>sqlite_get_table()</b> is an array of pointers
   433    433   to strings.  There is one element in this array for each column of
   434    434   each row in the result.  NULL results are represented by a NULL
   435    435   pointer. In addition to the regular data, there is an added row at the 
   436         -beginning of the array that contains the names of each column of the
          436  +beginning of the array that contains the name of each column of the
   437    437   result.</p>
   438    438   
   439    439   <p>As an example, consider the following query:</p>
   440    440   
   441    441   <blockquote>
   442    442   SELECT employee_name, login, host FROM users WHERE logic LIKE 'd%';
   443    443   </blockquote>
................................................................................
   615    615   be executed is generated from this format string and from whatever
   616    616   additional arguments are attached to the end of the function call.</p>
   617    617   
   618    618   <p>There are two advantages to using the SQLite printf
   619    619   functions instead of <b>sprintf()</b>.  First of all, with the
   620    620   SQLite printf routines, there is never a danger of overflowing a
   621    621   static buffer as there is with <b>sprintf()</b>.  The SQLite
   622         -printf routines automatically allocate (and later free)
          622  +printf routines automatically allocate (and later frees)
   623    623   as much memory as is 
   624    624   necessary to hold the SQL statements generated.</p>
   625    625   
   626    626   <p>The second advantage the SQLite printf routines have over
   627    627   <b>sprintf()</b> are two new formatting options specifically designed
   628    628   to support string literals in SQL.  Within the format string,
   629    629   the %q formatting option works very much like %s in that it

Changes to www/datatypes.tcl.

     1      1   #
     2      2   # Run this script to generated a datatypes.html output file
     3      3   #
     4         -set rcsid {$Id: datatypes.tcl,v 1.2 2002/08/14 03:03:58 drh Exp $}
            4  +set rcsid {$Id: datatypes.tcl,v 1.3 2002/08/15 11:48:14 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Datatypes In SQLite</title>
     9      9   </head>
    10     10   <body bgcolor="white">
    11     11   <h1 align="center">
................................................................................
   180    180   CLOB</br>
   181    181   TEXT
   182    182   </blockquote>
   183    183   
   184    184   <p>
   185    185   The search for these strings in the type declaration is case insensitive,
   186    186   of course.  If any of the above strings occur anywhere in the type
   187         -declaration, then the datatype of the column is text.  Otherwise the
   188         -datatype is numeric.  Note in particular that the datatype for columns
          187  +declaration, then the datatype of the column is text.  Notice that
          188  +the type "VARCHAR" contains "CHAR" as a substring so it is considered
          189  +text.</p>
          190  +
          191  +<p>If none of the strings above occur anywhere in the type declaration,
          192  +then the datatype is numeric.  Note in particular that the datatype for columns
   189    193   with an empty type declaration is numeric.
   190    194   </p>
          195  +
          196  +<h2>5.0 &nbsp; Examples</h2>
          197  +
          198  +<p>
          199  +Consider the following two command sequences:
          200  +</p>
          201  +
          202  +<blockquote><pre>
          203  +CREATE TABLE t1(a INTEGER UNIQUE);        CREATE TABLE t2(b TEXT UNIQUE);
          204  +INSERT INTO t1 VALUES('0');               INSERT INTO t2 VALUES(0);
          205  +INSERT INTO t1 VALUES('0.0');             INSERT INTO t2 VALUES(0.0);
          206  +</pre></blockquote>
          207  +
          208  +<p>In the sequence on the left, the second insert will fail.  In this case,
          209  +the strings '0' and '0.0' are treated as numbers since they are being 
          210  +inserted into a numeric column but 0==0.0 which violates the uniqueness
          211  +constraint.  However, the second insert in the right-hand sequence works.  In
          212  +this case, the constants 0 and 0.0 are treated a strings which means that
          213  +they are distinct.</p>
          214  +
          215  +<p>SQLite always converts numbers into double-precision (64-bit) floats
          216  +for comparison purposes.  This means that a long sequence of digits that
          217  +differ only in digits of far to the right will compare equal if they
          218  +are in a numeric column but will compare unequal if they are in a text
          219  +column.  We have:</p>
          220  +
          221  +<blockquote><pre>
          222  +INSERT INTO t1                            INSERT INTO t2
          223  +   VALUES('12345678901234567890');           VALUES(12345678901234567890);
          224  +INSERT INTO t1                            INSERT INTO t2
          225  +   VALUES('12345678901234567891');           VALUES(12345678901234567891);
          226  +</pre></blockquote>
          227  +
          228  +<p>As before, the second insert on the left will fail because the comparison
          229  +will convert both strings into floating-point number first and the only
          230  +difference in the strings is in the 20-th digit which exceeds the resolution
          231  +of a 64-bit float.  In contrast, the second insert on the right will work
          232  +because in that case, the numbers being inserted are strings and are
          233  +compared using memcmp().</p>
          234  +
          235  +<p>
          236  +Numeric and text types make a difference for the DISTINCT keyword too:
          237  +</p>
          238  +
          239  +<blockquote><pre>
          240  +CREATE TABLE t3(a INTEGER);               CREATE TABLE t4(b TEXT);
          241  +INSERT INTO t3 VALUES('0');               INSERT INTO t4 VALUES(0);
          242  +INSERT INTO t3 VALUES('0.0');             INSERT INTO t4 VALUES(0.0);
          243  +SELECT DISTINCT * FROM t3;                SELECT DISTINCT * FROM t4;
          244  +</pre></blockquote>
          245  +
          246  +<p>
          247  +The SELECT statement on the left returns a single row since '0' and '0.0'
          248  +are treated as numbers and are therefore indistinct.  But the SELECT 
          249  +statement on the right returns two rows since 0 and 0.0 are treated
          250  +a strings which are different.</p>
   191    251   }
   192    252   
   193    253   puts {
   194    254   <p><hr /></p>
   195    255   <p><a href="index.html"><img src="/goback.jpg" border=0 />
   196    256   Back to the SQLite Home Page</a>
   197    257   </p>
   198    258   
   199    259   </body></html>}

Changes to www/faq.tcl.

     1      1   #
     2      2   # Run this script to generated a faq.html output file
     3      3   #
     4         -set rcsid {$Id: faq.tcl,v 1.16 2002/08/14 12:56:56 drh Exp $}
            4  +set rcsid {$Id: faq.tcl,v 1.17 2002/08/15 11:48:14 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>SQLite Frequently Asked Questions</title>
     9      9   </head>
    10     10   <body bgcolor="white">
    11     11   <h1 align="center">Frequently Asked Questions</h1>
................................................................................
    75     75     statements is ignored (mostly).  You can put any type of data you want
    76     76     into any column, without regard to the declared datatype of that column.
    77     77     </p>
    78     78   
    79     79     <p>An exception to this rule is a column of type INTEGER PRIMARY KEY.
    80     80     Such columns must hold an integer.  An attempt to put a non-integer
    81     81     value into an INTEGER PRIMARY KEY column will generate an error.</p>
           82  +
           83  +  <p>There is a page on <a href="datatypes.html">datatypes in SQLite</a>
           84  +  that explains this concept further.</p>
    82     85   }
    83     86   
    84     87   faq {
    85     88     SQLite lets me insert a string into a database column of type integer!
    86     89   } {
    87     90     <p>This is a feature, not a bug.  SQLite is typeless.  Any data can be
    88     91     inserted into any column.  You can put arbitrary length strings into
    89     92     integer columns, floating point numbers in boolean columns, or dates
    90     93     in character columns.  The datatype you assign to a column in the
    91         -  CREATE TABLE command is (mostly) ignored.  Every column is able to hold
           94  +  CREATE TABLE command does not restrict what data can be put into
           95  +  that column.  Every column is able to hold
    92     96     an arbitrary length string.  (There is one exception: Columns of
    93     97     type INTEGER PRIMARY KEY may only hold an integer.  An error will result
    94     98     if you try to put anything other than an integer into an
    95     99     INTEGER PRIMARY KEY column.)</p>
    96    100   
    97         -  <p>Because SQLite ignores data types, you can omit the data type definition
    98         -  from columns in CREATE TABLE statements.  For example, instead of saying
    99         -<blockquote><pre>
   100         -CREATE TABLE t1(
   101         -  f1 int,
   102         -  f2 varchar(10),
   103         -  f3 boolean
   104         -);
          101  +  <p>The datatype does effect how values are compared, however.  For
          102  +  columns with a numeric type (such as "integer") any string that looks
          103  +  like a number is treated as a number for comparison and sorting purposes.
          104  +  Consider these two command sequences:</p>
          105  +
          106  +  <blockquote><pre>
          107  +CREATE TABLE t1(a INTEGER UNIQUE);        CREATE TABLE t2(b TEXT UNIQUE);
          108  +INSERT INTO t1 VALUES('0');               INSERT INTO t2 VALUES(0);
          109  +INSERT INTO t1 VALUES('0.0');             INSERT INTO t2 VALUES(0.0);
   105    110   </pre></blockquote>
   106         -  You can save yourself a lot of typing and formatting by omitting the
   107         -  data type declarations, like this:
   108         -<blockquote><pre>
   109         -CREATE TABLE t1(f1,f2,f3);
   110         -</pre></blockquote>
   111         -  </p>
          111  +
          112  +  <p>In the sequence on the left, the second insert will fail.  In this case,
          113  +  the strings '0' and '0.0' are treated as numbers since they are being 
          114  +  inserted into a numeric column and 0==0.0 which violates the uniqueness
          115  +  constraint.  But the second insert in the right-hand sequence works.  In
          116  +  this case, the constants 0 and 0.0 are treated a strings which means that
          117  +  they are distinct.</p>
          118  +
          119  +  <p>There is a page on <a href="datatypes.html">datatypes in SQLite</a>
          120  +  that explains this concept further.</p>
   112    121   }
   113    122   
   114    123   faq {
   115    124     Why does SQLite think that the expression '0'=='00' is TRUE?
   116    125   } {
   117         -  <p>This is a consequence of SQLite being typeless.  All data is stored
   118         -  internally as a null-terminated string.  There is no concept of
   119         -  separate data types for strings and numbers.</p>
          126  +  <p>As of version 2.7.0, it doesn't.</p>
   120    127   
   121         -  <p>When doing a comparison, SQLite looks at the string on both sides of
   122         -  the comparison operator.  If both strings look like pure numeric
   123         -  values (with no extra punctuation or spacing) then the strings are
   124         -  converted to floating point numbers using <b>atof()</b> and the results
   125         -  are compared.  The results of <b>atof("0")</b> and <b>atof("00")</b>
   126         -  are both 0.0, so those two strings are considered to be equal.</p>
          128  +  <p>But if one of the two values being compared is stored in a column that
          129  +  has a numeric type, the the other value is treated as a number, not a
          130  +  string and the result succeeds.  For example:</p>
   127    131   
   128         -  <p>If only one string in a comparison is a pure numeric, then that string
   129         -  is assumed to be less than the other.  Of neither string is a pure numeric,
   130         -  then <b>strcmp()</b> is used for the comparison.</p>
          132  +<blockquote><pre>
          133  +CREATE TABLE t3(a INTEGER, b TEXT);
          134  +INSERT INTO t3 VALUES(0,0);
          135  +SELECT count(*) FROM t3 WHERE a=='00';
          136  +</pre></blockquote>
          137  +
          138  +  <p>The SELECT in the above series of commands returns 1.  The "a" column
          139  +  is numeric so in the WHERE clause the string '00' is converted into a
          140  +  number for comparison against "a".  0==00 so the test is true.  Now
          141  +  consider a different SELECT:</p>
          142  +
          143  +<blockquote><pre>
          144  +SELECT count(*) FROM t3 WHERE b=='00';
          145  +</pre></blockquote>
          146  +
          147  +  <p>In this case the answer is 0.  B is a text column so a text comparison
          148  +  is done against '00'.  '0'!='00' so the WHERE clause returns FALSE and
          149  +  the count is zero.</p>
          150  +
          151  +  <p>There is a page on <a href="datatypes.html">datatypes in SQLite</a>
          152  +  that explains this concept further.</p>
   131    153   }
   132    154   
   133    155   faq {
   134    156     Why doesn't SQLite allow me to use '0' and '0.0' as the primary
   135    157     key on two different rows of the same table?
   136    158   } {
   137         -  <p>Every row much have a unique primary key.
   138         -  But SQLite thinks that <b>'0'</b> and <b>'0.0'</b> are the
          159  +  <p>Your primary key must have a numeric type.  Change the datatype of
          160  +  your primary key to TEXT and it should work.</p>
          161  +
          162  +  <p>Every row must have a unique primary key.  For a column with a
          163  +  numeric type, SQLite thinks that <b>'0'</b> and <b>'0.0'</b> are the
   139    164     same value because they compare equal to one another numerically.
   140    165     (See the previous question.)  Hence the values are not unique.</p>
   141         -
   142         -  <p>You can work around this issue in two ways:</p>
   143         -  <ol>
   144         -  <li><p>Remove the <b>primary key</b> clause from the CREATE TABLE.</p></li>
   145         -  <li><p>Prepend a space to the beginning of every value you use for
   146         -      the primary key.  The initial
   147         -     space will mean that the entries are not pure numerics and hence
   148         -     will be compared as strings using <b>strcmp()</b>.</p></li>
   149         -  </ol>
   150    166   }
   151    167           
   152    168   faq {
   153    169     My linux box is not able to read an SQLite database that was created
   154    170     on my SparcStation.
   155    171   } {
   156    172     <p>You need to upgrade your SQLite library to version 2.6.3 or later.</p>

Changes to www/lang.tcl.

     1      1   #
     2      2   # Run this Tcl script to generate the sqlite.html file.
     3      3   #
     4         -set rcsid {$Id: lang.tcl,v 1.42 2002/08/14 00:08:13 drh Exp $}
            4  +set rcsid {$Id: lang.tcl,v 1.43 2002/08/15 11:48:14 drh Exp $}
     5      5   
     6      6   puts {<html>
     7      7   <head>
     8      8     <title>Query Language Understood By SQLite</title>
     9      9   </head>
    10     10   <body bgcolor=white>
    11     11   <h1 align=center>
................................................................................
   279    279   definitions and constraints.  The table name can be either an identifier
   280    280   or a string.  Tables names that begin with "<b>sqlite_</b>" are reserved
   281    281   for use by the engine.</p>
   282    282   
   283    283   <p>Each column definition is the name of the column followed by the
   284    284   datatype for that column, then one or more optional column constraints.
   285    285   SQLite is <a href="datatypes.html">typeless</a>.
   286         -The datatype for the column does not constraint what data may be put
          286  +The datatype for the column does not restrict what data may be put
   287    287   in that column.
   288    288   All information is stored as null-terminated strings.
   289    289   The UNIQUE constraint causes an index to be created on the specified
   290    290   columns.  This index must contain unique keys.
   291    291   The DEFAULT constraint
   292    292   specifies a default value to use when doing an INSERT.
   293    293   </p>