#!/usr/bin/perl
#insert body text in the db for storage in the db explicitly.

use strict;
use IO::File;
use vars qw( %prefs );
require "prontolib.pl";

read_prefs();

my $conn = open_db_conn();
my ($transactionFlag, $sql, $msgCount, $tableExists);

# See if we already created the body text table
# tables() may break with some obscure DBI drivers
my @tables = $conn->tables();
foreach my $table(@tables){
    $tableExists = 'true' if $table eq 'message_sources';
}

# Turn off AutoCommit for PostgreSQL
# Use Pg types for table
if($conn->{Driver}->{Name} eq 'Pg'){
    my $sql = 'SELECT version()';
    my $sth = $conn->prepare($sql);
    $sth->execute();
    my $versionString = $sth->fetchrow_array;
    $sth->finish;
    my ($pgVersion) = $versionString =~ /PostgreSQL\s+(\d\.\d)/;
	
    if ($prefs{'DriverVersion'} < 0.96 || $pgVersion < 7.1) {
       die "You need DBD::Pg version 0.96 or greater and PostgreSQL version
            7.1 or greater to store your messages in the database.\n";
    }  
    $transactionFlag = 1;
    $conn->{AutoCommit} = 0;
    $sql = "CREATE TABLE message_sources(id INTEGER PRIMARY KEY, bodytext TEXT)";
} else {
    # assuming MySQL
    $sql = "create table message_sources(id int4 PRIMARY KEY, bodytext longtext)";
}

$conn->do($sql) unless $tableExists eq 'true';

# Get the number of messages we should be inserting
$sql = "SELECT count(*) FROM messages";
my $query = $conn->prepare($sql);
$query->execute();
$msgCount = $query->fetchrow_array();

# Get the list of message ids
$sql = "select id from messages";
$query = $conn->prepare($sql);
$query->execute();

my $insertCount = 0;
my $query2;

while((my $id)=$query->fetchrow_array()) {
	print $id." text inserted into db\n";
	my $tmp = filename_to_tree("$prefs{'MailDir'}/$id");
	my $body = "";
	undef $/;
	my $fh = new IO::File;
	$fh->open("< $tmp");
	$body = <$fh>;
	$fh->close;
	$/ = "\n";
	my $sql2 = "insert into message_sources (id,bodytext) values (?,?)";
	$query2 = $conn->prepare($sql2);
	$query2->execute($id,$body);
	
	++$insertCount;
	if($transactionFlag){
	    # commit every 100 records
	    if($insertCount % 100 == 0){
	        $conn->commit();
			print "$insertCount records committed\n";
	    }
    }
}

# do a final commit
if($transactionFlag){
    $conn->commit();
	print "$insertCount records committed\n";
}

# Check the status
if($insertCount != $msgCount){
    print "Warning! Some messages may not have been inserted!\n";
    print "$insertCount of $msgCount total messages were inserted.\n";
}

$query->finish;
$query2->finish;	
$conn->disconnect;