New node access sytem

Goal: create an API which given a (nid, uid) pair can hand back a SQL fragment, which is capable of determining without requiring DISTINCT, whether there is a connection (grant) between nid and uid. That Drupal handles three grants and not one is not a problem -- we will use three sets of tables.

Problem: lots of (nid, uid) pairs. We just can't store (number of nodes) * (number of users) lines in access, that'd mean more than 900 million lines for drupal.org.

Solution: we group nodes and groups.

Our tables are

access (ngid, ugid)
node_groups (nid, ngid)
user_groups (uid, ugid)

Will build this in such a way that there if we join the three tables and there is a given (nid, uid) then there will be only one row in a result set. That's the trick.

Let's assume we already have a DB in this state and we are have just added a node to a node group. After that, we check whether our (nid, uid) unicity failed and fix it. Fixing happens by creating a new user group, which consists of all users which has access to node. Also we create a new node group which consists of only this node. We add an access rule between the two new groups. Finally, we remove the node from all old groups that caused unicity violation. It can be seen easily that this node will have one row for each users it belongs to. We have not touched any other relationship.

CREATE TEMPORARY temp1
SELECT uid
FROM node_groups ng
INNER JOIN access a ON ng.ngid = a.ngid
INNER JOIN user_groups ug ON a.ugid = ug.ugid
WHERE nid = $nid
GROUP BY nid, uid
HAVING count(*) > 1;

INSERT INTO access (ngid, ugid) VALUES ($new_ngid, $new_ugid)
INSERT INTO user_groups (ugid, uid) SElECT ($ugid, uid) FROM temp1

CREATE TEMPORARY temp2
SELECT ng.ngid
FROM t1
INNER JOIN node_groups ng ON t1.nid = ng.nid
INNER JOIN user_groups ug ON t1.uid = ug.uid
INNER JOIN access a ON a.ngid = ng.ngid AND a.ugid = ug.ugid
WHERE ng.nid = %d

DELETE node_groups FROM node_groups INNER JOIN temp2 ON node_groups.nid = $nid AND node_groups.ngid = temp2.ngid
INSERT INTO node_groups (nid, ngid) VALUES ($nid, $new_ngid)

The multi table delete can be removed and replace by a PHP loop because it's likely that there will be few lines. Same with temporary tables, but this is certainly faster.

The DELETE should cascade, ie. if a node group becomes empty, it should be deleted and the access rules which has that ngid, too. Now, if a user group has no more access rules, then that can be trashed, too.

The exact same goes for adding a single user to a user group.

Phew! Now, it's nice, but usually we do not need to deal with a single nid but an array of them, like belonging to a certain term. Let's call these 'administrative node groups'. When such a beast is created, we just create a ngid for it and create a new user group, too and add relevant users to it. Later, when node groups are split, nodes from one administrative group may have more than ngid but one ngid will always belong to only one administrative node group. So if you add a user to an 'administrative node group', you know which ngids belong to it, and which user groups has access to those groups and you add the user to said groups.