[trafficcontrol] branch master updated: Use strings for JSONB string comparisons (#7223)

[srijeet0406]

This is an automated email from the ASF dual-hosted git repository.

srijeet0406 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 dda86f41ce Use strings for JSONB string comparisons (#7223)
dda86f41ce is described below

commit dda86f41cec1f1750b2660323d1aa88f6d6364ab
Author: Zach Hoffman <zrhoff...@apache.org>
AuthorDate: Mon Nov 28 16:13:08 2022 -0700

    Use strings for JSONB string comparisons (#7223)
    
    * Use strings for JSONB string comparisons
    
    * Update DSR timestamps to RFC3339 (#119)
    
    Co-authored-by: ocket8888 <ocket8...@gmail.com>
---
 .../2022110908494015_ds_active_flag.down.sql       | 39 ++++++++++---------
 .../2022110908494015_ds_active_flag.up.sql         | 44 ++++++++++------------
 2 files changed, 39 insertions(+), 44 deletions(-)

diff --git 
a/traffic_ops/app/db/migrations/2022110908494015_ds_active_flag.down.sql 
b/traffic_ops/app/db/migrations/2022110908494015_ds_active_flag.down.sql
index f782558a18..01295ecbe1 100644
--- a/traffic_ops/app/db/migrations/2022110908494015_ds_active_flag.down.sql
+++ b/traffic_ops/app/db/migrations/2022110908494015_ds_active_flag.down.sql
@@ -32,22 +32,15 @@ DROP TYPE public.ds_active_state;
 
 UPDATE public.deliveryservice_request
 SET
-       deliveryservice = jsonb_set(deliveryservice, '{active}', 'true')
+       deliveryservice = deliveryservice || '{"active": true}'
 WHERE
-       deliveryservice IS NOT NULL
-       AND
-       deliveryservice ? 'active'
-       AND
        deliveryservice ->> 'active' = 'ACTIVE';
 
 UPDATE public.deliveryservice_request
 SET
-       deliveryservice = jsonb_set(deliveryservice, '{active}', 'false')
+       deliveryservice = deliveryservice || '{"active": false}'
 WHERE
-       deliveryservice IS NOT NULL
-       AND
-       deliveryservice ? 'active'
-       AND (
+       (
                deliveryservice ->> 'active' = 'PRIMED'
                OR
                deliveryservice ->> 'active' = 'INACTIVE'
@@ -55,23 +48,29 @@ WHERE
 
 UPDATE public.deliveryservice_request
 SET
-       original = jsonb_set(original, '{active}', 'true')
+       original = original || '{"active": true}'
 WHERE
-       original IS NOT NULL
-       AND
-       original ? 'active'
-       AND
        original ->> 'active' = 'ACTIVE';
 
 UPDATE public.deliveryservice_request
 SET
-       original = jsonb_set(original, '{active}', 'false')
+       original = original || '{"active": false}'
 WHERE
-       original IS NOT NULL
-       AND
-       original ? 'active'
-       AND (
+       (
                original ->> 'active' = 'PRIMED'
                OR
                original ->> 'active' = 'INACTIVE'
        );
+
+
+UPDATE public.deliveryservice_request
+SET
+       original = original || CAST('{"lastUpdated": "' || 
replace(replace(original ->> 'lastUpdated', 'T', ' '), 'Z', '+00') || '"}' AS 
jsonb)
+WHERE
+       original ->> 'lastUpdated' IS NOT NULL;
+
+UPDATE public.deliveryservice_request
+SET
+       deliveryservice = deliveryservice || CAST('{"lastUpdated": "' || 
replace(replace(deliveryservice ->> 'lastUpdated', 'T', ' '), 'Z', '+00') || 
'"}' AS jsonb)
+WHERE
+       deliveryservice ->> 'lastUpdated' IS NOT NULL;
diff --git 
a/traffic_ops/app/db/migrations/2022110908494015_ds_active_flag.up.sql 
b/traffic_ops/app/db/migrations/2022110908494015_ds_active_flag.up.sql
index 98d50d142c..5d7e59b8ff 100644
--- a/traffic_ops/app/db/migrations/2022110908494015_ds_active_flag.up.sql
+++ b/traffic_ops/app/db/migrations/2022110908494015_ds_active_flag.up.sql
@@ -32,40 +32,36 @@ ALTER TABLE public.deliveryservice RENAME COLUMN 
active_state TO active;
 
 UPDATE public.deliveryservice_request
 SET
-       deliveryservice = jsonb_set(deliveryservice, '{active}', '"ACTIVE"')
+       deliveryservice = deliveryservice || '{"active": "ACTIVE"}'
 WHERE
-       deliveryservice IS NOT NULL
-       AND
-       deliveryservice ? 'active'
-       AND
-       (deliveryservice -> 'active')::boolean IS TRUE;
+       deliveryservice ->> 'active' = 'true';
 
 UPDATE public.deliveryservice_request
 SET
-       deliveryservice = jsonb_set(deliveryservice, '{active}', '"PRIMED"')
+       deliveryservice = deliveryservice || '{"active": "PRIMED"}'
 WHERE
-       deliveryservice IS NOT NULL
-       AND
-       deliveryservice ? 'active'
-       AND
-       (deliveryservice -> 'active')::boolean IS FALSE;
+       deliveryservice ->> 'active' = 'false';
 
 UPDATE public.deliveryservice_request
 SET
-       original = jsonb_set(original, '{active}', '"ACTIVE"')
+       original = original || '{"active": "ACTIVE"}'
 WHERE
-       original IS NOT NULL
-       AND
-       original ? 'active'
-       AND
-       (original -> 'active')::boolean IS TRUE;
+       original ->> 'active' = 'true';
 
 UPDATE public.deliveryservice_request
 SET
-       original = jsonb_set(original, '{active}', '"PRIMED"')
+       original = original || '{"active": "PRIMED"}'
 WHERE
-       original IS NOT NULL
-       AND
-       original ? 'active'
-       AND
-       (original -> 'active')::boolean IS FALSE;
+       original ->> 'active' = 'false';
+
+UPDATE public.deliveryservice_request
+SET
+       original = original || CAST('{"lastUpdated": "' || 
replace(replace(original ->> 'lastUpdated', ' ', 'T'), '+00', 'Z') || '"}' AS 
jsonb)
+WHERE
+       original ->> 'lastUpdated' IS NOT NULL;
+
+UPDATE public.deliveryservice_request
+SET
+       deliveryservice = deliveryservice || CAST('{"lastUpdated": "' || 
replace(replace(deliveryservice ->> 'lastUpdated', ' ', 'T'), '+00', 'Z') || 
'"}' AS jsonb)
+WHERE
+       deliveryservice ->> 'lastUpdated' IS NOT NULL;