#!/usr/bin/perl -w
#===============================================================================
#
#         FILE:  create_seq_test.pl
#
#        USAGE:  ./create_seq_test.pl
#
#  DESCRIPTION:  Test script to "create sequences" from backup sql file
#
#      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 later
$| = 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 filehandles, 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, which uses singular
                                                     # Character classes 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"