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
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 instructedCREATE 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
Hi Scott,
ReplyDeleteI 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
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!
ReplyDeleteYeah, 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.
Scott,
ReplyDeleteThis 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
JUst to make small hint for easy readiness:
ReplyDeleteFUNCTION 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;
I agree, I was just following the existing style - not trying to refactor too much. Thanks
ReplyDelete