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 text was updated to reflect the CIDR terminology (eg, 192.168.1.0/24 rather than 192.168.1.*).