On Tue, 31 Dec 2013 20:43:20 +0100
big stone <stonebi...@gmail.com> wrote:
> To get CTE in SQLite, I guess we must answer by the example the fears
> expressed by Simon and Rsmith.
>
> I propose the following method :
> - unproven-demand :
...
> - code size + performance increase fear :
It seems to me you have more substantial hurdle, namely
0. undemonstrated utility
(See my reply to you on 31 Dec 2013 13:59:26 -0500 for more details.)
Whether or not CTEs are wanted and easily implemented, they add exactly
zero to SQLite's capability. All they do is afford another form or
equivalent expression for the same query. That is: complexity without
power.
Meanwhile, here's a much more important failing that cannot be worked
around within SQL without a temporary table:
sqlite> create table i ( i int primary key );
sqlite> insert into i values (1);
sqlite> insert into i values (2);
sqlite> update i set i = i + 1;
SQL error: column i is not unique
The workaround is:
0. BEGIN TRANSACTION
1. Create a temporary table T to hold the new values with
CREATE TEMPORARY TABLE T AS SELECT ...
2. Delete rows from main table M based on the old values
3. INSERT INTO M SELECT * FROM T;
4. COMMIT TRANSACTION;
5. DROP TABLE T;
That only works in SQLite because a user-defined transaction locks the
database. In most DBMSs the same SQL leaves the main table M open to
update by another process, making the results indeterminate.
The problem crops up anytime unique constraints are in force, including
updating primary keys. I know of no more important flaw in SQLite.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
