blob: 0363fd44ba07cdd80ef11204f4821e9cfefaf028 (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
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 '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
print TEMP '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'
. "\n";
for ( my $i = 0 ; $i < 50000 ; $i++ ) {
my ( $nick, $mid ) = $sth->fetchrow() or goto NEXT;
print TEMP '<url><loc>https://juick.com/'
. $nick . '/'
. $mid
. '</loc></url>' . "\n";
}
NEXT: print TEMP '</urlset>' . "\n";
}
open( TEMP, ">_sitemap-users.xml" );
print TEMP '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
print TEMP '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'
. "\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 =
'<url><loc>https://juick.com/'
. CGI::escape($nick)
. '/</loc><lastmod>'
. $lastmod
. '</lastmod></url>' . "\n";
print $data;
print TEMP $data;
}
print TEMP '</urlset>' . "\n";
open( TEMP, ">_sitemap-tags.xml" );
print TEMP '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
print TEMP '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'
. "\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 '<url><loc>https://juick.com/tag/'
. CGI::escape($tag)
. '</loc></url>' . "\n";
}
print TEMP '</urlset>' . "\n";
open( TEMP, ">_sitemap-index.xml" );
print TEMP '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
print TEMP '<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'
. "\n";
print TEMP '<sitemap><loc>https://juick.com/_sitemap-users.xml</loc></sitemap>'
. "\n";
print TEMP '<sitemap><loc>https://juick.com/_sitemap-tags.xml</loc></sitemap>'
. "\n";
for ( my $next = 0 ; $next < $filescount ; $next++ ) {
print TEMP '<sitemap><loc>https://juick.com/_sitemap-messages'
. $next
. '.xml</loc></sitemap>' . "\n";
}
print TEMP '</sitemapindex>' . "\n";
|
