Hello,
if I test the query [1] on Toolserver I got an empty result, on labs I got an correct answer. With other articles this query works fine. Could somebody check this? This query is part of project WIWOSM, so unfortunately I can not move to Labs in the moment, because this project need a Postgresql installation that there still not exist. Is there any idea for a workaround to get from 300.000 Wikipedia articles the Wikidata Q-Number? Greetings Tim [1] SELECT `ips_item_id` FROM `wb_items_per_site` WHERE `ips_site_id` = 'dewiki' AND `ips_site_page` = 'Bundesanstalt_für_Verwaltungsdienstleistungen'; _______________________________________________ Toolserver-l mailing list ([hidden email]) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette |
Am 02.03.2014 12:58, schrieb Kolossos:
> Is there any idea for a workaround to get from 300.000 Wikipedia > articles the Wikidata Q-Number? > > [1] SELECT `ips_item_id` FROM `wb_items_per_site` > WHERE `ips_site_id` = 'dewiki' > AND `ips_site_page` = 'Bundesanstalt_für_Verwaltungsdienstleistungen'; dewiki and wikidatawiki are on the same database s5, so there is not difference. And replication is ok. Only commonswiki is missing on s5 since two days. Your query should not return any result on both databases because ips_site_page is using spaces instead of underscores. Because of the "ü" you could also use a wrong character encoding on your connection. For me SELECT @@hostname, `ips_item_id` FROM wikidatawiki_p.`wb_items_per_site` WHERE `ips_site_id` = 'dewiki' AND `ips_site_page` = 'Bundesanstalt für Verwaltungsdienstleistungen'; returns the correct result on toolserver and labs. +------------+-------------+ | @@hostname | ips_item_id | +------------+-------------+ | z-dat-s5-b | 15793045 | +------------+-------------+ 1 row in set (0.00 sec) +------------+-------------+ | @@hostname | ips_item_id | +------------+-------------+ | labsdb1002 | 15793045 | +------------+-------------+ 1 row in set (0.03 sec) But you could also rewrite your query and request dewiki instead of wikidatawiki: SELECT TRIM(LEADING 'Q' FROM TRIM(LEADING 'q' FROM pp_value)) AS ips_item_id FROM dewiki_p.page INNER JOIN dewiki_p.page_props ON page_id=pp_page WHERE page_namespace=0 AND page_title='Bundesanstalt_für_Verwaltungsdienstleistungen' AND pp_propname='wikibase_item'; +-------------+ | ips_item_id | +-------------+ | 15793045 | +-------------+ 1 row in set (0.04 sec) _______________________________________________ Toolserver-l mailing list ([hidden email]) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette |
Thanks for your answer.
I was connect with sql-s3 by using for connecting to db by: mysql -h wikidatawiki-p.db.toolserver.org On sql-s3 I still miss the entry but on sql-s5 it works. Greetings Tim Am 02.03.2014 14:32, schrieb Merlissimo: > Am 02.03.2014 12:58, schrieb Kolossos: >> Is there any idea for a workaround to get from 300.000 Wikipedia >> articles the Wikidata Q-Number? >> >> [1] SELECT `ips_item_id` FROM `wb_items_per_site` >> WHERE `ips_site_id` = 'dewiki' >> AND `ips_site_page` = 'Bundesanstalt_für_Verwaltungsdienstleistungen'; > > dewiki and wikidatawiki are on the same database s5, so there is not > difference. And replication is ok. Only commonswiki is missing on s5 > since two days. > Your query should not return any result on both databases because > ips_site_page is using spaces instead of underscores. Because of the "ü" > you could also use a wrong character encoding on your connection. > For me > > SELECT @@hostname, `ips_item_id` FROM > wikidatawiki_p.`wb_items_per_site` WHERE `ips_site_id` = 'dewiki' AND > `ips_site_page` = 'Bundesanstalt für Verwaltungsdienstleistungen'; > > returns the correct result on toolserver and labs. > > +------------+-------------+ > | @@hostname | ips_item_id | > +------------+-------------+ > | z-dat-s5-b | 15793045 | > +------------+-------------+ > 1 row in set (0.00 sec) > +------------+-------------+ > | @@hostname | ips_item_id | > +------------+-------------+ > | labsdb1002 | 15793045 | > +------------+-------------+ > 1 row in set (0.03 sec) > > > But you could also rewrite your query and request dewiki instead of > wikidatawiki: > > SELECT TRIM(LEADING 'Q' FROM TRIM(LEADING 'q' FROM pp_value)) AS ips_item_id > FROM dewiki_p.page > INNER JOIN dewiki_p.page_props ON page_id=pp_page > WHERE page_namespace=0 AND > page_title='Bundesanstalt_für_Verwaltungsdienstleistungen' > AND pp_propname='wikibase_item'; > > +-------------+ > | ips_item_id | > +-------------+ > | 15793045 | > +-------------+ > 1 row in set (0.04 sec) > > > _______________________________________________ > Toolserver-l mailing list ([hidden email]) > https://lists.wikimedia.org/mailman/listinfo/toolserver-l > Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette > _______________________________________________ Toolserver-l mailing list ([hidden email]) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette |
Free forum by Nabble | Edit this page |