Wiki:Dokumentation/2025-08-20

SQL Abfragen

user_count

sqlquery -en genwiki -qp queries.yaml -qn user_count -f mediawiki

query

select count(*) from user;

result

count(*)
17403

table_count

sqlquery -en genwiki -qp queries.yaml -qn table_count  --params table=image -f mediawiki

query

select count(*) from image;

result

count(*)
91394

find_pages

sqlquery -en genwiki -qp queries.yaml -qn find_pages  --params pattern="{{#css:",limit=10 -f mediawiki

query

SELECT
   r.rev_id AS SeitenId,
   CONCAT('[[', p.page_title, ']]') AS Seite,
   CONCAT('https://wiki.genealogy.net/index.php?oldid=', r.rev_id) AS Link,
   r.rev_timestamp AS Datum
FROM text t
JOIN content c ON t.old_id = REPLACE(c.content_address, 'tt:', '')
JOIN slots s ON c.content_id = s.slot_content_id
JOIN revision r ON s.slot_revision_id = r.rev_id
JOIN page p ON r.rev_page = p.page_id
WHERE t.old_text LIKE "%{{#css:%"
LIMIT 10

result

SeitenId	Seite	Link	Datum
2361829	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361829	20230306100941
2361850	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361850	20230306111218
2361853	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361853	20230306112026
2361856	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361856	20230306114149
2361857	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361857	20230306114433
2361858	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361858	20230306114648
2361859	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361859	20230306114851
2361860	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361860	20230306114958
2361931	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361931	20230306203901
2361933	Portal:Software	https://wiki.genealogy.net/index.php?oldid=2361933	20230306204118

Installation

pipx install pylodstorage

queries.yaml

in $HOME/.pylodstorage

# SQL Queries auf CompGen MediaWiki Datenbank
#  Wolfgang Fahl 2025-04-12
user_count:
  sql: |
    select count(*) from user;
tables:
  sql: |
    show tables
table_count:
  param_list:
    - name: table
      type: str
      default_value: user
  sql: |
    select count(*) from {{ table }};
find_pages:
  # find pages with a given pattern
  # https://stackoverflow.com/a/73109261/1497139
  param_list:
    - name: pattern
      type: str
      default_value: "{{subpage"
    - name: limit
      type: int
      default_value: 10
  sql: |
    SELECT
       r.rev_id AS SeitenId,
       CONCAT('[[', p.page_title, ']]') AS Seite,
       CONCAT('https://wiki.genealogy.net/index.php?oldid=', r.rev_id) AS Link,
       r.rev_timestamp AS Datum
    FROM text t
    JOIN content c ON t.old_id = REPLACE(c.content_address, 'tt:', '')
    JOIN slots s ON c.content_id = s.slot_content_id
    JOIN revision r ON s.slot_revision_id = r.rev_id
    JOIN page p ON r.rev_page = p.page_id
    WHERE t.old_text LIKE "%{{ pattern }}%"
    LIMIT {{ limit }}

endpoints.yaml

in $HOME/.pylodstorage

# WF 2025-08-20
# endpoint configuration
endpoints:
   genwiki:
    lang: sql
    database: *****
    endpoint: jdbc:mysql
    host: ***** 
    port: *****
    user: ****
    password: ***** 
    charset: utf8mb4