mql to-sql - a json-based rdbms query language

MQL-to-SQL

a JSON-based query language
for RDBMS data access from AJAX applications

Welcome!

Roland Bouman, Leiden, the Netherlands

Ex-MySQL, ex-Sun Microsystems

Web & BI developer at Strukton Rail

AuthorPentaho Solutions

Pentaho Kettle Solutions

http://rpbouman.blogspot.com, @rolandbouman

Agenda

The data access problem

What's wrong with SQL?

The Metaweb Query Language (MQL)

MQL-to-SQL

Demo

Questions

Agenda




MQL-to-SQL

Demo

Questions

The Data Access Problem

Web application

Classical Client/Server

SQL

Resultset

SQL

Resultset

RDBMS

HTTP

Browser

Desktop Application

?


Build a serviceProtocol on top of HTTP: REST, RPC

Data format: JSON, XML

Advantage: controlled data access

Disadvantage: controlled=limited


Advantage: unlimited data access

Disadvantage: unlimited=uncontrolled

SQL

SQL

Resultset

JSON

Build a query serviceExample: dbslayer

Agenda




MQL-to-SQL

Demo

Questions


Nothing of course, it's great!!!very powerful

expressive syntax

declarative

designed for the RDBMS

portable

well supported


SQL is very powerful...way too powerful

SQL has expressive syntax...and too hard to parse and generate

SQL is declarative...but not enough

SQL is designed for the RDBMS...can't escape relational model

SELECT f.title, f.description, f.release_year, f.length, f.rating, a.first_name, a.last_name, l.nameFROM film fINNER JOIN language lON f.language_id = l.idLEFT JOIN film_actor faON f.id = fa.film_idLEFT JOIN actor aON fa.actor_id = a.id

Film:

titledescriptionrelease_yearlengthrating

Actor:

last_namefirst_nameFilm_Actor:

Film KeyActor KeyLanguage:

language_name

SQL not declarative enough

Spoken in

Has cast

Starring in

SELECT f.title, f.description, f.release_year, f.length, f.rating, a.first_name, a.last_name, spoken_in.nameFROM film fRELATE spoken_in language l, has_cast film_actor fa RELATE starring_in actor a

Film:


Actor:


Film KeyActor KeyLanguage:

language_name

SQL not declarative enough

Spoken in

Has cast

Starring in

SQL can't escape the RM

Header line 1a line 2bUnnormalized data

header

line 1aline 2bSplit off
multivalued attributes repeating groups

First normal form (1NF)

header

12Higher normal forms (>= 3NF)

line 1aline 2b

Removeredundancy

header line 1aheader line 2bDenormalized data (1NF)

SQL queries: JOIN operation

???

Film:


Actor:


Film KeyActor Key

Language:

language_name


Category:

nameFilm_Category:

Film KeyCategory Key

SELECT f.*
, l.*, ol.*, (SELECT * FROM film_category fc INNER JOIN category c ON fc.category_id = c.id WHERE fc.film_id = f.film_id)
, (SELECT * FROM film_actor fa INNER JOIN actor a ON fa.actor_id = a.id WHERE fa.film_id = f.film_id)FROM film fINNER JOIN language lON f.language_id = l.idINNER JOIN language olON f.original_language_id = ol.id


SELECT f.*
, l.*, ol.*, c.*, a.*FROM film fINNER JOIN language lON f.language_id = l.idINNER JOIN language olON f.original_language_id = ol.idLEFT JOIN film_category fcON f.id = fc.film_idLEFT JOIN category cON fc.category_id = c.idLEFT JOIN film_actor faON f.id = fa.film_idLEFT JOIN actor aON fa.actor_id = a.id


How do we get rid of SQL?

Object relational mapper?

NoSQL? Document databases

ACID

Schema flexibility

Can we preserve the RM?

Agenda




MQL-to-SQL

Demo

Questions

The Metaweb Query Language

MQL rhymes with pickle

Native query language of Freebasehttp://www.freebase.com/

http://www.freebase.com/docs

JSON over HTTPNatural fit for AJAX applications

Freebase MQL Services

http://api.freebase.com/api/service/mqlread?query={"query":[{Your MQL here}]}

http://api.freebase.com/api/service/mqlread?queries={"q1": {"query":[{Your MQL here}]}, "q2": {"query":[{Your MQL here}]}}

http://api.freebase.com/api/service/mqlwrite

JSON

Subset of JavaScriptObject: set of property/value pairs

Scalar: boolean, null, number, string

Array: list of values

{
"type": "/sakila/film",
"film_id": 1,
"title": "ACADAMY DINOSAUR",
"language": {
"language_id": 1,
"language_name": "English"
},
"category":["Action","Adventure"]
}

JSON is great for AJAX

Modern Web applicationsAsynchronous JavaScript and XML

...except it's JSON rather than XML

1 to 1 runtime representationXML requires mapping, DOM traversal

JSON support in all modern browsers

Cross-domain requests (JSONP) injection

MQL Queries

JSON-basedBoth Query and Result

MQL is query by exampleMQL Query is an object template

Finds objects matching the template

Fills in value placeholders in template

Query & Result symmetryEasy to parse, easy to generate

Basic MQL query

{ "type": "/sakila/film",
"film_id": 1,
"title": "ACADAMY DINOSAUR",
"language": {
"language_id": 1,
"language_name": "English"
},
"category":["Action","Adventure"]
}

"film_id": 1,
"title": null,
"language": {
"language_id": null,
"name": null
},
"category": []
}

Query:

Result:

Property/value pairs act as filter

Value placeholders return data

Meta: Objects & Properties

Objects are bags of properties

Propertiesname/value pairs

Single- or multi-valued (array)

Value can be scalar or object

No explicit relationshipsImplicit: properties with object values

Meta: Types

Types define a bag of propertiesObjects may have multiple types

Types are grouped in domains

In MQL, use the type propertytype: "/domainname/typename"

Property has exactly 1 expected type

MQL Joins/subqueries

Implicit via object-type properties

Can't specify join conditions

MQL operators

Property/value act as equals operator

Explicit operators for advanced filters

!=, Have their usual meaning

|= and !|= test for membership"one of" and "not one of"

~= for word pattern matching

MQL operators

"film_id": 1
}

"film_id

mql to-sql - a json-based rdbms query language

Documents