51Degrees device detection functionality can be used within SQL Server to add extra properties to User-Agent data. When deployed as an assembly, functions can be called to return individual properties, or tables of properties.

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 a 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 in order to avoid initialising it's resources for every request, thereby improving performance. Additionally, 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.

								

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;
   
   										

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

									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.

									

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 second parameter provides a list of the properties that should be returned for a detection. The third 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).

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, the second 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 User- Agent. 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 similar to this.

UserAgent	Property	Value
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