FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum Development » Bug Reports » fud30_group_cache table, shouldn't it be a view?
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
fud30_group_cache table, shouldn't it be a view? [message #167259] Sat, 26 May 2012 02:41 Go to next message
NeXuS is currently offline  NeXuS   Korea, Republic of
Messages: 121
Registered: July 2010
Location: South Korea
Karma: 5
Senior Member
Contributing Core Developer
add to buddy list
ignore all messages by this user
I am actually a little surprised by this, and I may be wrong in the understanding of the DB structure, but it seems to me that all the data in fud30_group_cache is already contained in other tables, so why isn't it a view?
Re: fud30_group_cache table, shouldn't it be a view? [message #167262 is a reply to message #167259] Sat, 26 May 2012 04:03 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3628
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
FUDforum predates views in MySQL. If this table can be change this to a view, you are more than welcome to prepare a patch.
Re: fud30_group_cache table, shouldn't it be a view? [message #167347 is a reply to message #167262] Sat, 09 June 2012 06:03 Go to previous messageGo to next message
NeXuS is currently offline  NeXuS
Messages: 121
Registered: July 2010
Location: South Korea
Karma: 5
Senior Member
Contributing Core Developer
add to buddy list
ignore all messages by this user
I am about to patch the source tree, I would just have a few other heads check my view formulation

-- GROUP LEADERS 
SELECT gm.user_id, gr.resource_id, gm.group_id, 
    BIT_OR( gm.group_members_opt ) AS group_cache_opts
  FROM fud30_group_members gm 
  INNER JOIN fud30_group_resources gr
    ON gr.group_id=gm.group_id
  WHERE ( gm.group_members_opt>=65536 )
    AND ( gm.group_members_opt & 65536 ) > 0
    AND ( gm.group_members_opt & 131072 )
  GROUP BY user_id, resource_id, group_id
UNION
-- GROUP MEMBERS 
SELECT gm.user_id, gr.resource_id, gr.group_id, 
    BIT_AND( gm.group_members_opt ) AS group_cache_opts
  FROM fud30_group_members AS gm    
  INNER JOIN fud30_group_resources AS gr
    ON gr.group_id=gm.group_id
  WHERE ( gm.group_members_opt>=65536 )
    AND ( gm.group_members_opt & 65536 ) > 0
  GROUP BY user_id, resource_id, group_id;


By the way, the result generated by the statement above is different from fud30_group_cache: in fud30_group_cache, group_id is always 0, here it is set properly. I am not sure this is the correct behaviour, though, since the key of the caching seems to be (user_id, resource_id) independently of the group_id.

P.S. I am pretty sure this works for MySQL, but the syntax might need to be changed for other engines

[Updated on: Sat, 09 June 2012 06:10]

Report message to a moderator

Re: fud30_group_cache table, shouldn't it be a view? [message #167352 is a reply to message #167347] Sat, 09 June 2012 08:30 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3628
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
Bitwise operations are not cross-database compatible.
You need to use q_bitand(), q_bitor() and q_bitnot().
Re: fud30_group_cache table, shouldn't it be a view? [message #167353 is a reply to message #167352] Sat, 09 June 2012 09:21 Go to previous messageGo to next message
NeXuS is currently offline  NeXuS
Messages: 121
Registered: July 2010
Location: South Korea
Karma: 5
Senior Member
Contributing Core Developer
add to buddy list
ignore all messages by this user
I had imagined that much, I was more concerned about the correctnes of the query itself. I will check on my dev system ASAP, then make a patch.
Re: fud30_group_cache table, shouldn't it be a view? [message #167354 is a reply to message #167353] Sat, 09 June 2012 10:31 Go to previous messageGo to next message
NeXuS is currently offline  NeXuS
Messages: 121
Registered: July 2010
Location: South Korea
Karma: 5
Senior Member
Contributing Core Developer
add to buddy list
ignore all messages by this user
Ok, so here's a big question: how the hell do I use q_bit*() in the sql/*.tpl files?
If I can't I'll need to put the view creation somewhere else.
Re: fud30_group_cache table, shouldn't it be a view? [message #167361 is a reply to message #167354] Sun, 10 June 2012 03:42 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3628
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
We probably need to enhance the install & upgrade scripts to read the MySQL syntax from sql/*.tpl files and transform it into q_bit*() syntax before execution.
Re: fud30_group_cache table, shouldn't it be a view? [message #167448 is a reply to message #167361] Fri, 22 June 2012 03:31 Go to previous messageGo to next message
NeXuS is currently offline  NeXuS
Messages: 121
Registered: July 2010
Location: South Korea
Karma: 5
Senior Member
Contributing Core Developer
add to buddy list
ignore all messages by this user
I have a patch ready with the q_bit syntax everyting, but it seems I have hit a problem with MySQL views.
The select statement works as expected but the view gives an error
ERROR 1356 (HY000): View 'fud_test.group_cache' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use


I am filing a bug in a few minutes, let's see if there is any answer.
Re: fud30_group_cache table, shouldn't it be a view? [message #167467 is a reply to message #167448] Tue, 26 June 2012 20:40 Go to previous messageGo to next message
NeXuS is currently offline  NeXuS   Korea, Republic of
Messages: 121
Registered: July 2010
Location: South Korea
Karma: 5
Senior Member
Contributing Core Developer
add to buddy list
ignore all messages by this user
Ok, got response from MySql devs. It is a bug in MySql < 5.6.
What should I do regarding the patch? Shall I commit it, and then we change the system requirements?

EDIT:

Uhm... seems like 5.6 is the unstable branch, so I guess my patch will need to wait.

[Updated on: Tue, 26 June 2012 20:45]

Report message to a moderator

Re: fud30_group_cache table, shouldn't it be a view? [message #167468 is a reply to message #167467] Wed, 27 June 2012 02:31 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3628
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
add to buddy list
ignore all messages by this user
I suggest we create a new 'extra/patches' directory under trunk and submit it there.

We can later use this 'extra' directory for various other stuff, like 'extra/plugins', 'extra/themes', etc.
Re: fud30_group_cache table, shouldn't it be a view? [message #167469 is a reply to message #167468] Wed, 27 June 2012 02:52 Go to previous message
NeXuS is currently offline  NeXuS   Korea, Republic of
Messages: 121
Registered: July 2010
Location: South Korea
Karma: 5
Senior Member
Contributing Core Developer
add to buddy list
ignore all messages by this user
Sounds like a good idea, will do.
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: upgrade.php and q_bitand()
Next Topic: Backup bug
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 11 08:34:08 EST 2017

Total time taken to generate the page: 0.00849 seconds