dev/traffic_ops/seed.psql

/* 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.
*/

INSERT INTO tm_user (
	username,
	tenant_id,
	"role",
	local_passwd,
	confirm_local_passwd,
	email,
	full_name
) VALUES (
	'admin',
	(SELECT id FROM tenant WHERE "name" = 'root'),
	(SELECT id FROM "role" WHERE "name" = 'admin'),
	-- 'twelve12' encrypted with SCRYPT
	'SCRYPT:16384:8:1:p0Bppp/6IBeYxSwdLuYddsdMLBU/BNSlLY6fSIF7H1XW4eTbNVeMPVm7TuTEG4FM8PbqLlVwi8sPy8ZJznAlaQ==:sRcHWGe43mm/uEmXTIw37GcLEQZTlWAdf4vJqK8f0MDh8P+8gXoNx+nxWyb3r/0Bh+yyg0g/dUvti/ePZJL+Jw==',
	'SCRYPT:16384:8:1:p0Bppp/6IBeYxSwdLuYddsdMLBU/BNSlLY6fSIF7H1XW4eTbNVeMPVm7TuTEG4FM8PbqLlVwi8sPy8ZJznAlaQ==:sRcHWGe43mm/uEmXTIw37GcLEQZTlWAdf4vJqK8f0MDh8P+8gXoNx+nxWyb3r/0Bh+yyg0g/dUvti/ePZJL+Jw==',
	'admin@no-reply.atc.test',
	'Development Admin User'
)
ON CONFLICT DO NOTHING;

INSERT INTO cdn (
	"name",
	domain_name
) VALUES (
	'dev',
	'ciab.test'
)
ON CONFLICT DO NOTHING;

INSERT INTO snapshot (
	cdn,
	crconfig,
	monitoring
) VALUES (
	'dev',
	'{"config":{"coveragezone.polling.url":"file:///go/src/github.com/apache/trafficcontrol/dev/traffic_router/czf.json","dnssec.enabled":"false","domain_name":"ciab.test","geolocation.polling.url":"file:///go/src/github.com/apache/trafficcontrol/traffic_router/core/src/test/resources/geo/GeoLite2-City.mmdb.gz"},"contentServers":{"edge":{"cacheGroup":"dev","fqdn":"edge.dev.ciab.test","hashCount":999,"hashId":"edge","httpsPort":443,"interfaceName":"eth0","ip":"129.0.0.2","ip6":"","locationId":"dev","port":80,"profile":"EDGE_dev","status":"REPORTED","type":"EDGE","deliveryServices":{"dev-ds":["edge.dev-ds.ciab.test"]},"routingDisabled":0}},"contentRouters":{"trafficrouter":{"fqdn":"trafficrouter.dev.ciab.test","httpsPort":443,"ip":"","ip6":"","location":"tr-cg","port":80,"profile":"CCR_dev","status":"ONLINE"}},"deliveryServices":{"dev-ds":{"anonymousBlockingEnabled":"false","coverageZoneOnly":"false","deepCachingType":"NEVER","dispersion":{"limit":1,"shuffled":"true"},"domains":["dev-ds.ciab.test"],"ecsEnabled":"false","geolocationProvider":"maxmindGeolocationService","ip6RoutingEnabled":"false","matchsets":[{"protocol":"HTTP","matchlist":[{"regex":".*\\.dev-ds\\..*","match-type":"HOST"}]}],"missLocation":{"lat":1,"long":1},"protocol":{"acceptHttps":"false","redirectToHttps":"false"},"regionalGeoBlocking":"false","routingName":"cdn","soa":{"admin":"traffic_ops","expire":"604800","minimum":"30","refresh":"28800","retry":"7200"},"sslEnabled":"false","ttls":{"A":"","AAAA":"","NS":"3600","SOA":"86400"}}},"edgeLocations":{"dev":{"latitude":1,"longitude":1,"backupLocations":{"fallbackToClosest":"true"},"localizationMethods":["GEO","CZ","DEEP_CZ"]}},"trafficRouterLocations":{"tr-cg":{"latitude":1,"longitude":1,"backupLocations":{"fallbackToClosest":"false"},"localizationMethods":["GEO","CZ","DEEP_CZ"]}},"monitors":{"trafficmonitor":{"fqdn":"trafficmonitor.dev.ciab.test","httpsPort":null,"ip":"129.0.0.1","ip6":"","location":"dev","port":80,"profile":"RASCAL_TM_dev","status":"ONLINE"}},"stats":{"CDN_name":"dev","date":1649361181,"tm_host":"trafficops","tm_user":"admin","tm_version":"development"}}',
	'{"trafficServers":[{"profile":"EDGE_dev","status":"REPORTED","port":80,"cachegroup":"dev","hostname":"edge","fqdn":"edge.dev.ciab.test","interfaces":[{"ipAddresses":[{"address":"129.0.0.2","gateway":null,"serviceAddress":true}],"maxBandwidth":null,"monitor":true,"mtu":1500,"name":"eth0"}],"type":"EDGE","hashid":"","deliveryServices":[{"xmlId":"dev-ds"}]}],"trafficMonitors":[{"profile":"RASCAL_TM_dev","status":"ONLINE","port":80,"cachegroup":"dev","hostname":"trafficmonitor","fqdn":"trafficmonitor.dev.ciab.test","ip":"129.0.0.1","ip6":""}],"cacheGroups":[{"name":"dev","coordinates":{"latitude":1,"longitude":1}},{"name":"tr-cg","coordinates":{"latitude":1,"longitude":1}}],"profiles":[{"name":"CCR_dev","type":"CCR","parameters":null},{"name":"EDGE_dev","type":"EDGE","parameters":{"health.polling.format":"stats_over_http","health.polling.url":"http://edge:8080/_stats?application=\\u0026inf.name=${interface_name}"}}],"deliveryServices":[{"xmlId":"dev-ds","totalTpsThreshold":0,"status":"REPORTED","totalKbpsThreshold":0,"type":"HTTP","topology":"","hostRegexes":[".*\\.dev-ds\\..*"]}],"config":{"health.polling.interval":6000,"heartbeat.polling.interval":3000,"peers.polling.interval":3000,"tm.polling.interval":2000},"topologies":{}}'
)
ON CONFLICT DO NOTHING;

INSERT INTO profile (
	"name",
	description,
	"type",
	cdn
) VALUES (
	'RASCAL_TM_dev',
	'dev TM Profile',
	'TM_PROFILE',
	(SELECT id FROM cdn WHERE "name" = 'dev')
), (
	'EDGE_dev',
	'dev edge Profile',
	'ATS_PROFILE',
	(SELECT id FROM cdn WHERE "name" = 'dev')
), (
	'CCR_dev',
	'dev Traffic Router Profile',
	'TR_PROFILE',
	(SELECT id FROM cdn WHERE "name" = 'dev')
)
ON CONFLICT DO NOTHING;

INSERT INTO parameter (
	config_file,
	"name",
	"value"
) VALUES (
	'rascal-config.txt',
	'health.polling.interval',
	'6000'
), (
	'rascal-config.txt',
	'heartbeat.polling.interval',
	'3000'
), (
	'rascal-config.txt',
	'peers.polling.interval',
	'3000'
), (
	'rascal-config.txt',
	'tm.polling.interval',
	'2000'
), (
	'rascal.properties',
	'health.polling.url',
	'http://edge:8080/_stats?application=&inf.name=${interface_name}'
), (
	'rascal.properties',
	'health.polling.format',
	'stats_over_http'
), (
	'CRConfig.json',
	'geolocation.polling.url',
	'file:///go/src/github.com/apache/trafficcontrol/traffic_router/core/src/test/resources/geo/GeoLite2-City.mmdb.gz'
), (
	'CRConfig.json',
	'coveragezone.polling.url',
	'file:///go/src/github.com/apache/trafficcontrol/dev/traffic_router/czf.json'
), (
	'global',
	'tm.url',
	'https://trafficops'
)
ON CONFLICT DO NOTHING;

INSERT INTO profile_parameter
SELECT profile.id, parameter.id
FROM profile, parameter
WHERE parameter.config_file = 'rascal-config.txt'
AND parameter."name" LIKE '%.polling.%'
AND profile."name" = 'RASCAL_TM_dev'
ON CONFLICT DO NOTHING;

INSERT INTO profile_parameter
SELECT profile.id, parameter.id
FROM profile, parameter
WHERE parameter.config_file = 'rascal.properties'
AND profile."name" = 'EDGE_dev'
ON CONFLICT DO NOTHING;

INSERT INTO profile_parameter
SELECT profile.id, parameter.id
FROM profile, parameter
WHERE parameter.config_file = 'CRConfig.json'
AND profile."name" = 'CCR_dev'
ON CONFLICT DO NOTHING;

INSERT INTO division ("name") VALUES ('dev') ON CONFLICT DO NOTHING;
INSERT INTO region (
	"name",
	division
) VALUES (
	'dev',
	(SELECT id FROM division WHERE "name" = 'dev')
)
ON CONFLICT DO NOTHING;
INSERT INTO phys_location (
	"name",
	short_name,
	address,
	city,
	state,
	zip,
	region
) VALUES (
	'dev',
	'dev',
	'address',
	'city',
	'state',
	'zip',
	(SELECT id from region WHERE "name" = 'dev')
)
ON CONFLICT DO NOTHING;

INSERT INTO coordinate (
	"name",
	latitude,
	longitude
) VALUES (
	'dev',
	1,
	1
)
ON CONFLICT DO NOTHING;

INSERT INTO cachegroup (
	"name",
	short_name,
	"type",
	coordinate
) VALUES (
	'dev',
	'dev',
	(SELECT id FROM "type" WHERE "name" = 'EDGE_LOC'),
	(SELECT id FROM coordinate WHERE "name" = 'dev')
), (
	'tr-cg',
	'tr-cg',
	(SELECT id FROM "type" WHERE "name" = 'TR_LOC'),
	(SELECT id FROM coordinate WHERE "name" = 'dev')
)
ON CONFLICT DO NOTHING;

INSERT INTO "server" (
	host_name,
	domain_name,
	tcp_port,
	https_port,
	phys_location,
	cachegroup,
	type,
	status,
	profile,
	cdn_id
) VALUES (
	'trafficmonitor',
	'dev.ciab.test',
	80,
	NULL,
	(SELECT id FROM phys_location WHERE "name" = 'dev'),
	(SELECT id FROM cachegroup WHERE "name" = 'dev'),
	(SELECT id FROM "type" WHERE "name" = 'RASCAL'),
	(SELECT id FROM status WHERE "name" = 'ONLINE'),
	(SELECT id FROM profile WHERE "name"= 'RASCAL_TM_dev'),
	(SELECT id FROM cdn WHERE "name" = 'dev')
), (
	'edge',
	'dev.ciab.test',
	80,
	443,
	(SELECT id FROM phys_location WHERE "name" = 'dev'),
	(SELECT id FROM cachegroup WHERE "name" = 'dev'),
	(SELECT id FROM "type" WHERE "name" = 'EDGE'),
	(SELECT id FROM status WHERE "name" = 'REPORTED'),
	(SELECT id FROM profile WHERE "name"= 'EDGE_dev'),
	(SELECT id FROM cdn WHERE "name" = 'dev')
), (
	'trafficrouter',
	'dev.ciab.test',
	80,
	443,
	(SELECT id FROM phys_location WHERE "name" = 'dev'),
	(SELECT id FROM cachegroup WHERE "name" = 'tr-cg'),
	(SELECT id FROM "type" WHERE "name" = 'CCR'),
	(SELECT id FROM status WHERE "name" = 'ONLINE'),
	(SELECT id FROM profile WHERE "name" = 'CCR_dev'),
	(SELECT id FROM cdn WHERE "name" = 'dev')
)
ON CONFLICT DO NOTHING;

INSERT into server_profile(server, profile_name, priority)
SELECT s.id, p.name, 0
FROM server AS s
    JOIN profile p ON p.id=s.profile;

INSERT INTO interface (
	monitor,
	"name",
	"server"
) VALUES (
	FALSE,
	'eth0',
	(SELECT id FROM "server" WHERE host_name = 'trafficmonitor' LIMIT 1)
), (
	TRUE,
	'eth0',
	(SELECT id FROM "server" WHERE host_name = 'edge' LIMIT 1)
)
ON CONFLICT DO NOTHING;

INSERT INTO ip_address (
	address,
	interface,
	"server",
	service_address
) VALUES (
	'129.0.0.1'::inet,
	'eth0',
	(SELECT id FROM "server" WHERE host_name = 'trafficmonitor' LIMIT 1),
	TRUE
), (
	'129.0.0.2'::inet,
	'eth0',
	(SELECT id FROM "server" WHERE host_name = 'edge' LIMIT 1),
	TRUE
)
ON CONFLICT DO NOTHING;

INSERT INTO deliveryservice (
	xml_id,
	active,
	dscp,
	qstring_ignore,
	"type",
	cdn_id,
	display_name,
	tenant_id,
	miss_lat,
	miss_long
) VALUES (
	'dev-ds',
	TRUE,
	1,
	0,
	(SELECT id FROM "type" WHERE "name" = 'HTTP'),
	(SELECT id FROM cdn WHERE "name" = 'dev'),
	'dev',
	(SELECT id FROM tenant WHERE "name" = 'root'),
	1,
	1
)
ON CONFLICT DO NOTHING;

INSERT INTO regex (
	"type",
	pattern
) VALUES (
	(SELECT id FROM "type" WHERE "name" = 'HOST_REGEXP'),
	'.*\.dev-ds\..*'
)
ON CONFLICT DO NOTHING;

INSERT INTO deliveryservice_regex (
	deliveryservice,
	regex,
	set_number
) VALUES (
	(SELECT id FROM deliveryservice WHERE xml_id = 'dev-ds') ,
	(SELECT id FROM regex WHERE pattern = '.*\.dev-ds\..*' LIMIT 1),
	0
)
ON CONFLICT DO NOTHING;

INSERT INTO origin (
	"name",
	fqdn,
	protocol,
	is_primary,
	port,
	deliveryservice,
	tenant
) VALUES (
	'dev',
	'origin.infra.ciab.test',
	'http',
	TRUE,
	80,
	(SELECT id FROM deliveryservice WHERE xml_id = 'dev-ds'),
	(SELECT id FROM tenant WHERE "name" = 'root')
)
ON CONFLICT DO NOTHING;

INSERT INTO deliveryservice_server (
	deliveryservice,
	"server"
) VALUES (
	(SELECT id FROM deliveryservice WHERE xml_id = 'dev-ds'),
	(SELECT id FROM "server" WHERE host_name = 'edge')
)
ON CONFLICT DO NOTHING;