Migrate old pre-1.37 "Custom Drill-through" settings to x.37+ "Click Behavior"

[camsaul]

We need to update Card and DashboardCard visualization_settings and convert them from the old format to the new format where appropriate. @flamber has written SQL to do this for Postgres specifically:

-- MAKE SURE YOU HAVE BACKUPS BEFORE RUNNING. RUN THE "getter-select" TO SEE WHICH COLUMNS MIGHT BE UPDATED.
-- WARNING - THIS WILL REPLACE ANY 37+ "CLICK BEHAVIOR" WITH DATA FROM THE OLD <=36 "CUSTOM DRILL-THROUGH"



-- Function credit to https://stackoverflow.com/a/49688529
create or replace function jsonb_merge(CurrentData jsonb,newData jsonb)
 returns jsonb
 language sql
 immutable
as $jsonb_merge_func$
 select case jsonb_typeof(CurrentData)
   when 'object' then case jsonb_typeof(newData)
     when 'object' then (
       select    jsonb_object_agg(k, case
                   when e2.v is null then e1.v
                   when e1.v is null then e2.v
                   when e1.v = e2.v then e1.v 
                   else jsonb_merge(e1.v, e2.v)
                 end)
       from      jsonb_each(CurrentData) e1(k, v)
       full join jsonb_each(newData) e2(k, v) using (k)
     )
     else newData
   end
   when 'array' then CurrentData || newData
   else newData
 end
$jsonb_merge_func$;



-- MAKE SURE YOU HAVE BACKUPS BEFORE RUNNING. RUN THE "getter-select" TO SEE WHICH COLUMNS MIGHT BE UPDATED.
-- WARNING - THIS WILL REPLACE ANY 37+ "CLICK BEHAVIOR" WITH DATA FROM THE OLD <=36 "CUSTOM DRILL-THROUGH"
update report_dashboardcard
set visualization_settings=flamberfix.custom_drill_to_click_behavior_converter
from (
    -- START of getter
	select dashcard.id, jsonb_strip_nulls(
		  jsonb_merge(
			  jsonb_merge(
				  dashcard.visualization_settings::jsonb
			      ,
				  jsonb_strip_nulls(jsonb_build_object('click_behavior',
				    (SELECT
				       jsonb_strip_nulls(jsonb_build_object(
				         'type', clickType,
				         'linkType', 'url',
				         'linkTemplate', linkTemplate
				       ))
				     FROM (
				     	  SELECT
						    jsonb_merge(card.visualization_settings::jsonb, dashcard.visualization_settings::jsonb)::jsonb->>'click' as clickType,
						    jsonb_merge(card.visualization_settings::jsonb, dashcard.visualization_settings::jsonb)::jsonb->>'click_link_template' as linkTemplate
				     ) as json_viz
				     WHERE clickType = 'link' AND linkTemplate IS NOT NULL
				    )
				  ))::jsonb
			  )
			  ,
			  jsonb_strip_nulls(jsonb_build_object('column_settings', (
			    SELECT
			        jsonb_strip_nulls(jsonb_object_agg(field,
			          jsonb_strip_nulls(jsonb_build_object('click_behavior',
			            jsonb_strip_nulls(jsonb_build_object(
			              'type', clickType,
			              'linkType', 'url',
			              'linkTemplate', linkTemplate,
			              'linkTextTemplate', linkTextTemplate
			            ))
			          ))
			        ))::jsonb AS fields
			    FROM (
				  SELECT
				    key as field,
				    value::jsonb->'view_as' as clickType,
				    value::jsonb->'link_template' as linkTemplate,
				    value::jsonb->'link_text' as linkTextTemplate
				  FROM jsonb_each_text(jsonb_merge(card.visualization_settings::jsonb, dashcard.visualization_settings::jsonb)::jsonb->'column_settings')
				) as json_table
			    WHERE jsonb_typeof(clickType)!='null' AND jsonb_typeof(linkTemplate)!='null'
			  )
			  ))::jsonb
			)
		)::jsonb as custom_drill_to_click_behavior_converter
	from report_dashboardcard dashcard
	left join report_card card on card.id = dashcard.card_id
	where dashcard.card_id is not null and (
	  card.visualization_settings like '%"link_template":%' or card.visualization_settings like '%"click_link_template":%'
	  or
	  dashcard.visualization_settings like '%"link_template":%' or dashcard.visualization_settings like '%"click_link_template":%'
	)
	-- END of getter
) as flamberfix
where report_dashboardcard.id=flamberfix.id and report_dashboardcard.visualization_settings::jsonb!=flamberfix.custom_drill_to_click_behavior_converter;



DROP FUNCTION IF exists jsonb_merge(CurrentData jsonb,newData jsonb);

To get this working for MySQL & H2 as well we can leverage the data migrations code we have in metabase.db.data-migrations. An example data migration for viz settings would look something like

(defmigration ^{:added "0.39.0"} migrate-map-regions
  (reduce
   (fn [_ {card-id :id, {map-region :map.region, :as viz-settings} :visualization_settings}]
     ;; find viz settings meeting some condition
     (when (= map-region "us_states")
       ;; apply some changes to viz settings
       (let [new-settings (update viz-settings :map.region str/upper-case)]
         ;; save updated viz settings
         (db/update! Card card-id :visualization_settings new-settings))))
   nil
   (db/select-reducible [Card :id :visualization_settings])))

@flamber or @paulrosenzweig might be able to help out with questions around how the format has changed between old and new versions