Holistics-postgres public.bugs materialized view
Query
--b.internal_id --b.publish --b.last_seen --b.application_section --b.last_seen_date --b.last_seen_time --b.version_id --b.reviewer --b.last_editor_id --b.last_editor_is_tester --b.duplicated_of_id --b.is_favorite --b.application_section_id -- aggiungere info di base tryber? (eg. metalivello) -- e pensare a mat view con molti indici
SELECT
b.id as id,
b.status_id as status_id,
bs.name as status_label,
b.severity_id as severity_id,
INITCAP(bsv.name) as severity_label,
bsv.id::text || ' - ' || INITCAP(bsv.name) as numbered_severity_label,
b.created as created,
COALESCE(b.updated, b.created) as updated,
b.bug_replicability_id as bug_replicability_id,
brp.name as bug_replicability_label,
b.bug_type_id as bug_type_id,
bt.name as bug_type_label,
b.dev_id as device_id,
CASE
WHEN d.id IS NULL THEN FALSE
ELSE TRUE
END AS device_exists,
d.enabled::bool as device_enabled,
CASE
WHEN d.id IS NULL THEN 'Unknown'::text
ELSE d.form_factor
END AS device_form_factor,
b.manufacturer as device_manufacturer,
b.model as device_model,
b.os as device_os,
b.os_version as device_os_version,
CASE
WHEN d.form_factor = 'PC' THEN CONCAT(d.form_factor, ' ', b.os)
WHEN (d.form_factor = 'Tablet'
OR d.form_factor = 'Smartphone'
OR d.form_factor = 'Smartwatch'
OR d.form_factor = 'Smart-tv') THEN CONCAT(b.manufacturer, ' ', b.model)
WHEN d.id IS NULL THEN NULL
ELSE 'Other'
END as device_display_name,
b.is_perfect::bool as is_perfect,
b.is_duplicated::bool as is_duplicated,
b.wp_user_id as wp_user_id,
pf.id as tester_id,
b.campaign_id as campaign_id,
cp.class as campaign_class,
cp.family as campaign_family,
CASE
WHEN cp.status_id = 1 THEN 'Open'::text
WHEN cp.status_id = 2 THEN 'Closed'::text
ELSE 'Unknown'::text
END AS campaign_status,
cp.status_details as campaign_status_details,
cpcat.name AS campaign_category,
cptype.name AS campaign_type,
cp.end_date AS campaign_end_date,
cs.company AS campaign_customer
FROM
public.wp_appq_evd_bug b
LEFT JOIN public.wp_appq_evd_bug_type bt ON bt.id = b.bug_type_id
LEFT JOIN public.wp_appq_evd_bug_status bs ON bs.id = b.status_id
LEFT JOIN public.wp_appq_evd_severity bsv ON bsv.id = b.severity_id
LEFT JOIN public.wp_appq_evd_bug_replicability brp ON brp.id = b.bug_replicability_id
LEFT JOIN public.wp_appq_evd_profile pf ON pf.wp_user_id = b.wp_user_id
LEFT JOIN public.wp_appq_evd_campaign cp ON cp.id = b.campaign_id
LEFT JOIN public.wp_appq_campaign_type cptype ON cptype.id = cp.campaign_type_id
LEFT JOIN public.wp_appq_campaign_category cpcat ON cpcat.id = cptype.category_id
LEFT JOIN public.wp_appq_project pj ON pj.id = cp.project_id
LEFT JOIN public.wp_appq_customer cs ON cs.id = pj.customer_id
LEFT JOIN public.wp_crowd_appq_device d ON d.id = b.dev_id
Keys and Indexes
CREATE UNIQUE INDEX bugs_uniqueindex_id ON bugs (id);
CREATE INDEX bugs_statuslabel ON bugs (status_label);
CREATE INDEX bugs_severitylabel ON bugs (severity_label);
CREATE INDEX bugs_bugreplicabilitylabel ON bugs (bug_replicability_label);
CREATE INDEX bugs_bugtypelabel ON bugs (bug_type_label);
CREATE INDEX bugs_deviceid ON bugs (device_id);
CREATE INDEX bugs_isperfect ON bugs (is_perfect);
CREATE INDEX bugs_isduplicated ON bugs (is_duplicated);
CREATE INDEX bugs_wpuserid ON bugs (wp_user_id);
CREATE INDEX bugs_testerid ON bugs (tester_id);
CREATE INDEX bugs_campaignid ON bugs (campaign_id);
CREATE INDEX bugs_campaignclass ON bugs (campaign_class);
CREATE INDEX bugs_campaignfamily ON bugs (campaign_family);
CREATE INDEX bugs_campaignstatus ON bugs (campaign_status);
CREATE INDEX bugs_campaignstatusdetails ON bugs (campaign_status_details);
CREATE INDEX bugs_campaigncategory ON bugs (campaign_category);
CREATE INDEX bugs_campaigntype ON bugs (campaign_type);
CREATE INDEX bugs_customer ON bugs (customer);
CREATE INDEX bugs_campaignexists ON bugs (campaign_exists);
CREATE INDEX bugs_customerexists ON bugs (customer_exists);
CREATE INDEX bugs_customerid ON bugs (customer_id);