Last modified: 2014-02-21 18:42:07 UTC
OS: Centos 6.5 MediaWiki: 1.22.2 SemanticBundle: 20140103 (Semantic MediaWiki v.1.8.0.5 && Semantic DrillDown v1.3) PostgreSQL: 9.2.6 By using the above configuration, I faced two problems: 1) When I try to open a page that uses the SemanticDrillDown extension I get the following error: Query: SELECT COUNT(DISTINCT sdv.id) FROM semantic_drilldown_values sdv LEFT OUTER JOIN semantic_drilldown_filter_values sdfv ON sdv.id = sdfv.id WHERE ((! (sdfv.value IS NULL OR sdfv.value = '' OR (sdfv.value = '0') OR (sdfv.value = '1')))) Funzione: DatabaseBase::query Errore: 22P02 ERROR: invalid input syntax for type boolean: "" LINE 4: WHERE ((! (sdfv.value IS NULL OR sdfv.value = '' OR (sdfv.... ^ 2) The escape character used by the SD extension within the sql is not allowed in Postgres. The SD extension checks the occurrence of special characters like the quote (') before performing any sql query and it replaces the occurrence with the escaped one. For instance it replaces the quote occurrence with the slash-quote (\') sequence. When the query is performed by the SD extension, I get is the following error message: Query: CREATE TEMPORARY TABLE semantic_drilldown_filter_values AS SELECT s_id AS id, o_ids.smw_title AS value FROM "smw_di_wikipage" JOIN "smw_object_ids" p_ids ON "smw_di_wikipage".p_id = p_ids.smw_id JOIN "smw_object_ids" o_ids ON "smw_di_wikipage".o_id = o_ids.smw_id WHERE p_ids.smw_title = 'Simple\'title' Funzione: DatabaseBase::query Errore: 42601 ERROR: syntax error at or near "applicazione" LINE 6: WHERE p_ids.smw_title = 'Simple\'title' ^
Created attachment 14647 [details] SD_Filter.php file patched
Created attachment 14648 [details] SD_AppliedFilter.php file patched
Possible fixes in the attached files. Below a short description: 1) To find a solution, I tried to replicate the error by using the postgres client (psql). Since the semantic_drilldown_values and the semantic_drilldown_filter_values are temporary tables, i created them on the fly before performing the offending query. CREATE TEMPORARY TABLE semantic_drilldown_values ( id INT NOT NULL ); CREATE TEMPORARY TABLE semantic_drilldown_filter_values AS SELECT s_id AS id, o_ids.smw_title AS value FROM "smw_di_wikipage" JOIN "smw_object_ids" p_ids ON "smw_di_wikipage".p_id = p_ids.smw_id JOIN "smw_object_ids" o_ids ON "smw_di_wikipage".o_id = o_ids.smw_id WHERE p_ids.smw_title = 'Simple_title'; I even changed the NOT operator in the offending query by using the one allowed in postgres: SELECT COUNT(DISTINCT sdv.id) FROM semantic_drilldown_values sdv LEFT OUTER JOIN semantic_drilldown_filter_values sdfv ON sdv.id = sdfv.id WHERE ((not ((sdfv.value IS NULL) OR (sdfv.value = '') OR (sdfv.value = '0') OR (sdfv.value = '1')))); If I perform the query into psql, I get no error: count ------- 0 (1 row) Going over many possible solutions, the one working was to modify the offending query in the SemanticDrillDown extension code (SD_AppliedFilter.php file) in order to skip the blank string check. But in this way, I'm not sure if the patch fits the expected SemanticDrillDown behaviour. Is there a way for keeping the blank string check? 2) Postgres does not allow the slash escape by default. As a workaround the postgres configuration can be modified by changing the following property in the postgresql.conf file: backslash_quote = on standard_conforming_strings = off Unfortunately, the mediawiki overwrites the standard_conforming_strings value (as stated in the includes/db/DatabasePostgres.php file): $this->query( "SET standard_conforming_strings = on", __METHOD__ ); To solve this problem without changing the mediawiki core file, I modified the SD code (SD_Filter.php file). My changes are simple: the special character quote is replaced with a double quote sequence when postgres is the database. Since the mediawiki code has several utility methods for managing databases differences, I suggest to start using them within the SD code (if possible).
Change 114763 had a related patch set uploaded by Vincenzo Laudizio: Fixed SemanticDrillDown generated sql queries for working with Postgres https://gerrit.wikimedia.org/r/114763
Change 114763 merged by jenkins-bot: Fixed SemanticDrillDown generated sql queries for working with Postgres https://gerrit.wikimedia.org/r/114763
Thanks for all your work on this! Hopefully this is now fixed, thanks to your patch.
