LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (http://www.linuxquestions.org/questions/programming-9/)
-   -   text processing to modify sql create script (http://www.linuxquestions.org/questions/programming-9/text-processing-to-modify-sql-create-script-712101/)

pcock 03-16-2009 07:59 PM

text processing to modify sql create script
 
I have a large number of sql scripts that I'd like to process.
Code:

CREATE TABLE WAREHOUSE_OBJECT
(
  OBJECT_ID          numeric(15)  NOT NULL,
  OBJECT_IDENTIFIER  char(32)      NOT NULL
);

CREATE UNIQUE INDEX  S_WAREHOUSE_OBJECT_PK ON  S_WAREHOUSE_OBJECT
(CONFIG_ID);

ALTER TABLE  S_WAREHOUSE_OBJECT ADD(  --remove open bracket here
  CONSTRAINT S_WAREHOUSE_OBJECT_PK
 PRIMARY KEY
 (CONFIG_ID));  -- remove last close bracket here

I'd like to remove open/closed brackets (at ALTER TABLE) so it looks like this.

Code:

CREATE TABLE WAREHOUSE_OBJECT
(
  OBJECT_ID          numeric(15)  NOT NULL,
  OBJECT_IDENTIFIER  char(32)      NOT NULL
);

CREATE UNIQUE INDEX  S_WAREHOUSE_OBJECT_PK ON  S_WAREHOUSE_OBJECT
(CONFIG_ID);

ALTER TABLE  S_WAREHOUSE_OBJECT ADD
  CONSTRAINT S_WAREHOUSE_OBJECT_PK
 PRIMARY KEY
 (CONFIG_ID);

I tried something like this in awk but not sure how to eliminate the brackets :(
Code:

BEGIN {OFS=" "}
  $1 =="ALTER"      { x=$0 }
  $1 =="CONSTRAINT" { y=$0}
  $1 =="PRIMARY"    { z=$0}
  $1 =="((" {print x, y, z, $0}


pcock 03-17-2009 04:42 AM

I found a way to fix this :)

sed 's/ADD(/ADD/'
sed 's/));/);/'


All times are GMT -5. The time now is 08:26 AM.