Hi all

I am designing a table that will control user's access to various parts of the UI - e.g. access to financial reports, the ability to pass credits\write-offs etc. There are >30 areas where each user will be allowed or denied, and some where the field will have up to 5 degrees of access, as in None, Add only, Add and read, Add and edit, Add edit and delete.

I normally use an integer field in cases such as the last example, and a binary comparison to reveal the access 'level'. About half the 30 or so fields could be eliminated as they are true\false dichotomies. Rather than 'CanDoThis' BOOLEAN, and 'CanDoThat' BOOLEAN, etc, the single 'user_access' INTEGER field could hold all those values.

However, could I do a bitwise query to find out which users have access to one particular area? In some other databases thay can run a query such as:
  SELECT * FROM USER WHERE (user_access & 1) >0

A. Is eliminating all the fields a good idea?
B. Is that form of bitwise query possible in EDB?

Regards & TIA

Peter

Peter


>B. Is that form of bitwise query possible in EDB?

I think this is something Tim has said will be introduced so the answer is not yet without writing your own external function.

>A. Is eliminating all the fields a good idea?

It depends on why you're doing it.

Roy Lambert [Team Elevate]

On 9/19/2013 6:51 AM, Peter wrote:
> I normally use an integer field in cases such as the last example, and a binary comparison to reveal the access 'level'. About half the 30 or so fields could be eliminated as they are true\false dichotomies. Rather than 'CanDoThis' BOOLEAN, and 'CanDoThat' BOOLEAN, etc, the single 'user_access' INTEGER field could hold all those values.
> However, could I do a bitwise query to find out which users have access to one particular area? In some other databases thay can run a query such as:
>     SELECT * FROM USER WHERE (user_access & 1) >0
>
> B. Is that form of bitwise query possible in EDB?
>

AFAIK EDB has no bitwise operations but you can write a an external
function - in this case a very trivial one - that can be used in sql
statements.

See :
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=CREATE_FUNCTION
and
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=External_Modules

Raul