I'm trying to make a simple bashscript, that given an output from git diff of a sql schema definition file, would be able to create an update script and undo script. So having an input like this:
create table xxx
(
- aaa numeric(18) primary key,
- bbb numeric(18) not null,
- ccc numeric(18) not null,
+ aaa INT UNSIGNED AUTO_INCREMENT primary key,
+ AlarmId INT UNSIGNED not null,
+ ccc INT UNSIGNED not null,
ddd smallint not null,
eee varchar(2),
fff varchar(2),
)
let's assume I want to make an update script. I change all create
statements into alter
's, then strip all unnecessary rows, and finally replace the '+' marks with change
statements (it;s mysql script). I do like so:
sed "s/^\s*create/ALTER/g" < $infile | sed "/^\s\|@/d" | sed "/^-/d" | sed "s/^+\s*\(\`\?\w\+\`\?\)/\tCHANGE \1 \1/g"
So I finally get something like this:
ALTER table xxx
CHANGE aaa aaa INT UNSIGNED AUTO_INCREMENT primary key,
CHANGE bbb bbb INT UNSIGNED not null,
CHANGE ccc ccc INT UNSIGNED not null,
CHANGE ddd ddd smallint not null,
ALTER table yyy
CHANGE aaa aaa INT UNSIGNED primary key,
CHANGE bbb bbb smallint not null,
CHANGE ccc ccc smallint not null,
CHANGE ddd ddd INT UNSIGNED not null,
CHANGE eee eee varchar(1024) not null
ALTER table zzz
(and so on)
One last thing I need to do is to complete each statement with a colon - and I got totally stuck there. Basically, I need to find each ALTER
statement (or EOF), and add a colon at end of the line above it (possibly replacing an existing comma). I was able to do it in vim using it's s
command:
%s/\(,\)\=\_s\+\(ALTER.*\|\%$\)/;\r\2
But I failed to translate this to sed syntax. It appears, that forcing sed to accept a multiline pattern is a hard job. So - can anyone tell me how to do this? I guess sed is not the only option. It just was my first choice (not the best as it appeared :) ) Maybe there are some out-of-the-box solutions, and I'm just re-inventing the wheel?