Aggregate functions

chm.Concat

This function concatenates varchar/nvarchar columns in aggregation queries.

Example:

Suppose we have the following tables.

dbo.Books

dbo.Tags

dbo.BookTags

We can write a query like below to get list of books and their tags.

		  select
	b.[title],
	chm.Concat(t.[name], ',') as [tags]
from            dbo.Books       b
	inner join  dbo.BookTags    bt  on b.[id]     = bt.[bookId]
	inner join  dbo.Tags        t   on bt.[tagId] = t.[id]
group by b.[title]
		

chm.BitwiseAnd / chm.BitwiseOr

These functions apply bitwise and/or merge on integer columns in aggregation queries.

Practical Example, ACL system

In this section we implement a simple ACL system to show a practical usecase for BitwiseAnd nad BitwiseOr aggregate functions. List of tables we use are as follows:

dbo.Users

dbo.Roles

dbo.Entities

dbo.RolePermissions

dbo.UserRoles

In this ACL system permissions are defined over entities and they are granted to roles, not users. Each role can have a number of permissions over an entity.

Permissions of a role is defined as a tinyint value. It holds a combination of permissions. Each bit in a tinyint grant value means a specific permission. They are listed in the following table:

As it is seen, our ACL system supports 4 permissions. The rest 4 bits are reserved for later usage. Having this system, for a role like Operator to have View/Add/Edit permissions over 'Book' entity, a 7 value will be calculated.

		  R R R R D E A V
0 0 0 0 0 1 1 1  = 7
		

And permission value for an Admin role with full control over 'Book' entity would be 15.

		  R R R R D E A V
0 0 0 0 1 1 1 1  = 15
		

Now, since users can have multiple roles, a user can have multiple permissions over an entity. We need to perform a bitwise merge operation in order to retrieve final permissions of a user.

In fact, we need to have a polciy to decide what we should do when a user has multiple same permissions over an entity (e.g. he has an Add permission over Books using his Admin role, but he does not have such a permission over Books using his Member role).

We can use a bitwise merge over user permissions. This could be performed both as a bitwise-or or bitwise-and. It depends on our policy which one to choose. A bitwise-or is more permissive while a bitwise-and is more restrictive.

Now, we can write a helper UDF like below to get permissions of a given user over an entity:

		  create or alter dbo.GetPermission
(
	@username   varchar(50),
	@entity     varchar(100)
)
returns tinyint
as
begin
	declare @result tinyint

	select
		@result = chm.BitwiseOr(rp.[vale])
	from                dbo.Users           u
		inner join  dbo.UserRoles       ur  on u.[id]        = ur.[userId]
		inner join  dbo.Roles           r   on ur.[roleId]   = r.[id]
		inner join  dbo.RolePermissions rp  on r.[roleId]    = rp.[roleId]
		inner join  dbo.Entities        e   on rp.[entityId] = e.[id]
	where u.[username] = @username and e.[name] = @entity

	set @result = isnull(@result, 0)

	return @result
end
		

Here, we used bitwise-or policy. In order to switch to bitwise-and, we can simply replace chm.BitwiseOr() with chm.BitwiseAnd().

This simple and easy-to-use ACL system could be used in any type of application with any type of technology. After retrieving permissions of a user over an entity we can decide to show or hide buttons like 'Add', 'Edit', 'Delete' in say, administration panel of the application.

Here is a helper C# class that provides boolean getter properties for a loaded permission of a user:

		  public class UserPermission
{
	public readonly byte Value { get; private set; }
	public UserPermission(byte value)
	{
		Value = value;
	}
	public bool Check(byte index)
	{
		var bitValue = Math.Power(2, index);

		return Value & bitValue == bitValue;
	}
	public bool View
	{
		get { return Check(0); }
	}
	public bool Add
	{
		get { return Check(1); }
	}
	public bool Edit
	{
		get { return Check(2); }
	}
	public bool Delete
	{
		get { return Check(3); }
	}
}
		

Having this class, using it will be easy. The following is a Web API that returns permission of current user for a requested entity.

		  [ApiController]
[Route("[controller]/[entity]")]
public class PermissionController: ControllerBase
{
	readonly IConfiguration __configuration;
	public PermissionController(IConfiguration configuration)
	{
		_configuration = configuration;
	}
	public UserPermission Get(string entity)
	{
		var constr = _configuration.GetConnectionString("DefaultConnection");

		using (var con = new SqlConnection(constr))
		{
			using (var cmd = new SqlCommand("select dbo.GetPermission(@username, @entity)", con)))
			{
				cmd.Parameters.AddWithValue("@username", User.Identity.Name);
				cmd.Parameters.AddWithValue("@entity", entity);

				var permission = Convert.ToByte(cmd.ExecuteScaler());

				return new UserPermission(permission);
			}
		}
	}
}
		

If we use, Util.Db in Chameleon.Core, we can make Get() action simpler:

		  	...
public UserPermission Get(string entity)
{
	Util.Db.ConnectionString = _configuration.GetConnectionString("DefaultConnection");
	
	var permission = Util.Db.ExecuteSingleSql("select dbo.GetPermission(@username, @entity)",
							reader => new UserPermission(Convert.ToByte(reader[0])),
							new { username = User.Identity.Name, entity = entity });
	return permission;
}
		

For the final piece to complete the puzzle, here is a sample BookList React component that displays list of books and conditionally shows Add/Edit/Delete buttons based on current user's permissions fetched from the server -using the API provided above.

		  import React, { useState, useEffect } from 'react';
import { Link } from 'react-router-dom';
import { Button } from 'reactstrap';

const BookList = () => {
	const [books, setBooks] = useState([]);
	const [permission, setPermission] = useState({});

	const fetchBooks = async () => {
		const res = await fetch('/api/books');
		const books = await res.json();

		setBooks(books);
	}

	useEffect(() => {
		(async () => {
			const res = await fetch('/api/permission/books');
			const permission = await res.json();

			setPermission(permission);

			await fetchBooks();
		})();
	}, []);

	const handleDelete = async (bookId) => {
		if (confirm('Are you sure?')) {
			const res = await fetch(`/api/books/${bookId}`, {
				method: 'DELETE'
			});

			const result = await res.json();

			if (result) {
				await fetchBooks();
			}
		}
	}

	return <>
		{permission.add ? <Link to="/books/add">
			<Button variant="primary" size="md">
			Add
			</Button>
		</Link>: null}
		<Table className="table">
		<thead>
			<tr>
			<th>#</th>
			{permission.edit ? <th></th>: null}
			{permission.delete ? <th></th>: null}
			<th>Title</th>
			<th>Year</th>
			<th>Price</th>
			</tr>
		</thead>
		<tbody>
			{books.map((book, index) => <tr>
				<th scope="row">{index}</th>
				<td>{book.title}</td>
				{permission.edit ? <td>
					<Link to={`/books/edit/${book.id}`}>
						<Button variant="success" size="md">
						Edit
						</Button>
					</Link>
				</td>: null}
				{permission.delete ? <td>
					<Button variant="danger" size="md" onClick={() => handleDelete(book.id)}>
						Delete
					</Button>
				</td>: null}
				<td>{book.title}</td>
				<td>{book.year}</td>
				<td>{book.price}</td>
				</tr>)}
		</tbody>
		</Table>
	</>;
}

export default BookList;