SQLite2PG

The code below uses a simple file copy of the SQLite database and inserts it into a PostgreSQL database that has been prepared with make initialize-database. It's probably a bit rough but worked reasonably for me. I'd appreciate hearing about any comments, successes or failures you have with this.

Note that the sequences need to be renormalized after this completes for everything to work correctly. I've also pasted my commandline to do that below the script.

This code is posted in the hopes that it will be usefull but without any guarantee or warranty. I place it in the public domain.

#!/usr/bin/perl
 
 use strict;
 use warnings;
 use DBI;
 use DBIx::ContextualFetch;
 
 my $select = "select * from %s";
 my $insert = "insert into %s ( %s ) values ( %s )";
 my $delete = "delete from %s";
 
 my $sqlite = DBI->connect(
     'dbi:SQLite:dbname=./rt3.db',
     '', '',
     {
         AutoCommit => 0,
         RaiseError => 1,
        RootClass  => "DBIx::ContextualFetch",
     }
 );
 
 my $pg = DBI->connect(
     'dbi:Pg:dbname=rt3',
     'rt', '',
    { AutoCommit => 0, RaiseError => 1 }
 );
 
 for my $table ($sqlite->tables('%', '%', '%') ) {
 
     my %default = (
         integer => 0,
         'timestamp without time zone' => '1970-01-01 00:00:00',
     );
 
     my %type = ();
 
     $table =~ s/"//g;
     print "------- table $table \n";
 
     # skip sqlite meta-tables
     next if ($table =~ /^sqlite_/);
 
     # get the the source rows
     my $sel = $sqlite->prepare( sprintf($select, $table ) );
     $sel->execute();
     my @row = $sel->fetchall_hash;
 
     # remove all the old data
     $pg->do( sprintf( $delete, $table ) );
 
     for my $r (@row) {
 
         # give the database a chance to use its defaults
         for my $c (keys %{$r} ){
             if (not defined $r->{$c} or $r->{$c} eq '') {
                 delete $r->{$c};
             }
         }
 
         # figure out the insert statement
         my $q = sprintf( $insert,
             $table,
             join( ',', keys %{$r} ),
             join( ',', ('?') x scalar keys %{$r} ),
         );
         my $ins = $pg->prepare( $q );
 
         $ins->execute( values %{$r} );
     }
 }
 
 $sqlite->disconnect();
 
 $pg->commit();
 $pg->disconnect();
 
 

Command line for resetting sequences.

sudo perl -lane '
        /CREATE SEQUENCE (\S[^_]+)(\S+);/ or next;
        print qq|select setval(\047$1$2\047, (select max(id) from $1)+1);|;
' schema.Pg  | psql -Upostgres rt3