You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafficcontrol.apache.org by zr...@apache.org on 2022/05/16 15:26:14 UTC
[trafficcontrol] branch master updated: Idempotentiate Traffic Vault create_tables.sql (#6717)
This is an automated email from the ASF dual-hosted git repository.
zrhoffman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/trafficcontrol.git
The following commit(s) were added to refs/heads/master by this push:
new c66a5f54c4 Idempotentiate Traffic Vault create_tables.sql (#6717)
c66a5f54c4 is described below
commit c66a5f54c46bbba869ae53016ae848e1ace14239
Author: David Cole <da...@sky.uk>
AuthorDate: Mon May 16 16:26:08 2022 +0100
Idempotentiate Traffic Vault create_tables.sql (#6717)
* Idempotentiate TV create_tables.sql
* CHANGELOG
* Correcting issues identified by zrhoffman
---
CHANGELOG.md | 1 +
traffic_ops/app/db/trafficvault/create_tables.sql | 112 ++++++++++++---------
.../app/db/trafficvault/test/run-tvdb-test.sh | 6 ++
3 files changed, 71 insertions(+), 48 deletions(-)
diff --git a/CHANGELOG.md b/CHANGELOG.md
index db7aa0e5de..768c04969c 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -50,6 +50,7 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/).
- [#6780](https://github.com/apache/trafficcontrol/issues/6780) Fixed t3c to use secondary parents when there are no primary parents available.
- Correction where using the placeholder __HOSTNAME__ in "unknown" files (others than the defaults ones), was being replaced by the full FQDN instead of the shot hostname.
- [#6800](https://github.com/apache/trafficcontrol/issues/6800) Fixed incorrect error message for `/server/details` associated with query parameters.
+- [#6712](https://github.com/apache/trafficcontrol/issues/6712) - Fixed error when loading the Traffic Vault schema from `create_tables.sql` more than once.
### Removed
- Remove traffic\_portal dependencies to mitigate `npm audit` issues, specifically `grunt-concurrent`, `grunt-contrib-concat`, `grunt-contrib-cssmin`, `grunt-contrib-jsmin`, `grunt-contrib-uglify`, `grunt-contrib-htmlmin`, `grunt-newer`, and `grunt-wiredep`
diff --git a/traffic_ops/app/db/trafficvault/create_tables.sql b/traffic_ops/app/db/trafficvault/create_tables.sql
index 2fcb97e8bd..20c2a559b0 100644
--- a/traffic_ops/app/db/trafficvault/create_tables.sql
+++ b/traffic_ops/app/db/trafficvault/create_tables.sql
@@ -110,86 +110,102 @@ CREATE TABLE IF NOT EXISTS url_sig_key (
ALTER TABLE url_sig_key OWNER TO traffic_vault;
---
--- Name: dnssec dnssec_pkey; Type: CONSTRAINT; Schema: public; Owner: traffic_vault
---
+DO $$ BEGIN
+IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'dnssec_pkey' AND table_name = 'dnssec') THEN
+ --
+ -- Name: dnssec dnssec_pkey; Type: CONSTRAINT; Schema: public; Owner: traffic_vault
+ --
-ALTER TABLE ONLY dnssec
- ADD CONSTRAINT dnssec_pkey PRIMARY KEY (cdn);
+ ALTER TABLE ONLY dnssec
+ ADD CONSTRAINT dnssec_pkey PRIMARY KEY (cdn);
+END IF;
---
--- Name: sslkey sslkey_pkey; Type: CONSTRAINT; Schema: public; Owner: traffic_vault
---
+IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'sslkey_pkey' AND table_name = 'sslkey') THEN
+ --
+ -- Name: sslkey sslkey_pkey; Type: CONSTRAINT; Schema: public; Owner: traffic_vault
+ --
-ALTER TABLE ONLY sslkey
- ADD CONSTRAINT sslkey_pkey PRIMARY KEY (deliveryservice, cdn, version);
+ ALTER TABLE ONLY sslkey
+ ADD CONSTRAINT sslkey_pkey PRIMARY KEY (deliveryservice, cdn, version);
+END IF;
+IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'uri_signing_key_pkey' AND table_name = 'uri_signing_key') THEN
+ --
+ -- Name: uri_signing_key uri_signing_key_pkey; Type: CONSTRAINT; Schema: public; Owner: traffic_vault
+ --
---
--- Name: uri_signing_key uri_signing_key_pkey; Type: CONSTRAINT; Schema: public; Owner: traffic_vault
---
-
-ALTER TABLE ONLY uri_signing_key
- ADD CONSTRAINT uri_signing_key_pkey PRIMARY KEY (deliveryservice);
+ ALTER TABLE ONLY uri_signing_key
+ ADD CONSTRAINT uri_signing_key_pkey PRIMARY KEY (deliveryservice);
+END IF;
+IF NOT EXISTS (SELECT FROM information_schema.table_constraints WHERE constraint_name = 'url_sig_key_pkey' AND table_name = 'url_sig_key') THEN
+ --
+ -- Name: url_sig_key url_sig_key_pkey; Type: CONSTRAINT; Schema: public; Owner: traffic_vault
+ --
---
--- Name: url_sig_key url_sig_key_pkey; Type: CONSTRAINT; Schema: public; Owner: traffic_vault
---
-
-ALTER TABLE ONLY url_sig_key
- ADD CONSTRAINT url_sig_key_pkey PRIMARY KEY (deliveryservice);
-
-
---
--- Name: sslkey_cdn_idx; Type: INDEX; Schema: public; Owner: traffic_vault
---
+ ALTER TABLE ONLY url_sig_key
+ ADD CONSTRAINT url_sig_key_pkey PRIMARY KEY (deliveryservice);
+END IF;
-CREATE INDEX sslkey_cdn_idx ON sslkey USING btree (cdn);
+IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'sslkey' AND column_name = 'cdn') THEN
+ --
+ -- Name: sslkey_cdn_idx; Type: INDEX; Schema: public; Owner: traffic_vault
+ --
+ CREATE INDEX IF NOT EXISTS sslkey_cdn_idx ON sslkey USING btree (cdn);
+END IF;
---
--- Name: sslkey_deliveryservice_idx; Type: INDEX; Schema: public; Owner: traffic_vault
---
+IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'sslkey' AND column_name = 'deliveryservice') THEN
+ --
+ -- Name: sslkey_deliveryservice_idx; Type: INDEX; Schema: public; Owner: traffic_vault
+ --
-CREATE INDEX sslkey_deliveryservice_idx ON sslkey USING btree (deliveryservice);
+ CREATE INDEX IF NOT EXISTS sslkey_deliveryservice_idx ON sslkey USING btree (deliveryservice);
+END IF;
---
--- Name: sslkey_version_idx; Type: INDEX; Schema: public; Owner: traffic_vault
---
+IF EXISTS (SELECT FROM information_schema.columns WHERE table_name = 'sslkey' AND column_name = 'version') THEN
+ --
+ -- Name: sslkey_version_idx; Type: INDEX; Schema: public; Owner: traffic_vault
+ --
-CREATE INDEX sslkey_version_idx ON sslkey USING btree (version);
+ CREATE INDEX IF NOT EXISTS sslkey_version_idx ON sslkey USING btree (version);
+END IF;
+END$$;
--
-- Name: dnssec dnssec_last_updated; Type: TRIGGER; Schema: public; Owner: traffic_vault
--
-
-CREATE TRIGGER dnssec_last_updated BEFORE UPDATE ON dnssec FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated();
-
+DROP TRIGGER IF EXISTS dnssec_last_updated ON dnssec;
+CREATE TRIGGER dnssec_last_updated
+ BEFORE UPDATE ON dnssec
+ FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated();
--
-- Name: sslkey sslkey_last_updated; Type: TRIGGER; Schema: public; Owner: traffic_vault
--
-
-CREATE TRIGGER sslkey_last_updated BEFORE UPDATE ON sslkey FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated();
-
+DROP TRIGGER IF EXISTS sslkey_last_updated on sslkey;
+CREATE TRIGGER sslkey_last_updated
+ BEFORE UPDATE ON sslkey
+ FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated();
--
-- Name: uri_signing_key uri_signing_key_last_updated; Type: TRIGGER; Schema: public; Owner: traffic_vault
--
-
-CREATE TRIGGER uri_signing_key_last_updated BEFORE UPDATE ON uri_signing_key FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated();
-
+DROP TRIGGER IF EXISTS uri_signing_key_last_updated on uri_signing_key;
+CREATE TRIGGER uri_signing_key_last_updated
+ BEFORE UPDATE ON uri_signing_key
+ FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated();
--
-- Name: url_sig_key url_sig_key_last_updated; Type: TRIGGER; Schema: public; Owner: traffic_vault
--
-
-CREATE TRIGGER url_sig_key_last_updated BEFORE UPDATE ON url_sig_key FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated();
-
+DROP TRIGGER IF EXISTS url_sig_key_last_updated on url_sig_key;
+CREATE TRIGGER url_sig_key_last_updated
+ BEFORE UPDATE ON url_sig_key
+ FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated();
--
-- PostgreSQL database dump complete
diff --git a/traffic_ops/app/db/trafficvault/test/run-tvdb-test.sh b/traffic_ops/app/db/trafficvault/test/run-tvdb-test.sh
index 9dab7f4f65..dd6218540c 100755
--- a/traffic_ops/app/db/trafficvault/test/run-tvdb-test.sh
+++ b/traffic_ops/app/db/trafficvault/test/run-tvdb-test.sh
@@ -105,6 +105,12 @@ fi
./db/admin --trafficvault --env=production upgrade || { echo "DB upgrade failed!"; exit 1; }
+if ! ./db/admin --trafficvault -env=production load_schema ||
+ ! ./db/admin --trafficvault -env=production load_schema; then
+ echo 'Could not re-run create_tables.sql!'
+ exit 1
+fi;
+
# insert some dummy TVDB data, run db/reencrypt/reencrypt to test it
sslkey_data=$(cat /sslkey.dat)
dnssec_data=$(cat /dnssec.dat)