SQLite

Check-in [e2d95f85a3]
Login

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

Overview
Comment:Documentation updates prior to the releae of 2.7.0. (CVS 728)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e2d95f85a3c4beeb5f8e78498f52fc00475f38ed
User & Date: drh 2002-08-25 20:11:19.000
Context
2002-08-25
20:58
Version 2.7.0 (CVS 729) (check-in: 9e341d9c93 user: drh tags: trunk)
20:11
Documentation updates prior to the releae of 2.7.0. (CVS 728) (check-in: e2d95f85a3 user: drh tags: trunk)
19:20
Fix for ticket #136: Added the OP_RenameCursor VDBE instruction and used it to make cursor numbers right on nested subqueries. Also added OP_Gosub and OP_Return but have not actually used them for anything yet. (CVS 727) (check-in: c602603e7c user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to www/changes.tcl.
︙︙
21
22
23
24
25
26
27
28
29
30
31
32
33


34
35
36
37
38
39
40



proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Aug ?? (2.7.0)} {
<li>Make a distinction between numeric and text values when sorting.
    Text values sort according to memcmp().  Numeric values sort in
    numeric order.</li>
<li>Allow multiple simulataneous readers under windows by simulating
    the reader/writers locks that are missing from Win95/98/ME.</li>


}

chng {2002 Aug 12 (2.6.3)} {
<li>Add the ability to read both little-endian and big-endian databases.
    So database created under SunOS or MacOSX can be read and written
    under Linux or Windows and vice versa.</li>
<li>Convert to the new website: http://www.sqlite.org/</li>








|





>
>








21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42



proc chng {date desc} {
  puts "<DT><B>$date</B></DT>"
  puts "<DD><P><UL>$desc</UL></P></DD>"
}

chng {2002 Aug 25 (2.7.0)} {
<li>Make a distinction between numeric and text values when sorting.
    Text values sort according to memcmp().  Numeric values sort in
    numeric order.</li>
<li>Allow multiple simulataneous readers under windows by simulating
    the reader/writers locks that are missing from Win95/98/ME.</li>
<li>An error is now returned when trying to start a transaction if
    another transaction is already active.</li>
}

chng {2002 Aug 12 (2.6.3)} {
<li>Add the ability to read both little-endian and big-endian databases.
    So database created under SunOS or MacOSX can be read and written
    under Linux or Windows and vice versa.</li>
<li>Convert to the new website: http://www.sqlite.org/</li>
︙︙
Changes to www/lang.tcl. 
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.44 2002/08/18 19:09:24 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>




|








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.45 2002/08/25 20:11:19 drh Exp $}

puts {<html>
<head>
  <title>Query Language Understood By SQLite</title>
</head>
<body bgcolor=white>
<h1 align=center>
︙︙
720
721
722
723
724
725
726
727



728
729
730
731
732
733
734
735
736
737
738
739
740










741
742
743
744
745
746
747
748
749
750
751
752
753
754










755
756
757
758
759
760
761


<p>Both simple and aggregate functions are supported.  A simple
function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>

<p>The following simple functions are currently supported:</p>




<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
<td valign="top">Return the absolute value of argument <i>X</i>.</td>
</tr>

<tr>
<td valign="top" align="right">coalesce(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
all arguments are NULL then NULL is returned.</td>
</tr>











<tr>
<td valign="top" align="right">last_insert_rowid()</td>
<td valign="top">Return the ROWID of the last row insert from this
connection to the database.  This is the same value that would be returned
from the <b>sqlite_last_insert_rowid()</b> API function.</td>
</tr>

<tr>
<td valign="top" align="right">length(<i>X</i>)</td>
<td valign="top">Return the string length of <i>X</i> in characters.
If SQLite is configured to support UTF-8, then the number of UTF-8
characters is returned, not the number of bytes.</td>
</tr>











<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>
<td valign="top">Return a copy of string <i>X</i> will all characters
converted to lower case.  The C library <b>tolower()</b> routine is used
for the conversion, which means that this function might not
work correctly on UTF-8 characters.</td>
</tr>








|
>
>
>













>
>
>
>
>
>
>
>
>
>














>
>
>
>
>
>
>
>
>
>








720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784


<p>Both simple and aggregate functions are supported.  A simple
function can be used in any expression.  Simple functions return
a result immediately based on their inputs.  Aggregate functions
may only be used in a SELECT statement.  Aggregate functions compute
their result across all rows of the result set.</p>

<p>The functions shown below are available by default.  Additional
functions may be written in C and added to the database engine using
the <a href="c_interface.html#cfunc">sqlite_create_function()</a>
API.</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>abs(<i>X</i>)</td>
<td valign="top">Return the absolute value of argument <i>X</i>.</td>
</tr>

<tr>
<td valign="top" align="right">coalesce(<i>X</i>,<i>Y</i>,...)</td>
<td valign="top">Return a copy of the first non-NULL argument.  If
all arguments are NULL then NULL is returned.</td>
</tr>

<tr>
<td valign="top" align="right">glob(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>Y GLOB X</b>" syntax of SQLite.  The
<a href="c_interface.html#cfunc">sqlite_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the GLOB operator.</td>
</tr>

<tr>
<td valign="top" align="right">last_insert_rowid()</td>
<td valign="top">Return the ROWID of the last row insert from this
connection to the database.  This is the same value that would be returned
from the <b>sqlite_last_insert_rowid()</b> API function.</td>
</tr>

<tr>
<td valign="top" align="right">length(<i>X</i>)</td>
<td valign="top">Return the string length of <i>X</i> in characters.
If SQLite is configured to support UTF-8, then the number of UTF-8
characters is returned, not the number of bytes.</td>
</tr>

<tr>
<td valign="top" align="right">like(<i>X</i>,<i>Y</i>)</td>
<td valign="top">This function is used to implement the
"<b>Y LIKE X</b>" syntax of SQL.  The
<a href="c_interface.html#cfunc">sqlite_create_function()</a> 
interface can
be used to override this function and thereby change the operation
of the LIKE operator.</td>
</tr>

<tr>
<td valign="top" align="right">lower(<i>X</i>)</td>
<td valign="top">Return a copy of string <i>X</i> will all characters
converted to lower case.  The C library <b>tolower()</b> routine is used
for the conversion, which means that this function might not
work correctly on UTF-8 characters.</td>
</tr>
︙︙
807
808
809
810
811
812
813
814

815
816
817
818
819
820
821
822

upper-case letters.  The implementation of this function uses the C library
routine <b>toupper()</b> which means it may not work correctly on 
UTF-8 strings.</td>
</tr>
</table>

<p>
The following aggregate functions are supported:

</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>avg(<i>X</i>)</td>
<td valign="top">Return the average value of all <i>X</i> within a group.</td>
</tr>









|
>
|








830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846

upper-case letters.  The implementation of this function uses the C library
routine <b>toupper()</b> which means it may not work correctly on 
UTF-8 strings.</td>
</tr>
</table>

<p>
The following aggregate functions are available by default.  Additional
aggregate functions written in C may be added using the 
<a href="c_interface.html#cfunc">sqlite_create_aggregate()</a> API.</p>

<table border=0 cellpadding=10>
<tr>
<td valign="top" align="right" width=120>avg(<i>X</i>)</td>
<td valign="top">Return the average value of all <i>X</i> within a group.</td>
</tr>
︙︙
1168
1169
1170
1171
1172
1173
1174
1175


1176
1177
1178
1179
1180
1181
1182

<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row
has a fixed number of columns.  The number of columns in the
result is specified by the expression list in between the
SELECT and FROM keywords.  Any arbitrary expression can be used
as a result.  If a result expression is }
puts "[Operator *] then all columns of all tables are substituted"
puts {for that one expression.</p>



<p>The query is executed against one or more tables specified after
the FROM keyword.  If multiple tables names are separated by commas,
then the query is against the cross join of the various tables.
The full SQL-92 join syntax can also be used to specify joins.
A sub-query
in parentheses may be substituted for any table name in the FROM clause.








|
>
>








1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208

<p>The SELECT statement is used to query the database.  The
result of a SELECT is zero or more rows of data where each row
has a fixed number of columns.  The number of columns in the
result is specified by the expression list in between the
SELECT and FROM keywords.  Any arbitrary expression can be used
as a result.  If a result expression is }
puts "[Operator *] then all columns of all tables are substituted"
puts {for that one expression.  If the expression is the name of}
puts "a table followed by [Operator .*] then the result is all columns"
puts {in that one table.</p>

<p>The query is executed against one or more tables specified after
the FROM keyword.  If multiple tables names are separated by commas,
then the query is against the cross join of the various tables.
The full SQL-92 join syntax can also be used to specify joins.
A sub-query
in parentheses may be substituted for any table name in the FROM clause.
︙︙