Over a million developers have joined DZone.

Phabricator and NuoDB: Porting a PHP Application to a Distributed Database

DZone's Guide to

Phabricator and NuoDB: Porting a PHP Application to a Distributed Database

· Java Zone
Free Resource

Are you joining the containers revolution? Start leveraging container management using Platform9's ultimate guide to Kubernetes deployment.

This article was originally written by Philip Stoev


In order to certify that NuoDB is a fully-featured database product that can be used to power existing enterprise-grade applications, we frequently migrate various pieces of software to run on NuoDB.

Recently, we did that with Phabricator, which is an open-source, web-based suite of development collaboration tools, including source repository browser, reviews, bug tracker and wiki. Phabricator is written in PHP and its architecture includes a database abstraction layer, even though it previously only ran on MySQL. In the process, we learned some lessons that would help developers create portable database applications or port existing ones to a distributed database such as NuoDB.

Case and case sensitivity of database identifiers

By default, MySQL converts all identifiers to lowercase:

mysql> create table t1 (f1 integer);
mysql> insert into t1 values (123);
mysql> select * from t1;
| f1   |
|  123 |

The field name is returned as f1 lowercase, so if you have a database abstraction layer that converts query results to hashes, the value 123 will be stored under hash key f1 lowercase.

In NuoDB and other database products, table and column names convert to uppercase by default:

SQL> select * from t1;

so the value 123 will be stored under hash key F1 uppercase.

Two approaches can be used to bridge the gap:

Force NuoDB to use lowercase identifiers

This is achieved by specifying table and field names in double quotes when creating the tables. For example:

SQL> create table "t1" ("f1" integer);
SQL> insert into t1 values (123);
SQL> select * from t1;

The NuoDB Migrator can also be used to migrate the database schema in this case-preserving manner.

If a SELECT query uses column aliases, they also need to be enclosed in double quotes:

SQL> select f1 + f1 as "sum" from t1;

Force the PHP PDO driver to return lowercase column names in the result set

The PDO driver provides an attribute that can be used to control the case of the identifiers

// Set the case in which to return column_names.
$dbh->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
// The $result associative array will contain lowercase key names
$result = $sth->fetch(PDO::FETCH_ASSOC);

All column names in the result set, including those that may have been mixed-case, will be converted to lowercase.

Dealing with MySQL-specific syntax

In order to make porting applications easier, NuoDB already supports MySQL-specific functions, features and pieces of syntax. For example, NuoDB supports the ENUM data type.

By default, MySQL's query syntax has some oddities and is more permissive with respect to invalid or ambiguous queries. NuoDB aims to be more standards-compliant, so it operates like a MySQL database running with the following SQL modes:


String literals

In NuoDB, string literals are quoted using single quotes. The backslash character is not used for escaping. To escape a single quote, specify it twice:

SQL> select 'it''s' from dual;

String concatenation

In NuoDB, the pipe character is used to concatenate strings:

SQL> select 'this ' | 'string' from dual;
this string

Invalid values

NuoDB does not allow invalid values to be inserted into a table. For example, you can not insert a string such as 'ABC' into an INTEGER column

SQL> create table t1 (f1 integer);
SQL> insert into t1 values ('abc');
unable to convert string "abc" into type "integer"

Zero date and time values

Date values such as 0000-00-00 and time values such as 00:00:00 are not allowed. Consider using NULL or another placeholder value instead. The NuoDB Migrator has an option to rewrite such dates on the fly during the migration process.

SQL> create table t1 (f1 date);
SQL> insert into t1 values ('0000-00-00');
error converting to date from 0000-00-00

Incomplete GROUP BY

Queries such as:

SQL> select f1, f2, min(f3) from t1 group by f1;
column TEST.T1.F2 must appear in the GROUP BY clause or be used in an aggregate function

are rejected by NuoDB as the GROUP BY clause does not contain all the non-aggregate columns from the SELECT list. MySQL accepts such queries, but they do not conform to the SQL standard as the value returned for the f2 column is not fully defined.

You can consider rewriting such queries by adding the columns in question to the GROUP BY clause:

SQL> select f1, f2, min(f3) from t1 group by f1, f2;

Obtaining the last auto-generated value

NuoDB does not provide a syntax to obtain the last auto-increment value via a SQL function, such as LAST_INSERT_ID() . Instead, the value is obtained via a driver method:

print $dbh->lastInsertId();

Table locking

NuoDB does not provide the LOCK_TABLE() function, but frequently code that relies on this functionality can be rewritten to use standard transactions.

Consider this simplified example that generates continuously-incrementing integers:

CREATE TABLE counter (value INTEGER);
LOCK TABLE counter;
UPDATE counter SET value = value + 1;
SELECT value FROM counter;

In NuoDB, this functionality is available by using sequences:

SQL> create sequence counter;
SQL> select next value for counter from dual;


Even if you are faced with an application that was written only with a particular SQL database in mind, do try porting it to NuoDB in order to take advantage of modern database features such as geo-distribution and redundancy. NuoDB supports a wide range of SQL features and functions to make porting feasible, allowing legacy applications to be brought into the cloud era.


Moving towards a private or Hybrid cloud infrastructure model? Get started with our OpenStack Deployment Models guide to learn the proper deployment model for your organization.


Published at DZone with permission of Seth Proctor, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}