#!/usr/bin/perl -w

use strict;
use warnings;

# -----------------------------------
# Source: https://wiki.debian.org/Roundcube/DeprecationOfSQLitev2
#
# The original script (bash/awk) don't work like 
# expected when some fields contains '\x0d' characters...
# (or any characters interpreted like a "newline").
#
# Usage:
#   ./rc_sqlite_convert.pl
# Input file: 
#   A sqlite2 file in the current directory and named "rouncube"
# Output file: 
#   3 files converted for each table: 
#   'table_users.sql' 'table_identities.sql' and 'table_contacts.sql'
# 
# TJ : 2013/08/06-1
# -----------------------------------

my $DBFILENAME = "./roundcube";

my @column_names;
my $sql;

sub translate
{
  my ( $li ) = @_;
  
  if ( $li =~ /^BEGIN/ ) {
    die "already begin!" if $sql;
    $sql = 1;
    return "BEGIN;";
  } elsif ( $li =~ /^INSERT INTO/ ) {
    die "no begin!" unless $sql;

    my $result = '';
    my $insert_part;

    unless ( ( $insert_part ) = $li =~ /^(INSERT INTO \"[A-Za-z].*\")/ ) {
      ( $insert_part ) = $li =~ /^(INSERT INTO [^ ]*)/ ;
    }

    $result .= "$insert_part ";

    $result .= "(";

    my $first_pass = 1;
    foreach my $c ( @column_names ) {
      if ( $first_pass ) { $first_pass = 0; }
      else { $result .= ", "; }
      $result .= "`$c`";
    }

    $result .= ") ";

    $result .= substr( $li, length($insert_part) );

    return $result;

  } elsif ( $li =~ /^COMMIT/ ) {
    die "no begin!" unless $sql;
    $sql = 0;
    return "$li";
  }
  # else: ignore
  return;
}

# 3 tables only

foreach my $table ( qw( identities contacts users ) )
{
  $sql = 0;
  undef( @column_names );

  print "$DBFILENAME : \"$table\" ";
  
  # For each table, extract column names

  if ( open( my $db,"-|","sqlite $DBFILENAME 'pragma table_info($table)'" ) || die "$!" ) {

    while( my $li = readline($db) ) {
      chomp($li);
      if ( $li =~ /^\d+\|/ ) {
        if ( my @arr = split('\|',$li) ) {
          exists($arr[1]) or die "column incorrect $li";
          push(@column_names,$arr[1]);
        }
      }
    }
    close($db);

    die "no columns!" unless @column_names;
      
    #foreach my $c ( @column_names ) { print "DEBUG:table:$table > $c\n"; }
    
    # Dump and parse table contents
    
    my $filename = "table_$table.sql";

    if ( open( my $dest , ">" , "$filename" ) || die "$!" ) {
      if ( open( my $db,"-|","sqlite $DBFILENAME '.dump $table'" ) || die "$!" ) {
        my $mli = '';
        
        while ( my $line = readline($db) ) {
          $line =~ s/\x0a$// ;
          if ( $line =~ /\x0d$/ ) {
            $mli .= $line;
            next;
          }
          $mli .= $line;
        
          # translate
          my $result = translate( $mli );

          # write
          if ( defined( $result ) ) {
            print $dest "$result\n" or die "$!";
          }
        
          $mli = '';
        }

        die "no eof" unless eof($db);
  
        close($db);
      }
      close( $dest ) or die "$!";

      print ": File:$filename Ok.\n";
    }
  }
}

exit 0;

# --------------
# EOF