consider the input file which i am dealing with looks like this..
#cat 11.sql
create table abc (
.
.
.
) engine=Innodb ;
.
.
etc
.
.
.
create table UsM (
blah
blah
blah
) engine=Innodb ;
.
.
.
drop procedure if exists b77 ;
Delimiter $$
CREATE PROCEDURE b77(id int)
BEGIN
END
$$
Delimiter ;
.
.
.
drop procedure if exists a77;
Delimiter $$
CREATE PROCEDURE a77(id int)
BEGIN
END
$$
Delimiter ;
.
.
CREATE PROCEDURE gNodes (LEVEL INT)
BEGIN
blah
blah
.
.
SET rootName = (SELECT DirectoryCategory.name FROM DirectoryCategory WHERE DirectoryCategory.id IN (SELECT DirectoryTree.directoryCategoryId FROM DirectoryTree WHERE DirectoryTree.id=rootId)) ;
DROP TABLE IF EXISTS ResultNodes ;
CREATE TABLE ResultNodes (id INT, NAME TEXT) ;
END IF ;
IF LEVEL>0 THEN
SET parentPrefix = CONCAT(pa etc etc
.
.
END
$$
Delimiter ;
here somewhere in the middle of a procedure there is a "create table" thing... i am perfroming some action so that all create table to Engine=innodb is exctarcted to a file.. that command used is as below
perl -lne '(/^\s*create\s+table/i .. /^\s*\)\s+engine=Innodb/i) && print;' "11.sql" > onlytables.sql
but the problem is since there is a create table line in between a procedure even it is considered as a table and extracted... how to avoid this... i could perfrom this action in a while loop where it extractes all tables to a file and break when it encounters any line with create procedure in it( since create table lines come in first part of file and procedures comes next) but i need one liners to perfrom this action... any help is deeply appreciated .... :wall::wall:
