bin/upgrade-sqlite-0.05.pl

   1 #!/usr/bin/perl
   2
   3 use strict;
   4
   5 use DBI;
   6
   7 my ( $default_lang, $db_from, $db_to ) = @ARGV;
   8
   9 my @languages = (qw/ ru en /);
  10
  11 die "usage: $0 <default-lang> <from> <to>"
  12   unless $default_lang && -f $db_from && $db_to;
  13
  14 my $dbh_from = DBI->connect( "dbi:SQLite:$db_from" ) or die $DBI::errstr;
  15 my $dbh_to   = DBI->connect( "dbi:SQLite:$db_to" )   or die $DBI::errstr;
  16
  17 my $posts = $dbh_from->selectall_arrayref( 'SELECT * FROM post ORDER BY id ASC' );
  18 foreach my $post ( @$posts ) {
  19     my ( $id, $key, $addtime, $modtime, $title, $annot, $content ) = @$post;
  20     my $sth = $dbh_to->prepare(
  21         qq/
  22         INSERT INTO post(id,key,addtime,modtime,orig_lang)
  23         VALUES(?, ?, ?, ?, ?)
  24         /
  25     );
  26     $sth->execute( $id, $key, $addtime, $modtime, $default_lang )
  27       or warn $dbh_to->errstr;
  28
  29     $content =~ s/\Q$annot\E/$annot\n\[cut\]\n/s;
  30     foreach my $lang ( @languages ) {
  31         my $sth = $dbh_to->prepare(
  32             qq/
  33             INSERT INTO post_i18n(post_id,lang,istran,title,annot,content)
  34             VALUES (?, ?, ?, ?, ?, ?)
  35             /
  36         );
  37         $sth->execute( $id, $lang, 0, $title, $annot, $content );
  38     }
  39 }
  40
  41 my $tags = $dbh_from->selectall_arrayref( 'SELECT * FROM tag' );
  42 foreach my $tag ( @$tags ) {
  43     my $sth = $dbh_to->prepare(
  44         qq/
  45         INSERT INTO tag VALUES(?, ?)
  46         /
  47     );
  48     $sth->execute( @$tag )
  49       or warn $dbh_to->errstr;
  50 }
  51
  52 my $tag_maps = $dbh_from->selectall_arrayref( 'SELECT * FROM post_tag_map' );
  53 foreach my $tag_map ( @$tag_maps ) {
  54     my $sth = $dbh_to->prepare(
  55         qq/
  56         INSERT INTO post_tag_map VALUES(?, ?)
  57         /
  58     );
  59     $sth->execute( @$tag_map )
  60       or warn $dbh_to->errstr;
  61 }
  62
  63 my $comments = $dbh_from->selectall_arrayref( 'SELECT * FROM comment' );
  64 foreach my $comment ( @$comments ) {
  65     my $sth = $dbh_to->prepare(
  66         qq/
  67         INSERT INTO comment VALUES(?, ?, ?, ?, ?, ?, ?, ?)
  68         /
  69     );
  70     $sth->execute( @$comment )
  71       or warn $dbh_to->errstr;
  72 }
  73
  74 my $pages = $dbh_from->selectall_arrayref( 'SELECT * FROM page ORDER BY id ASC' );
  75 foreach my $page ( @$pages ) {
  76     my ( $id, $key, $addtime, $modtime, $title, $content ) = @$page;
  77     my $sth = $dbh_to->prepare(
  78         qq/
  79         INSERT INTO page(id,key,addtime,modtime,orig_lang)
  80         VALUES(?, ?, ?, ?, ?)
  81         /
  82     );
  83     $sth->execute( $id, $key, $addtime, $modtime, $default_lang )
  84       or warn $dbh_to->errstr;
  85
  86     foreach my $lang ( @languages ) {
  87         my $sth = $dbh_to->prepare(
  88             qq/
  89             INSERT INTO page_i18n(page_id,lang,istran,title,content)
  90             VALUES (?, ?, ?, ?, ?)
  91             /
  92         );
  93         $sth->execute( $id, $lang, 0, $title, $content );
  94     }
  95 }
  96
  97 $dbh_from->disconnect();
  98 $dbh_to->disconnect();