column types and constraints

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
54 messages Options
123
Reply | Threaded
Open this post in threaded view
|

column types and constraints

Mark Wagner
I recently pointed out that sqlite doesn't enforce type names and
constraints when creating tables but I was unable to explain/justify this
behavior.  I'm sure this has come up before and there's a clear answer but
I didn't find it easily.

For example this is accepted without error:  CREATE TABLE bar2 (x happy
days);

-- Mark
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Simon Slavin-3
On 27 Jun 2018, at 11:56pm, Mark Wagner <[hidden email]> wrote:

> I recently pointed out that sqlite doesn't enforce type names and
> constraints when creating tables but I was unable to explain/justify this
> behavior.  I'm sure this has come up before and there's a clear answer but
> I didn't find it easily.

The usual answer for these things is backward compatiibility.  The bug existed for so long before it was spotted that SQLite3 must continue to support it.  Roll on SQLite4.

> For example this is accepted without error:  CREATE TABLE bar2 (x happy
> days);

You can analyse what's actually happening:

SQLite version 3.22.0 2017-12-05 15:00:17
sqlite> CREATE TABLE bar2 (x happy days);
sqlite> .headers on
sqlite> .mode column
sqlite> PRAGMA table_info(bar2);
cid         name        type        notnull     dflt_value  pk        
----------  ----------  ----------  ----------  ----------  ----------
0           x           happy
days  0                       0  

The interpretation is that the first two words are a columnname and type, and the third word is a column ID, which is not useful.  And there's some sort of formatting bug in the CLI.  :-(

So don't do that.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Keith Medcalf

In the current tip of trunk it pretends the unknown tokens are surrounded by double-quotes.  Until you interpose a non type keyword ... at which point the parser stops "eating your junk as the type declaration" and resumes the grammar ..

sqlite> create table x(x happy days);
sqlite> pragma table_info(x);
0|x|happy days|0||0

sqlite> create table y(x happy not days);
Error: near "days": syntax error

sqlite> create table y(x happy integer days);
sqlite> pragma table_info(y);
0|x|happy integer days|0||0

sqlite> create table z(x happy dumbledorf the wood elf integer days);
sqlite> pragma table_info(z);
0|x|happy dumbledorf the wood elf integer days|0||0


Presumably this is so you can do things like:

create table x(x datetime text); -- the affinity of the column is text
rather than
create table x(x "datetime text"); -- the affinity of the column is text


However, AS does not "eat your junk" and you have to use quotes to embed spaces in the column/type string ...

sqlite> select x as dingbat french fries from x;
Error: near "french": syntax error
sqlite> select x as "dingbat french fries" from x;
1

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Simon Slavin
>Sent: Wednesday, 27 June, 2018 17:07
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>On 27 Jun 2018, at 11:56pm, Mark Wagner <[hidden email]> wrote:
>
>> I recently pointed out that sqlite doesn't enforce type names and
>> constraints when creating tables but I was unable to
>explain/justify this
>> behavior.  I'm sure this has come up before and there's a clear
>answer but
>> I didn't find it easily.
>
>The usual answer for these things is backward compatiibility.  The
>bug existed for so long before it was spotted that SQLite3 must
>continue to support it.  Roll on SQLite4.
>
>> For example this is accepted without error:  CREATE TABLE bar2 (x
>happy
>> days);
>
>You can analyse what's actually happening:
>
>SQLite version 3.22.0 2017-12-05 15:00:17
>sqlite> CREATE TABLE bar2 (x happy days);
>sqlite> .headers on
>sqlite> .mode column
>sqlite> PRAGMA table_info(bar2);
>cid         name        type        notnull     dflt_value  pk
>----------  ----------  ----------  ----------  ----------  ---------
>-
>0           x           happy
>days  0                       0
>
>The interpretation is that the first two words are a columnname and
>type, and the third word is a column ID, which is not useful.  And
>there's some sort of formatting bug in the CLI.  :-(
>
>So don't do that.
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Igor Tandetnik-2
In reply to this post by Mark Wagner
On 6/27/2018 6:56 PM, Mark Wagner wrote:
> I recently pointed out that sqlite doesn't enforce type names and
> constraints when creating tables but I was unable to explain/justify this
> behavior.

https://sqlite.org/datatype3.html

SQLite attempts to be maximally compatible with a wide variety of database engines, and those use all kinds of naming conventions for their data types. SQLite itself doesn't really have column data types - at least, not in the traditional sense. It has a concept of type affinity.

To this end, SQLite accepts any sequence of names, optionally followed by one or two numbers in parentheses, as a valid column type: https://sqlite.org/syntax/type-name.html . These names and numbers are largely ignored, except to the extent that a column type affinity is gleaned from them via a simple substring match.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Richard Hipp-3
In reply to this post by Mark Wagner
On 6/27/18, Mark Wagner <[hidden email]> wrote:
> I recently pointed out that sqlite doesn't enforce type names and
> constraints when creating tables but I was unable to explain/justify this
> behavior.  I'm sure this has come up before and there's a clear answer but
> I didn't find it easily.
>
> For example this is accepted without error:  CREATE TABLE bar2 (x happy
> days);

In the early days of SQLite, the goal was to get it to parse the
CREATE TABLE statements of as many different SQL engines as possible.
I looked at the supported datatypes of contemporary engines, and they
were all different.  So to maximize compatibility, I made the decision
to mostly ignore the "type" and accept any sequence of identifiers as
the type.  The actual type used it computed according to the following
rules, in order:

(1) If the type name contains "INT" then use INTEGER
(2) If the type name contains "CHAR", "CLOB", or "TEXT" then use TEXT
(3) If the type name contains "BLOB" then use BLOB
(4) If the type name contains "REAL", "FLOA", or "DOUB" then use REAL
(5) Otherwise use NUMERIC

Those rules are defined here: https://www.sqlite.org/datatype3.html#affname

This flexible type-name arrangement works because SQLite is very
forgiving about you putting non-proscribed values into columns - it
tries to convert if it can do so without loss of information but if it
cannot do a reversible type conversion it simply stores whatever you
give it.  Hence if you store a string '3456' into an INT column, it
converts the string into an integer, but if you store a string 'xyzzy'
in an INT column it will actually store the string value.

After the above decisions were made, SQLite became the most widely
used database engine on the planet and over a trillion SQLite database
files got created, and now we need to stick with that original idea
lest we cause compatibility issues for all that legacy.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Mark Wagner
Thanks for all the good background.  FWIW this came up because someone had
created a row with something like:  (column_name non null).  Needless to
say, this created a column without a "not null" constraint.



On Wed, Jun 27, 2018 at 5:02 PM Richard Hipp <[hidden email]> wrote:

> On 6/27/18, Mark Wagner <[hidden email]> wrote:
> > I recently pointed out that sqlite doesn't enforce type names and
> > constraints when creating tables but I was unable to explain/justify this
> > behavior.  I'm sure this has come up before and there's a clear answer
> but
> > I didn't find it easily.
> >
> > For example this is accepted without error:  CREATE TABLE bar2 (x happy
> > days);
>
> In the early days of SQLite, the goal was to get it to parse the
> CREATE TABLE statements of as many different SQL engines as possible.
> I looked at the supported datatypes of contemporary engines, and they
> were all different.  So to maximize compatibility, I made the decision
> to mostly ignore the "type" and accept any sequence of identifiers as
> the type.  The actual type used it computed according to the following
> rules, in order:
>
> (1) If the type name contains "INT" then use INTEGER
> (2) If the type name contains "CHAR", "CLOB", or "TEXT" then use TEXT
> (3) If the type name contains "BLOB" then use BLOB
> (4) If the type name contains "REAL", "FLOA", or "DOUB" then use REAL
> (5) Otherwise use NUMERIC
>
> Those rules are defined here:
> https://www.sqlite.org/datatype3.html#affname
>
> This flexible type-name arrangement works because SQLite is very
> forgiving about you putting non-proscribed values into columns - it
> tries to convert if it can do so without loss of information but if it
> cannot do a reversible type conversion it simply stores whatever you
> give it.  Hence if you store a string '3456' into an INT column, it
> converts the string into an integer, but if you store a string 'xyzzy'
> in an INT column it will actually store the string value.
>
> After the above decisions were made, SQLite became the most widely
> used database engine on the planet and over a trillion SQLite database
> files got created, and now we need to stick with that original idea
> lest we cause compatibility issues for all that legacy.
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Richard Hipp-3
On 6/27/18, Mark Wagner <[hidden email]> wrote:
> Thanks for all the good background.  FWIW this came up because someone had
> created a row with something like:  (column_name non null).  Needless to
> say, this created a column without a "not null" constraint.

It should have.  I get an error when I type:

CREATE TABLE t1(x NOT NULL);
INSERT INTO t1(x) VALUES(NULL);

I think something else must be going on.  Do you have an exact copy of
what "someone" typed?


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Mark Wagner
Sorry, my typo (I had entered the corrected code).  This:

create table t1(x text non null);

insert into t1(x) values(null);

select * from t1;



On Wed, Jun 27, 2018 at 6:14 PM Richard Hipp <[hidden email]> wrote:

> On 6/27/18, Mark Wagner <[hidden email]> wrote:
> > Thanks for all the good background.  FWIW this came up because someone
> had
> > created a row with something like:  (column_name non null).  Needless to
> > say, this created a column without a "not null" constraint.
>
> It should have.  I get an error when I type:
>
> CREATE TABLE t1(x NOT NULL);
> INSERT INTO t1(x) VALUES(NULL);
>
> I think something else must be going on.  Do you have an exact copy of
> what "someone" typed?
>
>
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Igor Tandetnik-2
In reply to this post by Richard Hipp-3
On 6/27/2018 9:14 PM, Richard Hipp wrote:
> On 6/27/18, Mark Wagner <[hidden email]> wrote:
>> Thanks for all the good background.  FWIW this came up because someone had
>> created a row with something like:  (column_name non null).  Needless to
>> say, this created a column without a "not null" constraint.
>
> It should have.  I get an error when I type:

Note the typo: "non null" where "not null" was meant. This creates a column with type "non". I'm not sure why "null" is accepted though - no path through syntax diagram seems to allow it at that spot. Perhaps there's an undocumented column constraint "NULL", to complement "NOT NULL"?
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Richard Hipp-3
On 6/27/18, Igor Tandetnik <[hidden email]> wrote:

> On 6/27/2018 9:14 PM, Richard Hipp wrote:
>> On 6/27/18, Mark Wagner <[hidden email]> wrote:
>>> Thanks for all the good background.  FWIW this came up because someone
>>> had
>>> created a row with something like:  (column_name non null).  Needless to
>>> say, this created a column without a "not null" constraint.
>>
>> It should have.  I get an error when I type:
>
> Note the typo: "non null" where "not null" was meant. This creates a column
> with type "non". I'm not sure why "null" is accepted though - no path
> through syntax diagram seems to allow it at that spot. Perhaps there's an
> undocumented column constraint "NULL", to complement "NOT NULL"?

"NULL" without the "NOT" is a valid constraint.  So the datatype is
"NON" and it has a "NULL" constraint, meaning is able to hold NULL
(the default).

This works on PosgreSQL, MySQL, and Oracle, for example:

     CREATE TABLE t1(x INT NULL);

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Igor Tandetnik-2
On 6/27/2018 10:43 PM, Richard Hipp wrote:

> On 6/27/18, Igor Tandetnik <[hidden email]> wrote:
>> On 6/27/2018 9:14 PM, Richard Hipp wrote:
>>> On 6/27/18, Mark Wagner <[hidden email]> wrote:
>>>> Thanks for all the good background.  FWIW this came up because someone
>>>> had
>>>> created a row with something like:  (column_name non null).  Needless to
>>>> say, this created a column without a "not null" constraint.
>>>
>>> It should have.  I get an error when I type:
>>
>> Note the typo: "non null" where "not null" was meant. This creates a column
>> with type "non". I'm not sure why "null" is accepted though - no path
>> through syntax diagram seems to allow it at that spot. Perhaps there's an
>> undocumented column constraint "NULL", to complement "NOT NULL"?
>
> "NULL" without the "NOT" is a valid constraint.

It's not mentioned here though: https://sqlite.org/syntax/column-constraint.html
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Mark Wagner
In reply to this post by Richard Hipp-3
Great explanation.  Thanks.

On Wed, Jun 27, 2018 at 7:43 PM Richard Hipp <[hidden email]> wrote:

> On 6/27/18, Igor Tandetnik <[hidden email]> wrote:
> > On 6/27/2018 9:14 PM, Richard Hipp wrote:
> >> On 6/27/18, Mark Wagner <[hidden email]> wrote:
> >>> Thanks for all the good background.  FWIW this came up because someone
> >>> had
> >>> created a row with something like:  (column_name non null).  Needless
> to
> >>> say, this created a column without a "not null" constraint.
> >>
> >> It should have.  I get an error when I type:
> >
> > Note the typo: "non null" where "not null" was meant. This creates a
> column
> > with type "non". I'm not sure why "null" is accepted though - no path
> > through syntax diagram seems to allow it at that spot. Perhaps there's an
> > undocumented column constraint "NULL", to complement "NOT NULL"?
>
> "NULL" without the "NOT" is a valid constraint.  So the datatype is
> "NON" and it has a "NULL" constraint, meaning is able to hold NULL
> (the default).
>
> This works on PosgreSQL, MySQL, and Oracle, for example:
>
>      CREATE TABLE t1(x INT NULL);
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Simon Slavin-3
In reply to this post by Igor Tandetnik-2
On 28 Jun 2018, at 5:43am, Igor Tandetnik <[hidden email]> wrote:

> It's not mentioned here though: https://sqlite.org/syntax/column-constraint.html

The syntax diagrams in the SQLite documentation are ... what's the term ? ... sufficient but not exhaustive.  In other words you can use some forms which violate the syntax diagrams without getting an error message.

Normal warnings apply: your "illegal" form may be accepted by one version of SQLite, but a later form may reject it as a syntax error.  Or worse still, it may accept it but interpret it differently.  So try not to do that.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Thomas Kurz
In reply to this post by Richard Hipp-3
I understand that you do not want to break compatibility. But couldn't a PRAGMA STRICT_SQL (or the like) be introduced that would force to

a) reject CREATE statements with unknown declarations (I often use "STRING" for the datatype, leading to hard-to-find problems as SQLite uses a numeric type for such a column)

b) reject INSERT (or the like) statements with data that do not match the declaration


----- Original Message -----
From: Richard Hipp <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Thursday, June 28, 2018, 02:02:26
Subject: [sqlite] column types and constraints

On 6/27/18, Mark Wagner <[hidden email]> wrote:
> I recently pointed out that sqlite doesn't enforce type names and
> constraints when creating tables but I was unable to explain/justify this
> behavior.  I'm sure this has come up before and there's a clear answer but
> I didn't find it easily.

> For example this is accepted without error:  CREATE TABLE bar2 (x happy
> days);

In the early days of SQLite, the goal was to get it to parse the
CREATE TABLE statements of as many different SQL engines as possible.
I looked at the supported datatypes of contemporary engines, and they
were all different.  So to maximize compatibility, I made the decision
to mostly ignore the "type" and accept any sequence of identifiers as
the type.  The actual type used it computed according to the following
rules, in order:

(1) If the type name contains "INT" then use INTEGER
(2) If the type name contains "CHAR", "CLOB", or "TEXT" then use TEXT
(3) If the type name contains "BLOB" then use BLOB
(4) If the type name contains "REAL", "FLOA", or "DOUB" then use REAL
(5) Otherwise use NUMERIC

Those rules are defined here: https://www.sqlite.org/datatype3.html#affname

This flexible type-name arrangement works because SQLite is very
forgiving about you putting non-proscribed values into columns - it
tries to convert if it can do so without loss of information but if it
cannot do a reversible type conversion it simply stores whatever you
give it.  Hence if you store a string '3456' into an INT column, it
converts the string into an integer, but if you store a string 'xyzzy'
in an INT column it will actually store the string value.

After the above decisions were made, SQLite became the most widely
used database engine on the planet and over a trillion SQLite database
files got created, and now we need to stick with that original idea
lest we cause compatibility issues for all that legacy.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

David Burgess-2
"This flexible type-name arrangement works because SQLite is very
forgiving about you putting non-proscribed values into columns - it
tries to convert if it can do so without loss of information but if it
cannot do a reversible type conversion it simply stores whatever you
give it.  Hence if you store a string '3456' into an INT column, it
converts the string into an integer, but if you store a string 'xyzzy'
in an INT column it will actually store the string value."

This is an important feature of SQLite.  In hindsight, an excellent decision.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Bob Friesenhahn
On Fri, 29 Jun 2018, David Burgess wrote:

> "This flexible type-name arrangement works because SQLite is very
> forgiving about you putting non-proscribed values into columns - it
> tries to convert if it can do so without loss of information but if it
> cannot do a reversible type conversion it simply stores whatever you
> give it.  Hence if you store a string '3456' into an INT column, it
> converts the string into an integer, but if you store a string 'xyzzy'
> in an INT column it will actually store the string value."
>
> This is an important feature of SQLite.  In hindsight, an excellent decision.

It is sloppy and absent additional constraints and checks added to the
schema, it puts a burden on the consumer of the data to assure that it
is the correct type.

It is not so clear that it was an excellent decision.

If the application requires an integer value, why do you think that it
is appropriate to pass it the string 'xyzzy'?

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
dmp
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

dmp
In reply to this post by Mark Wagner
> "This flexible type-name arrangement works because SQLite is very
> forgiving about you putting non-proscribed values into columns - it
> tries to convert if it can do so without loss of information but if it
> cannot do a reversible type conversion it simply stores whatever you
> give it.  Hence if you store a string '3456' into an INT column, it
> converts the string into an integer, but if you store a string 'xyzzy'
> in an INT column it will actually store the string value."
>
 > From: David Burgess
> This is an important feature of SQLite.  In hindsight, an excellent decision.

Unless your trying to create a generic user interface.

I have spent the last month trying to solve affinity with the columns.
The only way it seems to me to guarantee to solve the issue is to test
every retrieved column value and test its affinity.

The easier approach chosen just check the column affinity and assume
thats what is going to be stored there.

danap.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Bob Friesenhahn
On Fri, 29 Jun 2018, danap wrote:
>
> Unless your trying to create a generic user interface.
>
> I have spent the last month trying to solve affinity with the columns.
> The only way it seems to me to guarantee to solve the issue is to test
> every retrieved column value and test its affinity.

Affinity is only a hint and not an assurance of anything.

I solve the problem by bloating the schema with checks like this:

   foo integer default 1234
      check (typeof(foo) == 'integer'),

This enforces that someone can't put "Hello world" where an integer
belongs.

Without adding all the necessary safe-guards to ensure that only valid
data goes into the database, sqlite puts the using application at risk
(security and stability) with its wishy-washy ways.

Bob
--
Bob Friesenhahn
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Tim Streater-3
On 29 Jun 2018, at 17:46, Bob Friesenhahn <[hidden email]> wrote:

> On Fri, 29 Jun 2018, danap wrote:
>>
>> Unless your trying to create a generic user interface.
>>
>> I have spent the last month trying to solve affinity with the columns.
>> The only way it seems to me to guarantee to solve the issue is to test
>> every retrieved column value and test its affinity.
>
> Affinity is only a hint and not an assurance of anything.
>
> I solve the problem by bloating the schema with checks like this:
>
>    foo integer default 1234
>       check (typeof(foo) == 'integer'),
>
> This enforces that someone can't put "Hello world" where an integer
> belongs.
>
> Without adding all the necessary safe-guards to ensure that only valid
> data goes into the database, sqlite puts the using application at risk
> (security and stability) with its wishy-washy ways.

No one is forcing you, or anyone else, to use SQLite.


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: column types and constraints

Richard Hipp-3
In reply to this post by Bob Friesenhahn
On 6/29/18, Bob Friesenhahn <[hidden email]> wrote:
>
> Without adding all the necessary safe-guards to ensure that only valid
> data goes into the database, sqlite puts the using application at risk
> (security and stability) with its wishy-washy ways.
>

Can you provide an example of a security of stability problem caused
by flexible typing?
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
123