Something went wrong. Try again.
An ATProto Powered Reputation Network
Something went wrong. Try again.
1.0 kB · 21 lines
SQL
at master
12345678910111213141516171819202122ALTER TABLE trust_cache ADD COLUMN status TEXT NOT NULL DEFAULT '';ALTER TABLE trust_cache ADD COLUMN score REAL;
-- Convert legacy cache values before the JSON column disappears.UPDATE trust_cache SET status = COALESCE(json_extract(result, '$.status'), ''), score = json_extract(result, '$.score');
INSERT INTO trust_cache_paths(cache_key, position, did)SELECT c.cache_key, CAST(p.key AS INTEGER), p.valueFROM trust_cache c, json_each(c.result, '$.path') p;
INSERT INTO trust_cache_scores(cache_key, position, did, assertion, source, list_uri, score, comment)SELECT c.cache_key, CAST(p.key AS INTEGER), COALESCE(json_extract(p.value, '$.did'), ''), json_extract(p.value, '$.assertion'), COALESCE(json_extract(p.value, '$.source'), ''), COALESCE(json_extract(p.value, '$.list_uri'), ''), COALESCE(json_extract(p.value, '$.score'), 0), COALESCE(json_extract(p.value, '$.comment'), '')FROM trust_cache c, json_each(c.result, '$.path_scores') p;
ALTER TABLE trust_cache DROP COLUMN result;