tables.sql 1.99 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,
8 9 10 11
 score int DEFAULT 50,
 weightedscore numeric(5,2) DEFAULT 0,
 adminrating decimal DEFAULT 0,
 userrating decimal DEFAULT 0,
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),
dmorley's avatar
dmorley committed
30
 status smallint,
dmorley's avatar
dmorley committed
31
 latency smallint,
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,
37
 token text,
David Morley's avatar
David Morley committed
38
 publickey text,
39
 tokenexpire timestamp,
David Morley's avatar
David Morley committed
40
 podmin_statement text,
41
 podmin_notify boolean,
dmorley's avatar
dmorley committed
42 43
 sslexpire timestamp,
 dnssec boolean,
44
 comment_counts int,
45
 weight int DEFAULT 10,
dmorley's avatar
dmorley committed
46 47 48
 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
49 50 51 52 53 54 55
);
CREATE TABLE rating_comments (
 id serial8 UNIQUE PRIMARY KEY,
 domain text NOT NULL,
 comment text,
 admin text, 
 pod_id int,
56 57
 rating int,
 username text,
David Morley's avatar
David Morley committed
58
 userurl text,
59
 date_created timestamp DEFAULT current_timestamp
60
);
dmorley's avatar
dmorley committed
61
CREATE TABLE apikeys (
noplanman's avatar
noplanman committed
62
 id serial8 UNIQUE PRIMARY KEY,
dmorley's avatar
dmorley committed
63 64 65
 key text,
 email text,
 usage int,
dmorley's avatar
dmorley committed
66
 date_created timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
67 68 69
);

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

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

CREATE TABLE masterversions (
noplanman's avatar
noplanman committed
91
 id serial8 UNIQUE PRIMARY KEY,
David Morley's avatar
David Morley committed
92 93
 software text,
 version text,
dmorley's avatar
dmorley committed
94
 date_checked timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
95
);