0f2945fd3f
* feat(chalice): upgraded dependencies * feat(chalice): changed path analysis schema * feat(DB): click coordinate support * feat(chalice): changed path analysis issues schema feat(chalice): upgraded dependencies * fix(chalice): fixed pydantic issue * refactor(chalice): refresh token validator * feat(chalice): role restrictions * feat(chalice): EE path analysis changes * refactor(DB): changed creation queries refactor(DB): changed delte queries feat(DB): support new path analysis payload * feat(chalice): save path analysis card * feat(chalice): restrict access * feat(chalice): restrict access * feat(chalice): EE save new path analysis card
367 lines
No EOL
14 KiB
PL/PgSQL
367 lines
No EOL
14 KiB
PL/PgSQL
\set previous_version 'v1.5.4'
|
|
\set next_version 'v1.6.0'
|
|
SELECT openreplay_version() AS current_version,
|
|
openreplay_version() = :'previous_version' AS valid_previous,
|
|
openreplay_version() = :'next_version' AS is_next
|
|
\gset
|
|
|
|
\if :valid_previous
|
|
\echo valid previous DB version :'previous_version', starting DB upgrade to :'next_version'
|
|
BEGIN;
|
|
SELECT format($fn_def$
|
|
CREATE OR REPLACE FUNCTION openreplay_version()
|
|
RETURNS text AS
|
|
$$
|
|
SELECT '%1$s'
|
|
$$ LANGUAGE sql IMMUTABLE;
|
|
$fn_def$, :'next_version')
|
|
\gexec
|
|
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS public.dashboards
|
|
(
|
|
dashboard_id integer generated BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
project_id integer NOT NULL REFERENCES public.projects (project_id) ON DELETE CASCADE,
|
|
user_id integer NOT NULL REFERENCES public.users (user_id) ON DELETE SET NULL,
|
|
name text NOT NULL,
|
|
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
|
|
);
|
|
|
|
|
|
ALTER TABLE IF EXISTS metrics
|
|
DROP CONSTRAINT IF EXISTS null_project_id_for_template_only,
|
|
DROP CONSTRAINT IF EXISTS unique_key;
|
|
|
|
ALTER TABLE IF EXISTS metrics
|
|
ADD COLUMN IF NOT EXISTS edited_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
ADD COLUMN IF NOT EXISTS is_pinned boolean NOT NULL DEFAULT FALSE,
|
|
ADD COLUMN IF NOT EXISTS category text NULL DEFAULT 'custom',
|
|
ADD COLUMN IF NOT EXISTS is_predefined boolean NOT NULL DEFAULT FALSE,
|
|
ADD COLUMN IF NOT EXISTS is_template boolean NOT NULL DEFAULT FALSE,
|
|
ADD COLUMN IF NOT EXISTS predefined_key text NULL DEFAULT NULL,
|
|
ADD COLUMN IF NOT EXISTS default_config jsonb NOT NULL DEFAULT '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}'::jsonb,
|
|
ALTER COLUMN project_id DROP NOT NULL,
|
|
ADD CONSTRAINT null_project_id_for_template_only
|
|
CHECK ( (metrics.category != 'custom') != (metrics.project_id IS NOT NULL) ),
|
|
ADD CONSTRAINT unique_key UNIQUE (predefined_key);
|
|
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS public.dashboard_widgets
|
|
(
|
|
widget_id integer generated BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
dashboard_id integer NOT NULL REFERENCES public.dashboards (dashboard_id) ON DELETE CASCADE,
|
|
metric_id integer NOT NULL REFERENCES public.metrics (metric_id) ON DELETE CASCADE,
|
|
user_id integer NOT NULL REFERENCES public.users (user_id) ON DELETE SET NULL,
|
|
created_at timestamp NOT NULL DEFAULT timezone('utc'::text, now()),
|
|
config jsonb NOT NULL DEFAULT '{}'::jsonb
|
|
);
|
|
|
|
ALTER TABLE events_common.requests
|
|
ADD COLUMN IF NOT EXISTS host text NULL,
|
|
ADD COLUMN IF NOT EXISTS path text NULL,
|
|
ADD COLUMN IF NOT EXISTS query text NULL;
|
|
|
|
ALTER TABLE events.pages
|
|
ADD COLUMN IF NOT EXISTS query text NULL;
|
|
|
|
DO
|
|
$$
|
|
BEGIN
|
|
IF EXISTS(SELECT *
|
|
FROM information_schema.columns
|
|
WHERE table_schema = 'events'
|
|
AND table_name = 'pages'
|
|
AND column_name = 'base_path')
|
|
THEN
|
|
ALTER TABLE events.pages
|
|
DROP COLUMN IF EXISTS path;
|
|
ALTER TABLE events.pages
|
|
RENAME COLUMN base_path TO path;
|
|
DROP INDEX IF EXISTS events.pages_base_path_gin_idx2;
|
|
DROP INDEX IF EXISTS pages_base_path_idx2;
|
|
ALTER INDEX IF EXISTS events.pages_base_path_gin_idx RENAME TO pages_path_gin_idx;
|
|
ALTER INDEX IF EXISTS events.pages_base_path_idx RENAME TO pages_path_idx;
|
|
ALTER INDEX IF EXISTS events.pages_base_path_session_id_timestamp_idx RENAME TO pages_path_session_id_timestamp_idx;
|
|
ALTER INDEX IF EXISTS events.pages_base_path_base_pathLNGT2_idx RENAME TO pages_path_pathLNGT2_idx;
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
COMMIT;
|
|
|
|
ALTER TYPE metric_view_type ADD VALUE IF NOT EXISTS 'areaChart';
|
|
ALTER TYPE metric_view_type ADD VALUE IF NOT EXISTS 'barChart';
|
|
ALTER TYPE metric_view_type ADD VALUE IF NOT EXISTS 'stackedBarChart';
|
|
ALTER TYPE metric_view_type ADD VALUE IF NOT EXISTS 'stackedBarLineChart';
|
|
ALTER TYPE metric_view_type ADD VALUE IF NOT EXISTS 'overview';
|
|
ALTER TYPE metric_view_type ADD VALUE IF NOT EXISTS 'map';
|
|
ALTER TYPE metric_type ADD VALUE IF NOT EXISTS 'predefined';
|
|
|
|
INSERT INTO metrics (name, category, default_config, is_predefined, is_template, is_public, predefined_key, metric_type,
|
|
view_type)
|
|
VALUES ('Captured sessions', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'count_sessions', 'predefined', 'overview'),
|
|
('Request Load Time', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_request_load_time', 'predefined', 'overview'),
|
|
('Page Load Time', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_page_load_time', 'predefined', 'overview'),
|
|
('Image Load Time', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_image_load_time', 'predefined', 'overview'),
|
|
('DOM Content Load Start', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_dom_content_load_start', 'predefined', 'overview'),
|
|
('First Meaningful paint', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_first_contentful_pixel', 'predefined', 'overview'),
|
|
('No. of Visited Pages', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_visited_pages', 'predefined', 'overview'),
|
|
('Session Duration', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_session_duration', 'predefined', 'overview'),
|
|
('DOM Build Time', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_pages_dom_buildtime', 'predefined', 'overview'),
|
|
('Pages Response Time', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_pages_response_time', 'predefined', 'overview'),
|
|
('Response Time', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_response_time', 'predefined', 'overview'),
|
|
('First Paint', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_first_paint', 'predefined', 'overview'),
|
|
('DOM Content Loaded', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_dom_content_loaded', 'predefined', 'overview'),
|
|
('Time Till First byte', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_till_first_byte', 'predefined', 'overview'),
|
|
('Time To Interactive', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_time_to_interactive', 'predefined', 'overview'),
|
|
('Captured requests', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'count_requests', 'predefined', 'overview'),
|
|
('Time To Render', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_time_to_render', 'predefined', 'overview'),
|
|
('Memory Consumption', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_used_js_heap_size', 'predefined', 'overview'),
|
|
('CPU Load', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_cpu', 'predefined', 'overview'),
|
|
('Frame rate', 'overview', '{
|
|
"col": 1,
|
|
"row": 1,
|
|
"position": 0
|
|
}', true, true, true, 'avg_fps', 'predefined', 'overview'),
|
|
|
|
('Sessions Affected by JS Errors', 'errors', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'impacted_sessions_by_js_errors', 'predefined', 'barChart'),
|
|
('Top Domains with 4xx Fetch Errors', 'errors', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'domains_errors_4xx', 'predefined', 'lineChart'),
|
|
('Top Domains with 5xx Fetch Errors', 'errors', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'domains_errors_5xx', 'predefined', 'lineChart'),
|
|
('Errors per Domain', 'errors', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'errors_per_domains', 'predefined', 'table'),
|
|
('Fetch Calls with Errors', 'errors', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'calls_errors', 'predefined', 'table'),
|
|
('Errors by Type', 'errors', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'errors_per_type', 'predefined', 'barChart'),
|
|
('Errors by Origin', 'errors', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'resources_by_party', 'predefined', 'stackedBarChart'),
|
|
|
|
('Speed Index by Location', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'speed_location', 'predefined', 'map'),
|
|
('Slowest Domains', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'slowest_domains', 'predefined', 'table'),
|
|
('Sessions per Browser', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'sessions_per_browser', 'predefined', 'table'),
|
|
('Time To Render', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'time_to_render', 'predefined', 'areaChart'),
|
|
('Sessions Impacted by Slow Pages', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'impacted_sessions_by_slow_pages', 'predefined', 'areaChart'),
|
|
('Memory Consumption', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'memory_consumption', 'predefined', 'areaChart'),
|
|
('CPU Load', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'cpu', 'predefined', 'areaChart'),
|
|
('Frame Rate', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'fps', 'predefined', 'areaChart'),
|
|
('Crashes', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'crashes', 'predefined', 'areaChart'),
|
|
('Resources Loaded vs Visually Complete', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'resources_vs_visually_complete', 'predefined', 'areaChart'),
|
|
('DOM Build Time', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'pages_dom_buildtime', 'predefined', 'areaChart'),
|
|
('Pages Response Time', 'performance', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'pages_response_time', 'predefined', 'areaChart'),
|
|
('Pages Response Time Distribution', 'performance', '{
|
|
"col": 4,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'pages_response_time_distribution', 'predefined', 'barChart'),
|
|
|
|
('Missing Resources', 'resources', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'missing_resources', 'predefined', 'table'),
|
|
('Slowest Resources', 'resources', '{
|
|
"col": 4,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'slowest_resources', 'predefined', 'table'),
|
|
('Resources Fetch Time', 'resources', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'resources_loading_time', 'predefined', 'table'),
|
|
('Resource Loaded vs Response End', 'resources', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'resource_type_vs_response_end', 'predefined', 'stackedBarLineChart'),
|
|
('Breakdown of Loaded Resources', 'resources', '{
|
|
"col": 2,
|
|
"row": 2,
|
|
"position": 0
|
|
}', true, true, true, 'resources_count_by_type', 'predefined', 'stackedBarChart')
|
|
ON CONFLICT (predefined_key) DO UPDATE
|
|
SET name=excluded.name,
|
|
category=excluded.category,
|
|
default_config=excluded.default_config,
|
|
is_predefined=excluded.is_predefined,
|
|
is_template=excluded.is_template,
|
|
is_public=excluded.is_public,
|
|
metric_type=excluded.metric_type,
|
|
view_type=excluded.view_type;
|
|
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_host_nn_idx ON events_common.requests (host) WHERE host IS NOT NULL;
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_host_nn_gin_idx ON events_common.requests USING GIN (host gin_trgm_ops) WHERE host IS NOT NULL;
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_path_nn_idx ON events_common.requests (path) WHERE path IS NOT NULL;
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_path_nn_gin_idx ON events_common.requests USING GIN (path gin_trgm_ops) WHERE path IS NOT NULL;
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_query_nn_idx ON events_common.requests (query) WHERE query IS NOT NULL;
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_query_nn_gin_idx ON events_common.requests USING GIN (query gin_trgm_ops) WHERE query IS NOT NULL;
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS pages_query_nn_idx ON events.pages (query) WHERE query IS NOT NULL;
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS pages_query_nn_gin_idx ON events.pages USING GIN (query gin_trgm_ops) WHERE query IS NOT NULL;
|
|
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS pages_path_session_id_timestamp_idx ON events.pages (path, session_id, timestamp);
|
|
CREATE INDEX CONCURRENTLY IF NOT EXISTS pages_path_pathLNGT2_idx ON events.pages (path) WHERE length(path) > 2;
|
|
|
|
\elif :is_next
|
|
\echo new version detected :'next_version', nothing to do
|
|
\else
|
|
\warn skipping DB upgrade of :'next_version', expected previous version :'previous_version', found :'current_version'
|
|
\endif |