Test - Quarry

SQL

-- Sample Quarry Query to Extract Usernames from Wikitext
USE ckbwiki_p;

-- Replace 'بەکارھێنەر:Aram/تاقیکردنەوە' with the desired page title
-- Make sure the page title is properly URL-encoded if necessary
-- For example, spaces should be replaced with underscores
SET @pageTitle = 'بەکارھێنەر:Aram/تاقیکردنەوە';

-- Retrieve the wikitext of the specified page
SELECT rev_text
FROM revision
WHERE rev_page = (SELECT page_id FROM page WHERE page_title = @pageTitle)
ORDER BY rev_timestamp DESC
LIMIT 1;

-- Extract usernames from the wikitext using a regular expression
-- This query assumes that usernames are enclosed in double square brackets
WITH page_wikitext AS (
  SELECT rev_text
  FROM revision
  WHERE rev_page = (SELECT page_id FROM page WHERE page_title = @pageTitle)
  ORDER BY rev_timestamp DESC
  LIMIT 1
)
SELECT DISTINCT
  REGEXP_REPLACE(
    -- Regular expression to match usernames in double square brackets
    REGEXP_CAPTURE(page_wikitext.rev_text, r'\[\[User:([^|\]]+)(?:\|[^|\]]+)?\]\]'),
    -- Capture group containing the username
    r'\[\[User:([^|\]]+)(?:\|[^|\]]+)?\]\]',
    '\\1'
  ) AS username
FROM page_wikitext
WHERE page_wikitext.rev_text LIKE '%[[User:%';

-- This query retrieves and lists the extracted usernames
SQL

Checking query status...
