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

Question

I want to write documentation for 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 are used (which views and which functions).

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

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

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

and a brief description of the fields.

Soon I want to see what a specific object is using and which objects use a specific object.

I thought to use a text file for each object as well. I could list the objects that use the current, and which of them are used by the current.

<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 my documentation will sync quickly. Imagine adding a function f_cccc that calls f_aaaa and f_bbbb. I will need to change the doc to f_aaaa and f_bbbb

I know that UML describes relationships about objects (does it, right?). But I want something simple, and I don’t want to follow the 75-hour training ... And I'm not sure that you can have a “link” between objects and a function as I want.

Do you have something to offer?

I am using PostgreSQL for Linux (Fedora). If you offer a tool that does this, it should be compatible with PostgreSQL :-)

For my code documentation I use Doxygen.

thanks

Answer 1

Finally, I create a huge hmtl file.

The file contains many bindings and easily moves to various objects.

This is a lot of work, but that was exactly what I want :-)

Answer 2

You can get some of this information by requesting internal database information. If something depends on another, it suggests that he uses it. Here is an example query to give you an idea of ​​how to get through two related structures:

 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 is contained in the system catalog documentation.

Answer 3

I am not documenting to see the dependencies. The documentation is automatically out of date.

I use a tool for this. I am currently using ApexSQL products, but I have used Redgate in the past.

Answer 4

Just draw an EAR chart. A diagram of relationships between objects.

This lesson should give you a good idea about this. http://www.scribd.com/doc/7500847/entity-relationship-diagram

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

Edit:

Let's say you have CARB CARB, then you draw a box of CAR:

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

Thelast 3 - your features.

Answer 5

consider using naming conventions to enhance the dependencies of SQL objects:

Table 1

Table2

vw_table1_byField1

vw_table1_byField1_table2

vw_table2

fn_table1

fn_table1_table2