Rdbhdb v 0.9

A new version of Rdbhdb is available from Pypi. Rdbhdb is the DB API 2.0 module for accessing Rdbhost databases remotely from Python applications.

Rdbhdb v 0.9 download page on PyPI

Rdbhdb v 0.9 download page on Rdbhost

New in this version:

• Uses gzip decompression, optionally, for data downloads from server.
• Supports the sending of binary data (as buffer datatype) in query parameters.
• .execute_deferred() method provided to execute queries in deferred mode. Deferred mode does not return results, but allows a more generous time limit for query completion. Useful for updates or inserts that might need extra time.
• .nextset() method implemented on cursors. Multiple queries can be aggregated into a single .execute() call, delimited by ';'. One result set is returned for each query, and .nextset() advances to the next result set in the returned list.
• .https attribute added to connection, to force use of SSL. Defaults to False, no SSL.

This version is the one currently in use on rdbhost.appspot.com, for its monitoring function.

Edited 28 Oct 09: Added rdbhost hosted download link.

Edited 9 Nov 09: Spelled PyPI properly

SSL Downtime

I was told yesterday by a site user that www.rdbhost.com's SSL certificate was expired.

For the moment, the secure site of www.rdbhost.com is down, and will be back up later today when I have time to get the certificate updated and installed.

David Keeney

As of 8PM on Tuesday, SSL is back up and working.

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.