Fork of
Untitled query #30997
by ShakespeareFan00
This query is marked as a draft
This query has been published
by ShakespeareFan00.
SQL
x
USE enwiki_p;
SELECT
## CONCAT("User:",i.img_user_text) as username,
##p.page_latest,
##p.page_id,
##p.page_len,
CONCAT("File:",p.page_title) as page_title,
i.img_user_text,
##i.img_name,
i.img_timestamp
##i.img_sha1
# Cross reference pages and images by page_tile ( I.E we want the Titular image for each 'page'
FROM page p
INNER JOIN image i ON i.img_name=p.page_title AND p.page_namespace=6
# only want files in a specfic category
## INNER JOIN categorylinks c ON c.cl_from = p.page_id AND c.cl_to = 'Files_with_no_machine-readable_source'
#And with a specfic template
##INNER JOIN templatelinks t1 ON t1.tl_from = p.page_id
## AND t1.tl_namespace = 10
## AND t1.tl_title IN (
## Also exclude various deletion processes:
## 'Information')
#But that aren't also in other specified categories
LEFT JOIN categorylinks cnot ON cnot.cl_from = p.page_id AND cnot.cl_to
IN (
'Files_with_no_machine-readable_license',
'All_free_media',
'All_non-free_media',
'All_non-free_media',
'Redirects_from_moves',
'Wikipedia_restricted_images',
'Wikipedia_image_placeholders',
'DYK_images',
'Wikipedia_files_for_discussion',
'All_Wikipedia_files_missing_evidence_of_permission',
'All_Wikipedia_files_with_unknown_source',
'All_Wikipedia_files_with_unknown_copyright_status',
'Files_with_poor_sources',
'All_files_proposed_for_deletion',
'Wikipedia_files_for_discussion',
'All_Wikipedia_files_with_unknown_copyright_status',
'Wikipedia_files_with_disputed_copyright_information',
'Wikipedia_files_with_unconfirmed_permission_received_by_OTRS',
'Items_pending_OTRS_confirmation_of_permission',
'Items_with_OTRS_permission_confirmed',
'Wikipedia_files_requiring_splitting',
'Protected_sister_project_logos',
'Wikipedia_fully-protected_files',
'Wikipedia_files_on_Wikimedia_Commons_for_which_a_local_copy_has_been_requested_to_be_kept',
'Spoken_articles', ## These have definite authors
'Items_with_OTRS_permission_confirmed', ## The authorship will be in the OTRS permissions ticket
'Public_domain_images_ineligible_for_copyright_(shape)',
'Public_domain_images_ineligible_for_copyright_(simple)', ## Authorship is typically the brand owner for many of these
'Public_domain_images_ineligible_for_copyright_(logo)',## Authorship is typically the brand owner for many of these
'Public_domain_files_ineligible_for_copyright', ## Authorship may be irrelevant for these
'Public_domain_images_no_longer_eligible_for_claim_of_authorship',
'Images_in_the_public_domain_in_the_United_States', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
'All_free_in_US_media', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
'Author_died_more_than_100_years_ago_public_domain_files', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
'Author_died_more_than_70_years_ago_public_domain_files', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
'Pre-1996_PD_in_home_country_US_public_domain_images', ## An Unknown Authorship field could be completed as "Undetermined, due to the age of the media"
'Pre-1978_without_copyright_notice_US_public_domain_images',
'PD_US_not_renewed',
'United_States_government_images', ## An Unknown Authorship field could be completed as "US Goverment media, specifc photographer uncredtied or not otherwsie noted"
'United_States_Air_Force_images', ## An Unknown Authorship field could be completed as "US Air Force, specifc photographer/artist uncredtied or not otherwsie noted"
'United_States_Navy_images', ## An Unknown Authorship field could be completed as "US Navy, specifc photographer/artist uncredtied or not otherwsie noted"
'United_States_Army_images', ## An Unknown Authorship field could be completed as "US Army, specifc photographer/artist uncredtied or not otherwsie noted"
'United_States_military_images', ## An Unknown Authorship field could be completed as "US Deaprtment of Defence, specifc photographer/artist uncredtied or not otherwsie noted"
'Images_of_United_States_military_awards',
'NASA_images', ## An Unknown Authorship field could be completed as "Courtesy of NASA, specifc photographer/artist uncredtied or not otherwsie noted"
'National_Oceanic_and_Atmospheric_Administration_images', ## An Unknown Authorship field could be completed as "Courtesy of NOAA, specifc photographer/artist uncredtied or not otherwsie noted"
'United_States_Department_of_Energy_images', ## An Unknown Authorship field could be completed as "Courtesy of US Department of Energy, specifc photographer/artist uncredtied or not otherwsie noted"
'United_States_Department_of_Justice_images', ## An Unknown Authorship field could be completed as "Courtesy of US Department of Justice, specifc photographer/artist uncredtied or not otherwsie noted"
'United_States_Department_of_State_images', ## An Unknown Authorship field could be completed as "Courtesy of US State Department, specifc photographer/artist uncredtied or not otherwsie noted"
'National_Institutes_of_Health_images', ## An Unknown Authorship field could be completed as "Courtesy of National Institutes of Health, specifc photographer/artist uncredtied or not otherwsie noted"
'United_States_Geological_Survey_images', ## An Unknown Authorship field could be completed as "Courtesy of US Geological Survey, specifc photographer/artist uncredtied or not otherwsie noted"
'United_States_Department_of_Transportation_images',## An Unknown Authorship field could be completed as "Courtesy of Department of Transportation", specifc photographer/artist uncredtied or not otherwsie noted"
'United_States_Environmental_Protection_Agency_images', ## An Unknown Authorship field could be completed as "Courtesy of US Enivronmental Protection Agency, specifc photographer/artist uncredtied or not otherwsie noted"
'National_Park_Service_images', ## An Unknown Authorship field could be completed as "Courtesy of US National Parks Service, specifc photographer/artist uncredtied or not otherwsie noted"
'Images_of_the_United_States_Congress', ## An Unknown Authorship field could be completed as "US Congress, specifc photographer/artist uncredtied or not otherwsie noted"
'Self-images_that_have_been_claimed', ## These have the uploaders as authors.
'Self-published_work', ## These have the uploaders as authors.
'All_User-created_public_domain_files', ## These have the uploaders as authors.
'User-created_public_domain_files_from_May_2007', ## These have the uploaders as authors.
'User-created_public_domain_files', ## These have the uploaders as authors.
'Wikipedia_files_tagged_as_own_work', ## These have the uploaders as authors.
'Copyright_holder_released_public_domain_files',
'Creative_Commons_Attribution-ShareAlike_files',
'Creative_Commons_Attribution-ShareAlike_2.0_files',
'Creative_Commons_Attribution-ShareAlike_2.5_files',
'Creative_Commons_Attribution-ShareAlike_3.0_files',
'Creative_Commons_Attribution-ShareAlike_4.0_files',
'CC-zero_files',
'Cc-by-sa-2.5,2.0,1.0_files',
'Creative_Commons_Attribution_2.0_files',
'Creative_Commons_Attribution_2.5_files',
'Creative_Commons_Attribution_3.0_files',
'Creative_Commons_Attribution_4.0_files',
'GFDL_files',
'FAL_files',
'California_government_images',
'PD-Russia',
'PD-ZW-currency-exempt',
'PD_RomaniaGov',
'PD_other_reasons',
'Stamp_images_published_abroad_that_are_in_the_public_domain_in_the_United_States',
'PD-Canada_images_with_unknown_US_copyright_status',
'Images_in_the_public_domain_in_Thailand',
'UK_Government_images',
'Florida_government_images',
'Public_domain_German_government_images',
'Indian_public_domain_photographs',
'Australian government public domain images',
'Australian_public_domain_photographs',
'Australian_public_domain_photographs'
)
WHERE
##t1.tl_from IS NOT NULL AND
cnot.cl_from IS NULL
AND p.page_namespace = 6
AND img_user_text NOT IN (
## Bots , which means the file history cannot be looked at automatically..
'718 Bot',
## User's opting out
'Giano','Iridescent','Carrite', 'Andrew Davidson','Ritche333','Serial Number 54129','Bishonen',
## Removed for technical reasons.
'Titoxd',
## Already processed, or uploader aware.
'$?','$eti', 'Ritche333','Toiyabe', 'EdChem', 'Bcrowell'
)
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.