Admin: 2 revisions imported

2016-04-06T20:36:22Z

2 revisions imported

New page

The code below uses a simple file copy of the [[SQLite]] database and inserts it into a [[PostgreSQL]] database that has been prepared with <code> make initialize-database</code>. 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.

<nowiki>#!/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();

</nowiki>

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

SQLite2PG - Revision history

Admin: 2 revisions imported