This page describes the process of converting Tavi 0.26 from MySQL to PostgreSQL from scratch, after prototyping with Juha-Mikko Ahonens patch.

----

The first thing to do is update the scripts to create the database, and configure the settings. The existing scripts were modified to include PostgreSQL options. Next, a data conversion script was added to the install directory to help users migrate from MySQL to PostgreSQL.

The lowest level interface to the database in Tavi is the WikiDB class, so a PostgreSQL WikiDB class was created. This class has additional debugging added to the query output.

References to WikiDB were then grepped for, with only one result. Pagestore.php was then modifed to require the appropriate library. Before making any further PostgreSQL addition, it was decided to grep for all query and result calls to ensure none were missed as a result of not testing all website functions manually. 47 query() calls were found, and 26 result() calls were found (not including db.php, dbpg.php, and tavidoc.php).

When tested at this point, the first error was a syntax error regarding a table locking statement on the rate table. PostgreSQL uses MVCC rather than MySQL style locking, and I'm not an expert in database locking, so I'm trusting the DBMS to do the right things here.

The first file to be converted was rate.php. This file needed a transaction added (replacing the lock statement), queries altered and optimised to use the CIDR type, and also needed to have timestamps explicitly updated (MySQL does that automatically, whereas PostgreSQL doesn't). Regexes were altered to suit the CIDR queries, and the associated text in parse/html.php and lang/default.php was updated to reflect the CIDR terminology (eg, 192.168.1.0/24 rather than 192.168.1.*).

While working with the rate and associated IP blocking admin pages, the locking admin pages were also examined. This highlighted a problem with the allpages query, which simply wouldn't parse. A simpler (although more costly) query was added to the method for PostgreSQL to use. Now that the locking page could generate a list of all pages, unlocking and locking was tested. Unlocking worked, while locking did not - there was a problem with the times again, so the lib/page.php queries were modified to update the timestamps explicitly.

The pagestore.php calls were next to be addressed. This was more of the same, with some areas of possible physical-level optimisation apparent. The timestamp output format was also adjusted to be compatible with the expected 3.x MySQL format (not using the hacked 4.1 code that the VEMS Wiki uses).

An hour was wasted chasing a difference between MySQL and PostgreSQL in the WantedPages query... turns out MySQL isn't too fussy about the case of text strings when it does JOINs. I'm not sure this is a good thing in a Wiki. Nice to know about though, I guess.

The result of that problem was a moderately complex query that required an additional index to speed up by a factor of 8. A side benefit of this is that searches are fast - even without the TSearch2 module. Granted, we don't have a huge amount of data to scan but this was a suprisising find anyway. The ILIKE in PostgreSQL operator works as acceptably as the LIKE opertor in MySQL, and if it becomes too slow later, adding TSearch2 functionality can be scripted.

Minor customisation for VEMS follows...