Fork of
Untitled query #29651
by ShakespeareFan00
This query is marked as a draft
This query has been published
by ShakespeareFan00.
SQL
x
USE enwiki_p;
SELECT p.page_id, p.page_len, CONCAT("File:",p.page_title) as page_title FROM page p
INNER JOIN categorylinks c ON c.cl_from = p.page_id AND c.cl_to = 'Files_with_no_machine-readable_source'
LEFT JOIN categorylinks cnot ON cnot.cl_from = p.page_id AND cnot.cl_to
IN ('Wikipedia_files_tagged_as_own_work',
'All_non-free_media',
'Self-published_work',
'Wikipedia_files_for_discussion',
'All_Wikipedia_files_missing_evidence_of_permission',
'All_Wikipedia_files_with_unknown_source',
'Redirects_from_moves',
'Items_with_OTRS_permission_confirmed',
'All_Wikipedia_files_with_unknown_copyright_status',
'Wikipedia_files_with_unconfirmed_permission_received_by_OTRS',
'Items_pending_OTRS_confirmation_of_permission')
LEFT JOIN templatelinks t ON t.tl_from = p.page_id
AND t.tl_namespace = 10
AND t.tl_title IN ('Protected_image',
# Genrally accetped as PD
'PD-textlogo','PD-Old','PD-US','PD-1923','PD-old-100','PD-old-70','PD-URAA','PD-USGov-NASA','PD-USGov',
# Also exclude various deletion processes:
'Ffd',
'Deletable_file',
'Proposed_deletion/dated_files'
'Now_Commons',
# Also exclude files with disputed copyright status...
'NFUR_not_needed',
# ...,Information presented in others ways,...
'Google_Art_Project',
'Imgnote-hassource',
'Spoken_article_entry',
'Used_in_system','Pp-upload','Pp-template',
'Painting',
'Image_information_art',
'OTRS_source',
#.. and stuff that's less of a priority, or issue for other reasons.
'Keep_local_high-risk',
'Wikipedia-screenshot',
'Split-media',
'Split_media',
'Bsr',
'Img-claimed',
'Img-unclaimed',
'Media_by_uploader'
)
WHERE t.tl_from IS NULL
AND cnot.cl_from IS NULL
AND p.page_namespace = 6
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.