Re: [sqlite] column types and constraints
I see no unsigned integer support anywhere ... https://www.sqlite.org/datatype3.html And there are no API functions dealing with unsigned integers, only standard 2-s complement signed integers. https://www.sqlite.org/c3ref/funclist.html Declaring something an "unsigned integer" is the same as declaring it an "unsinged integer". The substring "int" declares the affinity and the rest is ignored as line noise. --- 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- >boun...@mailinglists.sqlite.org] On Behalf Of Warren Young >Sent: Saturday, 30 June, 2018 18:09 >To: SQLite mailing list >Subject: Re: [sqlite] column types and constraints > >On Jun 29, 2018, at 10:17 PM, Keith Medcalf >wrote: >> >> So what you really want is (a) to be able to error-out on SQL >operations that do not use proper affinity words (ie, only accept >INTEGER, DOUBLE, TEXT and BLOB), and; (b) to crash or return an error >instead of converting the data that you put or at least, that you >ask. > >That would work, too. > >There’s a schools of thought that says that “unsigned” should just go >away, that it causes more trouble than it’s worth. > >I believe the only reason SQLite supports it is to ease conversion of >schemas from other DBMSes. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On Jun 29, 2018, at 10:17 PM, Keith Medcalf wrote: > > So what you really want is (a) to be able to error-out on SQL operations that > do not use proper affinity words (ie, only accept INTEGER, DOUBLE, TEXT and > BLOB), and; (b) to crash or return an error instead of converting the data > that you put or at least, that you ask. That would work, too. There’s a schools of thought that says that “unsigned” should just go away, that it causes more trouble than it’s worth. I believe the only reason SQLite supports it is to ease conversion of schemas from other DBMSes. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
Hi, ALL,
I am not sure what are we talking about here and is discussed.
Just like on the paper the hard drive is storing the characters. It is
for us (humans, developers)
to interpret those characters as a TEXT, numeric value (be it INTEGER
or FLOAT/DOUBLE)
or some binary data.
As long as the client retrieves the exact set of characters that was
stored (and it is)
there is nothing to talk about. Because this is exactly what the
database is for - store
some data for the future use.
The strong typing in the DBMSes are there to simplify the life of the
people who creates
the library which is used by the actual developers to create client
application. Nothing more,
nothing less.
Just imagine if MS Access had the affinity paradigm instead of strong
data types. How hard would be
to write the VB for Access for MS devs.
So once again - it is exactly as on the paper - characters that should
be interpreted by people
(developers).
Thank you.
P.S.: I hope I'm right here and if not someone will surely correct me.
On Sat, Jun 30, 2018 at 3:57 PM, Keith Medcalf wrote:
>
> In your case, yes.
>
> If you do not wish SQLite3 to "convert" to the requested storage type on
> storage of a value, then do not specify a storage type (or specify a storage
> type of BLOB). Then whatever you request-to-store will be stored without
> conversion.
>
> SQLite version 3.25.0 2018-06-21 23:53:54
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table x(x);
> sqlite> insert into x values ('3');
> sqlite> insert into x values ('3.0');
> sqlite> insert into x values (3);
> sqlite> insert into x values (3.0);
> sqlite> insert into x values (x'123456789084759301939875459381798754');
> sqlite> insert into x values (null);
> sqlite> select x, typeof(x) from x;
> 3|text
> 3.0|text
> 3|integer
> 3.0|real
> 4Vx��u� ��uE��y�T|blob
> |null
> sqlite>
>
> ---
> 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-
>>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>>Sent: Saturday, 30 June, 2018 14:10
>>To: SQLite mailing list
>>Subject: Re: [sqlite] column types and constraints
>>
>>> when in fact it was the third-party interface wrapper.
>>
>>The examples I provided were all taken from the current sqlite3.exe
>>cli with 3.24.0 library. It is not a third-party issue.
>>
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 30 Jun 2018, at 8:58pm, Wout Mertens wrote: > Hmm, very odd. I'll need to check what happened again. Pay a lot of attention to the type you supplied when you created the table. The post you're responding to has this line: > sqlite> create table x(x primary key); This does not supply a type for the column. There could be all sorts of weird things going on. Do the same thing but with TEXT sqlite> create table x(x TEXT primary key); and you're more likely to be able to predict what happens. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
In your case, yes.
If you do not wish SQLite3 to "convert" to the requested storage type on
storage of a value, then do not specify a storage type (or specify a storage
type of BLOB). Then whatever you request-to-store will be stored without
conversion.
SQLite version 3.25.0 2018-06-21 23:53:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> insert into x values ('3');
sqlite> insert into x values ('3.0');
sqlite> insert into x values (3);
sqlite> insert into x values (3.0);
sqlite> insert into x values (x'123456789084759301939875459381798754');
sqlite> insert into x values (null);
sqlite> select x, typeof(x) from x;
3|text
3.0|text
3|integer
3.0|real
4Vx��u���uE��y�T|blob
|null
sqlite>
---
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-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Saturday, 30 June, 2018 14:10
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>> when in fact it was the third-party interface wrapper.
>
>The examples I provided were all taken from the current sqlite3.exe
>cli with 3.24.0 library. It is not a third-party issue.
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> when in fact it was the third-party interface wrapper. The examples I provided were all taken from the current sqlite3.exe cli with 3.24.0 library. It is not a third-party issue. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
Hmm, very odd. I'll need to check what happened again. I use node-sqlite3,
but it also doesn't seem to convert (
https://github.com/mapbox/node-sqlite3/blob/master/src/statement.cc#L274)
On Sat, Jun 30, 2018, 9:44 PM Keith Medcalf wrote:
>
> When you declare a column with no affinity (that is with blob or none
> affinity), the data is stored precisely and exactly as presented with no
> conversions performed by SQLite3. You give it a character string, it
> stores a character string. You give it an integer, it stores an integer.
> You give it a real it stores it as a real. You give it a bag-o-bytes, it
> is stored as a bag-o-bytes. If you give it something that can be converted
> (ie, a text representation of an integer) the data presented in stored (the
> text string). It is not converted.
>
> sqlite> create table x(x primary key);
> sqlite> insert into x values ('1234567890');
> sqlite> insert into x values ('12345678901234567890');
> sqlite> insert into x values ('1');
> sqlite> insert into x values ('1.0');
> sqlite> insert into x values (1);
> sqlite> insert into x values (2.0);
> sqlite> select x, typeof(x) from x;
> 1234567890|text
> 12345678901234567890|text
> 1|text
> 1.0|text
> 1|integer
> 2.0|real
>
> You were being "helped" by something other than SQLite3 because SQLite3
> does not behave in the manner you described.
>
> See
> https://www.sqlite.org/datatype3.html#determination_of_column_affinity
> in particular rule #3
>
> See also the sentence immediately preceding that section:
>
> "A column with affinity BLOB does not prefer one storage class over
> another and no attempt is made to coerce data from one storage class into
> another."
>
> Nor is there an SQLite3 API which will permit you to retrieve the data by
> "magical types". You ask for the datatype you want to receive and SQLite3
> will carry out the conversions necessary to meet your request, or; if you
> do not want any conversions, then you first ask what the data storage
> format of the item is, and then ask for the data to be returned in that
> format.
>
> Since you are not interacting directly with the SQLite3 C API, whomever
> wrote whatever it is that you are using included a bunch-o-magic which
> either is not documented, or that you did not read and therefore you
> assumed the issue you saw was SQLite3 when in fact it was the third-party
> interface wrapper.
>
> ---
> 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-
> >boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
> >Sent: Saturday, 30 June, 2018 11:44
> >To: SQLite mailing list
> >Subject: Re: [sqlite] column types and constraints
> >
> >story time:
> >
> >I was storing rows with text id's and never bothered setting a type,
> >it was
> >"id PRIMARY KEY" and I always assumed that it gave me back what I
> >stored in
> >it via the nodejs binding.
> >
> >One day I was storing a string of numbers which happened to fit in a
> >64 bit
> >int, and so sqlite stored them as integers, and when retrieving them,
> >they
> >became floats (since JS doesn't have 64 bit ints (yet)).
> >
> >That was a headscratcher. Now I explicitly set the type, always.
> >
> >
> >On Sat, Jun 30, 2018, 11:19 AM Olivier Mascia wrote:
> >
> >> > Le 30 juin 2018 à 09:04, Thomas Kurz a
> >écrit :
> >> >
> >> > CREATE TABLE a (col1 STRING);
> >> > INSERT INTO a (col1) VALUES ("3.0");
> >> > SELECT * from a;
> >> > ---> 3// this should never happen!!
> >>
> >> SQLite type affinity rules clearly do not recognise STRING as TEXT:
> >it
> >> does so only when the type contains the words CHAR, TEXT or CLOB.
> >STRING,
> >> which you use for your example, is even specifically warned about
> >(being of
> >> NUMERIC affinity).
> >>
> >>
> >https://www.sqlite.org/datatype3.html#determination_of_column_affinit
> >y
> >>
> >> --
> >> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> >> Olivier Mascia
> >>
> >>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> >users
> >>
> >___
> >sqlite-users mailing list
> >sqlite-users@mailinglists.sqlite.org
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
When you declare a column with no affinity (that is with blob or none
affinity), the data is stored precisely and exactly as presented with no
conversions performed by SQLite3. You give it a character string, it stores a
character string. You give it an integer, it stores an integer. You give it a
real it stores it as a real. You give it a bag-o-bytes, it is stored as a
bag-o-bytes. If you give it something that can be converted (ie, a text
representation of an integer) the data presented in stored (the text string).
It is not converted.
sqlite> create table x(x primary key);
sqlite> insert into x values ('1234567890');
sqlite> insert into x values ('12345678901234567890');
sqlite> insert into x values ('1');
sqlite> insert into x values ('1.0');
sqlite> insert into x values (1);
sqlite> insert into x values (2.0);
sqlite> select x, typeof(x) from x;
1234567890|text
12345678901234567890|text
1|text
1.0|text
1|integer
2.0|real
You were being "helped" by something other than SQLite3 because SQLite3 does
not behave in the manner you described.
See
https://www.sqlite.org/datatype3.html#determination_of_column_affinity
in particular rule #3
See also the sentence immediately preceding that section:
"A column with affinity BLOB does not prefer one storage class over another and
no attempt is made to coerce data from one storage class into another."
Nor is there an SQLite3 API which will permit you to retrieve the data by
"magical types". You ask for the datatype you want to receive and SQLite3 will
carry out the conversions necessary to meet your request, or; if you do not
want any conversions, then you first ask what the data storage format of the
item is, and then ask for the data to be returned in that format.
Since you are not interacting directly with the SQLite3 C API, whomever wrote
whatever it is that you are using included a bunch-o-magic which either is not
documented, or that you did not read and therefore you assumed the issue you
saw was SQLite3 when in fact it was the third-party interface wrapper.
---
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-
>boun...@mailinglists.sqlite.org] On Behalf Of Wout Mertens
>Sent: Saturday, 30 June, 2018 11:44
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>story time:
>
>I was storing rows with text id's and never bothered setting a type,
>it was
>"id PRIMARY KEY" and I always assumed that it gave me back what I
>stored in
>it via the nodejs binding.
>
>One day I was storing a string of numbers which happened to fit in a
>64 bit
>int, and so sqlite stored them as integers, and when retrieving them,
>they
>became floats (since JS doesn't have 64 bit ints (yet)).
>
>That was a headscratcher. Now I explicitly set the type, always.
>
>
>On Sat, Jun 30, 2018, 11:19 AM Olivier Mascia wrote:
>
>> > Le 30 juin 2018 à 09:04, Thomas Kurz a
>écrit :
>> >
>> > CREATE TABLE a (col1 STRING);
>> > INSERT INTO a (col1) VALUES ("3.0");
>> > SELECT * from a;
>> > ---> 3// this should never happen!!
>>
>> SQLite type affinity rules clearly do not recognise STRING as TEXT:
>it
>> does so only when the type contains the words CHAR, TEXT or CLOB.
>STRING,
>> which you use for your example, is even specifically warned about
>(being of
>> NUMERIC affinity).
>>
>>
>https://www.sqlite.org/datatype3.html#determination_of_column_affinit
>y
>>
>> --
>> Best Regards, Meilleures salutations, Met vriendelijke groeten,
>> Olivier Mascia
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> H2, HSQL Both are Java crap. SQLite is unfortunately the only embedded DBMS that gets along with a single file for both the library and the database files. (At least it's the only I know after scaning various systems.) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
story time:
I was storing rows with text id's and never bothered setting a type, it was
"id PRIMARY KEY" and I always assumed that it gave me back what I stored in
it via the nodejs binding.
One day I was storing a string of numbers which happened to fit in a 64 bit
int, and so sqlite stored them as integers, and when retrieving them, they
became floats (since JS doesn't have 64 bit ints (yet)).
That was a headscratcher. Now I explicitly set the type, always.
On Sat, Jun 30, 2018, 11:19 AM Olivier Mascia wrote:
> > Le 30 juin 2018 à 09:04, Thomas Kurz a écrit :
> >
> > CREATE TABLE a (col1 STRING);
> > INSERT INTO a (col1) VALUES ("3.0");
> > SELECT * from a;
> > ---> 3// this should never happen!!
>
> SQLite type affinity rules clearly do not recognise STRING as TEXT: it
> does so only when the type contains the words CHAR, TEXT or CLOB. STRING,
> which you use for your example, is even specifically warned about (being of
> NUMERIC affinity).
>
> https://www.sqlite.org/datatype3.html#determination_of_column_affinity
>
> --
> Best Regards, Meilleures salutations, Met vriendelijke groeten,
> Olivier Mascia
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> Bob wrote:
> 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.
I don't get to control others schema in providing a generic gui
for users.
> Simon wrote:
> The problem, as far as SQLite is concerned, is that the column
> should have been declared "TEXT' not STRING. Then it works correctly:
> SQLite version 3.22.0 2017-12-05 15:00:17 [...]
> sqlite> CREATE TABLE a (col1 STRING);
> sqlite> INSERT INTO a (col1) VALUES ("3.0");
> sqlite> SELECT * from a;
> 3
> sqlite> DROP TABLE a;
> sqlite> CREATE TABLE a (col1 TEXT);
> sqlite> INSERT INTO a (col1) VALUES ("3.0");
> sqlite> SELECT * from a;
> 3.0
> But course the dev team cannot correct the understanding of 'STRING'
> for backward compatibility reasons. And using affinities rather than
> types means that feeding a string into a numeric column does not
> generate an error. So the programmer never figured out that using
> 'STRING' as a type didn't do the right thing.
> It's a problem with multiple causes. And it cannot be fixed in SQLite3.
Nor should it! Ryan provided ample argument for a light DB engine that
SQLite seems to satisfy for millions with FLEXIBILITY! If you wish a
more constrained light RDBMS then H2, HSQL, and others are available.
I'm just happy at least I get a column affinity for type checking to
give some sanity. Thank you.
danap.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> And yes, you might think (like me) that this is silly and they should
> just fix it, warning people about the new way for a good few versions,
> and then just roll it out. But, that would mean there will exist schemas
Hmmm... but what kind of compatibility issues would you see if SQLite stored
values exactly as provided and did the conversion upon reading? I.e. "INSERT
... ('3.0')" would actually store a string. When reading this string with a
numeric parameter, the conversion can still be done. This way, I would of
course still have the same problem, but I had a chance to correct my mistake in
existing databases reading with explicit CASTs.
But there are other issues where SQLite corrected mistakes that were done
before. Think of the "pragma foreign_key_check" for example. One could fix data
type issues in a similar way without affecting compatibility. And those who
rely on a special interpretation of non-standard SQL (like putting strings and
expecting them to be treated as numbers) will always have the option to stay on
"their" SQLite version.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> Le 30 juin 2018 à 13:30, R Smith a écrit : > > PS: Anyone here that takes web servers and the like through upgrades to > different Apache, or PHP versions, or different MySQL/MariaDB versions will > know the effort of re-engineering your work from years ago to fit the new > upgrade. SQLite is the only "fire-and-forget" weapon out there, I feel like > that feature is worth a few TYPE pains - but that's just my feeling and it > might well be antiquated. +1: fully shared feeling. :) -- Best regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia (from mobile device) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 2018/06/30 9:04 AM, Thomas Kurz wrote: COLUMN xy VARCHAR(50) COLUMN xy MEDIUMTEXT COLUMN xy BIT COLUMN xy DECIMAL(5,2) Should those raise errors? Because to SQLite those are the exact same gibberish as: I would appreciate if SQLite raised an error each time the declaration mismatches the interpretation. In your example, all declarations are well-known SQL. And, if I understand correctly, SQLite treats each of those as NUMERIC. So: a) Yes, error, as I indent to store strings, but SQLite uses numbers. b) Yes, error, same case. c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC d) No, as DECIMAL ist compatible to NUMERIC. I'm sorry, upon re-reading I see the real point I tried to make got lost in that I concentrated too much on the physical examples. The real point is that, in c for example, SQLite is not understanding BIT as a type and it is not responsible for understanding all types used in all database engines. Imagine that you couldn't use BIT in MySQL, it's an MSSQL construct - why do you require SQLite to interpret it? Moreover, what if a brand new type arise today? Let's call it CAT_MOUSE_DOG as an example, and let's say Oracle, MSSQL and Postgres ALL adopt this new type. Do you still expect SQLite to error out when you declare a column with that type? SQLite doesn't "know" which types/conventions will arise in future, hence, since its birth (before many of the modern types) it has allowed UNKNOWN types in the declaration, and then made an effort to guess the best affinity upon it, which works well mostly, but not always. More pertinently, once it does actually lock down a "meaning" upon a type or affinity, it can never go back. It's worth being under-zealous. As to your statement about the problem with STRING - Yes, let's agree that SQLite dropped the ball on that one many many moons ago (so much so they have had enough backlash [just like your case] that they have amended the documentation to warn about that very specific case. By now there are billions of schemas in the world, many of which might have STRING declarations and have adopted a checking mechanism in their own code to deal with it (much like you might do now), so changing it may harm those implementations. And yes, you might think (like me) that this is silly and they should just fix it, warning people about the new way for a good few versions, and then just roll it out. But, that would mean there will exist schemas in the World that will be interpreted differently by different versions, and the selfish comfort to me comes when I think: "If I make a DB today that works... it will work for all those years/upgrades to come, and I can trust that it will because if the SQLite Devs won't fix STRING, then they are unlikely to change anything that will harm MY systems." Cheers! Ryan PS: Anyone here that takes web servers and the like through upgrades to different Apache, or PHP versions, or different MySQL/MariaDB versions will know the effort of re-engineering your work from years ago to fit the new upgrade. SQLite is the only "fire-and-forget" weapon out there, I feel like that feature is worth a few TYPE pains - but that's just my feeling and it might well be antiquated. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> Le 30 juin 2018 à 09:04, Thomas Kurz a écrit :
>
> CREATE TABLE a (col1 STRING);
> INSERT INTO a (col1) VALUES ("3.0");
> SELECT * from a;
> ---> 3// this should never happen!!
SQLite type affinity rules clearly do not recognise STRING as TEXT: it does so
only when the type contains the words CHAR, TEXT or CLOB. STRING, which you
use for your example, is even specifically warned about (being of NUMERIC
affinity).
https://www.sqlite.org/datatype3.html#determination_of_column_affinity
--
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> Le 30 juin 2018 à 09:04, Thomas Kurz a écrit : > >> a) COLUMN xy VARCHAR(50) >> b) COLUMN xy MEDIUMTEXT >> c) COLUMN xy BIT >> d) COLUMN xy DECIMAL(5,2) >> Should those raise errors? Because to SQLite those are the exact same >> gibberish as: > > I would appreciate if SQLite raised an error each time the declaration > mismatches the interpretation. In your example, all declarations are > well-known SQL. And, if I understand correctly, SQLite treats each of those > as NUMERIC. I think you don't. The rules are simple: https://www.sqlite.org/datatype3.html#determination_of_column_affinity > So: > a) Yes, error, as I indent to store strings, but SQLite uses numbers. Wrong. VARCHAR has CHAR in the name, SQLite uses TEXT affinity, which match perfectly. > b) Yes, error, same case. Wrong. MEDIUMTEXT has TEXT in the name, SQLite uses TEXT affinity, which match perfectly. > c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC If BIT was to be refused, then would DATE and BOOLEAN for instance. The three of them have the convenience to lean to NUMERIC storage thanks to affinity rules and it is perfectly fine when you have understood and agreed before choosing to use SQLite, that SQLite only uses 4 basic types (let's name them INT, REAL, TEXT, BLOB) and that any type wording used in declaration is meaningless except that it leads to a type affinity for the column. > d) No, as DECIMAL ist compatible to NUMERIC. Fine. -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 30 Jun 2018, at 8:35am, Keith Medcalf wrote:
> You "put" a ieee754 floating point double.
Keith, Are you referring to this ?
On 30 Jun 2018, at 8:04am, Thomas Kurz wrote:
> CREATE TABLE a (col1 STRING);
> INSERT INTO a (col1) VALUES ("3.0");
> SELECT * from a;
> ---> 3// this should never happen!!
If so, then I disagree with you. The column was defined as STRING. A string
was put into it. There is nothing in the above to suggest that the programmer
might one day want the value they supplied mutilated.
The problem, as far as SQLite is concerned, is that the column should have been
declared "TEXT' not STRING. Then it works correctly:
SQLite version 3.22.0 2017-12-05 15:00:17 [...]
sqlite> CREATE TABLE a (col1 STRING);
sqlite> INSERT INTO a (col1) VALUES ("3.0");
sqlite> SELECT * from a;
3
sqlite> DROP TABLE a;
sqlite> CREATE TABLE a (col1 TEXT);
sqlite> INSERT INTO a (col1) VALUES ("3.0");
sqlite> SELECT * from a;
3.0
But course the dev team cannot correct the understanding of 'STRING' for
backward compatibility reasons. And using affinities rather than types means
that feeding a string into a numeric column does not generate an error. So the
programmer never figured out that using 'STRING' as a type didn't do the right
thing.
It's a problem with multiple causes. And it cannot be fixed in SQLite3.
Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> You "put" a ieee754 floating point double. No I don't, I put a string ;-) I am not complaining that I can put anything anywhere. But the data type that I provide should be kept. When providing a string, it should be stored as such to have the possibility to get back the original value. When requesting an integer or float, it is perfectly ok to convert it. But it should be stored what is put. > BTW, SQL delimits strings with single-quotes. Double-quotes mean that what > is between them is an identifier. Sorry, I thought it was the other way round. But it doesn't actually matter in this case. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
You "put" a ieee754 floating point double. If you retrieved an ieee754
floating point double, you would get back that which you put! The fact that
internally SQLite3 stored it as a 3 (integer, token, string, whatever) is
irrelevant. You "gets" what you "puts", as long as what you "putted" is the
same as what you "asked" for. Are you complaining that you can "puts" anything
anywhere, or that once "putted" you can "ask" for whatever format you want? Or
are you complaining about how the "column type gibberish" is interpreted if you
do not stick to correct affinities? Or that data conversions occur on the
"putted" value when it is stored or that it may occur if you "asks" for a
different format than what is stored?
The rules for the parsing of the gibberish are here by the way:
https://www.sqlite.org/datatype3.html
The easy solution to those issues are "don't do that" ... and if you don't
trust that someone else didn't "don't do that" to your applications database,
then you better check what you are doing when you "asks" for data...
(And the command line shell is a bad way to do things because it does can only
"puts" text (which must always be converted), and can only display text after
conversion from whatever format the data was "putsed" and "stored" with).
BTW, SQL delimits strings with single-quotes. Double-quotes mean that what is
between them is an identifier. For historical reasons if something which is
double-quoted cannot be resolved to an identifier in the context it was used,
the assumption is that the coder (cuz programmers would never make such an
error) is a dodo and *meant* to use single-quotes but was typing in all-caps at
the time and put in the wrong character ...
---
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-
>boun...@mailinglists.sqlite.org] On Behalf Of Thomas Kurz
>Sent: Saturday, 30 June, 2018 01:04
>To: SQLite mailing list
>Subject: Re: [sqlite] column types and constraints
>
>> I don't disagree, but this means we lose sight of the important
>point
>>that, if you distill the problem to just "INTEGER", then sure, it
>looks
>> silly, and sure, you can fix it with internal auto-CHECKing, but in
>> SQLite the type affinity INTEGER stands father to LONGINT,
>MEDIUMINT,
>
>Ok, I understand your argumentation now. But I don't think
>distinguishing different integer types or strings with different
>length declarations would be mandatory. IIRC, they were introduced to
>save memory in times when database files were actually just a
>sequence of fixed-length records/structs. SQLite on the other hand
>always uses the least memory-consuming way for storing data anyway.
>
>But I must admit that a disctinction between alternatives of the same
>"base type" was something I didn't have in mind, so consider me
>convinced ;-)
>
>> COLUMN xy VARCHAR(50)
>> COLUMN xy MEDIUMTEXT
>> COLUMN xy BIT
>> COLUMN xy DECIMAL(5,2)
>> Should those raise errors? Because to SQLite those are the exact
>same
>> gibberish as:
>
>I would appreciate if SQLite raised an error each time the
>declaration mismatches the interpretation. In your example, all
>declarations are well-known SQL. And, if I understand correctly,
>SQLite treats each of those as NUMERIC. So:
>
>a) Yes, error, as I indent to store strings, but SQLite uses numbers.
>b) Yes, error, same case.
>c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC
>d) No, as DECIMAL ist compatible to NUMERIC.
>
>Let me explain my problem, something which happens to me quite often
>as many programming languages name the thing "string" that DBMSs want
>to have named "text" (why the hell??):
>
>CREATE TABLE a (col1 STRING);
>INSERT INTO a (col1) VALUES ("3.0");
>SELECT * from a;
>---> 3// this should never happen!!
>
>Instead of raising an error on the column definition (which would be
>most elegant imho), it would be perfectly ok if SQLite treated the
>string ("text") that I provide in the insert-statement (double
>quotes) actually as a string and did not try to convert it. Or, more
>clearly spoken, if it recognized that the conversion is not
>reversible to the original string.
>
>Currently, there is no warning whatsoever for me to lose data after
>having used an invalid column declaration. Meanwhile I'm more aware
>of that, but the first time I had spent hours trying to figure out
>what's going wrong until I found the problem.
>
Re: [sqlite] column types and constraints
> I don't disagree, but this means we lose sight of the important point
>that, if you distill the problem to just "INTEGER", then sure, it looks
> silly, and sure, you can fix it with internal auto-CHECKing, but in
> SQLite the type affinity INTEGER stands father to LONGINT, MEDIUMINT,
Ok, I understand your argumentation now. But I don't think distinguishing
different integer types or strings with different length declarations would be
mandatory. IIRC, they were introduced to save memory in times when database
files were actually just a sequence of fixed-length records/structs. SQLite on
the other hand always uses the least memory-consuming way for storing data
anyway.
But I must admit that a disctinction between alternatives of the same "base
type" was something I didn't have in mind, so consider me convinced ;-)
> COLUMN xy VARCHAR(50)
> COLUMN xy MEDIUMTEXT
> COLUMN xy BIT
> COLUMN xy DECIMAL(5,2)
> Should those raise errors? Because to SQLite those are the exact same
> gibberish as:
I would appreciate if SQLite raised an error each time the declaration
mismatches the interpretation. In your example, all declarations are well-known
SQL. And, if I understand correctly, SQLite treats each of those as NUMERIC. So:
a) Yes, error, as I indent to store strings, but SQLite uses numbers.
b) Yes, error, same case.
c) Preferably yes, but not mandatory, as BIT is compatible to NUMERIC
d) No, as DECIMAL ist compatible to NUMERIC.
Let me explain my problem, something which happens to me quite often as many
programming languages name the thing "string" that DBMSs want to have named
"text" (why the hell??):
CREATE TABLE a (col1 STRING);
INSERT INTO a (col1) VALUES ("3.0");
SELECT * from a;
---> 3// this should never happen!!
Instead of raising an error on the column definition (which would be most
elegant imho), it would be perfectly ok if SQLite treated the string ("text")
that I provide in the insert-statement (double quotes) actually as a string and
did not try to convert it. Or, more clearly spoken, if it recognized that the
conversion is not reversible to the original string.
Currently, there is no warning whatsoever for me to lose data after having used
an invalid column declaration. Meanwhile I'm more aware of that, but the first
time I had spent hours trying to figure out what's going wrong until I found
the problem.
INSERT INTO a (col1) VALUES (3.0);
SELECT * from a;
---> 3// I would accept that as I have provided a number
Maybe, this could be a compromise?
In your previous post, you wrote:
> [...]
> VARCHAR(3)
> Truncate the string to "MAM" in MySQL, without an error at all, just kill
> some data and move on!
My example is exactly the same. SQLite continues without an error losing me
data. There is no way getting it back. Even this don't work:
SELECT CAST(col1 AS TEXT) FROM a;
---> 3// <> "3.0" !!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
But there is no API which will permit you to retrieve "unsigned" integers from SQLite3. Nor is there an API which will let you "send" an unsigned integer to SQLite3 for storage in the database. All the sqlite3_column and sqlite3_bind interfaces deal only with signed integers. If you are using using compiler magic (ie, a cast) to fiddle-diddle with the declared interpretation of the SQLite3 APIs, then it is probably best that you know what you are doing. (Similarly if you use "some bits" of a pointer for other than their intended purpose (that is, pointing to an address in linear virtual memory) then you should expect the explosions and faults which will ensue). It is entirely possible to use a bag-o-bytes to declare all data values in your application, and then use compiler magic (ie, casts) to cause the compiler into generating whatever machine code your little heart might desire, however, you better really really know what you are doing if you go that route. The same applies if you expect SQLite3 to behave in a manner different than having only the datatypes integer, double, text, and bag-o-bytes, and using prayerful declarations that do not reflect the inherent data interfaces falls into the same bucket. In other words, what is stored is what you put, and what you get back is what you asked for. If you want to know what was put, you need to ask. If you don't ask and make an assumption, then you best be prepared to deal with the result of what was put converted to what you asked for. So what you really want is (a) to be able to error-out on SQL operations that do not use proper affinity words (ie, only accept INTEGER, DOUBLE, TEXT and BLOB), and; (b) to crash or return an error instead of converting the data that you put or at least, that you ask. That way at least if you use a stupid table declaration like CREATE TABLE theTable (theColumn unsigned integer(1024)); you will get a syntax error since neither the word "unsigned" nor a value in (brackets) are proper affinity declarations. Similarly if a datavalue is "text" and you ask for an "integer" you get an error since you are not "asking" for data which matches what was stored. --- 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- >boun...@mailinglists.sqlite.org] On Behalf Of Warren Young >Sent: Friday, 29 June, 2018 19:35 >To: SQLite mailing list >Subject: Re: [sqlite] column types and constraints > >On Jun 29, 2018, at 4:36 PM, Keith Medcalf >wrote: >> >> All of the issues raised are "application" problems, not database >problems. > >Computers are here to aid humans, not the other way around. > >> Clearly if you retrieved a value from the database and want to use >it as an index you have to do bounds checking. > >Why? I told the DBMS that the values in that column will be unsigned >integers, yet it accepted a non-integer for storage and then yielded >a negative value on retrieval. > >This code will yield a complaint from a sufficiently on-the-ball C >compiler: > >unsigned foo = external_function(); >if (foo >= 0) do_happy_path(); > >It will rightly complain that the condition is always true. > >> The ability to forsee that the world may not be entirely as you >expect is the root of the difference between a mere coder and a >professional software programmer. > >Therefore, all of the bugs written in C that we can attribute to >language design issues were perpetrated by mere coders. No true >Scotsman^Wprogrammer would ever make such a mistake. Compiler >diagnostics are for the weak. TODAY IS A GOOD DAY TO WRITE SOFTWARE. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On Jun 29, 2018, at 4:36 PM, Keith Medcalf wrote: > > All of the issues raised are "application" problems, not database problems. Computers are here to aid humans, not the other way around. > Clearly if you retrieved a value from the database and want to use it as an > index you have to do bounds checking. Why? I told the DBMS that the values in that column will be unsigned integers, yet it accepted a non-integer for storage and then yielded a negative value on retrieval. This code will yield a complaint from a sufficiently on-the-ball C compiler: unsigned foo = external_function(); if (foo >= 0) do_happy_path(); It will rightly complain that the condition is always true. > The ability to forsee that the world may not be entirely as you expect is the > root of the difference between a mere coder and a professional software > programmer. Therefore, all of the bugs written in C that we can attribute to language design issues were perpetrated by mere coders. No true Scotsman^Wprogrammer would ever make such a mistake. Compiler diagnostics are for the weak. TODAY IS A GOOD DAY TO WRITE SOFTWARE. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
pragma application_written_by=coder; -vs the default, which is- pragma application_written_by=programmer; ;-) All of the issues raised are "application" problems, not database problems. Clearly if you retrieved a value from the database and want to use it as an index you have to do bounds checking. The ability to forsee that the world may not be entirely as you expect is the root of the difference between a mere coder and a professional software programmer. (Not to mention, it was the same error that allowed the errant value to get into the database in the first place). --- 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- >boun...@mailinglists.sqlite.org] On Behalf Of Warren Young >Sent: Friday, 29 June, 2018 13:00 >To: SQLite mailing list >Subject: Re: [sqlite] column types and constraints > >On Jun 29, 2018, at 11:46 AM, Richard Hipp wrote: >> >> On 6/29/18, Bob Friesenhahn 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? > >The following is an answer to your challenge, not a feature request. >I’d enable strong typing support in SQLite if it were available, but >I started using SQLite knowing its stance on typing, so I can’t >honorably demand it now. > >Nevertheless: > >1. In defining a table schema, declare a column as UNSIGNED INTEGER; >SQLite ignores the “UNSIGNED” and uses INTEGER affinity. > >2. Store “-1FRED” in that column, for which SQLite uses TEXT >affinity, so as to not lose any data. > >3. Retrieve the value with sqlite3_column_int(), which forces a CAST >to INT, resulting in -1, that being the longest prefix that is INT- >like. > >4. The application uses the value as an index into an array. If the >application passes int* to sqlite3_column_int() to avoid compiler >complaints, they’ll get a negative index. If they pass unsigned* >instead, casting it to int* to placate the compiler, they get >UINT_MAX on a 2’s complement machine, which will certainly crash the >program when used as an array index. Either way, a security exploit >is probably available. > >If your reaction is that the application shouldn’t have allowed input >of “-1FRED” for an integer value, that’s true, but it would be nice >if SQLite would backstop the application’s restrictions. The >application tried to tell SQLite it wanted help enforcing its limits >when giving the UNSIGNED attribute in declaring the table schema. > >If you say that the application shouldn’t have trusted the value it >got from SQLite, why not? From the application programmer’s >perspective, it’s now validated data. > >It is of course possible to work around all of this. The application >programmer “just” has to write checks in layers closer to the end >user, checks which are not strictly necessary with other DBMSes. >People coming from those other DBMSes reasonably expect the data to >be implicitly trustworthy once it is finally at rest. >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 2018/06/30 12:05 AM, Thomas Kurz wrote: Indeed, but that option does exist, it's called CHECK constraints You're clearly right, but from my point of view, it's redundant to say COLUMN xy INTEGER CHECK type=integer, because "COLUMN INTEGER" already implies that the column is integer. And, btw, as CHECK already exists, it shouldn't be too complicated to have a mode where this CHECKing is automatically done//... I don't disagree, but this means we lose sight of the important point that, if you distill the problem to just "INTEGER", then sure, it looks silly, and sure, you can fix it with internal auto-CHECKing, but in SQLite the type affinity INTEGER stands father to LONGINT, MEDIUMINT, SMALLINT, INT, BIT, BOOL and possibly a slew of other types that escapes me now. Some of which will cease to function the same once you bind INTEGER to a stiff type format. Now you need to start introducing these as their own types, which means SQLite INTEGER affinity is no longer feasible. The same holds true for other affinities. It's either a type-affinity system, or a Type-system, it can't/shouldn't be both. My problem is rather that SQLite treats a column declared with an invalid type as NUMERIC. This can lead to problems that aren't quite obvious. COLUMN xy DOG_CAT_MOUSE should definitely raise an error. Okay... what about: COLUMN xy VARCHAR(50) or COLUMN xy MEDIUMTEXT or COLUMN xy BIT or COLUMN xy DECIMAL(5,2) Should those raise errors? Because to SQLite those are the exact same gibberish as: COLUMN xy DOG_CAT_MOUSE(99) It's either accepting unknown types, or not. There is no middle road. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> Indeed, but that option does exist, it's called CHECK constraints You're clearly right, but from my point of view, it's redundant to say COLUMN xy INTEGER CHECK type=integer, because "COLUMN INTEGER" already implies that the column is integer. And, btw, as CHECK already exists, it shouldn't be too complicated to have a mode where this CHECKing is automatically done without having me to declare (and forget) it in every column. (Just the way foreign key checks can be turned on or off. It can default to off without any problem.) My problem is rather that SQLite treats a column declared with an invalid type as NUMERIC. This can lead to problems that aren't quite obvious. COLUMN xy DOG_CAT_MOUSE should definitely raise an error. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 2018/06/29 10:23 PM, Thomas Kurz wrote: b) I think that noone wants type affinity to be actually removed. I'd just like an option to make SQLite behave like any other DBMS and - respect the data type declared for a column - reject column definitions with invalid types (e.g. "STRING") Indeed, but that option does exist, it's called CHECK constraints (and if you would like to get elaborate, even Triggers), but I also note Bob's point that these do consume more resources (probably significantly more in the case of Triggers) than had it been a Type-constraint. A major advantage is that you can be the author of when to check and when not to, you can be the author of what /exactly/ to check - DB Engines vary in how they check[*], but with SQLite you are the master of your destiny. [*] - A good example is a VARCHAR(3) constraint. Adding the string "MAMA" into that field will, depending on the DB: - FAIL your query with an error in MSSQL, one that may never have popped up during dev cycle but suddenly do in production. - Truncate the string to "MAM" in MySQL, without an error at all, just kill some data and move on! - Put the string "MAMA" into the DB without an error in SQLite. Which of these do you prefer? Whichever it is, you can mimic it in SQLite with a trigger or check constraint. What you cannot do though, is mimic SQLite's behaviour in any other Engine. Think about that. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 29 Jun 2018, at 9:23pm, Thomas Kurz wrote: > a) Why would I put strings in an integer column? If I need to do so, I have a > concept error in my database schema. > > b) I think that noone wants type affinity to be actually removed. I'd just > like an option to make SQLite behave like any other DBMS and > - respect the data type declared for a column > - reject column definitions with invalid types (e.g. "STRING") Here's the question: do you want consistency or do you want safety ? If you try to put TEXT into a REAL column you should get an error message, right ? If you try to put REAL into an INTEGER column, you should get an error message, right ? Or not ? Do you want automatic truncation rather than an error message ? Do you know what PostgreSQL, Oracle, Informix, DB2, MS-SQL, OCELOT, MySQL, Firebird, SQL Anywhere, and Borland Interbase do ? Do you want compatibility with all of them ? Those are rhetorical questions. I don't really want you to answer them. But I'm trying to point out that the SQL standard doesn't say what should happen and many other SQL engines have made their own choices. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On Fri, 29 Jun 2018, Richard Hipp wrote: On 6/29/18, Bob Friesenhahn 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? It is only necessary for the database to return something that the application is not designed for in order to cause problems for the application. The ability to inject wrong data depends on the interfaces which are exposed for introducing the wrong data (possibly including the sqlite3 shell), and any added safeguards in the database itself. Assuring that the expected type is returned is just part of the problem since often only particular values or ranges are allowable. This is why our database includes many checks (including using triggers) to defend against errant data (including the underlying type) on the way in. Unfortunately, such checks make the schema larger, consuming more RAM in all applications using the database. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On Fri, 29 Jun 2018, R Smith wrote: Type-constraining here would merely protect the programmer against him/herself. I know this is not entirely without merit, but a feature I would happily forego when weighed against even just one of the points made in the previous paragraph. The 'lite' in 'sqlite3' does not prohibit the same database from being developed by many programers using a variety of programming languages and scripts to solve very large problems. The stability of the sqlite file format and column type integrity are two different things. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
SQLite returns what has been saved in the database, the application should control what it writes to the database. If the database can be edited by an untrusted entity, the application should treat the content of the database as untrusted. Even if SQLite enforced data types, someone who can edit the database could also change the schema accordingly to allow to store malicious data. 2018-06-29 21:00 GMT+02:00, Warren Young : > On Jun 29, 2018, at 11:46 AM, Richard Hipp wrote: >> >> On 6/29/18, Bob Friesenhahn 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? > > The following is an answer to your challenge, not a feature request. I’d > enable strong typing support in SQLite if it were available, but I started > using SQLite knowing its stance on typing, so I can’t honorably demand it > now. > > Nevertheless: > > 1. In defining a table schema, declare a column as UNSIGNED INTEGER; SQLite > ignores the “UNSIGNED” and uses INTEGER affinity. > > 2. Store “-1FRED” in that column, for which SQLite uses TEXT affinity, so as > to not lose any data. > > 3. Retrieve the value with sqlite3_column_int(), which forces a CAST to INT, > resulting in -1, that being the longest prefix that is INT-like. > > 4. The application uses the value as an index into an array. If the > application passes int* to sqlite3_column_int() to avoid compiler > complaints, they’ll get a negative index. If they pass unsigned* instead, > casting it to int* to placate the compiler, they get UINT_MAX on a 2’s > complement machine, which will certainly crash the program when used as an > array index. Either way, a security exploit is probably available. > > If your reaction is that the application shouldn’t have allowed input of > “-1FRED” for an integer value, that’s true, but it would be nice if SQLite > would backstop the application’s restrictions. The application tried to > tell SQLite it wanted help enforcing its limits when giving the UNSIGNED > attribute in declaring the table schema. > > If you say that the application shouldn’t have trusted the value it got from > SQLite, why not? From the application programmer’s perspective, it’s now > validated data. > > It is of course possible to work around all of this. The application > programmer “just” has to write checks in layers closer to the end user, > checks which are not strictly necessary with other DBMSes. People coming > from those other DBMSes reasonably expect the data to be implicitly > trustworthy once it is finally at rest. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 2018/06/29 9:00 PM, Warren Young wrote: The following is an answer to your challenge, not a feature request// 4. The application uses the value as an index into an array. If the application passes int* to sqlite3_column_int() to avoid compiler complaints, they’ll get a negative index. If they pass unsigned* instead, casting it to int* to placate the compiler, they get UINT_MAX on a 2’s complement machine, which will certainly crash the program when used as an array index. Either way, a security exploit is probably available. I agree this demonstrates very well that SQLite can be one of the tools that touches data along a gauntlet of horribly bad programming practices that ends in a possible security loophole. However, it in no way demonstrates duck-typing to have a causal relationship to the vulnerability. You may as well have argued that the programmer read the value -7 from a file he expected would have a positive integer and then massaged it into a bad array-pointer through the same programming steps - this doesn't render fopen() a security risk. Further to this, how is this different from reading a very valid integer from, say MySQL, with a value of 722 when your array is only 120 long? If you are not going to range-check it, there's an immediate security risk right there[*]. Is this MySQL's fault? It is of course possible to work around all of this. The application programmer “just” has to write checks in layers closer to the end user, checks which are not strictly necessary with other DBMSes. People coming from those other DBMSes reasonably expect the data to be implicitly trustworthy once it is finally at rest. I think this statement is a great summation of the real problem, and I have to agree, perhaps in an "Introduction to SQLite" section on the website one can add/improve a "Types in SQLite" section that better prepares such programmers coming from alternate engines. We often see people posting here being confused by it in some way - so definitely an important thing. Cheers! Ryan [*] - Yes, the original demonstration hinged on the "unsigned" type constraint being violated, but the end-result vulnerability proposed was a range-check failure, not a sign failure. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
> At least, enough-so in my mind to defend David's assessment of "an > excellent decision". Sorry, I cannot follow the point. a) Why would I put strings in an integer column? If I need to do so, I have a concept error in my database schema. b) I think that noone wants type affinity to be actually removed. I'd just like an option to make SQLite behave like any other DBMS and - respect the data type declared for a column - reject column definitions with invalid types (e.g. "STRING") ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On Jun 29, 2018, at 11:46 AM, Richard Hipp wrote: > > On 6/29/18, Bob Friesenhahn 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? The following is an answer to your challenge, not a feature request. I’d enable strong typing support in SQLite if it were available, but I started using SQLite knowing its stance on typing, so I can’t honorably demand it now. Nevertheless: 1. In defining a table schema, declare a column as UNSIGNED INTEGER; SQLite ignores the “UNSIGNED” and uses INTEGER affinity. 2. Store “-1FRED” in that column, for which SQLite uses TEXT affinity, so as to not lose any data. 3. Retrieve the value with sqlite3_column_int(), which forces a CAST to INT, resulting in -1, that being the longest prefix that is INT-like. 4. The application uses the value as an index into an array. If the application passes int* to sqlite3_column_int() to avoid compiler complaints, they’ll get a negative index. If they pass unsigned* instead, casting it to int* to placate the compiler, they get UINT_MAX on a 2’s complement machine, which will certainly crash the program when used as an array index. Either way, a security exploit is probably available. If your reaction is that the application shouldn’t have allowed input of “-1FRED” for an integer value, that’s true, but it would be nice if SQLite would backstop the application’s restrictions. The application tried to tell SQLite it wanted help enforcing its limits when giving the UNSIGNED attribute in declaring the table schema. If you say that the application shouldn’t have trusted the value it got from SQLite, why not? From the application programmer’s perspective, it’s now validated data. It is of course possible to work around all of this. The application programmer “just” has to write checks in layers closer to the end user, checks which are not strictly necessary with other DBMSes. People coming from those other DBMSes reasonably expect the data to be implicitly trustworthy once it is finally at rest. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 2018/06/29 6:25 PM, Bob Friesenhahn wrote: 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//... 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'? Unlike other SQL Engines that operates in a User-interaction-space, SQLite operates in an application-interaction space. It is part and parcel of the application (or at least via a library that services direct API calls), and so where user-interactive databases have Type constraints as a way to force those from outside your app, it comes with penalties. To name a few: - it has to define very many kinds of variable types to cope with the different constraint needs, and - add more over time, which apart from being hefty, doesn't support the future-proof file format that SQLite does (that's why you need to run upgrade programs when installing a new MySQL DB, for example), - Once committed to a type, changing the nature of data that can be added requires a Schema change (not too hard if your DB is one server computer, but what if it runs in a billion separate devices?). - Extending the last point - this is even true if you want to do as simple an exercise as allowing 40-char strings where you previously had 30-char strings, because the client thought they knew their own requirement. While this all does serve to protect you from external users adding non-conformant data, in SQLite's case the DB is application-interactive, and apart from solving all of the above, any data that gets pushed to your DB file typically is either generated by the application, inputted via the application, or at a minimum vetted/controlled by the application. Type-constraining here would merely protect the programmer against him/herself. I know this is not entirely without merit, but a feature I would happily forego when weighed against even just one of the points made in the previous paragraph. At least, enough-so in my mind to defend David's assessment of "an excellent decision". What it definitely doesn't do, is create a security vulnerability or system-level risk. (i.e. Any potential risk it does create is one that can easily be mitigated by code in your own application) Maybe not the perfect solution, but I'd say a good alternative to the other good-but-non-perfect Types employed in other Engines. I hope that answers your question somewhat, Cheers! Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 6/29/18, Bob Friesenhahn 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 29 Jun 2018, at 17:46, Bob Friesenhahn 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
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 bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
"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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
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 bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
"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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
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 To: SQLite mailing list Sent: Thursday, June 28, 2018, 02:02:26 Subject: [sqlite] column types and constraints On 6/27/18, 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. 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 28 Jun 2018, at 5:43am, Igor Tandetnik 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
Great explanation. Thanks. On Wed, Jun 27, 2018 at 7:43 PM Richard Hipp wrote: > On 6/27/18, Igor Tandetnik wrote: > > On 6/27/2018 9:14 PM, Richard Hipp wrote: > >> On 6/27/18, Mark Wagner 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 > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 6/27/2018 10:43 PM, Richard Hipp wrote: On 6/27/18, Igor Tandetnik wrote: On 6/27/2018 9:14 PM, Richard Hipp wrote: On 6/27/18, Mark Wagner 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 6/27/18, Igor Tandetnik wrote: > On 6/27/2018 9:14 PM, Richard Hipp wrote: >> On 6/27/18, Mark Wagner 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 6/27/2018 9:14 PM, Richard Hipp wrote: On 6/27/18, Mark Wagner 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
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 wrote: > On 6/27/18, Mark Wagner 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 > d...@sqlite.org > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 6/27/18, Mark Wagner 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
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 wrote: > On 6/27/18, 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. 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 > d...@sqlite.org > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 6/27/18, 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. 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 d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
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- >boun...@mailinglists.sqlite.org] 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 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 nametypenotnull 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 >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] column types and constraints
On 27 Jun 2018, at 11:56pm, 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. 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 nametypenotnull 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
