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

aus GenWiki, dem genealogischen Lexikon zum Mitmachen.
Zur Navigation springen Zur Suche springen
Keine Bearbeitungszusammenfassung
Keine Bearbeitungszusammenfassung
 
(7 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 39: Zeile 39:
| align="right"|      91394
| align="right"|      91394
|}
|}
== find_pages ==
<source lang='bash'>
sqlquery -en genwiki -qp queries.yaml -qn find_pages  --params pattern="{{#css:",limit=10 -f mediawiki
</source>
=== query ===
<source lang='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 "%{{#css:%"
LIMIT 10
</source>
=== result ===
{| class="wikitable" style="text-align: left;"
|+ <!-- caption -->
|-
! align="right"|  SeitenId !! Seite              !! Link                                              !! align="right"|          Datum
|-
| align="right"|    2361829 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361829 || align="right"| 20230306100941
|-
| align="right"|    2361850 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361850 || align="right"| 20230306111218
|-
| align="right"|    2361853 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361853 || align="right"| 20230306112026
|-
| align="right"|    2361856 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361856 || align="right"| 20230306114149
|-
| align="right"|    2361857 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361857 || align="right"| 20230306114433
|-
| align="right"|    2361858 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361858 || align="right"| 20230306114648
|-
| align="right"|    2361859 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361859 || align="right"| 20230306114851
|-
| align="right"|    2361860 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361860 || align="right"| 20230306114958
|-
| align="right"|    2361931 || [[Portal:Software]] || https://wiki.genealogy.net/index.php?oldid=2361931 || align="right"| 20230306203901
|-
| 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“