h1. Querying Data Services
----
{cagridtoc:exclude=Querying Data Services}
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.

h2. Generate the Query Model
----
In the terminal, use the Rails scaffold command to create the query model, controller and views:
{terminal}
GridClient %> rails generate scaffold query object:string endpoint:string modifier:string description:string user_id:integer
{terminal}

Now migrate the table into the current database using the rake command:
{terminal}
GridClient %> rake db:migrate
{terminal}

h2. 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*"
{code}
helper_method :getGlobusCred

# Global Variable holding availabe services
$SERVICES

def getGlobusCred
return session[:globus]
end
{code}

h2. Add Data Service Actions
----
Create a new Ruby file *lib/DataServiceActions.rb* and copy in the following code:
{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
{code}

{note}
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).
{note}

h2. 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"*
{code}
include_class "org.apache.axis.message.addressing.EndpointReferenceType"

module Types
include_package "org.apache.axis.types"
end
{code}

{code}
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
{code}

h2. Update Query Controller
----
Replace the code in *app/controllers/queries_controller.rb* with following:

*"queries_controller"*
{code}
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
{code}

h2. 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"*
{code}
<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 %>
{code}

*parameters.html.erb*
{code}
<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 %>
{code}

*result.html.erb*
{code}
<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 %>
{code}

h2. Update Existing Query View Files
----

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

*"app/views/queries/index.html.erb"*
{code}
<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 %>
{code}

*"app/views/queries/new.html.erb"*
{code}
<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 %>
{code}

*"app/views/queries/edit.html.erb"*
{code}
<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 %>
{code}

*"app/views/queries/show.html.erb"*
{code}

<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) %>
{code}

*"app/views/queries/_form.html.erb"*
{code}
<%= 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 %>
{code}

h2. Update Routes
----
In *config/routes.rb*, add the member loop to the query resources line:
{code}
resources :queries do
member do
get 'result'
end
end
{code}
{note}
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)
{note}

Add the other query routes to *routes*:
{code}
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"
{code}

caGrid-CQL-utils-1.4.jar
caGrid-data-common-1.4.jar
caGrid-data-stubs-1.4.jar
caGrid-data-utils-1.4.jar

h2. 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:
{code}
<li><%=link_to "Queries", queries_path %></li>
{code}

h2. 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:
!new_query.png!

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

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

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.
!select_modifier.png!

{note}
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.
{note}

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.
!saved_query.png!

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

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.
!query_index.png!




{scrollbar}