Forum OpenACS Development: Re: Permission management reform for PostgreSQL

Posted by Gustaf Neumann on
Dear all, runs since a few days with the object context index (and the whole update machinery) removed. As noted above, here is the upgrade scripts for PostgreSQL for site-admins who want to drop the useless tables and triggers already now.

The plan should be to still create/maintain these tables for new installations for the time being, but to drop these in the next major release.

all the best

-- 1) Make sure to upgrade to at least acs-kernel 5.9.1d13
-- 2) Make a database dump as a backup; dropping is faster than recreating!

-- Drop the unused (potentially huge) materialized view
-- "acs_object_context_index" and its maintenance infrastructure.
DROP TABLE IF EXISTS acs_object_context_index CASCADE;
DROP TRIGGER IF EXISTS acs_objects_context_id_in_tr ON acs_objects CASCADE;
DROP TRIGGER IF EXISTS acs_objects_context_id_up_tr ON acs_objects CASCADE;
DROP FUNCTION IF EXISTS acs_objects_context_id_in_tr();
DROP FUNCTION IF EXISTS acs_objects_context_id_up_tr();

-- Drop the two (!) materialized views for the privilege hierarchy.
DROP TABLE IF EXISTS acs_privilege_descendant_map;
DROP TABLE IF EXISTS acs_privilege_hierarchy_index CASCADE;

DROP TRIGGER  IF EXISTS acs_priv_hier_ins_del_tr ON acs_privilege_hierarchy;
DROP FUNCTION IF EXISTS acs_priv_hier_ins_del_tr();

DROP TRIGGER  IF EXISTS acs_priv_del_tr ON acs_privileges;
DROP FUNCTION IF EXISTS acs_priv_del_tr();

DROP FUNCTION IF EXISTS priv_recurse_subtree(varbit, varchar);

-- Create "acs_privilege_descendant_map" as view (similar to the
-- Oracle implementation)
-- The clause after the first UNION ALL is just here to return the
-- identity column on the highest hierarchy ("admin, admin"). 
CREATE OR REPLACE VIEW acs_privilege_descendant_map AS
WITH RECURSIVE privilege_desc(parent, child) AS (
   SELECT child_privilege as parent, child_privilege as child FROM acs_privilege_hierarchy
   SELECT privilege as parent, privilege as child FROM
   (SELECT privilege FROM acs_privilege_hierarchy
    SELECT child_privilege FROM acs_privilege_hierarchy) identity
   SELECT h.privilege as parent, pd.child
   FROM acs_privilege_hierarchy h, privilege_desc pd
   WHERE pd.parent = h.child_privilege
) SELECT * FROM privilege_desc;