How do I map from page.page_namespace to a string ("Draft", "Talk", etc)?
Topic on Talk:Quarry
I would like to know as well if there is a shortcut for this. But meanwhile I use this
SELECT page_namespace, (case
when page_namespace = 0 then 'Main'
when page_namespace = 1 then 'Talk'
when page_namespace = 2 then 'User'
when page_namespace = 3 then 'User talk'
when page_namespace = 4 then 'Wikipedia'
when page_namespace = 5 then 'Wikipedia talk'
when page_namespace = 6 then 'File'
when page_namespace = 7 then 'File talk'
when page_namespace = 8 then 'MediaWiki'
when page_namespace = 9 then 'MediaWiki talk'
when page_namespace = 10 then 'Template'
when page_namespace = 11 then 'Template talk'
when page_namespace = 12 then 'Help'
when page_namespace = 13 then 'Help talk'
when page_namespace = 14 then 'Category'
when page_namespace = 15 then 'Category talk'
when page_namespace = 100 then 'Portal'
when page_namespace = 101 then 'Portal talk'
when page_namespace = 828 then 'Module'
when page_namespace = 829 then 'Module talk'
end) as namespace_name
I've been playing around with this, but can't get it to work. Unfortunately, my SQL is kind of weak. When I run https://quarry.wmflabs.org/query/30504, I get Unknown column 'page_namespace' in 'field list'.
Can you give an example of how this would be used in a real query?
The namespace label varies by wiki (depending on installed extensions) and UI language. See phab:T180558 for some discussion of a user provided table on ToolsDB that was once available for this sort of lookup. Unfortunately even if that tool was fixed there is currently no way to connect to ToolsDB from Quarry per phab:T151158
We download this periodically to make use of it in Analytics, this is the script we use:
https://github.com/wikimedia/analytics-refinery/blob/master/bin/download-project-namespace-map
You can store the results in a table and join to that if you like.