51Degrees device detection functionality can be used within SQL Server to add extra properties to user agent data. Deployed as an assembly functions can be called to returned individual properties, or tables of properties.
Installing to SQL Server
Using Visual Studio 2012 or 2013 configured with SQL Server Data Tools start by opening the FiftyOne.Foundation SQL.sqlproj project from the FoundationV3 folder of the source code obtained via Codeplex.
Publish the project to an SQL Server 2008 or greater using the Publish option from the Project menu. A dialogue similar to the following enables the server and database to be selected.
The assembly requires the UNSAFE permission to avoid initialising it's resources for every request improving performance. Additional a simple cache is used to speed up requests for duplicate user agents. By default SQL Server will prevent unsafe assemblies being installed and the following error will be generated during publishing.
SQL72014: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly 'FiftyOne.Foundation.SQL' failed because assembly 'FiftyOne.Foundation.SQL' is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
Use the sn.exe tool to create a .NET strong name key which can be used to sign the assembly.
sn -k yourkey.snk
If one does not exist already create a master key in the master database.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'your_password';
Create an asymmetric key in the master database. You may need to copy the snk file created in the first step to the database server as the path will be local to the sql server.
CREATE ASYMMETRIC KEY sqlkey FROM FILE = 'path_to_the_strong_name_key';
Create a login from the asymmetric key in the master database.
CREATE LOGIN yourlogin FROM ASYMMETRIC KEY yourkey;
Give the login you've just created the UNSAFE ASSEMBLY permission
GRANT UNSAFE ASSEMBLY TO yourlogin;
Build your assembly and sign it with your stong name key. In Visual Studio 2013 the SQLCLR tab of the project properties contains a button at the bottom of the page titled "Signing". The following dialogue should be used to provide the key file generated in the first step.
Repeat the steps to publish the assembly to the database
Using in SQL
Before any assembly can be loaded your server needs to have CLR enabled. The following SQL will enable it:
sp_configure 'clr enabled', 1
The assembly needs to be initialised with a device data file. Therefore before using any of the methods the following initialisation statement should be run once with the local server path to the 51Degrees device data file.
SELECT dbo.InitialiseDeviceDetection('[YOUR V3 DEVICE DATA FILE]', NULL, 10, 1);
The 2nd parameter provides a list of the properties that should be returned for a detection. The 3rd parameter is the number of seconds a result should be held in the cache before it is discarded. The final parameter is a Boolean to indicate if the data file should be loaded into main memory for faster performance (true), or retained on disk to reduce main memory consumption (false). See the reference section at the bottom of this page for more information on available methods and parameters.
The following line will initialise the detection service using main memory to return the DeviceType, IsMobile and ScreenInchesDiagonal with a cache life of 60 seconds.
SELECT dbo.InitialiseDeviceDetection('D:\51Degrees.dat', 'DeviceType, ScreenInchesDiagonal, IsMobile', 60, 1);
Following initialisation properties can be queried directly using the DevicePropertyByUserAgent method. The first parameter is the target user agent and the 2nd the property required.
The following example returns the IsMobile property for a user agent which represents the Google Nexus 4.
SELECT dbo.DevicePropertyByUserAgent('Mozilla/5.0 (Linux; Android 4.2.1; en-us; Nexus 4 Build/JOP40D) AppleWebKit/535.19 (KHTML, like Gecko) Chrome/18.0.1025.166 Mobile Safari/535.19', 'IsMobile');
Retrieving each value using the DevicePropertyByUserAgent method is complex when more than one property is needed for the same useragent. The DevicePropertiesByUserAgent method returns a table of all properties and values specified at initialisation and can be easily joined to existing tables of user agents with a CROSS JOIN. The following SQL uses the Value field of the UserAgentStrings table to cross join the results of DeviceProperties.
SELECT TOP 100 U.Value AS UserAgent, P.Property, P.Value FROM UserAgentStrings AS U CROSS APPLY dbo.DevicePropertiesByUserAgent(U.Value) AS P;
The resulting table would look something like this.
|Mozilla/5.0 (Linux; U; Android 4.3; en-us; HTCONE Build/JSS15J) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile Safari/534.30||IsMobile||True|
|Mozilla/5.0 (Linux; U; Android 4.3; en-us; HTCONE Build/JSS15J) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile Safari/534.30||ScreenInchesDiagonal||4.7|
|Mozilla/5.0 (Linux; U; Android 2.3.4; en-us; NOOK BNTV250 Build/GINGERBREAD 1.4.3) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Safari/533.1||IsMobile||True|
|Mozilla/5.0 (Linux; U; Android 2.3.4; en-us; NOOK BNTV250 Build/GINGERBREAD 1.4.3) AppleWebKit/533.1 (KHTML, like Gecko) Version/4.0 Safari/533.1||ScreenInchesDiagonal||7.0|
|Mozilla/5.0 (iPad; CPU OS 7_0_6 like Mac OS X) AppleWebKit/537.51.1 (KHTML, like Gecko) CriOS/33.0.1750.14 Mobile/11B651 Safari/9537.53||IsMobile||True|
|Mozilla/5.0 (iPad; CPU OS 7_0_6 like Mac OS X) AppleWebKit/537.51.1 (KHTML, like Gecko) CriOS/33.0.1750.14 Mobile/11B651 Safari/9537.53||ScreenInchesDiagonal||9.7|
The following SQL shows how a a PIVOT operation could be applied to return the property names as columns.
SELECT * FROM (
SELECT TOP 100 U.Value AS UserAgent, P.Property, P.Value
FROM UserAgentStrings AS U
CROSS APPLY dbo.DevicePropertiesByUserAgent(U.Value) AS P) AS S
(MIN(S.Value) FOR Property IN ([IsMobile], [ScreenInchesDiagonal])) AS P
The following shows an example of the results.
|Mozilla/5.0 (Linux; U; Android 4.3; en-us; HTCONE Build/JSS15J) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile Safari/534.30||True||4.7|
|Mozilla/5.0 (Linux; U; Android 4.3; en-us; SCH-R530U Build/JSS15J) AppleWebKit/534.30 (KHTML, like Gecko) Version/4.0 Mobile Safari/534.30 USCC-R530U||True||4.8|
|Mozilla/5.0 (iPhone; CPU iPhone OS 7_0_4 like Mac OS X) AppleWebKit/537.51.1 (KHTML, like Gecko) Mobile/11B554a [FBAN/FBIOS;FBAV/6.8;FBBV/745892;FBDV/iPhone5,1;FBMD/iPhone;FBSN/iPhone OS;FBSV/7.0.4;FBSS/2; FBCR/AT&T;FBID/phone;FBLC/en_US;FBOP/5]||True||3.5|
Adding device detection to an SQL database that already contains user agent data adds a significant additional dimension to analysis queries improving understanding of how device characteristics such as physical screen size impact successful outcomes. With 51Degrees it's easy to setup and implement.
51Degrees device detection could also be applied prior to loading the database in a transform layer and the results stored in the database for future analysis.