Perrypedia:UserCreates

Aus Perrypedia
Zur Navigation springen Zur Suche springen

Die beiden Seiten Vorlage:UserCreates und Vorlage:UserCreates20 werden täglich automatisch aktualisiert.

1. In der  Wikipedia-logo.png MySQL-Datenbank ist der event scheduler aktiviert.

Konfigurationsdatei /etc/mysql/conf.d/mysqld.cnf: 

event_scheduler = on
secure_file_priv = /var/lib/mysql-files

2. Die Auswertung der Neuanlagen erfolgt durch stored procedures, die vom event scheduler um 23.47 Uhr und um 23.52 Uhr aufgerufen werden. Die stored procedures erzeugen zwei Dateien:

  • /var/lib/mysql-files/UserCreates.tmp
  • /var/lib/mysql-files/UserCreates20.tmp

Die Tabelle revision_actor_temp wird übergangsweise aufgrund einer mehrstufigen Umstellung des Datenbankschemas benötigt, Details siehe hier. 

-- MySQL dump 10.13  Distrib 8.0.28, for Linux (x86_64)
--
-- Host: localhost    Database: ppdb
-- ------------------------------------------------------
-- Server version	8.0.28-0ubuntu0.20.04.3

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Dumping events for database 'ppdb'
--
/*!50106 SET @save_time_zone= @@TIME_ZONE */ ;
/*!50106 DROP EVENT IF EXISTS `RefreshUserCreates` */;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection  = utf8mb4_0900_bin */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = '' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `RefreshUserCreates` ON SCHEDULE EVERY 1 DAY STARTS '2016-10-29 23:47:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL UserCreates() */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
/*!50106 DROP EVENT IF EXISTS `RefreshUserCreates20` */;;
DELIMITER ;;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;;
/*!50003 SET character_set_client  = utf8mb4 */ ;;
/*!50003 SET character_set_results = utf8mb4 */ ;;
/*!50003 SET collation_connection  = utf8mb4_0900_bin */ ;;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;;
/*!50003 SET sql_mode              = '' */ ;;
/*!50003 SET @saved_time_zone      = @@time_zone */ ;;
/*!50003 SET time_zone             = 'SYSTEM' */ ;;
/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `RefreshUserCreates20` ON SCHEDULE EVERY 1 DAY STARTS '2016-10-29 23:52:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL UserCreates20() */ ;;
/*!50003 SET time_zone             = @saved_time_zone */ ;;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;;
/*!50003 SET character_set_client  = @saved_cs_client */ ;;
/*!50003 SET character_set_results = @saved_cs_results */ ;;
/*!50003 SET collation_connection  = @saved_col_connection */ ;;
DELIMITER ;
/*!50106 SET TIME_ZONE= @save_time_zone */ ;

--
-- Dumping routines for database 'ppdb'
--
/*!50003 DROP PROCEDURE IF EXISTS `UserCreates` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_bin */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UserCreates`()
BEGIN
SELECT user_name, count(*) AS total from revision, revision_actor_temp, actor, user where rev_id=revactor_rev AND revactor_actor=actor_id AND actor_user=user_id AND rev_parent_id=0 AND rev_page IN ( select page_id from page where page_namespace=0 and page_is_redirect=0 ) GROUP BY user_name ORDER BY total DESC INTO OUTFILE '/var/lib/mysql-files/UserCreates.tmp' FIELDS TERMINATED BY '|';
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `UserCreates20` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_0900_bin */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UserCreates20`()
BEGIN
SELECT user_name, count(*) AS total from revision, revision_actor_temp, actor, user where rev_id=revactor_rev AND revactor_actor=actor_id AND actor_user=user_id AND rev_parent_id=0 AND rev_page IN ( select page_id from page where page_namespace=0 and page_is_redirect=0 ) GROUP BY user_name ORDER BY total DESC LIMIT 20 INTO OUTFILE '/var/lib/mysql-files/UserCreates20.tmp' FIELDS TERMINATED BY '|';
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-03-28 17:45:40

3. Ein Shellskript, per  Wikipedia-logo.png Cron täglich gestartet um 23.58 Uhr, übernimmt die Daten in die Perrypedia.

/var/www/UserCreates.sh: 

#!/bin/bash
# Die Auswertungen werden durch Stored Procedures in MySQL erstellt.
cd /var/tmp
mv -f /var/lib/mysql-files/UserCreates20.tmp .
mv -f /var/lib/mysql-files/UserCreates.tmp .

# UserCreates20
echo "{|class=\"perrypedia_std_table sortable\" style=\"width:50%;\"" > page.txt
echo "! Benutzer" >> page.txt
echo "! Creates" >> page.txt
echo "|----" >> page.txt
while IFS= read -r line
do
	echo "|${line%|*}" >> page.txt                  # nur der Benutzername
	echo "|<center>${line#*|}</center>" >> page.txt # nur die Anzahl
	echo "|----" >> page.txt
done < UserCreates20.tmp
echo "|}" >> page.txt
echo "Stand: $(date +'%d.%m.%Y')" >> page.txt
echo "Dokumentation: siehe [[Perrypedia:UserCreates]]." >> page.txt
echo "<noinclude>[[Kategorie:Textbausteine|{{SortKey}}]]</noinclude>" >> page.txt
mv -f page.txt UserCreates20.txt

# UserCreates
echo "{|class=\"perrypedia_std_table sortable\" style=\"width:50%;\"" > page.txt
echo "! Benutzer" >> page.txt
echo "! Creates" >> page.txt
echo "|----" >> page.txt
while IFS= read -r line
do
        echo "|${line%|*}" >> page.txt                  # nur der Benutzername
        echo "|<center>${line#*|}</center>" >> page.txt # nur die Anzahl
        echo "|----" >> page.txt
done < UserCreates.tmp
echo "|}" >> page.txt
echo "Stand: $(date +'%d.%m.%Y')" >> page.txt
echo "Dokumentation: siehe [[Perrypedia:UserCreates]]." >> page.txt
echo "<noinclude>[[Kategorie:Textbausteine|{{SortKey}}]]</noinclude>" >> page.txt
mv -f page.txt UserCreates.txt

# in das Wiki importieren
cd /var/www/prod/mediawiki/maintenance
php importTextFiles.php --rc --bot --overwrite --prefix "Vorlage:" /var/tmp/UserCreates20.txt
php importTextFiles.php --rc --bot --overwrite --prefix "Vorlage:" /var/tmp/UserCreates.txt

4. Hinweis: Die stored procedures und den event scheduler gibt es nur in der Echtumgebung, nicht im Testwiki. Sie werden in der täglichen Datensicherung separat gesichert.

Abgerufen von „http://www.perrypedia.de/mediawiki/index.php?title=Perrypedia:UserCreates&oldid=1842611“