Sunday, 4 November 2012

Categorizr for APEX - extended

Software evolves.

And I think it's going on right now. In April, Christian Rokitta blogged about Categorizr.

He adapted some PHP code from Brett Jankord and suited it to PL/SQL.

When I was building the mobile application for my AUSOUG presentation, I found it useful for statistics gathering... but I wanted more.

I added a few more functions, and included an object type to allow queries shown further below.
These functions broke/butchered the agent string further into
  • OS 
  • OS Version
  • Browser
  • Browser version
I think I've done well to cover the major data sets.
CREATE TYPE categorizr_agent_details IS OBJECT (
     agent                 VARCHAR2 (2000)
    ,device                VARCHAR2 (2000)
    ,os                    VARCHAR2 (2000)
    ,os_version            VARCHAR2 (2000)
    ,browser               VARCHAR2 (2000)
    ,browser_version       VARCHAR2 (2000)
   );
/

CREATE OR REPLACE PACKAGE categorizr
AS
   /******************************************************************************
      NAME:       categorizr
      PURPOSE:    detect web user agent device type

      Based on:
      Categorizr Version 1.1
      http://www.brettjankord.com/2012/01/16/categorizr-a-modern-device-detection-script/
      Written by Brett Jankord - Copyright © 2011

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      0.1        30- 3-2012  crokitta         Created this package.
      0.2        24-10-2012  swesley          Included browser/os gets
                                              With inspiration from http://barakhshan.wetpaint.com/page/detecting+os+and+browser+pl-sql
                                              and help from my own dataset. Doubtful all scenarios considered
   ******************************************************************************/
   g_tablets_as_desktops   BOOLEAN := FALSE; --If TRUE, tablets will be categorized as desktops
   g_smarttv_as_desktops   BOOLEAN := FALSE; --If TRUE, smartTVs will be categorized as desktops
   g_user_agent            VARCHAR2 (2000); -- User Agent String used for detection

   g_agent_details  categorizr_agent_details;

   FUNCTION get_agent_details(p_user_agent  VARCHAR2)
      RETURN categorizr_agent_details;

   FUNCTION get_category
      RETURN VARCHAR2;

   FUNCTION get_browser
      RETURN VARCHAR2;

   FUNCTION get_os
      RETURN VARCHAR2;

   FUNCTION isdesktop
      RETURN BOOLEAN;

   FUNCTION istablet
      RETURN BOOLEAN;

   FUNCTION istv
      RETURN BOOLEAN;

   FUNCTION ismobile
      RETURN BOOLEAN;

   /*
    The package is initialized automatically when called, trying to fetch the value of
    the HTTP_USER_AGENT, which naturally only succeeds when called through a web gateway.
    Additionally the package just offers a mean to test a user agent strings manually by
    passing the string with a procedure call
   */

   PROCEDURE set_user_agent (http_user_agent_string VARCHAR2 DEFAULT NULL);
END categorizr;
If you're interested in the package body, click as instructed
CREATE OR REPLACE PACKAGE BODY categorizr AS
   /******************************************************************************
      NAME:       categorizr
      PURPOSE:    detect web user agent device type

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        30-3-2012   crokitta         Created this package.
      0.2        24-10-2012  swesley          Included browser/os gets
   ******************************************************************************/


   FUNCTION preg_match (pattern    VARCHAR2,
                        subject    VARCHAR2,
                        switch     VARCHAR2 DEFAULT NULL)
      RETURN BOOLEAN
   IS
      l_pattern   VARCHAR2 (32767) := pattern;
      l_subject   VARCHAR2 (32767) := subject;
   BEGIN
      IF LOWER (switch) = 'i'
      THEN
         l_pattern := LOWER (l_pattern);
         l_subject := LOWER (l_subject);
      END IF;

      IF REGEXP_INSTR (l_subject, l_pattern) = 0
      THEN
         RETURN FALSE;
      ELSE
         RETURN TRUE;
      END IF;
   END;

   PROCEDURE set_category
   IS
   BEGIN
      CASE
         -- Check if user agent is a smart TV - http://goo.gl/FocDk
         WHEN preg_match ('GoogleTV|SmartTV|Internet.TV|NetCast|NETTV|AppleTV|boxee|Kylo|Roku|DLNADOC|CE\-HTML', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'tv';
         -- Check if user agent is a TV Based Gaming Console
         WHEN preg_match ('Xbox|PLAYSTATION.3|Wii', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'tv';
         -- Check if user agent is a Tablet
         WHEN (preg_match ('iP(a|ro)d', g_user_agent, 'i')
               OR preg_match ('tablet|tsb_cloud_companion', g_user_agent, 'i'))
              AND (NOT preg_match ('RX-34', g_user_agent, 'i')
                   OR preg_match ('FOLIO', g_user_agent, 'i'))
         THEN
            g_agent_details.device := 'tablet';
         -- Check if user agent is an Android Tablet
         WHEN preg_match ('Linux', g_user_agent, 'i')
              AND preg_match ('Android', g_user_agent, 'i')
              AND (NOT preg_match ('Fennec|mobi|HTC.Magic|HTCX06HT|Nexus.One|SC-02B|fone.945', g_user_agent, 'i')
               --or preg_match ('GT-P1000', g_user_agent, 'i')
               )
         THEN
            g_agent_details.device := 'tablet';
         -- Check if user agent is a Kindle or Kindle Fire
         WHEN preg_match ('Kindle', g_user_agent, 'i')
              OR preg_match ('Mac.OS', g_user_agent, 'i')
                AND preg_match ('Silk', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'tablet';
         -- Check if user agent is a pre Android 3.0 Tablet
         WHEN preg_match (
                 'GT-P10|SC-01C|SHW-M180S|SGH-T849|SCH-I800|SHW-M180L|SPH-P100|SGH-I987|zt180|HTC(.Flyer|\_Flyer)|Sprint.ATP51|ViewPad7|pandigital(sprnova|nova)|Ideos.S7|Dell.Streak.7|Advent.Vega|A101IT|A70BHT|MID7015|Next2|nook',
                 g_user_agent,'i')
              OR preg_match ('MB511', g_user_agent, 'i')
                AND preg_match ('RUTEM', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'tablet';
         -- Check if user agent is unique Mobile User Agent
         WHEN preg_match ('BOLT|Fennec|Iris|Maemo|Minimo|Mobi|mowser|NetFront|Novarra|Prism|RX-34|Skyfire|Tear|XV6875|XV6975|Google.Wireless.Transcoder', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'mobile';
         -- Check if user agent is an odd Opera User Agent - http:--goo.gl/nK90K
         WHEN preg_match ('Opera', g_user_agent, 'i')
              AND preg_match ('Windows.NT.5', g_user_agent, 'i')
              AND preg_match ('HTC|Xda|Mini|Vario|SAMSUNG\-GT\-i8000|SAMSUNG\-SGH\-i9', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'mobile';
         -- Check if user agent is Windows Desktop
         WHEN preg_match ('Windows.(NT|XP|ME|9)', g_user_agent, 'i')
              AND NOT preg_match ('Phone', g_user_agent, 'i')
              OR preg_match ('Win(9|.9|NT)', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Check if agent is Mac Desktop
         WHEN preg_match ('Macintosh|PowerPC', g_user_agent, 'i')
              AND NOT preg_match ('Silk', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Check if user agent is a Linux Desktop
         WHEN preg_match ('Linux', g_user_agent, 'i')
              AND preg_match ('X11', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Check if user agent is a Solaris, SunOS, BSD Desktop
         WHEN preg_match ('Solaris|SunOS|BSD', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Check if user agent is a Desktop BOT/Crawler/Spider
         WHEN preg_match ('Bot|Crawler|Spider|Yahoo|ia_archiver|Covario-IDS|findlinks|DataparkSearch|larbin|Mediapartners-Google|NG-Search|Snappy|Teoma|Jeeves|TinEye', g_user_agent, 'i')
              AND NOT preg_match ('Mobile', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Otherwise assume it is a Mobile Device
         ELSE
            g_agent_details.device := 'mobile';
      END CASE;

      -- Categorize Tablets as desktops
      IF g_tablets_as_desktops
      AND g_agent_details.device = 'tablet'
      THEN
         g_agent_details.device := 'desktop';
      END IF;

      -- Categorize TVs as desktops
      IF g_smarttv_as_desktops
      AND g_agent_details.device = 'tv'
      THEN
         g_agent_details.device := 'desktop';
      END IF;
   END;

   PROCEDURE set_browser
   IS
   BEGIN
      IF preg_match ('Opera', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Opera';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Opera/')+6);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('MSIE', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Internet Explorer';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'MSIE ')+5);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('Chrome', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Chrome';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Chrome/')+7);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('Firefox', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Firefox';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Firefox/')+8);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('Safari', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Safari';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Safari/')+7);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSE
        g_agent_details.browser := 'Other';
        g_agent_details.browser_version := NULL;
      END IF;
   END set_browser;

   PROCEDURE set_os
   IS
   BEGIN
      IF preg_match ('iPad|iPod|iPhone', g_user_agent, 'i') THEN
        g_agent_details.os := 'iOS';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, ' OS ')+4);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(g_agent_details.os_version, ' ')-1);

      ELSIF preg_match ('Windows', g_user_agent, 'i') THEN
        g_agent_details.os := 'Windows';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Windows ')+8);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('Mac OS', g_user_agent, 'i') THEN
        g_agent_details.os := 'Macintosh';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Mac OS X ')+9);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('RIM Tablet', g_user_agent, 'i') THEN
        g_agent_details.os := 'RIM';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'RIM Tablet OS ')+13);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('Android', g_user_agent, 'i') THEN
        g_agent_details.os := 'Android';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Android ')+8);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('Linux', g_user_agent, 'i') THEN
        g_agent_details.os := 'Linux';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Linux ')+6);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSE
        g_agent_details.os := 'Other';
        g_agent_details.os_version := NULL;
      END IF;
   END set_os;

   FUNCTION get_agent_details(p_user_agent  VARCHAR2)
      RETURN categorizr_agent_details IS
   BEGIN
     -- override package initialisation
     set_user_agent(p_user_agent);
     RETURN g_agent_details;
   END get_agent_details;

   PROCEDURE set_user_agent (http_user_agent_string VARCHAR2 DEFAULT NULL)
   IS
   BEGIN
     g_agent_details := NEW categorizr_agent_details(null,null,null,null,null,null);
      g_user_agent := http_user_agent_string;

      IF g_user_agent IS NULL
      THEN
         BEGIN
            g_user_agent := OWA_UTIL.get_cgi_env ('HTTP_USER_AGENT');
         EXCEPTION
            WHEN OTHERS
            THEN
               g_user_agent := NULL;
         END;
      END IF;

      set_category;
      set_os;
      set_browser;
      g_agent_details.agent := g_user_agent;
   /*EXCEPTION
      WHEN OTHERS
      THEN
         g_user_agent := null;*/
   END;

   FUNCTION get_category
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_agent_details.device;
   END;

   FUNCTION get_browser
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_agent_details.browser;
   END;

   FUNCTION get_os
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_agent_details.os;
   END;

   -- Returns true if desktop user agent is detected
   FUNCTION isdesktop
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'desktop'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;

   -- Returns true if tablet user agent is detected
   FUNCTION istablet
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'tablet'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;

   -- Returns true if SmartTV user agent is detected
   FUNCTION istv
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'tv'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;

   -- Returns true if mobile user agent is detected
   FUNCTION ismobile
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'mobile'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;
BEGIN
   set_user_agent;
   null;
END categorizr;
I've created an interactive report on visits data from that application: http://apex.oracle.com/pls/apex/f?p=SW2012:CATEGORIZR


This query used to generate that report
select 
   cnt
  ,categorizr.get_agent_details(v.agent).browser browser
  ,categorizr.get_agent_details(v.agent).browser_version b_version
  ,categorizr.get_agent_details(v.agent).os os
  ,categorizr.get_agent_details(v.agent).os_version os_version
  ,categorizr.get_agent_details(v.agent).device device
  ,categorizr.get_agent_details(v.agent).agent the_agent
from 
  (select agent, count(*) cnt from am_visits group by agent) v

If you were to utilise this package for production code, I would first check it for defects - then question your need to set the user agent in the package body.

I might also suggest that as this code matures, it could be added to the Alexandra PL/SQL Library, administered by Morten, expanded/updated by the masses, encouraging other utilities to come visiting.

Let me know if you find it handy or have any feedback. Thank you Christian & Brett for the boost.

Scott

5 comments:

Christian Rokitta said...

Hi Scott,

I like your extension of my package.

I actually was working on a similar functionality, as you can see on my Categorizer package demo page. It get's more detailed information from the user agent string.

The problem with this kind of "browser sniffing" is, one has to maintain the code with new browser versions or brands.

Cheers,
Christian

Scott Wesley said...

Thanks Christian - I think I saw the hint of what you were doing when I was writing up the post, but failed to mention it - sorry!

Yeah, I was wondering that when I was reading your post about Brett's work - wondering the mechanism - it would certainly need to grow over time.

But for what use? There are other methods to determine the size of the page and responsive design helps style page design.

I thought the it provided interesting statistics - I would hate to code for different browsers in this day & age. IE is behind, but consumers should force demand!

I wonder how Google Analytics provides their OS/browser statistics - do they just feed the same input into their own mincer?

Ooh an interesting world.

Damir Vadas said...

Scott,

This is just amazing ... exactly I was prepared to make from the scratch. But this is now not needed....thank you very much-respect!
Damir Vadas
http://damir-vadas.blogspot.com

Damir Vadas said...

JUst to make small hint for easy readiness:
FUNCTION isdesktop
RETURN BOOLEAN
IS
BEGIN
return nvl(g_agent_details.device,'?') = 'desktop'
END;

-- Returns true if tablet user agent is detected
FUNCTION istablet
RETURN BOOLEAN
IS
BEGIN
return nvl(g_agent_details.device,'?') = 'tablet'
END;

-- Returns true if SmartTV user agent is detected
FUNCTION istv
RETURN BOOLEAN
IS
BEGIN
return nvl(g_agent_details.device,'?') = 'tv'
END;

-- Returns true if mobile user agent is detected
FUNCTION ismobile
RETURN BOOLEAN
IS
BEGIN
return nvl(g_agent_details.device,'?') = 'mobile'
END;

Scott Wesley said...

I agree, I was just following the existing style - not trying to refactor too much. Thanks