Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Browse files

Make changes in product_crash_ratio per bug.

  • Loading branch information...
commit 8e0595b34bd718a38e73868573ee8c780fd60417 1 parent 39d8663
@jberkus jberkus authored AdrianGaudebert committed
View
3  sql/upgrade/5.0/README.rst
@@ -6,6 +6,9 @@ This batch makes the following database changes:
bug #733021
Add data source for explosive crashes
+bug #738394
+ Reconcile crash ratio views with UI
+
...
The above changes should take only a few minutes to deploy.
View
23 sql/upgrade/5.0/explosive_crashes.sql
@@ -9,8 +9,8 @@ create table explosiveness (
product_version_id INT NOT NULL,
signature_id INT NOT NULL,
report_date DATE not null,
- oneday NUMERIC NOT NULL DEFAULT 0,
- threeday NUMERIC NOT NULL DEFAULT 0,
+ oneday NUMERIC,
+ threeday NUMERIC,
constraint explosiveness_key primary key ( product_version_id, signature_id, report_date )
);$q$,
-- owner of table; always breakpad_rw
@@ -37,6 +37,11 @@ DECLARE
-- mostly corresponds to Kairo "clampperadu"
mindiv_one INT := 30;
mindiv_three INT := 15;
+ mes_edate DATE;
+ mes_b3date DATE;
+ comp_e1date DATE;
+ comp_e3date DATE;
+ comp_bdate DATE;
BEGIN
-- this function populates a daily matview
-- for explosiveness
@@ -53,7 +58,7 @@ IF checkdata THEN
END IF;
-- check if product_adu and tcbs are updated
-SELECT 1
+PERFORM 1
FROM tcbs JOIN product_adu
ON tcbs.report_date = product_adu.adu_date
WHERE tcbs.report_date = updateday
@@ -63,7 +68,7 @@ IF NOT FOUND THEN
IF checkdata THEN
RAISE EXCEPTION 'Either product_adu or tcbs have not been updated to the end of %',updateday;
ELSE
- RAISE NOTICE 'Either product_adu or tcbs has not been updated, skipping.'
+ RAISE NOTICE 'Either product_adu or tcbs has not been updated, skipping.';
RETURN TRUE;
END IF;
END IF;
@@ -134,6 +139,8 @@ SELECT sum1day.signature_id,
as explosive_1day
FROM sum1day
LEFT OUTER JOIN agg9day USING ( signature_id, product_version_id );
+
+ANALYZE explosive_oneday;
-- create threeday temp table
CREATE TEMPORARY TABLE explosive_threeday
@@ -190,15 +197,17 @@ SELECT avg3day.signature_id,
FROM avg3day LEFT OUTER JOIN agg7day
USING ( signature_id, product_version_id );
+ANALYZE explosive_threeday;
+
-- truncate explosiveness
DELETE FROM explosiveness;
-- merge the two tables and insert
INSERT INTO explosiveness (
report_date, signature_id, product_version_id,
- oneday, threeday
-SELECT signature_id, product_version_id, explosive_1day, explosive_3day
-FROM explosive_oneday FULL OUTER JOIN explosive_threeday
+ oneday, threeday )
+SELECT updateday, signature_id, product_version_id, explosive_1day, explosive_3day
+FROM explosive_oneday LEFT OUTER JOIN explosive_threeday
USING ( signature_id, product_version_id )
ORDER BY product_version_id;
View
60 sql/upgrade/5.0/product_crash_ratio.sql
@@ -0,0 +1,60 @@
+\set ON_ERROR_STOP 1
+
+CREATE OR REPLACE VIEW product_crash_ratio AS
+WITH crcounts AS (
+ SELECT productdims_id AS product_version_id,
+ sum("count") as crashes,
+ adu_day as report_date
+ FROM daily_crashes
+ WHERE report_type IN ('C', 'p', 'T', 'P')
+ AND "count" > 0
+ GROUP BY productdims_id, adu_day
+),
+adusum AS (
+ SELECT product_version_id, adu_date, sum(adu_count) as adu_count
+ FROM product_adu
+ GROUP BY product_version_id, adu_date )
+SELECT crcounts.product_version_id, product_versions.product_name,
+ version_string, adu_date,
+ crashes, adu_count, throttle::numeric(5,2),
+ (crashes/throttle)::int as adjusted_crashes,
+ (adu_count/((crashes/throttle) * 100 ))::numeric(12,3) as crash_ratio
+FROM crcounts
+ JOIN product_versions ON crcounts.product_version_id = product_versions.product_version_id
+ JOIN adusum ON crcounts.report_date = adusum.adu_date
+ AND crcounts.product_version_id = adusum.product_version_id
+ JOIN product_release_channels ON product_versions.product_name
+ = product_release_channels.product_name
+ AND product_versions.build_type = product_release_channels.release_channel;
+
+ALTER VIEW product_crash_ratio OWNER TO breakpad_rw;
+GRANT SELECT ON product_crash_ratio TO analyst;
+
+CREATE OR REPLACE VIEW product_os_crash_ratio AS
+WITH crcounts AS (
+ SELECT productdims_id AS product_version_id,
+ os_short_name,
+ sum("count") as crashes,
+ adu_day as report_date
+ FROM daily_crashes
+ WHERE report_type IN ('C', 'p', 'T', 'P')
+ AND "count" > 0
+ GROUP BY productdims_id, adu_day, os_short_name
+)
+SELECT crcounts.product_version_id, product_versions.product_name,
+ version_string, os_names.os_short_name, os_names.os_name, adu_date,
+ crashes, adu_count, throttle::numeric(5,2),
+ (crashes/throttle)::int as adjusted_crashes,
+ (adu_count/((crashes/throttle) * 100 ))::numeric(12,3) as crash_ratio
+FROM crcounts
+ JOIN product_versions ON crcounts.product_version_id = product_versions.product_version_id
+ JOIN os_names ON crcounts.os_short_name::citext = os_names.os_short_name
+ JOIN product_adu ON crcounts.report_date = product_adu.adu_date
+ AND crcounts.product_version_id = product_adu.product_version_id
+ AND product_adu.os_name::citext = os_names.os_name
+ JOIN product_release_channels ON product_versions.product_name
+ = product_release_channels.product_name
+ AND product_versions.build_type = product_release_channels.release_channel;
+
+ALTER VIEW product_os_crash_ratio OWNER TO breakpad_rw;
+GRANT SELECT ON product_os_crash_ratio TO analyst;
View
5 sql/upgrade/5.0/upgrade.sh
@@ -15,6 +15,11 @@ echo 'data source for explosiveness'
echo 'bug 733021'
psql -f ${CURDIR}/explosive_crashes.sql breakpad
+echo '*********************************************************'
+echo 'make crash ratio views match UI'
+echo 'bug 738394'
+psql -f ${CURDIR}/product_crash_ratio.sql breakpad
+
#change version in DB
psql -c "SELECT update_socorro_db_version( '$VERSION' )" breakpad
Please sign in to comment.
Something went wrong with that request. Please try again.