Phabricator and NuoDB: Porting a PHP application to a distributed database

Introduction

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;
 F1
 ---
 123

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;
 f1
 ---
 123

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;
 sum
 ----
 246

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:

PIPES_AS_CONCAT,
ANSI_QUOTES,
NO_BACKSLASH_ESCAPES,
STRICT_ALL_TABLES,
ONLY_FULL_GROUP_BY,
ERROR_FOR_DIVISION_BY_ZERO,
NO_ZERO_DATE.

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;
----
it's

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:

$dbh->execute();
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);
INSERT INTO counter VALUES (0);
LOCK TABLE counter;
UPDATE counter SET value = value + 1;
SELECT value FROM counter;
UNLOCK TABLES;

In NuoDB, this functionality is available by using sequences:

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

Conclusion

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.

Glenn West
Anonymous's picture
<p>Guys what your long term

Guys what your long term plan here. In enterprise applications, they are often "grandfathered", sometimes by decades. Change the app in a closed source environment is just not possible. And even internally developed applications may not have developers to maintain. 

Seems like some of the above is not really difficult to implement, and may be that you have a "flavors" strategy, where the db takes on a persona depending on application expectation.

I've done db migrations, and I've seen the garbage, think that should have never went in, somehow made it in, your zeros dates are a good example. I know you want to stick to the standard, its a wonderful goal, its just not going to be very reasonable long term strategy, as there just too much to change on the client side.

 

 

 

 

@Glenn - I think what you are

@Glenn - I think what you are saying is true; there are a lot of legacy apps out there that are "grandfathered".

The challenges with moving those apps to any new database (not just NuoDB) share a lot of the same issues; differences with data-types, SQL Syntax, driver differences, app code changes, etc. So, in that respect; there are no drop-in replacement databases for these grandfathered applications. 

What we are seeing from the community as well as from customers, is that NuoDB is playing a part with new apps as well as with grandfathered apps. The latter, as you'd expect is a longer process to properly vet and test, etc.  

 

 

Add new comment

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.