"Who are you?" – client device Categorizr for APEX

This article describes a solution to determine the client device category (i.e. desktop, browser or tablet) your APEX application is accessed from.

categorizr

Until recently, building a web application with Oracle Application Express usually meant to build a desktop browser application. But if you look at the growing number of mobile devices, this will change very soon, if not already has for some of you. Being able to develop applications, that can be accessed by tablets or smart phones offers lots of new possibilities. But it also comes with some challenges. Mobile devices are different. Interaction by touch screen, screen size and (non-) support of certain common web functionality (flash, for example), just to mention a few.

Oracles announcement to integrate the jQuery mobile framework into APEX 4.2 will help us to deal with many of the challenges, developing for multiple devices will bring. Until now, one of the biggest challenges is, to determine the actual client your application is accessed on. Is it a desktop browser, a tablet or a smart phone? In this post I want to offer a solution that will provide an answer to this question, and can be used in APEX.

When building a web application, as a developer, you want to be able anticipate on how your application is being accessed. You might want to redirect the user to a different page or entirely different application. Or you might want to hide (i.e. not render) or show certain portions of a page. To be able to do so, you need to know the type of client used, and, ideally, be able to use this information in (PL/SQL) conditions.

Getting the info you need to decide

Every page request comes with a HTTP header, containing some generic information about the browser client it has been issued from: The HTTP_USER_AGENT string. This string holds information about browser, platform and version. The string itself can easily be accessed in PL/SQL by using the OWA_UTIL package function call:

l_user_agent := owa_util.get_cgi_env ('HTTP_USER_AGENT');

Once you've got the user agent string, the hard work starts. There are all kind of values you can get returned to you in the user agent string. It can be messy - VERY messy. Sometimes browser even lie about who they are, and the format is not standardized.

There are many approaches trying to find a way through this mess, and you can find a few articles on this at the bottom of this post. Some use databases with almost all possible user agent string stored, others try to decipher the string programmatically. Most of the code is available in Java, PHP or .net. I didn't find any appropriate code for PL/SQL. So I had to write my own or port some code I can understand.

Categorizr

What I wanted to achieve, was to be able to categorize the client device rendering the page in my application. I found some PHP code doing just that: Categorizr. The solution has some advantages:

  • It is lightweight and has not too many lines of code, which makes it reasonably easy to maintain it. With the growing number of mobile devices, I will have to maintain the algorithm. The approach chosen by the developer assumes the device to be a mobile device and then checks for other categories. By assuming devices are mobile from the beginning, Categorizr aims to be more future friendly. When new phones come out, you don’t need to worry if their new user agent is in your device detection script since devices are assumed mobile from the start.
  • The algorithm uses Regular Expressions, which are supported by PL/SQL. This minimizes the changes I have to apply to the code (Year, I know: I’m lazy).
  • Due to the minimal codebase and not querying large databases or accessing a web service, it’s fast.

The PLSQL package (specification) I have written offers these functions that you can use in your (APEX) PL/SQL code:

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 (c) 2011

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      0.1        30-3-2012   crokitta         Created this package.
   ******************************************************************************/
   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_device                VARCHAR2 (100);

   FUNCTION get_category
      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 are interested the package body, click the link below and it will show up:

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

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      0.1        30-3-2012   crokitta         Created this package.
   ******************************************************************************/


   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_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_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_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_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_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_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_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_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_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_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_device := 'desktop';
         -- Check if user agent is a Solaris, SunOS, BSD Desktop
         WHEN preg_match ('Solaris|SunOS|BSD', g_user_agent, 'i')
         THEN
            g_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_device := 'desktop';
         -- Otherwise assume it is a Mobile Device
         ELSE
            g_device := 'mobile';
      END CASE;

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

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

   PROCEDURE set_user_agent (http_user_agent_string VARCHAR2 DEFAULT NULL)
   IS
   BEGIN
      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;
   EXCEPTION
      WHEN OTHERS
      THEN
         g_user_agent := null;
   END;

   FUNCTION get_category
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_device;
   END;

   -- Returns true if desktop user agent is detected
   FUNCTION isdesktop
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_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_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_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_device = 'mobile'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;
BEGIN
   set_user_agent;
END categorizr;
/

The functions can easily be used in APEX region conditions or any other PL/SQL code. Install the package in your workspace’s schema or in a separate schema with execute granted to public and a public synonym on it.

You can download the whole package HERE.

I tested the package against a list of. 11,000 common user agent strings. The algorithm is not failsafe, but reasonably accurate. Of course I cannot test every client possible. Try the demo page I prepared and drop me an email if you believe the outcome of the package is not correct.

 

References:

Categorizr – A modern device detection script
User Agent String.Com
WURFL, the Wireless Universal Resource FiLe, is a Device Description Repository (DDR)

Comments

  1. Hi Christian,

    great stuff!

    What about a simple function which also can be called in a SELECT which gets the User-Agent-String as input and returns the category?

    ReplyDelete
    Replies
    1. Hi Peter,

      Good Idea. I'll have to rewrite the package a little bit. Coming soon, OK?!

      Delete
  2. Thanks Christian,

    that comes in very handy for visitor analysis.

    ReplyDelete
    Replies
    1. Peter,

      Than I'd rather finish the other package, Browser, (or better: integrate both solutions) that you can find on the demo page. It offers a much more fine-grained information of the client, like browser brand, version and OS.

      Delete
  3. Hi Christian

    As mentioned, here is my blog post regarding my extension ideas
    http://www.grassroots-oracle.com/2012/11/categorizr-for-apex-extended.html

    ReplyDelete
  4. Hi Scott,

    Have a look at the demo page: I was working on a similar package. The problem is: you have to maintain the algorithm to keep up with browser brands and versions. The usage of the HTTP_USER_AGENT is a mess.

    Cheers,
    Christian

    ReplyDelete
  5. very informative post when i read i found the new things. thanks for sharing.PCEF on the Client Device

    ReplyDelete
  6. very informative post when i read i found the new things. thanks for sharing.PCEF on the Client Device

    ReplyDelete
  7. hello all,

    where in the apex application is the best place (or way) to insert a command that determines the device? ideally i would like to use the login page for that purpose. would anywhere in front of the

    :P101_USERNAME := apex_authentication.get_login_username_cookie;

    be all right, in page 101? i wouldn't want to mess up any other functionality... any common practice or experience to be shared?

    thanks and best regards,


    -v-

    ReplyDelete
  8. Hi Tokke,

    I would suggest to use page 0 for this.
    When using page 101 AND public pages, you cannot be sure your command/process is executed.

    regards,
    Christian

    ReplyDelete

Post a Comment

Popular posts from this blog

Remember Me - APEX Autologin

Tabular Forms on Complex Views - using INSTEAD OF Triggers

Book Review: Oracle APEX 4.0 Cookbook