Tutorial: Workaround to create a sitemap

[Arise]

I've managed to create the sitemap for my blog.

Requirements:
- shell access to the linux machine (you must have to create the files, to acccess the mysql database and change the cron table)
Ofcourse, you can do that or something similar on other operating systems.


I've created 2 scripts:

-------------- BEGIN sitemap.sh--------------------
#!/bin/sh
echo '<?xml version="1.0" encoding="UTF-8"?>' > sitemap.xml
echo '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"' >> sitemap.xml
echo 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"' >> sitemap.xml
echo 'xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9' >> sitemap.xml
echo 'http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">' >> sitemap.xml
echo -n '<url><loc>http://programaresociala.ro</loc><lastmod>' >> sitemap.xml
echo -n "$(date '+%Y-%m-%d')" >> sitemap.xml
echo -n '</lastmod><changefreq>daily</changefreq><priority>1.0</priority></url>' >> sitemap.xml
mysql -h localhost -u username -ppassword < sitemap.sql >> sitemap.xml
echo '</urlset>' >> sitemap.xml
cp sitemap.xml /usr/local/apache-tomcat-5.5.25/webapps/programaresociala.ro/
-------------- END sitemap.sh--------------------

--------------BEGIN sitemap.sql--------------------
select CONCAT('<url><loc>','http://programaresociala.ro/',DATE_FORMAT(entry_date, '%Y/%m/%d/'),post_slug,'</loc><lastmod>',DATE_FORMAT(modified_date, '%Y-%m-%d'),'</lastmod><changefreq>weekly</changefreq><priority>0.8</priority></url>') as '' from blojsom.Entry  where status='published' order by entry_date DESC
--------------END sitemap.sql--------------------


The file sitemap.sh must have execute flag set. Then add it to your cron add as a line to execute it once a day:
Example:
# Create sitemap for programaresociala.ro at 4:55 every day:
55 4 * * * /maintenance/sitemap.sh /etc/cron.daily 1> /dev/null


How does everything works:
The cron script runs the sitemap.sh which in turns create the sitemap.xml file, add some lines to it, then executes sitemap.sql which adds some more lines, then the file sitemap.xml is closed and copied to your website directory.