CODEWITHSUNDEEP
mysql
Aimad Majdou
Aimad Majdou

Reputation: 583

Avoid repeated statements in MySQL script

I've created an automating backup using Event scheduler in MySQL, as the following :

DELIMITER $$
CREATE DEFINER=`root`@`localhost` EVENT `Backup` 
ON SCHEDULE EVERY 1 WEEK 
STARTS '2013-06-14 18:19:02' ON COMPLETION NOT PRESERVE ENABLE 
DO 
BEGIN
SET @sql_text1 = CONCAT("SELECT * FROM BonInterne INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "BonInterne.csv'" ); 
SET @sql_text2 = CONCAT("SELECT * FROM LigneBonInterne INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "LigneBonInterne.csv'" ); 
SET @sql_text3 = CONCAT("SELECT * FROM BonExterne INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "BonExterne.csv'" ); 
SET @sql_text4 = CONCAT("SELECT * FROM LigneBonExterne INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "LigneBonExterne.csv'" ); 
SET @sql_text5 = CONCAT("SELECT * FROM BonEntrée INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "BonEntrée.csv'" ); 
SET @sql_text6 = CONCAT("SELECT * FROM LigneBonEntrée INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "LigneBonEntrée.csv'" ); 
SET @sql_text7 = CONCAT("SELECT * FROM Inventaire INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "Inventaire.csv'" ); 
SET @sql_text8 = CONCAT("SELECT * FROM LigneInterventaire INTO OUTFILE '/home/aimad/GestionStock/" , DATE_FORMAT( NOW(), '%Y%m%d') , "LigneInventaire.csv'" ); 
PREPARE s1 FROM @sql_text;
PREPARE s2 FROM @sql_text;
PREPARE s3 FROM @sql_text;
PREPARE s4 FROM @sql_text;
PREPARE s5 FROM @sql_text;
PREPARE s6 FROM @sql_text;
PREPARE s7 FROM @sql_text;
PREPARE s8 FROM @sql_text;
EXECUTE s1;
EXECUTE s2; 
EXECUTE s3; 
EXECUTE s4; 
EXECUTE s5; 
EXECUTE s6; 
EXECUTE s7; 
EXECUTE s8;  
DEALLOCATE PREPARE s1;
DEALLOCATE PREPARE s2;
DEALLOCATE PREPARE s3;
DEALLOCATE PREPARE s4;
DEALLOCATE PREPARE s5;
DEALLOCATE PREPARE s6;
DEALLOCATE PREPARE s7;
DEALLOCATE PREPARE s8;
END $$
DELIMITER ;

The problem is that there are many things that repeat, such as DATE_FORMAT( NOW(), '%Y%m%d') and the execute statements and deallocate statements ....

My question is, isn't there any other way to create this script in fewer lines ?

Upvotes: 4

Views: 309

Answers (2)

Bohemian
Bohemian

Reputation: 424973

You have a lot of repeated code. In cases like this (in any language), you need to refactor your code.

Extract the repeated parts out into a separate procedure, passing whatever parameters are necessary, and recode the main procedure to (re)use the extracted code.

DELIMITER $$

CREATE PROCEDURE DO_BACKUP(table_name text)
BEGIN
  SET @sql = CONCAT("SELECT * FROM ", table_name,
    " INTO OUTFILE '/home/aimad/GestionStock/" ,
    DATE_FORMAT( NOW(), '%Y%m%d') , table_name, ".csv'" );
  PREPARE s1 FROM @sql;
  EXECUTE s1;  
  DEALLOCATE PREPARE s1;
END$$

CREATE DEFINER=`root`@`localhost` EVENT `Backup` 
ON SCHEDULE EVERY 1 WEEK 
STARTS '2013-06-14 18:19:02' ON COMPLETION NOT PRESERVE ENABLE 
DO 
BEGIN
  CALL DO_BACKUP('BonInterne');
  CALL DO_BACKUP('LigneBonInterne');
  CALL DO_BACKUP('BonExterne');
  CALL DO_BACKUP('LigneBonExterne');
  CALL DO_BACKUP('BonEntrée');
  CALL DO_BACKUP('LigneBonEntrée');
  CALL DO_BACKUP('Inventaire');
  CALL DO_BACKUP('LigneInterventaire');
END $$

DELIMITER ;

It will also make your code easier to read and maintain. Look how easy it would be to add another table.

Once refactored like this, you could quite easily drive this via values stored in a table. Especially if you changed the procedure to a function:

CREATE FUNCTION DO_BACKUP(table_name text)
RETURNS text
BEGIN
  -- as above
  RETURN NULL;
END$$

then create and populate a table (one time):

CREATE TABLE backup_table (
  table_name text);

INSERT INTO backup_table values ('BonInterne'), ('LigneBonInterne'), etc;

then you can simply do this:

select do_backup(table_name)
from table_name;

Adding and removing tables from your backup would be as simple as inserting and deleting rows from the table.

Upvotes: 2

peterm
peterm

Reputation: 92785

That is what you can do.

First of all let's create a helper function that given an index will return us a table name 

CREATE FUNCTION tname_from_idx(INT idx)
RETURNS VARCHAR(64)
RETURN CASE idx 
         WHEN 1 THEN 'BonInterne'
         WHEN 2 THEN 'LigneBonInterne'
         WHEN 3 THEN 'BonExterne'
         WHEN 4 THEN 'LigneBonExterne'
         WHEN 5 THEN 'BonEntrée'
         WHEN 6 THEN 'LigneBonEntrée'
         WHEN 7 THEN 'Inventaire'
         WHEN 8 THEN 'LigneInterventaire'
         ELSE 'n/a'
       END;

Secondly let's wrap up our backup code into a stored procedure so that we don't change an event every time we want to make changes to the backup logic

DELIMITER $$
CREATE PROCEDURE do_backup()
BEGIN
    DECLARE i     INT         DEFAULT 1;
    DECLARE tname VARCHAR(64) DEFAULT 'n/a';

    SET tname = tname_from_idx(i);
    WHILE  tname <> 'n/a' DO
      SET @sql = CONCAT('SELECT * FROM ', tname, ' INTO OUTFILE \'/home/aimad/GestionStock/', DATE_FORMAT(CURDATE(), '%Y%m%d'), tname, '.csv\'');
      PREPARE stmt FROM @sql; 
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
      SET i = i + 1;
      SET tname = tname_from_idx(i);
    END WHILE;
END$$
DELIMITER ;

That's it. Now you merely call this stored procedure from your event.

CREATE DEFINER=`root`@`localhost` EVENT `Backup` 
ON SCHEDULE EVERY 1 WEEK STARTS CURDATE() + INTERVAL 22 HOUR 
DO CALL do_backup();

Note: Our function and event are one-statement ones. Therefore they need no use of DELIMITER and a BEGIN ... END block

Upvotes: 1

Related Questions