We use SQL queries to do most of analytical work at our company (PostgreSQL). Currently there are 250+ queries stored in fixed directory structure (there is also a lot of DDL/DML files).
Is there any autodoc tool to create documentation of all those queries and produce some output (e.g. html)?
I know there are some tools that can be used for functions/tables/procedures documentation, but none (as far as I understand it) is suitable for queries, e.g.:
In documentation, I would like to track the following:
- file name
- sql description
- author(s)
- project(s)
- tags
Limitation of fixed directory structure is obvious – there are many times where the query belongs logically to more folders, but you have too choose one. We have tried to use trac wiki, but it is to complicated (you have to copy/paste every version of your query to wiki). On the other hand you can use tags, it is easily searchable and you have versioning. I’ve also tried using some query tools with advanced built-in search (e.g. toad plugin for eclipse), but you have to write pretty complex regexs to get files your are interested in.
This is why I believe that autodoc approach is the only possible way for this task.
Advertisement
Answer
I don’t think such a tool is readily possible for a general use case so your best option is to settle on convention is to write your own. The big thing is that many documentation generators for SQL will try to track dependencies etc. You can do this with tables relatively easily (dynamically discovering foreign keys for example) and in some cases you can do this with functions, but beyond this it becomes really difficult. Note I use postgresql_autodoc for db objects. Being able to track to tables is problematic (what if the query is run with a different search_path? There are many things you can’t know…).
If it were me, I would look at modifying doxygen to track the information you want. It’s further possible to do this only if you have one sql statement per file. For example, it is one thing to document files but very different to document queries.
I would however suggest adopting a discoverable stored procedure interface and putting the comments in SQL COMMENT ON statements. This is what we do and it makes autodocumentation much easier. Then you can use postgresql_autodoc if you want or other tools.