Bitwise or aggregate function

Hi,
I understand there are no aggregate function for ORing multiple row columns
together. We have a access control system that looks like
CREATE TABLE AccessTable
(
UserID int NOT NULL PRIMARY KEY,
EntityID int NOT NULL PRIMARY KEY,
AccessMask int NOT NULL
)
where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
etc. We would like to be able to do something like
select AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = @.EntityID
I've seen some samples of a two table solution but I just wanted to double
check if someone has a better solution. Running SQL Server 2005.
Thanks,
MansoManso wrote:
> Hi,
> I understand there are no aggregate function for ORing multiple row column
s
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
> Thanks,
> Manso
Why would you store this data as a bitmap in the first place? Try:
SELECT
MAX(AccessMask & 1)+
MAX(AccessMask & 2)+
MAX(AccessMask & 4)+
MAX(AccessMask & 8)+
MAX(AccessMask & 16)+
MAX(AccessMask & 32)+
MAX(AccessMask & 64)+
MAX(AccessMask & 128) AS aggr_orsum
FROM AccessTable
WHERE EntityID = @.EntityID ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Manso" <Manso@.discussions.microsoft.com> wrote in message
news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Hi,
> I understand there are no aggregate function for ORing multiple row
> columns
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
>
Well, it so happens SQL Server 2005 supports User-Defined Aggregate
functions.
For instance
--Aggregate.cs--
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(Format.Native,
IsInvariantToDuplicates=true,
IsInvariantToNulls = true,
IsInvariantToOrder = true,
IsNullIfEmpty = false)]
public struct AGGR_ORSUM
{
int accumulator;
public void Init()
{
accumulator = 0;
}
public void Accumulate(SqlInt32 Value)
{
if (Value.IsNull)
return;
accumulator |= Value.Value;
}
public void Merge(AGGR_ORSUM Group)
{
accumulator |= Group.accumulator;
}
public SqlInt32 Terminate()
{
return new SqlInt32(accumulator);
}
}
--end Aggregate.cs--
--Test.sql--
\
drop table AccessTable
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL ,
AccessMask int NOT NULL
)
insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
select dbo.AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = 1
--end Test.sql--
David|||Thanks for your prompt reply David.
Because it's a convenient way of representing access masks. I know it has a
few drawbacks but the upside is we can append new access bits without adding
columns or modifying the schema. Since we're never using the access column i
n
a where clause I find it to be the best option. We have about 13 different
access types today but that will increase.
I just managed to put together a function that does what we want:
declare @.Return int
set @.Return = 0
select @.Return = @.Return | AccessMask
from AccessTable
where where EntityID = @.EntityID
return @.Return
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> Why would you store this data as a bitmap in the first place? Try:
> SELECT
> MAX(AccessMask & 1)+
> MAX(AccessMask & 2)+
> MAX(AccessMask & 4)+
> MAX(AccessMask & 8)+
> MAX(AccessMask & 16)+
> MAX(AccessMask & 32)+
> MAX(AccessMask & 64)+
> MAX(AccessMask & 128) AS aggr_orsum
> FROM AccessTable
> WHERE EntityID = @.EntityID ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Manso wrote:
> Thanks for your prompt reply David.
> Because it's a convenient way of representing access masks. I know it has
a
> few drawbacks but the upside is we can append new access bits without addi
ng
> columns or modifying the schema. Since we're never using the access column
in
> a where clause I find it to be the best option. We have about 13 different
> access types today but that will increase.
How about :
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL,
AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
PRIMARY KEY (UserID, EntityID, AccessTypeID)
)

> I just managed to put together a function that does what we want:
> declare @.Return int
> set @.Return = 0
> select @.Return = @.Return | AccessMask
> from AccessTable
> where where EntityID = @.EntityID
> return @.Return
>
That gives an undefined result because it's a multi-row assignment,
which isn't officially supported. For that reason it may break under
some conditions. Also, it isn't a general aggregation solution because
it cannot GROUP.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Beautiful, David. Thanks a lot. We've been really indecisive whether to use
CLR in this project but maybe we should look into it...looks .
Thanks,
Manso
"David Browne" wrote:

> "Manso" <Manso@.discussions.microsoft.com> wrote in message
> news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Well, it so happens SQL Server 2005 supports User-Defined Aggregate
> functions.
> For instance
> --Aggregate.cs--
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
>
> [Serializable]
> [SqlUserDefinedAggregate(Format.Native,
> IsInvariantToDuplicates=true,
> IsInvariantToNulls = true,
> IsInvariantToOrder = true,
> IsNullIfEmpty = false)]
> public struct AGGR_ORSUM
> {
> int accumulator;
> public void Init()
> {
> accumulator = 0;
> }
> public void Accumulate(SqlInt32 Value)
> {
> if (Value.IsNull)
> return;
> accumulator |= Value.Value;
> }
> public void Merge(AGGR_ORSUM Group)
> {
> accumulator |= Group.accumulator;
> }
> public SqlInt32 Terminate()
> {
> return new SqlInt32(accumulator);
> }
>
> }
> --end Aggregate.cs--
> --Test.sql--
> \
> drop table AccessTable
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL ,
> AccessMask int NOT NULL
> )
> insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
> insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
> insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
> select dbo.AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = 1
> --end Test.sql--
> David
>
>|||I left out the check constraint for clarity.
I read somewhere else that this could give unwanted results somewhere else
but I haven't managed to find out under which circumstances. Do you know
when/why/how this could break?
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> How about :
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL,
> AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
> PRIMARY KEY (UserID, EntityID, AccessTypeID)
> )
>
> That gives an undefined result because it's a multi-row assignment,
> which isn't officially supported. For that reason it may break under
> some conditions. Also, it isn't a general aggregation solution because
> it cannot GROUP.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Manso wrote:
> I left out the check constraint for clarity.
But in my example you don't need the bitmap (because I changed the
key).

> I read somewhere else that this could give unwanted results somewhere else
> but I haven't managed to find out under which circumstances. Do you know
> when/why/how this could break?
Books Online (2000 and 2005) says
"If the SELECT statement returns more than one value, the variable is
assigned the last value returned."
So if your "aggregate" solution works it is arguably a bug!
The following KB confirms that "The correct behavior for an aggregate
concatenation query is undefined." So you are definitely on thin ice if
you rely on it.
http://support.microsoft.com/kb/287515/en-us
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"The correct behavior for an aggregate concatenation query is undefined."
This classic contrived phrase was written by some still unknown fuzzball
confirmed by
one intelligent MS employee.It is meaningless and that's putting it kindly:)
Perhaps it was Celko:) My guess is that since it came from Sybase MS thinks
the less said
about it the better.Aside from the functionality,which boils down to 'it
depends',
it is another example that 'words due matter':)
For interested readers see what sybase says about it (especially in Update)
in their
enterprise db.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145543785.636307.71580@.i39g2000cwa.googlegroups.com...
> Manso wrote:
> But in my example you don't need the bitmap (because I changed the
> key).
>
> Books Online (2000 and 2005) says
> "If the SELECT statement returns more than one value, the variable is
> assigned the last value returned."
> So if your "aggregate" solution works it is arguably a bug!
> The following KB confirms that "The correct behavior for an aggregate
> concatenation query is undefined." So you are definitely on thin ice if
> you rely on it.
> http://support.microsoft.com/kb/287515/en-us
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Writing assembly language in SQL will come back and get you. Use a
normalized table design with one column for each access privilege or a
hierarchy of privileges in a nested sets model.
Also, you had two PRIMARY KEYs!