As part of my plan to reduce MacMegasite’s hefty resource usage, I’m reducing the size of the database by archiving all stories from 2004 or earlier as static pages. Using a simple Perl script, I exported each node as a text file, which I then rendered as static pages using Blosxom.
Here’s the script I used. I’m also using Blosxom’s entries_index_tagged plugin to use the actual creation date for each article rather than the day I exported them.
#!/usr/bin/perl -w
use strict;
use DBI;my @row;
my $host = ‘localhost’;
my $db = ‘database’;
my $db_user = ‘user’;
my $db_password = ‘whatever’;# Connect to the requested server
my $dbh = DBI->connect(“dbi:mysql:$db:$host”, “$db_user”, “$db_password”)
or die(“Cannot connect to the database”);my $sql = “select nid,title,created,body from node where YEAR(FROM_UNIXTIME(created))<2005”;
my $sth = $dbh->prepare($sql);
$sth->execute or die “could not execute SQL”;while (@row = $sth->fetchrow_array)
{
my $OUT;
open $OUT, “>/Library/WebServer/Documents/blosxom/$row[0].txt”;
print $OUT “$row[1]\nmeta-creation_timestamp: $row[2]\n\n$row[3]”;
close $OUT;
}
I exported the data from a backup copy of the database on my powerbook and then rendered it with Blosxom on my machine. Finally, I uploaded the rendered directory of static pages to MacMegasite. I never installed Blosxom on the actual server – everything I uploaded is pure HTML. Finally, I deleted all nodes earlier than 2004 from the database.