Showing posts with label Ajax. Show all posts
Showing posts with label Ajax. Show all posts

Monday, July 27, 2015

Using REST services to work with the Pentaho BI Server repository

A couple of months ago, I wrote about how to use Pentaho's REST services to perform various user and role management tasks.

In that post, I tried to provide a few general guidelines to help developers interested in using the REST services, and, by way of example, I showed in detail how to develop a sample application that uses one particular service (namely, UserRoleDaoResource) from within the PHP language to perform basic user management tasks.

Recently, I noticed this tweet from Rafael Valenzuela (@sowe):


I suggested to use a Pentaho REST service call for that and I offered to share a little javascript wrapper I developed some time ago to help him get started.

Then, Jose Hidalgo (@josehidrom) chimed in an expressed some interest so I decided to go ahead and lift this piece of software and develop it into a proper, stand-alone javascript module for everyone to reuse.

Introducing Phile

Phile stands for Pentaho File access and is pronounced simply as "file". It is a stand-alone, cross-browser pure javascript module that allows you to build javascript applications that can work with the Pentaho respository.

The main use case for Phile is Pentaho BI Server plugin applications. That said, it should be possible to use Phile from within a server-side javascript environment like node js.

Phile ships as a single javascript resource. The unminified version, Phile.js, includes YUIDoc comments and weighs 32k. For production use, there's also a minified version available, Phile-compiled.js, which weighs 5.7k.

You can simply include Phile with a <script> tag, or you can load it with a module loader, like require(). Phile supports the AMD module convention as well as the common js module convention.

Phile has its YUIDoc api documentation included in the project.

Phile is released under the terms and conditions of the Apache 2.0 software license, but I'm happy to provide you with a different license if that does not, for some reason, suit your needs. The project is on github and I'd appreciate your feedback and/or contributions there. Github is also the right place to report any issues. I will happily accept your pull requests so please feel free to contribute!

Finally, the Phile project includes a Pentaho user console plugin. This plugin provides a sample application that demonstrates pretty much all of the available methods provided by Phile. Installing and tracing this sample application is a great way to get started with Phile.

Getting started with Phile

In the remainder of this post, I will discuss various methods provided by Phile, and explain some details of the underlying REST services. For quick reference, here's a table of contents for the following section:

Installing the sample application

The recommended way to get started with Phile is to install the sample application unto your Pentaho 5.x server. This basic application provides a minimal but functional way to manipulate files and directories using Phile. Think of it as something like the Browse files perspective that is built into the Pentaho user console, but implemented as a BI server plugin that builds on top of Phile.

To install the sample application, first download phile.zip, and extract it into pentaho-solutions/system. This should result in a new pentaho-solutions/system/phile subdirectory, which contains all of the resources for the plugin. After extraction, you'll have to restart your pentaho BI server so the plugin gets picked up. (Should you for whatever reason want to update the plugin, you can simply overwrite it, and refresh the pentaho user console in your browser. The plugin contains no server side components, except for the registration of the menu item.)

Once restarted, you should now have a "Pentaho files" menu item in the "Tools" menu:



Note: Currently neither Phile nor the sample application are available through the Pentaho marketplace. That's because the sample application is only a demonstration of Phile, and phile itself is just a library. In my opinion it does not have a place in the marketplace - developers should simply download the Phile script files and include them in their own projects.

Running the sample application

As you probably guessed, the sample application is started by activating the "Tools" > "Pentaho Files" menu. This will open a new "Pentaho Files" tab in the user console:


The user interface of the sample application features the following elements:
At the very top: Toolbar with push buttons for basic functionality.
The label on the buttons pretty much describes their function:
New File
Create a new file in the currently selected directory
New Directory
Create a new directory in the currently selected directory
Rename
Rename the currently selected file or directory
Delete
Move the currently selected file or directory to the trash, or permanently remove the currently selected item from the trash.
Restore
Restore the current item from the trash
API Docs
Opens the Phile API documentation in a new tab inside the Pentaho user console.
Left top: Treeview.
The main purpose of this treeview is to offer the user a way to navigate through the repository. The user can open and close folders by clicking the triangular toggle button right in front of a folder. Clicking the label of a file or folder will change the currently selected item to operate on. In response to a change of the currently selected item, the appropriate buttons in the toolbar are enabled or disabled as appropriate for the selected item. Initially, when the application is started, the tree will load the directory tree from the root of the repository down to the current user's home directory, and automatically select the current user's home directory. (In the screenshot, note the light blue highlighting of the /home/admin folder, which is the home directory of the admin user.)
Left bottom: trash
This is a view on the contents of the current user's trash bin. Unlike the treeview, this is a flat list of discarded items. Items in the trash can be selected just like items in the treeview. After selecting an item from the trash the appropriate toolbar buttons will be enabled and disabled to manipulate that item.
Right top: properties
Items stored in the repository carry internal metadata, as well as localization information. The properties pane shows the properties of the currently selected item in JSON format. JSON was chosen rather than some fancy, user-friendly form in order to let developers quickly see exactly what kinds of objects they will be dealing with when programming against the pentaho repository from within a javascript application.
Right bottom: contents and download link
If the currently selected item is a file, its contents will be shown in this pane. No formatting or pretty printing is applied to allow raw inspection of the file resource. In the title of the pane, a download link will become available to allow download of the currently selected resource for inspection on a local file system.

An overview of the Phile API

To get an idea of what the Phile API provides, click the "API Docs" button in the toolbar to open the API documentation. A new tab will open in the Pentaho user console showing the YUIDoc documentation. Inside the doc page, click the "Phile" class in the APIs tab (the only available class) and open the "Index" tab in the class overview:


Click on any link in the index to read the API documentation for that item. Most method names should give you a pretty good clue as to the function of the method. For example, createDirectory() will create a directory, discard() will remove a file or directory, and so on and so forth. We'll discuss particular methods in detail later in this post.

An closer look at the Phile API

Now we'll take a closer look to what actually makes the sample application work. For now, you need only be concerned with the index.html resource, which forms the entry point of the sample application.

Loading the script

The first thing of note is the <script> tag that includes the Phile module into the page:
    <script src="../js/Phile.js" type="text/javascript"></script>
Remember, you can either use the unminified source Phile.js, which is useful for debugging and educational uses, or the minified Phile-compiled.js for production use. The functionality of both scripts is identical, but the minified version will probably load a bit faster. The sample application uses the unminified script to make it easier to step through the code in case you're interested in its implementation, or in case you want to debug an issue.

The location of the script is up to you - you should choose a location that makes sense for your application and architecture. The sample application simply chose to put these scripts in a js directory next to the location of index.html, but if you want to use Phile in more than one application, it might make more sense to deploy it in a more central spot next to say the common ui scripts of Pentaho itself.

As noted before you can also dynamically load Phile with a javascript module loader like require(), which is pretty much the standard way that Pentaho's own applications use to load javascript resources.

Calling the Phile constructor

The next item of note in index.html is the call to the Phile() constructor:
    var phile = new Phile();
This instantiates a new Phile object using the default options, and assigns it to the phile (note the lower case) variable. The remainder of the sample application will use the Phile instance stored in the phile variable for manipulating Pentaho repository files.

(Normally the default options should be fine. For advanced uses, you might want to pass a configuration object to the Phile() constructor. Refer to the API documentation if you want to tweak these options.)

General pattern for using a Phile instance

Virtually all methods of Phile result in a HTTP request to an underlying REST service provided by the pentaho platform. Phile always uses asynchronous communication for its HTTP requests. This means the caller has to provide callback methods to handle the response of the REST service. Any Phile method that calls upon the backing REST service follows a uniform pattern that can best be explained by illustrating the generic request() method:
    phile.request({
      success: function(
        request, //the configuration object that was passed to the request method and represents the actual request
        xhr,     //The XMLHttpRequest object that was used to send the request to the server. Useful for low-level processing of the response
        response //An object that represents the response - often a javascript representation of the response document 
      ){
        //handle the repsonse
        
      },
      failure: function(request, xhr, exception){
        //handle the exception
        //Arguments are the same as passed to the success callback, 
        //but the exception argument represents the error that occurred rather than the response document
      },
      scope: ..., //optional: an object that will be used as the "this" object for the success and failure callbacks
      headers: {
        //any HTTP request headers.
        Accept: "application/json"
      },
      params: {
        //any name/value pairs for the URL query string.
      },
      ...any method specific properties...
    });
In most cases, developers need not call the request() method themselves directly. Most Phile methods provide far more specific functionality the generic request, and are implemented as a wrapper around to the generic request() method. These more specific methods fill in as many of the specific details about the request (such as headers and query parameters) as possible for that specific functionality, and should thus be far easier and more reliable to invoke than the generic request().

However, in virtually all cases the caller needs to provide the success() and a failure() callback methods to adequately handle server action. For each of the specific methods provided by Phile, the YUIDoc API documentation lists any specific options that apply to the configuration object passed to that particular method. You will notice that some of those more specific properties occur more often than others across different Phile methods. But the callback methods are truly generic and recur for every distinct Phile method that calls upon an underlying REST service.

Using Phile to work with repository directories

The request() method demonstrates the generic pattern for calling Phile methods, but the actual call will look different for various concrete requests. We'll take a look at a few of them as they are demonstrated by the sample application.

Getting the user's home directory with getUserHomeDir()

The sample application first obtains the user's home directory by calling the getUserHomeDir() method of the Phile object. The getUserHomeDir() method is implemented by making a HTTP GET request to services in the /api/session API.

The sample application uses the home directory to determine how to fill the repository treeview:
    //get the user home directory
    phile.getUserHomeDir({
      success: function(options, xhr, data){
        var dir = data.substr(0, data.indexOf("/workspace"));
        createFileTree(dir);
      },
      failure: failure
    });
The data argument returned to the success callback is simply a string that represents the path of the user's home directory.

For some reason, paths returned by these calls always end in /workspace, so for example, for the admin user we get back /home/admin/workspace. But it seems the actual user home directory should really be /home/admin, so we strip off the string "/workspace". Then, we pass the remaining path to the createFileTree() method of the sample application to fill the treeview.

As we shall see, when getting the repository structure for the treeview, the user home directory is used to decide how far down we should fetch the repository structure, and which directory to select initially.

Specifying a username to get a particular user's working directory

In the example shown above, the argument object passed in the call the getUserHomeDir() method of the Phile object did not include a user option. In this case, the home directory of the current user is retrieved with a GET request to /api/session/userWorkspaceDir. But getUserHomeDir() can also be used to retrieve the home directory of a specific user by passing the username of a specific user in the name option to the argument object passed to getUserHomeDir(). If the user option is present, a GET request is done instead to /api/session/workspaceDirForUser, which returns the home directory for that particular user.

Reading repository structure with the getTree() method

The createFileTree() function of the sample application obtains the repository structure to populate the treeview by doing a call to the method of the Phile object. The getTree() in Phile is implemented by doing a GET HTTP request to the /api/repo/files/.../tree service.
    function createFileTree(dir) {
      var dom = document.getElementById("tree-nodes");
      dom.innerHTML = "";
      var pathComponents = dir.split(Phile.separator);
      phile.getTree({
        path: dir[0],
        depth: dir.length,
        success: function(options, xhr, data) {
          createTreeBranch(data, pathComponents);
        },
        failure: failure
      });
    }
In the argument passed to getTree(), we see two options that we haven't seen before:
  • path specifies the path from where to search the repository.
  • depth is an integer that specifies how many levels (directories) the repository should be traversed downward from the path specified by the path option.
Many more methods of the Phile object support a path option to identify the file object that is being operated on. For flexibility, in each case where a path option is supported, it may be specified in either of the following ways:
  • As a string, separating path components with a forward slash. In code, you can use the static Phile.separator property to refer to the separator.
  • As an array of path component strings. (Actually - any object that has a join method is accepted and assumed to behave just like the join method of the javascript Array object)
Remember that the sample application initially populates the treeview by passing the user's home directory to createFileTree(). In order to pass the correct values to path and depth, the user home directory string is split into individual path components using the static Phile.separator property (which is equal to "/", the path separator). Whatever is the first component of that path must be the root of the repository, and so we use pathComponents[0] for path (i.e., get us the tree, starting at the root of the repository). The depth is specified as the total number of path components toward the user's home directory, ensuring that the tree we retrieve is at least so deep that it will contain the user's home directory.

The data that is returned to the success callback is an object that represents the repository tree structure. This object has just two properties, file and (optionally) children:
file
An object that represents the file object at this level. This object conveys only information (metadata) of the current file object.
children
An array that represents the children of the current file. The elements in the array are again objects with a file and (optionally) a children property, recursively repeating the initial structure.

The structure of file objects

Object like the one held by the file property described above are generally used to represent files by the Pentaho REST services, and other Phile API calls that expect file objects typically receive them in this format. They are basically the common currency exchanged in the Pentaho repository APIs.

Official documentation for file objects can be found here: repositoryFileTreeDto.

From the javascript side of things, the file object looks like this:
aclNode
String "true" or "false" to flag if this is an ACL node or not.
createdDate
A string that can be parsed as an integer to get the timestamp indicating the date/time this node was created.
fileSize
A string that can be parsed as an integer to get the size (in bytes) of this node in case this node represents a file. If a filesize is not applicable for this node, it is "-1".
folder
String "true" or "false" to flag if this is node represents a folder or not.
hidden
String "true" or "false" to flag if this is node is hidden for the end user or not.
id
A GUID identifiying this node.
locale
The current locale used for localized properties like title.
localeMapEntries
This is an array of localized properties for this file. The array items have these properties:
locale
The name of the locale for this map of localized properties. There is also a special "default" locale indicating the current locale.
properties
This is a bag of name/value pairs, representing localized properties.
key
The key for this property.
value
The value for this property.
locked
String "true" or "false" to flag if this is node is locked or not.
name
The name of this node.
ownerType
A string that can be parsed as an integer indicating the owner type.
path
A string containing the forward slash separated path components.
title
The title for presenting this node to the user.
versioned
String "true" or "false" to flag if this is node is versioned or not.
versionId
If the file is versioned, the versionId property is present and its value is a String that represents the version number.
Note that the list of properties above is just a sample: Depending on context, some properties might not be present, or extra properties may be present that are not listed here. But in general, key properties like id, path, folder, dateCreated, name and title will in practice always be present.

There are a few things that should be noted about the file object. What may strike you by surprise is that in the raw JSON response, all scalar properties are represented as strings. For example, properties a like folder and hidden and so on look like they should be booleans, but their value is either "true" or "false", and not, as one might expect, true and false. Likewise, integer fields like fileSize, and timestamp fields like dateCreated are also represented as strings - not as integers.

There is not really any good reason why the Pentaho REST service uses string representations in its response, since there are proper ways to express these different types directly in JSON. However, things are the way they are and there's not much Phile can (or should) do to change it. For convenience it might be a good idea to add a conversion function to Phile which turns these strings into values of a more appropriate datatype. Remember, contributions and pull requests are welcome!

For convenience, most of the Phile methods that require a path option can also pass a file option instead. If a file option is passed, it is expected to adhere to the structure of the file object described above. In those cases, Phile will use the path property of the passed file object as the path option. This allows application developers to use the file objects as they are received from the Pentaho server directly as input for new calls to the Phile API. (That said, the sample application currently does not use or demonstrate this feature and passes an explicit path instead.

Browsing folders with getChildren()

We just discussed how the getTree() method could retrieve the structure of the repository from one particular path up to (or rather, down to) a specific level or depth. There is a very similar method for obtaining the contents of only a single directory: getChildren(). This is implemented by calling the /api/repo/files/.../children service. In the sample application, this is used to populate the (hitherto unpopulated and collapsed) folder nodes in the treeview:
    function getChildrenForPath(path){
      phile.getChildren({
        path: path,
        success: function(options, xhr, data) {
          if (!data) {
            return;
          }
          var files = data.repositoryFileDto, i, n = files.length, file;
          sortFiles(files);
          for (i = 0; i < n; i++){
            file = files[i];r
            createTreeNode(file);
          }
        },
        failure: failure
      });
    }
Just like in the call to getTree() a path option must be specified. The getChildren() call does not support a depth option, which makes sense since it only returns the contents of the specified directory. (In other words, unlike getTree(), getChildren is not recursive, so it makes no sense to specify or require a "depth".)

Because getChildren() only returns the contents of the specified directory, the structure of the response data passed to the success() callback differs slightly from what is returned by getTree(): in the case of getChildren(), the response data is a javascript object that has a single repositoryFileDto property, which is an array of file objects:
{
  "repositoryFileDto": [
    ...many file objects...
  ]
}

Browsing the trash bin

For each user, the repository has a special trash folder that is used as temporary storage for discarded files (and directories). The trash folder cannot (should not) be approached directly with a call to getChildren() or getTree(). Instead, there is a special method available called getTrash() to list the contents of the trash folder. This method is implemented by doing a GET request to the /api/repo/files/deleted service.

The sample application reads the contents of the trash folder to populate the list in the left bottom of the screen with the loadTrash() function:
    function loadTrash() {
      phile.getTrash({
        success: function(options, xhr, data){
          var thrashList = document.getElementById("trash-nodes");
          createFileList(data, thrashList);
        },
        failure: failure
      });
    }
As you can see, loadTrash() simply calls the getTrash() method on the Phile instance, and uses the data passed back to the success() callback to build the list.

The data that getTrash() passes back to the callback has essentially the same structure as what is passed back by the getChildren() method: an object with a single repositoryFileDto property, which holds an array of file objects. However, since the file objects are in the trash, they have a couple of properties that are specific to discarded items:
deletedDate
A string that can be parsed as an integer to get the timestamp indicating the date/time this node was deleted.
originalParentFolderPath
A string that holds the original path of this file object before it was discarded to the trash folder. This is essential information in case you want to restore an item from the trash, since its actual own path property will be something like /home/admin/.trash/pho:4cce1a1b-95e2-4c2e-83a2-b19f6d446a0d/filename and refers to its location in the trash folder, which most likely means nothing to an end user.

Creating new directories

The sample application handles a click event on the "New Directory" toolbar button by calling the newDirectory() function. This function calls the createDirectory() method of the Phile object to create a new directory inside the currently selected directory:
    function newDirectory(){
      var path = getFileNodePath(selected);
      var name = prompt("Please enter a name for your new directory.", "new directory");
      if (name === null) {
        alert("You did not enter a name. Action will be canceled.");
        return;
      }
      var newPath = path + Phile.separator + name;
      phile.createDirectory({
        path: newPath,
        success: function(xhr, options, data) {
          selected.lastChild.innerHTML = "";
          selected.setAttribute("data-state", "expanded");
          getChildrenForPath(path);
        },
        failure: failure
      });
    }
As you can see, the user is presented with a prompt to get a name for a new directory, and this is simply appended to the path of the currently selected directory. This new path is then passed in the path property of the argument to the createDirectory method of the Phile object. (Please note that a serious application would provide some checks to validate the user input, but in order to demonstrate only the principles of using Phile, the sanmple application takes a few shortcuts here and there.)

The createDirectory() method is implemented by doing a PUT request to the /api/repo/dirs service. One might expect to be returned a file object that represents the newly created directory, but alas this is not the case: the request does not return any data. The sample application refreshes all of the children of the parent directory instead. (Please note that this is just a quick way to ensure the treeview reflects the new state of the directory accurately. A serious application should probably change the gui to add only the new directory, and otherwise retain the state of the tree. Again this is a shortcut just to keep the sample application nice and simple.)

Sorting file objects

When working with methods like getTree(), getChildren() and getTrash(), it may be needed to sort an array of file objects. For example, in the sample application, the treeview first presents all folders, and then the files, and within these types of file objects, the items are sorted alphabetically. The trash pane takes a different approach, and sorts all files based on their original path.

In the sample application this is achieved simply by calling the native sort() function on the array in which the file objects are received. But to achieve a particular type of sort, a comparator function is passed into the sort function.

Built-in file comparators

Phile offers a number of static general purpose file comparators:
compareFilesByPathCS()
Compares files by path in a case-sensitive (CS) manner.
compareFilesByOriginalPathAndName()
Compares files by orginal path, and then by name (in a case-sensitive manner).
compareFilesByTitleCS
Sorts folders before files, and then by title in a case-sensitive manner (CS).
compareFilesByTitleCI
Sorts folders before files, and then by title in a case-insensitive manner (CS).
Each of these methods can be of use in certain contexts. For example, the comparison implemented by compareFilesByPathCS() will by many people be regarded as the "natural" order of file objects, and this might be a useful sort order for things like autocomplete listboxes and such. The compareFilesByTitleCS() and compareFilesByTitleCI() methods on the other hand implement an order that is most suitable when presenting the files in a single directory in a GUI. And compareFilesByOriginalPathAndName() may prove to be useful when sorting items from the trash, since it takes the original name and location into account rather than the actual, current name and location.

The sample application uses the sortFiles() function to sort files in the treeview:
    function sortFiles(files){
      files.sort(Phile.compareFilesByTitleCI);
    }
As you can see, it's simply a matter of calling sort() on the array of files and passing the appropriate comparator. Since the comparators are static properties of the Phile constructor itself, it's qualified by prepending Phile. to its name.

Creating custom comparators

Phile offers a useful utility method to generate new comparators called createFileComparator(). The createFileComparator() is static an attached as a property directly to the Phile constructor itself, so in order to call it, it must be qualified, like so: Phile.createFileComparator().

The createFileComparator() takes a single argument, which represents a sort specification. It returns the comparator function that can be passed to the native sort() method of the javascript Array object.

The sort specification passed to createFileComparator() should be an object. Each property of this object indicates the name of a property in the file objects that are to be compared. The value of the property in the sort specification should be an object that contains extra information on how to treat the respective file in the comparison.

In the simplest case, the properties of the sort specification are all assigned null. In this case the fields will be compared as-is, which in practice means the field values of the file objects are compared in case-sensitive alphanumerical order. This is how the built-in comparator compareFilesByPathCS() is created:
    Phile.compareFilesByPathCS = Phile.createFileComparator({
      path: null
    });
This sort specification simply states that file objects must be compared by comparing the value of their respective path property. The built-in comparator compareFilesByOriginalPathAndName() is constructed similarly, but specifies that both originalPath and name fields are to be compared (in order):
 
    Phile.compareFilesByOriginalPathAndName = Phile.createFileComparator({
      originalParentFolderPath: null,
      name: null
    });

Specifying sort order

You can exert more control on how fields are compared by assigning an actual object to the respective property in the sort specification instead of null. When an object is assigned, you can set a direction property with a value of -1 to indicate reverse sort order.

This is put to good use by the built-in comparators compareFilesByTitleCS and compareFilesByTitleCI to ensure that folders are sorted before regular files:
    Phile.compareFilesByTitleCS = Phile.createFileComparator({
      folder: {direction: -1},
      title: null
    });
By first sorting on the folder property and after that on the title property, we achieve the desired presentation that most user will be accustomed to. However, the folder property will have a string value of either "true" or "false". Since we want all folders to be sorted before all files, we need to reverse the order (since "true" is larger than "false"). The sort specification {direction: -1} does exactly that.

Specifying case-insensitivy

Sometimes, it can be useful to exert control on the actual values that will be compared. To this end, you can specify a custom converter function in the field sort specification via the convert property. The specified function will then be applied to the raw field value, and the return value of the converter function will be used in the comparison rather than the raw value.

The built-in compareFilesByTitleCI comparator uses the convert property in the sort specification to implement a case-insensitive comparison:
    Phile.compareFilesByTitleCI = Phile.createFileComparator({
      folder: {direction: -1},
      title: {convert: function(value){return value.toUpperCase();}}
    });
In this sort specification, a convert function is specified for the title field, which accepts the original, raw title, and returns its upper case value by applying the built-in toUpperCase() method of the javascript String object. Since the upper case values rather than the raw values will be compared, this ensures the comparison is now case-insensitive.

More advanced comparators

Besides implementing case-insensitive comparison, specifying a convert function can be of use in other cases as well. For instance, if you want to sort on file creation date, you could specify a convert function that parses the createdDate property of the file object and returns its integer value to as to achieve a chronological order.

Reading and writing files and file properties

Now that we know how to use Phile to work with directories, let's take a look at working with files.

Reading file properties

Whenever the user of the sample application clicks the label of an item in either the treeview (left top) or the trash file list (left bottom), the current selection changes, and the properties pane (right top) will refresh and show the properties of the currently selected file. This provides a view of the corresponding file object as it is known in the repository.

The selection of an item is handled by the labelClick() function of the sample application. This method handles quite a bit of logic that is specific to the sample application, and we won't discuss it in full. Instead, we focus on the bit that retrieves the properties of the selected file, which is done with a call to the getProperties() method of the Phile object:
    phile.getProperties({
      path: getFileNodePath(node),
      success: function(options, xhr, data){
        var propertiesText = document.getElementById("properties-text");
        propertiesText.innerHTML = "";
        displayProperties(data, propertiesText);
      },
      failure: failure
    });
As we have seen in many other Phile calls, the path property of the object passed to the method provides the actual input. In this particular sample code, the value for path is extracted from the DOM element that represents the selected item in gui (this is held by the node variable), and the getFileNodePath() function simply extracts the path. (The details of that are not really relevant to using Phile and analysis of that code is left as an exercise to the reader.)

The data argument passed to the success() callback passed to the getProperties() method returns the properties of the file identified by the path property as a file object. The sample application simply clears the properties pane and then fills it with a JSON string representation of the object (by calling the displayProperties() function).

The actual make up of the returned file object will depend on whether the path identifies a regular file, a folder, or an discarded item in the trash folder. But the getProperties() method can be used in any of these cases to retrieve the properties of these items from the repository.

Reading file contents

In the sample application, changing the selection also affects the contents pane (right bottom). Changing the selection always clears whatever is in there, but if the newly selected item is a file (and not a directory or an item in the trash folder), its contents will also be displayed there.

Loading the content pane is handled by the labelClick() function, which is also responsible for loading the properties pane. The contents of the file are obtained with a call to the getContents() method of the Phile object:
    phile.getContents({
      path: getFileNodePath(node),
      headers: {
        Accept: "text/plain"
      },
      success: function(options, xhr, data) {
        displayContents(options.path, xhr.responseText);
      },
      failure: failure
    });
As usual, the path option is used to tell the method of which file the contents should be retrieved.

There are two features in this call to getContents() that we have not witnessed in any other Phile method call, and which are unique to this method:
  • A HTTP Accept header is specified to ensure the contents are retrieved as plain text (as indicated by the text/plain mime type value).
  • Rather than using the data argument passed to the success() callback, the responseText property of the actual XMLHttpRequest that was used to do the request to the REST service. That's because in this case, we only want to display the literal contents of the file. The exact type of the data passed to the success() callback may vary depending on the Content-Type response header, which is not what we want right now since we're interested in displaying only the raw file contents. This is exactly why the success() callback (as well as the failure() for that matter) is passed the actual XMLHttpRequest object - to access any lower level properties that might require custom handling.

Offering a download link to the user

When a file is selected, the title of the content pane (right bottom) presents a link to the end user that allows them to download the contents of the file. The url that may be used to download a particular file can be generated using the getUrlForDownload() method of the Phile object.

In the sample application, the download link is created in the displayContents() function, which is called after selecting a file item:
      function displayContents(path, contents){
        var a = document.getElementById("contents-download");
        a.textContent = path;
        a.href = phile.getUrlForDownload(path);

        var contentsText = document.getElementById("contents-text");
        contentsText.innerHTML = escapeHtml(contents);
      }
The getUrlForDownload() method takes a single path argument, and returns a string that represents a url that can be used to download the file. As usual, the path argument may be either a string, or an array of path components.

Note that generating the download link only involves string manipulation, and does not entail calling a backend REST service. Rather, when the generated url is used as the href attribute for a html-<A> element, clicking that link will access the REST service and initiate a download from the server. Therefore, this method does not require or accept callbacks, since generating the download link is not an asynchronous process.

Creating and writing files

In the previous section, we discussed how you can use the createDirectory() method of the Phile object to create a new directory. The Phile object also features a saveFile() to create and write regular files.

The sample application offers a "New File" button that allows the user to create a new file in the current directory. Clicking the button will invoke the newFile() function, which invokes the saveFile() method on the Phile object. The relevant snippet is shown below:
    phile.saveFile({
      path: newPath,
      data: contents,
      success: function(xhr, options, data) {
        selected.lastChild.innerHTML = "";
        selected.setAttribute("data-state", "expanded");
        getChildrenForPath(path);
      },
      failure: failure
    });
The call to saveFile() is quite similar to the one made to createDirectory(): the file that is to be created is conveyed by means of the path property in the argument to saveFile(), and the contents of the file are passed in via the data property. Just like in the case of createDirectory(), the callback does not receive any data; it would have been useful to receive an object that represents the newly created file but alas.

The saveFile() method can also be used to overwrite the contents of an existing file. You can test this in the sample application by entering the name of an existing file. Please note that neither the saveFile() method itself, nor the sample application warn against overwriting an existing file, but a serious application should probably check and prompt the user in such a case.

The saveFile() method is implemented by making a HTTP PUT request to the /api/repo/files service.

Discarding, restoring and renaming files

Phile also offers methods for discarding, restoring and renaming files.

Discarding files

Discarding a file means it will be removed from the user's point of view. This can either mean it is moved to the trash folder, or permanently deleted from the repository. Phile supports both operations using a single discard() method.

In the sample application, both modes of the discard() method are demonstrated in the deleteSelected() function:
    function deleteSelected(){
      if (!selected) {
        return;
      }
      var request = {
        success: function(options, xhr, data) {
          selected.parentNode.removeChild(selected);
          loadTrash();
        },
        failure: failure
      };
      var message, permanent;
      var properties = getFileNodeProperties(selected);
      var path = properties.path;
      if (selected.parentNode.id === "trash-nodes") {
        message = "Are you sure you want to permanently remove ";
        request.permanent = true;
        request.id = properties.id;
      }
      else {
        request.path = path;
        message = "Are you sure you want to discard ";
      }
      if (!confirm(message + path + "?")) {
        return;
      }
      phile.discard(request);
    }
The function builds a single request object which is passed to the discard() method of the Phile object in the last line of the function. Depending on whether the currently selected item is in the trash or a regular file or directory, different properties are set on the request object:
  • If the item is in the trash, a permanent property is set to true to indicate that the item should be permanently removed from the repository. Note that the permanent property can always be specified, even if the item is not in the trash. It's just that the sample application was designed to only permantly remove items from the trash. In addition, the id property is set on the request object to which the value of the id property of the file object is assigned.
  • If the item is not in the trash (and is thus either a directory or a regular file), only a path is set on the request object.
The success() callback is also set on the request, which removes the item that corresponds to the removed file from the gui and refreshes the view of the trash. The callback does not receive any data. This makes sense in case the object was permanently removed, but in case of moving the item to the crash it would have been nice to receive the file object that represents the moved file.

The discard() method has a slightly more complex implementation than any other method in the Phile object.

Ultimately, calling the discard() method results in a HTTP PUT request to either the delete or the deletepermanent service of the /api/repo/files API. The choice for delete or deletepermanent is controlled by the value of the permanent property on the argument passed to discard():
  • If permanent is true, deletepermanent will be used and the item will be permanently removed from the repository.
  • If permanent is absent or at least, not true, delete will be used and the item will be moved to the current user's trash folder.
However, unlike most (all?) other services that manipulate files and directories, delete and deletepermanent require that the file or directory to operate on is specified by it's id. As you might recall from our description of the file object, this is a GUID that uniquely identifies any item within the repository. So, to make the discard() function behave more like the other methods of the Phile object, measures have been taken to allow the caller to specify the file either as a path, or with a id: if an id is specified, that will always be used. But if no id is specified, Phile will see if a path was specified, and use that to make a call to getProperties() in order to retrieve the corresponding file object to extract its id, and then make another call to discard() using that id.

We mentioned earlier that as a convenience, you can specify a file object via the file property instead of a path, in which case the path property would be taken from that file object. The discard() method can also accept a file object as a specification for the file to be discarded, but in that case discard() will directly use the id property of that file object.

Restoring from the trash

You can restory items from the trash with the restore() method. The sample application demonstrates its usage in the restoreSelected() function:
    function restoreSelected(){
      if (!selected) {
        return;
      }
      var properties = getFileNodeProperties(selected);
      phile.restore({
        file: properties,
        success: function(options, xhr, data){
          var path = properties.originalParentFolderPath + Phile.separator + properties.name;
          createFileTree(path);
          loadTrash();
        },
        failure: failure
      });
    }
Currently you can specify the item to be restored from the trash using either an id or a file option. The sample application uses the latter. Currently, it is not possible to specify the file to be restored by its path (but you're welcome to implement it and send me a pull request). However, I currently feel that is not really that big of a problem, and might actually be a little bit confusing since items in the trash have both a path and an originalParentFolderPath.

The actual implementation of the restore() method relies on doing a HTTP PUT request to the /api/repo/files/restore service.

Renaming files

The rename() method can be used to rename files or directories. The sample application demonstrates its use in the renameSelected function:
    function renameSelected(){
      if (!selected) {
        return;
      }
      var newName = prompt("Please enter a new name for this file.", "new name");
      if (newName === null) {
        alert("You did not enter a name. Action will be canceled.");
        return;
      }
      var path = getFileNodePath(selected);
      phile.rename({
        path: path,
        newName: newName,
        success: function(options, xhr, data){
          debugger;
        },
        failure: failure
      });
    }
As usual, the file to operate on can be specified with a path (or file) property. The new name for the item should be a string and can be specified via the newName property.

The actual implementation of the rename() method relies on doing a HTTP PUT request to the /api/repo/files/rename service. However, I'm experiencing a problem in that this always results in a HTTP 500 status (Internal server error). However, the actual rename action does succeed. I filed a bug to report this issue here: BISERVER-12695.

Finally...

I hope this post was useful to you. Feel free to leave a comment on this blog. If you have a specific question about the project, then please report an issue on the github issue tracker. Remember, your feedback is very welcome, and I will gladly consider your requests to improve Phile or fix bugs. And I'd be even happier to receive your pull requests.

Friday, January 07, 2011

MQL-to-SQL: A JSON-based query language for your favorite RDBMS - Part III

This is the third article in a series providing background information to my talk for the MySQL User's conference, entitled MQL-to-SQL: a JSON-based Query Language for RDBMS Access from AJAX Applications.

In the first installment, I introduced freebase, an open shared database of the world's knowledge and its JSON-based query language, the Metaweb Query Language (MQL, pronounced Mickle). In addition, I discussed the JSON data format, its syntax, its relationship with the de-facto standard client side browser scripting language JavaScript, and its increasing relevance for modern AJAX-based webapplications.

The second installment provides a brief introduction to MQL as database query language, and how it compares to the de-facto standard query language for relational database systems (RDBMS), the Structured Query Language (SQL). I argued that MQL has some advantages over SQL, in particular for programming modern webapplications. I mentioned the following reasons:
  • Since MQL is JSON, and JSON is JavaScript, it's a more natural fit for modern AJAX applications
  • MQL is almost trivial to parse, making it much easier to write tool such as editors, but also to implement advanced authorization policies
  • MQL is easy to generate: the structure of MQL queries is mirrored by their results. A fragment of the result can be easily augmented with a subquery making it easy to subsequently drill down into the retrieved dataset
  • MQL is more declarative than SQL. Both attributes and relationships are represented as JSON object properties and one need not and cannot specify join conditions to combine data from different types of objects. The "cannot" is actually A Good Thing because it means one cannot make any mistakes.
  • MQL is more focussed on just the data. It largely lacks functions to transform data retrieved from the database forcing application developers to do data processing in the application or middleware layer, not in the database.
In this article, I want to discuss common practices in realizing data access for applications, especially web applications, and how database query languages like SQL and MQL fit in there.

Web Application Data Access Practices

After reading the introduction of this article, one might get the idea that I hate relational databases and SQL. I don't, I love them both! It's just that when developing database applications, especially for the web, SQL isn't helping much. Or rather, it's just one tiny clog in a massive clockwork that has to be set up again and again. Let me explain...

The Data Access Problem

It just happens to be the case that I'm an application developer. Typically, I develop rich internet and intranet applications and somewhere along the line, a database is involved for storing and retrieving data. So, I need to put "something" in place that allows the user to interact with a database via the web browser.
The way I write that I need "something" so the user can interact with the database, it seems like it's just one innocent little thing. In reality, "something" becomes a whole bunch of things that need to work together:
  • Browsers don't speak database wire protocols - they speak HTTP to back-end HTTP servers. No matter what, there is going to be some part that is accessible via HTTP that knows how to contact the database server. Examples of solutions to this part of the problem are Common Gateway Interface (CGI) programs, server-side scripting languages like PHP or Perl (which are often themselves implemented as a CGI program) or in the case of Java, specialized Servlet classes
  • The component at the HTTP server that mediates between web browser and database is going to require a protocol: a set of rules that determine how a URI, a HTTP method and parameters can lead to executing a database command. Examples of approaches to design such a protocol are Remote Procedure Calls (RPC) and Representational State Transfer (REST)
  • There's the way back too: the application running in the web browser is going to have to understand the results coming back from the database. For data, a choice has to be made for a particular data exchange format, typically eXtensible Markup Language or JSON
  • The user interface at the browser end need not only understand the protocol we invented for the data exchange, Ideally it should also guide the user and be able to validate whatever data the user is going to feed it. In other words, the user interface needs to have the metadata concerning the data exchange interface.

The Webservice Solution

A typical way to tackle the data access problem is:
  • analyze the functionality of the application, categorizing it into a series of clear and isolated actions
  • identify which data flows from application to database and back for each action
  • decide on a data representation, and a scheme to identify actions and parameters
  • create one or more programs in Java, Perl, PHP, Python, Ruby or whatever fits your stack that can execute the appropriate tasks and process the associated data flows to implement the actions
For web applications, the program or programs developed in this way are typically webservices that run as part of the process of the HTTP server. The client gets to do a HTTP request, to a particular URI, using the right HTTP method, and the right parameters. The service gets to process the parameters, execute tasks such as accessing a database, and finally, sending back a HTTP response, which typically contains data requested by the application.

Development Dynamics

The problem with the webservice approach is that it isn't very flexible. It presumes the application's functionality and hence the actions are quite well-defined. Although this looks reasonable on paper, in reality development tends to be quite evolutionary.

Typically, the core functionality of applications is quite well defined, but often a lot of additional functionalities are required. Although we can pretend these could be known in advance if only we'd spend more time designing and planning in advance, in practice, they often aren't. It may seem sad, but in many cases, the best way to find out is simply to start developing, and find out along the way. Agile is the latest buzzword that captures some of these development dynamics, but there have been other buzzwords for it in the past, such as RAD (rapid application development) and DSDM (dynamic systems development method).

The problem with this approach is that it requires a lot of going back-and-forth between front- and back-end development tasks: whenever the front-end wants to develop a new feature, it is often dependent upon the back-end offering a service for it. Front-end and back-end developers often are not the same people, so what we get is front-end development cycles having to wait on back-end development cycles to complete. Or in case front-end and back-end developers are the same person, they are constantly switching between tool sets and development environments.

In part this is because front-end development is usually done in JavaScript, and although server-side JavaScript is gaining ground, the server-side is still dominated mainly by Java, PHP, C++ and ASP.NET. But it's not just a programming language problem - developing a client, and especially a frond-end for end-users, presumes a very different mindset than developing a back-end process. Front-end development should focus on usability and quality user-experience; back-end development should focus on robustness, reliability, availability, scalability and performance. Although some of these aspects influence each other, in practice, front-end development is simply a different cup of tea than back-end development.

A Simple Plan: Building a Query Service

There is a very simple solution that would largely solve the data access problem without dealing with the inefficiencies of the recurring development process: If you could build a single service that can accept any parameters, understand them, and somehow return an appropriate result, we would never have to add functionality to the service itself. Instead, the front end application would somehow have to construct the right parameters to tell the service what it wants whenever the need arises.

This sounds almost like magic, right? So we must be kidding, right? Well, we're not kidding, and it's not magic either; it's more like a cheap parlour trick.
As the title of this section suggests, a query service fits this bill.

It would be very easy to build a single service that accepts a query as a parameter, and returns its result as response. And seriously, it's not that strange an idea: many people use between one and perhaps ten or twenty different services exactly like this everyday, multiple times...and it's called a search engine.

Can't we use something like that to solve our database access problem? Well, we could. But actually, someone beat you to it already.

DBSlayer, a Webservice for SQL Queries

A couple of years ago, The New York Times released DBSlayer. DBSlayer (DataBase accesS layer) is best described as a HTTP server that acts as a database proxy. It accepts regular SQL queries via a parameter in a regular HTTP GET request, and sends a HTTP response that contains the resulting data as JSON. It currently supports only MySQL databases but announcements were made that support was planned for other database products too. DBSlayer is actually a bit more than just a database access layer, as it also supports simple failover and round-robin request distribution, which can be used to scale out database requests. But I mention it here, because it implements exactly the kind of query service that would appear to solve all the aforementioned problems.

Or would it?

Every web developer and every database administrator should realize immediately that it's not a good idea. At least, not for internet-facing applications anyway. The DBSlayer developers documented that themselves quite clearly:
Access to the DBSlayer can be controlled via firewalls; the DBSlayer should never be exposed to the outside world.
... and ...
The account DBSlayer uses to access the MySQL database should not be allowed to execute dangerous operations like dropping tables or deleting rows. Ideally, the account would only be able to run selects and/or certain stored procedures.
So there's the rub: it may be very easy and convenient from the application development point of view, but it is a horrendous idea when you think about security. A general purpose SQL query service is simply too powerful.

If a web application accidentally allows arbitrary SQL to be executed, it would be called an SQL injection vulnerability, and it would be (or at least, should be) treated as a major breach of security. Creating a service that offers exactly that behavior as a feature doesn't lessen the security concerns at all.

What about a MQL query service

In this article I tried to explain the problems that must be solved in order to arrange and manage data access for web applications. The key message is that we need to create a service that provides data access. But in doing so, we have to balance between security, functionality and flexibility.

It is fairly easy to create a webservice that exactly fulfills a particular application requirement, thus ensuring security and manageability. However, this will usually be a very inflexible service, and it will need lots of maintenance to keep up with change in application requirements. It's also easy to create a webservice that is at least as powerful as the underlying database: this would be a database proxy over HTTP, just like DBSlayer. Although it will likely never need to change since it simply passes requests on to the back-end database, it is very hard to secure it in a way that would allow external requests from possibly malignant users.

I believe that an MQL webservice actually does offer the best of both worlds, without suffering from the disadvantages. A MQL query service will be flexible enough for most web applications - MQL queries are only limited by the underlying data model, not by the set of application-specific actions designed for one particular purpose. At the same time, it will be relatively easy to efficiently analyze MQL queries and apply policies to prevent malicious use. For example, checking that a MQL query doesn't join more than X tables is quite easy.

In the forthcoming installment, I will explore the concept of a MQL webservice in more detail, and I will explain more about the MQL-to SQL project. As always, I'm looking forward to your comments, suggestions and critique so don't hesitate to leave a comment.

MQL-to-SQL: A JSON-based query language for your favorite RDBMS - Part II

This is the second article in a series to provide some background to my talk for the MySQL User's conference.
The conference will be held April 11-14 2011 in the Hyatt Regency hotel in Santa Clara, California.

Abstract: MQL is a JSON-based database query language that has some very interesting features as compared to SQL, especially for modern (AJAX) web-applications. MQL is not a standard database query language, and currently only natively supported by Freebase. However, with MQL-to-SQL, a project that provides a SQL adapter for MQL, you can run MQL queries against any RDBMS with a SQL interface.

The my previous post, I covered some background information on modern web applications, JavaScript and JSON.
The topic of this installment is the MQL database query language and how it compares to SQL. In a third article, I will discuss the mql-to-sql project,
which implements a MQL service for relational database systems.

MQL Queries

A MQL query is either a JSON object, or a JSON array of objects. In fact, the sample object we discussed in previous section about JSON is nearly a valid MQL query. For now, consider the following JSON representation of the chemical element Oxygen:
{
"type": "/chemistry/chemical_element",
"name": "Oxygen",
"symbol": 'O',
"atomic_number": 8,
"ionization_energy": 13.6181,
"melting_point": -2.1835e+2,
"isotopes": []
}

The difference with the example from my previous post is that here, we added a key/value pair at the top, "type": "/chemistry/chemical_element", and we use an empty array ([])as value for the "isotopes" key.

So how is this a query? Didn't we claim JSON is a data format? So isn't this by definition data? And if this is data, how can it be a query?

Well, the answer is: yes, JSON is a data format, and so, yes: by definition, it must be data. But the paradox how some data can also be a query is quite easily solved once you realize that there are some pieces missing from the data. In this particular case, we left the "isotopes" array empty, whereas Oxygen has a bunch of isotopes in the real world.

This is how a JSON object can be a query:
  • By specifying values that describe known facts about an object, we define a filter or access path that can be used to find data in some database. For example, the JSON fragment above states that there exists an object of the type "/chemistry/chemical_element" which has the name "Oxygen", and the symbol "O", among other characterisics
  • By specifying special placeholders like null or an empty array or object (like [] and {} respectively), we define what data should be retrieved from the database so it can be returned in the result.

Executing a query with the Freebase MQL query editor


The easiest way to execute the query above is by using the Freebase Query Editor. Here's a screenshot:

In the screenshot, the left upper side is the area where you can type or paste your MQL query. You can then press the Run button, which is located below the query area on its right side.

When you hit the Run button, the query is sent to a special webservice called the mqlread service. The results appear in the results area on the right.

MQL Query Results

In the Text tab of the results area of the MQL query editor, you can see the raw response. It should be something like this:
{
"code": "/api/status/ok",
"result": {
"atomic_number": 8,
"ionization_energy": 13.6181,
"isotopes": [
"Oxygen-15",
"Oxygen-16",
"Oxygen-17",
"Oxygen-18",
"Oxygen-28",
"Oxygen-19",
"Oxygen-20",
"Oxygen-23",
"Oxygen-25",
"Oxygen-24",
"Oxygen-13",
"Oxygen-22",
"Oxygen-26",
"Oxygen-21",
"Oxygen-14",
"Oxygen-27",
"Oxygen-12"
],
"melting_point": -218.35,
"name": "Oxygen",
"symbol": "O",
"type": "/chemistry/chemical_element"
},
"status": "200 OK",
"transaction_id": "cache;cache01.p01.sjc1:8101;2010-05-15T03:08:10Z;0004"
}
As you can see, the response is again a JSON object. The outermost object is the result envelope, which contains the actual query result assigned to the result property, as well as a few other fields with information about how the request was fulfilled. (Actually, when we hit the Run button, our MQL query was also first embedded into a query envelope before it was sent to the mqlread service, but let's worry about those details later.

Query by Example, or filling-in-the blanks

If you compare the actual query result assigned to the "result" property of the result envelope with the original query, you will notice that they are largely the same: at least, the keys of query object and result object all match up, and so do the values, except the one for the "isotopes" key: whereas the "isotopes" property was an empty array in the query, in the result it contains an array filled with elements, each representing a particular isotope of the Oxygen element. These array elements were retrieved from the Oxygen entry stored in Freebase, which was found by matching the properties like "name": "Oxygen" and "atomic_number": 8.

This illustrates an important concept of MQL queries: the query result mirrors the structure of the query itself. Wherever the query contains a special placeholder value, such as an empty array ([]), a null value or an empty object ({}), the query engine will "fill in the blanks" with data that is retrieved from the database.

Another way of putting it is to say a MQL query is a query by example.

To hammer this concept down further, consider the following similar query. In this case, it contains even more special placeholder values (namely, the null values for all but the "name" property:
{
"type": "/chemistry/chemical_element",
"name": "Oxygen",
"symbol": null,
"atomic_number": null,
"ionization_energy": null,
"melting_point": null,
"isotopes": []
}
If you execute this query, you get a result that is essentially the same as the previous one. Because chemcical elements are identified by name (there are more properties that can identify a chemical element, name is just one of them), this query will match the same object in Freebase. This query specifies null for almost all other properties, and because null is a special placeholder for scalar values, the query engine responds by retrieving the values for those keys and returns them in the result.

Differences between MQL and SQL

Even this simple example reveals a lot about the differences between MQL and SQL.

Symmetry between Query and Result

In the previous example, we just saw that the query and its result have a similar structure. It's like the query is mirrored in the result. In SQL, this is very different. Let's see how different.

Assume for a minute we would have a relational database with a schema called "chemistry" and a table called "chemical_element". We could write an SQL query like this:
SELECT  name
, symbol
, atomic_number
, ionization_energy
, melting_point
FROM chemistry.chemical_element
WHERE name = 'Oxygen'
...and the result would look something like this:

+--------+--------+---------------+-------------------+---------------+
| name | symbol | atomic_number | ionization_energy | melting_point |
+--------+--------+---------------+-------------------+---------------+
| Oxygen | O | 8 | 13.6181 | -218.35 |
+--------+--------+---------------+-------------------+---------------+
Even if we forget for a moment that this particular SQL query doesn't handle retrieving the isotopes, the difference between the query and the result is striking - the structure of the SQL query, which is basically a piece of text, has very little to do with the structure of the result, which is unmistakenly tabular.

Everybody knows that SQL was designed for relational databases, so we probably shouldn't be too surprised that the result of the SQL query has a tablular form. But why does the query have to be text? Couldn't the query have been tabular too, leaving blanks where we'd expect to retrieve some data?

Lest I lose the proponents of SQL and the relational model here: don't get me wrong - I realize that an SQL query isn't just "a piece of text; rather SQL attempts to express facts about database state using a practical form of relational algebra, and the result represents the set that satisfies the facts stipulated by the query. And as we shall see later on, MQL actually has a few operator constructs that resemble the ones found in SQL. But for now the message is: MQL queries and their results look a lot like one another; SQL queries and their results do not.

Relational focus

I already mentioned the MQL is expressed in JSON, and we saw how both query and result are structured as objects. SQL results are always tabular. Superficially, this seems like the right thing to do for a query language that is supposed to work on and for relational database systems. But is it really that natural?

Many textbooks and courses about relational databases and SQL spend much time and effort on the topic of normalization, and rightly so! One of the great achievements of the relational model and normalization is that it helps to minimize or even eliminate data integrity problems that arise in non-relational storage structures and their inherent redundancy.

A typical textbook or course may start with a modelling exercise with some real-world, unnormalized (sub 1NF) data as input. The first mental step is to normalize that data to the first normal form (1NF), by splitting off multivalued attributes (a single data item with a list of values) and their big brother, repeating groups (a "table-inside-a-table"), to separate tables, on and on until all repeating groups and multi-valued attributes are eliminated. In the next phases of normalization, different forms of data redundancy are removed, moving up to even higher normal forms, typically finishing at the third normal form (3NF) or the Boyce-Codd normal form (BCNF).

The result of this normalization process is an overview of all separate independent sets of data, with their keys and relationships. From there, it's usually a small step to a physical database design. For example, in our Oxygen example, the list of isotopes is a multi-valued attribute. In a proper relational design, the isotopes would typically end up in a separate table of their own, with a foreign key pointing to a table containing the chemical elements.

The textbook usually continues with a crash course in SQL, and chances are the SELECT syntax is the first item on the menu. Within a chapter or two, you'll learn that although normalized storage is great for data maintenance, it isn't great for data presentation. Because applications and end users care a lot about data presentation, you'll learn how to use the JOIN operator to combine the results from related database tables. Usually the goal of that exercise is to end up with a result set that is typically 1NF, or at least, has a lower normal form than the source tables.

Don't get me wrong: this is great stuff! The relational model isn't fighting redundant data: its fighting data integrity issues, and the lack of control that results from storing redundant data. By first decomposing the data into independent sets for storage, and then using things like JOIN operations to re-combine them, SQL offers a good method to master data integrity issues, and to deliver consistent, reliable data to applications and end-users.

It's just a pity SQL forgot to finish what it started. Recall that the text-book modeling exercise started by eliminating repeating groups and multi-valued attributes to achieve 1NF. What SQL queries should we use to transform the data back to that format?

SQL turns out to be so single-mindedly focused on the relational model that it simply can't return sub-1NF, unnormalized data. Much like the square from flatland can't comprehend the sphere from spaceland, SQL simply hasn't got a clue about nested data structures, like multi-valued attributes and repeating groups.

Somewhere along the way, SQL forgot that the text-book course started with real-world, unnormalized data, full of repeating groups and multivalued attributes.

This represents quite a share of challenges for database application development. One class of software solutions that deal with solving this problem are the so-called object-relational mappers (ORM). It would not do enough credit to the ORM's to claim that their only purpose is to solve this problem, but it's definitely a major problem they take care of.

Parsing Queries

Everyone that has tried it knows that it isn't exactly trivial to write a fast yet fully functional SQL parser. Being able to parse SQL is a requirement for tools like query editors and report builders, but also for proxies and monitoring tools.

Parsing MQL on the other hand is almost trivially simple. At the application level, this would in theory make it quite easy to implement advanced access policies and limit the complexity of the queries on a per user or role basis.

Generating Queries

One of the things I like about MQL is that applications have to do a lot less work to formulate a query that drills down into some detail of a previously returned data set. For example, from the the result of our query about Oxygen, we just learned that there is an isotope called "Oxygen-16". Suppose we want to know more about that particular isotope, say, its relative abundance, and whether it's stable or not.

With SQL, we would have to construct a new algebraic expression that somehow combines the set of chemical elements with the set of isotopes. Although we would certainly need some data from the tabular result obtained from the previous query, we have little hope of actually re-using the query itself - at the application level, the SQL query is most likely just a piece of text, and it's probably not worth it to use string manipulation to forge a new query out of it.

Here's an example which shows the parts that should be added to accommodate this requirement, just to show that such a change isn't localized to just one spot in the original query:
SELECT  e.name
, e.symbol
, e.atomic_number
, e.ionization_energy
, e.melting_point
, i.name
, i.natural_abundance
, i.stable

FROM chemistry.chemical_element e
INNER JOIN chemistry.isotope i ON e.name = e.element_name
WHERE e.name = 'Oxygen'
AND i.name = 'Oxygen-16'
I won't let my head explode over the string manipulation code required to change the original query into this one. If you like, post clever solutions as a comment to this post :)

With MQL, this task is considerably easier. The query and the result have a high degree of correspondence. In our application, both would typically be represented as objects or structs. In most programming languages, it is trivial to go from the original Oxygen Query to an augmented form that retrieves the details about the isotope "Oxygen-16". This is especially true for JavaScript, where we'd simply write something like:
//execute the query and obtain the result.
//note: in a real application this would typically be an asynchronous request
//to the mqlread service which would accept and return JSON strings.
//For simplicity sake we pretend we have a mqlRead function that can accept
//a regular JavaScript object literal, convert it into a JSON string, and
//call the mqlread service synchronously, parse the the JSON query result
//into a JavaScript object and return that to the caller.

var queryResult = mqlRead({
type: "/chemistry/chemical_element",
name: "Oxygen",
symbol: null,
atomic_number: null,
ionization_energy: null,
melting_point: null,
isotopes: []
});

//assign a subquery for "Oxygen-16" to the isotopes property of the queryResult.
//Remember, the queryResult has the same structure as the original query, just
//with the null's and the empty arrays filled with data.

queryResult.isotopes = {
name: "Oxygen-16",
natural_abundance: null,
stable: null
};

//execute the modified query:

queryResult = mqlRead(queryResult);
(In the first example where we discussed the Oxygen query, you might've noticed that in the query result, the isotopes member was an array of strings, each representing a particular isotope. So naturally, you might be tempted to think that the value of the isotopes property is an array of strings. However, this is not quite the case. Rather, the isotopes property stands for the relationship between elements and its isotopes. Due to the form of the original query (having the empty array for the isotopes property), the MQL query engine responds by listing the default property of the related isotopes. In freebase, name is a special property and typically that's used as default property. So in that previous query result, the isotopes were merely represented only by their name. In the example above however, we assign a single object literal to the isotopes property which identifies one particular isotope. Because the isotopes property represents a relationship, the query should be read: "find me the oxygen element and the related isotope with the name Oxygen-16", and not "find me the oxygen element that has Oxygen-16 as its isotope".)

Of course, it's entirely possible to design data structures to hold all the data you need to generate your SQL queries (a query model). You can easily come up with something that would allow such a change to be made just as easy. But you need at least one extra step to generate the actual SQL string to send to the database. And of course, you need some extra steps again to extract data from the resultset for forging more queries.

In MQL, the effort to go from query to result and back are about as minimal as it can get. This results in less application code, which tends to be easier to understand.

SQL is declarative, but MQL is more so

When discussing its merits as a programming language, it is often mentioned that SQL is declarative rather than procedural. Often this is presented as an advantage: with a declarative language like SQL, we get to focus on the results we want, whereas a procedural language would force us to code all kinds of details about how these results should be obtained. Or so the story goes.

I won't deny SQL is declarative. For example, I don't need to spell out any particular data access algorithm required to find the Oxygen element and it's isotopes, I just write:
SELECT      e.name
, e.symbol
, e.atomic_number
, e.ionization_energy
, e.melting_point
, i.name
, i.natural_abundance
, i.stable
FROM chemistry.chemical_element e
INNER JOIN chemistry.chemical_element_isotope i
ON e.atomic_number = i.atomic_number
WHERE e.name = 'Oxygen'
But still: in order to successfully relate the chemical_element and chemical_element_isotope tables, I need to spell out that the values in chemical_element_isotope's atomic_number column have to be equal to the value in the atomic_number column of chemical_element. Come to think of it, how can I know the relationship is built on atomic_number, and not on symbol or name? And heaven forbid we accidentally compare the wrong columns, or forget one of the join conditions...

Now compare it to the equivalent MQL query:
{
"type": "/chemistry/chemical_element",
"name": "Oxygen",
"symbol": null,
"atomic_number": null,
"ionization_energy": null,
"melting_point": null,
"isotopes": [{
"name": null,
"natural_abundance": null,
"stable": null
}]
}

The SQL query may be declarative, but compared to the MQL query, it requires a lot more knowledge of the underlying data model. All we had to do in MQL, is specify an isotopes property, and list whatever we want to retrieve from the corresponding isotope instances. The only way we can mess up the MQL query is when we specify the wrong property names, in which case our query would simply fail to execute. In the SQL query, we could've been mistaken about which columns to compare, and get no result at all, or worse, a rubbish result. And with just a bit of ill luck, we can accidentally cause a cartesian product. Just for the hell of it, spot the error in the following SQL statement:
SELECT      e.name
, e.symbol
, e.atomic_number
, e.ionization_energy
, e.melting_point
, i.name
, i.natural_abundance
, i.stable
FROM chemistry.chemical_element e
INNER JOIN chemistry.chemical_element_isotope i
ON e.atomic_number = e.atomic_number
WHERE e.name = 'Oxygen'

Computational Completeness


Everybody with some experience in SQL programming knows that SQL is much more than a database query language. Even standard SQL is chock-full of operators and functions that allow you to build complex expressions and calculations. In fact, most SQL dialects support so many functions and operators that the manual needs at least a separate chapter to cover them. Algebra, Encryption, String formatting, String matching, Trigonometry: these are just a few categories of functions you can find in almost any SQL dialect I heard of.

By contrast, MQL is all about the data. MQL defines a set of relational operators, but their function and scope is limited to finding objects, not doing calculations on them. There is exactly one construct in MQL that resembles a function, and it is used for counting the number of items in a result set.

Personally, I think MQL would be better if it had a few more statistical or aggregate constructs like count. But overall, my current thinking is that the fact that MQL lacks the function-jungle present in most RDBMS-es is actually A Good Thing(tm). At the very least, it ensures queries stay focused on the data, and nothing but the data.

Next Time


In this article I discussed the basics of the MQL query language, and I compared SQL and MQL on a number of accounts. In the next installment, I will discuss how this relates to developing data access services for web applications.

DuckDb Performance: min, max, and median vs quantile

I was playing with some classical statistics in DuckDB and I ran into something I'd like to share. It's about the measures minimum,...