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
&
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
>
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
>
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
e 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:
> 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
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:
t: 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 b
> 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
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
> 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.
> 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
> 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
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
> 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
> 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
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
> 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
s 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 [m
> 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
>-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,
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
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
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,
> 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
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")
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
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
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
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
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
> 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
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.
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
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
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
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
"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
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
"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
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
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
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
> >>>
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).
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
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
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
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:
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:
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
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
t; 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 Jun
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.
53 matches
Mail list logo
