# 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+) /ix;

        $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)\((.*)$}{ SetOrEnumToVarchar($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";
}