Last modified: 2009-02-10 17:27:59 UTC
Hello, I tested Semantic MediaWiki extension on PostgreSQL (both 1.1.1 and yesterday's svn version). Here's what went wrong.
I tried creating page Property:Population with content "[[has type::number]]". It apparently performed this query...
INSERT INTO smw_specialprops (subject_id,subject_namespace,subject_title,property_id,value_string) VALUES ('556','102','Population','1','_num');
...and it got back this response:
ERROR: column "subject_title" of relation "smw_specialprops" does not exist
...which appears to be true. Is the PostgreSQL schema in sync with the MySQL schema?
Viewing *any* Property namespace pages seems to not work. For Property:Population, it appears to perform this query:
SELECT DISTINCT subject_id FROM smw_relations WHERE relation_title='Population' ORDER BY subject_title LIMIT 26;
PostgreSQL thinks this is bad form:
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
Not sure how much these help, but since the SMW developers asked for comments on how SMW works on PostgreSQL, I thought I'd submit my findings. Basically, I can browse articles and see their infoboxes, but viewing and editing property pages doesn't work, making the extension pretty useless at the moment. But we live in hope!
Also the query for creating a temporary table does not work with postgres:
A database error has occurred Query: CREATE TEMPORARY TABLE prop1( title VARCHAR(255) binary NOT NULL PRIMARY KEY) TYPE=MEMORY
Since we have a new (additional) schema for MySQL soon, it would be worth to work on proper PostgreSQL support for this. The current MySQL schema data is found in the functions setup() in the files SMW_SQLStore.php and SMW_SQLStore2.php (new), both in ./includes/storage. Help for setting up a PostgreSQL version of this is appreciated -- we will assist where we can (feel free to contact me via email on that).
What is the problem with the temporary table query? Does PostgreSQL not support those, or is the syntax not appropriate?
I'm thinking wrong syntax. "TYPE=MEMORY" looks badly like a MySQLism; I'm not sure if (or how) PostgreSQL does in-memory temporary tables, but PostgreSQL doesn't like weird=implementationspecificparameters. =) Here's the documentation: http://www.postgresql.org/docs/8.3/static/sql-createtable.html
Re-assign to extension developer for triage/comments.
Thanks to the recent contribution of Marcel Gsteiger, SMW 1.4.2 offers basic PostgreSQL support. The feature is not yet tested properly, and various concrete issues might come up after some more practical experience with the code. Yet, this bug can now be closed in its generality.