regular expression code not working

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

Venerable monks

I have downloaded some code which is supposed to convert mssql database create statements into mysql create statements but it isn't working. The create table statement is supposed to have a semi colon at the end but it isn't getting one. Here is the relevant section of code to read in a mssql schema from file and convert to mysql. I'm guessing the faulty bit is in the top few lines

 
   sub convert_schema {
  my ($name,$schema,$engine) = @_;
  my $schema2 = '';

  my @lines = split "\n",$schema;
  for (@lines) {

    # CHANGES FOR ALL TABLES

    s#[\[\]]##g;        # Remove brackets
    s/\)\n/\);\n/;            # Add semicolon to end of table statemen
+t
    s/char \(/char(/;        # Remove space between varchar and (
    s/ ,/,/g;            # Remove space before comma

    s/ varchar\(\d+\)/ text/ if (m/varchar\((\d+)\)/) and ($1 > 255); 
+# Use "text" instead of "varchar(n)" if n > 255
    s/\)$/\);/;

    s/ smalldatetime/ varchar(32)/g; # Trouble w/dbSNP dates, like "20
+00-08-25 17:02:00.0" - the .0 at the end.
    s/ datetime/ varchar(32)/g;
    s/ real/ float/g;
    s/ bit/ boolean/g;
[download]

this is what is created - note the absence of semi colon

CREATE TABLE Allele (
    allele_id int NOT NULL,
    allele varchar(255) NOT NULL,
    create_time varchar(32) NOT NULL,
    rev_allele_id int NULL,
    src varchar(10) NULL,
    last_updated_time varchar(32) NULL 

)
[download]

here is a mssql create table

CREATE TABLE [Allele] (
    [allele_id] [int] NOT NULL ,
    [allele] [varchar] (255) NOT NULL ,
    [create_time] [smalldatetime] NOT NULL ,
    [rev_allele_id] [int] NULL ,
    [src] [varchar] (10) NULL ,
    [last_updated_time] [smalldatetime] NULL 

)
[download]

I don't understand why this doesnt work. The regular expression looks ok to me

 s/\)\n/\);\n/;
[download]

It seems to be looking for a bracket followed by a new line and replacing with with a bracket followed by a semi colon and a newline? But it isn't working. It isn't detecting the \)\n bit because if i take out the \n bit it replaces all brackets but if i add the \n back it doesn't detect anything

thanks for any help

Comment on regular expression code not working Select or Download Code

Replies are listed 'Best First'.
Re: regular expression code not working by AnomalousMonk (Archbishop) on Dec 05, 2010 at 03:00 UTC
`my @lines = split "\n", $schema; for (@lines) { ... s/\)\n/\);\n/; ... }` [download] None of the substrings split from `$schema` in the `my @lines = split "\n", $schema;` statement will contain a newline because you're splitting on a newline, thus the match regex of `s/\)\n/\);\n/;` will never match. To add a ';' (semicolon) after a ')' (right-paren) that was at the end of a newline-terminated line, try `s{ \) \z }{);}xms;` or maybe to be on the safe side `s{ \) \s* \z }{);}xms;` (I assume you're stitching all the lines back together with something like a `$schema2 = join "\n", @lines;` statement).	[reply] [d/l] [select]
Re: regular expression code not working by ww (Archbishop) on Dec 04, 2010 at 21:45 UTC
Try: `$line =~ s/\)\z/\)\;\n/;` I can't tell if that'll do the job for you, but it appears to me that your terminating "`)`" may NOT be the first char on the line (depending on content of the input and/or how you're handling the input from files with the MSSQL schema). Alternately, you may want to read the documentation re regexen available at your command prompt -- including perlre and perlretut -- with specfic reference to multi-line and single-line mode.	[reply] [d/l] [select]
Re: regular expression code not working by Anonymous Monk on Dec 04, 2010 at 20:38 UTC
i changed it to this and it worked `s/^\)/\);\n/;` but i am worried as this is published code and I don't understand why the original expression failed for me	[reply] [d/l]
Re: regular expression code not working by Anonymous Monk on Dec 04, 2010 at 21:03 UTC
Maybe it was at the end of the string, with no following newline. I would use this: `s/^\s\)\s($\|\n)/);/;`	[reply] [d/l]
Re^2: regular expression code not working by Ratazong (Monsignor) on Dec 04, 2010 at 21:48 UTC
I assume your conclusion is right. Depending on your data, you might even solve it with an easier regex: `s/\)$/\);/; # replace an ) at the end of the line with an );` [download] HTH, Rata	[reply] [d/l]