Advanced Topics

The DBSlayer was designed to be relatively simple and straight-forward. However, interfacing web applications to databases always has a few rough areas and the DBSlayer is not immune from a few quirks. Here are a few of the more advanced topics.

Mapping MySQL Types to JSON types

Befitting a simple data encoding, JSON only support five basic types - Array, Object, Number, String, Boolean (and null values). This is almost sufficient for mapping MySQL types to/from their JSON equivalents; however, there are a few exceptions. In many of these cases where there is no mapping, DBSlayer returns the value as a String and leaves the calling application to do further type conversions. In addition, MySQL null values map directly to JSON null values. This approach can lose type information, however (especially when dealing with date/time types); for your convenience, each SQL result object returns a TYPES array that gives the explicit MySQL type name.

The explicit conversions of MySQL types to JSON types is as follows

MYSQL TYPEJSON TYPE
MYSQL_TYPE_TINY Number
MYSQL_TYPE_SHORT Number
MYSQL_TYPE_LONG Number
MYSQL_TYPE_INT24 Number
MYSQL_TYPE_DECIMAL Number
MYSQL_TYPE_NEWDECIMAL Number
MYSQL_TYPE_DOUBLE Number
MYSQL_TYPE_FLOAT Number
MYSQL_TYPE_LONGLONG Number
MYSQL_TYPE_BIT String
MYSQL_TYPE_TIMESTAMP String
MYSQL_TYPE_DATE String
MYSQL_TYPE_TIME String
MYSQL_TYPE_DATETIME String
MYSQL_TYPE_YEAR String
MYSQL_TYPE_STRING String
MYSQL_TYPE_VAR_STRING String
MYSQL_TYPE_NEWDATE String
MYSQL_TYPE_VARCHAR String
MYSQL_TYPE_BLOB String
MYSQL_TYPE_TINY_BLOB String
MYSQL_TYPE_MEDIUM_BLOB String
MYSQL_TYPE_LONG_BLOB String
MYSQL_TYPE_SET Unsupported - returns Null
MYSQL_TYPE_ENUM Unsupported - returns Null
MYSQL_TYPE_GEOMETRY Unsupported - return Null
MYSQL_TYPE_NULL Null

UTF8 Issues

The DBSlayer's JSON requires your data to be in UTF-8 format. This means your database should be in UTF-8 and it should return UTF-8 for queries. The DBSlayer supports \uXXXX expansion gracefully, but it may not necessarily parse the data from the database for conformance. This means that invalid UTF-8 may be passed onwards to the client without an error (at which point, decoding the JSON in the calling application will likely fail).

Configuring Your MySQL Database

The following steps are necessary for your database to work correctly with the DBSlayer

  • Your database schema should be using utf8 as the default. If this is not the case, use the MySQL command ALTER DATABASE DEFAULT CHARACTER SET = utf8 to fix this. (All your create table statements will inherit this.)
  • Check your table definitions to make sure they're also using UTF8. (MySQL allows per-table and per-column character sets.)
  • When you insert data into your tables; make sure your script is specifying UTF8 when it connects to MySQL. With PHP's mysqli extension, the syntax is mysqli_set_charset($db_link, 'utf8');
  • Make sure DBSlayer is connecting w/ UTF8 also. You'll need to edit dbslayer-servers.cnf to do this; here's an example: 
    [movies-master]
database=database
host=dbhost
user=username
pass=******
default-character-set=utf8
  • Finally, if possible try to start MySQL with the option default-character-set=utf8

Security

Designed on internal secure networks, the DBSlayer has extremely limited security mechanisms. No authentication is supported at this time, and there are no mechanisms to encrypt the traffic either. DBSlayer supports only two security features for now:

  • It is possible to bind a DBSlayer daemon to a particular interface only (/e.g.,/ localhost or an internal network IP)
  • The /shutdown endpoint can only be called from the same machine that the DBSlayer instance is running on

This is obviously a potential area for future work, but for now you are strongly use other methods to enforce any integrity requirements that are necessary. Even when the DBSlayer supports more security, you should probably use the following safeguards:

  • Access to the DBSlayer can be controlled via firewalls; the DBSlayer should never be exposed to the outside world.
  • The account DBSlayer uses to access the MySQL database should not be allowed to execute dangerous operations like dropping tables or deleting rows. Ideally, the account would only be able to run selects and/or certain stored procedures.

Enforcing the integrity of your database within the database is just generally a good idea, regardless of the security within the DBSlayer.

Connection Pooling and MySQL Session Settings

The DBSlayer uses a pool of persistent connections to talk to one or more backend MySQL databases. Each request against the DBSlayer should be completely self-contained and not dependent on other queries; since two sequential queries may be executed in different sessions against a single MySQL server or even multiple servers (via round-robin dispatching), there is no easy way to tie together two commands to run in the same session/same server. This is what is known as a stateless protocol, in that the server does not need to maintain state for each client to connect two separate queries together.

Being stateless is not much of a limitation, and it enables the DBSlayer to do connection pooling, round-robin dispatching and other powerful scaling mechanisms. HTTP is an excellent example of how powerful stateless protocols can be. MySQL is also mostly stateless for the bulk of your queries (Select/Update/Insert/Delete, etc.), but there are a few commands for MySQL that are meant to affect future commands in the session or may only affect one server in a round robin scheme:

  • Setting global variables
  • Setting session variables (the SET command)
  • Setting database access privileges
  • Creating/altering a table on a master without replication

In addition, the following should only be used if contained within a single query, either using semicolon separators or within a stored procedure. These should not be dispatched as separate queries to the DBSlayer:

  • Locking tables
  • Transactions