TracMultipleProjects/ComprehensiveSolution: create_multitrac_db.sql

-- clean out some flotsam that may remain from previous runs
-- CAUTION!  THIS WIPES OUT THE POSTGRESQL TRAC DATABASE --
DROP DATABASE trac;
DROP ROLE trac;
DROP ROLE trac_instance;
DROP ROLE trac_instance_master;

-- create a root user for the master Trac instance 
-- that can create new roles
CREATE ROLE trac
NOSUPERUSER NOCREATEDB CREATEROLE LOGIN
PASSWORD :tracpass ;

-- create the database
CREATE DATABASE trac WITH OWNER trac ENCODING 'UTF8';

-- connect to it.
\c trac

-- create a role in which all instance database users will have
-- membership.
CREATE ROLE trac_instance
NOSUPERUSER NOCREATEDB NOCREATEROLE NOLOGIN;

-- create a role for the master trac instance
CREATE ROLE trac_instance_master
NOSUPERUSER LOGIN
PASSWORD :tracpass IN ROLE trac_instance;

GRANT trac_instance_master to trac;

-- The sqlite2pg script will sign in as the user
-- of each trac instance and attempt to create a schema
-- for that user.  Later on these users will not need 
-- the ability to to create schemas, so at that point
-- we can revoke it from the trac_instance role all at
-- once.
GRANT CREATE ON DATABASE trac TO trac_instance;

-- This table is only used to declare the return values of the
-- following function.
CREATE TABLE projects ( project TEXT );

-- Returns a table containing the names of all the schemata used by trac instances.
CREATE OR REPLACE FUNCTION trac_projects()
    RETURNS SETOF projects
    AS
$body$
    SELECT substring(schema_name FROM 'trac_instance_(.*)') AS project 
    FROM information_schema.schemata 
    WHERE position('trac_instance_' in schema_name) = 1;
$body$
    LANGUAGE sql;

-- Needed in order to declare the cross-schema query functions.  My
-- SQL fu is too weak to know why we can't use the regular sql
-- language here; this formula was kindly given to me by Pedro Gimeno
-- Fortea <parigalo-at-formauri.es>.
create language plpgsql;

-- This function declares another function that does queries across
-- trac instances.
--
-- We need to do this dynamically so that trac-admin can update the
-- ticket schema in the future without breaking our multitrac queries.  
-- There's no way to declare the return type without knowing the format
-- of the 'ticket' table rows.
CREATE OR REPLACE FUNCTION declare_multitrac_query()
    RETURNS void
    AS
$declare_multitrac_query$
BEGIN
    EXECUTE $declarations$
        -- declare a table type that's just like the master instance's ticket
        -- table type, but with an extra text column for the schema name.
        CREATE TEMP TABLE fat_ticket ( LIKE trac_instance_master.ticket );
        ALTER TABLE fat_ticket ADD COLUMN project text;

        -- declare a function returning said type that aggregates the results
        -- of doing the query across all projects
        CREATE OR REPLACE FUNCTION multitrac_query(criteria TEXT)
            RETURNS SETOF fat_ticket
            AS
        $multitrac_query$
        DECLARE
           p RECORD;
           result RECORD;
        BEGIN
           FOR p IN SELECT * FROM trac_projects() LOOP
             FOR result IN EXECUTE 'SELECT '
                 || 'trac_instance_' || p.project || '.ticket.*' 
                 || ',''' || p.project || '''::text AS project'
                 || ' FROM trac_instance_' || p.project || '.ticket'
                 || ' WHERE ' || criteria
                 LOOP
               RETURN NEXT result;
             END LOOP;
           END LOOP;
           RETURN;
        END;
        $multitrac_query$
           LANGUAGE plpgsql;
    $declarations$ ;

END;
$declare_multitrac_query$
    LANGUAGE plpgsql;