English
Sat, 15/06/2013 - 23:45

Vim regexp: transforming multiple SQL inserts into a big one

If you ever face a very slow MySQL process based on a very long insert file and you want to optimize it by unifying a lot of queries, you can do something like the following.

Imagine you have a lot of these:

INSERT INTO `employee` (`cod_modular`, `sequential_pago`, `a_pater`, `a_mater`, `names`, `type_id_doc`, `document_number`, `cod_dre`, `desc_dre`, `cod_cgef`, `desc_cgef`, `nec`, `level`, `cod_ie`, `name_ei`, `esc_magisterial`, `cod_charge`, `desc_charge`, `situation`, `dias_licence`, `period`, `planner`) VALUES ('1991119073', 283001, 'PHILIPS', 'Castle', 'BETTY', 'DNI', '01712532', '21', 'SAN MARTIN', 'DX', 'CGEF A', '02', '3', 'DX023159', 'SCHOOL A', '0', '4006', 'PROFESSOR', 'Enabled', 0, 'May 2013', 'Active');
INSERT INTO `employee` (`cod_modular`, `sequential_pago`, `a_pater`, `a_mater`, `names`, `type_id_doc`, `document_number`, `cod_dre`, `desc_dre`, `cod_cgef`, `desc_cgef`, `nec`, `level`, `cod_ie`, `name_ei`, `esc_magisterial`, `cod_charge`, `desc_charge`, `situation`, `dias_licence`, `period`, `planner`) VALUES ('1991119084', 300003, 'MONTH', 'Roof', 'ESTHER', 'DNI', '01712482', '6', 'UCAYALI', 'EI', 'CGEF B', '02', '2', 'EI022330', 'SCHOOL B', '1', '4006', 'PROFESSOR', 'Enabled', 0, 'May 2013', 'Active');

And you want it rather like this:

INSERT INTO `employee` 
(`cod_modular`, `sequential_pago`, `a_pater`, `a_mater`, `names`, `type_id_doc`, `document_number`, `cod_dre`, `desc_dre`, `cod_cgef`, `desc_cgef`, `nec`, `level`, `cod_ie`, `name_ei`, `esc_magisterial`, `cod_charge`, `desc_charge`, `situation`, `dias_licence`, `period`, `planner`) 
VALUES 
('1991119073', 283001, 'PHILIPS', 'Castle', 'BETTY', 'DNI', '01712532', '21', 'SAN MARTIN', 'DX', 'CGEF A', '02', '3', 'DX023159', 'SCHOOL A', '0', '4006', 'PROFESSOR', 'Enabled', 0, 'May 2013', 'Active'),
('1991119084', 300003, 'MONTH', 'Roof', 'ESTHER', 'DNI', '01712482', '6', 'UCAYALI', 'EI', 'CGEF B', '02', '2', 'EI022330', 'SCHOOL B', '1', '4006', 'PROFESSOR', 'Enabled', 0, 'May 2013', 'Active');

And obviously you'd prefer to have like 250 of these inserts grouped together...

Well, here are good news: you can do it in a command (although you have to repeat it a few times) with VIM. Let's assume that the fixed query part (the fields names, from the first backtick, to the end of the VALUES word) is actually 305 characters long (you'll have to check that the first time, but the good news is it's fixed). What you want to do is select the current line (do that with the "v" key), then issue a command that will replace the last character of the first line, then all the static SQL string by just a comma, and that you want to do this 250 times... You would then do this :

:'<,'>250s/;nINSERT INTO .{305}/,/

This should work out of the box. If you have another number of chars for the static string, change the 305 correspondingly. If you want to do this more or less than 250 times (more is not advised as this might be a very heavy action for your VIM process), just change 250 to whathever you want.

If you have a few groups like this to do, just press the down key, the : key and the up key to get the same action executed twice in a row.

If you have to repeat that a hundred time, then you might want to look for another selector than '<,'>.