mfn/cakephp2-postgres-no-meta

A CakePHP2 Postgres database driver without getColumnMeta() support

Maintainers

Details

github.com/mfn/cakephp2-postgres-no-meta

Homepage

Source

Issues

Installs: 1 636

Dependents: 0

Suggesters: 0

Security: 0

Stars: 8

Watchers: 2

Forks: 4

Open Issues: 0

Type:cakephp-plugin

0.0.6 2020-03-25 06:19 UTC

Requires

MIT 64ea2c47cb73cd5910605dba3fee5d292821cd8d

  • Markus Fischer <markus.woop@fischer.name>

databasecakephppostgres

This package is auto-updated.

Last update: 2024-10-25 16:23:28 UTC

README

This driver was changed in the following ways:

  1. The default CakePHP 2.x Postgres driver uses getColumnMeta to infer column types from the server. Although the PHP part has been optimized in recent years [1], it still incurs an overhead to hit the Postgres database on every call with a query like SELECT RELNAME FROM PG_CLASS WHERE OID=... [2]

Thus, this implementation is born which foregoes any use of the meta data and simply uses PDO::FETCH_ASSOC.

  1. There's a problem with special crafted SQL statements which contain the \ character [3] which actually isn't CakePHP specific but a problem of the underlying PDO/PgSQL driver [4].

The method \Postgres::value() was overriden to apply the special C-style escape operation to strings [5].

  1. The default PHP/PDO lastInsertId always returns a string. This driver is adapted to return an integer if is_numeric returns true on it. This allows easier integration with codebases using strict_types=1.

Requirements and Installation

  1. You need at least CakePHP 2.10.12
    For CakePHP >= 2.0 and < 2.10.12 , you can use version 0.0.2 of this package
  2. Add the line "mfn/cakephp2-postgres-no-meta": "^0.0.6" to your app/composer.json
  3. Run php composer.phar require mfn/cakephp2-postgres-no-meta
  4. Load the plugin in app/Config/bootstrap.php with the line
CakePlugin::load('PostgresNoMeta');
  1. Use the driver in your app/Config/database.php: PostgresNoMeta.Database/PostgresNoMeta (instead of Database/Postgres)
  2. Profit!

Rational

During the switch of a big application from MySQL to Postgres it was discovered that much overhead was lost on Postgres and it was finally discovered that these meta queries incur a measurable overhead.

The individual queries are very fast but, depending on your queries, they may add up until a measurable point.

In our case there were performance improvement of up to 50% without any additional changes except activating this class. YMMV.

A little bit later also found problems with the generated SQL statements, which in special cases were translated from:

INSERT INTO models(field) VALUES('\'':1');

to

INSERT INTO models(field) VALUES('\''$1');

before sent to the server, causing various problems.

Further reference