Update Sqlite UPDATE and INSERT logic to use ON CONFLICT #5081
Description
Is your feature request related to a problem? Please describe.
Migrate from this redundant code:
conn.execute(
f"""
UPDATE {table_name}
SET value = ?, event_ts = ?, created_ts = ?
WHERE (entity_key = ? AND feature_name = ?)
""",
(
# SET
val.SerializeToString(),
timestamp,
created_ts,
# WHERE
entity_key_bin,
feature_name,
),
)
conn.execute(
f"""INSERT OR IGNORE INTO {table_name}
(entity_key, feature_name, value, event_ts, created_ts)
VALUES (?, ?, ?, ?, ?)""",
(
entity_key_bin,
feature_name,
val.SerializeToString(),
timestamp,
created_ts,
),
)To
conn.execute(
f"""
INSERT INTO {table_name} (entity_key, feature_name, value, event_ts, created_ts)
VALUES (?, ?, ?, ?, ?)
ON CONFLICT(entity_key, feature_name) DO UPDATE SET
value = excluded.value,
event_ts = excluded.event_ts,
created_ts = excluded.created_ts;
""",
(
entity_key_bin, # entity_key
feature_name, # feature_name
val.SerializeToString(), # value
timestamp, # event_ts
created_ts # created_ts
),
)Describe the solution you'd like
Update with this code
Describe alternatives you've considered
N/A
Additional context
Should do this for sqlite-vec use cases too.