--
-- $Id: pg_advisor.sql,v 1.13 2004/03/19 14:55:39 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;

--
-- missing array_index function
--

CREATE OR REPLACE FUNCTION array_index(anyarray, anyelement) 
RETURNS INTEGER AS '
DECLARE
  tab ALIAS FOR $1;
  item ALIAS FOR $2;
  i INTEGER;
BEGIN
  i := 1;
  LOOP
    IF i > array_upper(tab, 1) THEN
	RETURN NULL;
    END IF;
    IF tab[i] = item THEN
	RETURN i;
    END IF;
    i := i+1;
  END LOOP;
END;'
LANGUAGE plpgsql;

COMMENT ON FUNCTION array_index(anyarray, anyelement)
IS 'missing array_index function... should be already there!';

--
-- internal foreign key constraint...
--
CREATE VIEW in_foreign_key_contraint AS
SELECT
  -- constraint
  c.oid AS constraint, 
  -- referencing table
  c.conrelid AS crel, 
  cca.attname AS ccol,
  cca.atttypid AS ctyp,
  cca.atttypmod AS cmod,
  cca.attlen AS clen,
  -- referenced table, foreign part
  c.confrelid AS frel, 
  fka.attname AS fcol, 
  fka.atttypid AS ftyp, 
  fka.atttypmod AS fmod,
  fka.attlen AS flen,
  array_index(c.confkey, fka.attnum) AS component
FROM pg_constraint AS c
     JOIN pg_attribute AS cca ON (c.conrelid=cca.attrelid)
     JOIN pg_attribute AS fka ON (c.confrelid=fka.attrelid)
WHERE
  -- foreign key constraint
  c.contype='f'
  -- column attribute in constraint
  AND cca.attnum = ANY (c.conkey)
  -- foreign key attribute
  AND fka.attnum = ANY (c.confkey)
  -- matching constraints
  AND array_index(c.confkey, fka.attnum)=array_index(c.conkey, cca.attnum)
;

--
-- foreign keys which do not exactly match their target key types?
--

CREATE VIEW da_foreign_key_type_dont_match AS
SELECT 
  nc.nspname AS Schema,
  cc.relname AS Name,
  fkc.ccol AS AttName,
  format_type(fkc.ctyp, fkc.cmod) AS ColumnType,
  nf.nspname AS FSchema,
  cf.relname AS FName,
  fkc.fcol AS FAttName,
  format_type(fkc.ftyp, fkc.fmod) AS ForeignType,
  fkc.component AS Component
FROM in_foreign_key_contraint AS fkc 
  JOIN pg_class AS cc ON (fkc.crel=cc.oid)
  JOIN pg_namespace AS nc ON (cc.relnamespace=nc.oid)
  --JOIN pg_attribute AS ac ON (fkc.ccol=ac.oid)
  JOIN pg_class AS cf ON (fkc.frel=cf.oid)
  JOIN pg_namespace AS nf ON (cf.relnamespace=nf.oid)
  --JOIN pg_attribute AS af ON (fkc.fcol=af.oid)
WHERE
  -- no comments about system catalogs.
  nc.nspname NOT IN('pg_catalog','pg_toast','information_schema','xpg_advisor')
  AND 
  nf.nspname NOT IN('pg_catalog','pg_toast','information_schema','xpg_advisor')
  -- only tables (redundant?)
  AND cc.relkind='r' AND cf.relkind='r'
  -- non matching type
  AND fkc.ctyp!=fkc.ftyp
ORDER BY Schema ASC, Name ASC, FSchema ASC, FName ASC, Component ASC;

COMMENT ON VIEW da_foreign_key_type_dont_match
IS 'non matching foreing key component, maybe a bad design';

-- SELECT * FROM da_foreign_key_type_dont_match;

--
-- others?
--
-- foreign keys which do match their target types but with different sizes?
-- tables with large primary keys (such as TEXT, VARCHAR(64)...)?

--
-- how to SELECT COUNT(*) FROM [the view of which I have the oid]?
-- here is the missing function, that may be somewhere I guess.

CREATE OR REPLACE FUNCTION count_tuples(OID) 
RETURNS INTEGER AS '
DECLARE
  oid ALIAS FOR $1;
  tablename TEXT;
  schemaname TEXT;
  res RECORD;
BEGIN
  -- get 
  SELECT INTO schemaname, tablename   n.nspname, c.relname
  FROM pg_class AS c JOIN pg_namespace AS n ON (c.relnamespace=n.oid)
  WHERE c.oid=oid;
  IF NOT FOUND THEN 
    RETURN NULL;
  END IF;
  FOR res IN
    EXECUTE \'SELECT COUNT(*) AS n FROM \' || schemaname || \'.\' || tablename
  LOOP
    RETURN res.n;
  END LOOP;
  RETURN NULL;
END;'
LANGUAGE 'plpgsql';

COMMENT ON FUNCTION count_tuples(OID) 
IS 'count number of rows in table or view, given its oid';

-- count summary of design advices?
CREATE VIEW design_advices_summary AS
SELECT
  count_tuples(c.oid) AS Count,
  c.relname AS Name, 
  d.description AS Description
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_%'
ORDER BY Name ASC;

-- 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?