PostgreSQL

From Koha Wiki

Jump to: navigation, search
Home
Koha > Technical > Development

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]

Contents

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.

Personal tools