tables.sql 1.5 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,
David Morley's avatar
David Morley committed
6
 score int DEFAULT 10,
dmorley's avatar
dmorley committed
7
 weightedscore numeric(5,2) DEFAULT 10,
8 9
 adminrating decimal DEFAULT 10,
 userrating decimal DEFAULT 10,
David Morley's avatar
David Morley committed
10
 whois text,
11
 hidden text DEFAULT 'yes',
12
 ip text,
David Morley's avatar
David Morley committed
13
 ptr text,
14
 country text,
15 16 17 18 19 20
 city text,
 state text, 
 lat text,
 long text,
 connection text,
 postalcode text,
David Morley's avatar
David Morley committed
21
 email text,
22
 ipv6 text,
David Morley's avatar
David Morley committed
23 24 25 26 27 28
 secure text,
 sslvalid text,
 Hgitdate text,
 Hgitref text,
 Hruntime text,
 Hencoding text,
29 30
 pingdomurl text,
 pingdomlast text,
David Morley's avatar
David Morley committed
31
 monthsmonitored int,
root's avatar
root committed
32
 signup int,
33 34 35 36
 total_users int, 
 active_users_halfyear int,
 active_users_monthly int,
 local_posts int,
37
 uptimelast7 numeric(5,2),
David Morley's avatar
David Morley committed
38 39
 status text,
 responsetimelast7 text,
40 41 42 43
 service_facebook booleen,
 service_twitter booleen,
 service_tumblr booleen,
 service_wordpress booleen,
dmorley's avatar
dmorley committed
44 45
 token text,
 tokenexpire timestamp,
46
 comment_counts int,
dmorley's avatar
dmorley committed
47
 weight int DEFAULT 10,
48
 xmpp booleen,
David Morley's avatar
David Morley committed
49 50 51
 dateUpdated timestamp DEFAULT current_timestamp,
 dateLaststats timestamp DEFAULT current_timestamp,
 dateCreated timestamp DEFAULT current_timestamp
David Morley's avatar
David Morley committed
52 53 54 55 56 57 58
);
CREATE TABLE rating_comments (
 id serial8 UNIQUE PRIMARY KEY,
 domain text NOT NULL,
 comment text,
 admin text, 
 pod_id int,
59 60
 rating int,
 username text,
David Morley's avatar
David Morley committed
61 62
 userurl text,
 date timestamp DEFAULT current_timestamp
63 64 65 66 67 68 69 70 71
);
CREATE TABLE users (
 id serial8 UNIQUE PRIMARY KEY,
 admin int NOT NULL,
 username text UNIQUE NOT NULL,
 password text NOT NULL,
 userurl text,
 userpod text,
 dateCreated timestamp DEFAULT current_timestamp
72
);