<commit_before>--------------------------------------------
--
-- The basic table structure and similar
--
--------------------------------------------

COMMENT ON DATABASE nipap IS 'NIPAP database - schema version: 5';

CREATE EXTENSION IF NOT EXISTS ip4r;
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TYPE ip_net_plan_type AS ENUM ('reservation', 'assignment', 'host');
CREATE TYPE ip_net_plan_status AS ENUM ('assigned', 'reserved', 'quarantine');

CREATE TYPE priority_5step AS ENUM ('warning', 'low', 'medium', 'high', 'critical');


CREATE TABLE ip_net_asn (
	asn integer NOT NULL PRIMARY KEY,
	name text
);

COMMENT ON COLUMN ip_net_asn.asn IS 'AS Number';
COMMENT ON COLUMN ip_net_asn.name IS 'ASN name';

--
-- This is where we store VRFs
--
CREATE TABLE ip_net_vrf (
	id serial PRIMARY KEY,
	rt text,
	name text,
	description text,
	num_prefixes_v4 numeric(40) DEFAULT 0,
	num_prefixes_v6 numeric(40) DEFAULT 0,
	total_addresses_v4 numeric(40) DEFAULT 0,
	total_addresses_v6 numeric(40) DEFAULT 0,
	used_addresses_v4 numeric(40) DEFAULT 0,
	used_addresses_v6 numeric(40) DEFAULT 0,
	free_addresses_v4 numeric(40) DEFAULT 0,
	free_addresses_v6 numeric(40) DEFAULT 0,
	tags text[] DEFAULT '{}',
	avps hstore NOT NULL DEFAULT ''
);

--
-- A little hack to allow a single VRF with no VRF or name
--
CREATE UNIQUE INDEX ip_net_vrf__unique_vrf__index ON ip_net_vrf ((''::TEXT)) WHERE rt IS NULL;
CREATE UNIQUE INDEX ip_net_vrf__unique_name__index ON ip_net_vrf ((''::TEXT)) WHERE name IS NULL;
--
INSERT INTO ip_net_vrf (id, rt, name, description) VALUES (0, NULL, 'default', 'The default VRF, typically the Internet.');

CREATE UNIQUE INDEX ip_net_vrf__rt__index ON ip_net_vrf (rt) WHERE rt IS NOT NULL;
CREATE UNIQUE INDEX ip_net_vrf__name__index ON ip_net_vrf (name) WHERE name IS NOT NULL;

COMMENT ON TABLE ip_net_vrf IS 'IP Address VRFs';
COMMENT ON INDEX ip_net_vrf__rt__index IS 'VRF RT';
COMMENT ON INDEX ip_net_vrf__name__index IS 'VRF name';
COMMENT ON COLUMN ip_net_vrf.rt IS 'VRF RT';
COMMENT ON COLUMN ip_net_vrf.name IS 'VRF name';
COMMENT ON COLUMN ip_net_vrf.description IS 'VRF description';
COMMENT ON COLUMN ip_net_vrf.num_prefixes_v4 IS 'Number of IPv4 prefixes in this VRF';
COMMENT ON COLUMN ip_net_vrf.num_prefixes_v6 IS 'Number of IPv6 prefixes in this VRF';
COMMENT ON COLUMN ip_net_vrf.total_addresses_v4 IS 'Total number of IPv4 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.total_addresses_v6 IS 'Total number of IPv6 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.used_addresses_v4 IS 'Number of used IPv4 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.used_addresses_v6 IS 'Number of used IPv6 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.free_addresses_v4 IS 'Number of free IPv4 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.free_addresses_v6 IS 'Number of free IPv6 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.tags IS 'Tags associated with the VRF';



--
-- This table is used to store our pools. pools are the grouping of a number of
-- prefixes for a specific purpose and when you need a specific type of
-- address, ie a core loopback or similar, you'll just pick the right pool and
-- get an address assigned automatically.
--
CREATE TABLE ip_net_pool (
	id serial PRIMARY KEY,
	name text NOT NULL UNIQUE,
	description text,
	default_type ip_net_plan_type,
	ipv4_default_prefix_length integer,
	ipv6_default_prefix_length integer,
	member_prefixes_v4 numeric(40) DEFAULT 0,
	member_prefixes_v6 numeric(40) DEFAULT 0,
	used_prefixes_v4 numeric(40) DEFAULT 0,
	used_prefixes_v6 numeric(40) DEFAULT 0,
	total_addresses_v4 numeric(40) DEFAULT 0,
	total_addresses_v6 numeric(40) DEFAULT 0,
	used_addresses_v4 numeric(40) DEFAULT 0,
	used_addresses_v6 numeric(40) DEFAULT 0,
	free_addresses_v4 numeric(40) DEFAULT 0,
	free_addresses_v6 numeric(40) DEFAULT 0,
	free_prefixes_v4 numeric(40) DEFAULT NULL,
	free_prefixes_v6 numeric(40) DEFAULT NULL,
	total_prefixes_v4 numeric(40) DEFAULT NULL,
	total_prefixes_v6 numeric(40) DEFAULT NULL,
	tags text[] DEFAULT '{}',
	avps hstore NOT NULL DEFAULT ''
);
<commit_msg>Make pool and vrf names case-insensitive unique<commit_after>--------------------------------------------
--
-- The basic table structure and similar
--
--------------------------------------------

COMMENT ON DATABASE nipap IS 'NIPAP database - schema version: 5';

CREATE EXTENSION IF NOT EXISTS ip4r;
CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TYPE ip_net_plan_type AS ENUM ('reservation', 'assignment', 'host');
CREATE TYPE ip_net_plan_status AS ENUM ('assigned', 'reserved', 'quarantine');

CREATE TYPE priority_5step AS ENUM ('warning', 'low', 'medium', 'high', 'critical');


CREATE TABLE ip_net_asn (
	asn integer NOT NULL PRIMARY KEY,
	name text
);

COMMENT ON COLUMN ip_net_asn.asn IS 'AS Number';
COMMENT ON COLUMN ip_net_asn.name IS 'ASN name';

--
-- This is where we store VRFs
--
CREATE TABLE ip_net_vrf (
	id serial PRIMARY KEY,
	rt text,
	name text NOT NULL,
	description text,
	num_prefixes_v4 numeric(40) DEFAULT 0,
	num_prefixes_v6 numeric(40) DEFAULT 0,
	total_addresses_v4 numeric(40) DEFAULT 0,
	total_addresses_v6 numeric(40) DEFAULT 0,
	used_addresses_v4 numeric(40) DEFAULT 0,
	used_addresses_v6 numeric(40) DEFAULT 0,
	free_addresses_v4 numeric(40) DEFAULT 0,
	free_addresses_v6 numeric(40) DEFAULT 0,
	tags text[] DEFAULT '{}',
	avps hstore NOT NULL DEFAULT ''
);

--
-- A little hack to allow a single VRF with no VRF or name
--
CREATE UNIQUE INDEX ip_net_vrf__unique_vrf__index ON ip_net_vrf ((''::TEXT)) WHERE rt IS NULL;
CREATE UNIQUE INDEX ip_net_vrf__unique_name__index ON ip_net_vrf ((''::TEXT)) WHERE name IS NULL;
--
INSERT INTO ip_net_vrf (id, rt, name, description) VALUES (0, NULL, 'default', 'The default VRF, typically the Internet.');

CREATE UNIQUE INDEX ip_net_vrf__rt__index ON ip_net_vrf (rt) WHERE rt IS NOT NULL;
CREATE UNIQUE INDEX ip_net_vrf__name__index ON ip_net_vrf (lower(name)) WHERE name IS NOT NULL;

COMMENT ON TABLE ip_net_vrf IS 'IP Address VRFs';
COMMENT ON INDEX ip_net_vrf__rt__index IS 'VRF RT';
COMMENT ON INDEX ip_net_vrf__name__index IS 'VRF name';
COMMENT ON COLUMN ip_net_vrf.rt IS 'VRF RT';
COMMENT ON COLUMN ip_net_vrf.name IS 'VRF name';
COMMENT ON COLUMN ip_net_vrf.description IS 'VRF description';
COMMENT ON COLUMN ip_net_vrf.num_prefixes_v4 IS 'Number of IPv4 prefixes in this VRF';
COMMENT ON COLUMN ip_net_vrf.num_prefixes_v6 IS 'Number of IPv6 prefixes in this VRF';
COMMENT ON COLUMN ip_net_vrf.total_addresses_v4 IS 'Total number of IPv4 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.total_addresses_v6 IS 'Total number of IPv6 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.used_addresses_v4 IS 'Number of used IPv4 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.used_addresses_v6 IS 'Number of used IPv6 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.free_addresses_v4 IS 'Number of free IPv4 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.free_addresses_v6 IS 'Number of free IPv6 addresses in this VRF';
COMMENT ON COLUMN ip_net_vrf.tags IS 'Tags associated with the VRF';



--
-- This table is used to store our pools. pools are the grouping of a number of
-- prefixes for a specific purpose and when you need a specific type of
-- address, ie a core loopback or similar, you'll just pick the right pool and
-- get an address assigned automatically.
--
CREATE TABLE ip_net_pool (
	id serial PRIMARY KEY,
	name text NOT NULL,
	description text,
	default_type ip_net_plan_type,
	ipv4_default_prefix_length integer,
	ipv6_default_prefix_length integer,
	member_prefixes_v4 numeric(40) DEFAULT 0,
	member_prefixes_v6 numeric(40) DEFAULT 0,
	used_prefixes_v4 numeric(40) DEFAULT 0,
	used_prefixes_v6 numeric(40) DEFAULT 0,
	total_addresses_v4 numeric(40) DEFAULT 0,
	total_addresses_v6 numeric(40) DEFAULT 0,
	used_addresses_v4 numeric(40) DEFAULT 0,
	used_addresses_v6 numeric(40) DEFAULT 0,
	free_addresses_v4 numeric(40) DEFAULT 0,
	free_addresses_v6 numeric(40) DEFAULT 0,
	free_prefixes_v4 numeric(40) DEFAULT NULL,
	free_prefixes_v6 numeric(40) DEFAULT NULL,
	total_prefixes_v4 numeric(40) DEFAULT NULL,
	total_prefixes_v6 numeric(40) DEFAULT NULL,
	tags text[] DEFAULT '{}',
	avps hstore NOT NULL DEFAULT ''
);

CREATE UNIQUE INDEX ON ip_net_pool__name__index ON (lower(name));

{'fraction_non_alphanumeric': 0.09351651000241022, 'fraction_numerical': 0.02554832489756568, 'mean_word_length': 4.695950583390529, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3705574', 'n_tokens_mistral': 3038, 'n_tokens_neox': 2889, 'n_words': 1018}