Hello!
The documentation speak
"Datatypes In SQLite Version 3
The dynamic type system of SQLite is backwards compatible with the more common
static type systems of other database engines in the sense that SQL statement
that work on statically typed databases should would the same way in SQLite."
But is't wrong and SQLite type system is incompatible with other databases and
produce logic errors.
SQLite is typeless database but
$ sqlite3 :memory:
SQLite version 3.6.19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select 1='1';
0
PostgreSQL is strict typing database but
$ psql -h localhost --cluster 8.1/testing -U postgres template1
template1=> select 1='1';
?column?
----------
t
(1 row)
In SQLite selects, views and triggers on views there are a lot of problems as
result of the bug.
As example the trigger is work incorrect for _numeric_ group_name because the
group_name field is defined as text:
CREATE TRIGGER view_user_service_hw_insert instead of insert on
view_user_service_hw
begin
select RAISE (ABORT,'Service does not found')
WHERE (select count(id) from user_service where id=NEW.user_service_id
and delete_date IS NULL)=0;
select RAISE (ABORT,'The hardware is used yet')
WHERE (select count(id) from view_user_service_hw where
user_service_id=NEW.user_service_id and group_name=NEW.group_name and
delete_date IS NULL)>0;
insert into user_service_hw
(user_service_id,hw_id)
select
NEW.user_service_id, id from hw_telephony_number where
group_name=NEW.group_name and 'telephony_number'=(select hw_name from
view_user_service where id=NEW.user_service_id);
end;
And we must patch this like to
CREATE TRIGGER view_user_service_hw_insert instead of insert on
view_user_service_hw
begin
select RAISE (ABORT,'Service does not found')
WHERE (select count(id) from user_service where id=NEW.user_service_id
and delete_date IS NULL)=0;
select RAISE (ABORT,'The hardware is used yet')
WHERE (select count(id) from view_user_service_hw where
user_service_id=NEW.user_service_id and group_name=cast(NEW.group_name as text)
and delete_date IS NULL)>0;
insert into user_service_hw
(user_service_id,hw_id)
select
NEW.user_service_id, id from hw_telephony_number where
group_name=cast(NEW.group_name as text) and 'telephony_number'=(select hw_name
from view_user_service where id=NEW.user_service_id);
end;
There are no same problems in PostgreSQL and so SQLite has incompatible SQL.
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
