User:Jarekt/SQL
Jump to navigation
Jump to search
SQL 1[edit]
I would like a list of files on Commons which meet 3 conditions:
- file belongs to subcategories of Category:Creator template home categories
- transcludes Template:PD-Layout (files with PD license)
- 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]
- Detect transcluded templates with duplicate arguments
- Commons files to add to "Media_missing_infobox_template" category
- Find commons pages unable to access templates after SUL user renames
- Commons images without license
- Non-primary licenses
- Licenses without primary or custom category
- Commons license templates with no transclusions, and their authors
- https://wikitech.wikimedia.org/wiki/Nova_Resource:Tools/Shared_Resources/MySQL_queries