Select Git revision
create_schema_v3.sql
-
Radko Krkoš authored
* Previously some setup was included in the create_schema scripts, some documented in the cookbook. Most have now been moved to setup_db_production.sql script, which sets up access rights for various accounts (pdns_rest_api, pdnsd, pgbgcalc) and assigns details tables to their dedicated tablespace.
Radko Krkoš authored* Previously some setup was included in the create_schema scripts, some documented in the cookbook. Most have now been moved to setup_db_production.sql script, which sets up access rights for various accounts (pdns_rest_api, pdnsd, pgbgcalc) and assigns details tables to their dedicated tablespace.
create_schema_v3.sql 5.94 KiB
--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),