Avoid re-statements in MySQL script

Question

I created an automatic backup using the event scheduler in MySQL, as shown below:

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 many things are repeated, such as DATE_FORMAT( NOW(), '%Y%m%d') and execute and release statements ....

My question is: is there no other way to create this script in fewer lines?

Answer 1

You have a lot of duplicate code. In such cases (in any language) you need to refactor your code.

Extract the duplicate parts into a separate procedure, passing all the necessary parameters and redesigning 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 it easier to read and maintain your code. See how easy it would be to add another table.

After such a reorganization, you can easily manage this path of 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 fill in the table (once):

 CREATE TABLE backup_table ( table_name text); INSERT INTO backup_table values ('BonInterne'), ('LigneBonInterne'), etc; 

then you can just do this:

 select do_backup(table_name) from table_name; 

Adding and removing tables from your backup will be as simple as inserting and deleting rows from a table.

Answer 2

This is what you can do.

First of all , to create a helper function that specified an index, it will return the table name to us

 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 us complete our backup code in a stored procedure so that we do not change the 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 ; 

What is he . Now you just 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 singly charged. Therefore, they do not need to use DELIMITER and BEGIN ... END block