BEGIN TRANSACTION;
/* Data tables */
create table Params (
id text primary key,
val text
);
insert into Params values ('user_perm', 1887); -- permLogin + permRead + permPost + permThreadStart + permEditOwn + permDelOwn + permChat + permDownload + permAttach
insert into Params values ('anon_perm', 3); -- permLogin + permRead
insert into Params values ('log_events', 0);
insert into Params values ('chat_enabled', 1);
insert into Params values ('default_skin', 'Wasp');
insert into Params values ('default_mobile_skin', 'mobile');
insert into Params values ('email_confirm', 1);
insert into Params values ('forum_header', '<img src="/images/title.svg" alt=
" ▄▄ ▄▄▄ ▄▄▄ Power
█ █ ▄▄▄▄ ▄▄▄▄▄ █ █ █ █
█▄▄█ █▄▄▄ █ █ █ █▀▀▄ █▀▀▄
█ █ ▄▄▄█ █ █ █ █▄▄▀ █▄▄▀
">'
);
create table Guests (
addr integer primary key not null,
LastSeen integer,
Client text
);
create index idxGuests_time on Guests(LastSeen);
create index idxGuestsDesc on Guests(addr desc);
create table GuestRequests (
addr integer references Guests(addr) on delete cascade,
time integer,
method text,
request text,
referer text,
client text
);
create index idxGuestRequests on GuestRequests(addr);
create table Users (
id integer primary key autoincrement,
nick text unique collate nocase,
passHash text unique,
salt text unique,
status integer, -- see permXXXXX constants.
user_desc text, -- free text user description.
avatar blob, -- copy of the user avatar.
av_time integer, -- the time the avatar has been latest changed.
email text unique, -- user email.
Register integer, -- the time when the user has activated the account.
LastSeen integer, -- the time when the user has been last seen by taking some action.
Lang text, -- the language of the user interface.
Skin text, -- the name of the UI skin.
PostCount integer default 0 -- Speed optimization in order to not count the posts every time. Need automatic count.
);
-- create index idxUsers_nick on Users (nick collate nocase); - not needed because of the UNIQUE clause.
create index idxUsers_email on Users (email);
create index idxUsersX on Users(id, nick, avatar);
create index idxUsers_LastSeen on Users(LastSeen);
CREATE TABLE UserLog (
userID integer,
remoteIP integer,
Time integer,
Activity integer,
Param integer,
Client text,
foreign key (userID) references Users(id) on delete cascade on update cascade
);
create index idxUserLogIP on userlog(remoteIP);
create index idxUserLogTime on UserLog(time); -- Any other index on UserLog ruins the performance. See users_online.sql for the query.
create table WaitingActivation (
a_secret text primary key,
operation integer not null, -- 0 = Registering account; 1 = change email; 2 = reseting password.
nick text unique collate nocase,
passHash text unique,
salt text unique,
email text unique,
ip_from integer not null,
time_reg integer,
time_email integer
);
create table Threads (
id integer primary key autoincrement,
Slug text unique,
Caption text,
LastChanged integer,
UserID integer references Users(id),
Pinned integer default 0,
PostCount integer default 0,
ReadCount integer default 0
);
create index idxThreadsPinnedLastChanged on Threads (Pinned desc, LastChanged desc);
create index idxThreadsSlug on Threads (Slug);
create table ThreadPosters (
firstPost integer references Posts(id) on delete cascade on update cascade,
threadID integer references Threads(id) on delete cascade on update cascade,
userID integer references Users(id) on delete cascade on update cascade
);
create unique index idxThreadPosters on ThreadPosters(threadID, userID);
create index idxThreadPostersOrder on ThreadPosters(threadid, firstPost, userid);
create table ThreadsHistory (
threadid integer,
Slug text,
Caption text,
LastChanged integer,
UserID integer,
Pinned integer
);
-- in order to avoid duplicated information in the history.
create unique index idxThreadsHistory on ThreadsHistory(Slug, Caption, Pinned);
create trigger ThreadsAU after update of Slug, Caption, UserID, Pinned on Threads begin
insert or ignore into ThreadsHistory(threadid, Slug, Caption, LastChanged, Pinned) values (
old.id,
old.Slug,
old.Caption,
old.LastChanged,
old.Pinned
);
end;
create trigger ThreadsAD after delete on Threads begin
insert or ignore into ThreadsHistory(threadid, Slug, Caption, LastChanged, Pinned) values (
old.id,
old.Slug,
old.Caption,
old.LastChanged,
old.Pinned
);
end;
create table Posts (
id integer primary key autoincrement,
threadID integer references Threads(id) on delete cascade,
userID integer references Users(id) on delete cascade,
postTime integer,
editUserID integer default NULL references Users(id) on delete cascade,
editTime integer default NULL,
format integer,
Content text,
Rendered text
);
create index idxPosts_UserID on Posts (userID);
create index idxPosts_ThreadID on Posts (threadID);
create index idxPostsThreadUser on posts(threadid, userid);
create table PostCnt (
postID integer references Posts(id) on delete cascade on update cascade,
count integer
);
create index idxPostCount on PostCnt(postid);
create table PostsHistory (
postID integer,
threadID integer,
userID integer,
postTime integer,
editUserID integer,
editTime integer,
format integer,
Content text
);
create unique index idxPostsHistory on PostsHistory(postID, Content);
CREATE VIRTUAL TABLE PostFTS using fts5(Content, Caption, slug, User, Tags, prefix="1 2 3", tokenize='porter unicode61 remove_diacritics 1');
CREATE TRIGGER PostsAI AFTER INSERT ON Posts BEGIN
insert into PostFTS(rowid, Content, Caption, slug, user, tags) VALUES (
new.id,
new.Content,
(select Caption from Threads where id=new.threadid),
(select slug from Threads where id = new.threadid),
(select nick from users where id = new.userid),
(select group_concat(TT.Tag, ", ") from ThreadTags TT where TT.threadID = new.threadid)
);
insert into PostCNT(postid,count) VALUES (new.id, 0);
insert or ignore into ThreadPosters(firstPost, threadID, userID) values (new.id, new.threadID, new.userID);
update Users set PostCount = PostCount + 1 where Users.id = new.UserID;
update Threads set PostCount = PostCount + 1 where id = new.threadID;
END;
CREATE TRIGGER PostsAD AFTER DELETE ON Posts BEGIN
delete from PostFTS where rowid = old.id;
update Users set PostCount = PostCount - 1 where Users.id = old.UserID;
update Threads set PostCount = PostCount - 1 where id = old.threadID;
delete from ThreadPosters where threadid = old.threadid and userid = old.userid;
insert into ThreadPosters(firstPost, threadID, userID) select min(id), threadid, userid from posts where threadid = old.threadid and userid = old.userid;
insert or ignore into PostsHistory(postID, threadID, userID, postTime, editUserID, editTime, format, Content) values (
old.id,
old.threadID,
old.userID,
old.postTime,
old.editUserID,
old.editTime,
old.format,
old.Content
);
END;
CREATE TRIGGER PostsAU AFTER UPDATE OF Content, editTime, editUserID, threadID, format ON Posts BEGIN
update PostFTS set
rowid = new.id,
Content = new.Content,
Caption = (select Caption from Threads where id=new.threadid),
slug = (select slug from Threads where id = new.threadid),
user = (select nick from users where id = new.userid),
tags = (select group_concat(TT.Tag, ", ") from ThreadTags TT where TT.threadID = new.threadid)
where rowid = old.id;
insert or ignore into PostsHistory(postID, threadID, userID, postTime, editUserID, editTime, format, Content) values (
old.id,
old.threadID,
old.userID,
old.postTime,
old.editUserID,
old.editTime,
old.format,
old.Content
);
update Threads set PostCount = PostCount - 1 where id = old.threadID;
update Threads set PostCount = PostCount + 1 where id = new.threadID;
END;
create table LimitedAccessThreads (
threadID integer references Threads(id) on delete cascade,
userID integer references Users(id) on delete cascade
);
create unique index idxLimitedAccessThreads on LimitedAccessThreads(threadID, userID);
create table Tags (
Tag text primary key,
Importance integer not null default 0,
Description text
);
create index idxTagImportance on Tags(Importance desc);
create index idxTagsTagImp on Tags(tag, importance desc);
/* Relation tables */
create table ThreadTags (
ThreadID integer references Threads(id) on delete cascade,
Tag text references Tags(Tag) on delete cascade on update cascade
);
create unique index idxThreadTagsUnique on ThreadTags ( ThreadID, Tag );
create index idxThreadsTagsTags on ThreadTags (Tag);
create table UnreadPosts (
UserID integer references Users(id) on delete cascade,
PostID integer references Posts(id) on delete cascade,
ThreadID integer references Threads(id) on delete cascade on update cascade,
Time integer
);
create unique index idxUnreadPosts on UnreadPosts(UserID, PostID);
create index idxThreadUnread on UnreadPosts(userID, threadID);
create index idxUnreadPostsPostID on UnreadPosts(PostID);
create table Attachments (
id integer primary key autoincrement,
postID integer references Posts(id) on delete cascade,
filename text,
changed integer,
file blob,
key blob, -- the random key for xor encrypting the blob
md5sum text
);
create index idxAttachments on Attachments(postID);
create unique index idxAttachmentsUnique on Attachments(postID, md5sum);
create table AttachCnt (
fileID integer references Attachments(id) on delete cascade,
count integer not null default 0
);
create index idxAttachCnt on AttachCnt(fileID);
create trigger AttachmentsAI after insert on Attachments begin
insert into AttachCnt(fileid, count) VALUES (new.id, 0);
end;
create table Sessions (
id integer primary key autoincrement,
userID integer references Users(id) on delete cascade on update cascade,
fromIP text,
fromPort integer,
sid text,
last_seen integer,
unique (userID, fromIP)
);
create index idxSessions_UserID on Sessions(UserID);
create index idxSessions_Sid on Sessions(sid);
create table Tickets (
ssn integer references Sessions(id) on delete cascade on update cascade,
time integer,
ticket text unique
);
create index idxTickets_time on Tickets(time);
create table Messages (
id text primary key,
msg text,
header text,
link text
);
insert into Messages VALUES ('login_bad_password','Login incorrect.
Only perfect spellers may
enter this system.','Incorrect user or password!',NULL);
insert into Messages VALUES ('register_passwords_different','Passwords different.
Only perfect spellers may
register this forum.','Not matching passwords!',NULL);
insert into Messages VALUES ('register_short_pass','Short password
has been chosen. However,
I disagree !','The password is too short!',NULL);
insert into Messages VALUES ('login_missing_data','So many fields,
you have to fill.
Missed some.','Empty field!',NULL);
insert into Messages VALUES ('register_user_exists','Maybe the nickname,
or maybe the email,
is already taken.','Not available nickname or email!',NULL);
insert into Messages VALUES ('register_short_name','Short nick is not an
advantage, but burden.
Get longer.','The nickname too short!',NULL);
insert into Messages VALUES ('register_short_email','Queer email,
never saw alike before.
Don''t like it!','Too short email address!',NULL);
insert into Messages VALUES ('register_technical','Foreboding of evil,
quick shadow in very cold day.
A server is dying.','Server problem!',NULL);
insert into Messages VALUES ('user_created','Just step remains,
the secret, magic mail
you shall receive.','Yes!','<a href="/">Home</a>');
insert into Messages VALUES ('congratulations','It happened,
the journey ended at the door.
You''re welcome.','Hooray!','<a href="/!login/">Come in</a>');
insert into Messages VALUES ('bad_secret','Defeats and wins
take turns each other.
Now is the first.','Oh, no!','<a href="/">Home</a>');
insert into Messages VALUES ('login_bad_permissions','You are a sinner,
because some man of power
have banned you.','Forbidden!',NULL);
insert into Messages VALUES ('error_cant_post','You can''t post here,
still have no privileges.
Or have no longer.','Missing privileges!',NULL);
insert into Messages VALUES ('error_cant_create_threads','New thread,
new hurricane of passions,
but not for you.','Can''t start threads!',NULL);
insert into Messages VALUES ('register_bad_email','This email
does not looks like real.
It shall not pass!','Invalid email address!',NULL);
insert into Messages VALUES ('error_post_not_exists','With searching comes loss
and the presence of absence:
post not exists.','Missing post!',NULL);
insert into Messages VALUES ('error_cant_write','Write has failed.
I can''t tell you where or why.
Lazy programmers.','Unknown error!',NULL);
insert into Messages VALUES ('error_thread_not_exists','With searching comes loss
and the presence of absence:
thread not exists.','Missing thread!',NULL);
insert into Messages VALUES ('error_invalid_caption','The title is
missing, it''s pointless
to post, after all.','Empty title!',NULL);
insert into Messages VALUES ('error_invalid_content','Silence is golden.
But try to be silent without
posting void.','Empty post body!',NULL);
insert into Messages VALUES ('register_bot','Attempt to cheat
was miserable failure.
So, shame on you!','Cheat attempt detected!',NULL);
insert into Messages VALUES ('error_bad_ticket','Simple, deep, and still.
The old masters were patient.
Without desires.','Can''t post right now!',NULL);
insert into Messages VALUES ('password_changed','Your worthy password,
successfully has been changed.
You''r on the safe side.','Pasword changed!','<a href="/!login/">Login</a>');
insert into Messages VALUES ('change_different','Passwords different.
Only perfect spellers may
change their password.','Not matching passwords!',NULL);
insert into Messages VALUES ('change_password','Your present password,
you must provide as evidence
of your intentions.','Password does not match!',NULL);
insert into Messages VALUES ('email_activation_sent','The secret token
was sent to your new email.
To check the channel.','Check your mailbox!',NULL);
insert into Messages VALUES ('email_changed','The new address to send
a messages will never change
the old relationship.','E-mail has been changed!','<a href="/">Home</a>');
insert into Messages VALUES ('error_cant_delete','You can''t delete it,
still have no privileges.
Or have no longer.','Missing privileges!',NULL);
insert into Messages VALUES ('only_for_admins','Too dangerous place.
Not allowed to enter right now.
Maybe some day...
','For administrators only!','<a href="/">Home</a>');
insert into Messages VALUES ('missing_query','Looking for something,
Unknown but so desired.
Do meditate first.
','What are you looking for?',NULL);
insert into Messages VALUES ('cant_read','Knocking on the door
Please introduce yourself.
Are you expected?
','Private place!','<a href="/!login">Login first</a>');
insert into Messages VALUES ('closed_registration','This very place
Is not a place for you.
Go elsewhere now.
','Closed forum!','<a href="https://duckduckgo.com">A good place to start</a>');
create table Log (
process_id integer, -- the unique process id
timestamp integer,
event text, -- what event is logged - start process, end process, start request, end request
value text, -- details in variable form.
runtime integer
);
create table ProcessID (
id integer primary key autoincrement
);
create table ScratchPad (
name text primary key not null,
source text
);
create table ChatLog (
id integer primary key autoincrement,
time integer,
user text,
original text,
Message text
);
create table EventSessions (
session text unique not null,
time integer,
username text,
original text,
status integer
);
create index idxEventSessionsTime on EventSessions(time);
create index idxEventSessionsOrig on EventSessions(original);
create table EventQueue (
id integer primary key autoincrement,
type integer,
event text,
receiver text -- the sessionID of the receiver. If NULL then broadcast to all subscribed.
);
COMMIT;