CREATE TABLE ag_hourlymaster
(
   timestampid         TIMESTAMP         NOT NULL,
   groupid             FIXED(5)    NOT NULL,
   urlid               FIXED(9)    NOT NULL,
   categoryid          FIXED(6)    DEFAULT 1 NOT NULL,
   userid              FIXED(9)    NOT NULL,
   referrerid          FIXED(9)    NOT NULL,
   serverinfoid        FIXED(9)    NOT NULL,
   applianceid         FIXED(9)    NOT NULL,
   transcodeid         FIXED(9)    NOT NULL,
   actionid            FIXED(5)    NOT NULL,
   status              FIXED(9)    NOT NULL,
   userinfoid          FIXED(9)    NULL,
   refcount            FIXED(9)    NULL,
   avgresponsetime     FIXED(14,3) NULL,
   avgbytecount        FIXED(14,3) NULL,
   avgbytesdropped     FIXED(14,3) NULL,
   avgpacketsrequested FIXED(14,3) NULL,
   avgpacketsserved    FIXED(14,3) NULL,
   avgpacketsresent    FIXED(14,3) NULL,
   avgpacketsdropped   FIXED(14,3) NULL,
   avgplaytime         FIXED(14,3) NULL,
   bandwidth           FIXED(14,3) NULL,
   avgconnecttime      FIXED(14,3) NULL,
   avgbrowsetime       FIXED(14,3) NULL,
   reqmodid            FIXED(6)    NULL,
   respmodid           FIXED(6)    NULL,
   virusid             FIXED(9)    DEFAULT 1 NOT NULL,
   CONSTRAINT PK_hourlymaster PRIMARY KEY
            ( timestampid, applianceid, virusid, categoryid,
              transcodeid, serverinfoid, actionid,
              userid, status, urlid, referrerid, groupid)
)
//
CREATE INDEX IDX_hourly_userid ON ag_hourlymaster(userid)
//
CREATE INDEX IDX_hourly_appid ON ag_hourlymaster(applianceid)
//
CREATE VIEW v_ag_hourlymaster AS
SELECT  timestampid,
        groupid,
        urlid,
        categoryid,
        virusid,
        userid,
        referrerid,
        serverinfoid,
        applianceid,
        transcodeid,
        actionid,
        0 AS abortcodeid,
        status,
        userinfoid,
        refcount,
        avgresponsetime,
        avgbytecount,
        avgbytesdropped,
        avgpacketsrequested,
        avgpacketsserved,
        avgpacketsresent,
        avgpacketsdropped,
        avgplaytime,
        bandwidth,
        avgconnecttime,
        avgbrowsetime,
        reqmodid,
        respmodid,
        YEAR(timestampid)                AS dt_year,
        MONTH(timestampid)               AS dt_month,
        DAY(timestampid)                 AS dt_day,
        DAYOFWEEK(timestampid)           AS dt_dayofweek,
        HOUR(timestampid)                AS dt_hour,
        FLOOR((MONTH(timestampid)-1)/3)+1  AS dt_quarter,
        DATE(timestampid)                AS dt_date,
        (CASE WHEN ((transcodeid > 40 AND transcodeid < 56 ) OR
                    transcodeid = 201 OR transcodeid = 230 OR transcodeid = 231)
                 THEN 1
                 ELSE 0
         END) AS cache_hits,
        (CASE WHEN (transcodeid = 90 OR transcodeid = 145 OR transcodeid = 96)
                 THEN 1
                 ELSE 0
         END) AS reload_refs,
        0 AS abort_refs,
        (CASE WHEN (transcodeid > 89 AND transcodeid < 146)
                 THEN 1
                 ELSE 0
         END) AS non_cacheable_object_refs,
        (CASE WHEN ((transcodeid > 40 AND transcodeid < 56 ) OR
                    transcodeid = 201 OR transcodeid = 230 OR transcodeid = 231)
                 THEN 1
                 ELSE 0
         END) AS obj_hit_rate,
        (CASE WHEN ((transcodeid > 40 AND transcodeid < 56 ) OR
                    transcodeid = 201 OR transcodeid = 230 OR transcodeid = 231)
                 THEN avgbytecount
                 ELSE 0
         END) AS byte_hit_rate,
        (CASE WHEN transcodeid < 201
                THEN 1
                ELSE 0
         END) AS tcp_refs,
        (CASE WHEN (transcodeid > 200 and transcodeid < 230)
                THEN 1
                ELSE 0
         END) AS icp_refs,
        (CASE WHEN ((transcodeid > 70 AND transcodeid < 180) OR
                    transcodeid = 221 OR transcodeid = 232)
                THEN 1
                ELSE 0
         END) AS miss_refs,
        (CASE WHEN ((transcodeid > 40 AND transcodeid < 56 ) OR
                    transcodeid = 201 OR transcodeid = 230 OR transcodeid = 231)
                THEN avgbytecount
                ELSE 0
         END) AS cache_bytes,
        (CASE WHEN ((transcodeid > 70 AND transcodeid < 180) OR
                    transcodeid = 221 OR transcodeid = 232)
                THEN avgbytecount
                ELSE 0
         END) AS miss_bytes,
        (CASE WHEN (transcodeid > 89 AND transcodeid < 146)
                THEN avgbytecount
                ELSE 0
         END) AS non_cacheable_bytes
    FROM ag_hourlymaster
//
CREATE TABLE ag_abortcode
(
   abortcodeid FIXED(5)     CONSTRAINT pk_abortcode PRIMARY KEY,
   abortcode   VARCHAR(100) NOT NULL,
   description VARCHAR(255) NULL
)
//
CREATE UNIQUE INDEX IDX_abortcode
     ON ag_abortcode ( abortcode )
//
INSERT INTO ag_abortcode ( abortcodeid, abortcode, description ) VALUES ( 0, '-', '-' )