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.