User side testing and Twill

History

RdbHost has two faces: one that the human user sees, and one that their software sees. The software side, the 'web service' has always had good testing. That it was designed to be computer parse-able without screen-scraping meant it was easy to write automated tests for it. There are in fact two sets of web service tests, one that uses urllib2 to query directly, and another that tests the DB API module Rdbhdb by using it to query the server.

Website Testing

Until recently, the human-readable (aka the web site) side of the business was not tested in any automated way. I would test features I thought might be affected by a change, manually, after an update. I am not sharing any details, but that strategy has proven to be deficient. Now we have a test suite for the user side as well.

Twill

The software used for the new tests is Twill. Twill can be run as its own scriptable shell, or included as a python module and the functions called from python. The Twill python module has an instantiated object syntax, where you create a browser object and call methods on it. Unfortunately, the browser object does not support the 'show' method. I ended up giving up on the explicit browser object, and just used the implicit browser. It works well, but doesn't satisfy my 'explicit code is more comfortable' preference.

Twill

Tests and Email

Many features of the website use email for various authentication functions. To reset your password, the site emails a password reset-link. To change your email address of record, you have the site email a change link to the new address. Creating a new account involves emailing the initial login password to the submitted address. How does one test such a system with Python and Twill?

My solution was to use one of the spam-dodging sites that allow one to receive email anonymously. Just email to 'anyname@spamspot.com' and spamspot.com will post the email content to a blog style web page, intermingled chronologically with other user's emails. The test code can request an email, for whatever functionality being tested, give the site a spamspot.com email address to mail to, wait a few seconds for the email to arrive and be posted, and then use Python and Twill to request the spamspot page and parse out the rdbhost email and its embedded password or links using 're' module. It is appropriate (if also a little paranoid) to change the password shortly thereafter to minimize tampering by other readers of spamspot.

There are many anonymous email receiver websites. Spamspot.com (mentioned above) and Makemeaking.com are more useful for our purposes than some, because they include the email inline to the page, without requiring another link-following step, and the prerequisite link parsing step.

Spamspot.com

Makemetheking.com

Edited 24 Oct 09: added reference to Makemetheking.com

Edited 26 Oct 09: minor grammatical corrections

Multiple Recordsets

It can be very useful to put multiple queries into one web-service request. You get multiple query responses, and only suffer the request overhead for one request.

Rdbhost now supports returning multiple record sets for one request.

If you have multiple Postgresql statements in your request 'q' param, delimited by ';', each is processed in sequence, and the results for each are added to the list of record sets. Instead of one group in the results called 'records', you have an outer group called 'result_sets', and a group within that for each statement. The details differ between the json, json-easy, xml, and xml-easy formats, but the above description applies to each.

json format for multiple result-sets looks like:

{    
    "status": [
        "complete", 
        "OK"
    ],
    "result_sets": [
        {
            "status": [
                "complete", 
                "OK"
            ],
            "records": {
                "header": {
                    "id": 23
                }, 
                "rows": [
                    {
                        "id": 1
                    }
                ]
            }, 
            "row_count": [
                1, 
                "1 Rows Affected"
            ] 
        }, 
        {
            ...
        }
    ]
}

for comparison, the single recordset variant looks like:

{
    "status": [
        "complete", 
        "OK"
    ],
    "records": {
        "header": {
            "id": 23
        }, 
        "rows": [
            {
                "id": 1
            }
        ]
    }, 
    "row_count": [
        1, 
        "1 Rows Affected"
    ]
}

The single result-set variant puts the count, status, and records all in the root.

The multiple result-set variant puts creates a result-set group, containing count, status, and records, for each statement in the request. There is also one status element in the root, and if the status is 'error', there will be no result sets.

The xml formats have similar layout:

<xml xmlns="http://www.rdbhost.com/xml.html">
  <status value="complete">OK</status>
  <result_sets>
    <result_set>
      <row_count value="1">1 Rows Affected</row_count>
      <status value="complete"/>
      <records>
        <header>
          <fld type="23">id</fld>
        </header>
        <rec>
          <fld>1</fld>
        </rec>
      </records>
    </result_set>
    <result_set>
    ...
    </result_set>
  </result_sets>
</xml>

The xml formats have a 'result_sets' container element, and multiple 'result_set' container elements within that.

The service is free; you can learn more about it by registering for an account, reading the online documentation, and experimenting.

http://www.rdbhost.com

Stackoverflow Data

The stackoverflow database account here has been updated to include the September data dump. That includes cumulative data up to 31 August.

A fairly complete set of indexes was added to the basic tables. Long text fields, like message bodies, do not benefit much from ordinary indexes, because you rarely search on the whole content, and the key content is not necessarily at the beginning of the field, where an index would accelerate access. So the short fields are indexed, large text fields are not.

Now, full text indexing might be useful, but the typical use case is to find posts on a given topic, and just searching the stackoverflow site using its on-site search, or Googling, would be more generally useful. Full text searches in Postgresql involve, optimally, a non-standard functions that normalize the search terms; it gets better results than a straight keyword search, but involves a learning curve.

Maybe queries like 'how many posts about python have scores over 100?' would be useful, but that can be approximated by querying on tags joined to posts via tagging.

I hope the database is useful. Let me know if you have any comments or complaints.

The stackoverflow database is at:

http://www.rdbhost.com/rdbadmin/main.html?r0000000767

Just login with the default login and no password.

David

UserTesting

I gave a try to the usability testing website, UserTesting.com.

I paid around $70 for three tests. The first test occurred within 24 hours, and the others 3 days later. Each lasted 10 - 25 minutes. UserTesting sets up their testers with screencast type software, and with the software is recording their session, they are talking about their thought process doing the task assigned.

Rdbhost.com is a site for programmers, to support programming efforts. I did not think I could create a reasonable 15 minute task that involved creating a program, so I described a simple database operations task; create an account, create a table, put some data in it. I required the testers already have some SQL programming knowledge. Despite the task being tangential to our core purpose, the tests were informative, if also humbling.

Some observations of the testers:

All accomplished the basic task, despite some inefficiencies. Each fell back on typing SQL into the sql box for data entry, despite the existence of a form to do that function. The SQL box is there as a do-anything catchall, of course, and it did serve that purpose.

One tester 'cheated' by visiting the site and looking at internal pages before starting the recording session. (link color betrayed him). All offered subjective commentary beyond narrating their problem solving process. The subjective criticism bothered me a bit, until I decided to just filter it out and focus on the observations.

Observations by the testers:

Rdbadmin javascript 'button' links did not look like buttons. Testers worked all around the 'new item' link, because it looked like a subtitle, rather than a link. I admit, my own tinkering with the page format a few days prior had busted the link display, and I had not noticed.

The profile page was not intuitive, and while all three found their way to the Rdbadmin, only one did so quickly. Only one of the three actually read help pages, despite their prominent link placement.

The general look of the site got some knocking, getting called a 'spoof site', 'dated', and 'powerpoint slide'.

Outcome

I have fixed the obvious problems, making the admin links look, again, like links, and adding description to the profile page links. I have some notes for a more pervasive redesign, in time, but the quick fixes improved the usability quite a bit.

UserTesting.com gets a recommendation from me. The whole system is slick, with very useful screencasts delivered for each tester, as well as summary analysis in prose.

JsonP

Rdbhost.com now provides JSONP as a format option.

JSONP allows data to be requested from a site different from that of the referencing page. You can put a page on your domain somewhere, and retrieve data from www.rdbhost.com using a cross-site request and the JSONP format.

Let us look at a very simple example. Rdbhost maintains a table of performance statistics (on itself, about itself) in a table 'stats' in account db000000005 . We will query that table from our page for a count of records. The entire example page is 11 lines:

01 <html>
02 <body>
03    Count of records in 'stats':   <span id="count"></span>
04    <script>
05   function showct(data){
06      document.getElementById("count").innerHTML = data['records']['rows'][0]["count"];
07   };
08   </script>
09   <script src="http://www.rdbhost.com/db/r0000000005?q=SELECT%20count(*)%20from%20stats&callback=showct"></script>
10 </body>
11 </html>

Lines 01 to 03 are just html to show the count when we get it. Lines 05 to 07 are a javascript function to process the count data after receiving it, and line 09 is the actual request.

Line 09 is a script tag: it retrieves the page at the url identified in 'src', and processes it as a script. The 'callback' parameter indicates the name to 'wrap' around the data, and implies the request needs JSONP output format. The content received is this:

showct({
  "records": {
    "header": {
               "count": 20
              },
    "rows": [
             {
               "count": "5456"
             }
            ]
  },
  "row_count": [
                1,
                "1 Rows Affected"
               ],
  "status": [
              "complete",
              "OK"
            ]
})

When this is processed as a script, the 'showct(...)' is interpreted as a function call, and executed. The function had been defined in lines 05 to 07, and just extracted the 'count' element of the JSON and put it in the HTML element 'count', for display.

This page works cross-site, so you could host such a page on your site, under your domain, and retrieve records from rdbhost.com dynamically. If you prefer jQuery, you can retrieve JSONP content using the .getJSON function, including a 'callback' parameter in the url to retrieve. Using jQuery has the virtue that you do not need a named callback function, as jQuery will allow its typical anonymous callbacks even with JSONP.

Limitations:

• Requests use 'GET' mode, not 'POST', so any authentication information would be there in the link for users to read.
• Rdbhost does not permit authcodes to be passed in GET requests, for security reasons. JSONP usage is thus only useful for queries using the 'r' role, with no authentication. To query your own account, you would need to enable the 'r' role from the profile page, and probably GRANT it some SELECT privileges on tables in the account, using rdbadmin or the SQL_form.

Example pages can be found at the following links. Use view source to see the internals.

Script tag approach.

jQuery approach.

The request urls can easily get long enough to be unwieldy. These two pages demonstrate a technique for putting the url in a javascript variable, where it can be line-wrapped.

The Rdbhost website itself is at: http://www.rdbhost.com.

Stackoverflow Data

Stackoverflow.com, in case you haven't heard of it, is a very popular question and answer site for programmers.

It was developed by Jeff Atwood and Joel Spoelski, both well known bloggers on technology, and they produce an entertaining podcast on the development of stackoverflow.com and (more or less) related computer topics.

Anyway, to get to the point, the data at stackoverflow is all user generated, and licensed under Creative Commons. They release, about once a month, an xml dump of all the data, in a big archived file. The release includes all posts and all comments, and some user profile data.

With some pro-active assistance from Stéphane Bortzmeyer, I have imported the data from the August dump into an Rdbhost database and made it available to anonymous users, with SELECT privilege only (no changes to data permitted). The database engine behind the Rdbhost webservice is Postgresql.

The database is at: www.rdbhost.com/rdbadmin/main.html?r0000000767
Just click the login button; no authentication is required. The SQL admin software is a work-alike to Adminer (formerly phpMinAdmin), implemented in javascript.

Hopefully, the table and field names make sense and their meanings can be inferred. If you find the indexes to be insufficient for your purposes, email me or put a comment on this blog entry. There is a 3 second query duration limit, so any query needing a full-table scan on any of the larger tables will likely fail.

Have fun.

Edited: Added mention of Postgresql and made a few grammar/punctuation corrections.

Binary Content

Formats

The usual approach to using rdbhost is to request records, consisting of multiple fields, and that record data is incorporated into a structured JSON or XML page and delivered to requesting client. The client can then disassemble the structure to extract the data for use. Those JSON or XML formatted pages are text, and non-text data gets encoded/escaped to make it fit a textual structure.

Sometimes, though, you just want the raw data, without textual escaping. This would be useful for an image database, for example. You would like to just send the binary image data to the browser or other client, and have it processed without any text-binary decoding.


Binary Format

The 'binary' format does just this. If your request calls for a binary format, using the format parameter, all the fields of all the records of the output are just catenated together and the resulting blob is sent to the client. I would expect the typical use is querying for one field of one record, but if you have need to aggregate multiple fields together, it will do that. The data to be queried this way should generally be stored in bytea fields; bytea fields are delivered as-is, others are utf-8 decoded and non-decodable characters are escaped.

When you request binary format, the format parameter value can have content-type appended to it, delimited by a colon; for example "format=binary:image/jpeg" would result in the query data being sent as a binary blob, and with a 'content-type: image/jpeg' header line. If you are delivering the binary content directly to a browser, the content-type is very helpful to the browser in rendering the object correctly.

Inserting Binary Data into Database

Getting the binary data into the database is its own problem. Our sql_form supports http file uploads of binary data. Put the binary data in a file, and upload it through the form, and it will be processed by the server as binary. Unless you put it into a bytea field, it will be utf-8 encoded going into the table, and will be utf-8 decoded when delivered in response to a SELECT. The Python DB API module does not, today, support binary uploads, but that is planned for the next version. In the meantime, the PostgreSQL decode(...,'base64') function can be used to put binary data into a field, but you would need a client side base64 encoder to pre-encode the data.

Sample Page

A sample page can be viewed here. The full url is below, and it incorporates the account and the query all into the query string. The page itself is delivered in response to a database query, and the embedded image comes from another database query.

http://dev.rdbhost.com/db/r0000000763?q=select+data+from+d+where+tag%3d%27page%27&format=binary:text/html

That page was created using the sql_form form linked from the profile page, with SQL INSERTs and the file upload feature.


David

17 May 2010

Updated to correct url.