restoring single mysql table from dump

regex question

[jamie]

hi,

have been trying out this nifty perl script to restore a single table from a mysqldump (from the server hacks book by oreilly.):

#!/usr/bin/perl -wn 
BEGIN { $table = shift @ARGV } 
print if /^create table $table\b/io .. /^create table (?!$table)\b/io;

it extracts the desired table perfectly, but it also adds on the first line from the next create table statement. e.g. this is the last line in the extracted tabledump:

CREATE TABLE my_next_table (

i suppose i could hack the hack and delete the last line of the dump, but i was wondering why it didn't work as it should - coming from such a reputable souce?

i am not a perl expert, but from what i know about regular expressions, i just can't see why it barfs?

thanks for any suggestions!

[dcrombie]

I would use something like this on the command-line:

awk '/CREATE TABLE table/,/;/{print}' dumpfile;

;)

[jamie]

dcrombie,

thanks i think. i am sure there are other ways to do it, but i was interested in the perl way.

(as a side note i couldn't get yours to work either.. grrrrr ;-)

[wruppert]

The .. operator returns true after the second condition is met. This is one line too late for you.

This code works, but is not nearly as cool.

use strict;
use warnings;
use Carp;
 
my $table = shift;
my $printing = 0;
 
while (<>) {
  $printing = 1 if /^create table $table\b/io;
  $printing = 0 if $printing && /^create table (?!$table)\b/io;
  print if $printing;
}

[edited by: coopster at 11:51 am (utc) on April 21, 2005]
[edit reason] Disabled graphic smile faces for this post [/edit]

[wruppert]

This variation stops when the next table is found. If you are working with a very large DB, this might be better.

use strict;
use warnings;
use Carp;
 
my $table = shift;
my $printing = 0;
 
while (<>) {
  $printing = 1 if /^create table $table\b/io;
  last if $printing && /^create table (?!$table)\b/io;
  print if $printing;
}

[edited by: coopster at 11:51 am (utc) on April 21, 2005]
[edit reason] Disabled graphic smile faces for this post [/edit]

[jamie]

hi wruppert,

>> The .. operator returns true after the second condition is met. This is one line too late for you.

yes that's what i thought. i haven't actually been able to get your code working, as the way in which i fed the arguments to the original script is different - i guess i need to learn a bit more about perl loops, etc.

thanks for the input, if i get in a pickle, i'll get back to you!

cheers

[wruppert]

The first line using my script would just be

#!/usr/bin/perl

instead of

#!/usr/bin/perl -wn 

"use warnings;" handles the -w.
The -n makes perl assume the "while (<>)" loop.

The command line invocation would be:

perl thisprog.pl tablename dumpfile

Something close to the original would be this (same invocation):

#!/usr/bin/perl -wn
BEGIN { 
  $table = shift @ARGV;
  $printing = 0; 
}
$printing = 1 if /^create table $table\b/io; 
exit if $printing && /^create table (?!$table)\b/io; 
print if $printing; 

But if the program is going to be more than 1 line, I prefer the explicit loop.

[jamie]

that's great! works perfectly.

i can sort of get my head round the loop as well - many thanks for the explanations.

jamie