USE GateStore
GO
if @@ERROR=0 SET NOEXEC OFF else SET NOEXEC ON
GO

exec sp_configure 'clr enabled', 1
reconfigure
GO
ALTER DATABASE CURRENT SET TRUSTWORTHY ON
GO
if @@ERROR=0 SET NOEXEC OFF else SET NOEXEC ON
GO

set nocount on
declare @AssemblyName   varchar(100) = 'SQLfuncs'
declare @objId          int
declare @objName        varchar(100)
declare @objType        varchar(2)
declare @script         varchar(2014)
declare @restable table(OperationResult varchar(4096))
insert @restable values('delete of objects on DataBase "'+DB_NAME()+'" from assembly "'+@AssemblyName+'"')
declare CLR_Deleter cursor local forward_only
for select
         SO.id      as [InnerID]
        ,SO.name    as [InnerName]
        ,SO.[type]  as [InnerType]
from dbo.sysobjects SO
join sys.assembly_modules AM on AM.object_id = SO.id
where AM.assembly_id in (select assembly_id from sys.assemblies where name=@AssemblyName)
open CLR_Deleter
fetch next from CLR_Deleter into @objId, @objName, @objType
while @@FETCH_STATUS = 0
begin
-----------------------------------------------------------------------------------------------
set @script=''
if @objType in ('FS', 'FT')  set @script = 'IF OBJECT_ID ('''+@objName+''', '''+@objType+''') IS NOT NULL DROP FUNCTION [dbo].['+@objName+']' else
if @objType in ('PC')  set @script = 'IF OBJECT_ID ('''+@objName+''', '''+@objType+''') IS NOT NULL DROP PROCEDURE [dbo].['+@objName+']'
if @script<>''
 begin
 begin try
 EXECUTE (@script)
 insert @restable values('successful deleteing of the object: '+@objName+' ('+@objType+', '+CAST(@objId as varchar)+')')
 end try
 begin catch
 insert @restable values('Error deleteing of the object: '+@objName+' ('+@objType+', '+CAST(@objId as varchar)+')')
 end catch
 end
 else insert @restable values('Unknown object: '+@objName+' ('+@objType+', '+CAST(@objId as varchar)+')')
-----------------------------------------------------------------------------------------------
fetch next from CLR_Deleter into @objId, @objName, @objType
end
close CLR_Deleter;
deallocate CLR_Deleter;
----select OperationResult from @restable
print 'Удаление объектов для сборки "'+@AssemblyName+'" завершено'
GO

if Exists(SELECT * FROM  sys.assemblies where Name='SQLfuncs.XmlSerializers')
   DROP ASSEMBLY [SQLfuncs.XmlSerializers]
GO
if Exists(SELECT * FROM  sys.assemblies where Name='SQLfuncs')
   DROP ASSEMBLY [SQLfuncs]
GO
if Exists(SELECT * FROM  sys.assemblies where Name='newtonsoft.json')
   DROP ASSEMBLY [newtonsoft.json]
GO
if Exists(SELECT * FROM  sys.assemblies where Name='System.Runtime.Serialization')
   DROP ASSEMBLY [System.Runtime.Serialization]
GO
if Exists(SELECT * FROM  sys.assemblies where Name='System.Net.Http')
   DROP ASSEMBLY [System.Net.Http]
GO

 print '---- закончено удаление функций и сборок -----'
 GO


if not Exists(SELECT * FROM  sys.assemblies where Name='System.Net.Http')
   begin
   CREATE ASSEMBLY [System.Net.Http] AUTHORIZATION [dbo] FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Net.Http.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка System.Net.Http успешно создана'
   end
   else
   BEGIN TRY
   ALTER ASSEMBLY [System.Net.Http] FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Net.Http.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка System.Net.Http успешно обновлена'
   END TRY
   BEGIN CATCH
   print '--> сборка System.Net.Http идентична предыдущей и не требует обновления'
   END CATCH
GO

if not Exists(SELECT * FROM  sys.assemblies where Name='System.Runtime.Serialization')
   begin
   CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION [dbo] FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка System.Runtime.Serialization успешно создана'
   end
   else
   BEGIN TRY
   ALTER ASSEMBLY [System.Runtime.Serialization] FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка System.Runtime.Serialization успешно обновлена'
   END TRY
   BEGIN CATCH
   print '--> сборка System.Runtime.Serialization идентична предыдущей и не требует обновления'
   END CATCH
GO

if not Exists(SELECT * FROM  sys.assemblies where Name='newtonsoft.json')
   begin
   CREATE ASSEMBLY [newtonsoft.json] FROM 'D:\CLR\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка Newtonsoft.Json успешно создана'
   end
   else
   BEGIN TRY
   ALTER ASSEMBLY [newtonsoft.json] FROM 'D:\CLR\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка Newtonsoft.Json успешно обновлена'
   END TRY
   BEGIN CATCH
   print '--> сборка Newtonsoft.Json идентична предыдущей и не требует обновления'
   END CATCH
GO


if not Exists(SELECT * FROM  sys.assemblies where Name='SQLfuncs')
   begin
   CREATE ASSEMBLY [SQLfuncs] FROM 'D:\CLR\SQLfuncs.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка SQLfuncs успешно создана'
   end
   else
   BEGIN TRY
   ALTER ASSEMBLY [SQLfuncs] FROM 'D:\CLR\SQLfuncs.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка SQLfuncs успешно обновлена'
   END TRY
   BEGIN CATCH
   print '--> сборка SQLfuncs идентична предыдущей и не требует обновления'
   END CATCH
GO


if not Exists(SELECT * FROM  sys.assemblies where Name='SQLfuncs.XmlSerializers')
   begin
   CREATE ASSEMBLY [SQLfuncs.XmlSerializers] FROM 'D:\CLR\SQLfuncs.XmlSerializers.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка SQLfuncs.XmlSerializers успешно создана'
   end
   else
   BEGIN TRY
   ALTER ASSEMBLY [SQLfuncs.XmlSerializers] FROM 'D:\CLR\SQLfuncs.XmlSerializers.dll' WITH PERMISSION_SET = UNSAFE
   print '--> сборка SQLfuncs.XmlSerializers успешно обновлена'
   END TRY
   BEGIN CATCH
   print '--> сборка SQLfuncs.XmlSerializers идентична предыдущей и не требует обновления'
   END CATCH
GO


CREATE FUNCTION [dbo].[CLR_HttpGetBlobNew](@url [nvarchar](max))
RETURNS  varbinary(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[ReadBlob]
GO
IF OBJECT_ID ('CLR_HttpGetBlobNew', 'FS') IS NOT NULL
 print 'CLR_HttpGetBlobNew ready'
GO


CREATE FUNCTION [dbo].[CLR_HttpGet](@url [nvarchar](max), @headers [nvarchar](max))
RETURNS  varbinary(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[HttpGet]
GO
IF OBJECT_ID ('CLR_HttpGet', 'FS') IS NOT NULL
 print 'CLR_HttpGet'
GO


CREATE FUNCTION [dbo].[CLR_HttpGetBlobMethod](@url [nvarchar](max),@method [nvarchar](max),@headers [nvarchar](max),@parameters [nvarchar](max),@contenttype [nvarchar](max))
RETURNS varbinary(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[ReadBlobMethod]
GO
IF OBJECT_ID ('CLR_HttpGetBlobMethod', 'FS') IS NOT NULL
 print 'CLR_HttpGetBlobMethod ready'
GO


CREATE FUNCTION [dbo].[CLR_HttpGetBlobMethodEx](@url [nvarchar](max),@method [nvarchar](max),@headers [nvarchar](max),@parameters [nvarchar](max),@contenttype [nvarchar](max))
RETURNS TABLE (result varbinary(max), debug nvarchar(max), header nvarchar(max))
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[ReadBlobMethodEx]
GO
IF OBJECT_ID ('CLR_HttpGetBlobMethodEx', 'FT') IS NOT NULL
 print 'CLR_HttpGetBlobMethodEx ready'
GO

-- Для совместимости. Новая AsWin1251 ------------
CREATE FUNCTION [dbo].[CLR_UnicodeToUTF8](@source [nvarchar](max))
RETURNS nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[AsWin1251]--[UnicodeToUTF8]
GO
IF OBJECT_ID ('CLR_UnicodeToUTF8', 'FS') IS NOT NULL
 print 'CLR_UnicodeToUTF8 ready'
GO

-- Для совместимости. Новая AsUTF8 ------------
CREATE FUNCTION [dbo].[CLR_UTF8ToUnicode](@source [nvarchar](max))
RETURNS nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[AsUTF8]--[UTF8ToUnicode]
GO
IF OBJECT_ID ('CLR_UTF8ToUnicode', 'FS') IS NOT NULL
 print 'CLR_UTF8ToUnicode ready'
GO


CREATE FUNCTION [dbo].[CLR_AsWin1251](@source [nvarchar](max))
RETURNS nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[AsWin1251]
GO
IF OBJECT_ID ('CLR_AsWin1251', 'FS') IS NOT NULL
 print 'CLR_AsWin1251 ready'
GO

CREATE FUNCTION [dbo].[CLR_AsUTF8](@source [nvarchar](max))
RETURNS nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[AsUTF8]
GO
IF OBJECT_ID ('CLR_AsUTF8', 'FS') IS NOT NULL
 print 'CLR_AsUTF8 ready'
GO


CREATE FUNCTION [dbo].[CLR_JSONtoXML](@source [nvarchar](max))
RETURNS nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[JSONtoXML]
GO
IF OBJECT_ID ('CLR_JSONtoXML', 'FS') IS NOT NULL
 print 'CLR_JSONtoXML ready'
GO

CREATE FUNCTION [dbo].[CLR_XMLtoJSON](@source [nvarchar](max)) --------------------------------------------------------
RETURNS nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[XMLtoJSON]
GO
IF OBJECT_ID ('CLR_XMLtoJSON', 'FS') IS NOT NULL
 print 'CLR_XMLtoJSON ready'
GO

CREATE FUNCTION [dbo].[CLR_URLEncode](@source [nvarchar](max))
RETURNS nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[URLEncode]
GO
IF OBJECT_ID ('CLR_URLEncode', 'FS') IS NOT NULL
 print 'CLR_URLEncode ready'
GO

CREATE FUNCTION [dbo].[CLR_URLDecode](@source [nvarchar](max))
RETURNS nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[URLDecode]
GO
IF OBJECT_ID ('CLR_URLDecode', 'FS') IS NOT NULL
 print 'CLR_URLDecode ready'
GO

CREATE FUNCTION [dbo].[CLR_EmarsysRequest](@Method [nvarchar](4), @URI [nvarchar](1024), @json [nvarchar](max))
RETURNS TABLE (result nvarchar(max), error nvarchar(max))
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[Request]
GO
IF OBJECT_ID ('CLR_EmarsysRequest', 'FT') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_EmarsysRequest ready'
GO

CREATE FUNCTION [dbo].[CLR_EmarsysRequestEx](@Method [nvarchar](4), @User [nvarchar](256), @Password [nvarchar](256), @URI [nvarchar](1024), @json [nvarchar](max))
RETURNS TABLE (result nvarchar(max), error nvarchar(max))
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[RequestEx]
GO
IF OBJECT_ID ('CLR_EmarsysRequestEx', 'FT') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_EmarsysRequestEx ready'
GO


CREATE FUNCTION [dbo].[CLR_SaveToFile](@Data [nvarchar](max), @Filename [nvarchar](max))
RETURNS  nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[SaveToFile]
GO
IF OBJECT_ID ('CLR_SaveToFile', 'FS') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_SaveToFile ready'
GO
-- select dbo.CLR_SaveToFile('Текст для сохранения','C:\tmp\FileName.txt')

CREATE FUNCTION [dbo].[CLR_SaveBinaryToFile](@Data [varbinary](max), @Filename [nvarchar](max))
RETURNS  nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[SaveBinaryToFile]
GO
IF OBJECT_ID ('CLR_SaveBinaryToFile', 'FS') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_SaveBinaryToFile ready'
GO


CREATE FUNCTION [dbo].[CLR_GZipToString](@Data [varbinary](max))
RETURNS  varbinary(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[GZipToString]
GO
IF OBJECT_ID ('CLR_GZipToString', 'FS') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_GZipToString ready'
GO




CREATE FUNCTION [dbo].[CLR_Levis_PingPong]()
RETURNS  nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[Levis_PingPong]
GO
IF OBJECT_ID ('CLR_Levis_PingPong', 'FS') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_Levis_PingPong'
GO


CREATE FUNCTION [dbo].[CLR_Levis_GetPackage](@package [nvarchar](max))
RETURNS  nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[Levis_GetPackage]
GO
IF OBJECT_ID ('CLR_Levis_GetPackage', 'FS') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_Levis_GetPackage'
GO


CREATE FUNCTION [dbo].[CLR_Levis_PutPackage](@package [nvarchar](max))
RETURNS  nvarchar(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[Levis_PutPackage]
GO
IF OBJECT_ID ('CLR_Levis_PutPackage', 'FS') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_Levis_PutPackage'
GO

print 'в данной сборке CLR_GetTextHTML отсутствует (закомментарен)'
GO

CREATE FUNCTION [dbo].[CLR_HttpGetBinary](@url [nvarchar](max))
RETURNS  varbinary(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.HTTPFunctions].[HttpGetBinary]
GO
IF OBJECT_ID ('CLR_HttpGetBinary', 'FS') IS NOT NULL
 print 'CLR_HttpGetBinary ready'
GO

CREATE FUNCTION [dbo].[CLR_HMACSHA1Encode](@base [nvarchar](max), @key [nvarchar](max))
RETURNS  varbinary(max)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.StringFunctions].[HMACSHA1Encode]
GO
IF OBJECT_ID ('HMACSHA1Encode', 'FS') IS NOT NULL -- ATTENTION on Type of function --
 print 'HMACSHA1Encode ready'
GO

CREATE FUNCTION [dbo].[CLR_TableReturnFunction](@params [nvarchar](max))
RETURNS TABLE (DateCreate datetime, Body varbinary(max), Message nvarchar(max))
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TestFunctions].[TableReturnFunction]
GO
IF OBJECT_ID ('CLR_TableReturnFunction', 'FT') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_TableReturnFunction ready'
GO
-- select [DateCreate],[Body],[Message] from dbo.CLR_TableReturnFunction('')


CREATE PROCEDURE [dbo].[CLR_GetUsedDB]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TechFunctions].[GetUsedDB]
GO
IF OBJECT_ID ('CLR_GetUsedDB', 'PC') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_GetUsedDB ready'
GO
--exec dbo.CLR_GetUsedDB

CREATE PROCEDURE [dbo].[CLR_TryClearMemory](@before bigint out, @after bigint out)
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TechFunctions].[TryClearMemory]
GO
IF OBJECT_ID ('CLR_TryClearMemory', 'PC') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_TryClearMemory ready'
GO

CREATE PROCEDURE [dbo].[CLR_WriteLog](@message [nvarchar](max))
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TechFunctions].[WriteLog]
GO
IF OBJECT_ID ('CLR_WriteLog', 'PC') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_WriteLog ready'
GO

CREATE FUNCTION [dbo].[CLR_WriteLogFunc](@message [nvarchar](max))
RETURNS BIT
 WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [SQLfuncs].[SQLfuncs.TechFunctions].[WriteLogFunc]
GO
IF OBJECT_ID ('CLR_WriteLogFunc', 'FS') IS NOT NULL -- ATTENTION on Type of function --
 print 'CLR_WriteLogFunc ready'
GO

GRANT EXECUTE ON [dbo].[CLR_Levis_GetPackage] TO [nav_gate]
GRANT EXECUTE ON [dbo].[CLR_Levis_PingPong] TO [nav_gate]
GRANT EXECUTE ON [dbo].[CLR_Levis_PutPackage] TO [nav_gate]
GRANT EXECUTE ON [dbo].[CLR_HttpGetBlob] TO [mo2]
GRANT EXECUTE ON [dbo].[CLR_HttpGetBlobNew] TO [mo2]
GRANT EXECUTE ON [dbo].[CLR_HttpGetBlobMethod] TO [mo2]

set nocount on

declare @before bigint
declare @after bigint
exec dbo.CLR_TryClearMemory @before out, @after out

grant exec on CLR_HttpGetBlobMethod to public