tables.sql 2.8 KB
Newer Older
David Morley's avatar
David Morley committed
1 2 3
CREATE TABLE pods (
 id serial8 UNIQUE PRIMARY KEY,
 domain text UNIQUE NOT NULL,
4
 name text,
5
 softwarename text,
dmorley's avatar
dmorley committed
6
 masterversion text,
dmorley's avatar
dmorley committed
7
 fullversion text,
dmorley's avatar
dmorley committed
8
 shortversion text,
9
 stats_apikey text,
10 11 12
 score int DEFAULT 50,
 weightedscore numeric(5,2) DEFAULT 0,
 userrating decimal DEFAULT 0,
13
 ip text,
14
 detectedlanguage text,
15
 country text,
16
 countryname text,
17 18 19 20
 city text,
 state text, 
 lat text,
 long text,
David Morley's avatar
David Morley committed
21
 email text,
dmorley's avatar
dmorley committed
22
 ipv6 boolean,
David Morley's avatar
David Morley committed
23 24
 sslvalid text,
 monthsmonitored int,
25
 daysmonitored int,
dmorley's avatar
dmorley committed
26
 signup boolean,
27 28 29 30
 total_users int, 
 active_users_halfyear int,
 active_users_monthly int,
 local_posts int,
dmorley's avatar
dmorley committed
31
 uptime_alltime numeric(5,2),
dmorley's avatar
dmorley committed
32
 status smallint DEFAULT 1,
dmorley's avatar
latent  
dmorley committed
33
 latency smallint,
dmorley's avatar
dmorley committed
34
 service_xmpp boolean,
dmorley's avatar
dmorley committed
35
 services jsonb,
dmorley's avatar
dmorley committed
36
 protocols jsonb,
dmorley's avatar
dmorley committed
37
 token text,
David Morley's avatar
David Morley committed
38
 publickey text,
dmorley's avatar
dmorley committed
39
 tokenexpire timestamp,
David Morley's avatar
David Morley committed
40
 podmin_statement text,
41
 podmin_notify boolean,
dmorley's avatar
dmorley committed
42
 podmin_notify_level int DEFAULT 50,
dmorley's avatar
dmorley committed
43 44
 sslexpire timestamp,
 dnssec boolean,
45
 comment_counts int,
46
 weight int DEFAULT 10,
dmorley's avatar
dmorley committed
47 48 49
 date_updated timestamp DEFAULT current_timestamp,
 date_laststats timestamp DEFAULT current_timestamp,
 date_created timestamp DEFAULT current_timestamp
David Morley's avatar
David Morley committed
50
);
dmorley's avatar
dmorley committed
51

52
CREATE TABLE ratingcomments (
David Morley's avatar
David Morley committed
53 54 55 56
 id serial8 UNIQUE PRIMARY KEY,
 domain text NOT NULL,
 comment text,
 pod_id int,
57 58
 rating int,
 username text,
59
 date_created timestamp DEFAULT current_timestamp
60
);
dmorley's avatar
dmorley committed
61

dmorley's avatar
dmorley committed
62
CREATE TABLE apikeys (
noplanman's avatar
noplanman committed
63
 id serial8 UNIQUE PRIMARY KEY,
dmorley's avatar
dmorley committed
64 65 66
 key text,
 email text,
 usage int,
dmorley's avatar
dmorley committed
67
 date_created timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
68 69 70
);

CREATE TABLE clicks (
noplanman's avatar
noplanman committed
71
 id serial8 UNIQUE PRIMARY KEY,
dmorley's avatar
dmorley committed
72 73 74
 domain text,
 manualclick int,
 autoclick int,
dmorley's avatar
dmorley committed
75
 date_clicked timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
76 77 78
);

CREATE TABLE checks (
noplanman's avatar
noplanman committed
79
 id serial8 UNIQUE PRIMARY KEY,
dmorley's avatar
dmorley committed
80 81 82
 domain text,
 online boolean,
 error text,
dmorley's avatar
latent  
dmorley committed
83
 latency numeric(8,6),
David Morley's avatar
David Morley committed
84 85 86 87
 total_users int,
 local_posts int,
 comment_counts int,
 shortversion text,
dmorley's avatar
dmorley committed
88
 version text,
David Morley's avatar
David Morley committed
89 90 91 92
 date_checked timestamp DEFAULT current_timestamp
);

CREATE TABLE masterversions (
noplanman's avatar
noplanman committed
93
 id serial8 UNIQUE PRIMARY KEY,
David Morley's avatar
David Morley committed
94 95
 software text,
 version text,
96 97
 devlastcommit timestamp,
 releasedate timestamp,
dmorley's avatar
dmorley committed
98
 date_checked timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
99
);
dmorley's avatar
dmorley committed
100

dmorley's avatar
dmorley committed
101 102 103 104 105 106 107 108 109 110
CREATE TABLE monthlystats (
 id serial8 UNIQUE PRIMARY KEY,
 total_users int,
 total_posts int,
 total_comments int,
 total_pods int,
 total_uptime int,
 date_checked timestamp DEFAULT current_timestamp
);

dmorley's avatar
dmorley committed
111
CREATE TABLE meta (
dmorley's avatar
dmorley committed
112
 id serial8 UNIQUE PRIMARY KEY,
dmorley's avatar
dmorley committed
113 114 115
 name text,
 value text,
 date_created timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
116
);
dmorley's avatar
dmorley committed
117 118 119 120 121 122 123 124
INSERT INTO meta (name) VALUES('languages_updated');
INSERT INTO meta (name) VALUES('cacert_updated');
INSERT INTO meta (name) VALUES('geoip_updated');
INSERT INTO meta (name) VALUES('masterversions_updated');
INSERT INTO meta (name) VALUES('federation_updated');
INSERT INTO meta (name) VALUES('statstable_updated');
INSERT INTO meta (name) VALUES('backup');
INSERT INTO meta (name) VALUES('pods_updated');
125
INSERT INTO meta (name) VALUES('sitemap_updated');