mql-to-sql: json-based query language for rdbms access from ajax applications
TRANSCRIPT
MQL-to-SQLa JSON-based query language for RDBMS data access from AJAX applications
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
Welcome!
Roland Bouman, Leiden, the Netherlands Ex-MySQL, ex-Sun Microsystems Web & BI developer at Strukton Rail Author
Pentaho Solutions Pentaho Kettle Solutions
http://rpbouman.blogspot.com, MQL-to-SQL: a JSON-based Query @rolandbouman RDBMS language for yourProject: http://code.google.com/p/mql-to-sql/
Agenda
The data access problem What's wrong with SQL? The Metaweb Query Language (MQL) MQL-to-SQL Demo Questions
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
Agenda
The data access problem What's wrong with SQL? The Metaweb Query Language (MQL) MQL-to-SQL Demo Questions
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
The Data Access Problem
Classical Client/ServerSQL Resultset Desktop Application
Web applicationSQL
RDBMS
?Browser HTTP
Resultset
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
The Data Access Problem
Build a service
Protocol on top of HTTP: REST, RPC Data format: JSON, XML
Advantage: controlled data access Disadvantage: controlled=limited
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
The Data Access Problem
Build a query service
Example: dbslayerSQL JSON SQL Resultset
Advantage: unlimited data access Disadvantage: unlimited=uncontrolledMQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
Agenda
The data access problem What's wrong with SQL? The Metaweb Query Language (MQL) MQL-to-SQL Demo Questions
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
What's wrong with SQL?
Nothing of course, it's great!!!
very powerful expressive syntax declarative designed for the RDBMS portable well supported
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
What's wrong with SQL?
SQL is very powerful
...way too powerful ...and too hard to parse and generate ...but not enough ...can't escape relational modelMQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
SQL has expressive syntax
SQL is declarative
SQL is designed for the RDBMS
SQL not declarative enoughLanguage:SELECT f.title language_name , f.description , f.release_year Spoken in , f.length , f.rating Film: , a.first_name , a.last_name title Actor: description , l.name release_year FROM film f length last_name INNER JOIN language l rating first_name ON f.language_id = l.id Has cast Starring in LEFT JOIN film_actor fa ON f.id = fa.film_id LEFT JOIN actor a Film_Actor: ON fa.actor_id = a.idFilm Key Actor KeyMQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
SQL not declarative enoughLanguage:SELECT , , , , , , , FROM RELATE , f.title f.description f.release_year f.length f.rating a.first_name a.last_name spoken_in.name film f spoken_in language l has_cast film_actor fa RELATE starring_in actor alanguage_name
Spoken in
Film:title description release_year length rating
Actor:last_name first_name
Has cast
Starring in
Film_Actor:Film Key Actor KeyMQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
SQL can't escape the RMHeader line 1a line 2b header
Unnormalized data
Split off multivalued attributes repeating groups
line 1a line 2b
First normal form (1NF)
???header header line 1a header line 2b line 1a line 2b
Remove redundancy
SQL queries: JOIN operation
1 2
Denormalized data (1NF)
Higher normal forms (>= 3NF)
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
SQL can't escape the RMLanguage:language_name
Film: Category:name title description release_year length rating
Actor:last_name first_name
Film_Category:Film Key Category Key
Film_Actor:Film Key Actor Key
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
SQL can't escape the RMSELECT , , 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 f INNER JOIN language l ON f.language_id = l.id INNER JOIN language ol ON f.original_language_id = ol.idMQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
SQL can't escape the RMSELECT , , FROM INNER JOIN ON INNER JOIN ON LEFT JOIN ON LEFT JOIN ON LEFT JOIN ON LEFT JOIN ON f.* l.*, ol.* c.*, a.* film language f.language_id language f.original_language_id film_category f.id category fc.category_id film_actor f.id actor fa.actor_id f l l.id ol ol.id fc fc.film_id c c.id fa fa.film_id a a.id
= = = = = =
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
How do we get rid of SQL?
Object relational mapper? NoSQL?
Document databases ACID Schema flexibility
Can we preserve the RM?
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
Agenda
The data access problem What's wrong with SQL? The Metaweb Query Language (MQL) MQL-to-SQL Demo Questions
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
The Metaweb Query Language
MQL rhymes with pickle Native query language of Freebase
http://www.freebase.com/ http://www.freebase.com/docs Natural fit for AJAX applications
JSON over HTTP
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
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
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
JSON
Subset of JavaScript {
Object: 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"]MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
}
JSON is great for AJAX
Modern Web applications
Asynchronous JavaScript and XML ...except it's JSON rather than XML XML requires mapping, DOM traversal JSON support in all modern browsers injectionMQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
1 to 1 runtime representation
Cross-domain requests (JSONP)
MQL Queries
JSON-based
Both Query and Result MQL Query is an object template Finds objects matching the template Fills in value placeholders in template Easy to parse, easy to generateMQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
MQL is query by example
Query & Result symmetry
Basic MQL query
Property/value pairs act as filter Value placeholders return data Result:{ "type": "/sakila/film", "film_id": 1, "title": "ACADAMY DINOSAUR", "language": { "language_id": 1, "language_name": "English" }, "category":["Action","Adventure"] }MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
Query:{ "type": "/sakila/film", "film_id": 1, "title": null, "language": { "language_id": null, "name": null }, "category": [] }
Meta: Objects & Properties
Objects are bags of properties Properties
name/value pairs Single- or multi-valued (array) Value can be scalar or object Implicit: properties with object valuesMQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
No explicit relationships
Meta: Types
Types define a bag of properties
Objects may have multiple types Types are grouped in domains type: "/domainname/typename"
In MQL, use the type property
Property has exactly 1 expected type
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
MQL Joins/subqueries
Implicit via object-type properties Can't specify join conditions
MQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
MQL operators
Property/value act as equals operator Explicit operators for advanced filters !=,
Have their usual meaning "one of" and "not one of"
|= and !|= test for membership
~= for word pattern matchingMQL-to-SQL: a JSON-based Query language for your RDBMSProject: http://code.google.com/p/mql-to-sql/
MQL operators
Operators follow the property name Operations not returned in the result Query:{ "type": "/sakila/film", "film_id