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
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
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.
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.
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
In NuoDB, the pipe character is used to concatenate strings:
SQL> select 'this ' | 'string' from dual; ----------- this string
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
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();
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
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.