User:Bluedeck/etc/sandbox/box1515462153932

de schema design 1

create table content(
    id bigint not null,
    content mediumtext not null,
    primary key (id)
);

create table stats(
    hash char(64) not null,
    content varchar(32768) not null,
    primary key (hash)
);

create table user(
    username varchar(20) not null,
    id bigint not null auto_increment,
    creation_jst bigint not null,
    stats char(64) not null,
    secret char(64) not null,
    server_salt char(64) not null,
    client_salt char(64) not null,
    /* server salt and client salt derived at the time of creation of a user, and remains throughout the lifetime of the database. Alternatively the client salt can be derived from server salt. The client salt is served to the client to pre-hash the passphrase before sending it to the server */
    2fa char(64) not null,
    3fa char(64) not null,
    4fa char(64) not null,
    5fa char(64) not null,
    recovery char(64) not null,
    /* recovery is another she 256. all token slot are populated regardless of whether or not they're requested. */
    primary_mail varchar(20),
    blocked_till bigint not null,
    primary key (id),
    foreign key (stats) references stats(hash)
);
/* the server will maintain some entropy and a counter in its memory. invoking the entropy involves sending a query to the server. new_entropy = digest( old_entropy + counter++ + query); exposed_entropy = (old_entropy + counter++) */

create table page(
    id bigint not null auto_increment,
    stats char(64) not null,
    content bigint not null,
    deleted boolean not null,
    protected_till bigint not null,
    creation_jst bigint not null,
    latest_jst bigint not null,
    creator_id bigint not null,
    editor_id bigint not null,
    primary key (id),
    foreign key (stats) references stats(hash),
    foreign key (content) references content(id),
    foreign key (creator_id) references user(id),
    foreign key (editor_id) references user(id)
);

create table session(
    user_id bigint not null,
    id bigint not null auto_increment,
    description varchar(512) not null,
    token char(64) not null,
    expiry bigint not null,
    primary key (id),
    foreign key (user_id) references user(id)
);

create table history(
    page_id bigint not null,
    content bigint not null,
    time_of_link bigint not null,
    user_id bigint not null,
    summary varchar(512) not null,
    id bigint not null auto_increment,
    primary key(id),
    foreign key (content) references content(id),
    foreign key (page_id) references page(id),
    foreign key (user_id) references user(id)
);

create table log(
    jst bigint not null,
    id bigint not null auto_increment,
    stats char(64) not null,
    user_id bigint not null,
    target_type varchar(64) not null,
    col1 bigint,
    col2 bigint,
    col3 bigint,
    col4 bigint,
    col5 bigint,
    summary varchar(512) not null,
    primary key (id),
    foreign key (stats) references stats(hash),
    
    foreign key (col1) references user(id),
    foreign key (col2) references page(id),
    foreign key (col3) references history(id),
    foreign key (col4) references session(id),
    foreign key (col5) references log(id),
    
    constraint one_of_ids_not_null
    check (
           (col1 is not null and col2 is null and col3 is null and col4 is null and col5 is null)
        or (col2 is not null and col3 is null and col4 is null and col5 is null and col1 is null) 
        or (col3 is not null and col4 is null and col5 is null and col1 is null and col2 is null) 
        or (col4 is not null and col5 is null and col1 is null and col2 is null and col3 is null) 
        or (col5 is not null and col1 is null and col2 is null and col3 is null and col4 is null) 
    )
);