Share your knowledge: January 2013

Tuesday, January 22, 2013

XQuery demo using Zorba for Stanford DBClass

I decided to build a more appealing demo showing the potential of XQuery for building webpages. In this demo I render a table showing countries with a population greater than 100 million and which have languages listed. The languages are listed as pie charts using the Google Chart API.

XQuery used on countries XML data.

import module namespace http = "http://expath.org/ns/http-client";

declare %an:sequential function local:doc($href as xs:string) {
  http:send-request(<http:request href="{$href}" method="GET" />)[2] 
};


let $doc := local:doc("http://prod-c2g.s3.amazonaws.com/db/Winter2013/files/countries.xml")
let $bigCountries := $doc/countries/country[@population > 100000000 and exists(language)]

return
<html>
  <head>
    <title>Visualization of languages for countries with population greater than 100 million</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> </meta>
    <style type="text/css">
      table {{
        border-collapse: collapse;
      }}
      caption {{
        background-color: #ADAEAF;
        text-align: left;
      }}
      .header {{
        background-color: #E6F1F3;
      }}
      td, th {{
        border: 1px dotted #B3D4DB;
        padding: 2px;
        vertical-align: top;
        text-align: left;
      }}
    </style>
    <script type="text/javascript" src="https://www.google.com/jsapi"> </script>
    <script type="text/javascript">

      // Load the Visualization API and the piechart package.
      google.load('visualization', '1.0', {{'packages':['corechart']}});
  
      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawCharts);

      function drawChart(percentages, countryName) {{
        // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Country');
        data.addColumn('number', 'percentage');
        data.addRows(percentages);

        // Set chart options
        var options = {{'title':'Languages',
                       'width':400,
                       'height':300}};

        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.PieChart(document.getElementById(countryName));
        chart.draw(data, options);
      }}
   
      function drawCharts() {{
        {
           for $country in $bigCountries
           let $data := string-join(for $lang in $country/language return concat('["', data($lang), '",', data($lang/@percentage), ']'), ',')
           return concat('drawChart([', $data, '],  "', data($country/@name),'");
')
        }
      }}  
    </script> 
  </head>
  <body>
    <table>
      <caption>Countries with population greater than 100 million</caption>
      <thead>
        <tr class="header">
          <th>Name</th>
          <th>Population</th>
          <th>Area</th>
          <th>Languages</th>
        </tr>
      </thead>
      <tbody>
        {
           for $country in $bigCountries
           order by count($country/language) descending
           return
             <tr>
               <td>{data($country/@name)}</td>
               <td>{data($country/@population)}</td>
               <td>{data($country/@area)}</td>
               <td><div id="{data($country/@name)}"></div></td>
             </tr>
        }
      </tbody>
    </table>
  </body>
</html>

Result from executing XQuery on Zorba

<html>
  <head>
    <title>Visualization of languages for countries with population greater than 100 million</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <style type="text/css">
      table {
        border-collapse: collapse;
      }
      caption {
        background-color: #ADAEAF;
        text-align: left;
      }
      .header {
        background-color: #E6F1F3;
      }
      td, th {
        border: 1px dotted #B3D4DB;
        padding: 2px;
        vertical-align: top;
        text-align: left;
      }
    </style>
    <script type="text/javascript" src="https://www.google.com/jsapi"> </script>
    <script type="text/javascript">

      // Load the Visualization API and the piechart package.
      google.load('visualization', '1.0', {'packages':['corechart']});
  
      // Set a callback to run when the Google Visualization API is loaded.
      google.setOnLoadCallback(drawCharts);

      function drawChart(percentages, countryName) {
        // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Country');
        data.addColumn('number', 'percentage');
        data.addRows(percentages);

        // Set chart options
        var options = {'title':'Languages',
                       'width':400,
                       'height':300};

        // Instantiate and draw our chart, passing in some options.
        var chart = new google.visualization.PieChart(document.getElementById(countryName));
        chart.draw(data, options);
      }
   
      function drawCharts() {
        drawChart([["Hindi",30]],  "India");
        drawChart([["Japanese",100]],  "Japan");
        drawChart([["Pashtu",8],["Urdu",8],["Punjabi",48],["Sindhi",12],["Balochi",3],["Hindko",2],["Brahui",1],["Siraiki",10]],  "Pakistan");
        drawChart([["Russian",100]],  "Russia");
      }  
    </script>
  </head>
  <body>
    <table>
      <caption>Countries with population greater than 100 million</caption>
      <thead>
        <tr class="header">
          <th>Name</th>
          <th>Population</th>
          <th>Area</th>
          <th>Languages</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>Pakistan</td>
          <td>129275664</td>
          <td>803940</td>
          <td>
            <div id="Pakistan"/>
          </td>
        </tr>
        <tr>
          <td>India</td>
          <td>952107712</td>
          <td>3287590</td>
          <td>
            <div id="India"/>
          </td>
        </tr>
        <tr>
          <td>Japan</td>
          <td>125449704</td>
          <td>377835</td>
          <td>
            <div id="Japan"/>
          </td>
        </tr>
        <tr>
          <td>Russia</td>
          <td>148178480</td>
          <td>17075200</td>
          <td>
            <div id="Russia"/>
          </td>
        </tr>
      </tbody>
    </table>
  </body>
</html>

So what does this page look like in a browser?

Friday, January 18, 2013

Generating HTML and Excel with Xquery

I just wanted to share a small sample how you can generate a table in both HTML and Excel with one XQuery. Just in case the sample gets lost I will still share the code in this article as well.
A few remarks. You can save the result to a file test.html and test.xls. For the excel file you will need to remove the xml declaration (first line).

let $orders :=
<orders>
  <order id="1231">
    <product>Samsung Galaxy S3</product>
    <price valuta="EURO">467</price>
    <quantity>3</quantity>
  </order>
  <order id="1232">
    <product>iPhone 5</product>
    <price valuta="EURO">689</price>
    <quantity>5</quantity>
  </order>
  <order id="1233">
    <product>LG E610 Optimus L5</product>
    <price valuta="EURO">140</price>
    <quantity>2</quantity>
  </order>   
</orders>

return
<html xmlns:x="urn:schemas-microsoft-com:office:excel">
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> </meta>
    <meta http-equiv="Content-Type" content="application/vnd.ms-excel; charset=utf-8"> </meta>
    <style type="text/css">
      table {{
        border-collapse: collapse;
      }}
      .header {{
        background-color: #E6F1F3;
        text-align: left;
      }}
      td, th {{
        border: 1px dotted #B3D4DB;
        padding: 2px;
      }}
      .number {{
        text-align: right;
      }}
      .subtotal {{
        background-color: #E3E4E5; 
        text-align: right;
        font-weight: bold;
      }}
      .total {{
        background-color: #ADAEAF;
        text-align: right;
        font-weight: bold;        
      }}
    </style>
  </head>
  <body>
    <table>
      <colgroup>
        <col style="width: 100px;"/>
        <col style="width: 200px;"/>
        <col style="width: 100px;"/>
        <col style="width: 100px;"/>
      </colgroup>          
      <thead>
        <tr>
          <th class="header">Order No</th>
          <th class="header">Product</th>
          <th class="header">Price</th>
          <th class="header">Quantity</th>
        </tr>
      </thead>
      <tbody>
      {
        for $order in $orders/order
        let $price := data($order/price)
        let $quantity := data($order/quantity)
        let $subtotal := $price * $quantity
        return
        (
        <tr>
          <td>{data($order/@id)}</td>
          <td>{data($order/product)}</td>
          <td class="number">{$price}</td>
          <td class="number">{$quantity}</td>
        </tr>,
        <tr>
          <td class="subtotal"/>
          <td class="subtotal"/>
          <td class="subtotal">Subtotal</td>
          <td class="subtotal">{$subtotal}</td>
        </tr>
        )
      }
        <tr>
          <td class="total"/>
          <td class="total"/>
          <td class="total">Total</td>
          <td class="total">{sum(for $order in $orders/order return data($order/price) * data($order/quantity))}</td>
        </tr>
      </tbody>
    </table>
  </body>
</html>

You will get a warning from Excel but I already looked into this and there is no workaround available at this moment. I also noticed that when opening such a file from Internet Explorer it seems to hang the first time. A workaround it to Save the file the first time and open it. From that moment on Internet Explorer will be able to open the files correctly.

Friday, January 11, 2013

Rendering tweets with Play2.1

Tweet Controller

package controllers

import play.api.mvc.{Action, Controller}
import play.api.libs.functional.syntax._

import play.api.libs.json._
import play.api.libs.concurrent.Execution.Implicits.defaultContext
import play.api.libs.ws.WS

case class Tweet(from: String, text: String)

object Tweets extends Controller {

  implicit val tweetReads = (
    (__ \ "from_user_name").read[String] and
    (__ \ "text").read[String]
    )(Tweet)


  def tweetList(query: String) = Action {
    Async {
      val results = 10
      val responsePromise =
        WS.url("http://search.twitter.com/search.json")
          .withQueryString("q" -> query, "rpp" -> results.toString).get
      responsePromise.map {
        response =>
          val tweets = Json.parse(response.body).\("results").as[Seq[Tweet]]
          Ok(views.html.tweetlist(tweets))
      }
    }
  }
}

routes

GET     /tweets/:query              controllers.Tweets.tweetList(query: String)

scala template:

@(tweets: Seq[Tweet])
@main("Tweets!") {
    Tweets:
    @tweets.map { tweet =>
      
        @tweet.from: @tweet.text
      
    }
}

So invoking following URL http://localhost:9000/tweets/playframework now gives me the last 10 tweets containing playframework (case insensitive). I think it's pretty slick how little code is needed.