BuildAPI: 0.2

Quick note:

Last week I posted a series of blog posts that I did not have time to post during the past week or more. They were all sort of backed up and hence all posted at once.

BuildAPI 0.2

On to the BuildAPI project: Let me just say that I have been doing this project in a step-by-step approach in order to learn what I need to learn, understand what I am working with, and apply it practically.
Also, the BuildAPI wiki page has been updated: http://zenit.senecac.on.ca/wiki/index.php/BuildAPI

To see it running: http://iraq.proximity.on.ca:5000/
Please click on Project 0.2 under BuildAPI Project Test, or http://iraq.proximity.on.ca:5000/project

0.1x - Between 0.1 and 0.2

My 0.1 release included getting BuildAPI set up and taking a peek at the extensively used MVC model.
However, since BuildAPI uses Pylons framework, I took some time to read The Definitive Guide to Pylons book and skipped to the chapters that were necessary. It's a good thing the book is available online, it is such a great resource! It helped me to learn some of the language and concepts. Next was to create very simple controllers for working with model and view templates and that was the beginning of wrapping my head around the MVC model concepts.

0.2 - Summary

For 0.2, I feel very comfortable working with the MVC model and was able to create my own controller, add to the existing BuildAPI universal model script for querying (query.py, orginal seen here), and create my own MAKO template to output my results. (See MVC scripts). In addition, I appended a link to my project to the main BuildAPI index page, which I have set up on http://iraq.proximity.on.ca:5000.

In regards to the model file, I had to read some of the documentation on SQLAlchemy and how queries work in order to pull data from the database, and from that, I now understand how to formulate basic queries using SQLAlchemy. However, I am still having a bit of trouble with complex queries such as using joins and multiple where clauses (See blog post on SQLAlchemy and Basic Queries).

I also stripped special formatting from the output and template as I did not fully understand how it works and how it is integrated with Pylons and BuildAPI - it seemed confusing. By special formatting, I am referring to Javascript/JSON and Json tables and charts.
However, after a bit of fiddling and reading on google visualizations documentation, I get the gist of how it works and I have implemented simple json using google viz api. Although, I still need to seek out help and apply it practically - which will be in 0.3.

MVC – Revisited

From my 0.1 post, I'd like to revisit the MVC model in terms of the BuildAPI project instead of a generic breakdown. I will also reference the MVC scripts which can be seen later in the post.
Here goes

• The model (query.py) knows about the databases and queries, or in other terms, business objects/data
• The template (project.mako) knows about HTML, Javascript, CSS, and the tmpl_context or "c" variables (example: c_output in controller projecy.py script) that are passed to it by the controller.
• The controller knows about both the model and the template. Therefore, it will query or modify the model to obtain data, modifies the values of the "c" variables and then passes it to the template.
• Also, the model can invoke other models, and the controller other controllers etc

After working with it, I can definitely see how each component can be updated separately without affecting the other.

MVC Scripts

The Controller - project.py

import logging

from pylons import request, response, session, tmpl_context as c, url

from pylons.controllers.util import abort, redirect

from pylons.decorators import jsonify

from buildapi.lib.base import BaseController, render

from buildapi.model.query import GetProjectQuery

from buildapi.model.query import GetQueryTest

log = logging.getLogger(__name__)

class ProjectController(BaseController):

    def index(self):

        output = GetProjectQuery()

        results = GetQueryTest()

        c.output = output

        c.results = results

        return render("/project.mako")

        return self.jsonify(output)

        # What the lines below do is to gather the format of the request using request.GET method

        #  which contains the variables in a query string

        # These variables are set in the template through javascript or through the form request

        # This is commented out for reasons (See JSON/Javascript section)

        #if 'format' in request.GET:

        #    format = request.GET.getone('format')

        #else:

        #    format = 'html'

        #if format not in ('html', 'json'):

        #    abort(400, detail='Unsupported format: %s' % format)

        #if format == "html":

            # assign to c_output

            # return template render

        #else:

           # jsonify the data                   

           # return self.jsonify(results)

The Model - query.py (My test queries appended to existing file)

def GetProjectQuery():

    rr = meta.status_db_meta.tables['builders']

    q = select([rr.c.id, rr.c.name])

    q = q.limit(50)

    query_results = q.execute()

    output = []

    for r in query_results:

        this_result = {}

        for key, value in r.items():

            this_result[str(key)] = value

        output.append(this_result)

    return output

def GetQueryTest():

    rr = meta.status_db_meta.tables['builders']

    bb = meta.status_db_meta.tables['builds']

    q = select([rr.c.name, bb.c.starttime, bb.c.endtime])

    #q = q.join(rr, bb.c.builder_id = rr.c.id)

    q = q.where(and_(rr.c.id == bb.c.builder_id))

    q = q.where(and_(rr.c.name.like('%moz%')))

    q = q.limit(50)

    query_results = q.execute()

    results = []

    for r in query_results:

        this_result = {}

        for key, value in r.items():

            this_result[str(key)] = value

        results.append(this_result)

    return results

The Template - project.mako

Only the important parts shown. This creates the table using the results from the query that are passed in as variables.

This is for the first table:

<tr>

% for key in ('Builder ID','Builder Name'):

<td><h3>${key}</h3></td>

% endfor

</tr>

<tbody>

<%

  from pytz import timezone

  from datetime import datetime

  eastern = timezone('US/Eastern')

  now = datetime.now().replace(microsecond=0)

%>

% for key in c.output:

        <tr>

        % for x in ('id', 'name'):

        <td>${key[x]}</td>

        % endfor

</tr>

% endfor

</tbody>

This is for the second table:

% for key in ('Builder Name','Start Time', 'End Time', 'Duration'):

<td><h3>${key}</h3></td>

% endfor

</tr>

% for key in c.results:

        <%

        key['duration'] = key['endtime'] - key['starttime']

        %>

        <tr>

        % for x in ('name', 'starttime', 'endtime', 'duration'):

        <td> ${key[x]} </td>

        % endfor

        </tr>

% endfor

The below is simply using Google's visualization to create the table, it can also be used to create charts. I just don't know how to loop the data into a chart/table as yet properly.

<script type="text/javascript" src="http://www.google.com/jsapi"></script>

<script type="text/javascript">

        google.load('visualization', '1', {packages: ['table']});

</script>

% for key in c.output:

        rows: [{c:[{v: '${key['id']}'}, {v: '${key['name']}'}]}]};

% endfor

<script type="text/javascript">

    function drawVisualization() {

      // Create and populate the data table.

    var JSONObject = {

      cols: [{id: 'task', label: 'Builder ID', type: 'string'},

              {id: 'hours', label: 'Builder Name', type: 'string'}],

      rows: [{c:[{v: '${key['id']}'}, {v: '${key['name']}'}]}]};

      var data = new google.visualization.DataTable(JSONObject, 0.5);

      // Create and draw the visualization.

      visualization = new google.visualization.Table(document.getElementById('table'));

      visualization.draw(data, {'allowHtml': true});

    }

    google.setOnLoadCallback(drawVisualization);

</script>

After the above script, this is all that's needed in the HTML file that displays the table:

<div id="table"></div>

Javascript, JSON and Outputting data in special formats

That being said, JSON is just another data format structure, which is used for formatting the tables and charts. However, I am trying to get JSON and javascript to work so I can integrate with existing BuildAPI structure, but honestly, it is quite a lot to learn in a short time.

Whatever it is that I am doing or learning, I like to make sure I understand why I am doing it and how it works.

I've researched that the controller can produce the json on request using several methods, one of which is by passing the format specifier. In addition, this can be done either by passing it from a template using javascript to the controller or by using what is called "RESTful services". In Python documentation for Routes and RESTful services, it shows "Several routes are paired with an identical route containing the format variable. The intention is to allow users to obtain different formats by means of filename suffixes; e.g., "/messages/1.xml".

This ultimately means that you could put a test in the controller to see if it JSON or HTML data:

def view(self, id, format='html'):

     if format == 'json':

         return self.jsonify(data)

     else:

         return ('templates/project.mako')

Then, the output will depend on how the data is called, for example:
http://examplepath/controller/page.html - the format will be html thus displaying an HTML page
or
http://examplepath/controller/page.json - using json format and application/json content type

However, BuildAPI takes the approach of using javascript to set the format and furthermore, this javascript is inserted into another template file (call it B) that is invoked by the main template (call it A), so A invokes values from B. Maybe I am just confusing things and I have it all wrong? PLEASE correct me if I am wrong! (I've sent some emails out to request for help on the matter)

In BuildAPI, I understand that the controllers will check the 'format' of the request by using Pylon's "request.GET" and I understand that for the "Reports" this format (such as charts) is set in the respective mako template files. If it is an html request, it shows the HTML page, if it is a json request then it shows the json data in the form of a table or chart in the html page. However, some controllers (namely recent.py, running.py and pending.py) - where is the format set? I don't see it set anywhere in the controller, model or template file and it is not inheriting it from anywhere, yet the format seems to be set and it uses html + json to create those nice looking tables! How?

Moreover, I also received errors when trying to 'jsonify' output with more than one key/value pairs such as a pylons list that contains:
 {'name': 'mozilla-1.9.1-win32-unittest', 'starttime': '2009-09-27 14:06:30',  'endtime': '2009-09-27 14:27:50'}
It gives me the error:

Error - <type 'exceptions.TypeError'>: datetime.datetime(2009, 9, 27, 14, 27, 50) is not JSON serializable

Aaahh, the frustration!

Although, I've thought about a simpler solution:

1. Dump the data from the query into a python list
2. Then dump that data into json objects and pass it as a c_context variables to the MAKO template
3. n the template, iterate through the results to display it using google visualizations to create table or charts

However, I've read that this may not be as secure as it perhaps uses the javascript eval() function – but I'm not sure.

I will update this in the near future with a clear path to 0.3.

Update: I recently exchanged emails with Armen Zambrano regarding the objectives and will post another blog in the coming days of what I've decided to do.