Maybe `insert-one!` ought to query entity inserted?

[vkz]

trafficstars

Hi Bogdan.

I wonder if this logic ought to be reconsidered. What happens when you insert an entity into SQLite? You simply query for the last inserted row-id and update the entity struct that was past in to insert-one! and then simply return this entity to the user. I imagine there's an implicit assumption that what you insert is what you have in the database. Unfortunately, this isn't true when you have triggers that may e.g. default some values in the inserted row. I learnt this the hard way.

Here's an example:

create table if not exists test (
  id        integer primary key autoincrement,
  ts        timestamp default (strftime('%Y-%m-%dT%H:%M:%S', CURRENT_TIMESTAMP)),
  gt        timestamp,
  gf        text
);

create trigger if not exists test_default_ts_compute_gt after insert on test
BEGIN
  update test set ts = strftime('%Y-%m-%dT%H:%M:%S', ifnull(ts, CURRENT_TIMESTAMP)) where id = new.id;
  update test set gt = strftime('%Y-%m-%dT%H:%M:%S', ifnull(gt, datetime(ts, new.gf))) where id = new.id;
END;

This will render the entity returned by insert-one! stale.

What I think would be a less dangerous and more intuitive approach is to do the moral equivalent of:

BEGIN TRANSACTION;
      insert into test (ts, gt, gf) values (NULL, NULL, '24 hours');
      SELECT * FROM test where id = (SELECT last_insert_rowid());
END TRANSACTION;

This probably applies to other DBs not just SQLite.

Thank you

[Bogdanp]

I see your point, though it seems wasteful to have to do that (especially with databases that don't support RETURNING) by default given that tables with triggers are the exception not the rule.

To give a concrete counter-example, this week I have used deta to transfer over 30 TB of data from one database to another. Had deta re-selected after every insert, that would've translated into meaningful bandwidth and run time costs.

Maybe there could be a flag on fields to specify that the data ought to be re-hydrated after insert/update. Something along the lines of

(define-schema test
  ([id integer/f #:primary-key #:auto-increment]
   [ts datetime/f #:read-after-write]
   [gt datetime/f #:read-after-write]))

Since the user still has to think about this issue up-front that might not be much of an improvement over them just doing a lookup after an insert, though.