Fabian Gorsler wrote:

Hi,

due to the translation bug (XWIKI-1841) I spent some time looking for inconsistent rows in our database (Postgres 8.1) and I found several documents inserted twice into the database, which lead to problems when editing and viewing the documents. For luck our coporate wiki has just 23 pages with problems caused by this bug (the same xwd_fullname with different xwd_id fields / the select statement can be found at the end of this mail, perhaps anyone is intrested in).

The thing I'm quite confused about is that there are no unique constraints set on the the columns which could have prevented these problems. The DBMS would have forced the XWiki core to generate an error instead of inserting inconsistent data sets into the database.

I neither know much about Hibernate, nor I have tested whether XWiki would use constraints with other DBMS, but would it be possible to set those constraints in future versions?

We can't make only xwd_fullname unique, as the same document can have more than one translation, so we need to put a unique constraint on the (xwd_fullname, xwd_language) pair.

At the moment I'm quite scared about reactivating I18N for our corporate wiki, because an similar programming might occour again, but with a clean constraint setup, those errors would not happend silently in the background, but instead appear as they occur transparent to everyone.

If you pay attention to what language you are editing, there should be no errors.

...

From my point of view it should be possible to set an unique constraint on xwd_fullname and xwd_language. Up to now I did't understand why there is a need for the fields xwd_default_language and xwd_translation, but on the other I didn't investigate any time in this. A flaw in the initial design... xwd_translation isn't really needed, but we need a way to specify the default language. There is an idea to store this not in the document table, but in a document index table, so that changing the default language will be easier, among other things. But that is for another version of XWiki, and then indeed it will be enough to have only xwd_fullname and xwd_language. Wouldn't it be enough to have the fields xwd_fullname and xwd_language? (The next thing is: Why is there a need for xwd_web, xwd_name and xwd_fullname? - Just because I'm curious.)

Because at first there was only xwd_web and xwd_name, and xwd_fullname was added only for making shorter queries. We still need xwd_web in many queries, and computing the space name out of xwd_fullname in SQL is not such a valid option (due to performance).

Are there any similar thoughs? What do you think? For a modern DBMS (not MySQL 3.23 ;)) shoud be able to "support" XWiki and to prevent inconsistencies and corruption of data.

But nevertheless I love XWiki 1.1! The renaming feature and the panel support are great. Just these two new features made any problems during the migration from 0.9.840 forgotten. :)

The mentioned query from above for searching inconsistent rows in the database (working for Postgres 8.1):

select xwd_fullname, xwd_version from xwikidoc where xwd_fullname in (select xwd_fullname from xwikidoc group by xwd_fullname having count(*) > 1) order by xwd_fullname;

Sergiu