-- Blob Tabelle
-- Autor: Steffen Anders
-- Mindestens SQL-Server 2016 ist fuer Compression erforderlich
IF CONVERT(integer,SERVERPROPERTY('ProductMajorVersion')) < 13
  RAISERROR (N'%37.37s', -- Message text.
    16, -- Severity,
    1, -- State,
    N'Nicht unterstützte SQL-Server Version'); -- First argument supplies the string.
GO

IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ut_Blobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
  ALTER TABLE [dbo].[ut_Blobs]
    DROP CONSTRAINT [Pk_Blobs]
GO

IF NOT (exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ut_Blobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1))
  CREATE TABLE [dbo].[ut_Blobs] 
  (
      [RecId] int IDENTITY (1, 1) NOT NULL
     ,[BlobData] varbinary(max) NULL 
	 ,[BlobId] int NOT NULL DEFAULT 0
	 ,[PartId] int NOT NULL DEFAULT 0
	 ,[Compressed] int NOT NULL DEFAULT 0
  )
  WITH (DATA_COMPRESSION = ROW)
GO

IF 0 = (select count(*) from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Blobs' and sc.[name] = 'BlobId')
BEGIN
  ALTER TABLE [dbo].[ut_Blobs] ADD [BlobId] int NOT NULL DEFAULT 0
END
GO

IF 0 = (select count(*) from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Blobs' and sc.[name] = 'PartId')
BEGIN
  ALTER TABLE [dbo].[ut_Blobs] ADD [PartId] int NOT NULL DEFAULT 0
END
GO

IF 0 = (select count(*) from syscolumns sc inner join sysobjects so
        on sc.[id] = so.[id] and so.[name] = 'ut_Blobs' and sc.[name] = 'Compressed')
BEGIN
  ALTER TABLE [dbo].[ut_Blobs] ADD [Compressed] int NOT NULL DEFAULT 0
END
GO

ALTER TABLE [dbo].[ut_Blobs]
  ADD CONSTRAINT [Pk_Blobs]  PRIMARY KEY CLUSTERED ( [RecId] )
GO

IF 0 = (select count(*) from sysindexes si inner join sysobjects so 
   on  so.[xtype] = 'U'  and so.[name] = 'ut_Blobs' 
   and so.[id] = si.[id] and si.[name] = 'Ik_LargeBlobs')
  CREATE INDEX [Ik_LargeBlobs] ON [dbo].[ut_Blobs]
    ( [BlobId],[PartId] )
GO

UPDATE [dbo].[ut_Blobs] SET BlobId = RecId WHERE BlobId = 0
GO

------------------------------------------------------------ einfaches Blob anfügen

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Add_Blobs')
  DROP PROCEDURE [dbo].[up_Add_Blobs]
GO

CREATE PROCEDURE [dbo].[up_Add_Blobs] 
AS
  DECLARE @m_RecId int

  INSERT INTO [dbo].[ut_Blobs] ([BlobData]) VALUES (NULL)
  SET @m_RecId = @@IDENTITY
  IF @m_RecId > 0 
    UPDATE [dbo].[ut_Blobs] SET BlobId = RecId, PartId = 1 WHERE RecId = @m_RecId
  SELECT @m_RecId AS RecId 
GO

------------------------------------------------------------  Blobpart anfügen

IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Add_BlobPart')
  DROP PROCEDURE [dbo].[up_Add_BlobPart]
GO

CREATE PROCEDURE [dbo].[up_Add_BlobPart] 
  @RI integer
AS
  -- get BlobId from RecId
  DECLARE @m_BlobId int
  SET @m_BlobId = (SELECT BlobId FROM [dbo].[ut_Blobs] WHERE RecId = @RI)
  IF @m_BlobId = 0 BEGIN
    UPDATE [dbo].[ut_Blobs] SET BlobId = RecId WHERE RecId = @RI
	SET @m_BlobId = @RI
  END

  -- get next new PartId
  DECLARE @m_PartId int
  SET @m_PartId = (SELECT MAX(PartId) + 1 FROM dbo.ut_Blobs WHERE BlobId = @m_BlobId)

  -- create new part
  DECLARE @m_RecId int
  INSERT INTO [dbo].[ut_Blobs] ([BlobData]) VALUES (NULL)
  SET @m_RecId = @@IDENTITY
  IF @m_RecId > 0 
    UPDATE [dbo].[ut_Blobs] SET BlobId = @m_BlobId, PartId = @m_PartId WHERE RecId = @m_RecId
  SELECT @m_RecId AS RecId 
GO

------------------------------------------------------------ 
-- Blob loeschen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Del_Blobs')
  DROP PROCEDURE [dbo].[up_Del_Blobs]
GO
CREATE PROCEDURE [dbo].[up_Del_Blobs]
  @RI integer
AS
  DECLARE @m_BlobId int
  SET @m_BlobId = (SELECT BlobId FROM [dbo].[ut_Blobs] WHERE RecId = @RI)
  IF @m_BlobId <> 0
    DELETE FROM [dbo].[ut_Blobs] WHERE BlobId = @m_BlobId
  ELSE 
    DELETE FROM [dbo].[ut_Blobs] WHERE RecId = @RI
GO

------------------------------------------------------------ 
-- Anzahl Blobteile abfragen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_CountBlobParts')
  DROP PROCEDURE [dbo].[up_CountBlobParts]
GO
CREATE PROCEDURE [dbo].[up_CountBlobParts]
  @RI integer
AS
  DECLARE @m_BlobId int
  SET @m_BlobId = (SELECT BlobId FROM [dbo].[ut_Blobs] WHERE RecId = @RI)
  IF @m_BlobId <> 0
    SELECT COUNT(*) AS PartCount from [dbo].[ut_Blobs] WHERE BlobId = @m_BlobId
  ELSE 
    SELECT 1 AS PartCount
GO

------------------------------------------------------------ 
-- Blobparts auflisten
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_ListBlobParts')
  DROP PROCEDURE [dbo].[up_ListBlobParts]
GO
CREATE PROCEDURE [dbo].[up_ListBlobParts]
  @RI integer
AS
  DECLARE @m_BlobId int
  SET @m_BlobId = (SELECT BlobId FROM [dbo].[ut_Blobs] WHERE RecId = @RI)
  IF @m_BlobId <> 0
    SELECT RecId from [dbo].[ut_Blobs] WHERE BlobId = @m_BlobId ORDER BY PartId
  ELSE 
    SELECT @RI AS RecId
GO

---------------------------------------------------------------
--  Rohdaten des Blobs abfragen (koennen komprimiert sein)
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_BlobsRaw')
  DROP PROCEDURE [dbo].[up_Sel_BlobsRaw]
GO
CREATE PROCEDURE [dbo].[up_Sel_BlobsRaw]
  @RI integer
AS
  DECLARE @m_BlobId int
  SET @m_BlobId = (SELECT BlobId FROM [dbo].[ut_Blobs] WHERE RecId = @RI)
  IF @m_BlobId <> 0
    select ISNULL(DATALENGTH(BlobData),0) AS BlobSize, BlobData from [dbo].[ut_Blobs] WHERE BlobId = @m_BlobId ORDER BY PartId
  ELSE 
    select ISNULL(DATALENGTH(BlobData),0) AS BlobSize, BlobData from [dbo].[ut_Blobs] where RecId = @RI
GO

------------------------------------------------------------ 
-- Rohdatenlaenge abfragen 
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_SizeOf_BlobsRaw')
  DROP PROCEDURE [dbo].[up_SizeOf_BlobsRaw]
GO
CREATE PROCEDURE [dbo].[up_SizeOf_BlobsRaw]
  @RI integer
AS
  DECLARE @m_BlobId int
  SET @m_BlobId = (SELECT BlobId FROM [dbo].[ut_Blobs] WHERE RecId = @RI)
  IF @m_BlobId <> 0
    select SUM(ISNULL(DATALENGTH(BlobData),0)) AS BlobSize from [dbo].[ut_Blobs] WHERE BlobId = @m_BlobId
  ELSE 
    select DATALENGTH(BlobData) AS BlobSize from [dbo].[ut_Blobs] where RecId = @RI
GO

---------------------------------------------------------------------------
-- Blob abfragen
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Sel_Blobs')
  DROP PROCEDURE [dbo].[up_Sel_Blobs]
GO
CREATE PROCEDURE [dbo].[up_Sel_Blobs]
  @RI integer
AS
  DECLARE @m_BlobId int
  SELECT @m_BlobId = BlobId FROM [dbo].[ut_Blobs] WHERE RecId = @RI

  -- create temp table for uncompressed result
  SELECT PartId, Compressed, convert(varbinary(max),BlobData) AS BlobData INTO #TempBlob FROM [dbo].[ut_Blobs] WHERE 0 = 1

  -- insert data into temp table
  IF @m_BlobId <> 0
    INSERT #TempBlob (PartId, Compressed, BlobData) 
	SELECT PartId, Compressed, convert(varbinary(max),BlobData) FROM [dbo].[ut_Blobs] WHERE BlobId = @m_BlobId
  ELSE
    INSERT #TempBlob (PartId, Compressed, BlobData) 
	SELECT PartId, Compressed, convert(varbinary(max),BlobData) FROM [dbo].[ut_Blobs] WHERE RecId = @RI

  -- decompress
  UPDATE #TempBlob SET BlobData = DECOMPRESS(BlobData) WHERE Compressed = 1 AND (NOT BlobData IS NULL)

  -- select kompatible result
  SELECT ISNULL(DATALENGTH(BlobData),0) AS BlobSize, BlobData FROM #TempBlob ORDER BY PartId
GO

------------------------------------------------------------ 
-- entkomprimierte Bloblaenge abfragen 
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_SizeOf_Blobs')
  DROP PROCEDURE [dbo].[up_SizeOf_Blobs]
GO
CREATE PROCEDURE [dbo].[up_SizeOf_Blobs]
  @RI integer
AS
  DECLARE @m_BlobId int
  SELECT @m_BlobId = BlobId FROM [dbo].[ut_Blobs] WHERE RecId = @RI

  -- create temp table for uncompressed result
  SELECT PartId, Compressed, convert(varbinary(max),BlobData) AS BlobData INTO #TempBlob FROM [dbo].[ut_Blobs] WHERE 0 = 1

  -- insert data into temp table
  IF @m_BlobId <> 0
    INSERT #TempBlob (PartId, Compressed, BlobData) 
	SELECT PartId, Compressed, convert(varbinary(max),BlobData) FROM [dbo].[ut_Blobs] WHERE BlobId = @m_BlobId
  ELSE
    INSERT #TempBlob (PartId, Compressed, BlobData) 
	SELECT PartId, Compressed, convert(varbinary(max),BlobData) FROM [dbo].[ut_Blobs] WHERE RecId = @RI

  -- decompress
  UPDATE #TempBlob SET BlobData = DECOMPRESS(BlobData) WHERE Compressed = 1 AND (NOT BlobData IS NULL)

  -- Calculate uncompressed size
  select SUM(ISNULL(DATALENGTH(BlobData),0)) AS BlobSize from #TempBlob
GO

---------------------------------------------------------------
--  Blobdaten aktualisieren
IF 0 < (SELECT count(*) from [sysobjects] where [xtype] = 'P'   and [name] = 'up_Update_BlobPart')
  DROP PROCEDURE [dbo].[up_Update_BlobPart]
GO
CREATE PROCEDURE [dbo].[up_Update_BlobPart]
   @mBinData varbinary(max) 
  ,@mRecId integer
AS
  --UPDATE [dbo].[ut_Blobs] SET Compressed = 0, BlobData = @mBinData WHERE RecId = @mRecId
  IF @mBinData IS NULL
    UPDATE [dbo].[ut_Blobs] SET Compressed = 0, BlobData = NULL WHERE RecId = @mRecId
  ELSE
    UPDATE [dbo].[ut_Blobs] SET Compressed = 1, BlobData = COMPRESS(@mBinData) WHERE RecId = @mRecId
GO
