ghenry has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

This is driving me crazy. I have been on the CB all day, but it's best if you see all the relevant code here.

I am trying to search for certain pattern from a 12MB sql dump. I then print that to a new filehandle, straight after that I do some search and replaces.

The second operation on the already open filehandle just doesn't work, even with a simple replace of the first word to test.

If I just comment out the replacement part, and add print, that's fine and proves the seek worked.

Anyway, here's the code: 

#!/usr/bin/perl -w
#=====================================================================
+==========
#
#         FILE:  create_seq_test.pl
#
#        USAGE:  ./create_seq_test.pl
#
#  DESCRIPTION:  Test script to "create sequences" from backup sql fil
+e
#
#      OPTIONS:  ---
# REQUIREMENTS:  ---
#         BUGS:  ---
#        NOTES:  ---
#       AUTHOR:  Gavin Henry (GH), <ghenry@suretecsystems.com>
#      COMPANY:  Suretec Systems Ltd.
#      VERSION:  1.0
#      CREATED:  29/12/05 14:42:44 GMT
#     REVISION:  ---
#=====================================================================
+==========

use strict;
use warnings;
use Carp;
use Readonly;
use Regexp::DefaultFlags; # Adds xms to the end of all Regexp
use diagnostics;          # More helpful error messages, comment out l
+ater
$| = 1;                   # Set the autoflush flag to on

Readonly my $import_seqs  => 'dbexp.sql';
Readonly my $create_seqs  => 'cre_seq.sql';

open my $CREATE_SEQS, '+>', $create_seqs     # using indirect filehand
+les, r/w
    or croak "Can't open '$create_seqs': $!";

open my $SQL_TO_GREP, '<', $import_seqs      # three-argument form is 
+more robust
    or croak "Can't open '$import_seqs': $!";

while ( <$SQL_TO_GREP> ) {
    print $CREATE_SEQS $_ if /\A SELECT [ ] pg_catalog [.] setval [(]/
+;
}
close $SQL_TO_GREP or croak "Couldn't close '$import_seqs': $!";

seek( $CREATE_SEQS, 0, 0 )  # Go back to start of file before substitu
+tion
    or croak "can't rewind '$create_seqs': $!";

while ( <$CREATE_SEQS> ) {
    s{\A                                         # Substitute from sta
+rt of line
        SELECT [ ] pg_catalog [.] setval [(] ['] # Replace this, which
+ uses singular
                                                 # Character classes t
+o escape
                                                 # metacharacters, inc
+luding spaces

    }
    { CREATE [ ] SEQUENCE [ ] imp [.] }g;        # With this, globally

    s{ ['] [,] [ ] }{ [ ] INCREMENT [ ] BY [ ] 1 [ ] START [ ] WITH [ 
+] }g;

    s{ [,] [ ] false [)] ; }{ ; }g;

    s{ [,] [ ] true [)] ; }{ ; }g;
}

close $CREATE_SEQS or croak "Couldn't close '$create_seqs': $!";
print "Create Sequence statements file complete.\n"

[download]
Data that gets written to $create_seqs: 
SELECT pg_catalog.setval('country_id_seq', 240, true);
SELECT pg_catalog.setval('industry_id_seq', 27, true);
SELECT pg_catalog.setval('language_id_seq', 5, true);
SELECT pg_catalog.setval('privilege_id_seq', 3, true);
SELECT pg_catalog.setval('action_id_seq', 1, true);
SELECT pg_catalog.setval('lead_id_seq', 47, true);
SELECT pg_catalog.setval('company_type_id_seq', 10, true);
SELECT pg_catalog.setval('object_id_seq', 28091, true);
SELECT pg_catalog.setval('reason_id_seq', 226, true);
SELECT pg_catalog.setval('currency_id_seq', 252, true);
SELECT pg_catalog.setval('custom_fields_id_seq', 173, true);
SELECT pg_catalog.setval('custom_lists_id_seq', 166, true);
SELECT pg_catalog.setval('custom_options_id_seq', 623, true);
SELECT pg_catalog.setval('reference_companies_id_seq', 1, false);
SELECT pg_catalog.setval('email_template_seq', 81, true);
SELECT pg_catalog.setval('help_content_id_seq', 1, false);
SELECT pg_catalog.setval('department_id_seq', 8, true);
SELECT pg_catalog.setval('workflow_id_seq', 1, false);
SELECT pg_catalog.setval('pgroup_id_seq', 12, true);
SELECT pg_catalog.setval('aws_user_id_seq', 1, false);
SELECT pg_catalog.setval('sequence_id_seq', 1, false);
SELECT pg_catalog.setval('response_id_seq', 101, true);

[download]

Thanks.

Walking the road to enlightenment... I found a penguin and a camel on the way.....
Fancy a yourname@perl.me.uk? Just ask!!!

Replies are listed 'Best First'.
Re: Regexp Substitution on previously opened filehandle
by japhy (Canon) on Dec 29, 2005 at 16:02 UTC
    Oh, I see the problem. You can't just s/// on a line from a read-write filehandle and have the changes reflected in the file. You need to print the changed line back to the file. And that gets ugly and tricky if the size of the line has changed. You'd be best to do: 
    while (<FILE_1>) {
  if (/regex/) {
    s/.../.../;
    s/.../.../;
    print FILE_2;
  }
}

    [download]
    Jeff japhy Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and perl hacker
    How can we ever be the sold short or the cheated, we who for every service have long ago been overpaid? ~~ Meister Eckhart
[reply]
[d/l]
[select]
      Or use Tie::File to pretend you're working on an array. Then the s/// would affect the lines in the file.
      Caution: Contents may have been coded under pressure.
[reply]
[d/l]

      Thanks, one of those days again (which I seem to be having more of). Just needed to change the print statement a bit.

      Final version: 

      #!/usr/bin/perl -w
#=====================================================================
+==========
#
#         FILE:  create_seq_test.pl
#
#        USAGE:  ./create_seq_test.pl
#
#  DESCRIPTION:  Test script to "create sequences" from backup sql fil
+e
#
#      OPTIONS:  ---
# REQUIREMENTS:  ---
#         BUGS:  ---
#        NOTES:  ---
#       AUTHOR:  Gavin Henry (GH), <ghenry@suretecsystems.com>
#      COMPANY:  Suretec Systems Ltd.
#      VERSION:  1.0
#      CREATED:  29/12/05 14:42:44 GMT
#     REVISION:  ---
#=====================================================================
+==========

use strict;
use warnings;
use Carp;
use Readonly;
use Regexp::DefaultFlags; # Adds xms to the end of all Regexp
use diagnostics;          # More helpful error messages, comment out l
+ater
$| = 1;                   # Set the autoflush flag to on

Readonly my $import_seqs  => 'dbexp.sql';
Readonly my $create_seqs  => 'cre_seq.sql';

open my $CREATE_SEQS, '+>', $create_seqs     # using indirect filehand
+les, r/w
    or croak "Can't open '$create_seqs': $!";

open my $SQL_TO_GREP, '<', $import_seqs      # three-argument form is 
+more robust
    or croak "Can't open '$import_seqs': $!";

while ( <$SQL_TO_GREP> ) {
    if ( /\A SELECT [ ] pg_catalog [.] setval [(]/ ) {

        s{\A                                         # Substitute from
+ start of line
            SELECT [ ] pg_catalog [.] setval [(] ['] # Replace this, w
+hich uses singular
                                                     # Character class
+es to escape
                                                     # metacharacters,
+ including spaces

        }
        {CREATE SEQUENCE imp.};         

        s{ ['] [,] [ ] }{ INCREMENT BY 1 START WITH };

        s{ [,] [ ] false [)] ; }{;};

        s{ [,] [ ] true [)] ; }{;};

        print $CREATE_SEQS $_;
        }

}
close $SQL_TO_GREP or croak "Couldn't close '$import_seqs': $!";
close $CREATE_SEQS or croak "Couldn't close '$create_seqs': $!";

print "Create Sequence statements file complete.\n"

      [download]

      Walking the road to enlightenment... I found a penguin and a camel on the way.....
      Fancy a yourname@perl.me.uk? Just ask!!!
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom