tables.sql 1.83 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 7
 masterversion text,
 shortversion text,
David Morley's avatar
David Morley committed
8
 score int DEFAULT 10,
dmorley's avatar
dmorley committed
9
 weightedscore numeric(5,2) DEFAULT 10,
10 11
 adminrating decimal DEFAULT 10,
 userrating decimal DEFAULT 10,
dmorley's avatar
dmorley committed
12
 hidden boolean DEFAULT true,
13
 ip text,
14
 country text,
15 16 17 18
 city text,
 state text, 
 lat text,
 long text,
David Morley's avatar
David Morley committed
19
 email text,
dmorley's avatar
dmorley committed
20 21
 ipv6 boolean,
 secure boolean,
David Morley's avatar
David Morley committed
22 23
 sslvalid text,
 monthsmonitored int,
dmorley's avatar
dmorley committed
24
 signup boolean,
25 26 27 28
 total_users int, 
 active_users_halfyear int,
 active_users_monthly int,
 local_posts int,
dmorley's avatar
dmorley committed
29
 uptime_alltime numeric(5,2),
David Morley's avatar
David Morley committed
30
 status text,
dmorley's avatar
dmorley committed
31
 responsetime text,
32 33 34 35
 service_facebook boolean,
 service_twitter boolean,
 service_tumblr boolean,
 service_wordpress boolean,
dmorley's avatar
dmorley committed
36
 service_xmpp boolean,
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,
40
 terms text,
dmorley's avatar
dmorley committed
41 42
 sslexpire timestamp,
 dnssec boolean,
43
 comment_counts int,
dmorley's avatar
dmorley committed
44
 weight int DEFAULT 50,
dmorley's avatar
dmorley committed
45 46 47
 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
48 49 50 51 52 53 54
);
CREATE TABLE rating_comments (
 id serial8 UNIQUE PRIMARY KEY,
 domain text NOT NULL,
 comment text,
 admin text, 
 pod_id int,
55 56
 rating int,
 username text,
David Morley's avatar
David Morley committed
57
 userurl text,
58
 date_created timestamp DEFAULT current_timestamp
59
);
dmorley's avatar
dmorley committed
60 61 62 63
CREATE TABLE apikeys (
 key text,
 email text,
 usage int,
dmorley's avatar
dmorley committed
64
 date_created timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
65 66 67 68 69 70
);

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

CREATE TABLE checks (
 domain text,
 online boolean,
 error text,
78
 ttl numeric(8,6),
David Morley's avatar
David Morley committed
79 80 81 82 83 84 85 86 87 88
 total_users int,
 local_posts int,
 comment_counts int,
 shortversion text,
 date_checked timestamp DEFAULT current_timestamp
);

CREATE TABLE masterversions (
 software text,
 version text,
dmorley's avatar
dmorley committed
89
 date_checked timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
90
);
dmorley's avatar
dmorley committed
91