DROP TABLE DNS_Packets;
DROP TABLE PCAP_files;
DROP TYPE protocols;

CREATE TYPE protocols AS ENUM ('TCP', 'UDP');

CREATE TABLE Pcap_files(id SERIAL UNIQUE NOT NULL, 
       added TIMESTAMP NOT NULL DEFAULT now(), 
       filename TEXT NOT NULL, datalinktype TEXT, snaplength INTEGER, 
       filesize INTEGER, filedate TIMESTAMP,
       totalpackets INTEGER, storedpackets INTEGER);	

CREATE TABLE DNS_Packets (id SERIAL UNIQUE NOT NULL, 
       file INTEGER NOT NULL REFERENCES Pcap_files(id),
       rank INTEGER NOT NULL, -- Rank of the packet in the file
       date TIMESTAMP, -- Date of capture in UTC
       length INTEGER NOT NULL, -- Length on the cable, we may have stored 
                                -- less bytes
       added TIMESTAMP NOT NULL DEFAULT now(),
       src_address INET NOT NULL,
       dst_address INET  NOT NULL,
       protocol protocols  NOT NULL,     
       src_port INTEGER  NOT NULL,
       dst_port INTEGER  NOT NULL,
       -- Field names and semantic are found in RFC 1034 and 1035. We do not 
       -- try to be user-friendly
       query BOOLEAN   NOT NULL,
       query_id INTEGER   NOT NULL,
       opcode INTEGER   NOT NULL,
       rcode INTEGER  NOT NULL, 
       aa BOOLEAN  NOT NULL,
       tc BOOLEAN  NOT NULL,
       rd BOOLEAN  NOT NULL,
       ra BOOLEAN  NOT NULL,
       qname TEXT  NOT NULL,
       qtype INTEGER  NOT NULL, -- With helper functions TODO to translate numeric values to well-known text like AAAA, MX, etc
       edns0_size INTEGER, -- NULL if no EDNS0
       do_dnssec BOOLEAN, -- NULL if no EDNS0
       ancount INTEGER  NOT NULL,
       nscount INTEGER  NOT NULL,
       arcount INTEGER  NOT NULL
       );

-- Examples of requests: 
--
-- 1) to find the NXDOMAIN (rcode 3) responses:
-- SELECT DISTINCT substr(qname, 1, 40) AS domain,count(qname) AS num FROM DNS_packets WHERE NOT query AND rcode= 3 GROUP BY qname ORDER BY count(qname) DESC;
--
-- 2) to find the most talkative IPv6 clients
-- SELECT src_address,count(src_address) AS requests FROM DNS_packets WHERE family(src_address)=6 AND query GROUP BY src_address ORDER BY requests DESC;
--
-- 3) to find the typical EDNS0 sizes advertised by clients
-- SELECT edns0_size, count(edns0_size) AS occurrences FROM DNS_packets WHERE query GROUP BY edns0_size ORDER BY occurrences DESC;