Hi ;D

I've abstracted some of yer conversion code into a subroutine. It's more complete (not by too much).

update: made it work for all my tables (much better than my initial version ;D)

# thanks to Jeffa ([id://150476|MySQL 2 SQLite])
# with improvements
# I'd use SQL::Parser, but it don't support Mysql
# so I gotta do it ghetto (works for all my tables ;)
sub JeffasMysql2SQLite {
    my @Ssql = split /\n/,shift(@_);
    my %index;
    my @tablename;

    for my $sql( @Ssql ){
        push @tablename, $1 if $sql =~ m/create \s+ table \s+ (\w+) /i
+x;

        $sql =~ s/^#.*$//mg;              # chokes on comments
        $sql =~ s{^\s*?(KEY(?:\s*\w+\s*)?\(.*)}{
                    push @{$index{$tablename[-1]}},$1;
                    "";
                }giemx;
        $sql =~ s/auto_increment//ig;      # on 'auto_increment'
        $sql =~ s/UNSIGNED//ig;
        $sql =~ s/TYPE=\w+;/;/gi;          # and on 'TYPE=____'
        $sql =~ s/\\'/''/g;               # and on escaped '

        $sql =~ s{^\s*?(\w+)\s+(?:SET|ENUM)\((.*)$}{ SetOrEnumToVarcha
+r($1,$2); }exig;
    }

    my $ret = join "\n",@Ssql;

    for my $tablename( @tablename ){

        for my $ix( @{ $index{$tablename} } ){
            $ix=~ s/[,\s]+$//;

            if( $ix =~ /\bKEY\((\w+)\)/i ) {
                $ret.=" CREATE INDEX $1 on $tablename ($1);\n";
            } elsif( $ix =~ /\bKEY\s+(\w+)\s+\((.*?)\z/i ) {
                $ret.=" CREATE INDEX $1 on $tablename ($2;\n";
            }
        }
    }

    return $ret;
}

sub SetOrEnumToVarchar {
    my( $name, $val ) = @_;
    my $end   = substr $val, rindex($val,')') + 1;
       $val   = substr $val, 0, rindex($val,')') - 1;
    my $q     = substr $val, 0, 1, "";
     ( $val ) = sort {
                    $b <=> $a
                } map {
                    s/^\"//;
                    s/\"\$//;
                    length $_;
                }   split /$q,$q/, $val;

#warn "\n\t\tname $name\n\t\t val $val\n\t\t end $end\n\t\t   q $q\n\t
+\t";
    return "$name VARCHAR($val) $end";
}
[download]

I'm not sure what to do yet with ENUM/SET TYPES , since ANSI SQL92 does not support it, I guess i'll convert them to VARCHAR(n), where n is the length of the longest value.

update: Did it.

keyboard ENUM("dvorak","asdf","qwerty") NOT NULL DEFAULT "qwerty",

now translates to

keyboard VARCHAR(6) NOT NULL DEFAULT "qwerty",

update (Wed Mar 5 06:33:17 2003 GMT): Whoohooo, go SQL Fairy!!!! :D

MJD says you can't just make shit up and expect the computer to know what you mean, retardo! ** The Third rule of perl club is a statement of fact: pod is sexy.