You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafficcontrol.apache.org by "rimashah25 (via GitHub)" <gi...@apache.org> on 2023/08/30 18:46:24 UTC

[GitHub] [trafficcontrol] rimashah25 opened a new pull request, #7764: Squash db migration that have already been released (7.x.x)

rimashah25 opened a new pull request, #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764

   <!--
   Thank you for contributing! Please be sure to read our contribution guidelines: https://github.com/apache/trafficcontrol/blob/master/CONTRIBUTING.md
   If this closes or relates to an existing issue, please reference it using one of the following:
   
   Closes: #ISSUE
   Related: #ISSUE
   
   If this PR fixes a security vulnerability, DO NOT submit! Instead, contact
   the Apache Traffic Control Security Team at security@trafficcontrol.apache.org and follow the
   guidelines at https://apache.org/security regarding vulnerability disclosure.
   -->
   Closes: https://github.com/apache/trafficcontrol/issues/7655
   
   <!-- **^ Add meaningful description above** --><hr/>
   
   ## Which Traffic Control components are affected by this PR?
   <!-- Please delete all components from this list that are NOT affected by this PR.
   Feel free to add the name of a tool or script that is affected but not on the list.
   -->
   - Traffic Ops
   
   ## What is the best way to verify this PR?
   <!-- Please include here ALL the steps necessary to test your PR.
   If your PR has tests (and most should), provide the steps needed to run the tests.
   If not, please provide step-by-step instructions to test the PR manually and explain why your PR does not need tests. -->
   
   Run the db/admin tool on master and on this fork and compare the DB schema and data to ensure they are the same.
   You can use pg_dump to fetch the data from the DBs and diff to compare the output.
   
   ## If this is a bugfix, which Traffic Control versions contained the bug?
   <!-- Delete this section if the PR is not a bugfix, or if the bug is only in the master branch.
   Examples:
   - 5.1.2
   - 5.1.3 (RC1)
    -->
   
   
   ## PR submission checklist
   - [x] This PR has tests <!-- If not, please delete this text and explain why this PR does not need tests. -->
   - [x] This PR has documentation <!-- If not, please delete this text and explain why this PR does not need documentation. -->
   - [x] This PR has a CHANGELOG.md entry <!-- A fix for a bug from an ATC release, an improvement, or a new feature should have a changelog entry. -->
   - [x] This PR **DOES NOT FIX A SERIOUS SECURITY VULNERABILITY** (see [the Apache Software Foundation's security guidelines](https://apache.org/security) for details)
   
   <!--
   Licensed to the Apache Software Foundation (ASF) under one
   or more contributor license agreements.  See the NOTICE file
   distributed with this work for additional information
   regarding copyright ownership.  The ASF licenses this file
   to you 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.
   -->
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] zrhoffman commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "zrhoffman (via GitHub)" <gi...@apache.org>.
zrhoffman commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1312384741


##########
CHANGELOG.md:
##########
@@ -79,31 +80,34 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/).
 - [#7765](https://github.com/apache/trafficcontrol/pull/7765) *Traffic Stats* now uses Traffic Ops APIv5
 
 ### Fixed
+- [#7764](https://github.com/apache/trafficcontrol/pull/7764) *Traffic Ops* Collapsed DB migrations
 - [#7767](https://github.com/apache/trafficcontrol/pull/7767) *Traffic Ops* Fixed ASN update logic for APIv5
 - [RFC3339](https://github.com/apache/trafficcontrol/issues/5911)
-    - [#7759](https://github.com/apache/trafficcontrol/pull/7759) *Traffic Ops* Fixed `/profiles/{{ID}}/parameters` and `profiles/name/{{name}}/parameters` v5 APIs to respond with `RFC3339` timestamps.

Review Comment:
   Why is there such a big diff in the changelog?



##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 0
+    ON CONFLICT DO NOTHING;

Review Comment:
   This grants `DNS-SEC:READ` and `DNS-SEC:DELETE` to a bunch of roles that did not have it before: trouter (`1`), read-only (`4`), portal (`6`), steering (`7`), and federation (`8`):
   
   ```diff
   --- apache.sql	2023-08-31 17:46:13.333267263 -0600
   +++ 7764.sql	2023-08-31 17:46:46.260625355 -0600
   @@ -3022,6 +3022,16 @@
    6	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
    7	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
    8	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
   +1	DNS-SEC:DELETE	A_TIMESTAMP
   +4	DNS-SEC:READ	A_TIMESTAMP
   +4	DNS-SEC:DELETE	A_TIMESTAMP
   +6	DNS-SEC:READ	A_TIMESTAMP
   +6	DNS-SEC:DELETE	A_TIMESTAMP
   +7	DNS-SEC:READ	A_TIMESTAMP
   +7	DNS-SEC:DELETE	A_TIMESTAMP
   +8	DNS-SEC:READ	A_TIMESTAMP
   +8	DNS-SEC:DELETE	A_TIMESTAMP
   +1	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
    3	ACME:READ	A_TIMESTAMP
    4	ACME:READ	A_TIMESTAMP
    6	ACME:READ	A_TIMESTAMP
   ```



##########
traffic_ops/app/db/migrations/2022041410185700_add_server_profile_table.up.sql:
##########
@@ -1,38 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one or more
- * contributor license agreements.  See the NOTICE file distributed with this
- * work for additional information regarding copyright ownership.  The ASF
- * licenses this file to you 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 undera
- * the License.
- */
-
-CREATE TABLE IF NOT EXISTS public.server_profile (
-                                                     server bigint NOT NULL,
-                                                     profile_name text NOT NULL,
-                                                     priority int NOT NULL CHECK (priority >= 0),
-    CONSTRAINT pk_server_profile PRIMARY KEY (profile_name, server),
-    CONSTRAINT fk_server_id FOREIGN KEY (server) REFERENCES public.server(id) ON DELETE CASCADE ON UPDATE CASCADE,
-    CONSTRAINT fk_server_profile_name_profile FOREIGN KEY (profile_name) REFERENCES public.profile(name) ON UPDATE CASCADE ON DELETE RESTRICT
-    );
-
-INSERT into public.server_profile(server, profile_name, priority)
-SELECT s.id, p.name, 0
-FROM public.server AS s
-    JOIN public.profile p ON p.id=s.profile;
-
-DROP TRIGGER IF EXISTS before_update_ip_address_trigger on ip_address;
-
-DROP TRIGGER IF EXISTS before_create_ip_address_trigger on ip_address;
-
-DROP TRIGGER IF EXISTS before_update_server_trigger ON server;
-
-DROP TRIGGER IF EXISTS before_create_server_trigger ON server;

Review Comment:
   These triggers are created in `create_tables.sql` and those queries need to be removed.



##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 0
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO public.role_capability (role_id, cap_name)
+SELECT id, perm
+FROM public.role
+CROSS JOIN (
+    VALUES ('DELIVERY-SERVICE-SAFE:UPDATE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 0
+    ON CONFLICT DO NOTHING;

Review Comment:
   This grants `DELIVERY-SERVICE-SAFE:UPDATE` to the `trouter` user, but `trouter` should not have `DELIVERY-SERVICE-SAFE:UPDATE`.



##########
traffic_ops/app/db/create_tables.sql:
##########
@@ -1492,8 +1443,11 @@ CREATE TABLE IF NOT EXISTS server (
     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,
+    config_update_time timestamp NOT NULL DEFAULT TIMESTAMP 'epoch',
+    config_apply_time timestamp NOT NULL DEFAULT TIMESTAMP 'epoch',
+    revalidate_update_time timestamp NOT NULL DEFAULT TIMESTAMP 'epoch',
+    revalidate_apply_time timestamp NOT NULL DEFAULT TIMESTAMP 'epoch',

Review Comment:
   These lines produce different columns than the migration did (`with time zone DEFAULT` vs `without time zone DEFAULT`):
   
   ```diff
   --- apache.sql	2023-08-31 17:31:58.303674009 -0600
   +++ 7764.sql	2023-08-31 17:32:30.757614383 -0600
   @@ -1760,10 +1760,10 @@
        last_updated timestamp with time zone DEFAULT now() NOT NULL,
        https_port bigint,
        status_last_updated timestamp with time zone,
   -    config_update_time timestamp with time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL,
   -    config_apply_time timestamp with time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL,
   -    revalidate_update_time timestamp with time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL,
   -    revalidate_apply_time timestamp with time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL
   +    config_update_time timestamp without time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL,
   +    config_apply_time timestamp without time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL,
   +    revalidate_update_time timestamp without time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL,
   +    revalidate_apply_time timestamp without time zone DEFAULT '1970-01-01 00:00:00'::timestamp without time zone NOT NULL
    );
    ```
   
   The migration, for reference:
   
   https://github.com/apache/trafficcontrol/blob/d385dcb04ebce7792d4b13370b98c604b59408f5/traffic_ops/app/db/migrations/2022040623082100_server_config_update.up.sql#L20-L23



##########
traffic_ops/app/db/create_tables.sql:
##########
@@ -3811,6 +3696,385 @@ REVOKE ALL ON SCHEMA public FROM traffic_ops;
 GRANT ALL ON SCHEMA public TO traffic_ops;
 GRANT ALL ON SCHEMA public TO PUBLIC;
 
+--
+-- Name: cdni_capabilities; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_capabilities (
+    id bigint NOT NULL,
+    type text NOT NULL,
+    ucdn text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_capabilities OWNER TO traffic_ops;
+
+--
+-- Name: cdni_capabilities_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops
+--
+
+CREATE SEQUENCE cdni_capabilities_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER TABLE cdni_capabilities_id_seq OWNER TO traffic_ops;
+
+--
+-- Name: cdni_capabilities_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+ALTER SEQUENCE cdni_capabilities_id_seq OWNED BY cdni_capabilities.id;
+
+--
+-- Name: cdni_footprints; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_footprints (
+    id bigint NOT NULL,
+    footprint_type text NOT NULL,
+    footprint_value text[] NOT NULL,
+    ucdn text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_footprints OWNER TO traffic_ops;
+
+--
+-- Name: cdni_footprints_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops
+--
+
+CREATE SEQUENCE cdni_footprints_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER TABLE cdni_footprints_id_seq OWNER TO traffic_ops;
+
+--
+-- Name: cdni_footprints_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+ALTER SEQUENCE cdni_footprints_id_seq OWNED BY cdni_footprints.id;
+
+--
+-- Name: cdni_limits; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_limits (
+    id bigint NOT NULL,
+    limit_id text NOT NULL,
+    scope_type text,
+    scope_value text[],
+    limit_type text NOT NULL,
+    maximum_hard bigint NOT NULL,
+    maximum_soft bigint NOT NULL,
+    telemetry_id text NOT NULL,
+    telemetry_metric text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_limits OWNER TO traffic_ops;
+
+--
+-- Name: cdni_limits_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops
+--
+
+CREATE SEQUENCE cdni_limits_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER TABLE cdni_limits_id_seq OWNER TO traffic_ops;
+
+--
+-- Name: cdni_limits_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+ALTER SEQUENCE cdni_limits_id_seq OWNED BY cdni_limits.id;
+
+--
+-- Name: cdni_capabilities; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_capabilities (
+    id bigserial NOT NULL,
+    type text NOT NULL,
+    ucdn text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_capabilities OWNER TO traffic_ops;
+
+--
+-- Name: cdni_footprints; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_footprints (
+    id bigserial NOT NULL,
+    footprint_type text NOT NULL,
+    footprint_value text[] NOT NULL,
+    ucdn text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_footprints OWNER TO traffic_ops;
+
+--
+-- Name: cdni_telemetry; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_telemetry (
+    id text NOT NULL,
+    type text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL,
+    configuration_url text DEFAULT ''::text
+);
+
+ALTER TABLE cdni_telemetry OWNER TO traffic_ops;
+
+--
+-- Name: cdni_telemetry_metrics; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_telemetry_metrics (
+    name text NOT NULL,
+    time_granularity bigint NOT NULL,
+    data_percentile bigint NOT NULL,
+    latency integer NOT NULL,
+    telemetry_id text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_telemetry_metrics OWNER TO traffic_ops;
+
+--
+-- Name: cdni_limits; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_limits (
+    id bigserial NOT NULL,
+    limit_id text NOT NULL,
+    scope_type text,
+    scope_value text[],
+    limit_type text NOT NULL,
+    maximum_hard bigint NOT NULL,
+    maximum_soft bigint NOT NULL,
+    telemetry_id text NOT NULL,
+    telemetry_metric text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_limits OWNER TO traffic_ops;
+
+--
+-- Name: cdn_lock_user; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdn_lock_user (
+    owner text NOT NULL,
+    cdn text NOT NULL,
+    username text NOT NULL
+);
+
+ALTER TABLE cdn_lock_user OWNER TO traffic_ops;
+
+--
+-- Name: server_profile; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS server_profile (
+    server bigint NOT NULL,
+    profile_name text NOT NULL,
+    priority int NOT NULL CHECK (priority >= 0)
+);
+
+ALTER TABLE server_profile OWNER TO traffic_ops;
+
+--
+-- Name: cdni_capability_updates; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_capability_updates (
+    id bigserial NOT NULL,
+    request_type text NOT NULL,
+    ucdn text NOT NULL,
+    host text,
+    data json NOT NULL,
+    async_status_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_capability_updates OWNER TO traffic_ops;
+
+--
+-- Name: cdni_capabilities id; Type: DEFAULT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_capabilities ALTER COLUMN id SET DEFAULT nextval('cdni_capabilities_id_seq'::regclass);
+
+--
+-- Name: cdni_footprints id; Type: DEFAULT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_footprints ALTER COLUMN id SET DEFAULT nextval('cdni_footprints_id_seq'::regclass);
+
+--
+-- Name: cdni_limits id; Type: DEFAULT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_limits ALTER COLUMN id SET DEFAULT nextval('cdni_limits_id_seq'::regclass);
+
+--
+-- Name: cdn_lock cdn_lock_cdn_username_unique; Type: CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE cdn_lock ADD CONSTRAINT cdn_lock_cdn_username_unique UNIQUE (username, cdn);
+
+--
+-- Name: cdni_capabilities pk_cdni_capabilities; Type: CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_capabilities
+    ADD CONSTRAINT pk_cdni_capabilities PRIMARY KEY (id);
+
+--
+-- Name: cdni_footprints pk_cdni_footprints; Type: CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_footprints
+    ADD CONSTRAINT pk_cdni_footprints PRIMARY KEY (id);
+
+--
+-- Name: cdni_limits pk_cdni_limits; Type: CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_limits
+    ADD CONSTRAINT pk_cdni_limits PRIMARY KEY (id);
+
+--
+-- Name: cdni_telemetry pk_cdni_telemetry; Type: CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_telemetry
+    ADD CONSTRAINT pk_cdni_telemetry PRIMARY KEY (id);
+
+--
+-- Name: cdni_telemetry_metrics pk_cdni_telemetry_metrics; Type: CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_telemetry_metrics
+    ADD CONSTRAINT pk_cdni_telemetry_metrics PRIMARY KEY (name);
+
+--
+-- Name: cdn_lock_user pk_cdn_lock_user; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdn_lock_user
+    ADD CONSTRAINT pk_cdn_lock_user PRIMARY KEY (owner, cdn, username);
+
+--
+-- Name: server_profile pk_server_profile; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY server_profile
+    ADD CONSTRAINT pk_server_profile PRIMARY KEY (profile_name, server);
+
+--
+-- Name: cdni_capability_updates pk_cdni_capability_updates; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_capability_updates
+    ADD CONSTRAINT pk_cdni_capability_updates PRIMARY KEY (id);
+
+--
+-- Name: cdni_footprints fk_cdni_footprint_capabilities; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_footprints
+    ADD CONSTRAINT fk_cdni_footprint_capabilities FOREIGN KEY (capability_id) REFERENCES cdni_capabilities(id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+--
+-- Name: cdni_limits fk_cdni_limits_capabilities; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_limits
+    ADD CONSTRAINT fk_cdni_limits_capabilities FOREIGN KEY (capability_id) REFERENCES cdni_capabilities(id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+--
+-- Name: cdni_limits fk_cdni_limits_telemetry; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_limits
+    ADD CONSTRAINT fk_cdni_limits_telemetry FOREIGN KEY (telemetry_id) REFERENCES cdni_telemetry(id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+--
+-- Name: cdni_telemetry fk_cdni_telemetry_capabilities; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_telemetry
+    ADD CONSTRAINT fk_cdni_telemetry_capabilities FOREIGN KEY (capability_id) REFERENCES cdni_capabilities(id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+--
+-- Name: cdni_telemetry_metrics fk_cdni_telemetry_metrics_telemetry; Type: FK CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_telemetry_metrics
+    ADD CONSTRAINT fk_cdni_telemetry_metrics_telemetry FOREIGN KEY (telemetry_id) REFERENCES cdni_telemetry(id) ON UPDATE CASCADE ON DELETE CASCADE;
+
+--
+-- Name: cdni_telemetry_metrics fk_cdni_total_limits_telemetry; Type: CONSTRAINT; Schema: public; Owner: traffic_ops
+--
+
+ALTER TABLE ONLY cdni_telemetry_metrics
+    ADD CONSTRAINT fk_cdni_total_limits_telemetry FOREIGN KEY (telemetry_id) REFERENCES cdni_telemetry(id) ON UPDATE CASCADE ON DELETE CASCADE;

Review Comment:
   Remove this constraint, it did not exist in the original migration.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] rimashah25 commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "rimashah25 (via GitHub)" <gi...@apache.org>.
rimashah25 commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1312416904


##########
CHANGELOG.md:
##########
@@ -79,31 +80,34 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/).
 - [#7765](https://github.com/apache/trafficcontrol/pull/7765) *Traffic Stats* now uses Traffic Ops APIv5
 
 ### Fixed
+- [#7764](https://github.com/apache/trafficcontrol/pull/7764) *Traffic Ops* Collapsed DB migrations
 - [#7767](https://github.com/apache/trafficcontrol/pull/7767) *Traffic Ops* Fixed ASN update logic for APIv5
 - [RFC3339](https://github.com/apache/trafficcontrol/issues/5911)
-    - [#7759](https://github.com/apache/trafficcontrol/pull/7759) *Traffic Ops* Fixed `/profiles/{{ID}}/parameters` and `profiles/name/{{name}}/parameters` v5 APIs to respond with `RFC3339` timestamps.

Review Comment:
   I decoupled formatting changes.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] codecov[bot] commented on pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "codecov[bot] (via GitHub)" <gi...@apache.org>.
codecov[bot] commented on PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#issuecomment-1701566531

   ## [Codecov](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
   > Merging [#7764](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) (24f5ecb) into [master](https://app.codecov.io/gh/apache/trafficcontrol/commit/2257ddb1a49c0b157206a92e753a01ce46eab165?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) (2257ddb) will **increase** coverage by `32.85%`.
   > The diff coverage is `n/a`.
   
   ```diff
   @@              Coverage Diff              @@
   ##             master    #7764       +/-   ##
   =============================================
   + Coverage     30.97%   63.82%   +32.85%     
     Complexity       98       98               
   =============================================
     Files           655      266      -389     
     Lines         80356    11269    -69087     
     Branches        965      965               
   =============================================
   - Hits          24891     7193    -17698     
   + Misses        53394     3797    -49597     
   + Partials       2071      279     -1792     
   ```
   
   | [Flag](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/flags?src=pr&el=flags&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | Coverage Δ | |
   |---|---|---|
   | [golib_unit](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [grove_unit](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [t3c_unit](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [traffic_monitor_unit](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [traffic_ops_unit](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [traffic_portal_v2](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `74.39% <ø> (+0.03%)` | :arrow_up: |
   | [traffic_stats_unit](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `?` | |
   | [unit_tests](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/flags?src=pr&el=flag&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) | `74.39% <ø> (+45.21%)` | :arrow_up: |
   
   Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache#carryforward-flags-in-the-pull-request-comment) to find out more.
   
   [see 393 files with indirect coverage changes](https://app.codecov.io/gh/apache/trafficcontrol/pull/7764/indirect-changes?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)
   
   :mega: We’re building smart automated test selection to slash your CI/CD build times. [Learn more](https://about.codecov.io/iterative-testing/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] zrhoffman commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "zrhoffman (via GitHub)" <gi...@apache.org>.
zrhoffman commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1316411101


##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHRER "name" = 'operations'
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO public.role_capability (role_id, cap_name)
+SELECT id, perm
+FROM public.role
+CROSS JOIN (
+    VALUES ('DELIVERY-SERVICE-SAFE:UPDATE')
+) AS perms(perm)
+WHRER name in ('operations', 'read-only', 'portal', 'federation', 'steering')

Review Comment:
   This `WHRER` should be changed, too



##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHRER "name" = 'operations'

Review Comment:
   ```sql
   INSERT INTO public.role_capability
   SELECT id, perm FROM public.role
   CROSS JOIN (
       VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
   ) AS perms(perm)
   WHRER "name" = 'operations'
       ON CONFLICT DO NOTHING;
   ERROR:  syntax error at or near "WHRER"
   LINE 6: WHRER "name" = 'operations'
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] zrhoffman commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "zrhoffman (via GitHub)" <gi...@apache.org>.
zrhoffman commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1316168351


##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 8
+    ON CONFLICT DO NOTHING;

Review Comment:
   > I increased the check from 0 to 8.. not sure if that is the right way but hopefully it helps.
   
   Additional perms are no longer granted to portal (`6`), but this still grants `DNS-SEC:READ` and `DNS-SEC:DELETE` to a bunch of roles that did not have it before: trouter (`1`), read-only (`4`), steering (`7`), and federation (`8`):
   
   ```diff
   --- apache.sql	2023-09-05 11:04:30.851468321 -0600
   +++ 7764.sql	2023-09-05 11:05:02.572109742 -0600
   @@ -3024,6 +3024,14 @@
    6	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
    7	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
    8	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
   +1	DNS-SEC:DELETE	A_TIMESTAMP
   +4	DNS-SEC:READ	A_TIMESTAMP
   +4	DNS-SEC:DELETE	A_TIMESTAMP
   +7	DNS-SEC:READ	A_TIMESTAMP
   +7	DNS-SEC:DELETE	A_TIMESTAMP
   +8	DNS-SEC:READ	A_TIMESTAMP
   +8	DNS-SEC:DELETE	A_TIMESTAMP
   +1	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
    3	ACME:READ	A_TIMESTAMP
    4	ACME:READ	A_TIMESTAMP
    6	ACME:READ	A_TIMESTAMP
   ```
   
   Previous convo: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1312398427



##########
traffic_ops/app/db/migrations/2022050916074300_add_reserved_statuses.up.sql:
##########
@@ -1,23 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one or more
- * contributor license agreements.  See the NOTICE file distributed with this
- * work for additional information regarding copyright ownership.  The ASF
- * licenses this file to you 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 public.status ("name", description) VALUES ('ONLINE', 'Server is online.') ON CONFLICT ("name") DO NOTHING;
-INSERT INTO public.status ("name", description) VALUES ('OFFLINE', 'Server is Offline. Not active in any configuration.') ON CONFLICT ("name") DO NOTHING;
-INSERT INTO public.status ("name", description) VALUES ('REPORTED', 'Server is online and reported in the health protocol.') ON CONFLICT ("name") DO NOTHING;
-INSERT INTO public.status ("name", description) VALUES ('ADMIN_DOWN', 'Sever is administrative down and does not receive traffic.') ON CONFLICT ("name") DO NOTHING;
-INSERT INTO public.status ("name", description) VALUES ('PRE_PROD', 'Pre Production. Not active in any configuration.') ON CONFLICT ("name") DO NOTHING;

Review Comment:
   After this migration is removed and the statuses are created only from `seeds.sql`, they are created in a different order than they were previously:
   
   ```diff
   --- apache.sql	2023-09-05 11:04:30.851468321 -0600
   +++ 7764.sql	2023-09-05 11:05:02.572109742 -0600
   @@ -3449,12 +3457,12 @@
    --
    
    COPY public.status (id, name, description, last_updated) FROM stdin;
   -1	ONLINE	Server is online.	A_TIMESTAMP
   -2	OFFLINE	Server is Offline. Not active in any configuration.	A_TIMESTAMP
   +1	OFFLINE	Server is Offline. Not active in any configuration.	A_TIMESTAMP
   +2	ONLINE	Server is online.	A_TIMESTAMP
    3	REPORTED	Server is online and reported in the health protocol.	A_TIMESTAMP
    4	ADMIN_DOWN	Sever is administrative down and does not receive traffic.	A_TIMESTAMP
   -5	PRE_PROD	Pre Production. Not active in any configuration.	A_TIMESTAMP
   -10	CCR_IGNORE	Server is ignored by traffic router.	A_TIMESTAMP
   +5	CCR_IGNORE	Server is ignored by traffic router.	A_TIMESTAMP
   +6	PRE_PROD	Pre Production. Not active in any configuration.	A_TIMESTAMP
    \.
   ```
   
   I am fine with a different order, but the T3C integration tests still [fail](https://github.com/apache/trafficcontrol/actions/runs/6044075807/job/16402166989?pr=7764), claiming none of these statuses exist:
   
   ```go
   --- FAIL: TestT3cActionLog (3.22s)
       servers.go:33: Received unexpected error: no Status named REPORTED Messages: Could not create server 'atlanta-edge-01': no Status named REPORTED - alerts: []
   ```



##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 8
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO public.role_capability (role_id, cap_name)
+SELECT id, perm
+FROM public.role
+CROSS JOIN (
+    VALUES ('DELIVERY-SERVICE-SAFE:UPDATE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 2
+    ON CONFLICT DO NOTHING;

Review Comment:
   This still grants `DELIVERY-SERVICE-SAFE:UPDATE` to the `trouter` user, but `trouter` should not have `DELIVERY-SERVICE-SAFE:UPDATE` (see `DELIVERY-SERVICE-SAFE:UPDATE` in the diff above).
   
   Previous discussion: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1312398811



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] rimashah25 commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "rimashah25 (via GitHub)" <gi...@apache.org>.
rimashah25 commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1312420258


##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 0
+    ON CONFLICT DO NOTHING;

Review Comment:
   I increased the check from 0 to 8.. not sure if that is the right way but hopefully it helps.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] zrhoffman commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "zrhoffman (via GitHub)" <gi...@apache.org>.
zrhoffman commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1312208424


##########
traffic_ops/app/db/create_tables.sql:
##########
@@ -841,7 +835,6 @@ CREATE TABLE IF NOT EXISTS deliveryservice_tmuser (
     CONSTRAINT idx_89525_primary PRIMARY KEY (deliveryservice, tm_user_id)
 );
 
-
 ALTER TABLE deliveryservice_tmuser OWNER TO traffic_ops;

Review Comment:
   The `deliveryservice_tmuser` table was removed in `2022031612431414_remove_unused_ds_tmuser_table.up.sql`:
   
   https://github.com/apache/trafficcontrol/blob/2257ddb1a49c0b157206a92e753a01ce46eab165/traffic_ops/app/db/migrations/2022031612431414_remove_unused_ds_tmuser_table.up.sql#L18



##########
traffic_ops/app/db/create_tables.sql:
##########
@@ -3811,6 +3734,460 @@ REVOKE ALL ON SCHEMA public FROM traffic_ops;
 GRANT ALL ON SCHEMA public TO traffic_ops;
 GRANT ALL ON SCHEMA public TO PUBLIC;
 
+--
+-- Name: cdni_capabilities; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_capabilities (
+    id bigint NOT NULL,
+    type text NOT NULL,
+    ucdn text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_capabilities OWNER TO traffic_ops;
+
+--
+-- Name: cdni_capabilities_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops
+--
+
+CREATE SEQUENCE cdni_capabilities_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER TABLE cdni_capabilities_id_seq OWNER TO traffic_ops;
+
+--
+-- Name: cdni_capabilities_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+ALTER SEQUENCE cdni_capabilities_id_seq OWNED BY cdni_capabilities.id;
+
+--
+-- Name: cdni_footprints; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_footprints (
+    id bigint NOT NULL,
+    footprint_type text NOT NULL,
+    footprint_value text[] NOT NULL,
+    ucdn text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_footprints OWNER TO traffic_ops;
+
+--
+-- Name: cdni_footprints_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops
+--
+
+CREATE SEQUENCE cdni_footprints_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER TABLE cdni_footprints_id_seq OWNER TO traffic_ops;
+
+--
+-- Name: cdni_footprints_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+ALTER SEQUENCE cdni_footprints_id_seq OWNED BY cdni_footprints.id;
+
+--
+-- Name: cdni_limits; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_limits (
+    id bigint NOT NULL,
+    limit_id text NOT NULL,
+    scope_type text,
+    scope_value text[],
+    limit_type text NOT NULL,
+    maximum_hard bigint NOT NULL,
+    maximum_soft bigint NOT NULL,
+    telemetry_id text NOT NULL,
+    telemetry_metric text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_limits OWNER TO traffic_ops;
+
+--
+-- Name: cdni_limits_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops
+--
+
+CREATE SEQUENCE cdni_limits_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER TABLE cdni_limits_id_seq OWNER TO traffic_ops;
+
+--
+-- Name: cdni_limits_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+ALTER SEQUENCE cdni_limits_id_seq OWNED BY cdni_limits.id;
+
+--
+-- Name: cdni_capabilities; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_capabilities (
+    id bigserial NOT NULL,
+    type text NOT NULL,
+    ucdn text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_capabilities OWNER TO traffic_ops;
+
+--
+-- Name: cdni_footprints; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_footprints (
+    id bigserial NOT NULL,
+    footprint_type text NOT NULL,
+    footprint_value text[] NOT NULL,
+    ucdn text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_footprints OWNER TO traffic_ops;
+
+--
+-- Name: cdni_telemetry; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_telemetry (
+    id text NOT NULL,
+    type text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL,
+    configuration_url text DEFAULT ''::text
+);
+
+ALTER TABLE cdni_telemetry OWNER TO traffic_ops;
+
+--
+-- Name: cdni_telemetry_metrics; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_telemetry_metrics (
+    name text NOT NULL,
+    time_granularity bigint NOT NULL,
+    data_percentile bigint NOT NULL,
+    latency integer NOT NULL,
+    telemetry_id text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_telemetry_metrics OWNER TO traffic_ops;
+
+--
+-- Name: cdni_total_limits; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_total_limits (
+    limit_type text NOT NULL,
+    maximum_hard bigint NOT NULL,
+    maximum_soft bigint NOT NULL,
+    telemetry_id text NOT NULL,
+    telemetry_metric text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_total_limits OWNER TO traffic_ops;
+
+--
+-- Name: cdni_host_limits; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_host_limits (
+    limit_type text NOT NULL,
+    maximum_hard bigint NOT NULL,
+    maximum_soft bigint NOT NULL,
+    telemetry_id text NOT NULL,
+    telemetry_metric text NOT NULL,
+    capability_id bigint NOT NULL,
+    host text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_host_limits OWNER TO traffic_ops;

Review Comment:
   The `cdni_host_limits` table was dropped in  `2022050410412200_cdni_capacity_limit_with_scopes.up.sql`:
   
   https://github.com/apache/trafficcontrol/blob/2257ddb1a49c0b157206a92e753a01ce46eab165/traffic_ops/app/db/migrations/2022050410412200_cdni_capacity_limit_with_scopes.up.sql#L76



##########
traffic_ops/app/db/migrations/2022040623082100_server_config_update.up.sql:
##########
@@ -1,28 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one or more
- * contributor license agreements.  See the NOTICE file distributed with this
- * work for additional information regarding copyright ownership.  The ASF
- * licenses this file to you 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.
- */
-
--- Add columns, timestamp with time zone, nullable, default null
-ALTER TABLE public.server
-ADD COLUMN IF NOT EXISTS config_update_time TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMP 'epoch',
-ADD COLUMN IF NOT EXISTS config_apply_time TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMP 'epoch', 
-ADD COLUMN IF NOT EXISTS revalidate_update_time TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMP 'epoch', 
-ADD COLUMN IF NOT EXISTS revalidate_apply_time TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMP 'epoch';

Review Comment:
   These columns need to be added to `create_tables.sql`.



##########
traffic_ops/app/db/migrations/2022021611354000_add_user_to_ucdn_table.up.sql:
##########
@@ -1,18 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one or more
- * contributor license agreements.  See the NOTICE file distributed with this
- * work for additional information regarding copyright ownership.  The ASF
- * licenses this file to you 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.
- */
-
-ALTER TABLE tm_user ADD COLUMN IF NOT EXISTS ucdn text NOT NULL DEFAULT '';

Review Comment:
   The `ucdn` column needs to be added to the `tm_user` `CREATE TABLE` statement in `create_tables.sql`.



##########
traffic_ops/app/db/create_tables.sql:
##########
@@ -3811,6 +3734,460 @@ REVOKE ALL ON SCHEMA public FROM traffic_ops;
 GRANT ALL ON SCHEMA public TO traffic_ops;
 GRANT ALL ON SCHEMA public TO PUBLIC;
 
+--
+-- Name: cdni_capabilities; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_capabilities (
+    id bigint NOT NULL,
+    type text NOT NULL,
+    ucdn text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_capabilities OWNER TO traffic_ops;
+
+--
+-- Name: cdni_capabilities_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops
+--
+
+CREATE SEQUENCE cdni_capabilities_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER TABLE cdni_capabilities_id_seq OWNER TO traffic_ops;
+
+--
+-- Name: cdni_capabilities_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+ALTER SEQUENCE cdni_capabilities_id_seq OWNED BY cdni_capabilities.id;
+
+--
+-- Name: cdni_footprints; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_footprints (
+    id bigint NOT NULL,
+    footprint_type text NOT NULL,
+    footprint_value text[] NOT NULL,
+    ucdn text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_footprints OWNER TO traffic_ops;
+
+--
+-- Name: cdni_footprints_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops
+--
+
+CREATE SEQUENCE cdni_footprints_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER TABLE cdni_footprints_id_seq OWNER TO traffic_ops;
+
+--
+-- Name: cdni_footprints_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+ALTER SEQUENCE cdni_footprints_id_seq OWNED BY cdni_footprints.id;
+
+--
+-- Name: cdni_limits; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_limits (
+    id bigint NOT NULL,
+    limit_id text NOT NULL,
+    scope_type text,
+    scope_value text[],
+    limit_type text NOT NULL,
+    maximum_hard bigint NOT NULL,
+    maximum_soft bigint NOT NULL,
+    telemetry_id text NOT NULL,
+    telemetry_metric text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_limits OWNER TO traffic_ops;
+
+--
+-- Name: cdni_limits_id_seq; Type: SEQUENCE; Schema: public; Owner: traffic_ops
+--
+
+CREATE SEQUENCE cdni_limits_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MINVALUE
+    NO MAXVALUE
+    CACHE 1;
+
+ALTER TABLE cdni_limits_id_seq OWNER TO traffic_ops;
+
+--
+-- Name: cdni_limits_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+ALTER SEQUENCE cdni_limits_id_seq OWNED BY cdni_limits.id;
+
+--
+-- Name: cdni_capabilities; Type: SEQUENCE OWNED BY; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_capabilities (
+    id bigserial NOT NULL,
+    type text NOT NULL,
+    ucdn text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_capabilities OWNER TO traffic_ops;
+
+--
+-- Name: cdni_footprints; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_footprints (
+    id bigserial NOT NULL,
+    footprint_type text NOT NULL,
+    footprint_value text[] NOT NULL,
+    ucdn text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_footprints OWNER TO traffic_ops;
+
+--
+-- Name: cdni_telemetry; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_telemetry (
+    id text NOT NULL,
+    type text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL,
+    configuration_url text DEFAULT ''::text
+);
+
+ALTER TABLE cdni_telemetry OWNER TO traffic_ops;
+
+--
+-- Name: cdni_telemetry_metrics; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE cdni_telemetry_metrics (
+    name text NOT NULL,
+    time_granularity bigint NOT NULL,
+    data_percentile bigint NOT NULL,
+    latency integer NOT NULL,
+    telemetry_id text NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_telemetry_metrics OWNER TO traffic_ops;
+
+--
+-- Name: cdni_total_limits; Type: TABLE; Schema: public; Owner: traffic_ops
+--
+
+CREATE TABLE IF NOT EXISTS cdni_total_limits (
+    limit_type text NOT NULL,
+    maximum_hard bigint NOT NULL,
+    maximum_soft bigint NOT NULL,
+    telemetry_id text NOT NULL,
+    telemetry_metric text NOT NULL,
+    capability_id bigint NOT NULL,
+    last_updated timestamp with time zone DEFAULT now() NOT NULL
+);
+
+ALTER TABLE cdni_total_limits OWNER TO traffic_ops;

Review Comment:
   The `cdni_total_limits` table was dropped in  `2022050410412200_cdni_capacity_limit_with_scopes.up.sql`:
   
   https://github.com/apache/trafficcontrol/blob/2257ddb1a49c0b157206a92e753a01ce46eab165/traffic_ops/app/db/migrations/2022050410412200_cdni_capacity_limit_with_scopes.up.sql#L75



##########
traffic_ops/app/db/squash_migrations.sh:
##########
@@ -25,6 +25,7 @@ last_squashed_migration="$(<<<"$migrations_to_squash" tail -n1)"
 last_squashed_migration_timestamp="$(<<<"$last_squashed_migration" sed -E 's|migrations/([0-9]+).*|\1|')"
 first_migration="$(ls migrations/*.sql | grep -A1 "/${last_squashed_migration_timestamp}_" | tail -n1)"
 first_migration_timestamp="$(<<<"$first_migration" sed -E 's|migrations/([0-9]+).*|\1|')"
+# If running on mac, you need BSD version of sed. So, change -i to -i.bak for the below three sed statements.
 sed -i '/^--/,$d' create_tables.sql # keeps the Apache License 2.0 header

Review Comment:
   Just modify the `sed` command, the modified command works on both the GNU and BSD versions of `sed`.



##########
traffic_ops/app/db/migrations/2022040623082100_server_config_update.up.sql:
##########
@@ -1,28 +0,0 @@
-/*
- * Licensed to the Apache Software Foundation (ASF) under one or more
- * contributor license agreements.  See the NOTICE file distributed with this
- * work for additional information regarding copyright ownership.  The ASF
- * licenses this file to you 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.
- */
-
--- Add columns, timestamp with time zone, nullable, default null
-ALTER TABLE public.server
-ADD COLUMN IF NOT EXISTS config_update_time TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMP 'epoch',
-ADD COLUMN IF NOT EXISTS config_apply_time TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMP 'epoch', 
-ADD COLUMN IF NOT EXISTS revalidate_update_time TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMP 'epoch', 
-ADD COLUMN IF NOT EXISTS revalidate_apply_time TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMP 'epoch';
-
--- Drop previous columns
-ALTER TABLE public.server 
-DROP COLUMN IF EXISTS upd_pending, 
-DROP COLUMN IF EXISTS reval_pending;

Review Comment:
   These columns need to be removed from `create_tables.sql`.



##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +144,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHERE priv_level >= 30
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO public.role_capability (role_id, cap_name)
+SELECT id, perm
+FROM public.role
+CROSS JOIN (
+    VALUES ('DELIVERY-SERVICE-SAFE:UPDATE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 0
+    ON CONFLICT DO NOTHING;

Review Comment:
   `INSERT` statements belong in `seeds.sql`. Also, this results in the `admin` role and the `router` role gaining perms they did not previously have (role `1` is `trouter`, role `2` is `admin`):
   
   ```diff
   --- apache.sql	2023-08-31 14:42:08.747837019 -0600
   +++ 7764.sql	2023-08-31 14:42:41.985179003 -0600
   @@ -3022,6 +3091,9 @@
    6	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
    7	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
    8	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
   +2	DNS-SEC:READ	A_TIMESTAMP
   +2	DNS-SEC:DELETE	A_TIMESTAMP
   +1	DELIVERY-SERVICE-SAFE:UPDATE	A_TIMESTAMP
    3	ACME:READ	A_TIMESTAMP
    4	ACME:READ	A_TIMESTAMP
    6	ACME:READ	A_TIMESTAMP
   ```



##########
traffic_ops/app/db/squash_migrations.sh:
##########
@@ -25,6 +25,7 @@ last_squashed_migration="$(<<<"$migrations_to_squash" tail -n1)"
 last_squashed_migration_timestamp="$(<<<"$last_squashed_migration" sed -E 's|migrations/([0-9]+).*|\1|')"
 first_migration="$(ls migrations/*.sql | grep -A1 "/${last_squashed_migration_timestamp}_" | tail -n1)"
 first_migration_timestamp="$(<<<"$first_migration" sed -E 's|migrations/([0-9]+).*|\1|')"
+# If running on mac, you need BSD version of sed. So, change -i to -i.bak for the below three sed statements.
 sed -i '/^--/,$d' create_tables.sql # keeps the Apache License 2.0 header
 sed -Ei "s|(LastSquashedMigrationTimestamp\s+uint\s+= ).*|\1${last_squashed_migration_timestamp} // ${last_squashed_migration}|" admin.go
 sed -Ei "s|(FirstMigrationTimestamp\s+uint\s+= ).*|\1${first_migration_timestamp} // ${first_migration}|" admin.go

Review Comment:
   Might as well remove these lines while you're in here



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] rimashah25 commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "rimashah25 (via GitHub)" <gi...@apache.org>.
rimashah25 commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1316287812


##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 8
+    ON CONFLICT DO NOTHING;

Review Comment:
   I think I resolved it this time, 🤞 .



##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHERE "priv_level" < 20 AND "priv_level" > 8
+    ON CONFLICT DO NOTHING;

Review Comment:
   I think I resolved it this time, 🤞 .



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] rimashah25 commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "rimashah25 (via GitHub)" <gi...@apache.org>.
rimashah25 commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1312402547


##########
CHANGELOG.md:
##########
@@ -79,31 +80,34 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/).
 - [#7765](https://github.com/apache/trafficcontrol/pull/7765) *Traffic Stats* now uses Traffic Ops APIv5
 
 ### Fixed
+- [#7764](https://github.com/apache/trafficcontrol/pull/7764) *Traffic Ops* Collapsed DB migrations
 - [#7767](https://github.com/apache/trafficcontrol/pull/7767) *Traffic Ops* Fixed ASN update logic for APIv5
 - [RFC3339](https://github.com/apache/trafficcontrol/issues/5911)
-    - [#7759](https://github.com/apache/trafficcontrol/pull/7759) *Traffic Ops* Fixed `/profiles/{{ID}}/parameters` and `profiles/name/{{name}}/parameters` v5 APIs to respond with `RFC3339` timestamps.

Review Comment:
   I refactored the changelog to make it a better read for the release.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] rimashah25 commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "rimashah25 (via GitHub)" <gi...@apache.org>.
rimashah25 commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1312402547


##########
CHANGELOG.md:
##########
@@ -79,31 +80,34 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/).
 - [#7765](https://github.com/apache/trafficcontrol/pull/7765) *Traffic Stats* now uses Traffic Ops APIv5
 
 ### Fixed
+- [#7764](https://github.com/apache/trafficcontrol/pull/7764) *Traffic Ops* Collapsed DB migrations
 - [#7767](https://github.com/apache/trafficcontrol/pull/7767) *Traffic Ops* Fixed ASN update logic for APIv5
 - [RFC3339](https://github.com/apache/trafficcontrol/issues/5911)
-    - [#7759](https://github.com/apache/trafficcontrol/pull/7759) *Traffic Ops* Fixed `/profiles/{{ID}}/parameters` and `profiles/name/{{name}}/parameters` v5 APIs to respond with `RFC3339` timestamps.

Review Comment:
   I refactored the changelog to make it a better read for the release. If you want I can separate (de-couple) it in a different PR.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] rimashah25 commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "rimashah25 (via GitHub)" <gi...@apache.org>.
rimashah25 commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1316486440


##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHRER "name" = 'operations'

Review Comment:
   🤦‍♀️ 



##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHRER "name" = 'operations'

Review Comment:
   🤦‍♀️ 



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] zrhoffman commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "zrhoffman (via GitHub)" <gi...@apache.org>.
zrhoffman commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1317683247


##########
traffic_ops/app/db/squash_migrations.sh:
##########
@@ -25,9 +25,7 @@ last_squashed_migration="$(<<<"$migrations_to_squash" tail -n1)"
 last_squashed_migration_timestamp="$(<<<"$last_squashed_migration" sed -E 's|migrations/([0-9]+).*|\1|')"
 first_migration="$(ls migrations/*.sql | grep -A1 "/${last_squashed_migration_timestamp}_" | tail -n1)"
 first_migration_timestamp="$(<<<"$first_migration" sed -E 's|migrations/([0-9]+).*|\1|')"
-sed -i '/^--/,$d' create_tables.sql # keeps the Apache License 2.0 header
-sed -Ei "s|(LastSquashedMigrationTimestamp\s+uint\s+= ).*|\1${last_squashed_migration_timestamp} // ${last_squashed_migration}|" admin.go
-sed -Ei "s|(FirstMigrationTimestamp\s+uint\s+= ).*|\1${first_migration_timestamp} // ${first_migration}|" admin.go

Review Comment:
   My mistake, these lines should stay (and have `.bak` added).



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] zrhoffman merged pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "zrhoffman (via GitHub)" <gi...@apache.org>.
zrhoffman merged PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] rimashah25 commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "rimashah25 (via GitHub)" <gi...@apache.org>.
rimashah25 commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1312402547


##########
CHANGELOG.md:
##########
@@ -79,31 +80,34 @@ The format is based on [Keep a Changelog](http://keepachangelog.com/en/1.0.0/).
 - [#7765](https://github.com/apache/trafficcontrol/pull/7765) *Traffic Stats* now uses Traffic Ops APIv5
 
 ### Fixed
+- [#7764](https://github.com/apache/trafficcontrol/pull/7764) *Traffic Ops* Collapsed DB migrations
 - [#7767](https://github.com/apache/trafficcontrol/pull/7767) *Traffic Ops* Fixed ASN update logic for APIv5
 - [RFC3339](https://github.com/apache/trafficcontrol/issues/5911)
-    - [#7759](https://github.com/apache/trafficcontrol/pull/7759) *Traffic Ops* Fixed `/profiles/{{ID}}/parameters` and `profiles/name/{{name}}/parameters` v5 APIs to respond with `RFC3339` timestamps.

Review Comment:
   I refactored the changelog to make it a better read for the release. If you want I can separate it in a different PR.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


[GitHub] [trafficcontrol] rimashah25 commented on a diff in pull request #7764: Squash db migration that have already been released (7.x.x)

Posted by "rimashah25 (via GitHub)" <gi...@apache.org>.
rimashah25 commented on code in PR #7764:
URL: https://github.com/apache/trafficcontrol/pull/7764#discussion_r1316486906


##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHRER "name" = 'operations'
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO public.role_capability (role_id, cap_name)
+SELECT id, perm
+FROM public.role
+CROSS JOIN (
+    VALUES ('DELIVERY-SERVICE-SAFE:UPDATE')
+) AS perms(perm)
+WHRER name in ('operations', 'read-only', 'portal', 'federation', 'steering')

Review Comment:
   🤦‍♀️ 



##########
traffic_ops/app/db/seeds.sql:
##########
@@ -97,6 +103,23 @@ FROM public.role
 WHERE "name" in ('operations', 'read-only', 'portal', 'federation', 'steering')
 ON CONFLICT DO NOTHING;
 
+INSERT INTO public.role_capability
+SELECT id, perm FROM public.role
+CROSS JOIN (
+    VALUES ('DNS-SEC:READ'), ('DNS-SEC:DELETE')
+) AS perms(perm)
+WHRER "name" = 'operations'
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO public.role_capability (role_id, cap_name)
+SELECT id, perm
+FROM public.role
+CROSS JOIN (
+    VALUES ('DELIVERY-SERVICE-SAFE:UPDATE')
+) AS perms(perm)
+WHRER name in ('operations', 'read-only', 'portal', 'federation', 'steering')

Review Comment:
   🤦‍♀️ 



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org