 $$ LANGUAGE 'plpgsql' STABLE STRICT;
 
 
--- CREATE or REPLACE FUNCTION get_permissions( INT, INT ) RETURNS TEXT AS $$
---   SELECT bits_to_legacy_privilege(user_privileges( $1, $2 ));
--- $$ LANGUAGE 'sql' IMMUTABLE STRICT;
-
 -- List a user's memberships as a text string
 CREATE or REPLACE FUNCTION is_member_of_list( INT8 ) RETURNS TEXT AS $$
 DECLARE

LANGUAGE 'PlPgSQL' IMMUTABLE STRICT;


-- Privileges from accessor to grantor, by principal_id
CREATE or REPLACE FUNCTION principal_privileges( INT8, INT8 ) RETURNS BIT(24) AS $$
DECLARE
  in_accessor ALIAS FOR $1;
  in_grantor  ALIAS FOR $2;
  out_conferred BIT(24);
BEGIN
  out_conferred := 0::BIT(24);
  -- Self can always have full access
  IF in_grantor = in_accessor THEN
    RETURN ~ out_conferred;
  END IF;

  SELECT bit_or(subquery.privileges) INTO out_conferred FROM
     (SELECT privileges FROM grants WHERE by_principal = in_grantor AND to_principal = in_accessor AND NOT is_group AND by_collection IS NULL
                UNION
      SELECT privileges FROM grants JOIN group_member ON (to_principal=group_id AND member_id=in_accessor)
                       WHERE by_principal = in_grantor AND is_group AND by_collection IS NULL
                UNION
      SELECT 32::BIT(24) FROM group_member WHERE group_id = in_grantor AND member_id=in_accessor
     ) AS subquery ;
  IF out_conferred IS NULL THEN
    SELECT default_privileges INTO out_conferred FROM principal WHERE principal_id = in_grantor;
  END IF;

  RETURN out_conferred;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;



-- Privileges from accessor to grantor, by user_no
CREATE or REPLACE FUNCTION user_privileges( INT8, INT8 ) RETURNS BIT(24) AS $$
DECLARE
  in_accessor ALIAS FOR $1;
  in_grantor  ALIAS FOR $2;
  out_conferred BIT(24);
BEGIN
  out_conferred := 0::BIT(24);
  -- Self can always have full access
  IF in_grantor = in_accessor THEN
    RETURN ~ out_conferred;
  END IF;

  SELECT bit_or(subquery.privileges) INTO out_conferred FROM
     (SELECT privileges FROM grants JOIN principal by_p ON (by_principal=by_p.principal_id)
                        JOIN principal to_p ON (to_principal=to_p.principal_id)
                        WHERE by_p.user_no = in_grantor AND to_p.user_no = in_accessor AND by_collection IS NULL
                 UNION
      SELECT privileges FROM grants JOIN group_member ON (to_principal=group_id AND member_id=in_accessor)
                        JOIN principal by_p ON (by_principal=principal_id)
                        JOIN principal to_p ON (member_id=to_p.principal_id)
                       WHERE by_p.user_no = in_grantor AND to_p.user_no = in_accessor AND by_collection IS NULL
                UNION
      SELECT 32::BIT(24) FROM group_member
                        JOIN principal by_p ON (group_id=by_p.principal_id)
                        JOIN principal to_p ON (member_id=to_p.principal_id)
       WHERE group_id = in_grantor AND member_id=in_accessor
     ) AS subquery ;
  IF out_conferred IS NULL THEN
    SELECT default_privileges INTO out_conferred FROM principal WHERE user_no = in_grantor;
  END IF;

  RETURN out_conferred;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;


-- Privileges from accessor (by principal_id) to path
CREATE or REPLACE FUNCTION path_privileges( INT8, TEXT ) RETURNS BIT(24) AS $$
DECLARE
  in_accessor ALIAS FOR $1;
  in_path  ALIAS FOR $2;
  alt1_path TEXT;
  alt2_path TEXT;
  grantor_collection INT8;
  grantor_principal INT8;

  out_conferred BIT(24);
BEGIN
  out_conferred := 0::BIT(24);

  IF in_path ~ '^/?$' THEN
    RETURN 1; -- basic read privileges on root directory
  END IF;

  -- We need to canonicalise the path, so:
  -- If it matches '/' + some characters (+ optional '/')  => a principal URL
  IF in_path ~ '^/[^/]+/?$' THEN
    alt1_path := replace(in_path, '/', '');
    SELECT principal_privileges(in_accessor,principal_id) INTO out_conferred FROM usr JOIN principal USING(user_no) WHERE username = alt1_path;
    RETURN out_conferred;
  END IF;

  -- Otherwise look for the longest segment matching up to the last '/', or if we append one, or if we replace a final '.ics' with one.
  alt1_path := in_path;
  IF alt1_path ~ E'\\.ics$' THEN
    alt1_path := substr(alt1_path, 1, length(alt1_path) - 4) || '/';
  END IF;
  alt2_path := regexp_replace( in_path, '[^/]*$', '');
  SELECT collection_id, principal_id INTO grantor_collection, grantor_principal FROM collection JOIN principal USING (user_no)
                                    WHERE dav_name = in_path || '/' OR dav_name = alt1_path OR dav_name = alt2_path
                                    ORDER BY LENGTH(collection.dav_name) DESC LIMIT 1;

  SELECT bit_or(subquery.privileges) INTO out_conferred FROM
     (SELECT privileges FROM grants WHERE by_collection = grantor_collection AND to_principal = in_accessor AND NOT is_group
                       UNION
      SELECT privileges FROM grants JOIN group_member ON (to_principal=group_id AND member_id=in_accessor)
                       WHERE by_collection = grantor_collection AND is_group
     ) AS subquery ;
  IF out_conferred IS NULL THEN
    SELECT principal_privileges(in_accessor,grantor_principal) INTO out_conferred;
  END IF;

  RETURN out_conferred;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;



-- Expanded group memberships out to some depth
CREATE or REPLACE FUNCTION expand_memberships( INT8, INT ) RETURNS SETOF INT8 AS $$
  SELECT group_id FROM group_member WHERE member_id = $1




-- New in 1.2.7

CREATE or REPLACE FUNCTION write_sync_change( INT8, INT, TEXT ) RETURNS BOOLEAN AS $$
