phpBB.de

Code: Alles auswählen

#!/usr/bin/perl -w

#######################################################
# Perl script zum Erstellen einer Google-Sitemap      #
# Version 1.1                                         #
# Copyright by Holger Brinkhaus                       #
# Darf allgemein verwendet und weitergegeben werden,  #
# so lange der Hinweis auf den Autor enthalten bleibt #
#######################################################

use strict;
use DBI;

###############################################
# Folgende Konstanten müssen angepasst werden #
###############################################

# Vollständiger Pfad zum Ort der erstellten Sitemap #
my $SitemapPath = "/var/www/vhosts/MYDOMAIN.TLD/httpdocs/";

# URL des Forums #
my $SiteURL = "http://www.MYDOMAIN.TLD/";

# Name der Datenbank zu Forum #
my $dbName = "DBNAME";

# Name des Host der Datenbank #
my $dbHostName = "localhost";

# Prefix der phpbb-Tabellen #
my $dbPrefix = "phpbb_";

# Datenbankuser #
my $dbUser = "DBUSER";

# Passwort #
my $dbPassw = "DBPASSWD";

################################################
# Ab hier sollte nichts mehr angepasst werden  #
################################################

# Datenbank anbinden
my @dsn = ("DBI:mysql:database=" . $dbName . ";" .
           "host=" . $dbHostName, $dbUser, $dbPassw);
           
my $dbh = DBI->connect(@dsn,
    { PrintError => 0,
      AutoCommit => 1,
    }
    ) or die $DBI::errstr;


# Authorisierte Forenliste emitteln

my $sql = "SELECT DISTINCT f.forum_id, f.forum_topics, f.forum_last_post_time " .
          "FROM (" . $dbPrefix . "acl_groups AS a INNER JOIN " . $dbPrefix . "groups AS g ON a.group_id=g.group_id) INNER JOIN " . 
                $dbPrefix . "forums AS f ON f.forum_id=a.forum_id " .
          "WHERE g.group_name='BOTS' AND a.auth_role_id IN (10,14,15,17,19,21)";

my $fdata = $dbh->selectall_arrayref($sql)
                  or die $dbh->errstr();

# Standardwerte ermitteln
my $TopicsPerPage = fctConfigVal('topics_per_page');
my $PostsPerPage = fctConfigVal('posts_per_page');

# Alte Dateien loeschen

my @OldFiles = $SitemapPath . "site*.xml";
foreach (@OldFiles) {
	unlink($_);
}

 
 # Sitemaps erstellen
 
open (SFile, ">" . $SitemapPath . "sitemap.xml");

print SFile '<?xml version="1.0" encoding="UTF-8"?>'."\n";
print SFile '   <sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"' . "\n";
print SFile '   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' . "\n";
print SFile '   xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9' . "\n";
print SFile '   http://www.sitemaps.org/schemas/sitemap/0.9/siteindex.xsd">' . "\n\n";

for my $row (@$fdata) {
	my($ID, $Topics, $post_time) = @$row;
	
  if ($post_time != 0) {
  	subSchreibFSiteMap ($ID, $Topics, $post_time);
    print SFile '   <sitemap>' . "\n";
    print SFile '     <loc>' . $SiteURL . 'sitemapforum-' . $ID . '.xml</loc>' . "\n";
    print SFile '     <lastmod>' . fctISO_Datum ($post_time) . '</lastmod>' . "\n";
    print SFile '   </sitemap>' . "\n";
    }
  }
  
print SFile '   </sitemapindex>';

close SFile;
$dbh->disconnect();


# Funktionen

sub subSchreibFSiteMap {
	my $FID = shift;
	my $FTopics = shift;
	my $FPTime = shift;
	
  open (FFile, '>' . $SitemapPath . 'sitemapforum-' . $FID . '.xml');	
 
  print FFile '<?xml version="1.0" encoding="UTF-8"?>'."\n";
  print FFile '   <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"' . "\n";
  print FFile '   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' . "\n";
  print FFile '   xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9' . "\n";
  print FFile '   http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">' . "\n\n";
  
     # Forumseiten
  my $pages = int ($FTopics / $TopicsPerPage);
  if (($FTopics % $TopicsPerPage) == 0) {
  	$pages--;
    }
  for (my $i=0; $i<=$pages; $i++) {
    print FFile '   <url>' . "\n";
    if ($i == 0) {
    	print FFile '     <loc>' . $SiteURL . 'viewforum.php?f=' . $FID . '</loc>' . "\n";
    	print FFile '     <lastmod>' . fctISO_Datum ($FPTime) . '</lastmod>' . "\n";
    }
    else {
    	my $start = $i * $TopicsPerPage;
    	print FFile '     <loc>' . $SiteURL . 'viewforum.php?f=' . $FID . '&start=' . $start . '</loc>' . "\n";
    	print FFile '     <changefreq>weekly</changefreq>' . "\n";
    }
    print FFile '   </url>' . "\n";
  }
  
    # Topics
  my $tsql = 'SELECT topic_id, topic_replies, topic_last_post_time FROM ' . $dbPrefix . 'topics WHERE forum_id = ' . $FID . ' ORDER BY topic_last_post_time DESC';
  
  my $tdata = $dbh->selectall_arrayref($tsql) 
                        or die $dbh->errstr();
  
  for my $trow (@$tdata) {
	  my($TID, $Posts, $TPTime) = @$trow;
	  $Posts++;
  
    my $pages = int ($Posts / $PostsPerPage);
    if (($FTopics % $PostsPerPage) == 0) {
  	  $pages--;
      }
    for (my $i=0; $i<=$pages; $i++) {
    	print FFile '   <url>' . "\n";
    	if ($i == 0) {
      	print FFile '     <loc>' . $SiteURL . 'viewtopic.php?f=' . $FID . '&t=' . $TID . '</loc>' . "\n";
      	print FFile '     <lastmod>' . fctISO_Datum ($TPTime) . '</lastmod>' . "\n";    		
      }
      else {
      	my $start = $i * $PostsPerPage;
      	print FFile '     <loc>' . $SiteURL . 'viewtopic.php?f=' . $FID . '&t=' . $TID . '&start=' . $start  . '</loc>' . "\n";
      	print FFile '     <lastmod>' . fctISO_Datum ($TPTime) . '</lastmod>' . "\n";
      	print FFile '     <priority>0.3</priority>' . "\n"
      }
    	print FFile '   </url>' . "\n";
	  }
  }
  
  print FFile '   </urlset>';
  
  close FFile;
}


sub fctISO_Datum {
	my $SQL_Datum = shift;
	
	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime($SQL_Datum);
  my $t = sprintf "%4d-%02d-%02dT%02d:%02d:%02dZ", 1900+$year,$mon+1,$mday,$hour,$min,$sec;
  return $t;
}


sub fctConfigVal {
  my $CName = shift;
  
  my $Csql = "SELECT config_value FROM " . $dbPrefix . "config WHERE config_name='" . $CName . "'";
  my ($Val) = $dbh->selectrow_array ($Csql) 
                     or die $dbh->errstr();
  return $Val;
}