Showing posts with label php. Show all posts
Showing posts with label php. Show all posts

Saturday, October 18, 2014

Performing administrative tasks on Pentaho 5.x Business Analytics Server using RESTful webservices and PHP/cURL

Yesterday, I noticed a discussion in the Pentaho Business Analytics group on linkedin: Using RESTful services to add users, add roles, add solutions, add datasources. In this discussion, Capital Markets Analyst/Consultant Rob Tholemeier writes:
We built some code in PHP that performs most if the 3.x admin console functions. Now with 5.x there appears to be RESTful services to do the same. Does anyone have code examples they are willing to share that uses RESTful services to add users, add roles, add solutions, add datasources? Change the same, assign roles, deletes the same?
I think it is an interesting question. I haven't seen many people integrating Pentaho in their PHP web applications so I decided to do a quick write up to demonstrate that this is not only possible but actually quite easy.

For this write up, I used the following software: Because everything is more fun with pictures, here's a high level overview of this setup:

Pentaho 5.x RESTful Webservices

Pentaho 5.x featured major refactoring to modernize its webservices to be more RESTful. Here's an overview of all the services.

All these webservices reside under the /api path beneath the path of the Pentaho web application, which is by default in the /pentaho path at the root of the server. Each service has a distinct path beneath the /api path. So assuming the pentaho server is running on the localhost (at the default port of 8080), you can access all calls offered by a particular service beneath http://localhost:8080/pentaho/api/service-specific-path.

The Pentaho 5.x webservices are, to some extent, self-documenting. You can get an overview of the available call for a specific service by doing a HTTP OPTIONS request to the root path of the service. For example, an OPTIONS request to http://localhost:8080/pentaho/api/session might return a document like this to describe the service:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<application xmlns="http://wadl.dev.java.net/2009/02">
  <doc xmlns:jersey="http://jersey.java.net/" jersey:generatedBy="Jersey: 1.16 11/28/2012 03:18 PM"/>
  <grammars>
    <include href="http://localhost:8080/pentaho/api/application.wadl/xsd0.xsd">
      <doc title="Generated" xml:lang="en"/>
    </include>
  </grammars>
  <resources base="http://localhost:8080/pentaho/api/">
    <resource path="session">
      <resource path="/setredirect">
        <method id="setredirect" name="GET">
          <response>
            <representation mediaType="text/plain"/>
          </response>
        </method>
      </resource>
      <resource path="/userWorkspaceDir">
        <method id="doGetCurrentUserDir" name="GET">
          <response>
            <representation mediaType="text/plain"/>
          </response>
        </method>
      </resource>
      <resource path="/workspaceDirForUser/{user}">
        <param xmlns:xs="http://www.w3.org/2001/XMLSchema" name="user" style="template" type="xs:string"/>
        <method id="doGetUserDir" name="GET">
          <response>
            <representation mediaType="text/plain"/>
          </response>
        </method>
      </resource>
    </resource>
  </resources>
</application>


The documentation provides a categorized overview of the service calls, as well as the necessary technical information, such as:
The HTTP method
In general, with very few exceptions, Pentho's web services use either GET (to retrieve information) or PUT (to modify or change data). There are a few calls that use POST (for example, the publish service). As far as I know, DELETE is not used.
Parameters
If a service accepts parameters, they are either passed through the query string or as a document in the request message body. This is colloquially known as the POST data, regardless of whether the POST method is actually used.

As per the design of the HTTP protocol, GET calls only accept parameters via the query string. The PUT calls to the Pentaho webservices sometimes accept parameters via the query string. This happens only if the information passed in the parameter has a relatively simple structure - think of a single key/value pair, or maybe a list of values. More complicated data is typically conveyed via the message body.
Data type information
In general Pentaho's web services support both XML and JSON. You can control the data format of the response by specifying the Accept request header. To specify the format of the request message body, you should use the Content-Type header in the request.
Unfortunately the documentation does not have any human readable descriptive information, so sometimes a little inspection and experimentation is required to figure out exactly how things work.

For this write-up, I decided to focus on the administrative interface around users, roles and privileges.

NOTE: The link above only lists the web services that are built into the Pentaho Business Analytics Platform. The platform can, and ususally does, have multiple plugins that offer extra functionality and some of these plugins ship by default with the server so that one might consider those also as builtins. Each plugin can, and often does, offer its own service calls, but these are not listed in the documentation referred to above. A discussion of these is also out of scope for this write-up but I will try and get back on this topic in a future blog post. If you don't feel like waiting that long, you can try and figure out the webservice calls offered by a particular plugin by doing an OPTIONS request to the root path corresponding to a particular plugin. The root path of a particular plugin is http://localhost:8080/pentaho/plugin/plugin-id/api. You can obtain a list of all installed plugins by doing a GET request the ids service of the PluginManagerResource services.

Suggested Tools

I found the following tools to be very useful in inspecting and experimenting with the Pentaho webservices api.
  • Administration perspective in the Pentaho user console. This web application ships with Pentaho and is normally used by Pentaho administrators to manage users, roles and privileges. Basically, we want to offer the functionality provided by this web application, but then with PHP.
  • The network tab in google chrome's developer tools. I used the network tab to monitor calls from Pentaho's administration perspective to the services provided by the Pentaho server.
  • Postman REST client. This is a handy extension from the chrome webstore. It allows you to do all kinds of REST calls directly from within your chrome browser. This was useful to test any assumptions on the details of how to exactly construct requests to the Pentaho web services.

The UserRoleDaoResource services

All functionality to work with users, roles and privileges is bundled in the UserRoleDaoResource service. The services specific path of this service is /userroledao. So, all calls that belong to the UserRoleDaoResource service can be accessed through the path http://localhost:8080/pentaho/api/userroledao.

The following categorized overview illustrates the different kinds of calls that belong to the UserRoleDaoResource service:
Users
Named accounts. An account is typically associated with a particular person that needs to work with the Pentaho server. The following calls are specific to working with users:
GET users
Retrieve the list of existing Pentaho user accounts.
PUT createUser
Create a new Pentaho user account.
PUT updatePassword
Modify the password of the specified user.
PUT deleteUsers
Remove one or more Pentaho user accounts.
Roles
Roles are basically a package of privileges (system roles), which can be assigned to one or more users. Any given user can be assigned multiple roles.
GET roles
Retrieve the list of existing roles.
PUT createRole
Create a new role. After the role is created, privileges can be assigned to it, and the role can then be assigned to one or more users, effectively granting the associated privileges to those users.
PUT deleteRoles
Remove one or more roles.
Assignments
Users can get assigned multiple roles and many users can be assigned a particular role. The following calls can be used to create or remove these assocations:
PUT assignAllRolesToUser
Assign all available roles to the specified user.
PUT assignAllUsersToRole
Assign the specified role to all available users.
PUT assignRoleToUser
Assign a specific role to a particular user.
PUT assignUserToRole
Assign a specific role to a particular user.
PUT removeAllRolesFromUser
Unassign whatever roles were assigned to a specific user.
PUT removeAllUsersFromRole
Take the specified role away from all users that were assigned the role.
PUT removeRoleFromUser
Unassign a specific role from a particular user.
PUT removeUserFromRole
Unassign a specific role from a particular user.
System Roles (also called Logical Roles)
These are essentially privileges: the ability to perform a particular action. Examples of such actions are Read BI content, Publish BI content, Schedule a job etc. In Pentaho, system roles cannot be assigned directly to users; instead, they have to be assigned to a role. Roles can then be associated to users to effectively grant them the privileges associated with the role.
GET logicalRoleMap
This returns a document containing two separate bits of information: the list of available system roles, as well as the association between regular roles and system roles.
PUT roleAssignments
Specify which system roles are associated with a particular regular role. Note that there is no separate call to add or remove individual associations between a role and a system role: rather, an entire set of system roles is assigned to a role at once, removing whatever set was assigned prior to that role.
Remember, you can always obtain the entire set of calls available for the UserRoleDaoResource service for your server by doing a OPTIONS request at the root of the /pentaho/api/userroledao path.

Webservice calls in PHP with cURL

Typically, calling out to HTTP (or FTP) servers from within PHP is done using the cURL library. A full discussion of cURL in PHP is well out of scope; you can refer to the - excellent - official PHP documentation instead. I will only discuss the basic pattern and only in as far as it applies to calling the Pentaho webservices.

Basic cURL calling sequence

The basic cURL calling sequence may be summarized as follows:
  1. Obtain a cURL handle by calling curl_init(). You should save the handle to a variable so you can use it in subsequent calls to the cURL library.
  2. Configure the cURL request by doing various calls to curl_setopt($handle, $option, $value). Each curl_setopt call basically sets a property ("option") on the cURL handle that is passed as first argument to curl_setopt(). The library defines a large number of property keys to control the various aspects of the HTTP request, such as the HTTP method, the request headers, message body etcetera.
  3. Call curl_exec() to send the request. This function will also return the response if a prior call to curl_setopt($handle, CURLOPT_RETURNTRANSFER, TRUE) was made.
  4. Optionally, information about the response can be obtained by calling curl_getinfo()
  5. Finally, curl_close($handle) should be called to clean up the cURL handle and free any underlying resources used by the library.

Basic GET request to Pentaho with PHP/cURL

The following snippet shows how to do a GET request to Pentaho using PHP/cURL:
<?php
//obtain a cURL handle
$c = curl_init();

//specify the url and the HTTP method
curl_setopt($c, CURLOPT_URL, 'http://localhost:8080/pentaho/api/userroledao/users');
curl_setopt($c, CURLOPT_CUSTOMREQUEST, 'GET');

//supply credentials to authenticate against pentaho
curl_setopt($curl_handle, CURLOPT_USERPWD, 'admin:password');

//tell cURL to return the response as a string
curl_setopt($c, CURLOPT_RETURNTRANSFER, TRUE);

//obtain the response
$response = curl_exec($c);

//get the HTTP status code
$status = curl_getinfo($c, CURLINFO_HTTP_CODE);

//clean up the cURL handle
curl_close($c);
?>
As you can see the snippet follows the general cURL calling sequence. The options CURLOPT_URL and CURLOPT_CUSTOMREQUEST are used to specify the url and the HTTP method respectively, and CURLOPT_RETURNTRANSFER is set to TRUE to obtain the response as a string result when calling curl_exec.

The CURLOPT_USERPWD option is used to specify the credentials for basic HTTP authentication. The value is a string consisting of the username and password, separated by a colon, and the example uses the default built-in administrator's account called admin with the password password.

Note: The web service requests described in this blog post require authentication with the admin account, or at least an account that is privileged to perform administrative actions. Other webservices my work while being authenticated with less privileged accounts.

No specific request headers were set in this example. Because there is no specific Accept header to specify a format for the response, the default format will be used, which happens to be XML.

After executing this snippet, the variable $response will have a string value equivalent to the following document:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<userList>
    <users>suzy</users>
    <users>pat</users>
    <users>tiffany</users>
    <users>admin</users>
</userList>
We could have added an extra call to curl_setopt() to explicitly set the Accept header to specify that we want to receive the response in the JSON format:
<?php
//obtain a cURL handle
$c = curl_init();

...other calls to curl_setopt...

curl_setopt($c, CURLOPT_HTTPHEADER, array(
  'Accept: application/json'
));

$response = curl_exec($c);

curl_close($c); 
?>
Note that all request headers must be passed as an array of strings using a single call to curl_setopt($handle, CURLOPT_HTTP_HEADER, $array_of_header). Each element of the array of headers should be a single string, consisting of the header name followed by a colon followed by the header value.

After executing this snippet, the variable $response will contain a string equivalent to the following JSON document:
{
    "users": [
        "suzy",
        "pat",
        "tiffany",
        "admin"
    ]
}
While the format for the response defaults to XML, it is generally a good idea to always explicitly specify it. In order to explicitly request for XML, change the value for the Accept header to application/xml.

Processing the response

PHP has support for both XML as well as JSON. In this write-up I'll only use XML but it is good to realize that it would have worked just as well if we had used JSON. As PHP offers exactly one library for working with JSON, there many options for processing XML. (I'm inclined to say, way too many.)

Fortunately, for this particular task, the XML documents are always simple and never very large, and I have had good results working with the SimpleXML library. I believe this is included and enabled by default, which makes it a safe choice. Another reason why I like SimpleXML is that it offers exceptionally convenient access to the data in the XML document using property access operators and iterators.

It would be outside the scope of this write-up to discuss SimpleXML in detail but the following snippet may illustrate how easy it is to process an XML document like the <userList> response obtained from the GET to the /userroledao/users API call described above:
<?php
  $userlist = ...response from /userroledao/users...

  //parse xml string
  $doc = simplexml_load_string($userlist);

  //iterate elements
  foreach ($doc as $user) {
    //do something with the <user> element.
    //even though $user is an object we can easily extract its value by treating it as a string
    echo('<div>'.$user.'</div>');
  }  
?>
As you can see, it doesn't ever get much simpler than this: one call to simplexml_load_string to parse the xml document, and we can directly traverse the elements using foreach. Plus accessing the text content of the elements is also very easy: no need for a separate call to extract the text, just treat the element as a string. Note that if you do need more advanced ways to traverse the structure of the document and access the data, the SimpleXML library still goes a long way. You can even use XPath expressions, if you really need that.

Putting it together: a simple Pentaho Admin application in PHP

Using the techniques described above, and an argueably minimal amount of client side javascript, I put together a simple, yet fully functional administrative application for managing Pentaho roles, users and privileges in PHP. It is a single, self-contained script (php, html template, and javascript - no css) of just over 600 lines, including whitespace and comments. Here's what the application looks like:



User management features

The left had side of the page is dedicated to user management. From top to bottom we have:
User form
In the left top we have a form with fields for "Username" and "Password", and a "Create User" button. Hitting the button results in a PUT request to /userroledao/createUser to create a new user using the values in the "Username" and "Password" fields.
Existing Users list
Halfway the page below the user form there's a list showing the existing users. This is populated with the data from the response of a GET request to /userroledao/users.
Delete selected users button
Hitting this button fires a javascript function that collects the selection from the existing user list. This is used to do a PUT request to the /userroledao/deleteUsers service in order to delete those user accounts.
User roles list
When a single user is selected in the existing users list a GET request is made to the /userroledao/roles service to create a list of all available roles. Another GET request is made to the /userroledao/userRoles service and the data from the response is used to set the state of the checkboxes in front of the role names, indicating which roles are assigned to the user. If such a checkbox is checked by user interaction, a PUT request is made to the /userroledao/assignRoleToUser service, which will assign the corresponding role to the currently selected user. If the checkbox gets unchecked through user interaction, a PUT request is made to the /userroledao/removeRoleFromUser service, which will unassign the corresponding role from the currently selected user.

Role management features

The right had side of the page is dedicated to role management. From top to bottom we have:
Role form
In the right top we have a form with a "Rolename" field and a "Create Role" button. Hitting the button results in a PUT request to /userroledao/createRole to create a new role with the specified role name.
Existing Roles list
Halfway the page below the role form there's a list showing the existing roles. This is populated with the data from the response of a GET request to /userroledao/roles.
Delete selected roles button
Hitting this button fires a javascript function that collects the selection from the existing roles list. This is used to do a PUT request to the /userroledao/deleteRoles service in order to delete those roles.
Role members list
When a single role is selected in the existing roles list a GET request is made to the /userroledao/users service to create a list of all available users. Another GET request is made to the /userroledao/userRoles service and the data from the response is used to check the appropriate checkboxes in front of the users names to indicate which users got the current role assigned. If such a checkbox is checked through user interaction, a PUT request is made to the /userroledao/assignUserToRole service to assign the currently selected role to the checked user. If such a checkbox gets unchecked due to user interaction, a PUT request is made to the /userroledao/removeUserFromRole service to unassign the currently selected role from the unchecked user.
Privileges (logical roles) list
If a single role is selected in the existing roles list, a GET request is done to the /userroledao/logicalRoleMap service. The data from the response is used to create a list of all available privileges. From the same response, the list of logical roles assigned to the role selected in the existing role list is used to check the checkboxes in front of the logical role names in order to indicate which logical role names are assigned to the currently selected role. When such a checkbox is checked, or unchecked, a PUT request is done to the /userroledao/roleAssignments service to associate the appropriate set of logical roles with the currently selected role

With a few (arguably non-essential) exceptions, this application covers all services of the UserRoleDaoResource.

Implementation details

For reference I will now discuss the implementation details of this application.

User form

The user form can be used to create new users. Here's its corresponding HTML code:
<form method="POST">
  <table>
    <tr>
      <td>Username:</td>
      <td><input type="text" name="user" /></td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><input type="password" name="password" /></td>
    </tr>
    <tr>
      <td colspan="2">
        <input type="submit" name="action" value="Create User"/>
      </td>
    </tr>
  </table>
</form>
Hitting the "Create User" button submits the form. But since the form element does not specify a specific action url, it will simply refresh the page, setting the form fields as POST data. In the top of the PHP script, this is handled with the following PHP code:
if (isset($_POST['action'])) {
  $action = strtolower($_POST['action']);
}
else {
  $action = NULL;
}
switch ($action) {
  case 'create user':
    $status = create_user($_POST['user'], $_POST['password']);
    break;
  case '...':
    ...
    break;
  
  ... many more case branches ...
}
In fact, all of the actions that the user can initiate result in a POST request that refreshes the page, setting a specific value for the action field to select the appropriate backend action. In case of the user form, this results in a call to the PHP function create_user(), passing the values of the POST data fields user and password, which originate from the HTML user form.

The PHP code of the create_user() function is shown below:
//create a user with specified name and password.
function create_user($user, $password){
  $c = curl_init();

  curl_setopt($c, CURLOPT_CUSTOMREQUEST, 'PUT');
  curl_setopt($c, CURLOPT_URL, 'http://localhost:8080/pentaho/api/userroledao/createUser');

  curl_setopt($curl_handle, CURLOPT_USERPWD, 'admin:password');

  curl_setopt($c, CURLOPT_POSTFIELDS, 
    '<user>'.
      '<userName>'.$user.'</userName>'.
      '<password>'.$password.'</password>'.
    '</user>'
  );
  curl_setopt($c, CURLOPT_HTTPHEADER, array(
    'Content-Type: application/xml'
  ));

  curl_exec($c);
  $status = curl_getinfo($c, CURLINFO_HTTP_CODE);

  curl_close($c);
  return $status;
}
The create_user function follows the basic pattern of the cURL calling sequence. The difference with the preceding generic example is that this is a PUT request, and thus the value for the CURLOPT_CUSTOMREQUEST option is 'PUT' rather than 'GET'.

The /userroledao/createUser is specified to take a user-element in the request message body, which is used to convey the user name and password. This element is constructed as a XML document and passed to the message body using a curl_setopt call using the CURLOPT_POSTFIELDS option.

Because we are passing a message body in the request, we also need to set the Content-Type header to application/xml to specify that the data passed in the message body is an XML document.

Finally, after the call to curl_exec, we use a call to curl_getinfo() using the CURLINFO_HTTP_CODE constant to obtain the HTTP-status of the request. This should be 200 if the PUT request succeeds. If there is some problem with the request we should receive a code in the 400 range (if the request itself has some problem) or the the 500 range (in case the server is experiencing some problem that is not related to this particular request). For example, if the user already exists, one gets a 403 (Forbidden) status instead of 200.

Note: The Pentaho REST services do not seem to have a robust way to convey the exact nature of the problem in case the request could not be met. At least, I have not noticed any useful information being conveyed in the response except for the HTTP status code. I checked the Administration perspective in the Pentaho user console to see what would happen in case an existing users is entered, and there the action just silently fails. It would be nice to get a recommendation about how to deal with error situations when using the Pentaho web service calls.

The Existing Users list

The existing users list is one of the simplest items in the interface. The HTML / php code is shown below:
<select multiple="true" id="Users" onchange="userSelectionChanged(this)">
>?php
  $users = get_users();
  foreach ($users as $user) {
?>
   <option><?php echo($user); ?></option>
<?php
  }
?>
</select>
The get_users() function is a simple GET request to /userroledao/users, followed by a parse of the XML response. Since both these aspects have been discussed already I will not repeat that code here.

The <select> element has an onchange event handler which calls to the javascript function userSelectionChanged(). Whenever the selection of the user list changes, that function will be called. This function will then determine if a single user is selected, and if that is the case, it will refresh the user role list by explicitly reloading the userRolesFrame:
function userSelectionChanged(list){

  //get the list selection as an array of user names 
  var selection = [];
  for (var i = 0, options = list.options, n = options.length; i < n; i++) {
    if (options[i].checked) {
      selection.push(options[i].value);
    }
  }

  //get the user roles frame
  var frame = document.getElementById("userRolesFrame");

  if (selection.length === 1) {
    //if there's exaclty one selected user, then load its assigned roles in the frame
    frame.src = "?view=userRoles&user=" + selection[0];
  }
  else {
    //blank the frame
    frame.src = "about://blank";
  }
}

The user roles list

We just disussed how selecting a single item in the existing user list refreshes the user role list by loading the userRolesFrame. The frame will be loading the current php script, passing values for the view and user parameters via the query string. The PHP script handles this by checking for the value of the view parameter in the query string. If now view parameter is present, the default interface will load as shown in the screenshot. But if specifying a value of userRoles for view will render only a list of roles, checking the roles that are assigned to the user specified by the user parameter, which is also passed via the query string:
<?php
  //see if a specific view was requested
  if (isset($_GET['view'])) {
    $view = $_GET['view'];
  }
  else {
    $view = NULL;
  }

  //select and render the requested view 
  switch ($view) {

    //render the user roles view
    case 'userRoles':

      //get the current user
      $user = $_GET['user'];

      //get the current user's assigned roles
      $roles = get_user_roles($user);

      //store the user's roles as rolenames in an array
      $assigned_roles = array();
      foreach ($roles as $role) {
        array_push($assigned_roles, ''.$role);
      }

      //get all roles
      $roles = get_roles();
      
      //render all roles as a list of divs with a checkbox
      foreach ($roles as $role) {

        //if the current role appears in the array of assigned roles, check the checkbox.
        $checked = in_array(''.$role, $assigned_roles);
?>
        <div>
          <input
            onchange="changeUserRoleAssignment(this)"
            name="<?php echo($role) ?>"
            type="checkbox"
            <?php echo ($checked ? 'checked="true"' : '')?>
          />
          <?php echo($role) ?>
        </div>
<?php
      }
      break;
    case '...':

      ...code to handle other views here...

    default:

      ...code for the regular interface (no specific view) goes here...
  }
?>
First, get_user_roles($user) is called to GET a response from the /userroledao/userRoles service, which is a list of roles for the specified user. From the php side of things, nothing new is really happening here. The only difference with regard to getting the list of users is the url, which is now /userroledao/userRoles rather than /userroledao/users and which includes a querystring parameter to specify the user:
  curl_setopt($c, CURLOPT_URL, 'http://localhost:8080/pentaho/api/userroledao/userRoles?userName='.$user);
The get_user_roles($user) function calls returns an XML document containing <role>-elements representing the roles assigned to the specified users. We use the foreach loop to iterate them and we store their string values (i.e., the actual role names) in the array $assigned_roles.

The remainder of the code is very similar to how the existing user list was rendered, except that we now use a call to get_roles() rather than get_users(). This does a GET request to /userroledao/roles and returns an XML document containing all available roles. We then iterate through that list to create an input-element of type checkbox along with the actual role name. The checkbox is checked according to whether the current role name is found in the previously populated $assigned_roles array.

Each checkbox is given an onchange handler which is implemented by the changeUserRoleAssignment() javascript function. This function sets a few variables in a form to indicate whether the corresponding role is to be assigned or unassigned, and the submits the form. The code for the form and the function are shown below:
<form 
  name="userRoleAssignment" method="POST" 
  action="?view=<?php echo($view)?>&user=<?php echo(urlencode($user))?>"
>
  <input type="hidden" name="action"/>
  <input type="hidden" name="role"/>
  <input type="hidden" name="user" value="<?php echo($user)?>"/>
</form>

<script type="text/javascript">
  function changeUserRoleAssignment(checkbox) {
    var form = document.forms["userRoleAssignment"];
    form.elements["action"].value = checkbox.checked ? "assign role to user" : "unassign role from user";
    form.elements["role"].value = checkbox.name;
    form.submit();
  }
</script>
The changeUserRoleAssignment() function writes its associated role name (stored in its name property) in the role field of the form, and it uses its checked state to set the value of the action field to assign role to user or unassign role from user. It then submits the form.

Since this code appears all in the user role view, it has the effect of refreshing only the frame wherein the view is contained. Because the form sets the action value, it triggers a PHP backend action before rendering the view (just like we saw in the implementation of the create user action):
switch ($action) {
  case 'create user':
    $status = create_user($_POST['user'], $_POST['password']);
    break;
  case 'assign role to user':
    assign_role_to_user($_POST['role'], $_POST['user']);
    break;
  case 'unassign role from user':
    unassign_role_from_user($_POST['role'], $_POST['user']);
    break;
  
  ... many more case branches ...
}
The PHP functions assign_role_to_user() and unassign_role_from_user() both perform a straightforward PUT request to the /userroledao/assignRoleToUser and /userroledao/removeRoleFromUser services respectively. For each these requests, the values of the user and role fields are passed to the service via the query string parameters userName and roleNames respectively.

Note that these two services support multiple role names; however only one is passed at any time by our application. Should you wish to pass multiple role names, then you should separate rolenames by a tab-character (ascii character 0x09). Note that since the names are passed in the query string, they must be url-encoded.

Finally

Although I haven't covered all implementation details, the rest is simply more of the same stuff. If you want to play with the code yourself, you can download the entire PHP script here.

Thursday, January 06, 2011

Speaking at the MySQL conference 2011

I just received a confirmation that my presentation proposal for the MySQL user conference 2011 was accepted! The title for my proposal is MQL-to-SQL: a JSON-based Query Language for RDBMS Access from AJAX Applications, and it covers pretty much everything implied by the title.

As always, the Hyatt Regency Hotel in Santa Clara, California serves as the venue. The conference will be held from April 11-14. Except for the venue and period, I think this year's conference will bear few similarities to previous editions. Let me try and explain.

This year's theme is "MySQL, the ecosystem and Beyond". This means that the conference is using MySQL as an anchor for a myriad of topics which are of interest to a large majority of MySQL users. This explicitly leaves room for subjects that may not be directly related to the MySQL product proper.

So, not only products with a direct link to MySQL, such as drizzle and MariaDB are covered; NoSQL databases like CouchDB, MongoDB and Cassandra are quite well represented and the conference committee actively reached out to the PostgreSQL community to submit proposals. Traditional topics like scalability, performance and tuning remain strongly present, just like high availability, failover, and replication. As always, some of the world experts in this field will be speaking. In addition, infrastructural topics like virtualization and cloud computing are well represented (but of course, especially with regard to database management); One of the things I'm thrilled about is the presence of developer and applicattion centric topics like GIS, rapid application development, and object relational mapping.

Just take a look at the full schedule to get a taste of what this event will be offering. Personally, I think it's a great setup, and I'm happy and honored to attend!

In a series of upcoming blog posts, I plan to explain some of the subject matter regarding my own talk. But for now, I just want to tell you that I think this is going to be a great conference! I'm looking forward to attending a lot of high quality sessions, and meeting world leading experts in the MySQL and open source database ecosystem. I hope to see you there!

Friday, September 26, 2008

Changing Lanes: Leaving Sun/MySQL

After having worked for two years and one quarter of a year with MySQL AB (now Sun), I have decided to resign and pursue another career.

Before I joined MySQL AB I was working as a database consultant and application developer. I knew that joining MySQL would mean giving that up, but I was happy to do so because I really liked the MySQL product. I was also attracted by the company itself, the flexible working hours, working from home, travelling abroad, and, very important, being one of the few companies that is capable creating FOSS software on a commercial basis.

I have had a splendid time at MySQL AB, and later Sun. I met lots of smart people and travelled to a bunch of places I would probably not've had the chance to visit otherwise. I learned a lot about the MySQL server, and a lot about the challenges involved in making an open source software company profitable. I've been given the chance to co-author a book on MySQL Cluster, and authored the cluster and associate certification exams. Finally, I created a training course that learns you how to write MySQL UDFs.

Looking back, I feel quite satisfied, and I'm thankful for having had this opportunity. However, slowly but surely I have noticed that I have been less involved in technical stuff, at least professionally. I have been spending so much time on hobby hacking in my spare time, that I reconsidered my position, and decided that I would really like to pick up application development again. At some point, a good opportunity came by, and I decided to seize it. So, that's what I did.

As of the 1st of October, next Wednesday, I will be working for Strukton Rail. I will be working for a department that's responsible for business process improvement and re-engineering. I will be doing a bit of business process analysis and consultancy, but the larger part of my job will consist of developing a Web application to model business processes and to support project management. These applications will have a distinctly graphical nature, and I have a pretty clear idea where I want that to end up. In addition I will be adding business intelligence to these applications.

I will be in the lucky position to be able to decide for a large part which technologies and products we will be using, and I think I will be able to build all of it on open source/free software. I am pretty certain that I will be using MySQL, Pentaho, PHP, ExtJs and Google Gears.

My intention is to keep blogging about MySQL. Most likely I will write more about all these other technologies too.

I'd like to finish by saying again what a wonderful time I had with MySQL/Sun! I wish MySQL/Sun the best of luck, and I want to thank all of my colleagues there. Thank you! I have learned a lot from you, and I'm looking forward to meeting again some place, some time.

Roland

Sunday, August 12, 2007

Transforming Japanese and Chinese text from utf8 encoded XML to ASCII Rich text (rtf)

This is one of those things that seemed extremely difficult and cumbersome, but turned out to be trivially simple, and relatively painless. Here is the case:

Using PHP to generate XML documents from a MySQL Database


I am working with a MySQL database storing lots human readable text. The text takes the form of semi-structured, well-formed XML. This particular XML-format is based on a very small set of elements borrowed from HTML and is used mostly for semantic markup of the text.

The XML fragments are fairly small, and not represent an end product. They are part of a larger unit: ultimately, they are a part of some document. The final document is not stored as a whole, because it is conceived and maintained on the level of its parts: all these individual XML fragments logically form distinct units.

Rather, complete documents are generated from the MySQL database using a PHP script. The PHP script sends SQL queries to the database to collect all XML fragments that belong in one document. This results in a well-formed XML document consisting of many XML fragments that belong together.
mysql-php-xml
(The details of the actual technique used for the actual generation of the XML documents are not discussed any further in this article. Is suffices to say that this can be done either in PHP, or in SQL or a combination of both.)

Natural language text translation and Unicode


There is a variable number of documents, and one particular document can appear in many translations. So, the actual character data comes in a variety of natural languages, such as English, German, Chinese and Japanese. For this reason, the database stores the XML in unicode.

The MySQL column(s) that store this data are defined like this:

xml MEDIUMTEXT
CHARACTER SET utf8
COLLATE utf8_unicode_ci

XSLT converts XML to various formats


The XML documents that are generated from the database do not represent an end product. Rather, the XML is further processed to yield a variety of output documents. Most of these output documents contain the majority of the data in the originally generated XML document.

However, each particular type of output document serves a different purpose, such as delivery to a customer, internal text review, online web presentation etc. In most cases, a particular output document requires a distinct format that best serves its purpose. So, the same information is required in a variety of presentations, such as plain text, HTML and rtf.
xslt-to-several-ouput-formats
XSLT is used to transform the XML documents generated from the database into the desired target format(s). XSLT is a powerful special purpose language that is especially designed to transform XML input to plain text, HTML or XML output.

XSLT transformations are themselves denoted using XML. The transformation can be defined using a wide variety of constructs. XSLT uses XPath to define patterns that are matched in the input document. These patterns are associated with templates to generate output.

XSLT Templates can emit any structural XML construct, and in addition cause particular pattern matching rules to be applied, or perform traditional procedural processing on the input document (such as iteration, or explicitly call another template to be applied.)

Why an intermediate step?


Although it is possible to directly generate the desired target documents from the database, an intermediate step through XML has a number of advantages.

Single interface to extract data


With the intermediate XML step, there is just one piece of software that interacts with the database to extract data. This makes the solution more resilient to changes in the database schema. In many cases, a change in schema only requires the XML export functionality to be adjusted - the XSLT transformations can remain unchanged as long as equivalent input XML is generated. Instead of writing (and maintaining!) the-same-but-slightly-different queries, JOINs, ORDER BY's, GROUP BY's over and over, only the XML export script needs to be adjusted.

Translations of human readable text


There is another reason that justifies the intermediate XML format. The text stored in the database is conceived in one language -English- first, and then translated by human translators. Obviously, the translators need the original text first so they can translate it into the equivalent in their respective language. When the translators are done, it must be imported back again into the database.

This process is currently implemented by sending the XML document directly to the translators. The XML format is quite simple and intuitive, and translators are asked to replace the stretches of English text with the corresponding translated text. This is done in a straightforward manner, by directly editing the XML.
translations
Importing a translation is, again, a matter of applying an XSLT transformation, this time from XML to SQL.
db-load-translation
(The process is actually a little more complicated because there is also some checking involved to ensure that the structure received from the translators matches the expected structure. In this case, every structural element in the untranslated document must also be present in the translated document. Also, the translated document must not introduce any new structural elements.)

Rich Text Format


For a number of purposes, the application needs to generate output documents in the Rich Text Format format.

Rich Text Format or RTF is a proprietary data format for representing text and (simple) markup. It is owned by Microsoft

RTF is ASCII encoded: all characters in RTF documents are 7-bit ASCII characters. 8-bit characters and also unicode characters are supported in modern versions using special escape sequences. A RTF unicode character is denoted like this:

\u<decimal character code><ASCII character>

So, \u initiates a unicode escape sequence telling the RTF agent to expect an integer that specifies a character in the unicode character set. Immediately after that, there must be a normal ASCII character that can be used by RTF agents that do not implement the substitution of the unicode escape sequence with the respective unicode character.

For example, the text

日本語

can be written like this

\u26085?\u26412?\u35486?

in RTF. Unicode aware RTF agents should print the Japanese text 日本語. RTF clients that do not understand or implement the unicode escape will use the question mark instead, because that is placed immediately after each escape sequence:

???

Generating RTF from a unicode source with XSLT


As described earlier, XSLT is used to generate RTF documents (among others). So how do we ensure that the unicode characters in the input documents are converted to ASCII characters? How do we make sure the unicode characters that do not fit in ASCII are properly escaped using the RTF unicode escape syntax?

This is something that looked like a very hard problem to solve quickly and cleanly. Luckily, after only little experimentation, it turned out to be very, very easy, and if required almost no extra code. The solution consists of two steps. One step involves a very minor but extremely important modification at the XSLT level. The other step involves some processing of the transformation result.

Choosing an ASCII output encoding with <xsl:output>


XSLT transformations can optionally contain the <xsl:output> element to control various aspects of the generated output. I used <xsl:output> many times before to control whether the output of the transformation should be done using xml, plain text or HTML. The <xsl:output> element can also be used to specify the character encoding of the tranformation result.

Since RTF is essentially an ASCII format, this is the encoding we need to specify:

<xsl:output
method="text"
encoding="ascii"
/>

This ensures that only ASCII characters result from the transformation process, even if the input document is in, say an utf8 encoded text (as is the case here). The first 127 unicode characters map to the equivalent ASCII character. Characters with a character code that exceeds 127 are output using XML numeric character references. So for example,

日本語

becomes:

&#26085;&#26412;&#35486;

Looks familiar? Thought so ;)

Replacing XML entity references with RTF unicode escapes using PHP regular expressions


So after letting XSLT do the dirty work of performing the character code conversions and escaping them to entity references, we still need one final step to make RTF unicode escape sequences of these entity references. As we are in a PHP environment, this is a perfect job for the Pearl compatible regular expression extension:

$rtf = preg_replace(
'/&#([0-9]+);/'
'\u${1}?'
, $xslt_result
);

And that is all there is to it. Maybe a tad ugly, but it works ;)

Friday, April 20, 2007

So Wordpress does not like the MySQL SQL Mode

I just downloaded Wordpress to play around with. However, installation failed pretty miserably. The nature of the error messages led me to believe Wordpress does not like the SQL_MODE setting of my lcoal MySQL database.

By default, I have a fairly restrictive SQL_MODE for my local database server, which is set up by this line in my my.cnf option file:

sql_mode=TRADITIONAL,ANSI_QUOTES,PIPES_AS_CONCAT,IGNORE_SPACE,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION

Now, a lot of people don't know you can SET the SQL_MODE on a per-session basis. (A lot of people also don't know SQL_MODE is available as of MySQL 4.1, but that's another story).

Anyway, I just fixed problem. It really was very, very easy:

Find out which php script actually connects to MySQL


roland@roland-laptop:/var/www/wordpress$ grep -Rn mysql_connect *
wp-includes/wp-db.php:52: $this->dbh = @mysql_connect($dbhost, $dbuser, $dbpassword);

Open wp-includes/wp-db.php to edit it


roland@roland-laptop:/var/www/wordpress$ kedit wp-includes/wp-db.php

Locate the mysql_connect line, and after the wordpress code that checks whether the connection succeeds, add just one line of code:


// fix sql_mode
mysql_query("SET SESSION SQL_MODE := ''",$this->dbh);
// end fix sql_mode
$this->select($dbname);


So, basically, I reset the SQL_MODE to the default for only this session, and let Wordpress do its thing. Well, that seems to help...at least I can install now ;)

Of course, I just reverted from a rather restrictive setting for the SQL_MODE to an extremely permissive one. But you can do it the other way around too of course!

For example, if your ISP only provides a vanilla MySQL 4.1 or up, you might find MySQL too permissive in accepting invalid dates, or you might not like the way MySQL handles the GROUP BY clause.

Well, it's easy to setup a generic include script to connect to MySQL, just like Wordpress does, and then including a SET SESSION SQL_MODE statement, just like I did. Obviously, in that case you would assign a comma-separated list of SQL_MODEs to it to make MySQL more restrictive. Look here for a full list of SQL_Modes.

Tuesday, March 13, 2007

So, are Database Stored Procedures Good or Bad?

A little less than a week ago, I opened a zoomerang survey about database stored procedures. In this post, I'm presenting the results.

In just a few days, the survey was visited 232 times, and no less than 155 people completed the survey! (There was a very small (2) number of people that answered some, but not all the items, and these are excluded from the results proper.)

I didn't really know what to expect, but I did not expect as much as this! So, I'm quite pleased, and I want to thank everybody that took the time to complete the survey. Thank You very, very much, I appreciate your efforts a lot!

Before we Begin


Before I present the results, I want to explain a few things about the survey.

First of all, I want to stress that the results I am about to present are not to be generalized. There is no way to discover to what extent the responses are representative for a larger group.

To put things in perspective, one should realize that I actively solicited response by posting announcements on my own blog, the O'Reilly Database blog and the MySQL stored procedure forum. In addition, I posted to the PlanetMySQL Google Group.

If you want to use the results somewhere, feel free but be sure to link back here so people can judge for themselves what the status is of the results. Thank you.

Secondly, why do I need this survey? Isn't it clear when and where to use stored procedures?

Well, I have the impression that there are quite a few myths concerning stored procedures in general. Stored procedures seem to be the kind of topic that deeply divides database professionals in two camps. The discussion whether stored procedures are "Good" or "Bad" seems to have revived ever since MySQL started supporting them.

I happen to believe that stored procedures are Good in some cases, but Bad in others. I also think that in most cases, it is possible to deduce whether one should use a stored procedure - and when not. I've been wanting to write an article about this for some time now. However, there are already a lot of articles about it, and I decided it would be fun to link it to an inquiry so it would be easier for people to relate to it.

(The article is going to have to wait until next time - I'm just presenting the results of the survey -your opinion- today)

Now - without further ado - the results!

Performance


The first item in the survey was:
Certain application tasks can be implemented either as a server-side stored procedure or using client-side application code and 'pure' SQL statements. What is your opinion on the difference in performance between these two approaches?
Three alternative answers were offered:
  • Server side stored procedures are faster than client side application code and pure SQL statements (72%; 112)
  • Server side stored procedures are slower than client side application code and pure SQL statements (8%; 12)
  • I have a different opinion (20%; 31)
Here's a pie chart of the results:

Database Stored Procedures - Good or Bad-RawData_html_m368298f8

I must say, this one really surprised me. There seems to be quite a large group of people that have the opinion that procedures outperform normal, plain SQL statements. The people that think the opposite are in the smallest group. There is quite a substantial group that did not want to choose a particular stand and chose to formulate a different opinion.

In the group that chose "I have a different opinion", the vast majority (25 respondents, 16% of the total) provided an answer to the effect that it all depends on the specific situation. In some cases, a reason was given, usually to indicate that stored procedures could help to reduce network roundtrips.

One respondent had a very original and interesting answer:
Maybe faster but a severe CPU scalability risk.
If I understand correctly, what is meant is that although executing an individual stored procedure maybe faster than executing the equivalent application code, stored procedures can have an adverse effect on scaling up the solution.

Because stored procedures reside in the database, and are executed on the database server, the application will be limited by the processing power of the database server. This can be remedied by physically separating the processing tasks from the database tasks. This allows one to grow the application layer more independently from the data layer - something which will become more and more important as the number of application users grows.

Two persons gave a response that was very MySQL specific. Here are the responses:
Neither. Depends with MySQL on num of things...
and
*MySQL* SP are slower than normal SQL
I find the latter comment interesting because MySQL stored procedures are indeed different from, say, Oracle or MS SQL procedures in this respect. MySQL compiles stored procedures very differently than Oracle or MS SQL does. I'm planning to illustrate this in more detail next time.

A minority (2 respondents) just didn't have a specific opinion, and 1 person argued that it didn't matter at all, and that stored procedure performance would be about the same as that of application code running ordinary SQL statements.

Portabilty

The second item in the survey:
What is your opinion on the effect that stored procedures have for the portability of database applications?
Again, three alternative answers were offered:
  • Using stored procedures increases the portability of database applications (21%; 33)
  • Using stored procedures decreases the portability of database applications (68%; 105)
  • I have a different opinion (11%; 17)
And the pie chart of the results:

Database Stored Procedures - Good or Bad-RawData_html_m7d5cfeeb

There's a large majority that thinks stored procedures make applications less portable. The second largest group thinks the opposite. The group of people that have another opinion is now much smaller as compared to the previous item, but still substantial.

The largest (7) group of people with another opinion say something to the effect of "It depends". I was a bit surprised that only two people answered:
Portability of what - application or database?
Three individuals answered that portability is indeed hampered, but they argued that portability just isn't an issue at all, because they don't port. (Presumably, they mean they don't ever port the database)

Appropriateness

The third item tried to probe how people felt about database stored procedures in general. Given that databases are supposed to offer an implementation of the relational model, some people argue that stored procedures are actually completey alien and should not be a part of them. So my third item was:
Which of the following statements best matches your own opinion regarding stored procedure support in relational database products?
Again, three alternatives:

  • A stored procedure is a completely non-relational thing. Therefore, stored procedures do not belong inside a relational database management system. (12%; 19)

  • Stored procedures offer functionality that is complementary to the relational features of a relational database management system. Therefore, stored procedures are a useful feature for building database applications. (83%; 127)

  • I have a different opinion (5%; 7)

Here's a piechart of the results:

Database Stored Procedures - Good or Bad-RawData_html_42b9a6c1

So, this certainly is the least controversial item when looking at the numbers: a clear majority seems convinced that stored procedures are a useful tool for building applications. Although it is still considerable, it is the minority that feels stored prodedures are inappropriate (the survey does not grant extra points for passionate defense of the opinons ;).

In this case the group of people with different opinions was small, at least as compared to the other items. It was also not really possible to categorize these responses any further: everyone had their own reason for not choosing one of the other alternatives.

Programming Languages

The survey also inquired after the programming languages people use for their stored procedures and application. These item are a bit different as they ask about a fact rather than an opinion. Also, people could select multiple answers in addition to adding extra options.

First of all, I wanted to know which stored procedure languages are used. The item:
Which stored procedure language do you use regularly to build databases/database applications? Please check only those languages from which you use procedural constructs (rather than pure SQL constructs)
People could choose from these languages:

  • DB2 Stored Procedures - IBM DB2 (5%; 8)

  • java stored procedures - Oracle (5%; 8)

  • MySQL Stored Procedures - MySQL (45%; 65)

  • PL/pgSQL - Postgres (18%; 26)

  • PL/SQL - Oracle (36%; 52)

  • T-SQL - Sybase (7%; 10)

  • T-SQL - MS SQL Server (33%; 48)

  • .NET stored procedures - MS SQL Server (6%; 9)

  • Other, specify database vendor and language name (10%; 15)
Here's a bar chart, sorted by frequency:

Database Stored Procedures - Good or Bad-RawData_html_1d6d0594

So, most people that did the survey use MySQL. This is not very surprising as it is my usual topic (and I expect most respondents to be reader of my blog). Still, a considerable number of respondents use other languages (too); Oracle PL/SQL, and T-SQL (MS SQL) are really not that far behind. Beyond that, the numbers become considerably smaller, although PL/pgSQL (Postgres) and "Other" are still quite significant.

The "Other" category indicated two important omissions on my part: 6 respondents (4%) named "Firebird", "Interbase" or some combination of both. I feel I should've included that in the survey and I'm ashamed to say it did not cross my mind when I created the survey. Second to that, 5 respondents (3%) specified that they used no stored procedures at all, or at least not regularly. I should have made a separate category for that too I guess.

What is maybe one of the most interesting obervations is the relatively small but still substantial amount of general purpose languages in the stored procedure arena. Oracle java stored procedures (8 respondents; 5%) and MS SQL .NET stored procedures (9 respondents; 6%) show that these things are actually used. It would be very interesting to find out if these respondents used that in addition to or instead of traditional PL/SQL and T-SQL.

The last item in the survey was about application programming languages. The item:
Which computer languages do you use regularly for programming database applications?
The options were:

  • C (17%; 25)

  • C++ (15%; 23)

  • C# .NET (19%; 28)

  • COBOL (1%; 1)

  • Delphi (5%; 8)

  • Java (38%; 57)

  • Javascript (14%; 21)

  • LISP (0%; 0)

  • Perl (33%; 50)

  • Python (15%; 23)

  • PHP (53%; 80)

  • Smalltalk (1%; 2)

  • VB (9%; 13)

  • VB .NET (8%; 12)

  • VBScript (5%; 8)

  • .NET - other (1%; 1)

  • Other (15%; 23)
And the bar chart:

Database Stored Procedures - Good or Bad-RawData_html_15ca2d92

Clearly, PHP is the most popular language among the respondents, but Java and Perl are also quite strong. After that, there are quite some languages that certainly are used, but not by far as much as PHP, Java and Perl.

The "other" category showed quite a few recurring responses, again indicating I forgot to mention a few important ones. Ruby, Rails, and combinations of both were mentioned 13 times (8%) which is too substantial to ignore. PL/SQL was mentioned 4 times (3%) and it would be interesting to see if these respondents mean they use PL/SQL to create full blown end-user (web)applications (this is a feature I heavily used when I was working as application developer) or 'just' utilities, ETL and the like.

Summary



  • Most repondents think stored procedures are faster than pure SQL and application code

  • Most repondents think stored procedures make the application less portable

  • Most repondents think stored procedures are a useful addition to the database

  • Most repondents use MySQL, PL/SQL and/or MS SQL stored procedures

  • Most repondents use PHP, Java and/or Perl


Stuff that remains to be done


These are just the results. I'm planning to do a real analysis of the results to see if I can identify groups of respondents. For example, it seems obvious that respondents that use PHP and MySQL stored procedures are also the ones that think stored procedures are a good tool for application development, and that they are faster but less portable (it's a safe assumption, as these sum up all majorities ;). But how well do these groups really hang together? This an other things shortly on this blog.

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,...