DO $$ DECLARE previous_version CONSTANT text := 'v1.9.0-ee'; next_version CONSTANT text := 'v1.10.0-ee'; BEGIN IF (SELECT openreplay_version()) = previous_version THEN raise notice 'valid previous DB version'; ELSEIF (SELECT openreplay_version()) = next_version THEN raise notice 'new version detected, nothing to do'; ELSE RAISE EXCEPTION 'upgrade to % failed, invalid previous version, expected %, got %', next_version,previous_version,(SELECT openreplay_version()); END IF; END ; $$ LANGUAGE plpgsql; BEGIN; CREATE OR REPLACE FUNCTION openreplay_version() RETURNS text AS $$ SELECT 'v1.10.0-ee' $$ LANGUAGE sql IMMUTABLE; -- Backup dashboard & search data: DO $$ BEGIN IF NOT (SELECT EXISTS(SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'backup_v1_10_0')) THEN CREATE SCHEMA backup_v1_10_0; CREATE TABLE backup_v1_10_0.dashboards ( dashboard_id integer, project_id integer, user_id integer, name text NOT NULL, description text NOT NULL DEFAULT '', is_public boolean NOT NULL DEFAULT TRUE, is_pinned boolean NOT NULL DEFAULT FALSE, created_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()), deleted_at timestamp NULL DEFAULT NULL ); CREATE TABLE backup_v1_10_0.dashboard_widgets ( widget_id integer, dashboard_id integer, metric_id integer, user_id integer, created_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()), config jsonb NOT NULL DEFAULT '{}'::jsonb ); CREATE TABLE backup_v1_10_0.searches ( search_id integer, project_id integer, user_id integer, name text not null, filter jsonb not null, created_at timestamp default timezone('utc'::text, now()) not null, deleted_at timestamp, is_public boolean NOT NULL DEFAULT False ); CREATE TABLE backup_v1_10_0.metrics ( metric_id integer, project_id integer, user_id integer, name text NOT NULL, is_public boolean NOT NULL DEFAULT FALSE, active boolean NOT NULL DEFAULT TRUE, created_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()), deleted_at timestamp, edited_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()), metric_type text NOT NULL, view_type text NOT NULL, metric_of text NOT NULL DEFAULT 'sessionCount', metric_value text[] NOT NULL DEFAULT '{}'::text[], metric_format text, category text NULL DEFAULT 'custom', is_pinned boolean NOT NULL DEFAULT FALSE, is_predefined boolean NOT NULL DEFAULT FALSE, is_template boolean NOT NULL DEFAULT FALSE, predefined_key text NULL DEFAULT NULL, default_config jsonb NOT NULL ); CREATE TABLE backup_v1_10_0.metric_series ( series_id integer, metric_id integer, index integer NOT NULL, name text NULL, filter jsonb NOT NULL, created_at timestamp DEFAULT timezone('utc'::text, now()) NOT NULL, deleted_at timestamp ); INSERT INTO backup_v1_10_0.dashboards(dashboard_id, project_id, user_id, name, description, is_public, is_pinned, created_at, deleted_at) SELECT dashboard_id, project_id, user_id, name, description, is_public, is_pinned, created_at, deleted_at FROM public.dashboards ORDER BY dashboard_id; INSERT INTO backup_v1_10_0.metrics(metric_id, project_id, user_id, name, is_public, active, created_at, deleted_at, edited_at, metric_type, view_type, metric_of, metric_value, metric_format, category, is_pinned, is_predefined, is_template, predefined_key, default_config) SELECT metric_id, project_id, user_id, name, is_public, active, created_at, deleted_at, edited_at, metric_type, view_type, metric_of, metric_value, metric_format, category, is_pinned, is_predefined, is_template, predefined_key, default_config FROM public.metrics ORDER BY metric_id; INSERT INTO backup_v1_10_0.metric_series(series_id, metric_id, index, name, filter, created_at, deleted_at) SELECT series_id, metric_id, index, name, filter, created_at, deleted_at FROM public.metric_series ORDER BY series_id; INSERT INTO backup_v1_10_0.dashboard_widgets(widget_id, dashboard_id, metric_id, user_id, created_at, config) SELECT widget_id, dashboard_id, metric_id, user_id, created_at, config FROM public.dashboard_widgets ORDER BY widget_id; INSERT INTO backup_v1_10_0.searches(search_id, project_id, user_id, name, filter, created_at, deleted_at, is_public) SELECT search_id, project_id, user_id, name, filter, created_at, deleted_at, is_public FROM public.searches ORDER BY search_id; END IF; END $$ LANGUAGE plpgsql; CREATE TABLE IF NOT EXISTS frontend_signals ( project_id integer NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE, user_id integer NOT NULL REFERENCES users (user_id) ON DELETE CASCADE, timestamp bigint NOT NULL, action text NOT NULL, source text NOT NULL, category text NOT NULL, data jsonb, created_at timestamp DEFAULT timezone('utc'::text, now()) NOT NULL ); CREATE INDEX IF NOT EXISTS frontend_signals_user_id_idx ON frontend_signals (user_id); CREATE TABLE IF NOT EXISTS assist_records ( record_id integer generated BY DEFAULT AS IDENTITY PRIMARY KEY, project_id integer NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE, user_id integer NOT NULL REFERENCES users (user_id) ON DELETE SET NULL, session_id bigint NOT NULL REFERENCES sessions (session_id) ON DELETE SET NULL, created_at bigint NOT NULL DEFAULT (EXTRACT(EPOCH FROM now() at time zone 'utc') * 1000)::bigint, deleted_at timestamp without time zone NULL DEFAULT NULL, name text NOT NULL, file_key text NOT NULL, duration integer NOT NULL ); ALTER TYPE webhook_type ADD VALUE IF NOT EXISTS 'msteams'; UPDATE metrics SET is_public= TRUE; CREATE OR REPLACE FUNCTION get_global_key(key text) RETURNS text AS $$ DECLARE events_map CONSTANT JSONB := '{ "SESSIONS": "sessions", "sessionCount": "sessionCount", "CLICK": "click", "INPUT": "input", "LOCATION": "location", "CUSTOM": "custom", "REQUEST": "request", "FETCH": "fetch", "GRAPHQL": "graphql", "STATEACTION": "stateAction", "ERROR": "error", "CLICK_IOS": "clickIos", "INPUT_IOS": "inputIos", "VIEW_IOS": "viewIos", "CUSTOM_IOS": "customIos", "REQUEST_IOS": "requestIos", "ERROR_IOS": "errorIos", "DOM_COMPLETE": "domComplete", "LARGEST_CONTENTFUL_PAINT_TIME": "largestContentfulPaintTime", "TIME_BETWEEN_EVENTS": "timeBetweenEvents", "TTFB": "ttfb", "AVG_CPU_LOAD": "avgCpuLoad", "AVG_MEMORY_USAGE": "avgMemoryUsage", "FETCH_FAILED": "fetchFailed", "FETCH_URL": "fetchUrl", "FETCH_STATUS_CODE": "fetchStatusCode", "FETCH_METHOD": "fetchMethod", "FETCH_DURATION": "fetchDuration", "FETCH_REQUEST_BODY": "fetchRequestBody", "FETCH_RESPONSE_BODY": "fetchResponseBody", "GRAPHQL_NAME": "graphqlName", "GRAPHQL_METHOD": "graphqlMethod", "GRAPHQL_REQUEST_BODY": "graphqlRequestBody", "GRAPHQL_RESPONSE_BODY": "graphqlResponseBody", "USEROS": "userOs", "USERBROWSER": "userBrowser", "USERDEVICE": "userDevice", "USERCOUNTRY": "userCountry", "USERID": "userId", "USERANONYMOUSID": "userAnonymousId", "REFERRER": "referrer", "REVID": "revId", "USEROS_IOS": "userOsIos", "USERDEVICE_IOS": "userDeviceIos", "USERCOUNTRY_IOS": "userCountryIos", "USERID_IOS": "userIdIos", "USERANONYMOUSID_IOS": "userAnonymousIdIos", "REVID_IOS": "revIdIos", "DURATION": "duration", "PLATFORM": "platform", "METADATA": "metadata", "ISSUE": "issue", "EVENTS_COUNT": "eventsCount", "UTM_SOURCE": "utmSource", "UTM_MEDIUM": "utmMedium", "UTM_CAMPAIGN": "utmCampaign" }'; BEGIN RETURN jsonb_extract_path(events_map, key); END; $$ LANGUAGE plpgsql IMMUTABLE; ALTER TABLE IF EXISTS metrics ALTER COLUMN metric_type TYPE text, ALTER COLUMN metric_type SET DEFAULT 'timeseries', ALTER COLUMN view_type TYPE text, ALTER COLUMN view_type SET DEFAULT 'lineChart', ADD COLUMN IF NOT EXISTS thumbnail text; DO $$ BEGIN IF EXISTS(SELECT column_name FROM information_schema.columns WHERE table_name = 'metrics' AND column_name = 'is_predefined' AND table_schema = 'public') THEN -- 0. change metric_of UPDATE metrics SET metric_of=coalesce(replace(get_global_key(metric_of), '"', ''), left(metric_of, 1) || right(replace(initcap(metric_of), '_', ''), -1)) WHERE not is_predefined; -- 1. pre transform structure ALTER TABLE IF EXISTS metrics ADD COLUMN IF NOT EXISTS o_metric_id INTEGER, ADD COLUMN IF NOT EXISTS o_widget_id INTEGER; -- 2. insert predefined metrics related to dashboards as custom metrics INSERT INTO metrics(project_id, user_id, name, metric_type, view_type, metric_of, metric_value, metric_format, default_config, is_public, o_metric_id, o_widget_id) SELECT dashboards.project_id, dashboard_widgets.user_id, metrics.name, left(category, 1) || right(replace(initcap(category), ' ', ''), -1) AS metric_type, 'chart' AS view_type, left(predefined_key, 1) || right(replace(initcap(predefined_key), '_', ''), -1) AS metric_of, metric_value, metric_format, default_config, TRUE AS is_public, metrics.metric_id, dashboard_widgets.widget_id FROM metrics INNER JOIN dashboard_widgets USING (metric_id) INNER JOIN dashboards USING (dashboard_id) WHERE is_predefined; -- 3. update widgets UPDATE dashboard_widgets SET metric_id=metrics.metric_id FROM metrics WHERE metrics.o_widget_id IS NOT NULL AND dashboard_widgets.widget_id = metrics.o_widget_id; -- 4. delete predefined metrics DELETE FROM metrics WHERE is_predefined; ALTER TABLE IF EXISTS metrics DROP COLUMN IF EXISTS active, DROP COLUMN IF EXISTS is_predefined, DROP COLUMN IF EXISTS predefined_key, DROP COLUMN IF EXISTS is_template, DROP COLUMN IF EXISTS category, DROP COLUMN IF EXISTS o_metric_id, DROP COLUMN IF EXISTS o_widget_id, DROP CONSTRAINT IF EXISTS null_project_id_for_template_only, DROP CONSTRAINT IF EXISTS metrics_unique_key, DROP CONSTRAINT IF EXISTS unique_key; END IF; END; $$ LANGUAGE plpgsql; DROP TYPE IF EXISTS metric_type; DROP TYPE IF EXISTS metric_view_type; ALTER TABLE IF EXISTS events.clicks ADD COLUMN IF NOT EXISTS path text; DROP INDEX IF EXISTS events.clicks_url_gin_idx; DROP INDEX IF EXISTS events.inputs_label_value_idx; DROP INDEX IF EXISTS events.inputs_label_idx; DROP INDEX IF EXISTS events.pages_base_path_idx; DROP INDEX IF EXISTS events.pages_base_path_idx1; DROP INDEX IF EXISTS events.pages_base_path_idx2; DROP INDEX IF EXISTS events.pages_base_referrer_gin_idx1; DROP INDEX IF EXISTS events.pages_base_referrer_gin_idx2; DROP INDEX IF EXISTS events.resources_url_gin_idx; DROP INDEX IF EXISTS events.resources_url_idx; DROP INDEX IF EXISTS events.resources_url_hostpath_idx; DROP INDEX IF EXISTS events.resources_session_id_timestamp_idx; DROP INDEX IF EXISTS events.resources_duration_durationgt0_idx; DROP INDEX IF EXISTS events.state_actions_name_idx; DROP INDEX IF EXISTS events_common.requests_query_nn_idx; DROP INDEX IF EXISTS events_common.requests_host_nn_idx; DROP INDEX IF EXISTS events_common.issues_context_string_gin_idx; DROP INDEX IF EXISTS public.sessions_user_country_gin_idx; DROP INDEX IF EXISTS public.sessions_user_browser_gin_idx; DROP INDEX IF EXISTS public.sessions_user_os_gin_idx; DROP INDEX IF EXISTS public.issues_context_string_gin_idx; ALTER TABLE IF EXISTS projects ADD COLUMN IF NOT EXISTS beacon_size integer NOT NULL DEFAULT 0; -- To migrate saved search data SET client_min_messages TO NOTICE; CREATE OR REPLACE FUNCTION get_new_event_key(key text) RETURNS text AS $$ DECLARE events_map CONSTANT JSONB := '{ "CLICK": "click", "INPUT": "input", "LOCATION": "location", "CUSTOM": "custom", "REQUEST": "request", "FETCH": "fetch", "GRAPHQL": "graphql", "STATEACTION": "stateAction", "ERROR": "error", "CLICK_IOS": "clickIos", "INPUT_IOS": "inputIos", "VIEW_IOS": "viewIos", "CUSTOM_IOS": "customIos", "REQUEST_IOS": "requestIos", "ERROR_IOS": "errorIos", "DOM_COMPLETE": "domComplete", "LARGEST_CONTENTFUL_PAINT_TIME": "largestContentfulPaintTime", "TIME_BETWEEN_EVENTS": "timeBetweenEvents", "TTFB": "ttfb", "AVG_CPU_LOAD": "avgCpuLoad", "AVG_MEMORY_USAGE": "avgMemoryUsage", "FETCH_FAILED": "fetchFailed" }'; BEGIN RETURN jsonb_extract_path(events_map, key); END; $$ LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION get_new_event_filter_key(key text) RETURNS text AS $$ DECLARE event_filters_map CONSTANT JSONB := '{ "FETCH_URL": "fetchUrl", "FETCH_STATUS_CODE": "fetchStatusCode", "FETCH_METHOD": "fetchMethod", "FETCH_DURATION": "fetchDuration", "FETCH_REQUEST_BODY": "fetchRequestBody", "FETCH_RESPONSE_BODY": "fetchResponseBody", "GRAPHQL_NAME": "graphqlName", "GRAPHQL_METHOD": "graphqlMethod", "GRAPHQL_REQUEST_BODY": "graphqlRequestBody", "GRAPHQL_RESPONSE_BODY": "graphqlResponseBody" }'; BEGIN RETURN jsonb_extract_path(event_filters_map, key); END; $$ LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION get_new_filter_key(key text) RETURNS text AS $$ DECLARE filters_map CONSTANT JSONB := '{ "USEROS": "userOs", "USERBROWSER": "userBrowser", "USERDEVICE": "userDevice", "USERCOUNTRY": "userCountry", "USERID": "userId", "USERANONYMOUSID": "userAnonymousId", "REFERRER": "referrer", "REVID": "revId", "USEROS_IOS": "userOsIos", "USERDEVICE_IOS": "userDeviceIos", "USERCOUNTRY_IOS": "userCountryIos", "USERID_IOS": "userIdIos", "USERANONYMOUSID_IOS": "userAnonymousIdIos", "REVID_IOS": "revIdIos", "DURATION": "duration", "PLATFORM": "platform", "METADATA": "metadata", "ISSUE": "issue", "EVENTS_COUNT": "eventsCount", "UTM_SOURCE": "utmSource", "UTM_MEDIUM": "utmMedium", "UTM_CAMPAIGN": "utmCampaign" }'; BEGIN RETURN jsonb_extract_path(filters_map, key); END; $$ LANGUAGE plpgsql IMMUTABLE; DO $$ DECLARE row RECORD; events_att JSONB; event_filters_att JSONB; filters_att JSONB; element JSONB; s_element JSONB; new_value TEXT; new_events JSONB[]; new_filters JSONB[]; new_event_filters JSONB[]; changed BOOLEAN; planned_update JSONB[]; BEGIN planned_update := '{}'::jsonb[]; FOR row IN SELECT * FROM searches LOOP -- Transform events attributes events_att := row.filter -> 'events'; IF events_att IS NOT NULL THEN new_events := '{}'::jsonb[]; FOR element IN SELECT jsonb_array_elements(events_att) LOOP changed := FALSE; new_value := get_new_event_key(element ->> 'type'); if new_value IS NOT NULL THEN changed := TRUE; new_value := replace(new_value, '"', ''); element := element || jsonb_build_object('type', new_value); END IF; -- Transform event's sub-filters attributes event_filters_att := element -> 'filters'; new_event_filters := '{}'::jsonb[]; IF event_filters_att IS NOT NULL AND jsonb_array_length(event_filters_att) > 0 THEN FOR s_element IN SELECT jsonb_array_elements(event_filters_att) LOOP new_value := get_new_event_filter_key(s_element ->> 'type'); if new_value IS NOT NULL THEN changed := TRUE; new_value := replace(new_value, '"', ''); s_element := s_element || jsonb_build_object('type', new_value); new_event_filters := array_append(new_event_filters, s_element); END IF; END LOOP; element := element || jsonb_build_object('filters', new_event_filters); END IF; IF changed THEN new_events := array_append(new_events, element); END IF; END LOOP; IF array_length(new_events, 1) > 0 THEN row.filter := row.filter || jsonb_build_object('events', new_events); END IF; END IF; -- Transform filters attributes filters_att := row.filter -> 'filters'; IF filters_att IS NOT NULL THEN new_filters := '{}'::jsonb; FOR element IN SELECT jsonb_array_elements(filters_att) LOOP new_value := get_new_filter_key(element ->> 'type'); if new_value IS NOT NULL THEN new_value := replace(new_value, '"', ''); element := element || jsonb_build_object('type', new_value); new_filters := array_append(new_filters, element); END IF; END LOOP; IF array_length(new_filters, 1) > 0 THEN row.filter := row.filter || jsonb_build_object('filters', new_filters); END IF; END IF; IF array_length(new_events, 1) > 0 OR array_length(new_filters, 1) > 0 THEN planned_update := array_append(planned_update, jsonb_build_object('id', row.search_id, 'change', row.filter)); END IF; END LOOP; -- Update saved search IF array_length(planned_update, 1) > 0 THEN raise notice 'must update % elements',array_length(planned_update, 1); UPDATE searches SET filter=changes.change -> 'change' FROM (SELECT unnest(planned_update)) AS changes(change) WHERE search_id = (changes.change -> 'id')::integer; raise notice 'update done'; ELSE raise notice 'nothing to update'; END IF; END ; $$ LANGUAGE plpgsql; -- To migrate metric_series data DO $$ DECLARE row RECORD; events_att JSONB; event_filters_att JSONB; filters_att JSONB; element JSONB; s_element JSONB; new_value TEXT; new_events JSONB[]; new_filters JSONB[]; new_event_filters JSONB[]; changed BOOLEAN; planned_update JSONB[]; BEGIN planned_update := '{}'::jsonb[]; FOR row IN SELECT * FROM metric_series LOOP -- Transform events attributes events_att := row.filter -> 'events'; IF events_att IS NOT NULL THEN new_events := '{}'::jsonb[]; FOR element IN SELECT jsonb_array_elements(events_att) LOOP changed := FALSE; new_value := get_new_event_key(element ->> 'type'); if new_value IS NOT NULL THEN changed := TRUE; new_value := replace(new_value, '"', ''); element := element || jsonb_build_object('type', new_value); END IF; -- Transform event's sub-filters attributes event_filters_att := element -> 'filters'; new_event_filters := '{}'::jsonb[]; IF event_filters_att IS NOT NULL AND jsonb_array_length(event_filters_att) > 0 THEN FOR s_element IN SELECT jsonb_array_elements(event_filters_att) LOOP new_value := get_new_event_filter_key(s_element ->> 'type'); if new_value IS NOT NULL THEN changed := TRUE; new_value := replace(new_value, '"', ''); s_element := s_element || jsonb_build_object('type', new_value); new_event_filters := array_append(new_event_filters, s_element); END IF; END LOOP; element := element || jsonb_build_object('filters', new_event_filters); END IF; IF changed THEN new_events := array_append(new_events, element); END IF; END LOOP; IF array_length(new_events, 1) > 0 THEN row.filter := row.filter || jsonb_build_object('events', new_events); END IF; END IF; -- Transform filters attributes filters_att := row.filter -> 'filters'; IF filters_att IS NOT NULL THEN new_filters := '{}'::jsonb; FOR element IN SELECT jsonb_array_elements(filters_att) LOOP new_value := get_new_filter_key(element ->> 'type'); if new_value IS NOT NULL THEN new_value := replace(new_value, '"', ''); element := element || jsonb_build_object('type', new_value); new_filters := array_append(new_filters, element); END IF; END LOOP; IF array_length(new_filters, 1) > 0 THEN row.filter := row.filter || jsonb_build_object('filters', new_filters); END IF; END IF; IF array_length(new_events, 1) > 0 OR array_length(new_filters, 1) > 0 THEN planned_update := array_append(planned_update, jsonb_build_object('id', row.series_id, 'change', row.filter)); END IF; END LOOP; -- Update metric_series IF array_length(planned_update, 1) > 0 THEN raise notice 'must update % elements',array_length(planned_update, 1); UPDATE metric_series SET filter=changes.change -> 'change' FROM (SELECT unnest(planned_update)) AS changes(change) WHERE series_id = (changes.change -> 'id')::integer; raise notice 'update done'; ELSE raise notice 'nothing to update'; END IF; END ; $$ LANGUAGE plpgsql; DROP FUNCTION get_new_filter_key; DROP FUNCTION get_new_event_filter_key; DROP FUNCTION get_new_event_key; DROP FUNCTION get_global_key; DROP TABLE IF EXISTS public.funnels; ALTER TABLE IF EXISTS public.metrics ADD COLUMN IF NOT EXISTS data jsonb NULL; COMMIT; CREATE INDEX CONCURRENTLY IF NOT EXISTS clicks_selector_idx ON events.clicks (selector); CREATE INDEX CONCURRENTLY IF NOT EXISTS clicks_path_idx ON events.clicks (path); CREATE INDEX CONCURRENTLY IF NOT EXISTS clicks_path_gin_idx ON events.clicks USING GIN (path gin_trgm_ops); CREATE INDEX CONCURRENTLY IF NOT EXISTS issues_project_id_issue_id_idx ON public.issues (project_id, issue_id);