/*
  Aggregate functions: UnionBinary, IntersectBinary
  Table-valued function: SplitBinary
*/

using System;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;

[assembly: System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", 
"CA1020:AvoidNamespacesWithFewTypes", Scope = "namespace", 
Target = "Microsoft.Samples.SqlServer")]

namespace Microsoft.Samples.SqlServer
{
  [Serializable]
  [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Microsoft.SqlServer.Server.Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = true,
    IsInvariantToOrder = true,
    MaxByteSize = 4100)
  ]
  public class UnionBinary : Microsoft.SqlServer.Server.IBinarySerialize
  {
    private byte[] bitset;

    public void Init() { bitset = new byte[4]; }

    public void Accumulate(SqlInt16 value)
    {
      if (!value.IsNull && value.Value >= 0) {
        if (bitset.Length <= value.Value / 8) {
          Array.Resize(ref bitset, (value.Value/32 + 1) * 4);
        }
        bitset[value.Value / 8] |=
          System.Convert.ToByte(1 << (value.Value % 8));
      }
    }

    public void Merge(UnionBinary other)
    {
      if (other.bitset.Length > bitset.Length) {
        Array.Resize(ref bitset, other.bitset.Length);
      }
      for (int i = 0; i < bitset.Length; i++) {
        bitset[i] |= other.bitset[i];
      }
    }

    public SqlBinary Terminate() { return new SqlBinary(bitset); }

    public void Read(BinaryReader r)
    {
      int i = r.ReadInt32();
      bitset = r.ReadBytes(i);
    }

    public void Write(BinaryWriter w) {
      w.Write(bitset.Length);
      w.Write(bitset);
    }
  }

  [Serializable]
  [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Microsoft.SqlServer.Server.Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = true,
    IsInvariantToOrder = true,
    MaxByteSize = 4100)
  ]
  public class IntersectBinary : Microsoft.SqlServer.Server.IBinarySerialize
  {
    private byte[] bitset;

    public void Init() { bitset = new byte[4]; }

    public void Accumulate(SqlInt16 value)
    {
      if (!value.IsNull && value.Value >= 0) {
        if (bitset.Length <= value.Value / 8) {
          Array.Resize(ref bitset, (value.Value/32 + 1) * 4);
        }
        bitset[value.Value / 8] &=
          System.Convert.ToByte(1 << (value.Value % 8));
      }
    }

    public void Merge(IntersectBinary other)
    {
      if (other.bitset.Length > bitset.Length) {
        Array.Resize(ref bitset, other.bitset.Length);
      }
      for (int i = 0; i < bitset.Length; i++) {
        bitset[i] &= other.bitset[i];
      }
    }

    public SqlBinary Terminate() { return new SqlBinary(bitset); }

    public void Read(BinaryReader r)
    {
      int i = r.ReadInt32();
      bitset = r.ReadBytes(i);
    }

    public void Write(BinaryWriter w) {
      w.Write(bitset.Length);
      w.Write(bitset);
    }
  }

  public sealed class SplitBinaryCls
  {
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable SplitBinary(SqlBinary argument)
    {
      List<Int16> r = new List<Int16>();
      if (!argument.IsNull) {
        for (int i = 0; i < argument.Length; i++) {
          for (int j = 0; j < 8; j++) {
            if ((argument.Value[i] & Convert.ToByte(1 << j)) != 0) {
              r.Add(Convert.ToInt16(i*8+j));
            }
          }
        }
      }
      return r;
    }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", 
"CA1021:AvoidOutParameters")]
    public static void FillRow(Object obj, out Int16 stringElement)
    {
      stringElement = (Int16)obj;
    }

    private SplitBinaryCls() { }
  }
}

CREATE ASSEMBLY BinaryUtilities
FROM 'C:\...\BinaryUtilities\bin\debug\BinaryUtilities.dll'
WITH permission_set=Safe;
GO

CREATE AGGREGATE UnionBinary(@input smallint)
RETURNS varbinary(4096)
EXTERNAL NAME [BinaryUtilities].[Microsoft.Samples.SqlServer.UnionBinary];
GO
CREATE AGGREGATE IntersectBinary(@input smallint)
RETURNS varbinary(4096)
EXTERNAL NAME [BinaryUtilities].[Microsoft.Samples.SqlServer.IntersectBinary];
GO
CREATE FUNCTION SplitBinary(@input varbinary(4096)) 
RETURNS TABLE(ix smallint)
AS EXTERNAL NAME [BinaryUtilities].[Microsoft.Samples.SqlServer.SplitBinaryCls].[SplitBinary];
GO