diff options
author | Tom Smeding <tom@tomsmeding.com> | 2025-01-19 22:14:03 +0100 |
---|---|---|
committer | Tom Smeding <tom@tomsmeding.com> | 2025-01-19 22:14:15 +0100 |
commit | 6841765fcbfadbd4e3006f5fbcea45194c92e5a1 (patch) | |
tree | ff58fa6c9c687db9abe0783b58289947608c4e9e /server/schema.sql | |
parent | 0d55021d2fa41d318abd8fe43563e12a661937fa (diff) |
server: register & login
Diffstat (limited to 'server/schema.sql')
-rw-r--r-- | server/schema.sql | 62 |
1 files changed, 62 insertions, 0 deletions
diff --git a/server/schema.sql b/server/schema.sql new file mode 100644 index 0000000..54eac1f --- /dev/null +++ b/server/schema.sql @@ -0,0 +1,62 @@ +-- database schema version 1 + +pragma foreign_keys = on; + +create table Meta ( + version integer not null +); +insert into Meta(version) values (1); + +create table Users ( + id integer primary key, + username text unique not null, + passhash blob not null +); + +create table Logins ( + user integer not null, + token text unique not null, + expire integer not null -- unix timestamp, token invalid >= this time +); +create index Logins_user on Logins (user); + +create table Files ( + id integer primary key, + owner integer not null, + path text unique not null, -- names separated with single '/', neither start nor end with '/' + + foreign key (owner) references Users(id) on delete cascade +); + +-- Undo history of a file +create table Updates ( + id integer primary key, + file integer not null, + idx integer not null, -- index in the update history of this file; first is 0 + + position integer not null, + -- If old_value is null & new_value exists: + -- insert item _before_ the given position; the new item has itemid. + -- If old_value exists & new_value is null: + -- delete item at the given position; that item had itemid. + -- If old_value exists & new_value exists: + -- modify the item at the given position, which has itemid. + -- If old_value is null & new_value is null: + -- <disallowed>. + itemid integer not null, + old_value blob null, + new_value blob null, + + foreign key (file) references Files(id) on delete cascade, + unique (file, idx) +); + +-- Current state of a file +create table Chunks ( + id integer primary key, + file integer not null, + position integer not null, + contents blob not null, + + unique (file, position) +); |