tables.sql 2.21 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
 stats_apikey text,
9 10 11 12
 score int DEFAULT 50,
 weightedscore numeric(5,2) DEFAULT 0,
 adminrating decimal DEFAULT 0,
 userrating decimal DEFAULT 0,
dmorley's avatar
dmorley committed
13
 hidden boolean DEFAULT true,
14
 ip text,
15
 country text,
16 17 18 19
 city text,
 state text, 
 lat text,
 long text,
David Morley's avatar
David Morley committed
20
 email text,
dmorley's avatar
dmorley committed
21 22
 ipv6 boolean,
 secure boolean,
David Morley's avatar
David Morley committed
23 24
 sslvalid text,
 monthsmonitored int,
dmorley's avatar
dmorley committed
25
 signup boolean,
26 27 28 29
 total_users int, 
 active_users_halfyear int,
 active_users_monthly int,
 local_posts int,
dmorley's avatar
dmorley committed
30
 uptime_alltime numeric(5,2),
dmorley's avatar
dmorley committed
31
 status smallint,
dmorley's avatar
latent  
dmorley committed
32
 latency smallint,
33 34 35 36
 service_facebook boolean,
 service_twitter boolean,
 service_tumblr boolean,
 service_wordpress boolean,
dmorley's avatar
dmorley committed
37
 service_xmpp boolean,
dmorley's avatar
dmorley committed
38
 token text,
David Morley's avatar
David Morley committed
39
 publickey text,
dmorley's avatar
dmorley committed
40
 tokenexpire timestamp,
David Morley's avatar
David Morley committed
41
 podmin_statement text,
42
 podmin_notify boolean,
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

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

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

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

CREATE TABLE checks (
noplanman's avatar
noplanman committed
81
 id serial8 UNIQUE PRIMARY KEY,
dmorley's avatar
dmorley committed
82 83 84
 domain text,
 online boolean,
 error text,
dmorley's avatar
latent  
dmorley committed
85
 latency numeric(8,6),
David Morley's avatar
David Morley committed
86 87 88 89 90 91 92 93
 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
94
 id serial8 UNIQUE PRIMARY KEY,
David Morley's avatar
David Morley committed
95 96
 software text,
 version text,
dmorley's avatar
dmorley committed
97
 date_checked timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
98
);
dmorley's avatar
dmorley committed
99 100 101 102 103 104 105 106 107 108

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
);