RFC: Moving menu.ini information into the database

["Chris Travers" <..hidden..>]

Hi all;

As part of the refactoring effort, I looking at moving the menu
handling into the database.  This will allow add-on modules to add
menu items, allow system administrators to refactor menus using SQL
statements and the like.

Unfortunately, SQL is pretty bad at handling arbitrary-depth menus
(esp. without WITH RECURSIVE or CONNECT BY).  Fortunately, the amount
of data is likely to be pretty small, so it is not too bad using
stored procedures.

My current plan is to do something like as follows.  Note that this is
a rough draft and has not been through much testing.  I am looking for
feedback on general approach more than nitpicks about minor details
(though suggestions along these lines are welcome too).

My current approach is to store the data physically in a self-joined
table and then "render" a template-friendly view of the menu data
using the menu_generate() function.  Note that this function just
calls a recursive menu generate the menu list.

The menu list consists simply of a set of attributes: id (being the
node id), ordered path, label, and type (1 for submenu, 2 for
end-node).

The SQL code is:

CREATE TABLE menu_type (
   label text primary key,
   id serial unique
);

INSERT INTO menu_type (label) values ('submenu');
INSERT INTO menu_type (label) values ('end node');

CREATE TABLE menu_node (
   id serial PRIMARY KEY,
   node_type int references menu_type (id),
   label varchar not null,
   parent int references menu_node(id),
   position int not null,
   UNIQUE (parent, position)
);

INSERT INTO menu_node (id, node_type, label, parent, position)
VALUES (0, 1, 'Top-level', NULL, 0)

CREATE TABLE menu_attributes (
   id serial primary key,
   node_id int references menu_node (id),
   attribute varchar not null,
   value varchar not null
);

CREATE TYPE menu_item (
   id int,
   label varchar,
   position varchar,
   item_type int
);

CREATE TABLE menu_acls (
   id serial primary key,
   role_name varchar,
   acl_type varchar check in ('allow', 'deny'),
   node_id int references menu_node(id)
);

CREATE FUNCTION generate_menu(node_id int, position varchar)
RETURNS SETOF menu_item AS
$$
DECLARE
   item menu_item;
   node menu_node%ROWTYPE;
BEGIN
   FOR node IN
       SELECT * FROM menu_node WHERE node_id = id ORDER BY position
   DO LOOP
       IF position = '' THEN
           item.position = node.position::varchar;
       ELSE
           item.position = position || ',' || node.position
       END IF;
       item.id = node.id;
       item.label = node.label;
       return next item;
       IF node.node_type = 1 THEN
           FOR item SELECT menu_generate(item.id, item.position)
           DO LOOP
              return next item;
           END LOOP;
       END IF;
   END LOOP;
END;
$$ language plpgsql;

CREATE FUNCTION menu_generate() RETURNS SETOF menu_item AS
$$
BEGIN
   SELECT menu_generate(0, '');
END;
$$ language plpgsql;