On Friday, 30 August, 2019 14:45, Random Coder <random.co...@gmail.com> wrote:
> CREATE TABLE IF NOT EXISTS > event( > event_id TEXT PRIMARY KEY, > first_seen INTEGER, > last_seen INTEGER > ) WITHOUT ROWID; So first_seen and last_seen are permitted to be NULL? > INSERT INTO event(event_id, first_seen, last_seen) VALUES(?, ?, ?) > ON CONFLICT(event_id) DO UPDATE SET > first_seen = MIN(excluded.first_seen, hashes.first_seen), > last_seen = MAX(excluded.last_seen, hashes.last_seen); MAX(NULL, <anything not null>) -> NULL MIN(NULL, <anything not null>) -> NULL > ATTACH ':memory:' AS mem_db; > CREATE TABLE mem_db.valid(event_id TEXT PRIMARY KEY); >And populate that table with the events the user is interested in. >I'm doing this since I won't get the list of items in one list, it's >built up over some minutes. If the system running dies in the middle >of a request, it's OK to start over. Then I run: > SELECT > mem_db.valid.event_id, > event.first_seen, > event.last_seen > FROM > mem_db.valid > LEFT JOIN event ON > event.event_id = mem_db.valid.event_id; >And gather up the results and pretty them up for the user. >Does all of this seem valid? It works, so I'm OK with it, but I'm >far from a SQLite expert, and I want to know if I'm going to be >backing myself into a corner or otherwise torturing things that >should be done differently. Seems fine, other than that event.first_seen and event.last_seen can be NULL, in which case that field will never be updated. So while you may claim that you never store NULL in those fields, doing so will cause non-workage due to integrity failure, and the purpose of a DBMS is to enforce integrity. >Thanks for any feedback. Just my 2 cents. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
