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
 score int DEFAULT 50,
 weightedscore numeric(5,2) DEFAULT 0,
 userrating decimal DEFAULT 0,
12
 ip text,
13
 detectedlanguage text,
14
 country text,
15
 countryname 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
 ipv6 boolean,
David Morley's avatar
David Morley committed
22 23
 sslvalid text,
 monthsmonitored int,
24
 daysmonitored 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),
31
 status smallint DEFAULT 1,
dmorley's avatar
dmorley committed
32
 latency smallint,
dmorley's avatar
dmorley committed
33
 service_xmpp boolean,
David Morley's avatar
David Morley committed
34
 services jsonb,
35
 token text,
David Morley's avatar
David Morley committed
36
 publickey text,
37
 tokenexpire timestamp,
David Morley's avatar
David Morley committed
38
 podmin_statement text,
39
 podmin_notify boolean,
40
 podmin_notify_level int DEFAULT 50,
dmorley's avatar
dmorley committed
41 42
 sslexpire timestamp,
 dnssec boolean,
43
 comment_counts int,
44
 weight int DEFAULT 10,
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
);
David Morley's avatar
David Morley committed
49

50
CREATE TABLE ratingcomments (
David Morley's avatar
David Morley committed
51 52 53 54
 id serial8 UNIQUE PRIMARY KEY,
 domain text NOT NULL,
 comment 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
);
David Morley's avatar
David Morley committed
60

dmorley's avatar
dmorley committed
61
CREATE TABLE apikeys (
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 (
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 (
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
 total_users int,
 local_posts int,
 comment_counts int,
 shortversion text,
David Morley's avatar
David Morley committed
87
 version text,
David Morley's avatar
David Morley committed
88 89 90 91
 date_checked timestamp DEFAULT current_timestamp
);

CREATE TABLE masterversions (
92
 id serial8 UNIQUE PRIMARY KEY,
David Morley's avatar
David Morley committed
93 94
 software text,
 version text,
95 96
 devlastcommit timestamp,
 releasedate timestamp,
dmorley's avatar
dmorley committed
97
 date_checked timestamp DEFAULT current_timestamp
dmorley's avatar
dmorley committed
98
);
David Morley's avatar
David Morley 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
);