Code Conversion Details
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.