1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
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)
);
|
