On 2015-08-17 06:08 PM, Olivier Barthelemy wrote: > OK, > I already had removed my constraint as it is, since it is obviously invalid > > So for me : > Am i right to leave a constraint, say CHECK (varname=0 OR varname=1), to > avoid other integer values, or is the constraint useless? (i guess the > answer will be 'it depends on your code' :-P)
The answer is nothing of the sort. SQL is an algebraic construct and the answer should always be 100% verifiable and correct. The CHECK constraints are designed exactly for the sort of thing you are trying to do. The fact that Qt interprets, or used to interpret it differently, is of no concern to SQL. What we can tell you is that in SQLite, the type specified as BOOLEAN will be regarded as a Numeric type and will think of values as TRUE or FALSE based on whether they are numerically equal to 1 or 0. If the check constraint needs to check trueness/falseness, then that is how you need to phrase it. This is true now and in the past, although in the past Qt may have forwarded a value of 'true' as a 1 or stored it as the actual string 'true' or some such. > > And for sqlite itself : > As stated in the second question of my first message, shouldn't there be > some check in sqlite that the type in the constraints are compatible with > the fields, with an error at table creation? SQLite is a Typeless Database system, this is one of its most charming features. You can put anything in a column of any kind. It does come with some nasty surprises for people used to database systems that enforces type compatibility or any other compatibility. For instance, people declare a table with a column with type VARCHAR(3) then insert the name 'Johnathan' into it and are very surprised when they query it back and it returns the whole of 'Jonathan' as opposed the expected 'Joh' only (as a relaxed-setting MariaDB would) or indeed error out with a over-range message as MSSQL or PostGres would. This has many advantages especially if you use it as a local storage to other DB systems but it does require those check constraints to implicitly enforce value class compatibility. > > 2015-08-17 17:56 GMT+02:00 Simon Slavin <slavins at bigfraud.org>: > >> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy <barthelemy at >> geovariances.com> >> wrote: >> >>>> SQLite has no such type >>> I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything >> else. >>> The statement is passed as is to sqlite. >> SQLite is interpreting BOOLEAN as NUMERIC. See the last line of the table >> in section 2.2 of >> >> <https://www.sqlite.org/datatype3.html> >> >> This is in most cases the right thing to do, since BOOLEAN values stored >> by most SQL programming are really the numbers 0 and 1. However your CHECK >> constraints treat the values as if they're strings: >> >>> storage_implicit = 'true' OR >>> storage_implicit = 'false' >> and if the values are understood as NUMERIC, neither of those string >> comparisons will ever by true, so all INSERT commands will fail their >> constraint checks. >> >>> On previously created sqlite files, when i open then in sqlite manager >>> addon of Firefox, the field is still displayed as BOOLEAN >> FireFox is showing you the command that was used to create the table. >> This is an (unfortunate ?) aspect of how SQLite works: it stores the >> creation string rather than details of how it was understood. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > > 2015-08-17 17:56 GMT+02:00 Simon Slavin <slavins at bigfraud.org>: > >> On 17 Aug 2015, at 4:50pm, Olivier Barthelemy <barthelemy at >> geovariances.com> >> wrote: >> >>>> SQLite has no such type >>> I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything >> else. >>> The statement is passed as is to sqlite. >> SQLite is interpreting BOOLEAN as NUMERIC. See the last line of the table >> in section 2.2 of >> >> <https://www.sqlite.org/datatype3.html> >> >> This is in most cases the right thing to do, since BOOLEAN values stored >> by most SQL programming are really the numbers 0 and 1. However your CHECK >> constraints treat the values as if they're strings: >> >>> storage_implicit = 'true' OR >>> storage_implicit = 'false' >> and if the values are understood as NUMERIC, neither of those string >> comparisons will ever by true, so all INSERT commands will fail their >> constraint checks. >> >>> On previously created sqlite files, when i open then in sqlite manager >>> addon of Firefox, the field is still displayed as BOOLEAN >> FireFox is showing you the command that was used to create the table. >> This is an (unfortunate ?) aspect of how SQLite works: it stores the >> creation string rather than details of how it was understood. >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
