\prompt 'domain name: ' domain
\prompt 'bind to socket: ' bind
\qecho 'by default, parsav tracks rights on its own. you can override this later by replacing the rights table with a view, but you''ll then need to set appropriate rules on the view to allow administrators to modify rights from the web UI, or set the rights-readonly flag in the config table to true. for now, enter the name of an actor who will be granted full rights when she logs in.'
\prompt 'admin actor: ' admin
\qecho 'you will need to create an authentication view mapping your user database to something parsav can understand; see auth.sql for an example. enter the name of the view to use.'
\prompt 'auth view: ' auth
begin;
drop table if exists parsav_config;
create table if not exists parsav_config (
key text primary key,
value text
);
insert into parsav_config (key,value) values
('bind',:'bind'),
('domain',:'domain'),
('auth-source',:'auth'),
('administrator',:'admin');
-- note that valid ids should always > 0, as 0 is reserved for null
-- on the client side, vastly simplifying code
drop table if exists parsav_servers cascade;
create table parsav_servers (
id bigint primary key default (1+random()*(2^63-1))::bigint,
domain text not null,
key bytea
);
drop table if exists parsav_actors cascade;
create table parsav_actors (
id bigint primary key default (1+random()*(2^63-1))::bigint,
nym text,
handle text not null, -- nym [@handle@origin]
origin bigint references parsav_servers(id)
on delete cascade, -- null origin = local actor
bio text,
rank smallint not null default 0,
quota integer not null default 1000,
key bytea, -- private if localactor; public if remote
unique (handle,origin)
);
drop table if exists parsav_rights cascade;
create table parsav_rights (
key text,
actor bigint references parsav_actors(id)
on delete cascade,
allow boolean,
primary key (key,actor)
);
insert into parsav_actors (handle,rank,quota) values (:'admin',1,0);
insert into parsav_rights (actor,key,allow)
select (select id from parsav_actors where handle=:'admin'), a.column1, a.column2 from (values
('ban',true),
('config',true),
('censor',true),
('suspend',true),
('rebrand',true)
) as a;
drop table if exists parsav_posts cascade;
create table parsav_posts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
author bigint references parsav_actors(id)
on delete cascade,
subject text,
body text,
posted timestamp not null,
discovered timestamp not null,
scope smallint not null,
convo bigint, parent bigint,
circles bigint[], mentions bigint[]
);
drop table if exists parsav_conversations cascade;
create table parsav_conversations (
id bigint primary key default (1+random()*(2^63-1))::bigint,
uri text not null,
discovered timestamp not null,
head bigint references parsav_posts(id)
);
drop table if exists parsav_rels cascade;
create table parsav_rels (
relator bigint references parsav_actors(id)
on delete cascade, -- e.g. follower
relatee bigint references parsav_actors(id)
on delete cascade, -- e.g. follower
kind smallint, -- e.g. follow, block, mute
primary key (relator, relatee, kind)
);
drop table if exists parsav_acts cascade;
create table parsav_acts (
id bigint primary key default (1+random()*(2^63-1))::bigint,
kind text not null, -- like, react, so on
time timestamp not null,
actor bigint references parsav_actors(id)
on delete cascade,
subject bigint -- may be post or act, depending on kind
);
drop table if exists parsav_log cascade;
create table parsav_log (
-- accesses are tracked for security & sending delete acts
id bigint primary key default (1+random()*(2^63-1))::bigint,
time timestamp not null,
actor bigint references parsav_actors(id)
on delete cascade,
post bigint not null
);
end;