3

I'm working with a new set of tables and an increasing list of stored procedures.

Is there a way to generate documentation out of the comments on tables and stored procedures, like autodoc or doxygen?

Note: I'm using mysql.

closed as off-topic by Paul White, RolandoMySQLDBA, Colin 't Hart, Philᵀᴹ, RLF May 1 '15 at 12:10

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Paul White, RolandoMySQLDBA, Colin 't Hart, Philᵀᴹ, RLF
If this question can be reworded to fit the rules in the help center, please edit the question.

1

I can't for the life of me understand why this question received a flag for being off-topic. I think it is interesting and very relevant for the working DBA.

Unfortunately, as far as I can ascertain, it is impossible to do this automatically. You can run two commands

SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb' AND ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME = "procedurename";

and

SHOW PROCEDURE STATUS; -- will give you all procedures available.

SHOW CREATE PROCEDURE procedure_name; -- will give you details about a given procedure.

A good discussion of the topic is available here which shows the problems associated with the various commands.

Extending slightly the example given for the sample alluser() procedure as shown in the link above. I added comments with # and --.

DELIMITER //

CREATE PROCEDURE alluser()

# a proc to show all users
BEGIN
   SELECT *  -- selecting from the users' table
   FROM users;
END //
DELIMITER ;

What I found was that the comments are NOT returned using either SHOW PROCEDURE STATUS or SHOW CREATE PROCEDURE alluser; NOR the SQL statement above returns the comments.

[EDIT - I have further found that comments are not returned with SELECT * FROM Information_Schema.Routines.

I searched the system and found the field ROUTINE_COMMENT in INFORMATION_SCHEMA.ROUTINES - but it didn't return the comments either. What this field is for, I don't quite know - maybe it's not implemented yet?

I would respectfully suggest your best bet is to document as you go - put "full documentation for the procedure" as part of your testing and UAT framework.

  • Thanks, this gave me a good starting point. I already knew of the COMMENT keyworkd in the characteristics section of [CREATE PROCEDURE] (dev.mysql.com/doc/refman/5.0/en/create-procedure.html]) But I didn't know where it went. – Noe Nieto Apr 30 '15 at 18:23
  • In response to your opening question, it is possible to read the original question as looking for an external tool recommendation. – Paul White May 1 '15 at 0:35
1

To my knowledge, there are no tools like doxygen that can make doc from parsing SQL annotated code.

About keeping comments, note that comments in a stored procedure can be saved with the procedure code.

When a CREATE PROCEDURE statement is sent from the mysql command line client to the server, make sure the mysql client is using the -comments option.

See http://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html#option_mysql_comments

That being said, SQL under source control is probably better compared to extracting the stored procedures code back from the database.

0

Eventually I found a starting point thanks to Verace. But comments can be long, therefore i post this as an answer:

DROP PROCEDURE IF EXISTS foobar;

DELIMITER //

CREATE PROCEDURE foobar()

COMMENT '
This procedure does some useful stuff
'
BEGIN
   -- Do something useful here
   SELECT 1;
END //
DELIMITER ;

SELECT ROUTINE_COMMENT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'foobar';
SHOW PROCEDURE STATUS WHERE Name='foobar';
SHOW CREATE PROCEDURE foobar;

I cannot retrieve inline comments, but I can retrieve the comments from the characteristics section.

  • Sorry about the bum steer with my answer - what I wrote is true, I just never bother with the COMMENT keyword since we use a version control system (Mercurial) to hold our database schema so the comments are readily obtainable, and more often than not, putting them at the beginning (won't work after END either) isn't particularly useful. Still can't understand why you have 2 close votes for what I think is a very useful question. I'm giving it a +1! :-) Maybe you should use version control also? – Vérace Apr 30 '15 at 18:42
  • Yes, I'm storing my stored procedures in a VCS. The sql files might need to be parsed in order to do the magic. Storing schema, SPs, functions and so on a VCS is the way to go. – Noe Nieto Apr 30 '15 at 19:04
  • Just a final thought. If (and I mean IF) you are going to parse these, I think you'd be better off using Python (or Perl <Ugh...>) because IMHO, you'll have to be going from the MySQL server to the file system and back. Oracle's PL/SQL might be good for it - or PostgreSQL - but I don't think MySQL's SP language is up to it. – Vérace Apr 30 '15 at 19:34
  • Yes, python-sqlparse looks promising. github.com/andialbrecht/sqlparse – Noe Nieto Apr 30 '15 at 23:08
  • Buena suerte amigo! – Vérace Apr 30 '15 at 23:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.