SQL: good comments conventions

July 1, 2010

I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code.

I list four major places where SQL comments are helpful. I'll use the sakila database. It is originally scarcely commented; I'll present it now enhanced with comments, to illustrate.

Table definitions

The CREATE TABLE statement allows for a comment, intended to describe the nature of the table:

CREATE TABLE `film_text` (
 `film_id` smallint(6) NOT NULL,
 `title` varchar(255) NOT NULL,
 `description` text,
 PRIMARY KEY (`film_id`),
 FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Reflection of `film`, used for FULLTEXT search.'

It's too bad the comment's max length is 60 characters, though. However, it's a very powerful field.

Column definitions

One may comment particular columns:

CREATE TABLE `film` (
 `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 `description` text,
 `release_year` year(4) DEFAULT NULL,
 `language_id` tinyint(3) unsigned NOT NULL COMMENT 'Soundtrack spoken language',
 `original_language_id` tinyint(3) unsigned DEFAULT NULL COMMENT 'Filmed spoken language',
 `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
 `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
 `length` smallint(5) unsigned DEFAULT NULL,
 `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  ...
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

Stored routines definitions

Here's an original sakila procedure, untouched. It is already commented:

CREATE DEFINER=`root`@`localhost` PROCEDURE `rewards_report`(
 IN min_monthly_purchases TINYINT UNSIGNED
 , IN min_dollar_amount_purchased DECIMAL(10,2) UNSIGNED
 , OUT count_rewardees INT
)
 READS SQL DATA
 COMMENT 'Provides a customizable report on best customers'
BEGIN

 DECLARE last_month_start DATE;
 DECLARE last_month_end DATE;
 ...

SQL queries

Last but not least, while not part of the schema, SQL queries define the use of the schema. That is, the schema exists for the sole reason of being able to query it.

Where did that query come from? Which piece of code issued it? Why? What's its purpose?

Looking at the PROCESSLIST, the slow log, etc., it is easier when the queries are commented:

SELECT
 /* List film details along with participating actors */
 /* Issued by analytics module */
 film.*,
 COUNT(*) AS count_actors,
 GROUP_CONCAT(CONCAT(actor.first_name, ' ', actor.last_name))
FROM
 film
 JOIN film_actor USING(film_id)
 JOIN actor USING(actor_id)
GROUP BY film.film_id;

Conclusion

Source code commenting is an important practice, and usually watched out for. SQL & table definitions commenting are often scarce or non-existent. I urge DBAs to adopt a comments coding convention for SQL, and apply it whenever they can.

tags: , ,
posted in Development, MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

5 Comments to "SQL: good comments conventions"

  1. Roland Bouman wrote:

    It's funny you should mention sakila and comments....way, way, waaaay back when Mike Hillyer created the database, I proposed to put all documentation in comments, and use an XML export in combination with an XSLT stylesheet to generate the schema documentation into a HTML format.

    Alas, the comment fields are way to small to seriously use them for this purpose. Anyway...you reminded me of an inspiring past. Thanks!

  2. Daniƫl van Eeden wrote:

    It's too bad that it's not possible to add a comment to a CREATE DATABASE

  3. Shantanu Oak wrote:

    Apart from comments I do also use different cases like SeLeCt or selecT. Then I use 'grep' command to find the query from log file.

  4. shlomi wrote:

    @Roland,
    Glad I could bring up the past :)

    @Shantanu,
    sounds painful!

  5. Comment your MySQL schema « Electricmonk.nl weblog wrote:

    [...] SQL: good comments conventions [...]

Leave Your Comment

 
Powered by Wordpress and MySQL. Theme by openark.org