bin/upgrade-sqlite-0.04.pl

   1 #!/usr/bin/perl
   2
   3 use strict;
   4
   5 use DBI;
   6
   7 my ( $db_from, $db_to ) = @ARGV;
   8
   9 die "usage: $0 <from> <to>" unless -f $db_from && $db_to;
  10
  11 my $dbh_from = DBI->connect( "dbi:SQLite:$db_from" ) or die $DBI::errstr;
  12 my $dbh_to   = DBI->connect( "dbi:SQLite:$db_to" )   or die $DBI::errstr;
  13
  14 my $posts = $dbh_from->selectall_arrayref( 'SELECT * FROM post' );
  15 foreach my $post ( @$posts ) {
  16     my ( $id, $addtime, $title, $annot, $content ) = @$post;
  17     my $sth = $dbh_to->prepare(
  18         qq/
  19         INSERT INTO post(id,key,addtime,modtime,title,annot,content)
  20         VALUES(?, ?, ?, ?, ?, ?, ?)
  21         /
  22     );
  23     $sth->execute( $id, $id, $addtime, $addtime, $title, $annot, $content )
  24       or warn $dbh_to->errstr;
  25 }
  26
  27 my $tags = $dbh_from->selectall_arrayref( 'SELECT * FROM tag' );
  28 foreach my $tag ( @$tags ) {
  29     my $sth = $dbh_to->prepare(
  30         qq/
  31         INSERT INTO tag VALUES(?, ?)
  32         /
  33     );
  34     $sth->execute( @$tag )
  35       or warn $dbh_to->errstr;
  36 }
  37
  38 my $tag_maps = $dbh_from->selectall_arrayref( 'SELECT * FROM post_tag_map' );
  39 foreach my $tag_map ( @$tag_maps ) {
  40     my $sth = $dbh_to->prepare(
  41         qq/
  42         INSERT INTO post_tag_map VALUES(?, ?)
  43         /
  44     );
  45     $sth->execute( @$tag_map )
  46       or warn $dbh_to->errstr;
  47 }
  48
  49 my $comments = $dbh_from->selectall_arrayref( 'SELECT * FROM comment' );
  50 foreach my $comment ( @$comments ) {
  51     my $sth = $dbh_to->prepare(
  52         qq/
  53         INSERT INTO comment VALUES(?, ?, ?, ?, ?, ?, ?, ?)
  54         /
  55     );
  56     $sth->execute( @$comment )
  57       or warn $dbh_to->errstr;
  58 }
  59
  60 my $pages = $dbh_from->selectall_arrayref( 'SELECT * FROM page' );
  61 foreach my $page ( @$pages ) {
  62     my ( $id, $addtime, $name, $content ) = @$page;
  63     my $sth = $dbh_to->prepare(
  64         qq/
  65         INSERT INTO page(id,key,addtime,modtime,title,content)
  66         VALUES(?, ?, ?, ?, ?, ?)
  67         /
  68     );
  69     $sth->execute( $id, $id, $addtime, $addtime, $name, $content )
  70       or warn $dbh_to->errstr;
  71 }
  72
  73 $dbh_from->disconnect();
  74 $dbh_to->disconnect();