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.

Signing Assembly

1. Use the sn.exe tool to create a .NET strong name key which can be used to sign the assembly.

   sn -k yourkey.snk

2. If one does not exist already create a master key in the master database.

   											USE MASTER;
   
   												
   												GO
   												
   												
   												CREATE
   												
   											 MASTER 
   												
   												KEY
   												
   											 ENCRYPTION 
   												
   												BY
   												
   											 PASSWORD 
   												
   												=
   												
   												
   												'your_password'
   												
   											;
   
   											

3. 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'
   												
   											;
   
   											

4. Create a login from the asymmetric key in the master database.

   												
   												CREATE
   												
   											 LOGIN yourlogin 
   												
   												FROM
   												
   												
   												ASYMMETRIC
   												
   												
   												KEY
   												
   											 yourkey;
   
   											

5. Give the login you've just created the UNSAFE ASSEMBLY permission

   												
   												GRANT
   												
   											 UNSAFE ASSEMBLY 
   												
   												TO
   												
   											 yourlogin;
   
   											

6. 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.

   

7. Repeat the steps to publish the assembly to the database

   Creating [FiftyOne.Foundation.SQL]...
   
   Creating [dbo].[DeviceIdAsByteArray]...
   
   Creating [dbo].[DeviceIdAsString]...
   
   Creating [dbo].[DevicePropertyByUserAgent]...
   
   Creating [dbo].[InitialiseDeviceDetection]...
   
   Creating [dbo].[DevicePropertiesById]...
   
   Creating [dbo].[DevicePropertiesByStringId]...
   
   Creating [dbo].[DevicePropertiesByUserAgent]...
   
   Update complete.

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
										
										
										go
										
									
reconfigure

										
										go
										
									

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'
										
									);

									

Table Joins

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.

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
	PIVOT
	(
										
										MIN
										
									(S.Value) 
										
										FOR
										
									 Property 
										
										IN
										
									 ([IsMobile], [ScreenInchesDiagonal])) 
										
										AS
										
									 P

									

The following shows an example of the results.

Summary

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.