db_schema.txt

DROP TABLE certificate_tree;
DROP TABLE resource_certificate;
DROP TABLE roa;
DROP TABLE manifest;
DROP TABLE crl;



CREATE TYPE roaprefix AS(
prefix  cidr,
maxlen  int8
);

CREATE OR REPLACE FUNCTION roaprefix(
    cidr,
    int8
) RETURNS roaprefix AS
$$
SELECT ($1,$2)::roaprefix;
$$
LANGUAGE 'sql' IMMUTABLE STRICT
COST 1;


CREATE OR REPLACE FUNCTION roaprefix_overlaps(inet, roaprefix)
RETURNS boolean AS
$$
SELECT ($1) >>= ($2).prefix OR ($1) <<= ($2).prefix;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION roaprefix_overlaps(roaprefix, inet)
RETURNS boolean AS
$$
SELECT ($2) >>= ($1).prefix OR ($2) <<= ($1).prefix;
$$
LANGUAGE 'sql' IMMUTABLE;


CREATE OPERATOR @> (
LEFTARG = roaprefix,
RIGHTARG = inet,
PROCEDURE = roaprefix_overlaps
);


CREATE OPERATOR <@ (
LEFTARG = inet,
RIGHTARG = roaprefix,
PROCEDURE = roaprefix_overlaps
);




CREATE TABLE certificate_tree (
    tree_name text NOT NULL,
    date timestamp NOT NULL,
    trust_anchor_name text PRIMARY KEY,
    UNIQUE (tree_name)
);
CREATE TABLE resource_certificate (
    certificate_name text PRIMARY KEY,
    subject text,
    serial_nr numeric,
    issuer text,
    subject_key_identifier text,
    authority_key_identifier text,
    public_key text,
    isEE boolean,
    isCA boolean,
    isRoot boolean,
    validity_period_start timestamp,
    validity_period_end timestamp,
    is_valid boolean,
    validation_errors text[],
    validation_warnings text[],
    prefixes cidr[],
    asns bigint[],
    asn_ranges int8range[],
    manifest text,
    crl text,
    parent_certificate text,
    certificate_tree text NOT NULL,
    id bigint,
    parent_id bigint,
    has_kids boolean
);
CREATE TABLE roa (
    roa_name text PRIMARY KEY,
    asn numeric,
    validity_period_start timestamp,
    validity_period_end timestamp,
    is_valid boolean,
    validation_errors text[],
    validation_warnings text[],
    signing_time timestamp,
    prefixes roaprefix[],
    parent_certificate text,
    certificate_tree text NOT NULL,
    id bigint,
    parent_id bigint
);
CREATE TABLE manifest (
    manifest_name text PRIMARY KEY,
    files text[],
    validity_period_start timestamp,
    validity_period_end timestamp,
    is_valid boolean,
    validation_errors text[],
    validation_warnings text[],
    parent_certificate text,
    certificate_tree text NOT NULL,
    id numeric,
    parent_id bigint
);
CREATE TABLE crl (
    crl_name text PRIMARY KEY,
    revoked_objects text[],
    validity_period_start timestamp,
    validity_period_end timestamp,
    is_valid boolean,
    validation_errors text[],
    validation_warnings text[],
    parent_certificate text,
    certificate_tree text NOT NULL,
    id numeric,
    parent_id bigint
);