On: Strict Tables – Column type constraints in SQLite - Draft

[mingodad]

Here is a sample to test the "pedantic" patch:

====

select "23 % 3", 23 % 3;

select "12.3 % 3", 12.3 % 3;

select "12 % 2.5", 12 % 2.5;

select "23 / 3", 23 / 3;

select "12.3 / 3", 12.3 / 3;

select "12 / 2.5", 12 / 2.5;

create table ta(a text, b integer, c float);

select 'insert declared types == value types';

insert into ta(a,b,c) values('a', 1, 2.0);

select 'insert declared types != value types text';

insert into ta(a,b,c) values('b', '1', '2.0');

select 'insert declared types != value types';

insert into ta(a,b,c) values('c', 1.0, 2);

select 'update declared types == value types';

update ta set a = 'a' ,b = 1, c = 2.0 where a = 'a';

select 'update declared types != value types text';

update ta set a = 'a' ,b = '1', c = '2.0' where a = 'a';

select 'update declared types != value types';

update ta set a = 'a' ,b = 1.0, c = 2 where a = 'a';

select 'update one value declared types != value types';

update ta set b = 1.0 where a = 'a';

select 'update one value declared types != value types';

update ta set a = 49 where a = 'b';

====

Output default sqlite:

====

23 % 3|2

12.3 % 3|0.0

12 % 2.5|0.0

23 / 3|7

12.3 / 3|4.1

12 / 2.5|4.8

insert declared types == value types

insert declared types != value types text

insert declared types != value types

update declared types == value types

update declared types != value types text

update declared types != value types

update one value declared types != value types

update one value declared types != value types

====

Output of sqlite with "pedantic":

====

23 % 3|2

FP Remainder received non integer values 3.000000 :: 12.300000

12.3 % 3|0.0

FP Remainder received non integer values 2.500000 :: 12.000000

12 % 2.5|0.0

23 / 3|7

FP Division received non integer values 3.000000 :: 12.300000

12.3 / 3|4.1

FP Division received non integer values 2.500000 :: 12.000000

12 / 2.5|4.8

insert declared types == value types

insert declared types != value types text

Affinity applied on make record 1 : 1 : D

Affinity applied on make record 2 : 1 : E

insert declared types != value types

Affinity applied on make record 1 : 2 : D

update declared types == value types

update declared types != value types text

Affinity applied on make record 1 : 1 : D

Affinity applied on make record 2 : 1 : E

update declared types != value types

Affinity applied on make record 1 : 2 : D

update one value declared types != value types

Affinity applied on make record 1 : 2 : D

update one value declared types != value types

Affinity applied on make record 0 : 3 : B

====