Wiki:Dokumentation/2025-08-20: Unterschied zwischen den Versionen

aus GenWiki, dem genealogischen Lexikon zum Mitmachen.
Zur Navigation springen Zur Suche springen
Keine Bearbeitungszusammenfassung
 
(4 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:


= SQL Abfragen =
== user_count ==
<source lang='bash'>
<source lang='bash'>
sqlquery -en genwiki -qp queries.yaml -qn user_count -f mediawiki
sqlquery -en genwiki -qp queries.yaml -qn user_count -f mediawiki
</source>
</source>
== user_count ==
=== query ===
=== query ===
<source lang='sql'>
<source lang='sql'>
Zeile 42: Zeile 42:
== find_pages ==
== find_pages ==
<source lang='bash'>
<source lang='bash'>
sqlquery -en genwiki -qp queries.yaml -qn find_pages  --params pattern="{{subpage",limit=10 -f mediawiki
sqlquery -en genwiki -qp queries.yaml -qn find_pages  --params pattern="{{#css:",limit=10 -f mediawiki
</source>
</source>
=== query ===
=== query ===
<source lang='sql'>
<source lang='sql'>
SELECT  
SELECT
   t.old_id AS SeitenId,
   r.rev_id AS SeitenId,
   CONCAT('[[', p.page_title, ']]') AS Seite,
   CONCAT('[[', p.page_title, ']]') AS Seite,
  CONCAT('https://wiki.genealogy.net/index.php?oldid=', r.rev_id) AS Link,
   r.rev_timestamp AS Datum
   r.rev_timestamp AS Datum
FROM text t
FROM text t
JOIN revision r ON t.old_id = r.rev_id
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
JOIN page p ON r.rev_page = p.page_id
WHERE t.old_text LIKE "%{{subpage%"
WHERE t.old_text LIKE "%{{#css:%"
LIMIT 10
LIMIT 10


Zeile 65: Zeile 66:
|+ <!-- caption -->
|+ <!-- caption -->
|-
|-
! align="right"|  SeitenId !! Seite                                                   !! align="right"|          Datum
! align="right"|  SeitenId !! Seite               !! Link                                              !! align="right"|          Datum
|-
|-
| align="right"|    1196196 || [[121._Infanterie-Division_(WK1)]]                     || align="right"| 20130304165544
| align="right"|    2361829 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361829 || align="right"| 20230306100941
|-
|-
| align="right"|    1952199 || [[16._Landwehr-Division_(WK1)]]                         || align="right"| 20181124134307
| align="right"|    2361850 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361850 || align="right"| 20230306111218
|-
|-
| align="right"|    1208619 || [[2._KB_Division_(Alte_Armee)]]                         || align="right"| 20130321191000
| align="right"|    2361853 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361853 || align="right"| 20230306112026
|-
|-
| align="right"|    1952208 || [[21._Landwehr-Division_(WK1)]]                         || align="right"| 20181124135019
| align="right"|    2361856 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361856 || align="right"| 20230306114149
|-
|-
| align="right"|    1196197 || [[233._Infanterie-Division_(WK1)]]                     || align="right"| 20130304165700
| align="right"|    2361857 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361857 || align="right"| 20230306114433
|-
|-
| align="right"|    1196189 || [[236._Infanterie-Division_(WK1)]]                     || align="right"| 20130304164853
| align="right"|    2361858 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361858 || align="right"| 20230306114648
|-
|-
| align="right"|    1747350 || [[49._Infanterie-Brigade_(1.Großherzoglich_Hessische)]] || align="right"| 20160930201935
| align="right"|    2361859 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361859 || align="right"| 20230306114851
|-
|-
| align="right"|    1567235 || [[59._Landwehr-Infanterie-Brigade]]                     || align="right"| 20150315142835
| align="right"|    2361860 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361860 || align="right"| 20230306114958
|-
|-
| align="right"|    2011337 || [[6._(kgl.bayer.)_Infanterie-Brigade]]                  || align="right"| 20191025131444
| align="right"|    2361931 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361931 || align="right"| 20230306203901
|-
|-
| align="right"|     810263 || [[Aachen/Adressbuch_1936]]                             || align="right"| 20100417110938
| align="right"|   2361933 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361933 || align="right"| 20230306204118
|}
|}
= Installation =
<source lang='bash'>
pipx install pylodstorage
</source>
== queries.yaml ==
in $HOME/.pylodstorage
<source lang='yaml'>
# 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 }}
</source>
== endpoints.yaml ==
in $HOME/.pylodstorage
<source lang='yaml'>
# WF 2025-08-20
# endpoint configuration
endpoints:
  genwiki:
    lang: sql
    database: *****
    endpoint: jdbc:mysql
    host: *****
    port: *****
    user: ****
    password: *****
    charset: utf8mb4
</source>
[[Kategorie:GenWiki:Weiterentwicklung]]

Aktuelle Version vom 21. August 2025, 06:28 Uhr

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
Abgerufen von „https://genwiki39.genealogy.net/index.php?title=Wiki:Dokumentation/2025-08-20&oldid=2625432“