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.

In reply to Re: MySQL 2 SQLite by PodMaster
in thread MySQL 2 SQLite by jeffa

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.