Very Computer

by **Samik Raychaudhur** » Thu, 10 Jun 2004 00:53:43

Hello,
I have a scripting problem where I have a ddl and I want extract stubs
corresponding to CREATE TABLE command.
The ddl file looks like this:
*************************************************************
------------------------------------------------
-- DDL Statements for table "PQR"
------------------------------------------------

CREATE TABLE "PQR" (
"P" CHAR(6) NOT NULL ,
"Q" CHAR(3) NOT NULL ,
"R" CHAR(10) ,
"S" CHAR(10) NOT NULL WITH DEFAULT USER ,
"T" DATE NOT NULL WITH DEFAULT CURRENT DATE ,
"U" CHAR(8) NOT NULL WITH DEFAULT '00:00:00' ) IN "TV" ;

-- DDL Statements for primary key on Table "PQR"

ALTER TABLE "PQR"
ADD PRIMARY KEY
("P",
"Q");

------------------------------------------------
-- DDL Statements for table "ABC"
------------------------------------------------

CREATE TABLE "ABC" (
"A" CHAR(6) NOT NULL ,
"B" CHAR(12) NOT NULL ,
"C" CHAR(4) NOT NULL ,
"D" DATE NOT NULL ,
"E" DATE NOT NULL WITH DEFAULT CURRENT DATE ,
"F" CHAR(8) ) IN "TV" ;

-- DDL Statements for primary key on Table "ABC"

ALTER TABLE "ABC"
ADD PRIMARY KEY
("A",
"B",
"C",
"D",
"E");
----------------------------------
*************************************************************

What I need is a script to grab only the CREATE TABLE commands and push
them in some other file. These commands look like a paragraph and is
seperated by a blank line after each command. I was trying to use awk,
but was not sure how to go about it.
Please help me with some pointers.
Thanks very much.
-Samik

by **Adam Pric** » Thu, 10 Jun 2004 01:27:25

> Hello,
> I have a scripting problem where I have a ddl and I want extract stubs
> corresponding to CREATE TABLE command.
> The ddl file looks like this:
> *************************************************************
> ------------------------------------------------
> -- DDL Statements for table "PQR"
> ------------------------------------------------

> CREATE TABLE "PQR" (
> "P" CHAR(6) NOT NULL ,
> "Q" CHAR(3) NOT NULL ,
> "R" CHAR(10) ,
> "S" CHAR(10) NOT NULL WITH DEFAULT USER ,
> "T" DATE NOT NULL WITH DEFAULT CURRENT DATE ,
> "U" CHAR(8) NOT NULL WITH DEFAULT '00:00:00' ) IN "TV" ;

> -- DDL Statements for primary key on Table "PQR"

> ALTER TABLE "PQR"
> ADD PRIMARY KEY
> ("P",
> "Q");

> ------------------------------------------------
> -- DDL Statements for table "ABC"
> ------------------------------------------------

> CREATE TABLE "ABC" (
> "A" CHAR(6) NOT NULL ,
> "B" CHAR(12) NOT NULL ,
> "C" CHAR(4) NOT NULL ,
> "D" DATE NOT NULL ,
> "E" DATE NOT NULL WITH DEFAULT CURRENT DATE ,
> "F" CHAR(8) ) IN "TV" ;

> -- DDL Statements for primary key on Table "ABC"

> ALTER TABLE "ABC"
> ADD PRIMARY KEY
> ("A",
> "B",
> "C",
> "D",
> "E");
> ----------------------------------
> *************************************************************

> What I need is a script to grab only the CREATE TABLE commands and push
> them in some other file. These commands look like a paragraph and is
> seperated by a blank line after each command. I was trying to use awk,
> but was not sure how to go about it.
> Please help me with some pointers.
> Thanks very much.
> -Samik

Am I right in saying that this CREATE TABLE command starts with 'CREATE
TABLE' and ends with the first ";"?

If so then the following awk will work (assumes that the original is a file
named IN and the output is in a file named OUT.

awk '/CREATE TABLE/,/;/' < EXAMPLE > OUT

Adam

by **William Par** » Thu, 10 Jun 2004 02:52:14

> CREATE TABLE "PQR" (
> "P" CHAR(6) NOT NULL ,
> "Q" CHAR(3) NOT NULL ,
> "R" CHAR(10) ,
> "S" CHAR(10) NOT NULL WITH DEFAULT USER ,
> "T" DATE NOT NULL WITH DEFAULT CURRENT DATE ,
> "U" CHAR(8) NOT NULL WITH DEFAULT '00:00:00' ) IN "TV" ;
> CREATE TABLE "ABC" (
> "A" CHAR(6) NOT NULL ,
> "B" CHAR(12) NOT NULL ,
> "C" CHAR(4) NOT NULL ,
> "D" DATE NOT NULL ,
> "E" DATE NOT NULL WITH DEFAULT CURRENT DATE ,
> "F" CHAR(8) ) IN "TV" ;
> What I need is a script to grab only the CREATE TABLE commands and
> push them in some other file. These commands look like a paragraph and
> is seperated by a blank line after each command. I was trying to use
> awk, but was not sure how to go about it. Please help me with some
> pointers.

Hint:
awk -v RS='' '/^CREATE TABLE / {print}'
awk '/^.../,/^$/ {print}'
sed -n '/^.../,/^$/p'

Although using something other than blank line would be better, but you
get the idea...

--

No, I will not fix your computer! I'll reformat your harddisk, though.

by **John W. Krah** » Thu, 10 Jun 2004 06:02:24

> I have a scripting problem where I have a ddl and I want extract stubs
> corresponding to CREATE TABLE command.
> The ddl file looks like this:
> *************************************************************
> ------------------------------------------------
> -- DDL Statements for table "PQR"
> ------------------------------------------------

> CREATE TABLE "PQR" (
> "P" CHAR(6) NOT NULL ,
> "Q" CHAR(3) NOT NULL ,
> "R" CHAR(10) ,
> "S" CHAR(10) NOT NULL WITH DEFAULT USER ,
> "T" DATE NOT NULL WITH DEFAULT CURRENT DATE ,
> "U" CHAR(8) NOT NULL WITH DEFAULT '00:00:00' ) IN "TV" ;

> -- DDL Statements for primary key on Table "PQR"

> [snip]

> What I need is a script to grab only the CREATE TABLE commands and push
> them in some other file. These commands look like a paragraph and is
> seperated by a blank line after each command. I was trying to use awk,
> but was not sure how to go about it.
> Please help me with some pointers.

Perl has a paragraph mode that can handle it:

perl -00ne'print if /^CREATE TABLE/' original.file > new.file

John
--
use Perl;
program
fulfillment

Very Computer

Script for grabbing paragraphs

Script for grabbing paragraphs

Script for grabbing paragraphs

Script for grabbing paragraphs

Script for grabbing paragraphs