There is a list of features that SQLite does *not* support at http://www.sqlite.org/omitted.html. If you
find additional
features that SQLite does not support, you may want to list them
below.

----
*: INSERT INTO table SET column1=value1,column2=value2,... ;

*: This appears to be unsupported: updating multiple columns with subselect

   update T1 set (theUpdatedValue, theOtherValue) =
(select theTop, theValue from T2 where T2.theKey = T1.theID)

*: free text search capabilities in select statements:
Mysql does free text search Match(field_list) Against(keyword)

*: Multiple databases are not supported. For example, the following construct for creating a table in a
database db1 based on a table in database db2 won't work:

  create table db1.table1 as select * from db2.table1;

_: But I often need this. So, it should looks like a schemas in ORACLE database.

_:isn't this supported by ATTACH DATABASE?

*: Hierarchical Queries.

  START WITH <conditions> CONNECT BY [PRIOR]<conditions> (ORACLE)

*: SQL92 Character sets, collations, coercibility.

*: Stored Procedures

*: Rollup and Cube - _Who can tell me what this means?_

_::: Rollup and Cube are OLAP terms. See for example http://en.wikipedia.org/wiki/OLAP_cube

_::: I don't know much about it myself, but a quick google on the subject gives me... http://
www.winnetmag.com/SQLServer/Article/ArticleID/5104/5104.html and http://databases.about.com/
library/weekly/aa070101a.htm

_::: both of these imply that the CUBE operator causes new rows to be generated to give a wildcard
value to non-numeric columns and summing the numeric columns which match those wildcards. The
potential for generating a huge amount of data with cube is implicit, I think - hence its name. ROLLUP
appears to be related but removes some of the wildcards; I couldn't determine what from the limited
information in the articles. I could not find, on brief examination any more definitive reference. Anyone
got something more definitive than those articles ?  It seems to me that you can do with sum()
everything you can do with CUBE.

_::: CUBE an ROLLUP provide addition subtotal rows.  Lets say you are doing a query "SELECT x, y,
SUM(z) FROM t GROUP BY x, y" lets also say x and y each have two values.  This query will give you the
sums for all records with x1 y1, x1 y2, x2 y1, and x2 y2.  Rollup and cube both provide addition
subtotals.  Rollup adds 3 new sums: for all x1, for all x2, and the grand total.  You can imagine that the
GROUP BY list is being rolled up, so that it goes from being x, y; to being just x; to being empty.  The
result of the select for the column that is rolled up becomes NULL.  CUBE will do all combinations of
sums in the group by list: sum of all x1, all x2, all y1, all y2, and grand total.  No idea what that has to
do with a cube, though I do sort of picture a hyper-cube in my mind for no good reason.  If you ever
add ROLLUP and CUBE, I also recommend adding the GROUPING() function so that you can filter out the
additional computations you don't want, or do somthing like SELECT CASE WHEN GROUPING(name)
THEN
'Total' ELSE name END, hours FROM timesheets GROUP BY name.  I've used the feature plenty doing
reports, but then I'm a chronic SQL abuser.

*: INSERT INTO with multiple rows (ie. INSERT INTO table VALUES (...), (...). etc)

*: CREATE DATABASE, DROP DATABASE - _Does not seem meaningful for an
   embedded database engine like SQLite. To create a new database, just
   do sqlite_open(). To drop a database, delete the file._

*: ALTER VIEW, ALTER TRIGGER, ALTER TABLE

*: Schemas - _Who can tell me what this means?_

_::: The idea is that multiple users using the same database can cleanly
separate their tables, views (stored procs, etc) by prefixing them with
their login, so jack's jack.importantTable is distinct from jill's jill.importantTable. There are
administrative benefits ('Jack left and we don't like his work; can we kill everything he did?' Ans: 'Yes, let
me just
drop his schema..', with aliases, jill.importantTable can be made available to everybody as
'importantTable', permissions can be hung off schemas). The common notation (jill.importantTable)
would map to databasename.tablename in the current sqlite arrangement.

_:::: This doesn't really make a lot of sense for an embedded database.

_::::: I could use this. Im trying to use sqlite as a 'fake database'
that i can use in testing suite. For sqlite to be a good 'fake' of
something like Oracle it would help a lot if it had the ability to do stuff
like 'select * from blah.PERSON'. In this case the 'blah' is the schema name. PERSON is the table name.
Another example:  'select zipcode from blorg.ADDRESS'. blorg is the schema name, ADDRESS is the
table name.
right now it is giving 'no such database as blah' or 'no such database as
blorg'. i try to 'create database blah' but of cousre that doesnt work either.

_::::: at the very least, it could accept table names that have
a '.' in them. this would fake schemas good enough for me. right now
it doesnt seem to allow it.

_:::::: You can fake this syntax if you split the "schemas" off into seperate files, then do an ATTACH
DATABASE blorg.db AS blorg; SELECT zipcode FROM blorg.address;

*: TRUNCATE (MySQL, Postgresql and Oracle have it... but I dont know if this is a standard command) -
_SQLite does this automatically when you do a DELETE
without a WHERE clause. You can use also VACUUM command_

*: ORDER BY myfield ASC NULLS LAST (Oracle)

*: CREATE TRIGGER [BEFORE | AFTER | INSTEAD OF] (Oracle)

*: UPDATE with a FROM clause (not sure if this is standard, Sybase and Microsoft have it).

_:: Postgres also allows "UPDATE ... FROM ... ", BTW.  (As does Ingres --CAU)

_:: I was working on something where I really wanted to use this construct with SQLite, so I came up
with the following hack:

		--
		-- SQLite does not allow "UPDATE ... FROM"
		-- but this is what it might look like
		--
		UPDATE
			t1
		SET
			measure = t2.measure
		FROM
			t2, t1
		WHERE
			t2.key = t1.key
		;

		--
		-- emulating "UPDATE ... FROM" in SQLite
		--
		-- n.b.:  it assumes a PRIMARY KEY !
		--
		-- the INSERT never succeeds because
		-- the JOIN restricts the SELECT to
		-- existing rows, forcing the REPLACE
		--
		INSERT OR REPLACE INTO
			t1( key, measure )
		SELECT
			t2.key, t2.measure
		FROM
			t2, t1
		WHERE
			t2.key = t1.key
		;

_:: Since that works, maybe SQLite could be made to support the "UPDATE ... FROM" construct directly,
so we would not have to rely on conflict resolution to do essentially the same thing (not exactly the
same, since REPLACE is DELETE and INSERT, but sometimes close enough).  *< gifford hesketh::2004-
Oct-26*

_:: I've managed successfully to do this an alternative way, works in version 3.2.1 (--CAU:18-Aug
-2005) ...

		--
		-- emulating "UPDATE ... FROM" in SQLite
		--
		--
		UPDATE
			t1
		SET
			measure = ( SELECT measure FROM t2 WHERE t2.key = t1.key )
		;


*: Multi-column IN clause (ie. SELECT * FROM tab WHERE (key1, key2) IN (SELECT...)

*: INSERTing fewer values than columns does not fill the missing columns with the default values; if
fewer values than columns in the table are supplied, all columns filled have to be named before the
keyword values

*: INSERTing one record with all VALUES to DEFAULT: INSERT INTO example () VALUES ();

*: DISTINCT ON (expr,...) - this is from Postgres, where expr,... must be the leftmost expressions from
the ORDER BY clause

*: MEDIAN and standard deviation... are they standard?  Essential for sqlite standalone executable for
shell script users.

_:_MEDIAN is difficult because it cannot be done "on-line," i.e., on a stream of data. Following is a
solution to MEDIAN credited to David Rozenshtein, Anatoly Abramovich, and Eugene Birger; it is
explained here: http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html

      SELECT x.Hours median
      FROM BulbLife x, BulbLife y
      GROUP BY x.Hours
      HAVING
         SUM(CASE WHEN y.Hours <= x.Hours
            THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND
         SUM(CASE WHEN y.Hours >= x.Hours
            THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1

*: Oracle's join syntax using (+) and (-):

      SELECT a1.a, a1.b, a2.a, a2.b
      FROM a1 LEFT JOIN a2 ON a2.b = a1.a

_:...can be written in Oracle as:

      SELECT a1.a, a1.b, a2.a, a2.b
      FROM a1, a2
      WHERE a1.a = a2.b(+);

*: Oracle's Named Parameter output syntax.  In Oracle, one can declare parameters and select into them
as such

_:Select A1, A2, A3 into (:p1, :p2, :p3) from TableA

*: name columns in views (i.e. CREATE VIEW (foo, bar) AS SELECT qux, quo FROM baz;)

*:FLOOR and CEILING functions, e.g. "SELECT FLOOR(salary) FROM personnel;"

*:IF EXISTS function, e.g. "DROP TABLE IF EXISTS temp;"

*:a password('') function to mask some values (as used in MySQL) would be fine, I need it, if I give the
db out of the house, or is there something I didn't find? Or a simple MD5 function to obscure data
using a one way hash. See the MySQL function MD5 or Password for examples.

*:More than one primary key per table, I can specify this with MySQL for example and SQLite returns me
an error: more than one primary key specified...

_: "More than one primary key" is an oxymoron when you're talking about the relational data model.  By
definition, a primary key uniquely identfies a row.  What's the real problem you're trying to solve?

_:: A combined primary key is possible in SQLite, for example:

		CREATE TABLE strings (
			string_id INTEGER NOT NULL,
			language_id INTEGER NOT NULL,
			string TEXT,
			PRIMARY KEY (string_id, language_id)
		);

*:UPDATE t1, t2 SET t1.f1 = value WHERE t1.f2 = t2.fa

*:SHOW TABLES and DESCRIBE [tablename] would be nice - not sure if they're standard, but they are a
rather nice feature of MySQL...
-------- No, it's not standard. The standard says it should be a special database called
INFORMATION_SCHEMA, wich contains info about all databases, tables, columns, index, views, stored
procedures, etc.

Can someone tell me how to fake describe until something like this is implemented?  Sorry, I'm too
dependent on Oracle apparently :(

*:SELECT ... FOR UPDATE OF ... is not supported.  This is understandable considering the mechanics of
SQLite in that row locking is redundant as the entire database is locked when updating any bit of it.
However, it would be good if a future version of SQLite supports it for SQL interchageability reasons if
nothing else.  The only functionality required is to ensure a "RESERVED" lock is placed on the database
if not already there.

*:DELETE from table ORDER BY column LIMIT x,y is not supported. I worked around
it by using a second query and deleting, eg: SELECT timestamp from table LIMIT x,1; DELETE from table
where timestamp < .....

*:The corollary to above, UPDATE table SET x WHERE y ORDER BY z is not supported. Haven't tried the
LIMIT addition to that form.

*:Named parts of natural joins. For example: SELECT a.c1 FROM T1 a NATURAL JOIN T1 b. Because
sqlite reduces the number of columns kept, the name is lost.

*:The ALL and ANY quantifiers for comparisons with subquery results aren't supported.

*:create table wg (
cpf numeric not null,
id numeric not null,
nome varchar(25),
primary key (cpf)
foreign key (id)
);
the foreign key (id) generate an error, a chance to be supported in the future?
foreign key dont supported in sqlite? or generater automaticaly or ?

That's not a legal FOREIGN KEY clause; you have to specify what the foreign key references.  SQLite
parses, but does not enforce, syntactically-legal FOREIGN KEY specifications; there's a PRAGMA that will
retrieve foreign-key information from table definitions, allowing you to enforce such constraints with
application code.

*: Analytical functions

(What is?)
SQL is a very capable language and there are very few questions that it cannot answer. I find that I can
come up with some convoluted SQL query to answer virtually any question you could ask from the data.
However, the performance of some of these queries is not what it should be - nor is the query itself
easy to write in the first place. Some of the things that are hard to do in straight SQL are actually very
commonly requested operations, including:

Calculate a running total - Show the cumulative salary within a department row by row, with each row
including a summation of the prior rows' salary.

Find percentages within a group - Show the percentage of the total salary paid to an individual in a
certain department. Take their salary and divide it by the sum of the salary in the department.

Top-N queries - Find the top N highest-paid people or the top N sales by region.

Compute a moving average - Average the current row's value and the previous N rows values together.

Perform ranking queries - Show the relative rank of an individual's salary within their department.

Analytic functions, are designed to address these issues. They add extensions to the SQL language that
not only make these operations easier to code; they make them faster than could be achieved with the
pure SQL approach. These extensions are currently under review by the ANSI SQL committee for
inclusion in the SQL specification.

The syntax of the analytic function is rather straightforward in appearance, but looks can be deceiving.
It starts with:

FUNCTION_NAME(<argument>,<argument>,)
OVER
(<Partition-Clause> <Order-by-Clause> <Windowing Clause>)

The PARTITION BY clause logically breaks a single result set into N groups, according to the criteria set
by the partition expressions. The words 'partition' and 'group' are used synonymously.

The ORDER BY clause specifies how the data is sorted within each group (partition).

The windowing clause gives us a way to define a sliding or anchored window of data, on which the
analytic function will operate, within a group. This clause can be used to have the analytic function
compute its value based on any arbitrary sliding or anchored window within a group.


Ex:

This example shows how to use the analytical function SUM to perform a cumulative sum. First, we fill
some values in a table. The table is very simple and consists of the field dt and xy only. Note, that for a
given date it is possible to insert multiple rows which is exactly what I do here. What I am interested is
to extract the cumulative sum for each day in the table. That is, if I have three entries for the same
date, for example 3, 4 and 5, I don't want the sum to only be 3+4+5 for each row, but 3 for the first
row, 3+4 for the second row and 3+4+5 for the third row.
create table sum_example (
  dt date,
  xy number
);

insert into sum_example values (to_date('27.08.1970','DD.MM.YYYY'),4);{linebreak}
insert into sum_example values (to_date('02.09.1970','DD.MM.YYYY'),1);{linebreak}
insert into sum_example values (to_date('09.09.1970','DD.MM.YYYY'),5);{linebreak}
insert into sum_example values (to_date('26.08.1970','DD.MM.YYYY'),3);{linebreak}
insert into sum_example values (to_date('28.08.1970','DD.MM.YYYY'),4);{linebreak}
insert into sum_example values (to_date('26.08.1970','DD.MM.YYYY'),6);{linebreak}
insert into sum_example values (to_date('29.08.1970','DD.MM.YYYY'),9);{linebreak}
insert into sum_example values (to_date('30.08.1970','DD.MM.YYYY'),2);{linebreak}
insert into sum_example values (to_date('12.09.1970','DD.MM.YYYY'),7);{linebreak}
insert into sum_example values (to_date('23.08.1970','DD.MM.YYYY'),2);{linebreak}
insert into sum_example values (to_date('27.08.1970','DD.MM.YYYY'),5);{linebreak}
insert into sum_example values (to_date('09.09.1970','DD.MM.YYYY'),9);{linebreak}
insert into sum_example values (to_date('01.09.1970','DD.MM.YYYY'),3);{linebreak}
insert into sum_example values (to_date('07.09.1970','DD.MM.YYYY'),1);{linebreak}
insert into sum_example values (to_date('12.09.1970','DD.MM.YYYY'),4);{linebreak}
insert into sum_example values (to_date('03.09.1970','DD.MM.YYYY'),5);{linebreak}
insert into sum_example values (to_date('03.09.1970','DD.MM.YYYY'),8);{linebreak}
insert into sum_example values (to_date('07.09.1970','DD.MM.YYYY'),7);{linebreak}
insert into sum_example values (to_date('04.09.1970','DD.MM.YYYY'),8);{linebreak}
insert into sum_example values (to_date('09.09.1970','DD.MM.YYYY'),1);{linebreak}
insert into sum_example values (to_date('29.08.1970','DD.MM.YYYY'),3);{linebreak}
insert into sum_example values (to_date('30.08.1970','DD.MM.YYYY'),7);{linebreak}
insert into sum_example values (to_date('24.08.1970','DD.MM.YYYY'),7);{linebreak}
insert into sum_example values (to_date('07.09.1970','DD.MM.YYYY'),9);{linebreak}
insert into sum_example values (to_date('26.08.1970','DD.MM.YYYY'),2);{linebreak}
insert into sum_example values (to_date('09.09.1970','DD.MM.YYYY'),8);{linebreak}

select dt, sum(xy) over (partition by trunc(dt) order by dt rows between
unbounded preceding and current row) s, xy from sum_example;

drop table sum_example;

The select statement will return:

23.08.70          2          2{linebreak}
24.08.70          7          7{linebreak}
26.08.70          3          3{linebreak}
26.08.70          5          2{linebreak}
26.08.70         11          6{linebreak}
27.08.70          4          4{linebreak}
27.08.70          9          5{linebreak}
28.08.70          4          4{linebreak}
29.08.70          9          9{linebreak}
29.08.70         12          3{linebreak}
30.08.70          2          2{linebreak}
30.08.70          9          7{linebreak}
01.09.70          3          3{linebreak}
02.09.70          1          1{linebreak}
03.09.70          5          5{linebreak}
03.09.70         13          8{linebreak}
04.09.70          8          8{linebreak}
07.09.70          1          1{linebreak}
07.09.70          8          7{linebreak}
07.09.70         17          9{linebreak}
09.09.70          5          5{linebreak}
09.09.70         14          9{linebreak}
09.09.70         15          1{linebreak}
09.09.70         23          8{linebreak}
12.09.70          7          7{linebreak}
12.09.70         11          4{linebreak}

The third column correspondents to xy (the values inserted with the insert into ... above). The
interesting column is the second. For example on the 26th of August in 1970, the first row for that date
is 3 (equals xy), the second is 5 (equals xy+3) and the third is 11 (equals xy+3+5).

List of analitic functions:

AVG (<distinct|all> expression )
 Used to compute an average of an expression within a group and window. Distinct may be used to find
the average of the values in a group after duplicates have been removed.

CORR (expression, expression)
 Returns the coefficient of correlation of a pair of expressions that return numbers. It is shorthand for:

COVAR_POP(expr1, expr2) /

STDDEV_POP(expr1) * STDDEV_POP(expr2)).

Statistically speaking, a correlation is the strength of an association between variables. An association
between variables means that the value of one variable can be predicted, to some extent, by the value
of the other. The correlation coefficient gives the strength of the association by returning a number
between -1 (strong inverse correlation) and 1 (strong correlation). A value of 0 would indicate no
correlation.

COUNT (<distinct> <*> <expression>)
 This will count occurrences within a group. If you specify * or some non-null constant, count will count
all rows. If you specify an expression, count returns the count of non-null evaluations of expression.
You may use the DISTINCT modifier to count occurrences of rows in a group after duplicates have been
removed.

COVAR_POP (expression, expression)
 This returns the population covariance of a pair of expressions that return numbers.

COVAR_SAMP (expression, expression)
 This returns the sample covariance of a pair of expressions that return numbers.

CUME_DIST
 This computes the relative position of a row in a group. CUME_DIST will always return a number
greater then 0 and less then or equal to 1. This number represents the 'position' of the row in the
group of N rows. In a group of three rows, the cumulate distribution values returned would be 1/3, 2/3,
and 3/3 for example.

DENSE_RANK
 This function computes the relative rank of each row returned from a query with respect to the other
rows, based on the values of the expressions in the ORDER BY clause. The data within a group is sorted
by the ORDER BY clause and then a numeric ranking is assigned to each row in turn starting with 1 and
continuing on up. The rank is incremented every time the values of the ORDER BY expressions change.
Rows with equal values receive the same rank (nulls are considered equal in this comparison). A dense
rank returns a ranking number without any gaps. This is in comparison to RANK below.

FIRST_VALUE
 This simply returns the first value from a group.

LAG (expression, <offset>, <default>)
 LAG gives you access to other rows in a resultset without doing a self-join. It allows you to treat the
cursor as if it were an array in effect. You can reference rows that come before the current row in a
given group. This would allow you to select 'the previous rows' from a group along with the current
row. See LEAD for how to get 'the next rows'.

Offset is a positive integer that defaults to 1 (the previous row). Default is the value to be returned if
the index is out of range of the window (for the first row in a group, the default will be returned)

LAST_VALUE
 This simply returns the last value from a group.

LEAD (expression, <offset>, <default>)
 LEAD is the opposite of LAG. Whereas LAG gives you access to the a row preceding yours in a group -
LEAD gives you access to the a row that comes after your row.

Offset is a positive integer that defaults to 1 (the next row). Default is the value to be returned if the
index is out of range of the window (for the last row in a group, the default will be returned).

MAX(expression)
 Finds the maximum value of expression within a window of a group.

MIN(expression)
 Finds the minimum value of expression within a window of a group.

NTILE (expression)
 Divides a group into 'value of expression' buckets.

For example; if expression = 4, then each row in the group would be assigned a number from 1 to 4
putting it into a percentile. If the group had 20 rows in it, the first 5 would be assigned 1, the next 5
would be assigned 2 and so on. In the event the cardinality of the group is not evenly divisible by the
expression, the rows are distributed such that no percentile has more than 1 row more then any other
percentile in that group and the lowest percentiles are the ones that will have 'extra' rows. For example,
using expression = 4 again and the number of rows = 21, percentile = 1 will have 6 rows, percentile =
2 will have 5, and so on.

PERCENT_RANK
 This is similar to the CUME_DIST (cumulative distribution) function. For a given row in a group, it
calculates the rank of that row minus 1, divided by 1 less than the number of rows being evaluated in
the group. This function will always return values from 0 to 1 inclusive.

RANK
 This function computes the relative rank of each row returned from a query with respect to the other
rows, based on the values of the expressions in the ORDER BY clause. The data within a group is sorted
by the ORDER BY clause and then a numeric ranking is assigned to each row in turn starting with 1 and
continuing on up. Rows with the same values of the ORDER BY expressions receive the same rank;
however, if two rows do receive the same rank the rank numbers will subsequently 'skip'. If two rows
are number 1, there will be no number 2 - rank will assign the value of 3 to the next row in the group.
This is in contrast to DENSE_RANK, which does not skip values.

RATIO_TO_REPORT (expression)
 This function computes the value of expression / (sum(expression)) over the group.

This gives you the percentage of the total the current row contributes to the sum(expression).

REGR_ xxxxxxx (expression, expression)
 These linear regression functions fit an ordinary-least-squares regression line to a pair of expressions.
There are 9 different regression functions available for use.

ROW_NUMBER
 Returns the offset of a row in an ordered group. Can be used to sequentially number rows, ordered by
certain criteria.

STDDEV (expression)
 Computes the standard deviation of the current row with respect to the group.

STDDEV_POP (expression)
 This function computes the population standard deviation and returns the square root of the
population variance. Its return value is same as the square root of the VAR_POP function.

STDDEV_SAMP (expression)
 This function computes the cumulative sample standard deviation and returns the square root of the
sample variance. This function returns the same value as the square root of the VAR_SAMP function
would.

SUM(expression)
 This function computes the cumulative sum of expression in a group.

VAR_POP (expression)
 This function returns the population variance of a non-null set of numbers (nulls are ignored).
VAR_POP function makes the following calculation for us:

(SUM(expr*expr) - SUM(expr)*SUM(expr) / COUNT(expr)) / COUNT(expr)

VAR_SAMP (expression)
 This function returns the sample variance of a non-null set of numbers (nulls in the set are ignored).
This function makes the following calculation for us:

(SUM(expr*expr) - SUM(expr)*SUM(expr) / COUNT(expr)) / (COUNT(expr) - 1)

VARIANCE (expression)
 This function returns the variance of expression. Oracle will calculate the variance as follows:

0 if the number of rows in expression = 1

VAR_SAMP if the number of rows in expression > 1

More details on the web ... ask tom !?

====
FEATURES ADDED IN RECENT VERSIONS
====
*: Extended POSIX regular expressions (should be easy, man 3 regcomp, or http://
mirbsd.bsdadvocacy.org/man3/regcomp.htm for reference)
   SELECT * FROM table WHERE name REGEX '[a-zA-Z]+_{0,3}';

The infrastructure for this syntax now exists, but you have to create a user-defined regex matching
function.

*: The EXISTS keyword is not supported (IN is, but IN is only a special case of EXISTS). And what about
corelated subqueries ?

_:Both supported as of 3.1.

*: Inserting blob using X'AABBCCDD' syntax. (note: supported in Sqlite3)

*: CURRENT-Functions like CURRENT_DATE, CURRENT_TIME are missing _Try "SELECT date('now');" or
"SELECT datetime('now','localtime');"_

_:Added as of 3.1

*: ESCAPE clause for LIKE

_:Added as of 3.1

*:AUTO_INCREMENT field type. SQLite supports auto_incrementing fields but only if that field is set as
"INTEGER PRIMARY KEY".

_:Oh god no! Stop the evil from spreading! AUTO_INCREMENT is possibly the worst way of doing unique
ids for tables. It requires cached per-connection-handle last_insert_id() values. And you're probably
already familiar with how much of a hack THAT is.

_:A much better solution would be to give SQLite proper SEQUENCE support. You already have a private
table namespace, so using sqlite_sequences to store these wouldn't be such a big deal. This is created
when the database is created, and looks something like this, taken from a perl MySQL sequence
emulation module.

        create table mysql_sequences (
            sequence_name char(32) not null primary key,
            sequence_start bigint not null default 1,
            sequence_increment bigint not null default 1,
            sequence_value bigint not null default 1
        )

_:In fact, why don't you just take a look at the original module (DBIx::MySQLSequence): http://
search.cpan.org/~adamk/DBIx-MySQLSequence-0.1/MySQLSequence.pm. In fact, why don't you just
copy that module, and rewrite using code inside the database.

_:The main reason for doing this, is that if you want to insert multiple records which reference each
other, and these references are not null, you cannot insert one record until you have inserted the one to
which it refers, then fetched the last_insert_id(), added it to the other record, then insert that, and so in.
In trivial cases this isn't too bad, but imagine the cases where you have circular references, or don't
know the structure of the data in advance at all.

_:With sequence support and access to ids before inserting, there are algorithms to resolve these cases.
Without it, you are left with things like just outright suspending contraints checking, inserting
everything incorrectly, then hoping you can find all the cases of broken values, and fixing them. Which
sucks if you don't know the structure beforehand.

_:To resolve compatibility issues, just do what you do now with the INTEGER PRIMARY_KEY fields with
no default, but allow a DEFAULT SEQUENCENAME.NEXTVAL() or something...

_::For better or worse, the requested feature was added in 3.1

*:SELECT t1.ID, (SELECT COUNT(*) FROM t2 WHERE t2.ID=t1.ID) FROM t1{linebreak}
_:In other words, in a subselect backreferencing to a field in its parent select.

_::Now supported as of 3.1


===========
REMARK
===========
Sqlite is finally a database product that values performance and minimal footprint (disk and memory)
above a trashcan strategy that would add whatever feature to make the result so-called 'feature rich',
say, a bloated piece of software. Therefore, I would vehemently reject all additions listed above, except
for one. It's quite difficult to obtain the result for a correlated 'NOT EXISTS' subquery in any alternative
way; which is the choice way to determine a subset of data that doesn't satisfy criteria contained in
another table.

In my experience I have found 'NOT EXISTS' (or is it 'NOT IN') to be extraordinarly slow. Being that
SQLite provides 'EXCEPT' the much faster construct can be used to the same end (at least it was
faster with Oracles's equvalent: 'MINUS', to wit:

	select name,addr from employee where id not in (select id from sales)

becomes

	select name,addr from employee where id in (
		select id from employee
		except
		select id from sales
	)


-- Are you calling Oracle 'a bloated piece of software'?. LOL. I would love to see a comparison of Oracle
and SQLite (latest stable or bleeding edge SQLite version Vs Oracle 10g). I would love it. [This
comparison idea is as valid as comparing a novel to a short story.] Anyway, SQLite seems a lil' database
engine for lil' works. Sorry, not enough for me :). -- Why would anyone compare Oracle to sqlite other
than to say "can you add support for this Oracle syntax to make migration between them easier"? --
Someone might
mistakenly compare Oracle
to SQLite because they fail to comprehend that the two products solve
very different problems.

*: Up to this moment I thought that Postgree was smallest possible free DB engine (since MySQL is
*NOT* free), so if you are looking for something to distribute along with your application, SQLite seem
to win against 10g, MySQL, Postgree, or whatever (by Makc). - Just to be awkward, what about
berkelydb etc? And when you say free you mean free of restrictions don't you (rather than Free
software)?  -- Berkeley DB does not include SQL support.

*: To above paragraph. Sometimes it is better pay few hundreds of bucks (just few hours of
my work rate) and get much more powerful commercial solution which is royalty free. For example I
very love Valentina database - (http://www.paradigmasoft.com).  Valentina beats anything in 10-100+
times, especially on big dbs . It is not expensive, royalty free. Really full SQL92, yet they have cool
Object-Relational features.

*: To above paragraph: 'Valentina beats anything in 10-100+ times'. Ok, 10-100+ times of WHAT?.
RAM Usage?, CPU Usage?, CPU Cycles?, CPU Count?, Consistent Gets?, Concurrent Users?, Concurrent
Transactions?, Parses?, Executes?, Fetches?, Recursive Calls?, Physical Reads?, Documentation? ... If you
don't have arguments based on real data in a production environment with real data load (users &
transactions) your comment is useless. Benchmark it, prove it and then show us your results, not your
'thoughts'.

*: Let me answer. I have talk about speed :-) About time of queries execution in seconds. Example. I
have bench Table with million records. 10 fields of all types. 100MB size. Next query "SELECT DISTINCT
* From T1" Valentina do in 1.7 seconds, SqlLite in 280 seconds. Difference 180 times. Or query:
"SELECT * FROM T1 ORDER BY fld_byte, fld_varchar Desc"  Valentina do in 1.99 seconds, SqlLite in 115
seconds. Difference 55 times. And this is just sigle table queries. I do not mention joins on 3-5-7
tables with, with GROUP BY, HAVING, ...  And more. If to make database in 10 million records, so db
grow to 1Gb the you will see even better Valentina win. If you want get information about real
production power then just go to their users testimonials page and read quotes starting from 1998 year
it seems.

I wonder how useful these "remarks" are...

What about Apache Derby? It uses the Apache 2.0 license and is easy to embed in Java applications
(http://db.apache.org/derby/). -- See SqliteVersusDerby

----
Tcl related
----

*: Tcl variable bindings for list types?  i.e.:

  set values [list a b c]
  db eval { SELECT * FROM table WHERE x IN ($values) }

SQLite does its own variable interpolation which avoids the (messy) need to do value quoting/escaping
(to protect against SQL injection attacks, etc.) but in the case where it's an "IN ($variable)" clause, it
treats $variable as a single value instead of a Tcl list of values.  Or, maybe I'm doing something wrong.
If I am, please let me know: dossy@panoptic.com.