1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
#!/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;
|
