Querying Data Services


	Contents Generate the Query Model Globus Credential Helper Method Add Data Service Actions Update GridClient.rb Update Query Controller Create Query View Files Update Existing Query View Files Update Routes Add caGrid Jars Add Query Tab Saving And Running Queries

In this section, you will add the ability for users to query available data services utilizing the CQL Query functionality of caGrid. Users will be able to add modifiers to their queries and save each query for later use. The results from each query will be displayed in a table for the users to view.

Generate the Query Model

In the terminal, use the Rails scaffold command to create the query model, controller and views:

GridClient %> rails generate scaffold query object:string endpoint:string modifier:string description:string user_id:integer

Now migrate the table into the current database using the rake command:

GridClient %> rake db:migrate

Globus Credential Helper Method

Add a method to app/controllers/application_controller.rb, a line to indicate it as a helper method and a global variable for storing available services:

"application_controller.rb"

  helper_method :getGlobusCred

  # Global Variable holding availabe services
  $SERVICES

  def getGlobusCred
    return session[:globus]
  end

Add Data Service Actions

Create a new Ruby file lib/DataServiceActions.rb and copy in the following code:

include_class "gov.nih.nci.cagrid.cqlresultset.CQLQueryResults"
include_class "gov.nih.nci.cagrid.data.client.DataServiceClient"
include_class "gov.nih.nci.cagrid.data.utilities.CQLQueryResultsIterator"

module CqlQuery
  include_package "gov.nih.nci.cagrid.cqlquery"
end

class DataServiceActions
  def self.invokeNonSecureService (serviceEndpoint, objectName, modifier)
    puts "Querying: " + objectName
    
    begin
      client = DataServiceClient.new(serviceEndpoint)
      query = CqlQuery::CQLQuery.new
      target = CqlQuery::Object.new()
      target.setName(objectName)
      query.setTarget(target)
      
      # Set Modifiers if they exist
      queryModifier = CqlQuery::QueryModifier.new
      if modifier.at(0).eql?("count")
        queryModifier.setCountOnly(true)
        query.setQueryModifier(queryModifier)
        
      elsif modifier.at(0).eql?("distinct_attribute")
        queryModifier.setDistinctAttribute(modifier.at(1).to_java_string)
        query.setQueryModifier(queryModifier)
        
      elsif modifier.at(0).eql?("selected_attributes")   
        queryModifier.setAttributeNames(modifier.drop(1).to_java(:string))
        query.setQueryModifier(queryModifier)
      end
      
      #execute the query
      results = client.query(query)
      iter = CQLQueryResultsIterator.new(results, true)
      
      resultsXML = String.new
      while (iter.hasNext)
        singleResult = iter.next()
        resultsXML << singleResult
      end
      
      return resultsXML
    rescue
      puts "Error: " + $!
    end
  end
  
  def self.invokeSecureService (serviceEndpoint, cred, objectName, modifier)
    
    begin
      client = DataServiceClient.new(serviceEndpoint, cred)
      query = CqlQuery::CQLQuery.new
      target = CqlQuery::Object.new()
      target.setName(objectName)
      query.setTarget(target)
      
      # Set Modifiers if they exist
      queryModifier = CqlQuery::QueryModifier.new
      if modifier.at(0).eql?("count")
        queryModifier.setCountOnly(true)
        query.setQueryModifier(queryModifier)
        
      elsif modifier.at(0).eql?("distinct_attribute")
        queryModifier.setDistinctAttribute(modifier.at(1).to_java_string)
        query.setQueryModifier(queryModifier)
        
      elsif modifier.at(0).eql?("selected_attributes")   
        queryModifier.setAttributeNames(modifier.drop(1).to_java(:string))
        query.setQueryModifier(queryModifier)
      end
      
      #execute the query
      results = client.query(query)
      iter = CQLQueryResultsIterator.new(results, true)
      resultsXML = String.new
      while (iter.hasNext)
        singleResult = iter.next()
        resultsXML << singleResult
      end
      
      return resultsXML
    rescue
      puts "Error: " + $!
    end
  end
end

The Module CqlQuery definition makes it possible to instantiate all classes provided in the package "gov.nih.nci.cagrid.cqlquery" using the notation "CqlQuery::CQLQuery.new" or "CqlQuery::Object.new()" (parentheses are optional in Ruby).

Update GridClient.rb

Open the file lib/GridClient.rb and add the following include statement and modules to the top of the file and the methods below to GridClientClass:

"GridClient.rb"

include_class "org.apache.axis.message.addressing.EndpointReferenceType"

module Types
  include_package "org.apache.axis.types"
end

  def displayDomainObjectNames(epr)
    begin
      endpoint = EndpointReferenceType.new(Types::URI.new(epr.to_java_string))
      domainModel = MetadataUtils.getDomainModel(endpoint)
      umlClassCollection = domainModel.getExposedUMLClassCollection().getUMLClass()
      objects = Array.new
      umlClassCollection.each { |uml|
        objectName = uml.getPackageName + "." + uml.getClassName
        objects << objectName
      }
     
      return objects
    rescue
      puts "Error in displayDomainObjectNames: " + $!
    end
  end
  
  def displayDomainObjectAttributes(epr, object)
    begin
      endpoint = EndpointReferenceType.new(Types::URI.new(epr.to_java_string))
      domainModel = MetadataUtils.getDomainModel(endpoint)
      umlClassCollection = domainModel.getExposedUMLClassCollection().getUMLClass()
      attributesObjects = Array.new
      umlClassCollection.each { |uml|
        objectName = uml.getPackageName + "." + uml.getClassName
        if objectName.eql?(object)
          attributesObjects = uml.getUmlAttributeCollection.getUMLAttribute
        end
      }     
      attributes = Array.new
      attributesObjects.each { |att|
        attributes << att.getName
      }
     
      return attributes
    rescue
      puts "Error in displayDomainAttributes: " + $!
    end
  end
  
  def queryDataService(service, objectName, cred, modifier)
    begin
      serviceEndpoint = EndpointReferenceType.new(Types::URI.new(service.to_java_string))
      
      if(serviceEndpoint.getAddress.getScheme.eql?("http"))
        result = DataServiceActions.invokeNonSecureService(serviceEndpoint, objectName, modifier)
      else       
        result = DataServiceActions.invokeSecureService(serviceEndpoint, cred, objectName, modifier)
      end
    rescue
      puts "Error: " + $!    
    end
    return result
  end

Update Query Controller

Replace the code in app/controllers/queries_controller.rb with following:

"queries_controller"

class QueriesController < ApplicationController
    before_filter :logged_in
  # GET /queries
  # GET /queries.xml
  def index
    @queries = Array.new
    Query.all.each do |query|
      if query.user_id == current_user.id
        @queries << query
      end
    end
    @endpoints = Set.new
    @queries.each do |query|
      @endpoints << query.endpoint
    end
    
    respond_to do |format|
      format.html # index.html.erb
      format.xml  { render :xml => @queries }
    end
  end
  
  # GET /queries/1
  # GET /queries/1.xml
  def show
    @query = Query.find(params[:id])
    @attributes = @query.modifier.split(',')
    
    respond_to do |format|
      format.html # show.html.erb
      format.xml  { render :xml => @query }
    end
  end
  
  # GET /queries/new
  # GET /queries/new.xml
  def new
    @query = Query.new
    @discActions = DiscoveryActions.new
    $SERVICES ||= @discActions.getDataServices
    @services = $SERVICES
    respond_to do |format|
      format.html # new.html.erb
      format.xml  { render :xml => @query }
    end
  end
  
  # GET /queries/1/edit
  def edit
    client = GridClientClass.new
    @query = Query.find(params[:id])
    @service = Service.new
    @service.address = @query.endpoint
    @objects = client.displayDomainObjectNames(@service.address)
    @attributes = client.displayDomainObjectAttributes(@service.address, @query.object)
  end
  
  # POST /queries
  # POST /queries.xml
  def create
    
    if params[:service_button]
      puts "SERVICE BUTTON PRESSED"
      client = GridClientClass.new
      @query = Query.new(params[:query])
      @service = Service.new
      @service.address = params[:query][:endpoint]
      @objects = client.displayDomainObjectNames(@service.address)
      params[:endpoint] = @service.address
      respond_to do |format|
        format.html { render :action => "object" }
        format.xml  { render :xml => @query }
      end
      
    elsif params[:object_button]
      puts "OBJECT BUTTON PRESSED"
      client = GridClientClass.new
      @query = Query.new
      @service = Service.new
      @service.address = params[:endpoint]
      @query.object = params[:Object]
      puts "Service::address = "
      puts @service.address
      @attributes = client.displayDomainObjectAttributes(@service.address, @query.object)
      params[:obj] = params[:Object]
      respond_to do |format|       
        format.html { render :action => "parameters" }
        format.xml  { render :xml => @queries }
      end
      
    elsif params[:change_service_button]
      puts "CHANGE SERVICE BUTTON PRESSED"
      # Start a new query
      redirect_to(new_query_path)
      
    elsif params[:change_object_button]
      puts "CHANGE OBJECT BUTTON PRESSED"
      # Jump to object.html.erb to change object value
      client = GridClientClass.new
      @query = Query.new
      @service = Service.new
      @service.address = params[:endpoint]
      @objects = client.displayDomainObjectNames(@service.address)
      respond_to do |format|
        format.html { render :action => "object" }
        format.xml  { render :xml => @query }
      end     
      
    else
      puts "SAVING QUERY OBJECT"
      puts params[:obj]
      @query = Query.new(params[:query])
      @query.object = params[:obj]
      @query.endpoint = params[:endpoint]
      @query.user_id = params[:user_id]
      
      # Construct Modifiers
      String mod = params[:modifier]
      if mod.eql?("distinct_attribute")
        mod = mod + "," + params[:DA][0]    
        
      elsif mod.eql?("selected_attributes")
        params[:SA].each { |sa|
          mod = mod + "," + sa
        }
      end
      
      @query.modifier = mod
      
      respond_to do |format|
        if @query.save
          format.html { redirect_to(@query, :notice => 'Query was successfully created.') }
          format.xml  { render :xml => @query, :status => :created, :location => @query }
        else
          format.html { render :action => "new" }
          format.xml  { render :xml => @query.errors, :status => :unprocessable_entity }
        end
      end
    end
  end
  
  # PUT /queries/1
  # PUT /queries/1.xml
  def update
    if params[:change_button]
      redirect_to(query_service_path)
    else
      @query = Query.find(params[:id])
      
      # Construct Modifiers
      String mod = params[:modifier]
      if mod.eql?("distinct_attribute")
        mod = mod + "," + params[:DA][0]    
        
      elsif mod.eql?("selected_attributes")
        params[:SA].each { |sa|
          mod = mod + "," + sa
        }
      end
      mod_hash = {"modifier" => mod}
      
      respond_to do |format|
        if @query.update_attributes(params[:query].merge(mod_hash))
          format.html { redirect_to(@query, :notice => 'Query was successfully updated.') }
          format.xml  { head :ok }
        else
          format.html { render :action => "edit" }
          format.xml  { render :xml => @query.errors, :status => :unprocessable_entity }
        end
      end
    end
  end
  
  # DELETE /queries/1
  # DELETE /queries/1.xml
  def destroy
    @query = Query.find(params[:id])
    @query.destroy
    
    respond_to do |format|
      format.html { redirect_to(queries_url) }
      format.xml  { head :ok }
    end
  end
  
  # GET /queries/1/result
  def result
    puts "RESULT CALLED"
    @query = Query.find(params[:id])
    @service = Service.new
    @service.address = @query.endpoint
    @modifier = @query.modifier.split(',')
    client = GridClientClass.new
    @result = client.queryDataService(@service.address, @query.object, getGlobusCred, @modifier)
    @attributes = Array.new
    @rows = self.processXML(@result, @modifier)
    
    respond_to do |format|
      if @rows.drop(1).length > 0
        format.html 
        format.xml  { render :xml => @query }
      else
        flash[:notice] = "No Results Found"
        format.html
        format.xml {render :xml => @query }
      end
    end
  end
  
  def processXML(xmlArray, modifier)
    require "rexml/document"
    rows = Array.new
    if !xmlArray.nil?
    xmlArray = "<EOF>" + xmlArray + "</EOF>"
    end
    @xml = REXML::Document.new xmlArray
    
    # Process Object Query
    
    if modifier.at(0).eql?("object")
      client = GridClientClass.new
      attributes = client.displayDomainObjectAttributes(@service.address, @query.object)
      rows << attributes
      @xml.elements.each("*/*") { |element|
        values = Array.new
        attributes.each { |att|
          values << element.attributes[att]
        }
        rows << values
      }
    end
  
    # Process Distinct Attribute Query
    if modifier.at(0).eql?("distinct_attribute") || modifier.at(0).eql?("selected_attributes")
      attributes = modifier.drop(1)
      rows << attributes
      @xml.elements.each("*/*") { |parent| 
      values = Array.new
        parent.elements.each { |element| 
          attributes.each { |att|
            if element.attributes["name"].eql?(att)
              values << element.attributes["value"]
            end
          }
        }
          rows << values
      }
    end
  
    # Process Count Query
    if modifier.at(0).eql?("count")
      rows << modifier
      values = Array.new
      @xml.elements.each("*/*") { |element|
        values << element.attributes["count"]
      }
      rows << values
    end
    puts rows
    return rows
  end
  
end

Create Query View Files

Create three new query view files:
app/views/queries/object.html.erb
app/views/queries/parameters.html.erb
app/views/queries/result.html.erb

Now copy the corresponding code into each view file:

"object.html.erb"

<h2>Select Object from Service</h2>

<%= form_for(@query) do |f| %>

 <%= hidden_field_tag :endpoint, params[:endpoint] %>
 
 <div class="field">
    <%= f.label :Service %><br />
	<b><%=h @service.address %></b>
  </div>
  
  <%= submit_tag 'Change Service', :name => 'change_service_button' %>
  
  <br></br>
  
  <div class="field">
    <%= f.label :object %><br />
    <%= select_tag "Object", options_for_select(@objects, @objects[0]) %>
  </div>
  
  <%= submit_tag 'Select Object', :name => 'object_button' %>
  
  <% end %>

<%= link_to 'Back', queries_path %>

parameters.html.erb

<h2>Select Modifiers</h2>

<%= form_for(@query) do |f|  %>

 <div class="field">
    <%= f.label :Service %><br />
	<b><%=h @service.address %></b>
  </div>
  
  <%= submit_tag 'Change Service', :name => 'change_service_button' %>
  <br></br>
   <div class="field">
    <%= f.label :Object %><br />
	<b><%=h @query.object %></b>
  </div>
  
  <%= submit_tag 'Change Object', :name => 'change_object_button' %>
  
  <%= hidden_field_tag :endpoint, params[:endpoint] %>
  
  <% end %>
  
  </br>
  
<%= render 'form' %>

<%= link_to 'Back', queries_path %>

result.html.erb

<h1>Results</h1>

<h2>Service</h2>
<%= @service.address %>

<h2>Object</h2>
<%= @query.object %>

<h2>Modifiers</h2>
<% @modifier.each do |mod| %>
<%= mod %><br>
<% end %>

<h2>Description</h2>
<%= @query.description %>

<h2>Results</h2>
<div id="table.sample">
<table>
  <tr>
  	<% @rows.at(0).each do |att|%>
    <th><%= att %></th>
	<% end %>
  </tr>
  
  	<% @rows.drop(1).each do |row| %>
	<tr>
		<%row.each do |value| %>
			<td> <%= value %> </td>
		<% end %>
	</tr>
	<% end %>
	
</table>
</div>

<BR>
<%= link_to 'Back to Queries', queries_path %>

Update Existing Query View Files

Open the query index view and replace the code with the following:

"app/views/queries/index.html.erb"

<h1>Listing Queries by Service</h1>

<% @endpoints.each do |endpoint| %>
  <div id="highlight">
    <%= endpoint %>
</div>

<table>
  <tr>
    <th>Object</th>
    <th>Modifier</th>
    <th>Description</th>
    <th></th>
    <th></th>
    <th></th>
	<th></th>
  </tr>
  
	<% @queries.each do |query| %>
  <tr>
  	<% if query.endpoint == endpoint %>
    <td><%= query.object %></td>
    <td><%= query.modifier.split(',')[0] %></td>
    <td><%= query.description %></td>
    <td><%= link_to 'Show', query %></td>
    <td><%= link_to 'Edit', edit_query_path(query) %></td>
    <td><%= link_to 'Destroy', query, :confirm => 'Are you sure?', :method => :delete %></td>
	<td><%= link_to 'Run Query', result_query_path(query) %></td>
	<% end %>
  </tr>
	<% end %>
</table>
<br />

<%= link_to 'New Query', new_query_path %>

"app/views/queries/new.html.erb"

<h2>Select Service URL</h2>

<%= form_for(@query) do |f| %>

 <div class="field">
    <%= f.label :Service %><br />
    <%= f.collection_select :endpoint, @services, :address, :address %>
  </div>
  
  <%= submit_tag 'Select Service', :name => 'service_button' %>
  
  <% end %>

<%= link_to 'Back', queries_path %>

"app/views/queries/edit.html.erb"

<h1>Editing query</h1>

<%= form_for(@query) do |f|  %>

 <div class="field">
    <%= f.label :Service %><br />
	<b><%=h @service.address %></b>
  </div>
  
</br>
   <div class="field">
    <%= f.label :Object %><br />
	<b><%=h @query.object %></b>
  </div>

  
  <%= hidden_field_tag :endpoint, params[:endpoint] %>
  
    
  <% end %>
  
  </br>
  

<%= render 'form' %>

<%= link_to 'Show', @query %> |
<%= link_to 'Back', queries_path %>

"app/views/queries/show.html.erb"


<p>
  <b>Service:</b>
  <%= @query.endpoint %>
</p>

<p>
  <b>Object:</b>
  <%= @query.object %>
</p>

<p>
  <b>Modifier:</b>
  <% for att in @attributes %>
  <%= att %><BR>
  <% end %>
</p>

<p>
  <b>Description:</b>
  <%= @query.description %>
</p>

<p>
	<b>User:</b>
	<%= User.find_by_id(@query.user_id).username.capitalize %>
</p>


<%= link_to 'Edit', edit_query_path(@query) %> |
<%= link_to 'Back', queries_path %> | 
<%= link_to 'Run Query', result_query_path(@query) %>

"app/views/queries/_form.html.erb"

<%= form_for(@query) do |f| %>
  <% if @query.errors.any? %>
    <div id="error_explanation">
      <h2><%= pluralize(@query.errors.count, "error") %> prohibited this query from being saved:</h2>
      <ul>
      <% @query.errors.full_messages.each do |msg| %>
        <li><%= msg %></li>
      <% end %>
      </ul>
    </div>
  <% end %>
    
  <%= hidden_field_tag :endpoint, params[:endpoint] %>
  <%= hidden_field_tag :obj, @query.object %>
  
  <%= radio_button_tag :modifier, "object", true, :onclick => "['DA', 'SA'].each(Element.hide)" %> Object
  <%= radio_button_tag :modifier, "count", false, :onclick => "['DA', 'SA'].each(Element.hide)" %> Count
  <%= radio_button_tag :modifier, "distinct_attribute", false, :onchange => "Element.show('DA'); Element.hide('SA');" %> Distict Attribute
  <%= radio_button_tag :modifier, "selected_attributes", false, :onclick => "Element.show('SA'); Element.hide('DA');" %> Selected Attributes
  
  <div id="DA" style="display:none;">
      <BR>
      <% for att in @attributes %>
      <%= radio_button_tag "DA[]", att %>
      <%= att %>
      <BR>
      <% end %>
  </div>
  <div id="SA" style="display:none;">
      <BR>
      <% for att in @attributes %>
      <%= check_box_tag "SA[]", att %>
      <%= att %>
      <BR>
      <% end %>
  </div>

  
  <br></br>
  <div class="field">
    <%= f.label :description %><br />
    <%= f.text_field :description %>
  </div>
  	<%= hidden_field_tag :user_id, current_user.id %>
  <div class="actions">
    <%= f.submit %>
  </div>
<% end %>

Update Routes

In config/routes.rb, add the member loop to the query resources line:

  resources :queries do
    member do
      get 'result'
    end
  end

This adds "result" to the other RESTful actions available to query. Now, query result pages will match other action URLs (ex. queries/3/results displays the results from query_id = 3)

Add the other query routes to routes:

  get "query" => "queries#index", :as => "query/index"
  get "queries/new" => "queries#new", :as => "queries/new"
  get "queries/object" => "queries#create", :as => "query/object"
  get "queries/params" => "queries#new", :as => "query/params"

Add caGrid Jars

Copy the following [^Query_Jars.zip] into the "lib/jars/caGrid/" directory:
caGrid-CQL-cql.1.0-1.4.jar
caGrid-CQL-cql.2.0-1.4.jar
caGrid-CQL-utils-1.4.jar
caGrid-data-common-1.4.jar
caGrid-data-stubs-1.4.jar
caGrid-data-utils-1.4.jar

Add Query Tab

Add this line to "app/views/layouts/application.html.erb" just below the link to services to add a link to the "queries/index" page:

<li><%=link_to "Queries", queries_path %></li>

Saving And Running Queries

Once logged in, you can navigate to http://localhost:3000/queries or click the Query tab to start adding queries. Select "New Query" to begin constructing a new query:

From the drop down menu, select the service you wish to query and press "Select Service":

After pressing the "Select Service" button, a drop down menu of available objects will be displayed:

Once an object is selected, radio buttons will appear along with a text box to add a description. The radio buttons correspond to modifiers available through CQL Queries of caGrid Services. If either "Distinct" or "Selected" attributes is chosen, then new options should appear giving the user the ability to select which attribute values to return.

Pressing either "Change Service" or "Change Object" will allow the user to go back and change those values before saving. However, changing these values will change later options of the form and therefore object and modifier information is not retained.

Press the "Create Query" button to save the query with the selected modifiers and description. A message with flash saying the query has been successfully created and you will be redirected to a summary page for that query.

Clicking the "Run Query" link will execute the query and display the results in a table under a summary of the query.

Clicking the "Back To Queries" link will bring you back the query index page. This page will list all queries sorted by Service URL that have been saved by the user. Queries can be edited by clicking the "Edit" link or deleted by clicking the "Destroy" link.

Knowledgebase