tables.sql 1.65 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
 sslvalid text,
dmorley's avatar
dmorley committed
23
 statsurl text,
David Morley's avatar
David Morley committed
24
 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 31
 uptime_alltime numeric(5,2),
 uptime_custom text,
David Morley's avatar
David Morley committed
32
 status text,
dmorley's avatar
dmorley committed
33
 responsetime text,
34 35 36 37
 service_facebook boolean,
 service_twitter boolean,
 service_tumblr boolean,
 service_wordpress boolean,
dmorley's avatar
dmorley committed
38
 service_xmpp boolean,
dmorley's avatar
dmorley committed
39 40
 token text,
 tokenexpire timestamp,
dmorley's avatar
dmorley committed
41
 terms text DEFAULT '/terms',
dmorley's avatar
dmorley committed
42 43
 sslexpire timestamp,
 dnssec boolean,
44
 comment_counts int,
dmorley's avatar
dmorley committed
45
 weight int DEFAULT 50,
David Morley's avatar
David Morley committed
46 47 48
 dateUpdated timestamp DEFAULT current_timestamp,
 dateLaststats timestamp DEFAULT current_timestamp,
 dateCreated 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 59
 userurl text,
 date timestamp DEFAULT current_timestamp
60
);
dmorley's avatar
dmorley committed
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
CREATE TABLE apikeys (
 key text,
 email text,
 usage int,
 dateCreated timestamp DEFAULT current_timestamp
);

CREATE TABLE clicks (
 domain text,
 manualclick int,
 autoclick int,
 dateClicked timestamp DEFAULT current_timestamp
);

CREATE TABLE checks (
 domain text,
 online boolean,
 error text,
 dateChecked timestamp DEFAULT current_timestamp
);
dmorley's avatar
dmorley committed
81