pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to cascade information like the user roles ?
7+ messages / 3 participants
[nested] [flat]
* How to cascade information like the user roles ?
@ 2010-01-19 00:33 Andreas <maps.on@gmx.net>
2010-01-19 14:27 ` Re: How to cascade information like the user roles ? Filip Rembiałkowski <plk.zuber@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas @ 2010-01-19 00:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to cascade information like the user roles ?
2010-01-19 00:33 How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
@ 2010-01-19 14:27 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2010-01-19 16:47 ` Re: How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 7+ messages in thread
From: Filip Rembiałkowski @ 2010-01-19 14:27 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
2010/1/19 Andreas <maps.on@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
?
> --
> 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.rembialkowski@gmail.com
http://filip.rembialkowski.net/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to cascade information like the user roles ?
2010-01-19 00:33 How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
2010-01-19 14:27 ` Re: How to cascade information like the user roles ? Filip Rembiałkowski <plk.zuber@gmail.com>
@ 2010-01-19 16:47 ` Andreas <maps.on@gmx.net>
2010-01-20 05:45 ` Re: How to cascade information like the user roles ? Gurjeet Singh <singh.gurjeet@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas @ 2010-01-19 16:47 UTC (permalink / raw)
To: Filip Rembiałkowski <plk.zuber@gmail.com>; +Cc: pgsql-sql
Filip Rembiałkowski schrieb:
>
>
> 2010/1/19 Andreas <maps.on@gmx.net <mailto:maps.on@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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to cascade information like the user roles ?
2010-01-19 00:33 How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
2010-01-19 14:27 ` Re: How to cascade information like the user roles ? Filip Rembiałkowski <plk.zuber@gmail.com>
2010-01-19 16:47 ` Re: How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
@ 2010-01-20 05:45 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2010-01-20 06:23 ` Re: How to cascade information like the user roles ? Gurjeet Singh <singh.gurjeet@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Gurjeet Singh @ 2010-01-20 05:45 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: Filip Rembiałkowski <plk.zuber@gmail.com>; pgsql-sql
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 <maps.on@gmx.net>
> Filip Rembiałkowski schrieb:
>
>>
>>
>> 2010/1/19 Andreas <maps.on@gmx.net <mailto:maps.on@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.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Attachments:
App_role_inheritance.sql (application/octet-stream, 2.2K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to cascade information like the user roles ?
2010-01-19 00:33 How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
2010-01-19 14:27 ` Re: How to cascade information like the user roles ? Filip Rembiałkowski <plk.zuber@gmail.com>
2010-01-19 16:47 ` Re: How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
2010-01-20 05:45 ` Re: How to cascade information like the user roles ? Gurjeet Singh <singh.gurjeet@gmail.com>
@ 2010-01-20 06:23 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2010-01-20 16:43 ` Re: How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 7+ messages in thread
From: Gurjeet Singh @ 2010-01-20 06:23 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: Filip Rembiałkowski <plk.zuber@gmail.com>; pgsql-sql
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 <singh.gurjeet@gmail.com>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 <maps.on@gmx.net>
>
>> Filip Rembiałkowski schrieb:
>>
>>>
>>>
>>> 2010/1/19 Andreas <maps.on@gmx.net <mailto:maps.on@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.gurjeet@{ 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.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to cascade information like the user roles ?
2010-01-19 00:33 How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
2010-01-19 14:27 ` Re: How to cascade information like the user roles ? Filip Rembiałkowski <plk.zuber@gmail.com>
2010-01-19 16:47 ` Re: How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
2010-01-20 05:45 ` Re: How to cascade information like the user roles ? Gurjeet Singh <singh.gurjeet@gmail.com>
2010-01-20 06:23 ` Re: How to cascade information like the user roles ? Gurjeet Singh <singh.gurjeet@gmail.com>
@ 2010-01-20 16:43 ` Andreas <maps.on@gmx.net>
2010-01-22 12:25 ` Re: How to cascade information like the user roles ? Gurjeet Singh <singh.gurjeet@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andreas @ 2010-01-20 16:43 UTC (permalink / raw)
To: Gurjeet Singh <singh.gurjeet@gmail.com>; +Cc: pgsql-sql
Thanks a whole lot and some :)
It's great that you actually did a working script.
I find it tremendosly easier to learn with a working example than with
some links to other documentation which makes or does not make sense.
I've got a 8.4 server so both ways work nicely.
Is there a way to prevent inheritance loops instead of denying
parent_id > child_id ?
Parallel inheritance has to work, though. 1 <-- 2 and 3 <-- 4
I found the function running into a stack overflow when I intetionally
created a loop to check what'll happen. At least this is a predefined
limit in max_stack_depth.
The with recursive thingy just kept going which generally tends to be
worse, I guess.
Regards :)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to cascade information like the user roles ?
2010-01-19 00:33 How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
2010-01-19 14:27 ` Re: How to cascade information like the user roles ? Filip Rembiałkowski <plk.zuber@gmail.com>
2010-01-19 16:47 ` Re: How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
2010-01-20 05:45 ` Re: How to cascade information like the user roles ? Gurjeet Singh <singh.gurjeet@gmail.com>
2010-01-20 06:23 ` Re: How to cascade information like the user roles ? Gurjeet Singh <singh.gurjeet@gmail.com>
2010-01-20 16:43 ` Re: How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
@ 2010-01-22 12:25 ` Gurjeet Singh <singh.gurjeet@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Gurjeet Singh @ 2010-01-22 12:25 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Wed, Jan 20, 2010 at 10:13 PM, Andreas <maps.on@gmx.net> wrote:
> Thanks a whole lot and some :)
>
> It's great that you actually did a working script.
> I find it tremendosly easier to learn with a working example than with some
> links to other documentation which makes or does not make sense.
>
> I've got a 8.4 server so both ways work nicely.
>
> Is there a way to prevent inheritance loops instead of denying parent_id >
> child_id ?
>
I'd suggest creating an ON INSERT+UPDATE trigger on app_role_inherits and
checking and disallowing loops there. If possible use a non-recursive method
to detect loop.
> Parallel inheritance has to work, though. 1 <-- 2 and 3 <-- 4
>
I didn't get this sentence!
>
> I found the function running into a stack overflow when I intetionally
> created a loop to check what'll happen. At least this is a predefined limit
> in max_stack_depth.
> The with recursive thingy just kept going which generally tends to be
> worse, I guess.
>
The WITH RECURSIVE is actually implemented non-recursively internally; see:
http://www.postgresql.org/docs/current/static/queries-with.html . So if you
have loops in there, there's nothing stopping the query from running
forever, except probably running out of disk for the intermediate table.
So either prevent loops in the data being queried, or put a WHERE clause
there that would prevent the query running forever.
Best regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com
singh.gurjeet@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-01-22 12:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-19 00:33 How to cascade information like the user roles ? Andreas <maps.on@gmx.net>
2010-01-19 14:27 ` Filip Rembiałkowski <plk.zuber@gmail.com>
2010-01-19 16:47 ` Andreas <maps.on@gmx.net>
2010-01-20 05:45 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2010-01-20 06:23 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2010-01-20 16:43 ` Andreas <maps.on@gmx.net>
2010-01-22 12:25 ` Gurjeet Singh <singh.gurjeet@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox