Using awk or sed add a line based on a pattern match

Question

A have a file containing SQL migration commands. For example

// admin_setting
$this->createTable('{{%admin_setting}}', [
    'setting_id' => Schema::TYPE_INTEGER . "(11)  NOT NULL AUTO_INCREMENT",
    'short_description' => Schema::TYPE_STRING . "(255) NOT NULL",
    'value' => Schema::TYPE_TEXT . " NULL",
    'PRIMARY KEY (setting_id)',
], $this->tableOptions);

// authorization
$this->createTable('{{%authorization}}', [
    'authorization_id' => Schema::TYPE_INTEGER . "(11)  NOT NULL AUTO_INCREMENT",
    'short_description' => Schema::TYPE_STRING . "(255) NULL",
    'PRIMARY KEY (authorization_id)',
], $this->tableOptions);

I want to add another command before the line containing the "createTable() command, so that my example file will look as follows :-

// admin_setting
$this->dropTable('{{%admin_setting}}');
$this->createTable('{{%admin_setting}}', [
    'setting_id' => Schema::TYPE_INTEGER . "(11)  NOT NULL AUTO_INCREMENT",
    'short_description' => Schema::TYPE_STRING . "(255) NOT NULL",
    'value' => Schema::TYPE_TEXT . " NULL",
    'PRIMARY KEY (setting_id)',
], $this->tableOptions);

// authorization
$this->dropTable('{{%authorization}}');
$this->createTable('{{%authorization}}', [
    'authorization_id' => Schema::TYPE_INTEGER . "(11)  NOT NULL AUTO_INCREMENT",
    'short_description' => Schema::TYPE_STRING . "(255) NULL",
    'PRIMARY KEY (authorization_id)',
], $this->tableOptions);

I am seeing lots of examples to delete lines matching a pattern, but none so far that can get me the above changes.

The closest I came is

   awk '/createTable/{print "$this->DropTable();"}1' file

but I cannot fill in the contents inside the brackets.

Answer 1

$ cat tst.awk
{ new = $0 }
sub(/createTable/,"dropTable",new) {
    sub(/,.*/,");",new)
    print new
}
{ print }

$ awk -f tst.awk file
// admin_setting
$this->dropTable('{{%admin_setting}}');
$this->createTable('{{%admin_setting}}', [
    'setting_id' => Schema::TYPE_INTEGER . "(11)  NOT NULL AUTO_INCREMENT",
    'short_description' => Schema::TYPE_STRING . "(255) NOT NULL",
    'value' => Schema::TYPE_TEXT . " NULL",
    'PRIMARY KEY (setting_id)',
], $this->tableOptions);

// authorization
$this->dropTable('{{%authorization}}');
$this->createTable('{{%authorization}}', [
    'authorization_id' => Schema::TYPE_INTEGER . "(11)  NOT NULL AUTO_INCREMENT",
    'short_description' => Schema::TYPE_STRING . "(255) NULL",
    'PRIMARY KEY (authorization_id)',
], $this->tableOptions);

Answer 2

This might work for you (GNU sed):

sed '/createTable/{h;G;s//dropTable/;s/, \[/);/}' file

Copy the line containing createTable, then append the copied line and pattern match and replace on the first occurrence.

Answer 3

You can play around with the field delimiter in awk. Setting it to a single quote, you can access the desired value in field 2:

awk -F"'" '/createTable/{print "$this->dropTable('\''"$2"'\'');"}1'

sed's s can also be used:

sed 's/\$this->createTable(\('\''[^'\'']*'\''\)/$this->dropTable(\1);\n\0/'

It's a bit tricky to escape the single quotes. Alternatively you can enclose the command with double quotes, however, in that case you need to take care on preventing the shell from interpreting the $:

sed "s/\\\$this->createTable(\('[^']*'\)/\$this->dropTable(\1);\n\0/"

Answer 4

A GNU awk solution:

/createTable/ {
    match ($0, /createTable\('(.*)',/, x)
    print "$this->dropTable('" x[1] "');"
}

{
    print $0
}

edit: used the suggestions, noting that this is GNU awk, and removed unneeded print from the first block, tks.

note: I think the whole {print $0} may be replaced by '1' (single character '1'), but I believe readable is better than short here.