Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Discussion forum for MOD Writers regarding MOD Development.
User avatar
Schnorrer42
Registered User
Posts: 67
Joined: Fri Jan 28, 2005 4:41 am

Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by Schnorrer42 » Wed Feb 24, 2010 2:04 am

I think I'm going to pull my hair out. What's left of it, that is.

I need to be able to get a list of forums that are globally readable --that is guests could see them-- in SQL.
The 2.0.x script used forums.auth_view=0 to accomplish this.

Can someone point me in the right direction to duplicate this functionality in 3.0.x?
Just a snippet of sql? :D :D

Also, is there a primer for understanding the new permissions approach (geared towards SQL would be best)?

Thanks.

User avatar
m157y
Registered User
Posts: 482
Joined: Mon Apr 30, 2007 9:39 am
Location: Russia, Moscow, Khimki
Contact:

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by m157y » Wed Feb 24, 2010 3:51 am

okay, catch your sql-snippet
sql-snippet removed. my mistake in it. link to post with right sql-snippet will be added later
And a reply to your question...

Introduction
New permissions system consists from three parts:
  1. Options
  2. Roles
  3. Users/Groups
So you want to know something about each part.
But i'll tell you only some basics for SQL. If you want to know about administrative part you must read 'phpBB3 Permissions' article at Knowledge Base

Options
It's placed at phpbb_acl_options table. This table looks like this:
  • auth_option_id - unique id for option. It'll be automatically generated by auto_increment at MySQL or analogs at other DBs.
  • auth_option - option name is also unique and used at phpBB code for option searching, for example

    Code: Select all

    $auth->acl_get('a_')
    it'll return true if user has option which called 'a_' and false, if not.
  • is_global - shows to phpbb that this option is global and it used at all forums, like 'a_' which showed at previous example.
  • is_local - shows to phpbb_that this option is local for this forum, for example

    Code: Select all

    $auth->acl_get('f_read', $forum_id)
    at this example function will return true if user has local option for forum with id equal to $forum_id
  • founder_only - shows that this option is available only for board founder(user how was installed this board)
Roles
At DB roles consists from two parts: phpbb_acl_roles and phpbb_acl_roles_data. So i'll tell you about both.
Firstly about phpbb_acl_roles. This table looks like this:
  • role_id - as you've probably already guessed, it's unique id, which will be generated by auto_increment or analogs.
  • role_name - contains name of language variable for role name. So at code you'll be able to use something like this:

    Code: Select all

    $user->lang[$role['role_name']]
    It's very usefull, because all roles was added by phpBB Group at phpBB by default or will be added by MODs so it'll be always translatable to all languages.
  • role_description - and this field contains name of language variable for role description. It can be used like a role_name.
  • role_type - this defines role type. By default phpBB contains four role types:
    1. a_ - global roles for administrator rights, for example a_userdel will show to us, that user has rights for user deleting.
    2. m_ - global and local roles for moderator rights, for example m_lock role for forum will show that user can lock topics at this forum, but m_lock for forum with id equeal zero will show that user can lock topics at all forums.
    3. u_ - global roles for user right, for example u_masspm role for user defines can user send mass private messages or not.
    4. f_ - local roles for forum rights, for example f_read role for forum read ability.
  • role_order - and it's simpliest part of this table, just a number of place at order list for role.
Okay, we can move to second table phpbb_acl_roles_data. It's little but very important at roles work and looks like this:
  • role_id - as you already know it's unique id, but thid id is equal to role id from phpbb_acl_roles table, it connecting roles between those tables.
  • auth_option_id - and this is unique id of option, which was described at first part of my message, it connecting roles with options from phpbb_acl_options table.
  • auth_setting - but this is role setting value. As you know phpBB have three role setting values: 'Yes', 'No' and 'Never'. But at DB we have numbers. Why? It's simple! At ./includes/constants.php you'll find this constant defines:

    Code: Select all

    // ACL
    define('ACL_NEVER', 0);
    define('ACL_YES', 1);
    define('ACL_NO', -1);
    It's our settings! 0 used for 'Never', 1 - for 'Yes' and -1 for 'No'. Some paranoid-users can change this. But most people uses default values. So for what those constants? Of course, for simplification life of phpBB Developers and MOD Developers. They don't need to remember lot of numbers, just constants with names which tells you what they mean. Also i think, that i don't need tell you how works Yes, No and Never values board, because it's information for another article, which was already written.
And i think that we can move to final part of this message...

Users/Groups
Our final part also consists from two parts: phpbb_acl_groups and phpbb_acl_users, but they are very similar. Not difficult to guess that it's because _groups table used for groups and _users - for users.
We start from groups table: phpbb_acl_groups, which looks like this:
  • group_id - as you already guess, it's unique id for group, which generated at phpbb_groups table and connect us with this table.
  • forum_id - and this is unique if for forum. I think it don't need any description...
  • auth_option_id - this field was described two times before. But here it has one disctinction it can be set to zero and it's show to us that we don't set option directly, we using role...
  • auth_role_id - and if we using role, this field will be set to role unique id, which we described before. Of course, if zero here it'll show to us that we don't using role, but using auth option dirctly. Little loop :)
  • auth_setting - and again already described field. This field is replica of auth_setting from phpbb_acl_roles_data, with one difference. It'll be set to zero if we using roles (if auth_role_id not set to zero). It's because roles already have this value and this value will be ignored.
And our last table is phpbb_acl_users, which looks like this:
  • user_id
  • forum_id
  • auth_option_id
  • auth_role_id
  • auth_setting
I don't stopped on this table because is exactly like phpbb_acl_groups only with one difference group_id field replaced with user_id, which as you understand can be founded at phpbb_users table.

That's all! Hope it will help you at MOD writing and permission system understanding.

Sincerely yours,
Andrey (m157y)
Last edited by m157y on Wed Feb 24, 2010 9:55 pm, edited 4 times in total.
m157y aka Misty
NO SUPPORT VIA PM
KarmaMOD for phpBB 3.0.x | bbAJAX
Follow me on twitter

User avatar
m157y
Registered User
Posts: 482
Joined: Mon Apr 30, 2007 9:39 am
Location: Russia, Moscow, Khimki
Contact:

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by m157y » Wed Feb 24, 2010 3:53 am

Khm, maybe this little description of permissions system is opportunity to correct those message for the article in the Knowledge Base.
m157y aka Misty
NO SUPPORT VIA PM
KarmaMOD for phpBB 3.0.x | bbAJAX
Follow me on twitter

User avatar
Schnorrer42
Registered User
Posts: 67
Joined: Fri Jan 28, 2005 4:41 am

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by Schnorrer42 » Wed Feb 24, 2010 8:10 am

Andrey!!!

Thank you very much. I am on my iPhone right now but I'm going to use that SQL tomorrow morning. It almost looks simple, which means that it is the elegant solution.

I also look forward to studying your primer. This screen is too small to be of much use.

Thank you again!
S42

User avatar
m157y
Registered User
Posts: 482
Joined: Mon Apr 30, 2007 9:39 am
Location: Russia, Moscow, Khimki
Contact:

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by m157y » Wed Feb 24, 2010 8:35 am

No problem :)
P.S.
And it's perfectly looks on iPhone at landscape :)
m157y aka Misty
NO SUPPORT VIA PM
KarmaMOD for phpBB 3.0.x | bbAJAX
Follow me on twitter

User avatar
Schnorrer42
Registered User
Posts: 67
Joined: Fri Jan 28, 2005 4:41 am

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by Schnorrer42 » Wed Feb 24, 2010 9:26 pm

Hi,

The SQL produces way too many roles, by about a factor of 200. I get back 4716 rows and when I change the select clause to distinct(f.forum_id) I get 24 rows which is the correct number compared to the phpbb2 forum.

I'm wondering if the issue is ard not being qualified in the first half of the OR clause in the WHERE clause, but I cannot figure out how to fix it... if it is the problem.

edit: When I remove half of the OR clause, the half WITH the ard qualifying clause returns 12 forums, and the half without the qualifyer returns 4704

I hate to bother you to ask for your help again, but I have to.

Thanks again in advance.
S42

User avatar
m157y
Registered User
Posts: 482
Joined: Mon Apr 30, 2007 9:39 am
Location: Russia, Moscow, Khimki
Contact:

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by m157y » Wed Feb 24, 2010 9:54 pm

Yep, sorry me for my mistake. I missed checker for "Yes" state for auth settings. But we don't need distinct, because guests group can have only one 'f_read' role for each forum.
Try this

Code: Select all

SELECT f.*
	FROM phpbb_forums f, phpbb_groups g, phpbb_acl_options ao, phpbb_acl_groups ag, phpbb_acl_roles_data ard
	WHERE ao.auth_option = 'f_read'
		AND (
				(ag.auth_option_id = ao.auth_option_id AND ag.auth_setting = 1)
				OR
				(ard.auth_option_id = ao.auth_option_id AND ag.auth_role_id = ard.role_id AND ard.auth_setting = 1)
			)
		AND ag.group_id = g.group_id
		AND g.group_name = 'GUESTS'
		AND ag.forum_id = f.forum_id;
m157y aka Misty
NO SUPPORT VIA PM
KarmaMOD for phpBB 3.0.x | bbAJAX
Follow me on twitter

User avatar
Schnorrer42
Registered User
Posts: 67
Joined: Fri Jan 28, 2005 4:41 am

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by Schnorrer42 » Wed Feb 24, 2010 10:08 pm

Same number of rows, 4716. :cry:

User avatar
Schnorrer42
Registered User
Posts: 67
Joined: Fri Jan 28, 2005 4:41 am

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by Schnorrer42 » Wed Feb 24, 2010 10:12 pm

Why wouldn't there be an entry in acl_auth_groups with auth_option_id=20 ("f_read") for my GUESTS group ("2759") for each forum where they have read privs? There are only 12 in the table out of the 24 that are allowed to unregistered guests.

(PS Did you catch my edit in my post above?)

User avatar
Schnorrer42
Registered User
Posts: 67
Joined: Fri Jan 28, 2005 4:41 am

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by Schnorrer42 » Wed Feb 24, 2010 10:21 pm

Going on the assumption was that the ARD wasn't qualified, I decided to try a union splitting the two halves of the OR clause, and it seems to be giving the correct results.

Code: Select all

SELECT f.forum_id, forum_name,  "NO ARD","NO ARD"
FROM karmamod_forums f, karmamod_groups g, karmamod_acl_options ao, karmamod_acl_groups ag
WHERE ao.auth_option = 'f_read'
   AND (
         (ag.auth_option_id = ao.auth_option_id)
      )
   AND ag.group_id = g.group_id
   AND g.group_name = 'GUESTS'
   AND ag.forum_id = f.forum_id
UNION
SELECT f.forum_id, forum_name, ard.auth_option_id, ard.role_id
FROM karmamod_forums f, karmamod_groups g, karmamod_acl_options ao, karmamod_acl_groups ag, karmamod_acl_roles_data ard
WHERE ao.auth_option = 'f_read'
   AND (
         (ard.auth_option_id = ao.auth_option_id AND ag.auth_role_id = ard.role_id)
      )
   AND ag.group_id = g.group_id
   AND g.group_name = 'GUESTS'
   AND ag.forum_id = f.forum_id
How does that look to you?

User avatar
m157y
Registered User
Posts: 482
Joined: Mon Apr 30, 2007 9:39 am
Location: Russia, Moscow, Khimki
Contact:

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by m157y » Wed Feb 24, 2010 10:54 pm

As for server-load distinct and union are same, so i'm prefer

Code: Select all

SELECT DISTINCT f.*
   FROM phpbb_forums f, phpbb_groups g, phpbb_acl_options ao, phpbb_acl_groups ag, phpbb_acl_roles_data ard
   WHERE ao.auth_option = 'f_read'
      AND (
            (ag.auth_option_id = ao.auth_option_id AND ag.auth_setting = 1)
            OR
            (ard.auth_option_id = ao.auth_option_id AND ag.auth_role_id = ard.role_id AND ard.auth_setting = 1)
         )
      AND ag.group_id = g.group_id
      AND g.group_name = 'GUESTS'
      AND ag.forum_id = f.forum_id;
P.S.
Union works because it works like distinct.

Update: oh, i'm so stupid. of course, at first half, which is without ARD qualifying clause will return a lot of results, because we doesn't used table with ARD qualifier.
m157y aka Misty
NO SUPPORT VIA PM
KarmaMOD for phpBB 3.0.x | bbAJAX
Follow me on twitter

User avatar
Schnorrer42
Registered User
Posts: 67
Joined: Fri Jan 28, 2005 4:41 am

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by Schnorrer42 » Wed Feb 24, 2010 11:50 pm

Yes, and simplifying further... the first half doesn't need the forum table, either (and we can specify UNION ALL to reduce overhead since they apear to be exclusive?)

Code: Select all

SELECT forum_id, "FROM_AG", "FROM_AG", "FROM_AG"
FROM karmamod_groups g, karmamod_acl_options ao, karmamod_acl_groups ag
WHERE ao.auth_option = 'f_read'
   AND ag.auth_option_id = ao.auth_option_id
   AND ag.group_id = g.group_id
   AND g.group_name = 'GUESTS'
UNION ALL
SELECT f.forum_id, forum_name, ard.auth_option_id, ard.role_id
FROM karmamod_forums f, karmamod_groups g, karmamod_acl_options ao, karmamod_acl_groups ag, karmamod_acl_roles_data ard
WHERE ao.auth_option = 'f_read'
   AND (
         (ard.auth_option_id = ao.auth_option_id AND ag.auth_role_id = ard.role_id)
      )
   AND ag.group_id = g.group_id
   AND g.group_name = 'GUESTS'
   AND ag.forum_id = f.forum_id
OK, so the first half of the UNION is optimized and I understand it. Now on to the second half which I don't understand -- yet. :D :D :D
I still don't understand why just the first half wouldn't be sufficient? Shouldn't all forums have a group permission entry for each group? sigh... But I'm going to dive into this. Thanks again for the pointers, I'll post back if I figure anything else out. Please do the same if I've missed something.

PS the UNION and distinct are about the same in performance, I'd guess, but not when the distinct is covering an error...
However in my taste and opinion, the UNION documents the different sources of the information, explaining the programmer's intent.

I wish the old system back! :D

User avatar
m157y
Registered User
Posts: 482
Joined: Mon Apr 30, 2007 9:39 am
Location: Russia, Moscow, Khimki
Contact:

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by m157y » Thu Feb 25, 2010 12:16 am

You latest sql-snippet will cause an error :)
because you set just forum_id and forum_name without table "pointers". Also if you don't need anything expect forum_id, you're right, you don't need phpbb_forums table.
And some information about second part of union, i think you don't understand this:
ard.auth_option_id = ao.auth_option_id AND ag.auth_role_id = ard.role_id
okay, i'll describe it.
ard.auth_option_id is option id from permission role and it must be option with name 'f_read'
ard.role_id is id of permission role, which we catched by option_id and this id used at groups table.
read about roles from my first post at this topic. :)

Oh, and i forgot to answer on your question.. You need both parts because you can set permissions without roles (select each permissions manually at acp) or with roles (just select roles, like "read-only access"). so you need copy "manual"-permissions(first part) and "role"-permissions(second part).

and about union and union all.
union like distinct just sorts all results and deletes duplicates, but union all just copies two results at one final array. so union all don't delete duplicates. of course, union all will be faster.
m157y aka Misty
NO SUPPORT VIA PM
KarmaMOD for phpBB 3.0.x | bbAJAX
Follow me on twitter

User avatar
Schnorrer42
Registered User
Posts: 67
Joined: Fri Jan 28, 2005 4:41 am

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by Schnorrer42 » Thu Feb 25, 2010 2:49 am

m157y wrote:You latest sql-snippet will cause an error :)
because you set just forum_id and forum_name without table "pointers".
OK, I'm not sure I follow that -- do you mean a syntax error or a logical error? I've just copied the code from this thread, substituted my prefix for karmamod then I re-reran it against my database and it gave the expected results. If you're referring to a lack of "f.", and so-forth, they are only required when the column names are ambiguous to the server. Perhaps we have a different SQL interpreter.
m157y wrote:Also if you don't need anything expect forum_id, you're right, you don't need phpbb_forums table.
Yes, that is my need, a way to find the forums that were globally readable.
m157y wrote:Oh, and i forgot to answer on your question.. You need both parts because you can set permissions without roles (select each permissions manually at acp) or with roles (just select roles, like "read-only access"). so you need copy "manual"-permissions(first part) and "role"-permissions(second part).
Thanks, I guess. I don't understand why my tables were converted in seemingly random ways. I'm not sure I grasp the philosophy behind all of this complexity. I never understood why people were unahppy with phpbb2's permissions, they seemed to work just fine for me, and didn't require an admin permissions trace tool to try to figure out where permissions were coming from.
m157y wrote:and about union and union all.
union like distinct just sorts all results and deletes duplicates, but union all just copies two results at one final array. so union all don't delete duplicates. of course, union all will be faster.
That's exactly what I said. SQL I understand well, phpBB permissions are difficult! :D :D :D :D

I have to change to work on a critical issue at the moment, but I'll get back to work understanding that second half when I'm done.
Last edited by Schnorrer42 on Thu Feb 25, 2010 2:57 am, edited 1 time in total.

User avatar
Schnorrer42
Registered User
Posts: 67
Joined: Fri Jan 28, 2005 4:41 am

Re: Duplicating phpbb2.0.x forums.auth_view in phpbb3.0.x

Post by Schnorrer42 » Thu Feb 25, 2010 2:54 am

echo post

Locked

Return to “[3.0.x] MOD Writers Discussion”