forked from clojars/clojars-web
-
Notifications
You must be signed in to change notification settings - Fork 0
/
clojars.sql
72 lines (63 loc) · 2.13 KB
/
clojars.sql
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
63
64
65
66
67
68
69
70
create table users
(id INTEGER PRIMARY KEY AUTOINCREMENT,
user TEXT UNIQUE NOT NULL,
password TEXT NOT NULL,
salt TEXT NOT NULL,
email TEXT NOT NULL,
ssh_key TEXT NOT NULL,
created DATE NOT NULL);
create table jars
(id INTEGER PRIMARY KEY AUTOINCREMENT,
group_name TEXT NOT NULL,
jar_name TEXT NOT NULL,
version TEXT NOT NULL,
user TEXT NOT NULL,
created DATE NOT NULL,
description TEXT,
homepage TEXT,
scm TEXT,
authors TEXT);
create table deps
(id INTEGER PRIMARY KEY AUTOINCREMENT,
group_name TEXT NOT NULL,
jar_name TEXT NOT NULL,
version TEXT NOT NULL,
dep_group_name TEXT NOT NULL,
dep_jar_name TEXT NOT NULL,
dep_version TEXT NOT NULL);
create table groups
(id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
user TEXT NOT NULL);
--
-- Search support: quick and dirty, but it works
--
create virtual table search using fts3
(id INTEGER PRIMARY KEY,
content text not null,
jar_name text not null,
group_name text not null);
create trigger insert_search insert on jars
begin
delete from search where jar_name = new.jar_name and group_name = new.group_name;
insert into search (id, jar_name, group_name, content) values
(new.id, new.jar_name, new.group_name,
new.jar_name || ' ' ||
new.group_name || ' ' ||
new.version || ' ' ||
coalesce(new.authors, '') || ' ' ||
new.user || ' ' ||
coalesce(new.description, ''));
end;
create trigger update_search update on jars
begin
delete from search where jar_name = new.jar_name and group_name = new.group_name;
insert into search (id, jar_name, group_name, content) values
(new.id, new.jar_name, new.group_name,
new.jar_name || ' ' ||
new.group_name || ' ' ||
new.version || ' ' ||
coalesce(new.authors, '') || ' ' ||
new.user || ' ' ||
coalesce(new.description, ''));
end;