Ukladanie komentárov v stored procedúrach

Píšete stored procedúru pre MySQL na spracovanie nejakých dát. Používate na to svoj obľúbený editor a túto procedúru udržiavate ako súbor na disku. Commitnete svoj výtvor do verzionovacieho nástroja a naimportujete svoju procedúru do MySQL:

DELIMITER ;;
DROP PROCEDURE IF EXISTS `calc_daily_counters` ;;
CREATE PROCEDURE `calc_daily_counters` ()
BEGIN
    -- Declare '_val' variables to read in each record from the cursor
    DECLARE tablename_val VARCHAR(32);
    DECLARE paramname_val VARCHAR(32);

    -- Declare variables used just for cursor and loop control
    DECLARE no_more_rows BOOLEAN;
    DECLARE loop_cntr INT DEFAULT 0;
    DECLARE num_rows INT DEFAULT 0;

    -- Declare the cursor
    DECLARE daily_counters_cur CURSOR FOR
    SELECT tablename, paramname FROM conf_daily_counters;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- ERROR
        ROLLBACK;
    END;

    DECLARE EXIT HANDLER FOR SQLWARNING
    BEGIN
        -- WARNING
        ROLLBACK;
    END;

    -- Declare 'handlers' for exceptions
    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

    OPEN daily_counters_cur;
    SELECT FOUND_ROWS() INTO num_rows;
    the_loop: LOOP

        FETCH  daily_counters_cur
        INTO   tablename_val, paramname_val;

        -- break out of the loop if
        -- 1) there were no records, or
              -- 2) we've processed them all
        IF no_more_rows THEN
            CLOSE daily_counters_cur;
            LEAVE the_loop;
        END IF;

        SET @sql = CONCAT('INSERT INTO trend_daily_counters (date, value, counter) ',
            'SELECT CURDATE(), "', paramname_val, '", COUNT(*) FROM `', tablename_val, '`;');
        SELECT @sql;
        PREPARE STMT FROM @sql;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;

        -- count the number of times looped
        SET loop_cntr = loop_cntr + 1;

    END LOOP the_loop;

END ;;
DELIMITER ;

$ mysql vasadatabaza < calc_daily_counters.sql

A tu je zrada! Štandardný MySQL klient zahodí pri importe do DB všetky vaše komentáre. Existuje ale riešenie, ako si tieto komentáre zachovať. Vytvorte pre MySQL klienta nasledovný konfiguračný súbor ~/.my.cnf:

[client]
default-character-set = utf8
user = vasemeno
database = vasadatabaza
password = ake_mate_radi_heslo?
[mysql]
comments = 1
prompt=(\\u@\\h) [\\d]>\\_

A takto si overíme, že všetkých našich desať komentárov sa zachovalo pri importe do databázy:

$ grep -e '^\s*--' calc_daily_counters.sql | wc -l 
10
$ mysql --comments=0 < calc_daily_counters.sql 
$ echo 'SHOW CREATE PROCEDURE calc_daily_counters \\G' | mysql | grep -e '^\s*--' | wc -l
0
$ mysql --comments=1 < calc_daily_counters.sql                                          
$ echo 'SHOW CREATE PROCEDURE calc_daily_counters \\G' | mysql | grep -e '^\s*--' | wc -l
10
$ mysql < calc_daily_counters.sql                                           
$ echo 'SHOW CREATE PROCEDURE calc_daily_counters \\G' | mysql | grep -e '^\s*--' | wc -l
10