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.
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)
open $OUT, “>/Library/WebServer/Documents/blosxom/$row.txt”;
print $OUT “$row\nmeta-creation_timestamp: $row\n\n$row”;
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.