How do you document your database code to see dependencies between database objects?

Question

I want to write documentation on my pet project.

I have 30 tables and almost 50 views and about 30 functions (stored procedures) in my PostgreSQL database.

I would like to see where tables ( which views and which functions ) are used.

I would like to see where views ( which views and which functions ) are used.

I would like to see if a function is used by another function.

I would like also to write a description of each objects ( tables, views and functions )

and a short description of fields.

Shortly, I want to be able to see what use a specific object and which objects use a specific object.

I thought to use a text file and for every object. I might list objects that use the current one and which ones are used by the current one.

<pre>
Table aaaa
A short description
used by : view v_aaaa

id  int     primary key
name  varchar(30)   name of a...

================================
view v_aaaa
A short description

list of fields...

used by function f_aaaa
Depends on  table aaaa



==============================

function f_aaaa
A short description
Parameters ( description, IN/OUT )
Depends on view v_aaaa
           function fbbbb 

==============================

function f_bbbb
A short description
Parameters
Called by function f_aaaa

I'm afraid that my documentation will be unsynchronize quickly Imagine I add a function f_cccc that calls f_aaaa and f_bbbb. I'll have to modify doc on f_aaaa and f_bbbb

I know that UML describes relations about entities (It does that, right ???). But I want something simple and I don't want to follow a 75 hours training... And I'm not sure that you can have a "link" between entities and function as I want.

Do you have something to suggest me ?

I use PostgreSQL on Linux (Fedora). If you propose a tool that do this, it must be compatible with PostgreSQL :-)

For my code documentation, I use Doxygen.

Thank you

score 4 · Answer 1 · answered Jul 22 '09 at 21:15

You can actually collect some of this information by querying the database's internal "depends" information. If something depends on another, that suggests it uses it. Here's a sample query to give you an idea how to traverse the two structures involved:

SELECT
  c1.oid as relid,
  n1.nspname || '.' || c1.relname as relation,
  c1.relkind,
  c2.oid as relid,
  n2.nspname || '.' || c2.relname as dependency,
  c2.relkind
FROM 
  pg_depend d,
  pg_class c1,
  pg_namespace n1,
  pg_class c2,
  pg_namespace n2
WHERE 
  d.objid = c1.oid AND
  c1.relnamespace = n1.oid AND 
  n1.nspname NOT IN('information_schema', 'pg_catalog') AND
  n1.nspname !~ '^pg_toast' AND
  d.refobjid = c2.oid AND
  c2.relnamespace = n2.oid AND 
  n2.nspname NOT IN('information_schema', 'pg_catalog') AND
  n2.nspname !~ '^pg_toast' AND
  c1.oid != c2.oid
GROUP BY n1.nspname,c1.relname,c1.oid,c1.relkind,
  n2.nspname,c2.relname,c2.oid,c2.relkind
ORDER BY n1.nspname,c1.relname;

Information about all these internal bits can be found in the system catalog documentation.

score 2 · Answer 2 · answered Jul 22 '09 at 17:42

2

I don't document in order to see dependencies. Documentation is automatically out of date.

I use a tool for that. At present I use the products from ApexSQL, but I've used the Redgate tools in the past.

answered Jul 22 '09 at 17:42

John Saunders

160,644
26
247
397

It's a little bit expensive for a pet projects :-) There's no version for PostgreSQL :-( – Luc M Jul 22 '09 at 20:09

score 0 · Answer 3 · answered Jul 22 '09 at 15:22

0

Just draw a EAR diagram. Entity-Relation diagram.

This tutorial should give you a good understanding of it. http://www.scribd.com/doc/7500847/entity-relationship-diagram

And you have this one http://www.getahead-direct.com/gwentrel.htm

Edit:

Lets say you have a tabe CAR, then you draw a box CAR:

CAR
----------
id (int)
name (vchar)
numbSeats (int)
----------
GetCar
SetCar
DeleteCar

Thelast 3 are your functions.

answered Jul 22 '09 at 15:22

Steven

19,224
47
152
257

Where may I see that GetCar is called by GetFastestCar ? – Luc M Jul 22 '09 at 15:50
You don't. You can have a class called CAR where GetCar and GetFastesCar would be functions, each calling a DB class to retrieve information. Ofcourse, GetFAstesCar could be a child of GetCar, but then we are talking OO and inheritance. If you want to map classes, then UML is what you need to learn. – Steven Jul 22 '09 at 22:21

score 0 · Answer 4 · answered Aug 19 '09 at 15:39

0

consider using a naming convention to reinforce SQL object dependencies:

Table1

Table2

vw_table1_byField1

vw_table1_byField1_table2

vw_table2

fn_table1

fn_table1_table2

answered Aug 19 '09 at 15:39

Beth

9,531
1
24
43

3

IMHO it's a real bad idea. Function names must express what they do and not what the function use – Luc M Aug 20 '09 at 13:22
Yeah, I can see that, too. He's asking about documentation, and you can use the naming convention for both what it does & what it's dependent on. – Beth Aug 20 '09 at 14:42

score -1 · Accepted Answer · answered Sep 17 '09 at 20:51

-1

I finally build a huge hmtl file.

File contains alot of anchors and it's easy to navigate to different objects.

It's a lot of work but it was exactly what I want :-)

answered Sep 17 '09 at 20:51

Luc M

16,630
26
74
89

1

Manually building that must have been painful way back in '09 ! In a sense this is what javadoc, sphynx and other documenting tools do, except they parse the code, and read comments defined in a specific format and generate all the html for you. It's a pity there isn't something like this for postgresql but I guess you could do it inside one of those other languages. I came here looking for such a solution but haven't seen one. – Davos Nov 07 '17 at 01:33

How do you document your database code to see dependencies between database objects?

5 Answers5

Linked