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
