Creating a Dataset Utility App (Part 2)

Presto makes it easy to create applications that utilize enterprise data and services. This 2-part article discusses how to create a simple enterprise App using Presto Mashups and Ext JS.  The example that will be covered throughout the article is the Dataset Utility App.

The Dataset Utility App allows the user to input a dataset through a grid input.  The user can define columns with names and types and then enter data into grid rows.  When the user submits the dataset, a database table is created based on the defined columns and is populated with the user-provided data.  You can follow along with the article by searching for “Dataset Utility” in the search box of your Presto server.

Part 1 described the steps to create and initialize App JavaScript classes, showed how to render dynamic content with Ext JS, and described how to bind events to DOM elements.  Part 2 covers dynamically creating a database table with a custom Presto Mashup, dynamically publishing and activating Mashables, and persisting data using Mashable service operations.

Creating a Mashup that Performs Database Operations

Now that we can get the dataset from the user, we need to be able to create a table with the columns provided to house the data.  We can create the table by implementing a custom Presto Mashup using EMML.  The Create Dataset Mashup creates a database table based on a provided name and column information (i.e. column names and types).  To follow along, simply search for the "Create Dataset" in the search box of your Presto server.

The basic EMML mashup definition is shown below.

Most of the code shown here is a boilerplate that provides namespace information for our XML file. The aspects of this definition that are specific to our mashup include the mashup name (createDatasetMashup) and operation (runMashup).

The name must be unique among mashups defined on your Presto instance and serves to identify the Mashup when we invoke operations.  Operations define the logic for executing the Mashup.  In addition, each operation name must be unique within the Mashup.

The first step in defining mashup logic is to determine the expected inputs and outputs of the operation.  Here we need to provide the inputs necessary to generate a SQL table definition based on the user dataset.

Here we define an input named param of document type.  The document type allows for complex, structured input such as XML.  A default value is provided which demonstrates the structure of the expected XML.  Providing a default value is optional, but may help to give the caller more information about how your operation is invoked.

Notice that the XML structure of the input enables the caller to specify the table name and columns.  The Mashup operation will use this information to dynamically generate a SQL create table statement.  The output is simply defined as a variable named result with a type of document.  This enables us to return a success message or the details of an error condition to the caller.

Now that the inputs and outputs have been defined, we define the variables.

The first set of variables provide the connection information necessary to define a datasource.  The datasource is used to connect to the database where we intend to create the dataset.  Note that the name of the datasource should be unique on your Presto server.

The sqlScript variable will maintain the create statement SQL that we intend to generate.  The provider variable specifies the global Presto datasource defined for the database we are connecting to.  The schema variable defines the schema within the database that we will create the table.

Prior to creating the table, we need to determine if a table by the same name already exists.  

Presto provides a set of APIs to obtain meta-data about Mashups and Mashables.  We can find out the name of all existing tables within the schema by using the getProviderTables operation from the DBMashupManager service. By invoking this operation with our provider and schema as inputvariables, all tables in the schema will be placed in the specified outputvariable.

Once we have the necessary information, we can validate the caller input and generate the create statement SQL.  As this logic would result in complex EMML, a scripting language was used to simplify the implementation.

The Create Dataset Mashup uses Groovy to generate the sqlScript.  Presto supports a variety of scripting languages including JavaScript, jRuby, and, of course, Groovy.  This provides the freedom of choice to select the language you feel is best-suited to the problem.

Scripts can receive EMML variables through the inputvariables attribute and can return results through the outputvariable attribute.  The Groovy script parses the caller XML from the param input variable, ensures that the name of the dataset is not contained in the existingTables, and initializes the sqlScript variable with a create table SQL script.

Once we have prepared our sqlScript, we can use sqlUpdate to execute the SQL against the defined datasource.

Notice that result is the variable we defined for output previously.  This ensures that if execution of the SQL script fails, a descriptive error message will be returned to the caller.

Invoking our Mashup Data with jQuery

To invoke the Create Dataset Mashup operation we need to issue a POST request.  This is simple using the jQuery ajax function.

Initially we create the URL for the operation we intend to request.  When performing our Mashup operation the requestUrl will be initialized as:

The dataset definition is serialized to the expected XML format based on the data in the DatasetModel instance we saw in Part 1.  We then invoke the jQuery ajax function with the requestUrl and our datasetXml specifying the type of request as POST.  This will initiate an AJAX request to our Mashup operation and will send our datasetXml as the message body of the POST.

Notice that we specified processData as false.  Generally, jQuery prepares the data for the request, but in this case, our XML payload has already been prepared making any additional processing unnecessary.  The success attribute provides the callback function that will be invoked if the AJAX request completes successfully.

Dynamically Publishing Database Mashables

Once we have invoked the Create Dataset Mashup and the table has been successfully created for our dataset, we can use Presto APIs to publish the table as a Database Mashable.  Earlier we demonstrated that the DBMashupManager service could retrieve the tables for a provider and schema.  We can also expose a table as a Database Mashable through the generateAndPublishTableScript operation.

As before, we initially we create the URL for the operation we intend to request.  The requestUrl will be initialized as:

We then specify the data for the request:

• provider:  the global Presto datasource we want the operation to execute against
• schema:  the schema we want the operation to use
• tableName:  the name of the table to be registered with the Database Mashable
• serviceName:  the service name we are requesting for the database mashable

For the tableName we use the dataset name the user provided.  The serviceName specified follows the Database Mashable convention, all underscores are converted to camel-case format.  For more information on Database Mashable conventions you can refer to the Presto Documentation.

We then invoke the jQuery ajax function with the requestUrl and our data specifying the type of request as POST.  This will initiate an AJAX request to the Mashup operation and will send our data as the message body of the POST.  Notice that here we provide the data object without any processing.  jQuery handles this processing which simplifies our request logic.  Again, the success attribute provides a closure that is called if the AJAX request completes successfully.

Once the operation completes, a Database Mashable will be published for our table.  The result of the service call includes the actual service name of the published Mashable.  This will either be the serviceName we provided or some variant depending on whether the provided name is already in use. We can then use this serviceName to activate the service and save the data provided by the user.

Though we have published our Database Mashable, the service is deactivated by default.  We need to activate this service in order to call on any operations.  Again, we first create the URL for the operation we intend to request.  The requestUrl will be initialized as:

In this case we invoke the jQuery ajax function with a simple GET request to the requestUrl providing the serviceName we intend to activate.

Creating Mashup Data with jQuery

Once the service is activated, the data the user provided in the grid can be saved in our table by utilizing the service create operation.

Here the requestUrl is created according to Database Mashable conventions.  When the create operaiton is generated by the DBMashupManager, the default name of the insert operation is insert followed by the camel-case table name.  For example, if our table name was my_first_dataset the requestUrl would be initialized as:

In order to modify data through the create operation of our service we need to issue a POST request. As before, we invoke the jQuery ajax function.

Each row of data in the grid is sent to the create operation synchronously by recursively calling the _insertRowData operation with an incrementing rowIndex.  This allows us to verify that each row is inserted successfully prior to informing the user that the operation completed successfully.

The rowIndex is used to retrieve the data for each row from our DatasetModel instance.  Each row being inserted is given a unique datasetId.  As we recursively insert the rows, we continually check whether the nextRowIndex exceeds the number of rows provided by the user.  Once all rows have been successfully inserted, we notify the user that the dataset was saved successfully and reset the state of the utility.

This concludes part 2 which demonstrated the power of Presto APIs by dynamically creating a database table with a custom Presto Mashup, dynamically publishing and activating a Database Mashable, and persisting data using the published Mashable's service operations.  The completed App can now be included in any HTML page, iGoogle, or even Sharepoint by using the provided Presto App embedding capabilites.  Simply check out the “Embed” toolbar when accessing the Dataset Utility App for details.