Changeset 309:1181f65d21a7

Timestamp:

08/18/11 11:27:09 (9 months ago)

Author:

Anselm Lingnau <anselm@…>

Branch:

default

Message:

Tweaks to the archive index database, for the benefit of Project Guinevere.
This introduces a month index for threads as well as a pointer to the last
message in a thread. We also use Time::Piece throughout for consistency.

Files:

• bin/pl-thread (modified) (6 diffs)
• lib/Lancelot/Module/archive_store.pm (modified) (7 diffs)

bin/pl-thread

@@ -68 +68 @@
 my ($subject_count, $sender_count);
 my $prev_date = 0;
+
+my (@month, %month_index, $m_idx);
 
 my $db = new Lancelot::DB $listaddr, { user => $opts{user} }
@@ -122 +124 @@
     my ($n, $msg) = @_;
 
-    $m[$n]->{date} = Lancelot::Module::archive_store::determine_date($msg);
-    if ($prev_date != 0 && (abs($m[$n]->{date} - $prev_date) > 15*24*3600)) {
+    $m[$n]->{date} = Lancelot::Module::archive_store::determine_date($msg) || gmtime;
+    if ($prev_date != 0 && (abs($m[$n]->{date}->epoch - $prev_date) > 15*24*3600)) {
         print STDERR "Unusual date: $n: $m[$n]->{date} (vs. $prev_date)\n";
     }
-    $prev_date = $m[$n]->{date};
+    $prev_date = $m[$n]->{date}->epoch;
 
     $m[$n]->{msgid} = $msg->header("Message-ID") || sprintf $msgid, $n;
@@ -139 +141 @@
                                    \%last_subject, \%message);
     $m[$n]->{root} = find_root($n);
+    my ($year, $month) = ($m[$n]->{date}->year, $m[$n]->{date}->mon);
+    my $m_id;
+    unless ($m_id = $month_index{"$year-$month"}) {
+        push @month, [ ++$m_idx, $year, $month ];
+        $m_id = $month_index{"$year-$month"} = $month[$#month]->[0];
+    }
+    $m[$n]->{month_id} = $m_id;
+    $m[$m[$n]->{root}]->{lastmsg_id} = $n;
 }
 
@@ -172 +182 @@
     my %kids;
 
-    print STDERR "identify_threads: from=$from to=$to\n";
+    # print STDERR "identify_threads: from=$from to=$to\n";
     for (my $i = $from; $i <= $to; $i++) {
         next unless $m[$i];
@@ -210 +220 @@
     }
 
+    print STDERR "Writing month records ...\n";
+    $sth = $dbh->prepare("INSERT INTO month VALUES(?,?,?)");
+    foreach my $m (@month) {
+        $sth->execute(@$m);
+    }
+
     print STDERR "\nWriting messages and threads ...\n";
     $sth = $dbh->prepare("INSERT INTO message VALUES(?,?,?,?,?,?,?,?,?,?,?)");
-    my $tth = $dbh->prepare("INSERT INTO thread VALUES(?,?,?,?,?)");
+    my $tth = $dbh->prepare("INSERT INTO thread VALUES(?,?,?,?,?,?,?)");
 
     foreach my $hh (0 .. $#head) {
@@ -224 +240 @@
             my $m = $m[$k];
             $sth->execute($k, $m->{root}, $count, $m->{parent}, $m->{level},
-                          $t[$i-1], $t[$i+1], $m->{sender}, $m->{date},
+                          $t[$i-1], $t[$i+1], $m->{sender}, $m->{date}->epoch,
                           $m->{subject}, $m->{msgid});
             $count++;
         }
-        $tth->execute($h, $m[$t[1]]->{subject}, $m[$t[1]]->{date},
+        $tth->execute($h, $m[$t[1]]->{month_id}, $m[$t[1]]->{subject},
+                      $m[$t[1]]->{date}->epoch, $m[$t[1]]->{lastmsg_id},
                       $hh ? $head[$hh-1] : -1, $hh < $#head ? $head[$hh+1] : -1);
     }

lib/Lancelot/Module/archive_store.pm

@@ -29 +29 @@
 use Encode qw/encode decode/;
 use Email::Date;
+use Time::Piece;
 
 use Lancelot::Log qw/log/;
@@ -108 +109 @@
 
     $msg->header_set("Delivery-Date", localtime->strftime("%Y%m%d%H%M%S"));
-    my $date = determine_date($msg);
-    $date = time if $date < 0;
+    my $date = determine_date($msg) || gmtime;
 
     my $dbh = open_database($adir);
@@ -139 +139 @@
 
     $dbh->do("INSERT INTO message VALUES (?,0,0,0,0,0,-1,?,?,?,?)", {},
-             $number, $snd_id, $date, $sub_id, $msg->header("Message-ID"));
+             $number, $snd_id, $date->epoch, $sub_id, $msg->header("Message-ID"));
 
     thread_message($dbh, $number, $msg, $sub_id, $date);
@@ -164 +164 @@
         my ($prev_thread) = $dbh->selectrow_array("SELECT MAX(id) FROM thread", {});
         $dbh->do("UPDATE thread SET next=? WHERE next=-1", {}, $id);
-        $dbh->do("INSERT INTO thread VALUES (?,?,?,?,-1)", {},
-                 $id, $sub_id, $date, $prev_thread);
+        my ($year, $month) = ($date->year, $date->mon);
+        $dbh->do("INSERT OR IGNORE INTO month VALUES (NULL, ?, ?)", {},
+                 $year, $month);
+        my ($month_id) = $dbh->selectrow_array(
+            "SELECT id FROM month WHERE year=? AND month=?", {}, $year, $month);
+        $dbh->do("INSERT INTO thread VALUES (?,?,?,?,?,?,-1)", {},
+                 $id, $month_id, $sub_id, $date->epoch, $id, $prev_thread);
         $dbh->do("UPDATE message SET next=? WHERE next=-1", {}, $id);
     }
@@ -178 +183 @@
     if ($msg->header("Delivery-Date")) {
         return Time::Piece->strptime($msg->header("Delivery-Date"),
-                                     "%Y%m%d%H%M%S")->epoch;
+                                     "%Y%m%d%H%M%S");
     }
     if (defined ($rec = $msg->header("Received"))
         && ($rec =~ /\(qmail .*\); (.*) -0000$/)) {
-        return Time::Piece->strptime($1, "%d %B %Y %H:%M:%S")->epoch;
+        return Time::Piece->strptime($1, "%d %B %Y %H:%M:%S");
     }
     if ($msg->header("Date")) {
-        return find_date($msg)->epoch;
-    }
-    return -1;
+        return find_date($msg);
+    }
+    return undef;
 }
 
@@ -274 +279 @@
         $count++;
     }
+    $dbh->do("UPDATE thread SET lastmsg_id=?", {}, $thread[$#thread-1]);
 }
 
@@ -329 +335 @@
     $dbh->do('CREATE INDEX subject_text ON subject(text)');
     $dbh->do(<<'END');
+    CREATE TABLE month (
+        id INTEGER PRIMARY KEY,
+        year INTEGER NOT NULL,
+        month INTEGER NOT NULL);
+END
+    $dbh->do('CREATE INDEX month_year_month ON month(year,month)');
+    $dbh->do(<<'END');
     CREATE TABLE thread (
         id INTEGER PRIMARY KEY,
+        month_id INTEGER,
         subject_id INTEGER,
         date INTEGER,
+        lastmsg_id INTEGER,
         prev INTEGER,
-        next INTEGER)
+        next INTEGER);
 END
     return $dbh;