Today’s tasks have included taking a list of identifiers and names and converting them into a series of SQL statements to create, populate and drop temporary database tables. If I just had one or two lines in the form:
then the easiest way to convert them into:
CREATE TEMPORARY TABLE DataIdentifier ( event_id int(10) UNSIGNED NOT NULL, status varchar(3) NOT NULL, PRIMARY KEY (event_id) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO DataIdentifier SELECT event_id, IF(has_data,'Yes','No') AS status FROM BigDataTable WHERE data_id = 56; DROP TABLE DataIdentifier;
would be by hand. With getting on for a 100 lines, that was not going to fly. In the past, I have tended to pull up a spreadsheet to accomplish such a task (splitting into columns by the comma and then using CONCATENATE statements to create the required expressions). However, that would be a fairly mammoth concatenation and I had an inkling that there would be a better way of doing it without leaving Vim (in which I was editing the list).
It turns out that the trick was to use backreferences. If you break the initial regular expression into a series of chunks, enclosed in (escaped brackets) , you can then refer to the result of each chunk as 1, 2, etc. The following, colon-delimited substitution command can be run on a range to produce the desired result:
s:(w+)(,)(w+):CREATE TEMPORARY TABLE \3 (\r event_id int(10) UNSIGNED NOT NULL,\r status varchar(3) NOT NULL,\r PRIMARY KEY (event_id)\r) ENGINE=MyISAM DEFAULT CHARSET=latin1;\r\rINSERT INTO \3\rSELECT event_id, IF(history_of_condition, 'Yes','No') AS status\rFROM BigDataTable\rWHERE data_id = \1;\r\rDROP TABLE \3;\r:
It is a monster but it does the job beautifully. Now I just need to get all the DROP TABLE statements out of the way to the end so I can do something with all the temporary tables before zapping them.