--
-- $Id: pg_advisor.sql,v 1.3 2004/03/19 10:41:47 coelho Exp $
--
-- pg_advisor maybe future system schema?
--
-- at the time it is xpg_advisor as pg_ is reserved.
--

DROP SCHEMA xpg_advisor CASCADE;

CREATE SCHEMA xpg_advisor;

COMMENT ON SCHEMA xpg_advisor 
IS 'various advices about database design or performance' ;

SET search_path TO xpg_advisor,pg_catalog;

--
-- DESIGN ADVICES (da_*)
--

--
-- tables without primary keys
--

CREATE VIEW da_tables_without_primary_key AS 
SELECT n.nspname AS Schema, c.relname AS Name
FROM pg_class AS c 
  JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
WHERE
  -- no comments about system catalogs.
  n.nspname NOT IN ('pg_catalog','pg_toast','information_schema','xpg_advisor')
  -- only tables
  AND c.relkind = 'r' 
  -- no primary key
  AND NOT c.relhaspkey
ORDER BY Schema ASC, Name ASC;

COMMENT ON VIEW da_tables_without_primary_key
IS 'it is better to have a primary key on your tables';

-- SELECT * FROM da_tables_without_primary_key;

--
-- tables with composite primary keys?
-- 

CREATE VIEW da_tables_with_composite_primary_key AS
SELECT n.nspname AS Schema, c.relname AS Name
FROM pg_class AS c 
  JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
  JOIN pg_constraint AS o ON (o.conrelid=c.oid)
WHERE
  -- no comments about system catalogs.
  n.nspname NOT IN ('pg_catalog','pg_toast','information_schema','xpg_advisor')
  -- only tables
  AND c.relkind = 'r' 
  -- with a primary key
  AND c.relhaspkey
  -- the primary key constraint
  AND contype = 'p'
  -- more than 1 element in conkey
  AND array_upper(o.conkey, 1)>1
ORDER BY Schema ASC, Name ASC;

COMMENT ON VIEW da_tables_with_composite_primary_key
IS 'it may be considered a better design to have simple primary keys';

-- SELECT * FROM da_tables_with_composite_primary_key;

--
-- other ideas for later:
--
-- foreign keys which do not exactly match their target key types?
-- foreign keys which do match their target types but with different sizes?
-- tables with large primary keys (such as TEXT, VARCHAR(64)...)?
--


-- count summary of design advices?
-- how to SELECT COUNT(*) FROM [the view of which I have the oid]?
-- Do I need a function that count tuples in a table given its oid?
-- CREATE FUNCTION pg_count_tuples(INTEGER oid) RETURNS INTEGER...
CREATE VIEW design_advices_summary AS
SELECT c.relname AS Name, d.description AS Description
-- , pg_count_tuples(c.oid) AS Count
FROM pg_class AS c
  JOIN pg_description AS d ON (d.objoid=c.oid)
  JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
WHERE
  -- xpg_advisor views
  n.nspname = 'xpg_advisor'
  -- only design advices
  AND c.relname LIKE 'da_%';

-- SELECT * FROM design_advices_summary;

--
-- PERFORMANCE ADVICES (pa_*)
--
-- no usable primary key index for foreign key referencial integrity checks?
-- no usable index for foreigh key on deletes?
--

-- count summary of performance advices?