View blocks by roles

Hi,
Thanks for your feedback.
Well a few mistakes (tabs, my editor is now configured, and user_roles(0)) I will correct in next patch.
A little comment : for the moment I choosed NULL value for everybody if not, we face a few problems :
- if the admin add a new role, it won't be asigned the right to view blocks with the "everybody" grant.
- also if people import their blocks or if they apply this patch with a site containing blocks settings, they would be invisble unless explicitly allowed.
That's why I considered this option, but if you prefer, I can select all checkboxes.

For the validate step, I was thinking of it, but for the moment and the design, It was useless.

Regarding of the table, the only issue I was contempling, was the lack of a block_id in blocks table, so I would have to create one. Are you ok with that ?

Hi,
Sorry for the delay, here is the new patch based on current CVS.

Observations :
- I created a new table : block_visibility that store the visibility for each role / block
When you update db scheme it would make visible blocks for all your availables roles, i.e. it changes nothing ;), just set up correctly permissions.
- When you configure blocks, you will see a set of checkboxes to allow block to be visible regarding of the user's roles. If no role checked, nobody will see it.

Know bugs (by design ?) :
- When a custom block is created it receives no permissions, it means that it would be invisible unless you configure role in "configure" screen.
I'm aware of this problem, but I still don't now how to deal with it.
The major issue is that I can't predict which id will be inserted in 'boxes' table as it works with auto_increment / SERIAL columns instead of Drupal own sequence table.
We can work around it, but I'm not sure it's a good solution to change this, it would cause a lot of troubles...
Maybe look for a db_insert_id function ? Any ideas ?
For the moment, the user have to grant permissions to role to be able to see custom block.

To do :
- Add visibility rehash in _block_rehash function.
- Change help messages ?
- Does my queries look nice ? (It works for sure, but is it a good way to write a join : FROM blocks b LEFT JOIN role r ON 1 = 1 ? )
- Others suggestions ?

It should probably display the checkboxes for permissions on the block creation screen. All checkboxes check by default.

I was thinking of this issue, it's just that I didn't find out how to display it without redundancy between form's groups from block_box_form and from block_block, it's done now.

It really should use Drupal's sequesnces table. You can safely ignore any auto increment or SERIAL. Just use db_next_id as usual.

Ok, I will make the changes.

I've never seen the INSERT ... SELECT syntax elsewhere in Drupal. This may mean that it is not Postgres-compatible or it is avaided for other reasons.

Well INSERT ... SELECT syntax is part of SQL ANSI 92 and fully implemented in Postgre. I couldn't find since when, but at least since 6.4 version of Postgre. So I think it's good enough. And maybe if no drupal developer ever used it, it's maybe they never needed it ;)
Btw, if there's any limitation not to use it, who can tell me ?

A real join condition should be used, such as r.rid = v.rid.

I've done this on purpose :
- If I'm modifying the block options and a new role has been added posterior to the block. Now I got a sort of inconsistance in my block_visibility table, because I miss this new role_id for the (module, delta) tuple.
So when I'm reviewing/modifying my block configuration, I need to detect this inconsistence to be able to add this new (module, delta, rid) tuple in block_visibility.
This is made doing a JOIN between role and blocks with this strange condition : 1=1 because they don't have a commun column. The other reason I've done it this way is because my MySQL documentation told me to prefer LEFT JOIN to RIGHT JOIN for compatibility.
What do you think about it ?
Should I use RIGHT JOIN without fear ?
It would gives us :

SELECT r.rid, v.visible
FROM blocks b
LEFT JOIN block_visibility v ON v.module = b.module AND v.delta = b.delta
RIGHT OUTER JOIN role r ON r.rid = v.rid
WHERE b.module = '%s' AND b.delta = '%s' 

and works the same way.

Tomorrow I'll post my changes.

Ok here is the new version including these changes :
- based on latest CVS version
- I created a new function _block_roles_save to handle saving roles
- when a new custom block is created, it automatically selects all availables roles (user can change it) and save it
- it also handles drupal sequence system for boxes table to allow insertion of the roles next to the custom block creation

Still pending : make a _block_roles_rehash.
Well I'm not very sure about this one, because it would generate a lot of queries :
number of rows in blocks table x num in role
_block_rehash works this way, but is it reasonnable ?
If _block_rehash is needed not to display blocks that doesn't exists anymore, _block_roles_rehash is only here to clean the block_visibility table, but there won't be any problem with new or missing roles, block would just respect current roles.
What should I do ?

In fact this was the way how was working my first patch, but then drumm advice me to go ahead with the block_visibility table, that's done with last version.

Any more improvement ?

While I've gotten this to work I don't think this is the best method to address these block filtering requirements. This method requires the site administrator to know enough PHP to be able to write the filters. It also requires PHP filter code changes every time one of your filter parameters is added to the site or the filter requirements change. If the site admin has a syntax error in the PHP code then the site breaks.

I totally agree that this changes are a little difficult for an admin without a good knowledge of PHP and also Drupal internal function. But this could be handled with providing a good documentation about using this Dries made an interesting post see http://lists.drupal.org/archives/drupal-devel/2005-04/msg01064.html

What about changing block.module to allow custom modules to implement their own filtering? This would allow a wide variety of solutions to be developed and people would not feel the need to hack a solution into the core block module.

I don't think that making various different approach for the same goal is convenient.

I think the idea of "block visibility check" is a little difficult to understand, but providing a little doc to realize basic task should help any "newbie" admin, and then if he wants, he can push forward investingating drupal internal functions (pretty interesting in fact ;) ).

Due to various mails I received about this patch, I have to notify that it's totally unmaintained from a long time due to the patch submitted by chx : Provide a 70x5 textarea in which I can a write a block's "visbility check" using Drupal's APIs. which is far more powerfull than mine but maybe a little more complicated.

This solution is now part of the future 4.7 release, be patient or download the CVS to give it a try.

Thanks