Re: [SQL] How to cascade information like the user roles ?
2010/1/19 Andreas > Hi, > > I need something like the user-roles of PG to store options of my users. > I guess i need a table with roles, options and one that stores the > refernces from roles to options. > > roles (role_id, role_name) > option (option_id, option_name) > role_has_option (role_fk, option_fk) > > so far is easy. Now I can let role1 have option1 and option2 ... > > But I'd further like to let role2 inherit role1's options and also have > option3. > role_inherits_role (parent_role_fk, child_role_fk) > 1, 2 > > What SELECT would deliver all options for role2 inkluding the inherited > ones? > like > role_id, option_id > 2, 1 > 2, 2 > 2, 3 > select role_fk as role_id, option_fk as option_id from role_has_option where role_fk = 2 union select inh.child_role_fk, opt.option_fk from role_has_option opt join role_inherits_role inh on inh.parent_role_fk = opt.role_fk where inh.child_role_fk = 2 ? > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [SQL] How to cascade information like the user roles ?
Filip Rembiałkowski schrieb: 2010/1/19 Andreas mailto:maps...@gmx.net>> Hi, I need something like the user-roles of PG to store options of my users. I guess i need a table with roles, options and one that stores the refernces from roles to options. roles (role_id, role_name) option (option_id, option_name) role_has_option (role_fk, option_fk) so far is easy. Now I can let role1 have option1 and option2 ... But I'd further like to let role2 inherit role1's options and also have option3. role_inherits_role (parent_role_fk, child_role_fk) 1, 2 What SELECT would deliver all options for role2 inkluding the inherited ones? like role_id, option_id 2, 1 2, 2 2, 3 select role_fk as role_id, option_fk as option_id from role_has_option where role_fk = 2 union select inh.child_role_fk, opt.option_fk from role_has_option opt join role_inherits_role inh on inh.parent_role_fk = opt.role_fk where inh.child_role_fk = 2 Thanks. I am looking for a more general solution that expands even multiple steps of inheritance like a more complex example: role_1 --> option_1 + option_2 role_2 --> option_3 and inherits role_1 role_3 --> option_2 + option_4 role_4 --> option_5 and inherits role_2 and role_3 I need a general solution that gives all options for any given role including every inherited options over a unlimited hierarchy of parents. Sounds complex, I know, but this is what PG does with its user-roles. So I'd do in this example a SELECT ... WHERE role_id = 4 and get 4, 5 directly 4, 3 from role_2 4, 1 from role_1 over role_2 4, 2 from role_1 over role_2 4, 2 from role_3 (inherited double occurance) 4, 4 from role_4 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to cascade information like the user roles ?
You need a recursive lookup, and I guess 8.4's WITH RECURSIVE construct can
help here. But if you are on an older version of Postgres, you will have to
write recursive functions to do it.
I tried my hands on it, and attached is an implementation of such a
recursive function. It returns the expected results.
Hope it helps.
Best regards,
2010/1/19 Andreas
> Filip Rembiałkowski schrieb:
>
>>
>>
>> 2010/1/19 Andreas mailto:maps...@gmx.net>>
>>
>>
>>Hi,
>>
>>I need something like the user-roles of PG to store options of my
>>users.
>>I guess i need a table with roles, options and one that stores the
>>refernces from roles to options.
>>
>>roles (role_id, role_name)
>>option (option_id, option_name)
>>role_has_option (role_fk, option_fk)
>>
>>so far is easy. Now I can let role1 have option1 and option2 ...
>>
>>But I'd further like to let role2 inherit role1's options and also
>>have option3.
>>role_inherits_role (parent_role_fk, child_role_fk)
>>1, 2
>>
>>What SELECT would deliver all options for role2 inkluding the
>>inherited ones?
>>like
>>role_id, option_id
>>2, 1
>>2, 2
>>2, 3
>>
>>
>>
>> select role_fk as role_id, option_fk as option_id from role_has_option
>> where role_fk = 2
>> union
>> select inh.child_role_fk, opt.option_fk from role_has_option opt join
>> role_inherits_role inh on inh.parent_role_fk = opt.role_fk where
>> inh.child_role_fk = 2
>>
> Thanks.
> I am looking for a more general solution that expands even multiple steps
> of inheritance like a more complex example:
> role_1 --> option_1 + option_2
> role_2 --> option_3 and inherits role_1
> role_3 --> option_2 + option_4
>
> role_4 --> option_5 and inherits role_2 and role_3
>
> I need a general solution that gives all options for any given role
> including every inherited options over a unlimited hierarchy of parents.
> Sounds complex, I know, but this is what PG does with its user-roles.
> So I'd do in this example a SELECT ... WHERE role_id = 4
> and get
> 4, 5 directly
> 4, 3 from role_2
> 4, 1 from role_1 over role_2
> 4, 2 from role_1 over role_2
> 4, 2 from role_3 (inherited double occurance)
> 4, 4 from role_4
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
App_role_inheritance.sql
Description: Binary data
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to cascade information like the user roles ?
And here's the WITH RECURSIVE version, which does not need the recursive
function, but will work only with Postgres 8.4 or above.
postgres=# with recursive roles(role_id) as
(select 4
union all
select parent_role_id
from app_role_inherits as i,
roles as r
where i.role_id = r.role_id)
select /* r.role_id, */ m.option_id
from roles as r,
app_role_option_map m
where m.role_id = r.role_id;
option_id
---
1
2
3
2
4
5
(6 rows)
Best regards,
On Wed, Jan 20, 2010 at 11:15 AM, Gurjeet Singh wrote:
> You need a recursive lookup, and I guess 8.4's WITH RECURSIVE construct can
> help here. But if you are on an older version of Postgres, you will have to
> write recursive functions to do it.
>
> I tried my hands on it, and attached is an implementation of such a
> recursive function. It returns the expected results.
>
> Hope it helps.
>
> Best regards,
>
>
> 2010/1/19 Andreas
>
>> Filip Rembiałkowski schrieb:
>>
>>>
>>>
>>> 2010/1/19 Andreas mailto:maps...@gmx.net>>
>>>
>>>
>>>Hi,
>>>
>>>I need something like the user-roles of PG to store options of my
>>>users.
>>>I guess i need a table with roles, options and one that stores the
>>>refernces from roles to options.
>>>
>>>roles (role_id, role_name)
>>>option (option_id, option_name)
>>>role_has_option (role_fk, option_fk)
>>>
>>>so far is easy. Now I can let role1 have option1 and option2 ...
>>>
>>>But I'd further like to let role2 inherit role1's options and also
>>>have option3.
>>>role_inherits_role (parent_role_fk, child_role_fk)
>>>1, 2
>>>
>>>What SELECT would deliver all options for role2 inkluding the
>>>inherited ones?
>>>like
>>>role_id, option_id
>>>2, 1
>>>2, 2
>>>2, 3
>>>
>>>
>>>
>>> select role_fk as role_id, option_fk as option_id from role_has_option
>>> where role_fk = 2
>>> union
>>> select inh.child_role_fk, opt.option_fk from role_has_option opt join
>>> role_inherits_role inh on inh.parent_role_fk = opt.role_fk where
>>> inh.child_role_fk = 2
>>>
>> Thanks.
>> I am looking for a more general solution that expands even multiple steps
>> of inheritance like a more complex example:
>> role_1 --> option_1 + option_2
>> role_2 --> option_3 and inherits role_1
>> role_3 --> option_2 + option_4
>>
>> role_4 --> option_5 and inherits role_2 and role_3
>>
>> I need a general solution that gives all options for any given role
>> including every inherited options over a unlimited hierarchy of parents.
>> Sounds complex, I know, but this is what PG does with its user-roles.
>> So I'd do in this example a SELECT ... WHERE role_id = 4
>> and get
>> 4, 5 directly
>> 4, 3 from role_2
>> 4, 1 from role_1 over role_2
>> 4, 2 from role_1 over role_2
>> 4, 2 from role_3 (inherited double occurance)
>> 4, 4 from role_4
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>
>
> --
> gurjeet.singh
> @ EnterpriseDB - The Enterprise Postgres Company
> http://www.enterprisedb.com
>
> singh.gurj...@{ gmail | yahoo }.com
> Twitter/Skype: singh_gurjeet
>
> Mail sent from my BlackLaptop device
>
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
