| /* |
| |
| Licensed under the Apache License, Version 2.0 (the "License"); |
| you may not use this file except in compliance with the License. |
| You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, software |
| distributed under the License is distributed on an "AS IS" BASIS, |
| WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| See the License for the specific language governing permissions and |
| limitations under the License. |
| */ |
| |
| -- |
| -- PostgreSQL database dump |
| -- |
| |
| -- Dumped from database version 9.5.4 |
| -- Dumped by pg_dump version 9.5.5 |
| |
| SET statement_timeout = 0; |
| SET lock_timeout = 0; |
| SET client_encoding = 'UTF8'; |
| SET standard_conforming_strings = on; |
| SET check_function_bodies = false; |
| SET client_min_messages = warning; |
| SET row_security = off; |
| |
| -- |
| -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: |
| -- |
| |
| CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; |
| |
| |
| SET search_path = public, pg_catalog; |
| |
| |
| -- |
| -- Name: on_update_current_timestamp_last_updated(); Type: FUNCTION; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE OR REPLACE FUNCTION on_update_current_timestamp_last_updated() RETURNS trigger |
| LANGUAGE plpgsql |
| AS $$ |
| BEGIN |
| NEW.last_updated = now(); |
| RETURN NEW; |
| END; |
| $$; |
| |
| ALTER FUNCTION public.on_update_current_timestamp_last_updated() OWNER TO traffic_ops; |
| |
| -- |
| -- Name: before_server_table(); Type: FUNCTION; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE OR REPLACE FUNCTION before_server_table() |
| RETURNS TRIGGER AS |
| $$ |
| DECLARE |
| server_count BIGINT; |
| BEGIN |
| WITH server_ips AS ( |
| SELECT s.id, i.name, ip.address, s.profile |
| FROM server s |
| JOIN interface i on i.server = s.ID |
| JOIN ip_address ip on ip.Server = s.ID and ip.interface = i.name |
| WHERE i.monitor = true |
| ) |
| SELECT count(*) |
| INTO server_count |
| FROM server_ips sip |
| JOIN server_ips sip2 on sip.id <> sip2.id |
| WHERE sip.id = NEW.id |
| AND sip2.address = sip.address |
| AND sip2.profile = sip.profile; |
| |
| IF server_count > 0 THEN |
| RAISE EXCEPTION 'Server [id:%] does not have a unique ip_address over the profile [id:%], [%] conflicts', |
| NEW.id, |
| NEW.profile, |
| server_count; |
| END IF; |
| RETURN NEW; |
| END; |
| $$ LANGUAGE plpgsql; |
| ALTER FUNCTION public.before_server_table() OWNER TO traffic_ops; |
| |
| -- |
| -- Name: before_ip_address_table(); Type: FUNCTION; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE OR REPLACE FUNCTION before_ip_address_table() |
| RETURNS TRIGGER |
| AS |
| $$ |
| DECLARE |
| server_count BIGINT; |
| server_id BIGINT; |
| server_profile BIGINT; |
| BEGIN |
| WITH server_ips AS ( |
| SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server |
| FROM server s |
| JOIN interface i |
| on i.server = s.ID |
| JOIN ip_address ip |
| on ip.Server = s.ID and ip.interface = i.name |
| WHERE ip.service_address = true |
| ) |
| SELECT count(distinct(sip.sid)), sip.sid, sip.profile |
| INTO server_count, server_id, server_profile |
| FROM server_ips sip |
| WHERE (sip.server <> NEW.server AND (SELECT host(sip.address)) = (SELECT host(NEW.address)) AND sip.profile = (SELECT profile from server s WHERE s.id = NEW.server)) |
| GROUP BY sip.sid, sip.profile; |
| |
| IF server_count > 0 THEN |
| RAISE EXCEPTION 'ip_address is not unique across the server [id:%] profile [id:%], [%] conflicts', |
| server_id, |
| server_profile, |
| server_count; |
| END IF; |
| RETURN NEW; |
| END; |
| $$ LANGUAGE PLPGSQL; |
| ALTER FUNCTION public.before_ip_address_table() OWNER TO traffic_ops; |
| |
| -- |
| -- Name: on_delete_current_timestamp_last_updated(); Type: FUNCTION; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE OR REPLACE FUNCTION public.on_delete_current_timestamp_last_updated() |
| RETURNS trigger |
| AS $$ |
| BEGIN |
| update last_deleted set last_updated = now() where table_name = TG_ARGV[0]; |
| RETURN NEW; |
| END; |
| $$ |
| LANGUAGE plpgsql; |
| |
| ALTER FUNCTION public.on_delete_current_timestamp_last_updated() OWNER TO traffic_ops; |
| |
| -- |
| -- Name: update_ds_timestamp_on_insert(); Type: FUNCTION; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE OR REPLACE FUNCTION update_ds_timestamp_on_insert() |
| RETURNS trigger |
| AS $$ |
| BEGIN |
| UPDATE deliveryservice |
| SET last_updated=now() |
| WHERE id IN ( |
| SELECT deliveryservice |
| FROM CAST(NEW AS deliveryservice_tls_version) |
| ); |
| RETURN NULL; |
| END; |
| $$ LANGUAGE plpgsql; |
| |
| -- |
| -- Name: update_ds_timestamp_on_delete(); Type: FUNCTION; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE OR REPLACE FUNCTION update_ds_timestamp_on_delete() |
| RETURNS trigger |
| AS $$ |
| BEGIN |
| UPDATE deliveryservice |
| SET last_updated=now() |
| WHERE id IN ( |
| SELECT deliveryservice |
| FROM CAST(OLD AS deliveryservice_tls_version) |
| ); |
| RETURN NULL; |
| END; |
| $$ LANGUAGE plpgsql; |
| |
| SET default_tablespace = ''; |
| |
| SET default_with_oids = false; |
| |
| DO $$ |
| BEGIN |
| IF NOT EXISTS (SELECT FROM pg_type WHERE typname = 'change_types') THEN |
| -- |
| -- Name: change_types; Type: TYPE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TYPE change_types AS ENUM ( |
| 'create', |
| 'update', |
| 'delete' |
| ); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM pg_type WHERE typname = 'deep_caching_type') THEN |
| -- |
| -- Name: deep_caching_type; Type: TYPE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TYPE deep_caching_type AS ENUM ( |
| 'NEVER', |
| 'ALWAYS' |
| ); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM pg_type WHERE typname = 'http_method_t') THEN |
| -- |
| -- Name: http_method_t; Type: TYPE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TYPE http_method_t AS ENUM ( |
| 'GET', |
| 'POST', |
| 'PUT', |
| 'PATCH', |
| 'DELETE' |
| ); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM pg_type WHERE typname = 'origin_protocol') THEN |
| -- |
| -- Name: localization_method; Type: TYPE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TYPE localization_method AS ENUM ( |
| 'CZ', |
| 'DEEP_CZ', |
| 'GEO' |
| ); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM pg_type WHERE typname = 'origin_protocol') THEN |
| -- |
| -- Name: origin_protocol; Type: TYPE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TYPE origin_protocol AS ENUM ( |
| 'http', |
| 'https' |
| ); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM pg_type WHERE typname = 'profile_type') THEN |
| -- |
| -- Name: profile_type; Type: TYPE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TYPE profile_type AS ENUM ( |
| 'ATS_PROFILE', |
| 'TR_PROFILE', |
| 'TM_PROFILE', |
| 'TS_PROFILE', |
| 'TP_PROFILE', |
| 'INFLUXDB_PROFILE', |
| 'RIAK_PROFILE', |
| 'SPLUNK_PROFILE', |
| 'DS_PROFILE', |
| 'ORG_PROFILE', |
| 'KAFKA_PROFILE', |
| 'LOGSTASH_PROFILE', |
| 'ES_PROFILE', |
| 'UNK_PROFILE', |
| 'GROVE_PROFILE' |
| ); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM pg_type WHERE typname = 'workflow_states') THEN |
| -- |
| -- Name: workflow_states; Type: TYPE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TYPE workflow_states AS ENUM ( |
| 'draft', |
| 'submitted', |
| 'rejected', |
| 'pending', |
| 'complete' |
| ); |
| END IF; |
| |
| IF NOT EXISTS(SELECT FROM pg_type WHERE typname = 'server_ip_address') THEN |
| -- |
| -- Name: server_ip_address; Type: TYPE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TYPE server_ip_address AS (address inet, gateway inet, service_address boolean); |
| END IF; |
| |
| IF NOT EXISTS(SELECT FROM pg_type WHERE typname = 'server_interface') THEN |
| -- |
| -- Name: server_interface; Type: TYPE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TYPE server_interface AS (ip_addresses server_ip_address ARRAY, max_bandwidth bigint, monitor boolean, mtu bigint, name text); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM pg_type WHERE typname = 'deliveryservice_signature_type') THEN |
| -- |
| -- Name: deliveryservice_signature_type; Type: DOMAIN; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE DOMAIN deliveryservice_signature_type AS text CHECK (VALUE IN ('url_sig', 'uri_signing')); |
| END IF; |
| END$$; |
| |
| -- |
| -- Name: acme_account; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS acme_account ( |
| email text NOT NULL, |
| private_key text NOT NULL, |
| provider text NOT NULL, |
| uri text NOT NULL, |
| PRIMARY KEY (email, provider) |
| ); |
| |
| -- |
| -- Name: api_capability; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS api_capability ( |
| id bigserial PRIMARY KEY, |
| http_method http_method_t NOT NULL, |
| route text NOT NULL, |
| capability text NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| UNIQUE (http_method, route, capability) |
| ); |
| |
| ALTER TABLE api_capability OWNER TO traffic_ops; |
| |
| -- |
| -- Name: asn; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS asn ( |
| id bigint NOT NULL, |
| asn bigint NOT NULL, |
| cachegroup bigint DEFAULT '0'::bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89468_primary PRIMARY KEY (id, cachegroup) |
| ); |
| |
| |
| ALTER TABLE asn OWNER TO traffic_ops; |
| |
| -- |
| -- Name: asn_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS asn_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE asn_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: asn_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE asn_id_seq OWNED BY asn.id; |
| |
| -- |
| -- Name: async_status; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS async_status ( |
| id bigint NOT NULL, |
| status TEXT NOT NULL, |
| message TEXT, |
| start_time timestamp with time zone DEFAULT now() NOT NULL, |
| end_time timestamp with time zone, |
| |
| CONSTRAINT async_status_pkey PRIMARY KEY (id) |
| ); |
| |
| ALTER TABLE async_status OWNER TO traffic_ops; |
| |
| -- |
| -- Name: async_status_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS async_status_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE async_status_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: async_status_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE async_status_id_seq OWNED BY async_status.id; |
| |
| -- |
| -- Name: cachegroup; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS cachegroup ( |
| id bigint, |
| name text NOT NULL, |
| short_name text NOT NULL, |
| parent_cachegroup_id bigint, |
| secondary_parent_cachegroup_id bigint, |
| type bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| fallback_to_closest boolean DEFAULT TRUE, |
| coordinate bigint, |
| CONSTRAINT idx_89476_primary PRIMARY KEY (id, type) |
| ); |
| |
| |
| ALTER TABLE cachegroup OWNER TO traffic_ops; |
| |
| -- |
| -- Name: cachegroup_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS cachegroup_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE cachegroup_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: cachegroup_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE cachegroup_id_seq OWNED BY cachegroup.id; |
| |
| -- |
| -- Name: cachegroup_fallbacks; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS cachegroup_fallbacks ( |
| primary_cg bigint NOT NULL, |
| backup_cg bigint NOT NULL CHECK (primary_cg != backup_cg), |
| set_order bigint NOT NULL, |
| UNIQUE (primary_cg, backup_cg), |
| UNIQUE (primary_cg, set_order) |
| ); |
| |
| ALTER TABLE cachegroup_fallbacks OWNER TO traffic_ops; |
| |
| -- |
| -- Name: cachegroup_localization_method; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS cachegroup_localization_method ( |
| cachegroup bigint NOT NULL, |
| method localization_method NOT NULL, |
| UNIQUE (cachegroup, method) |
| ); |
| |
| -- |
| -- Name: cachegroup_parameter; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS cachegroup_parameter ( |
| cachegroup bigint DEFAULT '0'::bigint NOT NULL, |
| parameter bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89484_primary PRIMARY KEY (cachegroup, parameter) |
| ); |
| |
| |
| ALTER TABLE cachegroup_parameter OWNER TO traffic_ops; |
| |
| -- |
| -- Name: capability; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS capability ( |
| name text NOT NULL, |
| description text, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT capability_pkey PRIMARY KEY (name) |
| ); |
| |
| ALTER TABLE capability OWNER TO traffic_ops; |
| |
| -- |
| -- Name: cdn; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS cdn ( |
| id bigint, |
| name text NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| dnssec_enabled boolean DEFAULT false NOT NULL, |
| domain_name text NOT NULL, |
| CONSTRAINT cdn_domain_name_unique UNIQUE (domain_name), |
| CONSTRAINT idx_89491_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE cdn OWNER TO traffic_ops; |
| |
| -- |
| -- Name: cdn_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS cdn_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE cdn_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: cdn_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE cdn_id_seq OWNED BY cdn.id; |
| |
| -- |
| -- Name: cdn_lock; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS public.cdn_lock ( |
| username text NOT NULL, |
| cdn text NOT NULL, |
| message text, |
| soft boolean NOT NULL DEFAULT TRUE, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| CONSTRAINT pk_cdn_lock PRIMARY KEY ("cdn") |
| ); |
| |
| |
| ALTER TABLE cdn_lock OWNER TO traffic_ops; |
| |
| -- |
| -- Name: cdn_notification; Type: TABLE; Schema: public; Owner: traffic_ops |
| |
| CREATE TABLE cdn_notification ( |
| id bigint NOT NULL, |
| cdn text NOT NULL, |
| "user" text NOT NULL, |
| notification text NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| CONSTRAINT cdn_notification_pkey PRIMARY KEY (id) |
| ); |
| |
| ALTER TABLE cdn_notification OWNER TO traffic_ops; |
| |
| -- |
| -- Name: cdn_notification_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS cdn_notification_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE cdn_notification_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: cdn_notification_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE cdn_notification_id_seq OWNED BY cdn_notification.id; |
| |
| -- |
| -- Name: coordinate; Type: TABLE; Schema: public: Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS coordinate ( |
| id bigserial, |
| name text UNIQUE NOT NULL, |
| latitude numeric NOT NULL DEFAULT 0.0, |
| longitude numeric NOT NULL DEFAULT 0.0, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT coordinate_pkey PRIMARY KEY (id) |
| ); |
| |
| -- |
| -- Name: deliveryservice; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS deliveryservice ( |
| id bigint, |
| xml_id text NOT NULL, |
| active boolean DEFAULT false NOT NULL, |
| dscp bigint NOT NULL, |
| signing_algorithm deliveryservice_signature_type, |
| qstring_ignore smallint, |
| geo_limit smallint DEFAULT '0'::smallint, |
| http_bypass_fqdn text, |
| dns_bypass_ip text, |
| dns_bypass_ip6 text, |
| dns_bypass_ttl bigint, |
| type bigint NOT NULL, |
| profile bigint, |
| cdn_id bigint NOT NULL, |
| ccr_dns_ttl bigint, |
| global_max_mbps bigint, |
| global_max_tps bigint, |
| long_desc text, |
| long_desc_1 text, |
| long_desc_2 text, |
| max_dns_answers bigint DEFAULT '5'::bigint, |
| info_url text, |
| miss_lat numeric, |
| miss_long numeric, |
| check_path text, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| protocol smallint DEFAULT '0'::smallint, |
| ssl_key_version bigint DEFAULT '0'::bigint, |
| ipv6_routing_enabled boolean DEFAULT false, |
| range_request_handling smallint DEFAULT '0'::smallint, |
| edge_header_rewrite text, |
| origin_shield text, |
| mid_header_rewrite text, |
| regex_remap text, |
| cacheurl text, |
| remap_text text, |
| multi_site_origin boolean DEFAULT false, |
| display_name text NOT NULL, |
| tr_response_headers text, |
| initial_dispersion bigint DEFAULT '1'::bigint, |
| dns_bypass_cname text, |
| tr_request_headers text, |
| regional_geo_blocking boolean DEFAULT false NOT NULL, |
| geo_provider smallint DEFAULT '0'::smallint, |
| geo_limit_countries text, |
| logs_enabled boolean DEFAULT false, |
| geolimit_redirect_url text, |
| tenant_id bigint NOT NULL, |
| routing_name text NOT NULL DEFAULT 'cdn', |
| deep_caching_type deep_caching_type NOT NULL DEFAULT 'NEVER', |
| fq_pacing_rate bigint DEFAULT 0, |
| anonymous_blocking_enabled boolean NOT NULL DEFAULT FALSE, |
| consistent_hash_regex text, |
| max_origin_connections bigint NOT NULL DEFAULT 0 CHECK (max_origin_connections >= 0), |
| ecs_enabled boolean NOT NULL DEFAULT false, |
| range_slice_block_size integer CHECK (range_slice_block_size >= 262144 AND range_slice_block_size <= 33554432) DEFAULT NULL, |
| topology text, |
| first_header_rewrite text, |
| inner_header_rewrite text, |
| last_header_rewrite text, |
| service_category text, |
| max_request_header_bytes int NOT NULL DEFAULT 0, |
| CONSTRAINT routing_name_not_empty CHECK ((length(routing_name) > 0)), |
| CONSTRAINT idx_89502_primary PRIMARY KEY (id, type) |
| ); |
| |
| |
| ALTER TABLE deliveryservice OWNER TO traffic_ops; |
| |
| -- |
| -- Name: deliveryservices_required_capability; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS deliveryservices_required_capability ( |
| required_capability TEXT NOT NULL, |
| deliveryservice_id bigint NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| |
| PRIMARY KEY (deliveryservice_id, required_capability) |
| ); |
| |
| -- |
| -- Name: deliveryservice_consistent_hash_query_param; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS deliveryservice_consistent_hash_query_param ( |
| name TEXT NOT NULL, |
| deliveryservice_id bigint NOT NULL, |
| CONSTRAINT name_empty CHECK (length(name) > 0), |
| CONSTRAINT name_reserved CHECK (name NOT IN ('format','trred')), |
| PRIMARY KEY (name, deliveryservice_id) |
| ); |
| |
| -- |
| -- Name: deliveryservice_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS deliveryservice_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE deliveryservice_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: deliveryservice_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE deliveryservice_id_seq OWNED BY deliveryservice.id; |
| |
| |
| -- |
| -- Name: deliveryservice_regex; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS deliveryservice_regex ( |
| deliveryservice bigint NOT NULL, |
| regex bigint NOT NULL, |
| set_number bigint DEFAULT '0'::bigint, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89517_primary PRIMARY KEY (deliveryservice, regex) |
| ); |
| |
| |
| ALTER TABLE deliveryservice_regex OWNER TO traffic_ops; |
| |
| -- |
| -- Name: deliveryservice_request; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS deliveryservice_request ( |
| assignee_id bigint, |
| author_id bigint NOT NULL, |
| change_type change_types NOT NULL, |
| created_at timestamp with time zone NOT NULL DEFAULT now(), |
| id bigserial, |
| last_edited_by_id bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| deliveryservice jsonb DEFAULT NULL, |
| status workflow_states NOT NULL, |
| original jsonb DEFAULT NULL, |
| CONSTRAINT deliveryservice_request_pkey PRIMARY KEY (id), |
| CONSTRAINT appropriate_requested_and_original_for_change_type CHECK ( |
| (change_type = 'delete' AND original IS NOT NULL AND deliveryservice IS NULL) |
| OR |
| (change_type = 'create' AND original IS NULL AND deliveryservice IS NOT NULL) |
| OR ( |
| change_type = 'update' AND |
| deliveryservice IS NOT NULL AND |
| ( |
| ( |
| (status = 'complete' OR status = 'rejected' OR status = 'pending') |
| AND |
| original IS NOT NULL |
| ) |
| OR |
| ( |
| (status = 'draft' OR status = 'submitted') |
| AND |
| original IS NULL |
| ) |
| ) |
| ) |
| ) |
| ); |
| |
| ALTER TABLE deliveryservice_request OWNER TO traffic_ops; |
| |
| -- |
| -- Name: deliveryservice_request_comment; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS deliveryservice_request_comment ( |
| author_id bigint NOT NULL, |
| deliveryservice_request_id bigint NOT NULL, |
| id bigserial, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| value text NOT NULL, |
| CONSTRAINT deliveryservice_request_comment_pkey PRIMARY KEY (id) |
| ); |
| |
| -- |
| -- Name: deliveryservice_server; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS deliveryservice_server ( |
| deliveryservice bigint NOT NULL, |
| server bigint NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| CONSTRAINT idx_89521_primary PRIMARY KEY (deliveryservice, server) |
| ); |
| |
| |
| ALTER TABLE deliveryservice_server OWNER TO traffic_ops; |
| |
| -- |
| -- Name: deliveryservice_tls_version; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS deliveryservice_tls_version ( |
| deliveryservice bigint NOT NULL, |
| tls_version text NOT NULL, |
| CONSTRAINT deliveryservice_tls_version_pkey PRIMARY KEY (deliveryservice, tls_version), |
| CONSTRAINT deliveryservice_tls_version_tls_version_check CHECK (tls_version <> '') |
| ); |
| |
| ALTER TABLE deliveryservice_tls_version OWNER TO traffic_ops; |
| |
| -- |
| -- Name: update_ds_timestamp_on_tls_version_insertion; Type: TRIGGER; Schema: public; Owner: traffic_ops |
| -- |
| DROP TRIGGER IF EXISTS update_ds_timestamp_on_tls_version_insertion on deliveryservice_tls_version; |
| CREATE TRIGGER update_ds_timestamp_on_tls_version_insertion |
| AFTER INSERT ON deliveryservice_tls_version |
| FOR EACH ROW EXECUTE PROCEDURE update_ds_timestamp_on_insert(); |
| |
| -- |
| -- Name: update_ds_timestamp_on_tls_version_delete; Type: TRIGGER; Schema: public; Owner: traffic_ops |
| -- |
| DROP TRIGGER IF EXISTS update_ds_timestamp_on_tls_version_delete on deliveryservice_tls_version; |
| CREATE TRIGGER update_ds_timestamp_on_tls_version_delete |
| AFTER DELETE ON deliveryservice_tls_version |
| FOR EACH ROW EXECUTE PROCEDURE update_ds_timestamp_on_delete(); |
| |
| -- |
| -- Name: deliveryservice_tmuser; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS deliveryservice_tmuser ( |
| deliveryservice bigint NOT NULL, |
| tm_user_id bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89525_primary PRIMARY KEY (deliveryservice, tm_user_id) |
| ); |
| |
| |
| ALTER TABLE deliveryservice_tmuser OWNER TO traffic_ops; |
| |
| -- |
| -- Name: division; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS division ( |
| id bigint NOT NULL, |
| name text NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89531_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE division OWNER TO traffic_ops; |
| |
| -- |
| -- Name: division_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS division_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE division_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: division_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE division_id_seq OWNED BY division.id; |
| |
| -- |
| -- Name: dnschallenges; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS dnschallenges ( |
| fqdn text NOT NULL, |
| record text NOT NULL, |
| xml_id text NOT NULL |
| ); |
| |
| -- |
| -- Name: federation; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS federation ( |
| id bigint NOT NULL, |
| cname text NOT NULL, |
| description text, |
| ttl integer NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89541_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE federation OWNER TO traffic_ops; |
| |
| -- |
| -- Name: federation_deliveryservice; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS federation_deliveryservice ( |
| federation bigint NOT NULL, |
| deliveryservice bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89549_primary PRIMARY KEY (federation, deliveryservice) |
| ); |
| |
| |
| ALTER TABLE federation_deliveryservice OWNER TO traffic_ops; |
| |
| -- |
| -- Name: federation_federation_resolver; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS federation_federation_resolver ( |
| federation bigint NOT NULL, |
| federation_resolver bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89553_primary PRIMARY KEY (federation, federation_resolver) |
| ); |
| |
| |
| ALTER TABLE federation_federation_resolver OWNER TO traffic_ops; |
| |
| -- |
| -- Name: federation_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS federation_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE federation_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: federation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE federation_id_seq OWNED BY federation.id; |
| |
| |
| -- |
| -- Name: federation_resolver; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS federation_resolver ( |
| id bigint NOT NULL, |
| ip_address text NOT NULL, |
| type bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89559_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE federation_resolver OWNER TO traffic_ops; |
| |
| -- |
| -- Name: federation_resolver_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS federation_resolver_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE federation_resolver_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: federation_resolver_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE federation_resolver_id_seq OWNED BY federation_resolver.id; |
| |
| |
| -- |
| -- Name: federation_tmuser; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS federation_tmuser ( |
| federation bigint NOT NULL, |
| tm_user bigint NOT NULL, |
| role bigint, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89567_primary PRIMARY KEY (federation, tm_user) |
| ); |
| |
| |
| ALTER TABLE federation_tmuser OWNER TO traffic_ops; |
| |
| -- |
| -- Name: hwinfo; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS hwinfo ( |
| id bigint NOT NULL, |
| serverid bigint NOT NULL, |
| description text NOT NULL, |
| val text NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89583_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE hwinfo OWNER TO traffic_ops; |
| |
| -- |
| -- Name: hwinfo_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS hwinfo_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE hwinfo_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: hwinfo_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE hwinfo_id_seq OWNED BY hwinfo.id; |
| |
| -- |
| -- Name: interface; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS interface ( |
| max_bandwidth bigint DEFAULT NULL CHECK (max_bandwidth IS NULL OR max_bandwidth >= 0), |
| monitor boolean NOT NULL, |
| mtu bigint DEFAULT 1500, |
| name text NOT NULL CHECK (name != ''), |
| server bigint NOT NULL, |
| router_host_name text NOT NULL DEFAULT '', |
| router_port_name text NOT NULL DEFAULT '', |
| PRIMARY KEY (name, server) |
| ); |
| |
| ALTER TABLE interface |
| ADD CONSTRAINT interface_mtu_check |
| CHECK (((mtu IS NULL) OR (mtu >= 1280))); |
| |
| -- |
| -- Name: ip_address; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS ip_address ( |
| address inet NOT NULL, |
| gateway inet CHECK ( |
| gateway IS NULL OR ( |
| family(gateway) = 4 AND |
| masklen(gateway) = 32 |
| ) OR ( |
| family(gateway) = 6 AND |
| masklen(gateway) = 128 |
| ) |
| ), |
| interface text NOT NULL, |
| server bigint NOT NULL, |
| service_address boolean NOT NULL DEFAULT FALSE, |
| PRIMARY KEY (address, interface, server) |
| ); |
| |
| -- |
| -- Name: before_create_ip_address_trigger; Type: TRIGGER; Schema: public; Owner: traffic_ops |
| -- |
| DROP TRIGGER IF EXISTS before_create_ip_address_trigger on ip_address; |
| CREATE TRIGGER before_create_ip_address_trigger |
| BEFORE INSERT ON ip_address |
| FOR EACH ROW EXECUTE PROCEDURE before_ip_address_table(); |
| |
| -- |
| -- Name: before_update_ip_address_trigger; Type: TRIGGER; Schema: public; Owner: traffic_ops |
| -- |
| DROP TRIGGER IF EXISTS before_update_ip_address_trigger on ip_address; |
| CREATE TRIGGER before_update_ip_address_trigger |
| BEFORE UPDATE ON ip_address |
| FOR EACH ROW WHEN (NEW.address <> OLD.address) |
| EXECUTE PROCEDURE before_ip_address_table(); |
| |
| -- |
| -- Name: job; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS job ( |
| id bigint NOT NULL, |
| ttl_hr integer, |
| asset_url text NOT NULL, |
| start_time timestamp with time zone NOT NULL, |
| entered_time timestamp with time zone NOT NULL, |
| job_user bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| job_deliveryservice bigint, |
| invalidation_type text NOT NULL DEFAULT 'REFRESH', |
| CONSTRAINT idx_89593_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE job OWNER TO traffic_ops; |
| |
| -- |
| -- Name: job_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS job_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE job_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: job_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE job_id_seq OWNED BY job.id; |
| |
| CREATE TABLE IF NOT EXISTS last_deleted ( |
| table_name text NOT NULL PRIMARY KEY, |
| last_updated timestamp with time zone NOT NULL DEFAULT now() |
| ); |
| |
| -- |
| -- Name: log; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS log ( |
| id bigint NOT NULL, |
| level text, |
| message text NOT NULL, |
| tm_user bigint NOT NULL, |
| ticketnum text, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89634_primary PRIMARY KEY (id, tm_user) |
| ); |
| |
| |
| ALTER TABLE log OWNER TO traffic_ops; |
| |
| -- |
| -- Name: log_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS log_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE log_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: log_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE log_id_seq OWNED BY log.id; |
| |
| -- |
| -- Name: origin; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS origin ( |
| id bigserial NOT NULL, |
| name text UNIQUE NOT NULL, |
| fqdn text NOT NULL, |
| protocol origin_protocol NOT NULL DEFAULT 'http', |
| is_primary boolean NOT NULL DEFAULT FALSE, |
| port bigint, -- TODO: port numbers have a max of 65535 - this could be just an integer |
| ip_address text, -- TODO: these should be inet type, not text |
| ip6_address text, |
| deliveryservice bigint NOT NULL, |
| coordinate bigint, |
| profile bigint, |
| cachegroup bigint, |
| tenant bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT origin_pkey PRIMARY KEY (id) |
| ); |
| |
| ALTER TABLE origin OWNER TO traffic_ops; |
| |
| -- |
| -- Name: parameter; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS parameter ( |
| id bigint NOT NULL, |
| name text NOT NULL, |
| config_file text, |
| value text NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| secure boolean DEFAULT false NOT NULL, |
| CONSTRAINT unique_param UNIQUE (name, config_file, value), |
| CONSTRAINT idx_89644_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE parameter OWNER TO traffic_ops; |
| |
| -- |
| -- Name: parameter_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS parameter_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE parameter_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: parameter_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE parameter_id_seq OWNED BY parameter.id; |
| |
| |
| -- |
| -- Name: phys_location; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS phys_location ( |
| id bigint NOT NULL, |
| name text NOT NULL, |
| short_name text NOT NULL, |
| address text NOT NULL, |
| city text NOT NULL, |
| state text NOT NULL, |
| zip text NOT NULL, |
| poc text, |
| phone text, |
| email text, |
| comments text, |
| region bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89655_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE phys_location OWNER TO traffic_ops; |
| |
| -- |
| -- Name: phys_location_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS phys_location_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE phys_location_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: phys_location_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE phys_location_id_seq OWNED BY phys_location.id; |
| |
| -- |
| -- Name: profile; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS profile ( |
| id bigint NOT NULL, |
| name text NOT NULL, |
| description text, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| type profile_type NOT NULL, |
| cdn bigint NOT NULL, |
| routing_disabled boolean NOT NULL DEFAULT FALSE, |
| CONSTRAINT idx_89665_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE profile OWNER TO traffic_ops; |
| |
| -- |
| -- Name: profile_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS profile_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE profile_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: profile_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE profile_id_seq OWNED BY profile.id; |
| |
| |
| -- |
| -- Name: profile_parameter; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS profile_parameter ( |
| profile bigint NOT NULL, |
| parameter bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89673_primary PRIMARY KEY (profile, parameter) |
| ); |
| |
| |
| ALTER TABLE profile_parameter OWNER TO traffic_ops; |
| |
| -- |
| -- Name: regex; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS regex ( |
| id bigint NOT NULL, |
| pattern text DEFAULT ''::text NOT NULL, |
| type bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89679_primary PRIMARY KEY (id, type) |
| ); |
| |
| |
| ALTER TABLE regex OWNER TO traffic_ops; |
| |
| -- |
| -- Name: regex_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS regex_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE regex_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: regex_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE regex_id_seq OWNED BY regex.id; |
| |
| |
| -- |
| -- Name: region; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS region ( |
| id bigint NOT NULL, |
| name text NOT NULL, |
| division bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89690_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE region OWNER TO traffic_ops; |
| |
| -- |
| -- Name: region_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS region_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE region_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: region_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE region_id_seq OWNED BY region.id; |
| |
| |
| -- |
| -- Name: role; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS role ( |
| id bigint, |
| name text NOT NULL, |
| description text NOT NULL, |
| priv_level bigint NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT role_name_unique UNIQUE (name), |
| CONSTRAINT idx_89700_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE role OWNER TO traffic_ops; |
| |
| -- |
| -- Name: role_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS role_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE role_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: role_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE role_id_seq OWNED BY role.id; |
| |
| -- |
| -- Name: role_capability; Type TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS role_capability ( |
| role_id bigint NOT NULL, |
| cap_name text NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| UNIQUE (role_id, cap_name) |
| ); |
| |
| ALTER TABLE role_capability OWNER TO traffic_ops; |
| |
| -- |
| -- Name: server; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS server ( |
| id bigint NOT NULL, |
| host_name text NOT NULL, |
| domain_name text NOT NULL, |
| tcp_port bigint, |
| xmpp_id text, |
| xmpp_passwd text, |
| phys_location bigint NOT NULL, |
| rack text, |
| cachegroup bigint DEFAULT '0'::bigint NOT NULL, |
| type bigint NOT NULL, |
| status bigint NOT NULL, |
| offline_reason text, |
| upd_pending boolean DEFAULT false NOT NULL, |
| profile bigint NOT NULL, |
| cdn_id bigint NOT NULL, |
| mgmt_ip_address text, |
| mgmt_ip_netmask text, |
| mgmt_ip_gateway text, |
| ilo_ip_address text, |
| ilo_ip_netmask text, |
| ilo_ip_gateway text, |
| ilo_username text, |
| ilo_password text, |
| guid text, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| https_port bigint, |
| reval_pending boolean NOT NULL DEFAULT FALSE, |
| status_last_updated timestamp with time zone, |
| CONSTRAINT idx_89709_primary PRIMARY KEY (id) |
| ); |
| |
| -- |
| -- Name: before_update_server_trigger; Type: TRIGGER; Schema: public; Owner: traffic_ops |
| -- |
| DROP TRIGGER IF EXISTS before_update_server_trigger ON server; |
| CREATE TRIGGER before_update_server_trigger |
| BEFORE UPDATE ON server |
| FOR EACH ROW WHEN (NEW.profile <> OLD.profile) |
| EXECUTE PROCEDURE before_server_table(); |
| |
| -- |
| -- Name: before_create_server_trigger; Type: TRIGGER; Schema: public; Owner: traffic_ops |
| -- |
| DROP TRIGGER IF EXISTS before_create_server_trigger ON server; |
| CREATE TRIGGER before_create_server_trigger |
| BEFORE INSERT ON server |
| FOR EACH ROW EXECUTE PROCEDURE before_server_table(); |
| |
| |
| ALTER TABLE server OWNER TO traffic_ops; |
| |
| -- |
| -- Name: server_capability; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS server_capability ( |
| name TEXT PRIMARY KEY, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| CONSTRAINT name_empty CHECK (length(name) > 0) |
| ); |
| |
| -- |
| -- Name: server_server_capability; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS server_server_capability ( |
| server_capability TEXT NOT NULL, |
| server bigint NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| |
| PRIMARY KEY (server, server_capability) |
| ); |
| |
| -- |
| -- Name: service_category; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS service_category ( |
| name TEXT PRIMARY KEY CHECK (name <> ''), |
| last_updated TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL |
| ); |
| -- |
| -- Name: server_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS server_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE server_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: server_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE server_id_seq OWNED BY server.id; |
| |
| |
| -- |
| -- Name: servercheck; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS servercheck ( |
| id bigint NOT NULL, |
| server bigint NOT NULL, |
| aa bigint, |
| ab bigint, |
| ac bigint, |
| ad bigint, |
| ae bigint, |
| af bigint, |
| ag bigint, |
| ah bigint, |
| ai bigint, |
| aj bigint, |
| ak bigint, |
| al bigint, |
| am bigint, |
| an bigint, |
| ao bigint, |
| ap bigint, |
| aq bigint, |
| ar bigint, |
| bf bigint, |
| at bigint, |
| au bigint, |
| av bigint, |
| aw bigint, |
| ax bigint, |
| ay bigint, |
| az bigint, |
| ba bigint, |
| bb bigint, |
| bc bigint, |
| bd bigint, |
| be bigint, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89722_primary PRIMARY KEY (id, server) |
| ); |
| |
| |
| ALTER TABLE servercheck OWNER TO traffic_ops; |
| |
| -- |
| -- Name: servercheck_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS servercheck_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE servercheck_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: servercheck_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE servercheck_id_seq OWNED BY servercheck.id; |
| |
| -- |
| -- Name: snapshot; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS snapshot ( |
| cdn text NOT NULL, |
| crconfig json NOT NULL, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| monitoring json NOT NULL, |
| CONSTRAINT snapshot_pkey PRIMARY KEY (cdn) |
| ); |
| |
| ALTER TABLE snapshot OWNER TO traffic_ops; |
| |
| -- |
| -- Name: staticdnsentry; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS staticdnsentry ( |
| id bigint NOT NULL, |
| host text NOT NULL, |
| address text NOT NULL, |
| type bigint NOT NULL, |
| ttl bigint DEFAULT '3600'::bigint NOT NULL, |
| deliveryservice bigint NOT NULL, |
| cachegroup bigint, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT idx_89729_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE staticdnsentry OWNER TO traffic_ops; |
| |
| -- |
| -- Name: staticdnsentry_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS staticdnsentry_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE staticdnsentry_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: staticdnsentry_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE staticdnsentry_id_seq OWNED BY staticdnsentry.id; |
| |
| |
| -- |
| -- Name: stats_summary; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS stats_summary ( |
| id bigint NOT NULL, |
| cdn_name text DEFAULT 'all'::text NOT NULL, |
| deliveryservice_name text NOT NULL, |
| stat_name text NOT NULL, |
| stat_value double precision NOT NULL, |
| summary_time timestamp with time zone DEFAULT now() NOT NULL, |
| stat_date date, |
| CONSTRAINT idx_89740_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE stats_summary OWNER TO traffic_ops; |
| |
| -- |
| -- Name: stats_summary_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS stats_summary_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE stats_summary_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: stats_summary_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE stats_summary_id_seq OWNED BY stats_summary.id; |
| |
| |
| -- |
| -- Name: status; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS status ( |
| id bigint NOT NULL, |
| name text NOT NULL, |
| description text, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT status_name_unique UNIQUE (name), |
| CONSTRAINT idx_89751_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE status OWNER TO traffic_ops; |
| |
| -- |
| -- Name: status_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS status_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE status_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: status_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE status_id_seq OWNED BY status.id; |
| |
| |
| -- |
| -- Name: steering_target; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS steering_target ( |
| deliveryservice bigint NOT NULL, |
| target bigint NOT NULL, |
| value bigint NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| type bigint NOT NULL, |
| CONSTRAINT idx_89759_primary PRIMARY KEY (deliveryservice, target) |
| ); |
| |
| |
| ALTER TABLE steering_target OWNER TO traffic_ops; |
| |
| -- |
| -- Name: tenant; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS tenant ( |
| id bigserial, |
| name text UNIQUE NOT NULL, |
| active boolean NOT NULL DEFAULT FALSE, |
| parent_id bigint DEFAULT 1 CHECK (id != parent_id), |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT tenant_pkey PRIMARY KEY (id) |
| ); |
| |
| -- |
| -- Name: tm_user; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS tm_user ( |
| id bigint NOT NULL, |
| username text NOT NULL, |
| public_ssh_key text, |
| role bigint, |
| uid bigint, |
| gid bigint, |
| local_passwd text, |
| confirm_local_passwd text, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| company text, |
| email text, |
| full_name text, |
| new_user boolean DEFAULT false NOT NULL, |
| address_line1 text, |
| address_line2 text, |
| city text, |
| state_or_province text, |
| phone_number text, |
| postal_code text, |
| country text, |
| token text, |
| registration_sent timestamp with time zone, |
| tenant_id bigint NOT NULL, |
| last_authenticated timestamp with time zone, |
| CONSTRAINT idx_89765_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE tm_user OWNER TO traffic_ops; |
| |
| -- |
| -- Name: tm_user_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS tm_user_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE tm_user_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: tm_user_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE tm_user_id_seq OWNED BY tm_user.id; |
| |
| |
| -- |
| -- Name: to_extension; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS to_extension ( |
| id bigint NOT NULL, |
| name text NOT NULL, |
| version text NOT NULL, |
| info_url text NOT NULL, |
| script_file text NOT NULL, |
| isactive boolean DEFAULT false NOT NULL, |
| additional_config_json text, |
| description text, |
| servercheck_short_name text, |
| servercheck_column_name text, |
| type bigint NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| CONSTRAINT idx_89776_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE to_extension OWNER TO traffic_ops; |
| |
| -- |
| -- Name: to_extension_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS to_extension_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE to_extension_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: to_extension_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE to_extension_id_seq OWNED BY to_extension.id; |
| |
| -- |
| -- Name: toplogy; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS topology ( |
| name text PRIMARY KEY, |
| description text NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL |
| ); |
| |
| -- |
| -- Name: topology_cachegroup; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS topology_cachegroup ( |
| id BIGSERIAL PRIMARY KEY, |
| topology text NOT NULL, |
| cachegroup text NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| CONSTRAINT unique_topology_cachegroup UNIQUE (topology, cachegroup) |
| ); |
| |
| -- |
| -- Name: topology_cachegroup_parents; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS topology_cachegroup_parents ( |
| child bigint NOT NULL, |
| parent bigint NOT NULL, |
| rank integer NOT NULL, |
| last_updated timestamp with time zone DEFAULT now() NOT NULL, |
| CONSTRAINT topology_cachegroup_parents_rank_check CHECK (rank = 1 OR rank = 2), |
| CONSTRAINT unique_child_rank UNIQUE (child, rank), |
| CONSTRAINT unique_child_parent UNIQUE (child, parent) |
| ); |
| |
| -- |
| -- Name: type; Type: TABLE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE TABLE IF NOT EXISTS type ( |
| id bigint NOT NULL, |
| name text NOT NULL, |
| description text, |
| use_in_table text, |
| last_updated timestamp with time zone NOT NULL DEFAULT now(), |
| CONSTRAINT type_name_unique UNIQUE(name), |
| CONSTRAINT idx_89786_primary PRIMARY KEY (id) |
| ); |
| |
| |
| ALTER TABLE type OWNER TO traffic_ops; |
| |
| -- |
| -- Name: type_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE SEQUENCE IF NOT EXISTS type_id_seq |
| START WITH 1 |
| INCREMENT BY 1 |
| NO MINVALUE |
| NO MAXVALUE |
| CACHE 1; |
| |
| |
| ALTER TABLE type_id_seq OWNER TO traffic_ops; |
| |
| -- |
| -- Name: type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER SEQUENCE type_id_seq OWNED BY type.id; |
| |
| DO $$ BEGIN |
| IF NOT EXISTS (SELECT FROM information_schema.tables |
| WHERE table_name = 'profile_type_values' |
| AND table_type = 'VIEW') THEN |
| -- |
| -- Name: profile_type_values; Type: VIEW; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE VIEW profile_type_values AS |
| SELECT unnest(enum_range(NULL::profile_type)) AS VALUE |
| ORDER BY (unnest(enum_range(NULL::profile_type))); |
| |
| ALTER TABLE profile_type_values OWNER TO traffic_ops; |
| END IF; |
| END$$; |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY asn ALTER COLUMN id SET DEFAULT nextval('asn_id_seq'::regclass); |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY async_status ALTER COLUMN id SET DEFAULT nextval('async_status_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup ALTER COLUMN id SET DEFAULT nextval('cachegroup_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cdn ALTER COLUMN id SET DEFAULT nextval('cdn_id_seq'::regclass); |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cdn_notification ALTER COLUMN id SET DEFAULT nextval('cdn_notification_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice ALTER COLUMN id SET DEFAULT nextval('deliveryservice_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY division ALTER COLUMN id SET DEFAULT nextval('division_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation ALTER COLUMN id SET DEFAULT nextval('federation_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation_resolver ALTER COLUMN id SET DEFAULT nextval('federation_resolver_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY hwinfo ALTER COLUMN id SET DEFAULT nextval('hwinfo_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY job ALTER COLUMN id SET DEFAULT nextval('job_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY log ALTER COLUMN id SET DEFAULT nextval('log_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY parameter ALTER COLUMN id SET DEFAULT nextval('parameter_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY phys_location ALTER COLUMN id SET DEFAULT nextval('phys_location_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY profile ALTER COLUMN id SET DEFAULT nextval('profile_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY regex ALTER COLUMN id SET DEFAULT nextval('regex_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY region ALTER COLUMN id SET DEFAULT nextval('region_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY role ALTER COLUMN id SET DEFAULT nextval('role_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY server ALTER COLUMN id SET DEFAULT nextval('server_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY servercheck ALTER COLUMN id SET DEFAULT nextval('servercheck_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY staticdnsentry ALTER COLUMN id SET DEFAULT nextval('staticdnsentry_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY stats_summary ALTER COLUMN id SET DEFAULT nextval('stats_summary_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY status ALTER COLUMN id SET DEFAULT nextval('status_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY tm_user ALTER COLUMN id SET DEFAULT nextval('tm_user_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY to_extension ALTER COLUMN id SET DEFAULT nextval('to_extension_id_seq'::regclass); |
| |
| |
| -- |
| -- Name: id; Type: DEFAULT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY type ALTER COLUMN id SET DEFAULT nextval('type_id_seq'::regclass); |
| |
| DO $$ BEGIN |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'asn' AND column_name = 'id') THEN |
| -- |
| -- Name: idx_89468_cr_id_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89468_cr_id_unique ON asn USING btree (id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'asn' AND column_name = 'cachegroup') THEN |
| -- |
| -- Name: idx_89468_fk_cran_cachegroup1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89468_fk_cran_cachegroup1 ON asn USING btree (cachegroup); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cachegroup' AND column_name = 'name') THEN |
| -- |
| -- Name: idx_89476_cg_name_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89476_cg_name_unique ON cachegroup USING btree (name); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cachegroup' AND column_name = 'short_name') THEN |
| -- |
| -- Name: idx_89476_cg_short_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89476_cg_short_unique ON cachegroup USING btree (short_name); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cachegroup' AND column_name = 'parent_cachegroup_id') THEN |
| -- |
| -- Name: idx_89476_fk_cg_1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89476_fk_cg_1 ON cachegroup USING btree (parent_cachegroup_id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cachegroup' AND column_name = 'secondary_parent_cachegroup_id') THEN |
| -- |
| -- Name: idx_89476_fk_cg_secondary; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89476_fk_cg_secondary ON cachegroup USING btree (secondary_parent_cachegroup_id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cachegroup' AND column_name = 'type') THEN |
| -- |
| -- Name: idx_89476_fk_cg_type1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89476_fk_cg_type1 ON cachegroup USING btree (type); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cachegroup' AND column_name = 'id') THEN |
| -- |
| -- Name: idx_89476_lo_id_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89476_lo_id_unique ON cachegroup USING btree (id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cachegroup_parameter' AND column_name = 'parameter') THEN |
| -- |
| -- Name: idx_89484_fk_parameter; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89484_fk_parameter ON cachegroup_parameter USING btree (parameter); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cdn' AND column_name = 'name') THEN |
| -- |
| -- Name: idx_89491_cdn_cdn_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89491_cdn_cdn_unique ON cdn USING btree (name); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice' AND column_name = 'id') THEN |
| -- |
| -- Name: idx_89502_ds_id_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89502_ds_id_unique ON deliveryservice USING btree (id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice' AND column_name = 'tenant_id') THEN |
| -- |
| -- Name: idx_k_deliveryservice_tenant_idx; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_k_deliveryservice_tenant_idx ON deliveryservice USING btree (tenant_id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice' AND column_name = 'xml_id') THEN |
| -- |
| -- Name: idx_89502_ds_name_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89502_ds_name_unique ON deliveryservice USING btree (xml_id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice' AND column_name = 'cdn_id') THEN |
| -- |
| -- Name: idx_89502_fk_cdn1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89502_fk_cdn1 ON deliveryservice USING btree (cdn_id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice' AND column_name = 'profile') THEN |
| -- |
| -- Name: idx_89502_fk_deliveryservice_profile1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89502_fk_deliveryservice_profile1 ON deliveryservice USING btree (profile); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice' AND column_name = 'type') THEN |
| -- |
| -- Name: idx_89502_fk_deliveryservice_type1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89502_fk_deliveryservice_type1 ON deliveryservice USING btree (type); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice_regex' AND column_name = 'regex') THEN |
| -- |
| -- Name: idx_89517_fk_ds_to_regex_regex1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89517_fk_ds_to_regex_regex1 ON deliveryservice_regex USING btree (regex); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice_server' AND column_name = 'server') THEN |
| -- |
| -- Name: idx_89521_fk_ds_to_cs_contentserver1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89521_fk_ds_to_cs_contentserver1 ON deliveryservice_server USING btree (server); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice_tmuser' AND column_name = 'tm_user_id') THEN |
| -- |
| -- Name: idx_89525_fk_tm_userid; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89525_fk_tm_userid ON deliveryservice_tmuser USING btree (tm_user_id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'division' AND column_name = 'name') THEN |
| -- |
| -- Name: idx_89531_name_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89531_name_unique ON division USING btree (name); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'federation_deliveryservice' AND column_name = 'deliveryservice') THEN |
| -- |
| -- Name: idx_89549_fk_fed_to_ds1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89549_fk_fed_to_ds1 ON federation_deliveryservice USING btree (deliveryservice); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'federation_federation_resolver' AND column_name = 'federation') THEN |
| -- |
| -- Name: idx_89553_fk_federation_federation_resolver; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89553_fk_federation_federation_resolver ON federation_federation_resolver USING btree (federation); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'federation_federation_resolver' AND column_name = 'federation_resolver') THEN |
| -- |
| -- Name: idx_89553_fk_federation_resolver_to_fed1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89553_fk_federation_resolver_to_fed1 ON federation_federation_resolver USING btree (federation_resolver); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'federation_resolver' AND column_name = 'ip_address') THEN |
| -- |
| -- Name: idx_89559_federation_resolver_ip_address; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89559_federation_resolver_ip_address ON federation_resolver USING btree (ip_address); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'federation_resolver' AND column_name = 'type') THEN |
| -- |
| -- Name: idx_89559_fk_federation_mapping_type; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89559_fk_federation_mapping_type ON federation_resolver USING btree (type); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'federation_tmuser' AND column_name = 'federation') THEN |
| -- |
| -- Name: idx_89567_fk_federation_federation_resolver; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89567_fk_federation_federation_resolver ON federation_tmuser USING btree (federation); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'federation_tmuser' AND column_name = 'role') THEN |
| -- |
| -- Name: idx_89567_fk_federation_tmuser_role; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89567_fk_federation_tmuser_role ON federation_tmuser USING btree (role); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'federation_tmuser' AND column_name = 'tm_user') THEN |
| -- |
| -- Name: idx_89567_fk_federation_tmuser_tmuser; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89567_fk_federation_tmuser_tmuser ON federation_tmuser USING btree (tm_user); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'hwinfo' AND column_name = 'serverid') THEN |
| -- |
| -- Name: idx_89583_fk_hwinfo1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89583_fk_hwinfo1 ON hwinfo USING btree (serverid); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'hwinfo' AND column_name = 'serverid') |
| AND EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'hwinfo' AND column_name = 'description') THEN |
| -- |
| -- Name: idx_89583_serverid; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89583_serverid ON hwinfo USING btree (serverid, description); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'job' AND column_name = 'job_deliveryservice') THEN |
| -- |
| -- Name: idx_89593_fk_job_deliveryservice1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89593_fk_job_deliveryservice1 ON job USING btree (job_deliveryservice); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'job' AND column_name = 'job_user') THEN |
| -- |
| -- Name: idx_89593_fk_job_user_id1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89593_fk_job_user_id1 ON job USING btree (job_user); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'job' AND column_name = 'start_time') THEN |
| -- |
| -- Name: job_start_time_idx; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS job_start_time_idx ON job (start_time DESC NULLS LAST); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'log' AND column_name = 'tm_user') THEN |
| -- |
| -- Name: idx_89634_fk_log_1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89634_fk_log_1 ON log USING btree (tm_user); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'log' AND column_name = 'last_updated') THEN |
| -- |
| -- Name: idx_89634_idx_last_updated; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89634_idx_last_updated ON log USING btree (last_updated); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'parameter' AND column_name = 'name') |
| AND EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'parameter' AND column_name = 'value') THEN |
| -- |
| -- Name: idx_89644_parameter_name_value_idx; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89644_parameter_name_value_idx ON parameter USING btree (name, value); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'phys_location' AND column_name = 'region') THEN |
| -- |
| -- Name: idx_89655_fk_phys_location_region_idx; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89655_fk_phys_location_region_idx ON phys_location USING btree (region); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'phys_location' AND column_name = 'name') THEN |
| -- |
| -- Name: idx_89655_name_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89655_name_unique ON phys_location USING btree (name); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'phys_location' AND column_name = 'short_name') THEN |
| -- |
| -- Name: idx_89655_short_name_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89655_short_name_unique ON phys_location USING btree (short_name); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'profile' AND column_name = 'name') THEN |
| -- |
| -- Name: idx_89665_name_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89665_name_unique ON profile USING btree (name); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'profile' AND column_name = 'cdn') THEN |
| -- |
| -- Name: idx_181818_fk_cdn1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_181818_fk_cdn1 ON profile USING btree (cdn); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'profile_parameter' AND column_name = 'parameter') THEN |
| -- |
| -- Name: idx_89673_fk_atsprofile_atsparameters_atsparameters1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89673_fk_atsprofile_atsparameters_atsparameters1 ON profile_parameter USING btree (parameter); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'profile_parameter' AND column_name = 'profile') THEN |
| -- |
| -- Name: idx_89673_fk_atsprofile_atsparameters_atsprofile1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89673_fk_atsprofile_atsparameters_atsprofile1 ON profile_parameter USING btree (profile); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'regex' AND column_name = 'type') THEN |
| -- |
| -- Name: idx_89679_fk_regex_type1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89679_fk_regex_type1 ON regex USING btree (type); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'regex' AND column_name = 'id') THEN |
| -- |
| -- Name: idx_89679_re_id_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89679_re_id_unique ON regex USING btree (id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'region' AND column_name = 'division') THEN |
| -- |
| -- Name: idx_89690_fk_region_division1_idx; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89690_fk_region_division1_idx ON region USING btree (division); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'region' AND column_name = 'name') THEN |
| -- |
| -- Name: idx_89690_name_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89690_name_unique ON region USING btree (name); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'server' AND column_name = 'cdn_id') THEN |
| -- |
| -- Name: idx_89709_fk_cdn2; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89709_fk_cdn2 ON server USING btree (cdn_id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'server' AND column_name = 'profile') THEN |
| -- |
| -- Name: idx_89709_fk_contentserver_atsprofile1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89709_fk_contentserver_atsprofile1 ON server USING btree (profile); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'server' AND column_name = 'status') THEN |
| -- |
| -- Name: idx_89709_fk_contentserver_contentserverstatus1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89709_fk_contentserver_contentserverstatus1 ON server USING btree (status); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'server' AND column_name = 'type') THEN |
| -- |
| -- Name: idx_89709_fk_contentserver_contentservertype1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89709_fk_contentserver_contentservertype1 ON server USING btree (type); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'server' AND column_name = 'phys_location') THEN |
| -- |
| -- Name: idx_89709_fk_contentserver_phys_location1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89709_fk_contentserver_phys_location1 ON server USING btree (phys_location); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'server' AND column_name = 'cachegroup') THEN |
| -- |
| -- Name: idx_89709_fk_server_cachegroup1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89709_fk_server_cachegroup1 ON server USING btree (cachegroup); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'server' AND column_name = 'id') THEN |
| -- |
| -- Name: idx_89709_se_id_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89709_se_id_unique ON server USING btree (id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'servercheck' AND column_name = 'server') THEN |
| -- |
| -- Name: idx_89722_fk_serverstatus_server1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89722_fk_serverstatus_server1 ON servercheck USING btree (server); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'servercheck' AND column_name = 'server') THEN |
| -- |
| -- Name: idx_89722_server; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89722_server ON servercheck USING btree (server); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'servercheck' AND column_name = 'id') THEN |
| -- |
| -- Name: idx_89722_ses_id_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89722_ses_id_unique ON servercheck USING btree (id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'staticdnsentry' AND column_name = 'host') |
| AND EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'staticdnsentry' AND column_name = 'address') |
| AND EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'staticdnsentry' AND column_name = 'deliveryservice') |
| AND EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'staticdnsentry' AND column_name = 'cachegroup') THEN |
| -- |
| -- Name: idx_89729_combi_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89729_combi_unique ON staticdnsentry USING btree (host, address, deliveryservice, cachegroup); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'staticdnsentry' AND column_name = 'cachegroup') THEN |
| -- |
| -- Name: idx_89729_fk_staticdnsentry_cachegroup1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89729_fk_staticdnsentry_cachegroup1 ON staticdnsentry USING btree (cachegroup); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'staticdnsentry' AND column_name = 'deliveryservice') THEN |
| -- |
| -- Name: idx_89729_fk_staticdnsentry_ds; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89729_fk_staticdnsentry_ds ON staticdnsentry USING btree (deliveryservice); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'staticdnsentry' AND column_name = 'type') THEN |
| -- |
| -- Name: idx_89729_fk_staticdnsentry_type; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89729_fk_staticdnsentry_type ON staticdnsentry USING btree (type); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'topology_cachegroup' AND column_name = 'cachegroup') THEN |
| -- |
| -- Name: topology_cachegroup_cachegroup_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS topology_cachegroup_cachegroup_fkey ON topology_cachegroup USING btree (cachegroup); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'topology_cachegroup' AND column_name = 'topology') THEN |
| -- |
| -- Name: topology_cachegroup_topology_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS topology_cachegroup_topology_fkey ON topology_cachegroup USING btree (topology); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'topology_cachegroup_parents' AND column_name = 'child') THEN |
| -- |
| -- Name: topology_cachegroup_parents_child_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS topology_cachegroup_parents_child_fkey ON topology_cachegroup_parents USING btree (child); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'topology_cachegroup_parents' AND column_name = 'parent') THEN |
| -- |
| -- Name: topology_cachegroup_parents_parents_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS topology_cachegroup_parents_parents_fkey ON topology_cachegroup_parents USING btree (parent); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'deliveryservice' AND column_name = 'topology') THEN |
| -- |
| -- Name: deliveryservice_topology_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS deliveryservice_topology_fkey ON deliveryservice USING btree (topology); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'tenant' AND column_name = 'parent_id') THEN |
| -- |
| -- Name: idx_k_tenant_parent_tenant_idx; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_k_tenant_parent_tenant_idx ON tenant USING btree (parent_id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'tm_user' AND column_name = 'role') THEN |
| -- |
| -- Name: idx_89765_fk_user_1; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89765_fk_user_1 ON tm_user USING btree (role); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'tm_user' AND column_name = 'tenant_id') THEN |
| -- |
| -- Name: idx_k_tm_user_tenant_idx; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_k_tm_user_tenant_idx ON tm_user USING btree (tenant_id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'tm_user' AND column_name = 'email') THEN |
| -- |
| -- Name: idx_89765_tmuser_email_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89765_tmuser_email_unique ON tm_user USING btree (email); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'tm_user' AND column_name = 'username') THEN |
| -- |
| -- Name: idx_89765_username_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89765_username_unique ON tm_user USING btree (username); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'to_extension' AND column_name = 'type') THEN |
| -- |
| -- Name: idx_89776_fk_ext_type_idx; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS idx_89776_fk_ext_type_idx ON to_extension USING btree (type); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'to_extension' AND column_name = 'id') THEN |
| -- |
| -- Name: idx_89776_id_unique; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS idx_89776_id_unique ON to_extension USING btree (id); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cachegroup' AND column_name = 'coordinate') THEN |
| -- |
| -- Name: cachegroup_coordinate_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS cachegroup_coordinate_fkey ON cachegroup USING btree (coordinate); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'cachegroup_localization_method' AND column_name = 'cachegroup') THEN |
| -- |
| -- Name: cachegroup_localization_method_cachegroup_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS cachegroup_localization_method_cachegroup_fkey ON cachegroup_localization_method USING btree(cachegroup); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'origin' AND column_name = 'is_primary') |
| AND EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'origin' AND column_name = 'deliveryservice') THEN |
| -- |
| -- Name: origin_is_primary_deliveryservice_constraint; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE UNIQUE INDEX IF NOT EXISTS origin_is_primary_deliveryservice_constraint ON origin (is_primary, deliveryservice) WHERE is_primary; |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'origin' AND column_name = 'deliveryservice') THEN |
| -- |
| -- Name: origin_deliveryservice_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS origin_deliveryservice_fkey ON origin USING btree (deliveryservice); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'origin' AND column_name = 'coordinate') THEN |
| -- |
| -- Name: origin_coordinate_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS origin_coordinate_fkey ON origin USING btree (coordinate); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'origin' AND column_name = 'profile') THEN |
| -- |
| -- Name: origin_profile_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS origin_profile_fkey ON origin USING btree (profile); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'origin' AND column_name = 'cachegroup') THEN |
| -- |
| -- Name: origin_cachegroup_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS origin_cachegroup_fkey ON origin USING btree (cachegroup); |
| END IF; |
| |
| IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'origin' AND column_name = 'tenant') THEN |
| -- |
| -- Name: origin_tenant_fkey; Type: INDEX; Schema: public; Owner: traffic_ops |
| -- |
| |
| CREATE INDEX IF NOT EXISTS origin_tenant_fkey ON origin USING btree (tenant); |
| END IF; |
| END$$; |
| |
| |
| DO $$ |
| DECLARE table_names VARCHAR[] := CAST(ARRAY[ |
| 'api_capability', |
| 'asn', |
| 'cachegroup', |
| 'cachegroup_fallbacks', |
| 'cachegroup_localization_method', |
| 'cachegroup_parameter', |
| 'capability', |
| 'cdn', |
| 'coordinate', |
| 'deliveryservice', |
| 'deliveryservice_regex', |
| 'deliveryservice_request', |
| 'deliveryservice_request_comment', |
| 'deliveryservice_server', |
| 'deliveryservice_tmuser', |
| 'deliveryservices_required_capability', |
| 'division', |
| 'federation', |
| 'federation_deliveryservice', |
| 'federation_federation_resolver', |
| 'federation_resolver', |
| 'federation_tmuser', |
| 'hwinfo', |
| 'job', |
| 'log', |
| 'origin', |
| 'parameter', |
| 'phys_location', |
| 'profile', |
| 'profile_parameter', |
| 'regex', |
| 'region', |
| 'role', |
| 'role_capability', |
| 'server', |
| 'server_capability', |
| 'server_server_capability', |
| 'servercheck', |
| 'service_category', |
| 'snapshot', |
| 'staticdnsentry', |
| 'stats_summary', |
| 'status', |
| 'steering_target', |
| 'tenant', |
| 'tm_user', |
| 'to_extension', |
| 'topology', |
| 'topology_cachegroup', |
| 'topology_cachegroup_parents', |
| 'type' |
| ] AS VARCHAR[]); |
| table_name TEXT; |
| trigger_name TEXT := 'on_delete_current_timestamp'; |
| trigger_exists BOOLEAN; |
| |
| BEGIN |
| FOREACH table_name IN ARRAY table_names |
| LOOP |
| EXECUTE FORMAT('SELECT EXISTS ( |
| SELECT |
| FROM pg_catalog.pg_trigger |
| WHERE tgname = ''%s'' |
| AND tgrelid = CAST(''%s'' AS REGCLASS)) |
| ', |
| QUOTE_IDENT(trigger_name), |
| QUOTE_IDENT(table_name)) INTO trigger_exists; |
| IF NOT trigger_exists |
| THEN |
| EXECUTE FORMAT(' |
| CREATE TRIGGER %s |
| AFTER DELETE ON %s |
| FOR EACH ROW |
| EXECUTE PROCEDURE %s_last_updated(''%s''); |
| ', |
| QUOTE_IDENT(trigger_name), |
| QUOTE_IDENT(table_name), |
| QUOTE_IDENT(trigger_name), |
| QUOTE_IDENT(table_name) |
| ); |
| END IF; |
| IF table_name = 'topology' THEN |
| EXECUTE FORMAT(' |
| CREATE INDEX IF NOT EXISTS %s_last_updated_idx |
| ON %s (last_updated DESC NULLS LAST); |
| ', |
| QUOTE_IDENT(table_name), |
| QUOTE_IDENT(table_name) |
| ); |
| ELSIF table_name = 'phys_location' THEN |
| EXECUTE FORMAT(' |
| CREATE INDEX IF NOT EXISTS phys_location_last_updated_idx |
| ON %s (last_updated DESC NULLS LAST); |
| ', |
| QUOTE_IDENT(table_name) |
| ); |
| |
| ELSIF NOT (table_name = 'stats_summary' OR table_name = 'cachegroup_fallbacks' OR table_name = 'cachegroup_localization_method')THEN |
| EXECUTE FORMAT(' |
| CREATE INDEX IF NOT EXISTS %s_last_updated_idx |
| ON %s (last_updated DESC NULLS LAST); |
| ', |
| QUOTE_IDENT(table_name), |
| QUOTE_IDENT(table_name) |
| ); |
| END IF; |
| END LOOP; |
| END |
| $$; |
| |
| -- |
| -- Add on_update_current_timestamp TRIGGER to all tables |
| -- |
| DO $$ |
| DECLARE |
| table_names VARCHAR[] := CAST(ARRAY[ |
| 'api_capability', |
| 'asn', |
| 'cachegroup', |
| 'cachegroup_parameter', |
| 'capability', |
| 'cdn', |
| 'cdn_lock', |
| 'cdn_notification', |
| 'coordinate', |
| 'deliveryservice', |
| 'deliveryservice_regex', |
| 'deliveryservice_request', |
| 'deliveryservice_request_comment', |
| 'deliveryservice_server', |
| 'deliveryservice_tmuser', |
| 'division', |
| 'federation', |
| 'federation_deliveryservice', |
| 'federation_federation_resolver', |
| 'federation_resolver', |
| 'federation_tmuser', |
| 'hwinfo', |
| 'job', |
| 'log', |
| 'origin', |
| 'parameter', |
| 'phys_location', |
| 'profile', |
| 'profile_parameter', |
| 'regex', |
| 'region', |
| 'role', |
| 'role_capability', |
| 'server', |
| 'servercheck', |
| 'snapshot', |
| 'staticdnsentry', |
| 'status', |
| 'steering_target', |
| 'tenant', |
| 'tm_user', |
| 'topology', |
| 'topology_cachegroup', |
| 'topology_cachegroup_parents', |
| 'type' |
| ] AS VARCHAR[]); |
| table_name TEXT; |
| trigger_name TEXT := 'on_update_current_timestamp'; |
| trigger_exists BOOLEAN; |
| BEGIN |
| FOREACH table_name IN ARRAY table_names |
| LOOP |
| EXECUTE FORMAT('SELECT EXISTS ( |
| SELECT |
| FROM pg_catalog.pg_trigger |
| WHERE tgname = ''%s'' |
| AND tgrelid = CAST(''%s'' AS REGCLASS)) |
| ', |
| QUOTE_IDENT(trigger_name), |
| QUOTE_IDENT(table_name)) INTO trigger_exists; |
| IF NOT trigger_exists |
| THEN |
| EXECUTE FORMAT(' |
| CREATE TRIGGER %s |
| BEFORE UPDATE ON %s |
| FOR EACH ROW |
| EXECUTE PROCEDURE %s_last_updated(); |
| ', |
| QUOTE_IDENT(trigger_name), |
| QUOTE_IDENT(table_name), |
| QUOTE_IDENT(trigger_name) |
| ); |
| END IF; |
| END LOOP; |
| END$$; |
| |
| -- New code block to deallocate table_name variable to avoid identifier collision |
| DO $$ BEGIN |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_notification_cdn' AND table_name = 'cdn_notification') THEN |
| -- |
| -- Name: fk_notification_cdn; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cdn_notification |
| ADD CONSTRAINT fk_notification_cdn FOREIGN KEY (cdn) REFERENCES cdn(name) ON DELETE CASCADE ON UPDATE CASCADE; |
| |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_notification_user' AND table_name = 'cdn_notification') THEN |
| -- |
| -- Name: fk_notification_user; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cdn_notification |
| ADD CONSTRAINT fk_notification_user FOREIGN KEY ("user") REFERENCES tm_user(username) ON DELETE CASCADE ON UPDATE CASCADE; |
| |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_atsprofile_atsparameters_atsparameters1' AND table_name = 'profile_parameter') THEN |
| -- |
| -- Name: fk_atsprofile_atsparameters_atsparameters1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY profile_parameter |
| ADD CONSTRAINT fk_atsprofile_atsparameters_atsparameters1 FOREIGN KEY (parameter) REFERENCES parameter(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'deliveryservice_service_category_fkey' AND table_name = 'deliveryservice') THEN |
| -- |
| -- Name: deliveryservice_service_category_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice |
| ADD CONSTRAINT deliveryservice_service_category_fkey FOREIGN KEY (service_category) REFERENCES service_category(name) ON UPDATE CASCADE; |
| END IF; |
| |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'deliveryservice_tls_version_deliveryservice_fkey' AND table_name = 'deliveryservice_tls_version') THEN |
| -- |
| -- Name: deliveryservice_tls_version_deliveryservice_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_tls_version |
| ADD CONSTRAINT deliveryservice_tls_version_deliveryservice_fkey FOREIGN KEY (deliveryservice) REFERENCES deliveryservice(id) ON DELETE CASCADE ON UPDATE CASCADE; |
| END IF; |
| |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'ip_address_server_fkey' AND table_name = 'ip_address') THEN |
| -- |
| -- Name: ip_address_server_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY ip_address |
| ADD CONSTRAINT ip_address_server_fkey FOREIGN KEY (interface, server) REFERENCES interface(name, server) ON DELETE CASCADE ON UPDATE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'ip_address_interface_fkey' AND table_name = 'ip_address') THEN |
| -- |
| -- Name: ip_address_interface_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY ip_address |
| ADD CONSTRAINT ip_address_interface_fkey FOREIGN KEY (server) REFERENCES server(id) ON DELETE CASCADE ON UPDATE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'topology_cachegroup_cachegroup_fkey' AND table_name = 'topology_cachegroup') THEN |
| -- |
| -- Name: topology_cachegroup_cachegroup_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY topology_cachegroup |
| ADD CONSTRAINT topology_cachegroup_cachegroup_fkey FOREIGN KEY (cachegroup) REFERENCES cachegroup(name) ON UPDATE CASCADE ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'topology_cachegroup_topology_fkey' AND table_name = 'topology_cachegroup') THEN |
| -- |
| -- Name: topology_cachegroup_topology_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY topology_cachegroup |
| ADD CONSTRAINT topology_cachegroup_topology_fkey FOREIGN KEY (topology) REFERENCES topology(name) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'topology_cachegroup_parents_child_fkey' AND table_name = 'topology_cachegroup_parents') THEN |
| -- |
| -- Name: topology_cachegroup_parents_child_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY topology_cachegroup_parents |
| ADD CONSTRAINT topology_cachegroup_parents_child_fkey FOREIGN KEY (child) REFERENCES topology_cachegroup(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'topology_cachegroup_parents_parent_fkey' AND table_name = 'topology_cachegroup_parents') THEN |
| -- |
| -- Name: topology_cachegroup_parents_parent_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY topology_cachegroup_parents |
| ADD CONSTRAINT topology_cachegroup_parents_parent_fkey FOREIGN KEY (parent) REFERENCES topology_cachegroup(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_deliveryservice_id' AND table_name = 'deliveryservices_required_capability') THEN |
| -- |
| -- Name: fk_deliveryservice_id; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservices_required_capability |
| ADD CONSTRAINT fk_deliveryservice_id FOREIGN KEY (deliveryservice_id) REFERENCES deliveryservice(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_required_capability' AND table_name = 'deliveryservices_required_capability') THEN |
| -- |
| -- Name: fk_required_capability; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservices_required_capability |
| ADD CONSTRAINT fk_required_capability FOREIGN KEY (required_capability) REFERENCES server_capability(name) ON UPDATE CASCADE ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'deliveryservice_topology_fkey' AND table_name = 'deliveryservice') THEN |
| -- |
| -- Name: deliveryservice_topology_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice |
| ADD CONSTRAINT deliveryservice_topology_fkey FOREIGN KEY (topology) REFERENCES topology (name) ON UPDATE CASCADE ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_server' AND table_name = 'server_server_capability') THEN |
| -- |
| -- Name: fk_server; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY server_server_capability |
| ADD CONSTRAINT fk_server FOREIGN KEY (server) REFERENCES server(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_server_capability' AND table_name = 'server_server_capability') THEN |
| -- |
| -- Name: fk_server_capability; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY server_server_capability |
| ADD CONSTRAINT fk_server_capability FOREIGN KEY (server_capability) REFERENCES server_capability(name) ON UPDATE CASCADE ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_deliveryservice' AND table_name = 'deliveryservice_consistent_hash_query_param') THEN |
| -- |
| -- Name: fk_deliveryservice; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE deliveryservice_consistent_hash_query_param |
| ADD CONSTRAINT fk_deliveryservice FOREIGN KEY (deliveryservice_id) REFERENCES deliveryservice(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_atsprofile_atsparameters_atsprofile1' AND table_name = 'profile_parameter') THEN |
| -- |
| -- Name: fk_atsprofile_atsparameters_atsprofile1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY profile_parameter |
| ADD CONSTRAINT fk_atsprofile_atsparameters_atsprofile1 FOREIGN KEY (profile) REFERENCES profile(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_cdn1' AND table_name = 'deliveryservice') THEN |
| -- |
| -- Name: fk_cdn1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice |
| ADD CONSTRAINT fk_cdn1 FOREIGN KEY (cdn_id) REFERENCES cdn(id) ON UPDATE RESTRICT ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_cdn2' AND table_name = 'server') THEN |
| -- |
| -- Name: fk_cdn2; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY server |
| ADD CONSTRAINT fk_cdn2 FOREIGN KEY (cdn_id) REFERENCES cdn(id) ON UPDATE RESTRICT ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_cg_1' AND table_name = 'cachegroup') THEN |
| -- |
| -- Name: fk_cg_1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup |
| ADD CONSTRAINT fk_cg_1 FOREIGN KEY (parent_cachegroup_id) REFERENCES cachegroup(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_cg_param_cachegroup1' AND table_name = 'cachegroup_parameter') THEN |
| -- |
| -- Name: fk_cg_param_cachegroup1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup_parameter |
| ADD CONSTRAINT fk_cg_param_cachegroup1 FOREIGN KEY (cachegroup) REFERENCES cachegroup(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_cg_secondary' AND table_name = 'cachegroup') THEN |
| -- |
| -- Name: fk_cg_secondary; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup |
| ADD CONSTRAINT fk_cg_secondary FOREIGN KEY (secondary_parent_cachegroup_id) REFERENCES cachegroup(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_cg_type1' AND table_name = 'cachegroup') THEN |
| -- |
| -- Name: fk_cg_type1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup |
| ADD CONSTRAINT fk_cg_type1 FOREIGN KEY (type) REFERENCES type(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_contentserver_atsprofile1' AND table_name = 'server') THEN |
| -- |
| -- Name: fk_contentserver_atsprofile1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY server |
| ADD CONSTRAINT fk_contentserver_atsprofile1 FOREIGN KEY (profile) REFERENCES profile(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_contentserver_contentserverstatus1' AND table_name = 'server') THEN |
| -- |
| -- Name: fk_contentserver_contentserverstatus1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY server |
| ADD CONSTRAINT fk_contentserver_contentserverstatus1 FOREIGN KEY (status) REFERENCES status(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_contentserver_contentservertype1' AND table_name = 'server') THEN |
| -- |
| -- Name: fk_contentserver_contentservertype1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY server |
| ADD CONSTRAINT fk_contentserver_contentservertype1 FOREIGN KEY (type) REFERENCES type(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_contentserver_phys_location1' AND table_name = 'server') THEN |
| -- |
| -- Name: fk_contentserver_phys_location1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY server |
| ADD CONSTRAINT fk_contentserver_phys_location1 FOREIGN KEY (phys_location) REFERENCES phys_location(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_cran_cachegroup1' AND table_name = 'asn') THEN |
| -- |
| -- Name: fk_cran_cachegroup1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY asn |
| ADD CONSTRAINT fk_cran_cachegroup1 FOREIGN KEY (cachegroup) REFERENCES cachegroup(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_deliveryservice_profile1' AND table_name = 'deliveryservice') THEN |
| -- |
| -- Name: fk_deliveryservice_profile1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice |
| ADD CONSTRAINT fk_deliveryservice_profile1 FOREIGN KEY (profile) REFERENCES profile(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_deliveryservice_type1' AND table_name = 'deliveryservice') THEN |
| -- |
| -- Name: fk_deliveryservice_type1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice |
| ADD CONSTRAINT fk_deliveryservice_type1 FOREIGN KEY (type) REFERENCES type(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_ds_to_cs_contentserver1' AND table_name = 'deliveryservice_server') THEN |
| -- |
| -- Name: fk_ds_to_cs_contentserver1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_server |
| ADD CONSTRAINT fk_ds_to_cs_contentserver1 FOREIGN KEY (server) REFERENCES server(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_ds_to_cs_deliveryservice1' AND table_name = 'deliveryservice_server') THEN |
| -- |
| -- Name: fk_ds_to_cs_deliveryservice1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_server |
| ADD CONSTRAINT fk_ds_to_cs_deliveryservice1 FOREIGN KEY (deliveryservice) REFERENCES deliveryservice(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_ds_to_regex_deliveryservice1' AND table_name = 'deliveryservice_regex') THEN |
| -- |
| -- Name: fk_ds_to_regex_deliveryservice1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_regex |
| ADD CONSTRAINT fk_ds_to_regex_deliveryservice1 FOREIGN KEY (deliveryservice) REFERENCES deliveryservice(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_ds_to_regex_regex1' AND table_name = 'deliveryservice_regex') THEN |
| -- |
| -- Name: fk_ds_to_regex_regex1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_regex |
| ADD CONSTRAINT fk_ds_to_regex_regex1 FOREIGN KEY (regex) REFERENCES regex(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_ext_type' AND table_name = 'to_extension') THEN |
| -- |
| -- Name: fk_ext_type; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY to_extension |
| ADD CONSTRAINT fk_ext_type FOREIGN KEY (type) REFERENCES type(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_federation_federation_resolver1' AND table_name = 'federation_federation_resolver') THEN |
| -- |
| -- Name: fk_federation_federation_resolver1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation_federation_resolver |
| ADD CONSTRAINT fk_federation_federation_resolver1 FOREIGN KEY (federation) REFERENCES federation(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_federation_mapping_type' AND table_name = 'federation_resolver') THEN |
| -- |
| -- Name: fk_federation_mapping_type; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation_resolver |
| ADD CONSTRAINT fk_federation_mapping_type FOREIGN KEY (type) REFERENCES type(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_federation_resolver_to_fed1' AND table_name = 'federation_federation_resolver') THEN |
| -- |
| -- Name: fk_federation_resolver_to_fed1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation_federation_resolver |
| ADD CONSTRAINT fk_federation_resolver_to_fed1 FOREIGN KEY (federation_resolver) REFERENCES federation_resolver(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_federation_tmuser_federation' AND table_name = 'federation_tmuser') THEN |
| -- |
| -- Name: fk_federation_tmuser_federation; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation_tmuser |
| ADD CONSTRAINT fk_federation_tmuser_federation FOREIGN KEY (federation) REFERENCES federation(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_federation_tmuser_role' AND table_name = 'federation_tmuser') THEN |
| -- |
| -- Name: fk_federation_tmuser_role; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation_tmuser |
| ADD CONSTRAINT fk_federation_tmuser_role FOREIGN KEY (role) REFERENCES role(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_federation_tmuser_tmuser' AND table_name = 'federation_tmuser') THEN |
| -- |
| -- Name: fk_federation_tmuser_tmuser; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation_tmuser |
| ADD CONSTRAINT fk_federation_tmuser_tmuser FOREIGN KEY (tm_user) REFERENCES tm_user(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_federation_to_ds1' AND table_name = 'federation_deliveryservice') THEN |
| -- |
| -- Name: fk_federation_to_ds1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation_deliveryservice |
| ADD CONSTRAINT fk_federation_to_ds1 FOREIGN KEY (deliveryservice) REFERENCES deliveryservice(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_federation_to_fed1' AND table_name = 'federation_deliveryservice') THEN |
| -- |
| -- Name: fk_federation_to_fed1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY federation_deliveryservice |
| ADD CONSTRAINT fk_federation_to_fed1 FOREIGN KEY (federation) REFERENCES federation(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_hwinfo1' AND table_name = 'hwinfo') THEN |
| -- |
| -- Name: fk_hwinfo1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY hwinfo |
| ADD CONSTRAINT fk_hwinfo1 FOREIGN KEY (serverid) REFERENCES server(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'interface_server_fkey' AND table_name = 'interface') THEN |
| -- |
| -- Name: interface_server_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE interface |
| ADD CONSTRAINT interface_server_fkey FOREIGN KEY (server) REFERENCES server(id) ON DELETE CASCADE ON UPDATE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_job_deliveryservice1' AND table_name = 'job') THEN |
| -- |
| -- Name: fk_job_deliveryservice1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY job |
| ADD CONSTRAINT fk_job_deliveryservice1 FOREIGN KEY (job_deliveryservice) REFERENCES deliveryservice(id) ON DELETE CASCADE ON UPDATE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_job_user_id1' AND table_name = 'job') THEN |
| -- |
| -- Name: fk_job_user_id1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY job |
| ADD CONSTRAINT fk_job_user_id1 FOREIGN KEY (job_user) REFERENCES tm_user(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_log_1' AND table_name = 'log') THEN |
| -- |
| -- Name: fk_log_1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY log |
| ADD CONSTRAINT fk_log_1 FOREIGN KEY (tm_user) REFERENCES tm_user(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_parameter' AND table_name = 'cachegroup_parameter') THEN |
| -- |
| -- Name: fk_parameter; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup_parameter |
| ADD CONSTRAINT fk_parameter FOREIGN KEY (parameter) REFERENCES parameter(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_phys_location_region' AND table_name = 'phys_location') THEN |
| -- |
| -- Name: fk_phys_location_region; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY phys_location |
| ADD CONSTRAINT fk_phys_location_region FOREIGN KEY (region) REFERENCES region(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_regex_type1' AND table_name = 'regex') THEN |
| -- |
| -- Name: fk_regex_type1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY regex |
| ADD CONSTRAINT fk_regex_type1 FOREIGN KEY (type) REFERENCES type(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_region_division1' AND table_name = 'region') THEN |
| -- |
| -- Name: fk_region_division1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY region |
| ADD CONSTRAINT fk_region_division1 FOREIGN KEY (division) REFERENCES division(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_server_cachegroup1' AND table_name = 'server') THEN |
| -- |
| -- Name: fk_server_cachegroup1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY server |
| ADD CONSTRAINT fk_server_cachegroup1 FOREIGN KEY (cachegroup) REFERENCES cachegroup(id) ON UPDATE RESTRICT ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_serverstatus_server1' AND table_name = 'servercheck') THEN |
| -- |
| -- Name: fk_serverstatus_server1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY servercheck |
| ADD CONSTRAINT fk_serverstatus_server1 FOREIGN KEY (server) REFERENCES server(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_staticdnsentry_cachegroup1' AND table_name = 'staticdnsentry') THEN |
| -- |
| -- Name: fk_staticdnsentry_cachegroup1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY staticdnsentry |
| ADD CONSTRAINT fk_staticdnsentry_cachegroup1 FOREIGN KEY (cachegroup) REFERENCES cachegroup(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_staticdnsentry_ds' AND table_name = 'staticdnsentry') THEN |
| -- |
| -- Name: fk_staticdnsentry_ds; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY staticdnsentry |
| ADD CONSTRAINT fk_staticdnsentry_ds FOREIGN KEY (deliveryservice) REFERENCES deliveryservice(id) ON DELETE CASCADE ON UPDATE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_staticdnsentry_type' AND table_name = 'staticdnsentry') THEN |
| -- |
| -- Name: fk_staticdnsentry_type; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY staticdnsentry |
| ADD CONSTRAINT fk_staticdnsentry_type FOREIGN KEY (type) REFERENCES type(id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_steering_target_delivery_service' AND table_name = 'steering_target') THEN |
| -- |
| -- Name: fk_steering_target_delivery_service; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY steering_target |
| ADD CONSTRAINT fk_steering_target_delivery_service FOREIGN KEY (deliveryservice) REFERENCES deliveryservice(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_steering_target_target' AND table_name = 'steering_target') THEN |
| -- |
| -- Name: fk_steering_target_target; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY steering_target |
| ADD CONSTRAINT fk_steering_target_target FOREIGN KEY (target) REFERENCES deliveryservice(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_tm_user_ds' AND table_name = 'deliveryservice_tmuser') THEN |
| -- |
| -- Name: fk_tm_user_ds; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_tmuser |
| ADD CONSTRAINT fk_tm_user_ds FOREIGN KEY (deliveryservice) REFERENCES deliveryservice(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_tm_user_id' AND table_name = 'deliveryservice_tmuser') THEN |
| -- |
| -- Name: fk_tm_user_id; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_tmuser |
| ADD CONSTRAINT fk_tm_user_id FOREIGN KEY (tm_user_id) REFERENCES tm_user(id) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_user_1' AND table_name = 'tm_user') THEN |
| -- |
| -- Name: fk_user_1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY tm_user |
| ADD CONSTRAINT fk_user_1 FOREIGN KEY (role) REFERENCES role(id) ON DELETE SET NULL; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_capability' AND table_name = 'api_capability') THEN |
| -- |
| -- Name: fk_capability; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY api_capability |
| ADD CONSTRAINT fk_capability FOREIGN KEY (capability) REFERENCES capability (name) ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'steering_target_type_fkey' AND table_name = 'steering_target') THEN |
| -- |
| -- Name: steering_target_type_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY steering_target |
| ADD CONSTRAINT steering_target_type_fkey FOREIGN KEY (type) REFERENCES type (id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_tenantid' AND table_name = 'deliveryservice') THEN |
| -- |
| -- Name: fk_tenantid; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice |
| ADD CONSTRAINT fk_tenantid FOREIGN KEY (tenant_id) REFERENCES tenant (id) MATCH FULL; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_author' AND table_name = 'deliveryservice_request') THEN |
| -- |
| -- Name: fk_author; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_request |
| ADD CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES tm_user(id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_assignee' AND table_name = 'deliveryservice_request') THEN |
| -- |
| -- Name: fk_assignee; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_request |
| ADD CONSTRAINT fk_assignee FOREIGN KEY (assignee_id) REFERENCES tm_user(id) ON DELETE SET NULL; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_last_edited_by' AND table_name = 'deliveryservice_request') THEN |
| -- |
| -- Name: fk_last_edited_by; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_request |
| ADD CONSTRAINT fk_last_edited_by FOREIGN KEY (last_edited_by_id) REFERENCES tm_user (id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_author' AND table_name = 'deliveryservice_request_comment') THEN |
| -- |
| -- Name: fk_author; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_request_comment |
| ADD CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES tm_user (id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'origin_profile_fkey' AND table_name = 'origin') THEN |
| -- |
| -- Name: origin_profile_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY origin |
| ADD CONSTRAINT origin_profile_fkey FOREIGN KEY (profile) REFERENCES profile (id) ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'origin_deliveryservice_fkey' AND table_name = 'origin') THEN |
| -- |
| -- Name: origin_deliveryservice_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY origin |
| ADD CONSTRAINT origin_deliveryservice_fkey FOREIGN KEY (deliveryservice) REFERENCES deliveryservice (id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'origin_coordinate_fkey' AND table_name = 'origin') THEN |
| -- |
| -- Name: origin_coordinate_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY origin |
| ADD CONSTRAINT origin_coordinate_fkey FOREIGN KEY (coordinate) REFERENCES coordinate (id) ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'origin_cachegroup_fkey' AND table_name = 'origin') THEN |
| -- |
| -- Name: origin_cachegroup_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY origin |
| ADD CONSTRAINT origin_cachegroup_fkey FOREIGN KEY (cachegroup) REFERENCES cachegroup (id) ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'origin_tenant_fkey' AND table_name = 'origin') THEN |
| -- |
| -- Name: origin_tenant_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY origin |
| ADD CONSTRAINT origin_tenant_fkey FOREIGN KEY (tenant) REFERENCES tenant (id) ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fkey_lock_cdn' AND table_name = 'cdn_lock') THEN |
| -- |
| -- Name: fk_lock_cdn; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cdn_lock |
| ADD CONSTRAINT fk_lock_cdn FOREIGN KEY ("cdn") REFERENCES cdn(name) ON DELETE CASCADE ON UPDATE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fkey_lock_username' AND table_name = 'cdn_lock') THEN |
| -- |
| -- Name: fk_lock_username; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cdn_lock |
| ADD CONSTRAINT fk_lock_username FOREIGN KEY ("username") REFERENCES tm_user(username) ON DELETE CASCADE ON UPDATE CASCADE; |
| END IF; |
| |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'cachegroup_coordinate_fkey' AND table_name = 'cachegroup') THEN |
| -- |
| -- Name: cachegroup_coordinate_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup |
| ADD CONSTRAINT cachegroup_coordinate_fkey FOREIGN KEY (coordinate) REFERENCES coordinate (id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_primary_cg' AND table_name = 'cachegroup_fallbacks') THEN |
| -- |
| -- Name: fk_primary_cg; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup_fallbacks |
| ADD CONSTRAINT fk_primary_cg FOREIGN KEY (primary_cg) REFERENCES cachegroup (id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_backup_cg' AND table_name = 'cachegroup_fallbacks') THEN |
| -- |
| -- Name: fk_backup_cg; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup_fallbacks |
| ADD CONSTRAINT fk_backup_cg FOREIGN KEY (backup_cg) REFERENCES cachegroup (id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'cachegroup_localization_method_cachegroup_fkey' AND table_name = 'cachegroup_localization_method') THEN |
| -- |
| -- Name: cachegroup_localization_method_cachegroup_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY cachegroup_localization_method |
| ADD CONSTRAINT cachegroup_localization_method_cachegroup_fkey FOREIGN KEY (cachegroup) REFERENCES cachegroup (id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_deliveryservice_request' AND table_name = 'deliveryservice_request_comment') THEN |
| -- |
| -- Name: fk_deliveryservice_request; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY deliveryservice_request_comment |
| ADD CONSTRAINT fk_deliveryservice_request FOREIGN KEY (deliveryservice_request_id) REFERENCES deliveryservice_request (id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_cdn1' AND table_name = 'profile') THEN |
| -- |
| -- Name: fk_cdn1; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY profile |
| ADD CONSTRAINT fk_cdn1 FOREIGN KEY (cdn) REFERENCES cdn (id) ON UPDATE RESTRICT ON DELETE RESTRICT; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_role_id' AND table_name = 'role_capability') THEN |
| -- |
| -- Name: fk_role_id; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY role_capability |
| ADD CONSTRAINT fk_role_id FOREIGN KEY (role_id) REFERENCES role (id) ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'snapshot_cdn_fkey' AND table_name = 'snapshot') THEN |
| -- |
| -- Name: snapshot_cdn_fkey; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY snapshot |
| ADD CONSTRAINT snapshot_cdn_fkey FOREIGN KEY (cdn) REFERENCES cdn (name) ON UPDATE CASCADE ON DELETE CASCADE; |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_parentid' AND table_name = 'tenant') THEN |
| -- |
| -- Name: fk_parentid; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY tenant |
| ADD CONSTRAINT fk_parentid FOREIGN KEY (parent_id) REFERENCES tenant (id); |
| END IF; |
| |
| IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'fk_tenantid' AND table_name = 'tm_user') THEN |
| -- |
| -- Name: fk_tenantid; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops |
| -- |
| |
| ALTER TABLE ONLY tm_user |
| ADD CONSTRAINT fk_tenantid FOREIGN KEY (tenant_id) REFERENCES tenant (id) MATCH FULL; |
| END IF; |
| END$$; |
| |
| |
| -- |
| -- Name: public; Type: ACL; Schema: -; Owner: traffic_ops |
| -- |
| |
| REVOKE ALL ON SCHEMA public FROM PUBLIC; |
| REVOKE ALL ON SCHEMA public FROM traffic_ops; |
| GRANT ALL ON SCHEMA public TO traffic_ops; |
| GRANT ALL ON SCHEMA public TO PUBLIC; |
| |
| |
| -- |
| -- PostgreSQL database dump complete |
| -- |