I program heavily in the Symfony framework under PHP, and I required more flexibility in my ORM (Object Relational Model), since the default ORM included with Symfony, Propel, doesn’t play very nice with Oracle. Reading the Symfony forums on the main ORMs for use with Symfony, I came across the Doctrine Project, of which the syntax is inspired by Java Hibernate’s HQL called DQL (Doctrine Query Language).

I’ve never worked with HQL/DQL before, but I found myself loving it because I can write (almost) natural SQL-like query statements that act on my object model. Lately, I’ve been thinking about how to bring about a FQL-like (Facebook Query Language) platform to a project I’m working on at work, and had this sudden realization: I can bring a SQL-like syntax to platform developers through the use of DQL.

DQL is by no means efficient like FQL in terms of how it performs its data mining and (possibly) hydration, but if you want to give developers the power to select from tables in your database without giving full or just read-only access, using Doctrine’s DQL is the way to go.

My reasoning is this:

Let’s assume that I want developers to be able to access some basic information from a table where I store users, but that users table might also have information such as a password, which I do not want to expose to developers. However, on our code backend, we of course need access to the password field, but on our developer end, restrict completely.

In my Doctrine ORM, I would define two objects, one that corresponds with internal access, and another with external. Here’s a sample:

intUser:
  tableName: users
  columns:
    user_id:
      type: integer(12)
      primary: true
    username:
      type: string(20)
    password:
      type: string(20)


extUser:
  tableName: users
  columns:
    user_id:
      type: integer(12)
      primary: true
    username:
      type: string(20)

I run this schema definition through Doctrine to generate the models for both objects. I would never let developers know about the intUser definition as that means they would have access to the password, so I tell them about extUser and the fields that are accessible.

For example, let’s assume I have a REST gateway set up at http://www.mywebsite.com/query

I send a DQL statement in a parameter to that URL:

http://www.mywebsite.com/query?q=SELECT * FROM extUser

And have it pass q to Doctrine, where would parse that query and return the appropriate records (only the username and id in this case). You can also employ Doctrine named queries to get closer to an actual FQL-like syntax.

Anyways, this is my theory on how I think I can quickly implement FQL-like functionality into my project, but I actually have not tested it yet.

What I also love about Doctrine is it has a really complex, but nice Backus-Nour Form definition on its website.

I’m really curious about how the Doctrine lexer works, and will probably explore more about how it parses the BNF later on as implanting languages in another languages is pretty neat.

Finally, yes, it works extremely well with Oracle (and MySQL as well; using it in another project), and the developers are extremely active in terms of getting bugs fixed. Compare this to Propel, where there’s only one or two devs and it takes forever to get things fixed.