MQL-to-SQL: a JSON-based Puery Language for RDBMS Access from AJAX Applications

Past year, I got interested in Freebase and its query language, MQL. In my opinion, MQL is an interesting database query language in general, and a number of features make it downright excellent for implementing secure but flexible access to relational databases over the web, especially for modern (AJAX) web applications – better than SQL.

I created an implementation dubbed MQL-to-SQL, which is available at http://code.google.com/p/mql-to-sql/ under the terms of the LGPL. It’s basically a PHP script that supports the the protocol of the freebase MQL-read webservice. MQL-to-SQL itself uses PDO (PHP Data Objects – a standard database access abstraction layer built into PHP) for database access and is thus to some extent RDBMS independent. I currently have examples based on the sakila sample database working on SQlite, MySQL, Postgres (pagila) and Oracle. For sample queries, see the project wiki To kickstart it without downloading and installing, try them in the online demo

In this 45-minute session, I want to explain how MQL works and why it is a great solution to solve the RDBMS access problem for modern (AJAX) web applications. In addition, I will explain how I implemented MQL-to-SQL, and how it translates MQL queries to SQL queries, executes them and returns the result document. Time permitting I will show what the advantage is for the web-client (as compared to being limited to tabular results returned by SQL queries)

The outline for the talk follows below:

Solving the data access problem in Web vs client-server applications:

• How web applications typically use a purpose-built REST or RPC style webservice and a data format like XML or JSON to access and manipulate data over HTTP
• How by contrast, client-server applications typically use a query language like SQL instead of a purpose-built service protocol
• How this relates to so-called document-based queries and results in NoSQL solutions like CouchDB and MongoDB

The problem with SQL from the perspective of a web applications

• Why you don’t very often see a webservice that accepts SQL queries
• How to integrate SQL with your application language, and how ORMs make it even worse (yes!)

The MQL query language

• Some background on freebase, for which MQL was designed
• Syntax of MQL queries and results, with examples (hello world and beyond)
• SQL vs MQL: what is similar, what is not; what are the respective strengths and weaknesses and how does that translate to particular use-cases

Why MQL is a great solution for modern (AJAX) web applications:

• accessible through a webservice, but because it is a query language, it is inherently flexible, and you don’t need an application-specific purpose-built webservice.
• Application language integration: both queries and results are JSON-based, which is easily processed and generated by JavaScript, which is the de-facto client side and emerging server-side scripting language for web applications
• Query by example and Query/Result symmetry: in MQL, generating new queries from previously returned results is trivial. This is very unlike the SQL case, where the language is an algebra, but the result is pure data.
• Gain performance by saving roundtrips: MQL queries and results are objects and their structure can in principle be arbitrarily complex. With a typical RPC or REST protocol one would need multiple requests to obtain the same information.
• Overcome the “object-relational impedance mismatch”: since MQL queries and results are objects there is no need for a separate layer to translate relational results to object structures. In addition, MQL queries are like “fill in the blanks” templates for the result, and unlike some popular ORM dialects (like hibernate or doctrine), MQL queries do not look like “algebras-gone-objectpipelines”
• without compromising flexibility, MQL is much easier to secure than SQL

MQL-to-SQL: an open source MQL implementation for your RDBMS

• concepts: mapping MQL to SQL and relational results to result documents
• implementation: PHP, PDO
• how to get started with MQL-to-SQL using your favorite RDBMS