PostgreSQL

The intention of this Wiki page is to collect information on how to run Koha on PostgtreSQL.

There is also a Bug open for this: [Bug 7365]

The Main Problem: MySQLisms

The main problem with running Koha on PostgreSQL are the many MySQLisms, that is SQL fragments that use MySQL extensions and that are non-standard SQL. The use of MySQLisms must be avoided at all cost. This page should help you to reach that goal.

Idioms

What	MySQL	PostgreSQL	Neutral SQL	C4:DBQ Function	DBH Function
The current date	CURDATE(), CURRENT_DATE()	now()::date	CAST(now() AS date)	n/a	n/a
Colum names that are keywords	`colname`	"colname"	??	n/a	n/a
Show columns in a table	SHOW COLUMNS FROM, DESCRIBE	SELECT column_name, data_type FROM information_schema.columns WHERE table_name ='tablename'	n/a	tableColumns("<tablename>")[1]	column_info()

Remarks

[1] Actually we use the following statement to be consistent with the result set usage in Koha:

SELECT column_name as "Field", data_type as "Type" FROM information_schema.columns WHERE table_name ='tablename'

C4:DBQ - Creating SQL Query Fragments on the Fly

The proposed solution for idioms that do now have a portable form (patch pending) is to replace non-portable static SQL code by function calls that return database specific SQL fragements as strings to be concatenated with the static SQL code. This method is minimally invasive and does not need a paradigm change:

my $dbq = C4:DBQ->new();
$dbh->do("SELECT " . $dbq->today() . " AS today");

Here, the (fictious) function $dbq->today() would return 'CURDATE()' for MySQL or 'now()::date' for PostgreSQL. Please note that this is a bad example since 'CAST(now() AS date)' would work on either database.

Code Organization

• C4/DBQ.pm: Factory "class" to create a database specific object:
• C4/DBQ/MySQL.pm: MySQL implementation
• C4/DBQ/PostgreSQL.pm: PostgreSQL implementation
• t/DBQ.pl: Unit test

C4:DBQ->new() Returns either a MySQL or PostgreSQL object, depending on which database has been selected. If more database types are supported, it will return the respective object type.