SQLite Forum

Can DEFAULT be the only possible value?
Login

Can DEFAULT be the only possible value?

(1) By anonymous on 2020-10-11 11:07:20 [link] [source]

I know how to do this with a trigger but investigating the [unlikely] possibility of doing it without one.

Can a column be forced to a predefined expression instead of just an overridable default one? I'm thinking something like:

CREATE TABLE log(dt ALWAYS(datetime('now','localtime')),event);

where INSERT or UPDATE of dt is either silently converted to datetime('now'), or allowed to be NULL if the column allows nulls.

This is a very common use case, and writing triggers seems like overkill. (Does any popular database support this?)

(2) By J. King (jking) on 2020-10-11 11:12:12 in reply to 1 [link] [source]

Sounds like you want the newly-implemented generated columns feature.

(3) By anonymous on 2020-10-11 11:28:29 in reply to 2 [link] [source]

Great idea but doesn't seem to work with datetime.

CREATE TABLE log(dt GENERATED ALWAYS AS (datetime('now','localtime')) STORED,event);
INSERT INTO log(event) VALUES('xxx');

gives:

Error: non-deterministic use of datetime() in a generated column

(5) By Keith Medcalf (kmedcalf) on 2020-10-11 19:29:53 in reply to 3 [source]

datetime('now', 'localtime') is non-deterministic and not permitted in a GENERATED ALWAYS clause.

Get rid of the parameters and the error will vanish.

SQLite version 3.34.0 2020-10-11 17:33:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table log(dt text as (datetime()) stored, event text not null);
sqlite> insert into log values ('xxx');
sqlite> select * from log;
┌─────────────────────┬───────┐
│         dt          │ event │
├─────────────────────┼───────┤
│ 2020-10-11 19:06:00 │ xxx   │
└─────────────────────┴───────┘

All the datetime functions are "weird" with respect to whether or not they are supported in GENERATED ALWAYS expressions or index expressions and the support decisions are arbitrary. (The default builtin datetime functions include date, time, datetime, strftime and julianday)

For example, you can use datetime() in a GENERATED ALWAYS but not datetime('now') even though they are 100% identical.

This is generally so that the SQL syntactic sugar CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP will work.

If you want to use 'localtime' you must convert it at retrieval time. This is because the 'localtime' is subject to the whim of politicians and sorcerers and what you think of as the localtime now may not be the same tomorrow.

(7) By anonymous on 2020-10-12 07:21:53 in reply to 5 [link] [source]

Worked! Thanks.

(6) By Keith Medcalf (kmedcalf) on 2020-10-11 19:38:59 in reply to 2 [link] [source]

There is a difference between:

create table t0 (dt default (datetime()), event);
and
create table t1 (dt as (datetime()) stored, event);

In the former case the value of dt is set once on row insert (if it is null or not provided by the insert statement) and can be updated at whim.

In the latter case the value of dt is set when the row is inserted or updated but cannot be set by an insert or update statement.

The only way to change these behaviours is though the use of triggers.

(8) By anonymous on 2020-10-12 08:42:54 in reply to 6 [link] [source]

create table t1 (dt as (datetime()) stored, event);

In the latter case the value of dt is set when the row is inserted or updated but cannot be set by an insert or update statement.

A bit unfortunate that the user has no control on whether to have the dt take a value only on INSERT but remain unchanged on further updates.

In many cases you need the original timestamp for creation_dt and update-able timestamps for change_dt fields.

Maybe an extension to the syntax could be made to have either STORED (when inserted/updated) or STORED ONCE (when first inserted).

(9) By Gunter Hick (gunter_hick) on 2020-10-12 13:35:06 in reply to 8 [link] [source]

I don't think an extension is necessary. Consider:

CREATE TABLE ... ( ..., creation_date default datetime(), modification_date as (datetime()) stored, ...);

Should this not save the datetime of the INSERT in creation_date?
Should this not save the datetime of the last update in modification_date?

The creation_date may need protection against updates in a BEFORE UPDATE trigger that calls RAISE(ABORT, 'update to creation_date is not allowed').

(10) By Keith Medcalf (kmedcalf) on 2020-10-12 18:43:27 in reply to 9 [link] [source]

You would be better served by having the creation_date default to NULL and using before insert and before update triggers to prevent it from being updated and an after insert trigger to set the creation_date. ie,

create table t(..., creation_date text default NULL, modification_date text as (datetime()) stored);

create trigger t_bi_creation_date before insert of creation_date on t
begin
 select raise(ABORT, 'Cannot set creation_date');
end;

create trigger t_bu_creation_date before update of creation_date on t when old.creation_date is not null
begin
 select raise(ABORT, 'Cannot update creation_date');
end;

create trigger t_ai_creation_date after insert on t
begin
  update t
     set creation_date = datetime()
    where rowid = new.rowid;
end;

Then the creation_date and modification_date fields will be entirely self maintaining and unfiddlable (save dropping the triggers first).

(11) By Keith Medcalf (kmedcalf) on 2020-10-12 19:34:37 in reply to 10 [link] [source]

create trigger t_bi_creation_date before insert of creation_date on t
begin
 select raise(ABORT, 'Cannot set creation_date');
end;

ooops ... you cannot use OF in an INSERT trigger ...

create trigger t_bi_creation_date before insert on t when new.creation_date is not null
begin
  select raise(ABORT, 'Cannot set creation_date');
end;

(4) By Simon Slavin (slavin) on 2020-10-11 14:40:59 in reply to 1 [link] [source]

You would need a feature which has access to both the old and new versions of the value, and the simplest way to do that is, as you already mentioned, triggers.

Another way to do it would be to implement an authorizer function which refuses all updates to a certain column of a certain table.

https://sqlite.org/c3ref/set_authorizer.html

#define SQLITE_UPDATE 23 /* Table Name Column Name */

However, authorizer function is defined per application. If you have just one app which changes the database this might be reasonable, but if you are defending against other apps making the change it won't work.