PHP Protocol Buffer to MySQL (and back!) bridge

Protocol Buffers are a binary data transfer protocol from Google. Google officially supports C++, Java and Python. There are 3rd party libraries that support other languages. I previously mentioned several that support PHP, including the one that we’re using at work, protoc-gen-php.

One challenge that we faced was storing our data. Should we store our data and convert to Protocol Buffers every time we sent it, or should we just work in Protocol Buffers and store it to the database directly?

We decided to store it in the database in a format compatible with the Protocol Buffer classes so we could easily access it as a Protocol Buffer object again later.

The following classes and scripts were written to help make that bridge between the Protocol Buffer classes generated by protoc-gen-php and MySQL.

They perform two main functions:

  1. Generate MySQL table create statements to build tables to hold the protocol buffer data
  2. Make classes which extend the protoc-gen-php classes with extra functions for database storage and retrieval (and a few bonuses)

 Generating The MySQL

We’ll start by generating some tables for our database. You’ll need php-cli installed, and you’ll need protoParser.php and protoMySQL.php in your PHP include path (or current directory) and makeMysql.php in your $PATH (or current directory).

Edit protoMySQL.php’s preferences (lines 17-36) to suit your configuration and needs.

Now something as simple as:

php ./makeMysql.php *.proto

should generate the MySQL table create statements you will need.

Generate The DB Classes

With protoc-gen-php, each .proto object gets a corresponding class. eg. list.proto.php is created from list.proto

makeClasses.php creates listDB.php which extends the classes generated by protoc-gen-php. Each proto object gets its own protoDB class and file.

php ./makeClasses.php *.proto

Those classes should then be used instead of their original non-database supporting proto classes.

DB Class Functions

__construct($id_or_object = NULL, $limit = PHP_INT_MAX)

$id_or_object
If it’s an object, we assume it’s a non-database variant, and use its members to populate this object.
If it’s numeric, we fetch the object from the database
Otherwise, we pass it up to the parent object.

$limit
Used in the parent constructor

get($id = NULL, $args = NULL)
$id
The database ID of the object to fetch.

$args
Enough MySQL arguments to uniquely identify the proto to retrieve.
If an array, each field is added to the query.
If a string, the string is appended to the query as is, after the WHERE clause.

Returns object if found, NULL if not found (or if multiples found)

unique()
Calls array_unique on any repeating elements.
Note: Arrays of objects are compared using their __toString methods

Returns nothing

load($object)

If you have an equivalent object (eg. a non-database or database version of the object) you can load it into the current object with this method.

Returns nothing

nullOrVal($val)
Determine if we should append NULL or an escaped string. Used in MySQL queries to ensure safe values.

Returns “NULL” or a mysql_real_escape’d string

delete()
Shallow delete from database. Since sub-objects could be shared/referenced by other proto objects this only deletes this object’s entry in the database

Returns nothing

put()
INSERT or UPDATE this object in the database.

Returns the insert ID of the object

toJSON($asArray = FALSE)
Returns a JSON representation of the current object, or an array appropriate for use in json_encode.

fromJSON($json)
Load the variables in the object from a JSON string

purge()
Like delete, but does delete referenced proto objects from database.
Returns number of sub-objects deleted.

License, Warranty and Support

My employers have been kind enough to let me release these scripts under and Open Source license. They are released under the GPL v.2 without any warranty.

We are actually switching away from MySQL on this particular project, and so these scripts are unlikely to receive any further updates.

I will provide such support as I have time for through the comments on this blog post.

Happy programming!

This entry was posted in Computers, Digitization, Projects, Something Interesting. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current day month ye@r *