LJYBM2AL5Z3TRSMCRTWSJ7XADJ6H5UWCMLFXMJIHEJZX2YDWJTMQC QTLCENKPK4QOQJTHEAWAJYTJWVH7ZI5KQ2CQTMJRMA4TOMCHTVBAC BXNLFE3IMYFUYXQ6RYXKU4FVWV4T2H7WI5AL2DVYJMA4X2I5XB6QC 755UGKECZ3PFYEA2TFFOUZTF27CRQTBZWO7UYFU6WQDJMZDBVPRAC R4JDMB7LL3FLA4NJEAV2DQEXII5XS5KIMG3H4YS5P6W7ZZUE7FIQC CBHKQGLDCAH2E4ZNACITBSMADOKPERFCWQPUGMH7UN5TLJXLYI4QC JVURIEXR72OUGZ2EHP5HB6OMXUNPHMAV66YUUP2444TPOG5XGASQC QHK3BXHEC6IAAI7HK76Y7W3IQQIEFH7TD7AYMBCLOE7U2L7BT2LQC RZB6HZ2NI5PIUIWQL63CSKXZGGUG4L6XCSCGR3TI723OO57NDT3AC 2R3WFEOT3WWS6NFBBABSVRUNUPTXHFFMGPZZQOCPLTD2WB3U55HQC MONVI5STEDKY5ALVMEXJJXDUX6XQRKTFLP7BBNOQML3VSJEM2JAAC -- these exist for all posts--id integer primary key AUTOINCREMENT,type integer not null check (type >= 0 AND type <= 5 ),-- milliseconds since UNIX Epochtimestamp integer not null check (timestamp > 0),-- depends on postType-- join & leave don't have other fields then channeltext text,topic text,deletedHashes blob, -- concatenated-- `post/text` | a textual chat message, posted to a channel |CONSTRAINT is_text check (type != 0 OR (channel is not null AND text is not null)),-- `post/delete`| the deletion of a previously published post |CONSTRAINT is_delete check (type != 1 OR (deletedHashes is not null)),-- `post/info` | set or clear informative key/value pairs on a userCONSTRAINT is_info check (),-- `post/topic` | set or clear a channel's topic stringCONSTRAINT is_topic check (type != 3 OR (channel is not null AND topic is not null)),-- `post/join` | announce membership to a channelCONSTRAINT is_join check (type != 4 OR (channel is not null)),-- `post/leave` | announce cessation of membership to a channelCONSTRAINT is_leave check (type != 5 OR (channel is not null));source blob not null,);CREATE INDEX IF NOT EXISTS parent_idx ON links(parent);-- we will get posts for which we don't have the parent's (yet)-- foreign key(source) references posts(hash),-- foreign key(parent) references posts(hash),parent blob not nullcreate table IF NOT EXISTS links (channel text,),foreign key(user_id) references users(id)type != 2 OR (infos is not null)infos blob, -- encoded as (json?) map {k=>v, k=>v}channel text, -- all but delete have this oneuser_id integer not null,raw_post blob not null,hash blob unique not null check (length(hash) = 32),create table IF NOT EXISTS posts (
%% read posts and links schema and exec itPrivDir = code:priv_dir(cable),SchemaPath = filename:join(PrivDir, "schema.sql"),{ok, Schema} = file:read_file(SchemaPath),
%% Embedded schema - more reliable than code:priv_dir/1Schema = <<"create table IF NOT EXISTS posts (-- these exist for all posts--id integer primary key AUTOINCREMENT,hash blob unique not null check (length(hash) = 32),type integer not null check (type >= 0 AND type <= 5 ),-- milliseconds since UNIX Epochtimestamp integer not null check (timestamp > 0),user_id integer not null,raw_post blob not null,-- depends on postTypechannel text, -- all but delete have this one-- join & leave don't have other fields then channeltext text,topic text,deletedHashes blob, -- concatenatedinfos blob, -- encoded as (json?) map {k=>v, k=>v}-- `post/text` | a textual chat message, posted to a channel |CONSTRAINT is_text check (type != 0 OR (channel is not null AND text is not null)),-- `post/delete`| the deletion of a previously published post |CONSTRAINT is_delete check (type != 1 OR (deletedHashes is not null)),-- `post/info` | set or clear informative key/value pairs on a userCONSTRAINT is_info check (type != 2 OR (infos is not null)),-- `post/topic` | set or clear a channel's topic stringCONSTRAINT is_topic check (type != 3 OR (channel is not null AND topic is not null)),-- `post/join` | announce membership to a channelCONSTRAINT is_join check (type != 4 OR (channel is not null)),-- `post/leave` | announce cessation of membership to a channelCONSTRAINT is_leave check (type != 5 OR (channel is not null)),foreign key(user_id) references users(id));create table IF NOT EXISTS links (channel text,source blob not null,parent blob not null-- we will get posts for which we don't have the parent's (yet)-- foreign key(source) references posts(hash),-- foreign key(parent) references posts(hash),);CREATE INDEX IF NOT EXISTS parent_idx ON links(parent);">>,