also: link posts to users and remove signature for some deduplication
QHK3BXHEC6IAAI7HK76Y7W3IQQIEFH7TD7AYMBCLOE7U2L7BT2LQC {ok, LoadedPost} = db:loadPost(Db, Id),?assertEqual(Decoded, LoadedPost),{ok, LoadedByHash} = db:loadPost(Db, Hash),?assertEqual(Decoded, LoadedByHash),
{ok, [Header, Body, Bin]} = db:loadPost(Db, Id),{ok, [Header, Body, Bin]} = db:loadPost(Db, Hash),
PostInsert = HeaderSansLinks ++ case proplists:get_value(type, Header) of
%% establish user id{rowid, _RowId} = sqlite3:sql_exec(Db, "INSERT INTO users (public_key) VALUES (?) ON CONFLICT DO NOTHING;", [{blob, PubKey}]),[{columns, ["id","public_key","name"]}, {rows, [{UserId, {blob, PubKey}, _Name}]}] = sqlite3:read(Db, users, {public_key, {blob, PubKey}}),%% construct post insert statementPostInsert = [{user_id, UserId}, {raw_post, Binary}] ++ HeaderTrimmed ++ case proplists:get_value(type, Header) of
ExpectedCols = ["id", "hash", "type", "timestamp", "public_key", "signature", "channel", "text", "topic", "deletedHashes", "infos"],
ExpectedCols = ["id", "hash", "type", "timestamp", "user_id", "raw_post", "channel", "text", "topic", "deletedHashes", "infos"],
channels => [ColId, {name, text, not_null}, {topic, text, not_null}],users => [ColId, {pubkey, blob, not_null}, {name, text, not_null}]
channels => [{name, text, [primary_key]}, {topic, text, not_null}],users => [ColId, {public_key, blob, [unique, not_null]}, {name, text}]