Using gawk to clean up SQL

Shell Programming and Scripting
1

Hi,

What would be the best awk commands to change this:

CREATE TABLE "allow_ip_services" (
  "host" varchar(20) default NULL,
  "user" varchar(30) NOT NULL,
  "services" varchar(100) default NULL,
  KEY "host" ("host"),
  KEY "user" ("user")
);

into this

CREATE TABLE "allow_ip_services" (
  "host" varchar(20) default NULL,
  "user" varchar(30) NOT NULL,
  "services" varchar(100) default NULL
);

If I do a simple gawk '!/KEY/' file I end up with:

CREATE TABLE "allow_ip_services" (
  "host" varchar(20) default NULL,
  "user" varchar(30) NOT NULL,
  "services" varchar(100) default NULL,
);

My SQLite doesn't like the trailing coma :=)

2

Try this:

awk '/services/{sub(",","")} !/KEY/' file

Regards

3

Thanks Franklin52. I should have been more specific.

I would like to delete all lines with KEY in it and delete the last comma from previous line whatever it is. In my example that previous line incidentally starts with "services" but I am looking for a more general solution.

4

Ok, try this:

awk '/KEY/{next}
/);/{sub(",","",s);print s "\n" $0;exit}
{print s;s=$0}
' file

Regards

5

It works. Thanks.