User:Jarekt/SQL

From Wikimedia Commons, the free media repository
Jump to navigation Jump to search
MediaWiki 1.19 database schema
MediaWiki 1.24.1 database schema

SQL 1[edit]

I would like a list of files on Commons which meet 3 conditions:

  1. file belongs to subcategories of Category:Creator template home categories
  2. transcludes Template:PD-Layout (files with PD license)
  3. does not transclude template:Creator

Those files will be likely candidates for adding one of the Category:Creator templates. 

SELECT /* SLOW_OK */  page.page_title as file 
FROM page
INNER JOIN categorylinks 
  ON page.page_id = categorylinks.cl_from
INNER JOIN templatelinks 
  ON templatelinks.tl_from = page.page_id
LEFT JOIN (SELECT COUNT(*) as bool, tl_from FROM templatelinks WHERE templatelinks.tl_title = 'Creator' GROUP BY tl_from) as tlinks 
  ON tlinks.tl_from = page.page_id
WHERE 
  page.page_namespace = 6 AND 
  categorylinks.cl_to IN
   (SELECT page.page_title 
    FROM page
    INNER JOIN categorylinks 
    ON categorylinks.cl_from = page.page_id 
    WHERE 
      categorylinks.cl_to = 'Creator_template_home_categories' AND 
      page.page_namespace=14
   ) AND 
  templatelinks.tl_title = 'PD-Layout' AND
  isnull(tlinks.bool);

SQL 2[edit]

Find files in Commons containing string "www.wga.hu" which are not in Category:Web Gallery of Art or its subcategory Category:Images from Web Gallery of Art. 

SELECT page_title as file 
FROM image 
 INNER JOIN page ON img_name = page_title 
WHERE 
  page.page_namespace = 6 AND 
  img_description LIKE '%www.wga.hu%' AND 
  NOT EXISTS (
    SELECT 1 FROM categorylinks 
    WHERE (categorylinks.cl_to = 'Web_Gallery_of_Art' 
       OR categorylinks.cl_to = 'Images_from_Web_Gallery_of_Art') 
       AND categorylinks.cl_from = page.page_id
  );


SQL 3[edit]

Number of transclusions of Wikimedia Commons creator templates for all templates in comm:Category:Creator templates 

select concat("Creator:",page_title), count(*) as tot
from 
  page 
  join categorylinks on page_id = cl_from
  join templatelinks on page_title = tl_title and page_namespace = tl_namespace
where 
  cl_to='Creator_templates'
  and tl_namespace = 100
group by page_id
order by tot desc, page_title;

SQL 4[edit]

Commons:Category:Institution template stubs contains bot created stubs of institution templates (kept in custom namespace "Institution:" ns:106 ). I would like to know number of transclusions of each of those templates. 

SELECT templatelinks.tl_title as title, COUNT(*) as transclusion_count 
FROM categorylinks 
INNER JOIN page ON categorylinks.cl_from = page.page_id 
INNER JOIN templatelinks ON templatelinks.tl_title = page.page_title AND page.page_namespace = templatelinks.tl_namespace 
WHERE cl_to = 'Institution_template_stubs' GROUP BY templatelinks.tl_title;

{{Works of authors who died less than 65 years ago}}+{{PD-old}}[edit]

SELECT /* SLOW_OK */ page.page_title 
FROM 
  page
  INNER JOIN templatelinks as template1 ON template1.tl_from = page.page_id
  INNER JOIN templatelinks as template2 ON template2.tl_from = page.page_id
WHERE
  template1.tl_title = 'Works of authors who died less than 65 years ago' and
  template2.tl_title in ('PD-old', 'PD-old-70', 'PD-old-100') and
  page.page_namespace = 6

No license Query[edit]

select /* SLOW_OK */ page_title 
from page 
where 
  page_is_redirect=0 and 
  page_namespace=6 and 
  not exists (
    select * 
    from templatelinks 
    where 
      tl_from=page_id and 
      tl_namespace=10 and 
      tl_title in ("License_template_tag","GNU-Layout","No_license","Delete","Speedydelete","Remove_this_line_and_insert_a_license_instead") 
    limit 1 
  )

Proposed "no infobox" Query[edit]

select /* SLOW_OK */ page_title 
from page 
where 
  page_is_redirect=0 and 
  page_namespace=6 and 
  not exists (
    select * 
    from templatelinks, categorylinks
    where 
      tl_from=page_id and 
      tl_namespace=10 and 
      cl_from=page_id and 
      (tl_title in ("Information","Infobox_template_tag") or
      cl_to in ("Media_missing_infobox_template", "Artworks missing infobox template‎", "Items with OTRS permission missing infobox template‎", "Pages using Information template with parsing errors‎") )  
    limit 1 
  )

/* Commons files without an infobox and not in "Media_missing_infobox_template" Category */
use commonswiki_p;

select /* SLOW_OK */ page_title 
from page p1
where 
  p1.page_namespace=6 and          -- files only
  p1.page_is_redirect=0 and        -- no redirects
  p1.page_title not like "%/%" and -- skip rare image subpages 
  not exists ( -- file does not transclude "Information" or "Infobox_template_tag" template
    select * 
    from templatelinks 
    where 
      tl_from = p1.page_id and 
      tl_namespace=10 and 
      tl_title in ("Information","Infobox_template_tag") -- all official infoboxes (except "information") use empty "Infobox_template_tag" template
    limit 1 
  ) and
  not exists ( -- file not already in "Media_missing_infobox_template" or subcategories
    select * 
    from categorylinks c1
    where 
      c1.cl_from=page_id and 
      c1.cl_to in (SELECT p2.page_title -- subcategories of "Media_missing_infobox_template"
                   FROM page p2
                   INNER JOIN categorylinks c2 on c2.cl_from = p2.page_id 
                   WHERE p2.page_namespace=14 
                   AND (c2.cl_to = "Media_missing_infobox_template" OR p2.page_title="Media_missing_infobox_template")
                  )
    limit 1 
  )

Find commons pages unable to access templates after SUL user renames[edit]

select p1.page_namespace, p1.page_title, tl_title 
from 
  templatelinks 
  inner join page p1 on 
    tl_from = p1.page_id 
  left outer join page p2 on 
    (p2.page_namespace = 2 and 
    p2.page_title = tl_title) 
  inner join logging_logindex on 
    log_namespace = 2 and 
    log_title = tl_title and 
    log_type = "move" and 
    log_params like "%~%" 
where 
  tl_namespace = 2  and 
  tl_from_namespace in ( 0, 1, 4, 6, 7) and 
  p2.page_title is null

Queries[edit]

Retrieved from "https://commons.wikimedia.org/w/index.php?title=User:Jarekt/SQL&oldid=180614539"