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
