SQLite: pivioting summary with sub selects and triggers

Accounting and summaries can be a drag in SQL. This is just one reminder of such a problem...

One audit table and one summary table with counts counts counts....


DROP TABLE IF EXISTS SUMMARIES;
CREATE TABLE "SUMMARIES" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"value" INT,
"YEAR" INTEGER DEFAULT NULL,
"MONTH" INTEGER DEFAULT NULL,
"ACTION" TEXT DEFAULT NULL,
"ENVIRONMENT" TEXT DEFAULT NULL,
"USER" TEXT DEFAULT NULL,
"SUCCESS" INT DEFAULT NULL,
"HOUR_OF_DAY" INTEGER DEFAULT NULL,
"DAY_OF_WEEK" TEXT DEFAULT NULL,
CONSTRAINT "ALL" UNIQUE ("YEAR", "MONTH", "ACTION", "ENVIRONMENT", "USER", "SUCCESS", "HOUR_OF_DAY", "DAY_OF_WEEK") ON CONFLICT ROLLBACK
);

-- ----------------------------
-- Table structure for "AUDIT_RECORD"
-- ----------------------------
DROP TABLE IF EXISTS "AUDIT_RECORD";
CREATE TABLE "AUDIT_RECORD" (
"ENVIRONMENT" TEXT(8) NOT NULL DEFAULT '-',
"USER" TEXT NOT NULL DEFAULT '-',
"TIMESTAMP" TEXT NOT NULL,
"CREATED_DT" REAL,
"SUCCESS" TEXT,
"PROJECT" TEXT NOT NULL,
"LABEL" TEXT,
"YEAR" INTEGER NOT NULL,
"MONTH" INTEGER NOT NULL,
"DAY" INTEGER NOT NULL,
"DAY_OF_MONTH" INTEGER NOT NULL,
"OWNER" TEXT,
"HOUR_OF_DAY" INTEGER,
"ACTION" TEXT NOT NULL,
PRIMARY KEY ("USER", "TIMESTAMP", "ENVIRONMENT")
);




Triggers for each of the counts as an after insert

CREATE TRIGGER "trigger_year_action_summary" AFTER INSERT ON "AUDIT_RECORD" FOR EACH ROW
BEGIN

INSERT INTO SUMMARIES ('VALUE','YEAR','MONTH','ENVIRONMENT','ACTION','USER','SUCCESS','HOUR_OF_DAY')
SELECT 0,new.YEAR,new.MONTH,new.ENVIRONMENT,new.ACTION,null,null,null
WHERE NOT EXISTS (
SELECT 1 FROM SUMMARIES WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null

);

UPDATE SUMMARIES
SET value = value + 1
WHERE
MONTH = new.MONTH
and ACTION = new.ACTION
and YEAR = new.YEAR
and environment = new.ENVIRONMENT
and success is null
and user is null
and hour_of_day is null
and day_of_week is null
;




select
c.id
,c.year as 'YEAR'
, c.environment as 'ENV'
, c.month as 'MONTH'
, c.value as 'total'
, x.value as 'builds'
, y.value as 'deploys'
, xs.value as 'bld_suc'
, xf.cvalue as 'bld_fail'
, ys.value as 'dly_suc'
, yf.cvalue as 'dly_fail'
from
summaries c
,(select year, month, value, environment from summaries where action = 'builds' and success is null ) x
,(select year, month, value, environment from summaries where action = 'deploy' and success is null ) y
,(select year, month, value, environment from summaries where action = 'builds' and success =0) xs
,(select year, month, value, environment, sum(value) as "cvalue" from summaries where action = 'builds' and success != 0 group by year, month, environment) xf
,(select year, month, value, environment from summaries where action = 'deploy' and success =0) ys
,(select year, month, value, environment, sum(value) as "cvalue" from summaries where action = 'deploy' and success != 0 group by year, month, environment) yf
where
c.year = 2010
and c.environment = 'PRD'
and c.action is null
and x.year = c.year
and x.environment = c.environment
and x.month = c.month
and y.year = c.year
and y.environment = c.environment
and y.month = c.month
and xs.year = c.year
and xs.environment = c.environment
and xs.month = c.month
and xf.year = c.year
and xf.environment = c.environment
and xf.month = c.month
and yf.year = c.year
and yf.environment = c.environment
and yf.month = c.month
and ys.year = c.year
and ys.environment = c.environment
and ys.month = c.month