--DROP MATERIALIZED VIEW IF EXISTS count_stats; --DROP VIEW IF EXISTS __count_stats; DROP TABLE IF EXISTS texts3_details; DROP TABLE IF EXISTS texts3; DROP TABLE IF EXISTS ips3_details; DROP TABLE IF EXISTS ips3; DROP TABLE IF EXISTS negatives3_details; DROP TABLE IF EXISTS negatives3; --DROP TABLE IF EXISTS negative_types; --DROP TABLE IF EXISTS rec_types; --DROP TABLE IF EXISTS sources; --DROP TABLE IF EXISTS tokens; --DROP TABLE IF EXISTS users; --DROP TABLE IF EXISTS blacklist_domain; --DROP EXTENSION IF EXISTS pg_trgm; CREATE EXTENSION IF NOT EXISTS pg_trgm; --CREATE TABLE blacklist_domain ( -- pattern text NOT NULL PRIMARY KEY --); --CREATE TABLE users ( -- id serial UNIQUE, -- name text NOT NULL PRIMARY KEY, -- fullname text NOT NULL, -- valid boolean NOT NULL DEFAULT TRUE --); --CREATE TABLE tokens ( -- userid integer NOT NULL REFERENCES users(id), -- tokenname text NOT NULL, -- created timestamp NOT NULL DEFAULT NOW(), -- token text NOT NULL UNIQUE DEFAULT MD5(RANDOM()::text), -- PRIMARY KEY (userid, tokenname) --); --CREATE TABLE rec_types ( -- id integer NOT NULL PRIMARY KEY CHECK (id > 0 AND id < 65536), -- rtype text NOT NULL --); --INSERT INTO rec_types VALUES -- (1, 'A'), -- (2, 'NS'), -- (5, 'CNAME'), -- (12, 'PTR'), -- (15, 'MX'), -- (28, 'AAAA') --; --DROP TYPE IF EXISTS source_import_type; --CREATE TYPE source_import_type AS ENUM ('dnscap', 'json'); --CREATE TABLE sources ( -- id smallint NOT NULL PRIMARY KEY, -- source text NOT NULL, -- import_type source_import_type, -- credibility boolean NOT NULL, -- Source credibility: 0: untrusted / 1: trusted (performs full recursion) -- control boolean NOT NULL, -- Source control: 0: third-party / 1: own -- acquisition boolean NOT NULL, -- Data acquisition method: 0: recursion / 1: traffic analysis -- cache boolean NOT NULL -- Caching mechanism: 0: no cache / 1: with cache --); CREATE TABLE texts3 ( lvalue text NOT NULL, rvalue text NOT NULL, rtype integer NOT NULL REFERENCES rec_types(id) CHECK (rtype NOT IN (1, 28)), count integer NOT NULL CHECK (count > 0), time_first timestamp NOT NULL, time_last timestamp NOT NULL CHECK (time_last >= time_first), id bigserial UNIQUE, PRIMARY KEY (lvalue, rtype, rvalue) INCLUDE (id) ); CREATE INDEX "texts3_lvalue_idx" ON texts3 USING btree (lvalue); CREATE INDEX "texts3_rvalue_idx" ON texts3 USING btree (rvalue); CREATE INDEX "texts3_lvalue_trgm_idx" ON texts3 USING gin (lvalue gin_trgm_ops); CREATE INDEX "texts3_rvalue_trgm_idx" ON texts3 USING gin (rvalue gin_trgm_ops); CREATE TABLE texts3_details ( id bigint NOT NULL REFERENCES texts3(id) ON DELETE CASCADE, source smallint NOT NULL REFERENCES sources(id), time timestamp NOT NULL, ttl integer NOT NULL, transactionid bytea NOT NULL, x20randomness bytea, authenticated_authoritative bit(2) NOT NULL, rem jsonb, PRIMARY KEY (id, source, time) ); CREATE TABLE ips3 ( domain text NOT NULL, ip inet NOT NULL, rtype integer NOT NULL REFERENCES rec_types(id) CHECK (rtype IN (1, 28)), count integer NOT NULL CHECK (count > 0), time_first timestamp NOT NULL, time_last timestamp NOT NULL CHECK (time_last >= time_first), id bigserial UNIQUE, PRIMARY KEY (ip, rtype, domain) INCLUDE (id) ); CREATE INDEX "ips3_domain_idx" ON ips3 USING btree (domain); CREATE INDEX "ips3_domain_trgm_idx" ON ips3 USING gin (domain gin_trgm_ops); CREATE TABLE ips3_details ( id bigint NOT NULL REFERENCES ips3(id) ON DELETE CASCADE, source smallint NOT NULL REFERENCES sources(id), time timestamp NOT NULL, ttl integer NOT NULL, transactionid bytea NOT NULL, x20randomness bytea, authenticated_authoritative bit(2) NOT NULL, rem jsonb, PRIMARY KEY (id, source, time) ); --CREATE TABLE negative_types ( -- id smallint NOT NULL PRIMARY KEY, -- ntype text NOT NULL --); --INSERT INTO negative_types VALUES -- (0, 'NODATA'), -- (1, 'Format error'), -- (2, 'Server failure'), -- (3, 'NXDOMAIN'), -- (4, 'Not implemented'), -- (5, 'Refused') --; CREATE TABLE negatives3 ( qname text NOT NULL, rtype integer NOT NULL REFERENCES rec_types(id), count integer NOT NULL CHECK (count > 0), time_first timestamp NOT NULL, time_last timestamp NOT NULL CHECK (time_last >= time_first), id bigserial UNIQUE, PRIMARY KEY (qname, rtype) INCLUDE (id) ); CREATE INDEX "negatives3_qname_idx" ON negatives3 USING btree (qname); CREATE INDEX "negatives3_qname_trgm_idx" ON negatives3 USING gin (qname gin_trgm_ops); CREATE TABLE negatives3_details ( id bigint REFERENCES negatives3(id) ON DELETE CASCADE, source smallint NOT NULL REFERENCES sources(id), time timestamp NOT NULL, reply smallint NOT NULL REFERENCES negative_types(id), PRIMARY KEY (id, source, time, reply) ); --CREATE VIEW __count_stats AS ( --SELECT *, ("A"+"AAAA"+"NS"+"CNAME"+"PTR"+"MX"+"NODATA"+"NXDOMAIN"+"Format error"+"Server failure"+"Not implemented"+"Refused") AS "Total" FROM (SELECT date(time) AS day, COUNT(*) FILTER (WHERE rtype=1) AS "A", COUNT(*) FILTER (WHERE rtype=28) AS "AAAA" FROM ips3_details INNER JOIN ips3 USING (id) GROUP BY day) AS ip FULL JOIN (SELECT date(time) AS day, COUNT(*) FILTER (WHERE rtype=2) AS "NS", COUNT(*) FILTER (WHERE rtype=5) AS "CNAME", COUNT(*) FILTER (WHERE rtype=12) AS "PTR", COUNT(*) FILTER (WHERE rtype=15) AS "MX" FROM texts3_details INNER JOIN texts3 USING (id) GROUP BY day) AS text USING (day) FULL JOIN (SELECT date(time) AS day, COUNT(*) FILTER (WHERE reply=0) AS "NODATA", COUNT(*) FILTER (WHERE reply=3) AS "NXDOMAIN", COUNT(*) FILTER (WHERE reply=1) AS "Format error", COUNT(*) FILTER (WHERE reply=2) AS "Server failure", COUNT(*) FILTER (WHERE reply=4) AS "Not implemented", COUNT(*) FILTER (WHERE reply=5) AS "Refused" FROM negatives3_details INNER JOIN negatives3 USING (id) GROUP BY day) AS negative USING (day) WHERE day < date(NOW()) AND day > date(NOW()) - INTERVAL '3 mons' ORDER BY day DESC --);