Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[James K. Lowden]

On Thu, 9 Oct 2014 11:16:25 -0400
Stephen Chrzanowski  wrote:
> On Wed, Oct 8, 2014 at 8:38 PM, James K. Lowden
>  wrote:
> 
> >
> > The problem I see with your suggestion is that I can't think of
> > another situation, with or without NULL, with or without defaults,
> > where
> >
> > insert into T (t) value (X)
> >
> > results in T.t = Y.  You should get what you asked for, or an error,
> > not a magical transformation.

> I wouldn't call it 'magical' if the definition is right on the field
> declaration

Perhaps "magical" wasn't the best term.  My simple point is that in no
other case does inserting a value X into a column result in a
different value Y appearing there.  

The "value (DEFAULT)" syntax does not suffer from that problem.  

I hear someone saying "triggers".  Sure, you can abuse the system.
Anyone burned by triggers that do anything other than ensure
referential integrity quickly learns to be twice shy.  The system is
not improved when it subverts stated intentions.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Stephen Chrzanowski]

I wouldn't call it 'magical' if the definition is right on the field
declaration, and depending on the 'worth' of that particular bit of data
(Booleans in this case, and I've got absolutely zero concern to the actual
state of said boolean, but taking into consideration the typeless nature of
SQLite, NULL/Anything else? --BTW, I'm not dissing on the typeless aspect.
Due to my SQL2000 training back years yonder, I always define a type out of
habbit, but I'm always aware of the typlessness aspect), and weighing on
data loss, versus 'magical data', versus development covering grounds for
unexpected results, I'd personally build into the database a safety that
would try to protect itself at the database level.  Then implementing a
constraint based on something like ENUMs, where, I know that a default
value can safely be designed on a blank insert (Say a value reporting
'ERROR' of some sort), if something shows up that I'm not allowing in the
database, it'd resolve issues of program crashes.  Results may be
unexpected to the end user, but, there wouldn't be a crash, and you would
possibly alleviate the restriction on further processing.

I can understand the concern when looking at a well aged database that
you've never seen, do an insert/update and something unexpected shows up
without an error.  But in situations like that there would be two sources
of information to get a direction on why the unexpected result showed up.
Right at the table def'n, as well as (more infrequently) database
documentation in a KB or in a book/binder or whatever.

I can also see where how frequently this WOULDN'T be used, and quite right
that it shouldn't be used all the time.  However, I've come into an
instance where I just care MORE that there is data present and has SOME
kind of meaning, be it valid a RELEVANT choice (Not necessarily valid), or
something understood by the overhead application, and the program can
continue BECAUSE the value entered is expected by the program, regardless
if it makes sense to why that outcome is what it is.

There are two reasons I've "thought this up".  Obviously one is because I
said I was doing a new database up and saw a possible connection in the
SQLite IDE I use.  The other was when playing a game dealing with mods,
where if/when one mod breaks, the rest of the game still functions.  I'm
not kicked out of my game abruptly, and can continue on doing what I need
to do, but, that function of that additional mod is either disabled, or
eventually comes back due to whatever trigger starts getting it to process
what it needs to do.

As for the whole magic thing, every freak'n day I get up and I see any of
the technology sitting around, I find it magical that it even exists.  The
fact that this DBE does what it does is magic to me, not to mention the OS
variety it can run on, and maintain internal integrity REGARDLESS of what
platform it is running on.

On Wed, Oct 8, 2014 at 8:38 PM, James K. Lowden 
wrote:

>
> The problem I see with your suggestion is that I can't think of another
> situation, with or without NULL, with or without defaults, where
>
> insert into T (t) value (X)
>
> results in T.t = Y.  You should get what you asked for, or an error,
> not a magical transformation.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[James K. Lowden]

On Wed, 8 Oct 2014 00:14:51 -0400
Stephen Chrzanowski  wrote:

> When adding a NULL value to a table that has the NOT NULL flag set on
> that field, instead of raising an exception, if the field definition
> were to have the word "USE" between "ON CONFLICT" and "DEFAULT" in
> its declaration, it'd use whatever the fields default value was set
> to.  If USE is included, the DEFAULT value must be included,
> otherwise the table isn't created.

I think what you want is usually provided by the DML, not the DDL.  

> update ModGroups set ActiveOnServer=null where GroupID = 1;

becomes

update ModGroups set ActiveOnServer=DEFAULT where GroupID = 1;

which is more direct and IMO clearer.  

The problem I see with your suggestion is that I can't think of another
situation, with or without NULL, with or without defaults, where

insert into T (t) value (X) 

results in T.t = Y.  You should get what you asked for, or an error,
not a magical transformation.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Petite Abeille]

On Oct 8, 2014, at 8:51 PM, Stephen Chrzanowski  wrote:

> If the field def'n were to be changed to [ col2 NUMBER DEFAULT ON NULL 0 ]
> and then when I insert/update something that becomes NULL and the result
> becomes 0 for that field, then yeah, bingo.

Yep, that’s exactly what it says on the tin.

But back to SQLite... your best bet is to implement such behavior with 
triggers. Same difference really.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Stephen Chrzanowski]

If the field def'n were to be changed to [ col2 NUMBER DEFAULT ON NULL 0 ]
and then when I insert/update something that becomes NULL and the result
becomes 0 for that field, then yeah, bingo.

On Wed, Oct 8, 2014 at 2:14 PM, Petite Abeille 
wrote:

>
> On Oct 8, 2014, at 6:14 AM, Stephen Chrzanowski 
> wrote:
>
> > When adding a NULL value to a table that has the NOT NULL flag set on
> that
> > field, instead of raising an exception, if the field definition were to
> > have the word "USE" between "ON CONFLICT" and "DEFAULT" in its
> declaration,
> > it'd use whatever the fields default value was set to.  If USE is
> included,
> > the DEFAULT value must be included, otherwise the table isn't created.
>
> Oh, wait… isn’t that what an other, unnamed database does? [1]
>
> E.g. DEFAULT ON NULL
>
> [1]
> http://www.oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1.php#nulls
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Petite Abeille]

On Oct 8, 2014, at 6:14 AM, Stephen Chrzanowski  wrote:

> When adding a NULL value to a table that has the NOT NULL flag set on that
> field, instead of raising an exception, if the field definition were to
> have the word "USE" between "ON CONFLICT" and "DEFAULT" in its declaration,
> it'd use whatever the fields default value was set to.  If USE is included,
> the DEFAULT value must be included, otherwise the table isn't created.

Oh, wait… isn’t that what an other, unnamed database does? [1]

E.g. DEFAULT ON NULL

[1] 
http://www.oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1.php#nulls


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Markus Schaber]

Hi,

> It can even compare two fields in the same row and test one against the other
> so you can, for example, make sure you don't have any people who are both
> male and pregnant.

Actually, this restriction could be problematic:
https://en.wikipedia.org/wiki/Transgender_pregnancy



Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this 
e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Simon Slavin]

On 8 Oct 2014, at 1:22pm, Stephen Chrzanowski  wrote:

> Can one not also put a constraint up on the field to say that the field can
> only be of a certain value, kind of to emulate ENUM?

Sure you can.  You can do anything expressible in SQL which turns into a 
BOOLEAN value.  Use a CHECK constraint:



It can even compare two fields in the same row and test one against the other 
so you can, for example, make sure you don't have any people who are both male 
and pregnant.

You can also use FOREIGN KEYS to check that a value entered in a field really 
does point to a row in another table.

Unfortunately neither of these things fulfill your original request.  If the 
data fails these checks and is rejected, you just get a standard error code.  
It doesn't name the constraint which was failed.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Stephen Chrzanowski]

On Wed, Oct 8, 2014 at 1:26 AM, Simon Slavin  wrote:

>
> You could probably use a TRIGGER that detects the type of row you don't
> want and replaces it with your preferred form.  This doesn't do exactly
> what you want, but it is something like it.
>
>
That'd work, but extra effort and more potential debugging if a problem
comes up.  But getting into triggers I think goes more into your next point
of internally programming the engine via a language instead of the engine
going to the user and saying STAHP!!


> SQL doesn't detail any method of communicating with the user (or even the
> programmer).  From its origins as a simple demonstration of a concept it
> has grown so powerful that people try to do programming in it without using
> a proper programming language.  It may be that a new procedural data
> manipulation language will come along one day but I think it will allow the
> programmer to define data conflict in a more elegant manner.
>

CTEs anyone? ;)

I can see where you're coming from in that it might be more important to
let the programmer know about potential faults via raised exceptions, but
we've already got the IGNORE definition which could potentially cause data
loss instead of internal data manipulation.  Knowing you can't catch
everything without a very bloated and slow program, for some things, I
think it'd be alright if the engine took care of itself with the provisions
provided by the database author.  In my mind, if a default is given, and an
exception is raised due to a constraint, going back to a safe place with
safe data might be better than an exception being raised and just have
another thing to deal with.  Data loss is HORRIBLE, but, data manipulation
in certain criteria (As like in my example where a boolean can't have three
results -- True/False/NULL) by adding a single verb to the table construct
seems more palatable to me.

Can one not also put a constraint up on the field to say that the field can
only be of a certain value, kind of to emulate ENUM?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Simon Slavin]

On 8 Oct 2014, at 5:14am, Stephen Chrzanowski  wrote:

> The one downside I just realized is that ON CONFLICT can be used outside of
> the table declarations as well, so perhaps a different word or signal might
> be needed for it to make linguistic sense, or, this version of ON CONFLICT
> USE DEFAULT can only be used in the tables field def'n.
> 
> Thoughts?

You could probably use a TRIGGER that detects the type of row you don't want 
and replaces it with your preferred form.  This doesn't do exactly what you 
want, but it is something like it.

I did have one system which created reports on certain operations and put them 
in a TABLE called 'log'.  I could use TRIGGERs in that program to have it 
notice data I didn't like, report on it, then delete the offending row.  All 
the program had to do was look in TABLE log for anything new tagged 'conflict' 
and report it back to the user.  It was a pretty neat toy but it turned out 
more complicated than I really needed.

SQL doesn't detail any method of communicating with the user (or even the 
programmer).  From its origins as a simple demonstration of a concept it has 
grown so powerful that people try to do programming in it without using a 
proper programming language.  It may be that a new procedural data manipulation 
language will come along one day but I think it will allow the programmer to 
define data conflict in a more elegant manner.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible automatic ON CONFLICT resolution via DEFAULTS

[Stephen Chrzanowski]

I'm making up a small database (for yet another tool I never plan on
releasing) and during the table creation, I had a thought about the "Not
Null" and "On Conflict" resolution mechanism.

When adding a NULL value to a table that has the NOT NULL flag set on that
field, instead of raising an exception, if the field definition were to
have the word "USE" between "ON CONFLICT" and "DEFAULT" in its declaration,
it'd use whatever the fields default value was set to.  If USE is included,
the DEFAULT value must be included, otherwise the table isn't created.

So a sample of the new format, and expected results:

CREATE TABLE [ModGroups] (
  [GroupID] INTEGER NOT NULL,
  [GroupName] CHAR,
  [ActiveOnServer] BOOLEAN NOT NULL *USE *DEFAULT 1,
  [ActiveOnClient] BOOLEAN NOT NULL ON CONFLICT *USE *DEFAULT 1);

insert into ModGroups (GroupID, GroupName, ActiveOnServer, ActiveOnClient)
values (1,'Test',0,0);
select * from ModGroups;
1|Test|0|0

update ModGroups set ActiveOnServer=null where GroupID = 1;
select * from ModGroups;
1|Test|1|0

The benefit of this would be that you'd absolutely enforce the NOT NULL
constraint by populating it with something that isn't NULL, and you'd
potentially be filling the value with something of relevance, and you
wouldn't have an exception for something that should have a default value
anyways.  I can't see this as breaking existing databases as this would be
a new feature set.  Anything that exists in the wild won't use the new
routine, but, anything going forward would, so long it is defined as such.

There are cases, of course, where you'd want to raise an exception when
trying to insert a null value, however, for those reasons you wouldn't use
the word USE, but for my purpose, on a boolean field, having a third option
just doesn't make sense, so, I'd rather default it to whatever I assigned
as a default than raise an exception.  This might negate my looking at bad
code, but, at least my data wouldn't get hurt.

Since SQLite doesn't have type affinities, I know I could insert 'ABCD' in
either 'ActiveOn*' field directly via the CLI or a 3rd party SQLite DB
manager and it'd cause my app to crap as it isn't something it would
consider or designed to put into the field, but, if the app ever did come
across that kind of issue, having it set the value to NULL as a protection
mechanism would again add another layer of protection on the data to
eliminate bad data.  Something like [ update ModGroups set
ActiveOnServer=null where not ActiveOnServer in (0,1) ] would be a fast and
easy update.  The application wouldn't need to be aware of what the default
is.

The one downside I just realized is that ON CONFLICT can be used outside of
the table declarations as well, so perhaps a different word or signal might
be needed for it to make linguistic sense, or, this version of ON CONFLICT
USE DEFAULT can only be used in the tables field def'n.

Thoughts?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users