/*=====================================================================

  File:      install.sql for AdventureWorks SQLCLR Example
  Summary:   Installs some basic utilities for the AdventureWorks 
             OLTP database which use server side .NET Framework functionality.
  Date:		 January 23, 2004

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

 This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE [name] = N'ContactDataSchemaCollection')
DROP XML SCHEMA COLLECTION Person.ContactDataSchemaCollection;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_CreateContact')
DROP PROCEDURE [dbo].[usp_CreateContact];
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'usp_LookupConversionRate')
DROP PROCEDURE [dbo].[usp_LookupConversionRate]
GO

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'Contact')
DROP ASSEMBLY Contact;
GO

IF EXISTS (SELECT * FROM sys.objects WHERE ([name] = N'ConvertCurrency') AND ([type] = 'FS'))
DROP FUNCTION ConvertCurrency;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = N'ConversionFunction') 
DROP ASSEMBLY ConversionFunction;
GO

IF EXISTS (SELECT * FROM sys.types WHERE [name] = N'Currency') 
DROP TYPE Currency;
GO




IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = N'CurrencyType') 
DROP ASSEMBLY CurrencyType;
GO
CREATE XML SCHEMA COLLECTION Person.ContactDataSchemaCollection
AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema targetNamespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactData" 
                xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactData" 
                elementFormDefault="qualified"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema" >

    <xsd:complexType name="Contact" abstract="true">
        <xsd:sequence>
            <xsd:element name="NameStyle" minOccurs="0" type="xsd:boolean" default="false" />
            <xsd:element name="Title" type="xsd:string" minOccurs="0"/>
            <xsd:element name="FirstName" type="xsd:string" />
            <xsd:element name="MiddleName" type="xsd:string" minOccurs="0" />
            <xsd:element name="LastName" type="xsd:string" />
            <xsd:element name="Suffix" type="xsd:string" minOccurs="0"/>
            <xsd:element name="EmailAddress" type="xsd:string"/>
            <xsd:element name="EmailPromotion" type="xsd:int" minOccurs="0"/>
            <xsd:element name="Phone" type="xsd:string" minOccurs="0"/>
            <xsd:element name="PasswordHash" type="xsd:string" />
            <xsd:element name="PasswordSalt" type="xsd:string" />
            <xsd:element name="AdditionalContactInfo" minOccurs="0">
                <xsd:complexType>
                    <xsd:sequence>
                        <xsd:any namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo" processContents="strict" />
                    </xsd:sequence>
                </xsd:complexType>
            </xsd:element>
        </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="Customer" abstract="true">
        <xsd:complexContent>
            <xsd:extension base="Contact">
                <xsd:sequence>
                    <xsd:element name="SalesPersonID" type="xsd:int" minOccurs="0" />
                    <xsd:element name="TerritoryID" type="xsd:int" minOccurs="0" />                   
                    <!-- Logically CustomerType goes here, but the type is implicit in the derived types, so we will
                         leave it for the derived types to define CustomerType -->
                </xsd:sequence>
            </xsd:extension>
        </xsd:complexContent>
    </xsd:complexType>
    
    
    <xsd:complexType name="IndividualContact">
        <xsd:complexContent>
            <xsd:extension base="Customer">
                <xsd:sequence>
                    <xsd:element name="CustomerType" minOccurs="0" default="I">
                        <xsd:simpleType>
                            <xsd:restriction base="xsd:string">
                                <xsd:enumeration value="I" />
                                <xsd:enumeration value="i" />
                            </xsd:restriction>
                        </xsd:simpleType>
                    </xsd:element>

                    <xsd:element name="Demographics" minOccurs="0">
                        <xsd:complexType>
                            <xsd:sequence>
                                <xsd:any namespace="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" 
                                        processContents="strict" />
                            </xsd:sequence>
                        </xsd:complexType>
                    </xsd:element>
                </xsd:sequence>
            </xsd:extension>
        </xsd:complexContent>
    </xsd:complexType>
    
    <xsd:complexType name="StoreContact">
        <xsd:complexContent>
            <xsd:extension base="Contact">
                <xsd:sequence>
                    <xsd:element name="CustomerID" type="xsd:int" />
                    <xsd:element name="ContactTypeID" type="xsd:byte" />
                </xsd:sequence>
            </xsd:extension>
        </xsd:complexContent>
    </xsd:complexType>
    
    <xsd:complexType name="VendorContact">
        <xsd:complexContent>
            <xsd:extension base="Contact">
                <xsd:sequence>
                    <xsd:element name="VendorID" type="xsd:int" />
                    <xsd:element name="ContactTypeID" type="xsd:byte" />
                </xsd:sequence>
            </xsd:extension>
        </xsd:complexContent>
    </xsd:complexType>
    
    <xsd:complexType name="EmployeeContact">
        <xsd:complexContent>
            <xsd:extension base="Contact">
                <xsd:sequence>
                    <xsd:element name="NationalIDNumber" type="xsd:string" />
                    <xsd:element name="LoginID" type="xsd:string" />
                    <xsd:element name="DepartmentID" type="xsd:short" />
                    <xsd:element name="ManagerID" type="xsd:int" minOccurs="0" />
                    <xsd:element name="ShiftID" type="xsd:byte" />
                    <xsd:element name="JobTitle" type="xsd:string" />
                    <xsd:element name="Address">
                        <xsd:complexType>
                            <xsd:sequence>
                                <xsd:element name="AddressLine1" type="xsd:string" />
                                <xsd:element name="AddressLine2" type="xsd:string" minOccurs="0"/>
                                <xsd:element name="City" type="xsd:string" />
                                <xsd:element name="StateProvinceID" type="xsd:int" />
                                <xsd:element name="PostalCode" type="xsd:string" />
                            </xsd:sequence>
                        </xsd:complexType>
                    </xsd:element>
                    <xsd:element name="BirthDate" type="xsd:dateTime" />
                    <xsd:element name="MaritalStatus">
                        <xsd:simpleType>
                            <xsd:restriction base="xsd:string">
                                <xsd:enumeration value="M" />
                                <xsd:enumeration value="m" />
                                <xsd:enumeration value="S" />
                                <xsd:enumeration value="s" />
                            </xsd:restriction>
                        </xsd:simpleType>
                    </xsd:element>
                    <xsd:element name="Gender">
                        <xsd:simpleType>
                            <xsd:restriction base="xsd:string">
                                <xsd:enumeration value="M" />
                                <xsd:enumeration value="m" />
                                <xsd:enumeration value="F" />
                                <xsd:enumeration value="f" />
                            </xsd:restriction>
                        </xsd:simpleType>
                    </xsd:element>                    
                    <xsd:element name="HireDate" type="xsd:dateTime" />
                    <xsd:element name="SalariedFlag" type="xsd:boolean" minOccurs="0" default="true" />
                    <xsd:element name="VacationHours" type="xsd:short" />
                    <xsd:element name="SickLeaveHours" type="xsd:short" />
                </xsd:sequence>
            </xsd:extension>
        </xsd:complexContent>
    </xsd:complexType>
    
    <xsd:element name="Contact">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:choice>
                    <xsd:element name="Individual" type="IndividualContact" />
                    <xsd:element name="Store" type="StoreContact" />
                    <xsd:element name="Vendor" type="VendorContact" />
                    <xsd:element name="Employee" type="EmployeeContact" />
                </xsd:choice>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>' ;
GO

-- Add the assembly which contains the CLR methods we want to invoke on the server.
DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
SELECT @SamplesPath = replace(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY Contact
FROM @SamplesPath + 'AdventureWorks\VB\Contact\bin\Contact.dll'
WITH permission_set = Safe;
GO

CREATE PROCEDURE [dbo].[usp_CreateContact]
(
    @ContactData NVARCHAR(max)
    ,@ContactID int OUTPUT
    ,@CustomerID int OUTPUT	
)
AS EXTERNAL NAME Contact.[Microsoft.Samples.SqlServer.ContactUtils].CreateContact;
GO

-- =======================================================
-- Register assemblies for currency data type and conversion
-- =======================================================

DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
SELECT @SamplesPath = replace(physical_name, 'Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf', 'Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [CurrencyType] FROM @SamplesPath + 'AdventureWorks\VB\CurrencyType\bin\CurrencyType.dll'
with permission_set = safe;


-- These aid in debugging if you detach the database and move it elsewhere
ALTER ASSEMBLY [CurrencyType] ADD FILE FROM @SamplesPath + 'AdventureWorks\VB\CurrencyType\bin\CurrencyType.pdb';
ALTER ASSEMBLY [CurrencyType] ADD FILE FROM @SamplesPath + 'AdventureWorks\VB\CurrencyType\Currency.vb';

CREATE ASSEMBLY [ConversionFunction] FROM @SamplesPath + 'AdventureWorks\VB\ConversionFunction\bin\ConversionFunction.dll'
with permission_set = unsafe;


-- These aid in debugging if you detach the database and move it elsewhere
ALTER ASSEMBLY [ConversionFunction] ADD FILE FROM @SamplesPath + 'AdventureWorks\VB\ConversionFunction\bin\ConversionFunction.pdb';
ALTER ASSEMBLY [ConversionFunction] ADD FILE FROM @SamplesPath + 'AdventureWorks\VB\ConversionFunction\CurrencyConverter.vb';
GO

CREATE TYPE Currency EXTERNAL NAME [CurrencyType].[Microsoft.Samples.SqlServer.Currency];
GO

CREATE FUNCTION ConvertCurrency
(
    @fromAmount AS money,
    @toCultureName AS nvarchar(10),
    @onDate as DateTime
)
RETURNS Currency
AS EXTERNAL NAME [ConversionFunction].[Microsoft.Samples.SqlServer.CurrencyConverter].ConvertCurrency;
GO

CREATE PROCEDURE usp_LookupConversionRate
(
    @OnDate datetime,
    @ToCurrencyCode nchar(3),
    @Result decimal(10,4) OUTPUT
)
AS
BEGIN
    --It is not permitted to perform certain side-effects in functions, and
    --SET NOCOUNT is one of them.  Since this sproc is called from 
    --the ConvertCurrency CLR UDF, we must not do that side-effect or
    --there will be an error at runtime.
    --SET NOCOUNT ON;

    SELECT @Result = (SELECT TOP 1 AverageRate FROM Sales.CurrencyRate 
    WHERE CurrencyRateDate <= @OnDate AND FromCurrencyCode = N'USD' 
    AND ToCurrencyCode = @ToCurrencyCode 
    ORDER BY CurrencyRateDate DESC);

    IF (@Result IS NULL)
        SELECT @Result = (SELECT TOP 1 AverageRate FROM Sales.CurrencyRate 
        WHERE CurrencyRateDate > @OnDate AND FromCurrencyCode = N'USD' 
        AND ToCurrencyCode = @ToCurrencyCode
        ORDER BY CurrencyRateDate ASC);
END;
GO



--
-- end register assemblies
-- 

--
-- Start security grants
--

-- Capture the OS version in a temp table so that we can pick 
-- the correct windows account to grant access to for the objects 
-- defined in this file.  This enables ASP.NET programs to access 
-- these objects.
CREATE TABLE #GetVersionValues
(
	[Index]	int,
	[Name]	sysname,
	Internal_value	int,
	Character_Value	sysname
);
GO

INSERT #GetVersionValues
EXEC master.dbo.xp_msver 'WindowsVersion';
GO

DECLARE @OSVersion decimal(9, 2);
DECLARE @ASPUserName nvarchar(100);
DECLARE @NetworkService nvarchar(100);

-- For globalization purposes, the sample install script uses a SID to set NT AUTHORITY\NETWORK SERVICE
-- SELECT SUSER_SID('NT AUTHORITY\NETWORK SERVICE') returns the English SID. 
SET @NetworkService = SUSER_SNAME(0x010100000000000514000000);

SELECT @OSVersion = CONVERT(decimal(9, 2), SUBSTRING(Character_Value, 1, CHARINDEX(' ', Character_Value) - 1)) 
FROM #GetVersionValues;

-- IIS6 uses a different account to run web apps under.  II6 first shipped with Windows 2003 (v5.2).
IF (@OSVersion < 5.20) 
	SET @ASPUserName = @@ServerName + N'\ASPNET';
ELSE
	SET @ASPUserName = @NetworkService;

IF NOT EXISTS(SELECT principal_id FROM sys.server_principals where name = @ASPUserName)
BEGIN
    EXEC sp_grantlogin @ASPUserName;
END

IF NOT EXISTS(SELECT principal_id from sys.database_principals where name = @ASPUserName)
BEGIN
    EXEC sp_grantdbaccess @ASPUserName;
END


EXEC('GRANT EXECUTE ON assembly :: [CurrencyType] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON assembly :: [ConversionFunction] TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON type :: Currency TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON ConvertCurrency TO [' + @ASPUserName + ']');

EXEC('GRANT EXECUTE ON usp_LookupConversionRate TO [' + @ASPUserName + ']');

EXEC('GRANT SELECT ON Sales.CurrencyRate TO [' + @ASPUserName + ']');

GO

DROP TABLE #GetVersionValues;
GO
--
-- End security grants
--