From dd23559a978da8980675ad4089948ade9bbc323d Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Mon, 30 Jan 2023 21:23:30 +0300 Subject: scripts: sitemap.pl --- scripts/sitemap.pl | 81 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 81 insertions(+) create mode 100644 scripts/sitemap.pl diff --git a/scripts/sitemap.pl b/scripts/sitemap.pl new file mode 100644 index 00000000..0363fd44 --- /dev/null +++ b/scripts/sitemap.pl @@ -0,0 +1,81 @@ +#!/usr/bin/perl + +use strict; +use DBI; +use CGI; +use utf8; + +my $dbh = DBI->connect("dbi:Pg:dbname=juick"); + +my $sql = +"SELECT users.nick,messages.message_id FROM messages INNER JOIN users ON messages.user_id=users.id WHERE users.banned=0 AND messages.privacy=1"; +my $sth = $dbh->prepare($sql) or die; +$sth->execute or die; +my $filescount = int( $sth->rows / 50000 ) + 1; +for ( my $next = 0 ; $next < $filescount ; $next++ ) { + open( TEMP, ">_sitemap-messages" . $next . ".xml" ); + print TEMP '' . "\n"; + print TEMP '' + . "\n"; + for ( my $i = 0 ; $i < 50000 ; $i++ ) { + my ( $nick, $mid ) = $sth->fetchrow() or goto NEXT; + print TEMP 'https://juick.com/' + . $nick . '/' + . $mid + . '' . "\n"; + } + NEXT: print TEMP '' . "\n"; +} +open( TEMP, ">_sitemap-users.xml" ); +print TEMP '' . "\n"; +print TEMP '' + . "\n"; + +my $sql = +'SELECT nick, to_char(lastmessage, \'YYYY-MM-DD"T"HH24:MI:SS"Z"\') FROM users INNER JOIN messages ON users.id=messages.user_id WHERE users.banned=0 GROUP BY users.id HAVING COUNT(message_id)>4'; +my $sth = $dbh->prepare($sql) or die; +$sth->execute or die; +while ( my ( $nick, $lastmod ) = $sth->fetchrow ) { + my $data = + 'https://juick.com/' + . CGI::escape($nick) + . '/' + . $lastmod + . '' . "\n"; + print $data; + print TEMP $data; +} + +print TEMP '' . "\n"; + +open( TEMP, ">_sitemap-tags.xml" ); +print TEMP '' . "\n"; +print TEMP '' + . "\n"; + +my $sql = +"SELECT tags.name FROM tags WHERE noindex=0 AND synonym_id IS NULL AND stat_messages>9 AND stat_users>2"; +my $sth = $dbh->prepare($sql) or die; +$sth->execute or die; +while ( my ($tag) = $sth->fetchrow ) { + print TEMP 'https://juick.com/tag/' + . CGI::escape($tag) + . '' . "\n"; +} + +print TEMP '' . "\n"; + +open( TEMP, ">_sitemap-index.xml" ); +print TEMP '' . "\n"; +print TEMP '' + . "\n"; +print TEMP 'https://juick.com/_sitemap-users.xml' + . "\n"; +print TEMP 'https://juick.com/_sitemap-tags.xml' + . "\n"; +for ( my $next = 0 ; $next < $filescount ; $next++ ) { + print TEMP 'https://juick.com/_sitemap-messages' + . $next + . '.xml' . "\n"; +} +print TEMP '' . "\n"; -- cgit v1.2.3