Rdbhost

Named Query Parameters

2011-07-15T10:18:00.000-06:00

The Rdbhost protocol now uses named parameters in addition to positional parameters.

Where before you could submit a query like:

<input name="q"
value="SELECT * FROM personnel WHERE firstName = %s AND lastName = %s" />
<input name="arg000" value="John" />
<input name="arg001" value="Smith" />

Now you can make it more readable, like:

<input name="q" value="SELECT * FROM personnel WHERE firstName = %(first) AND lastName = %(last)" />
<input name="arg:first" value="John" />
<input name="arg:last" value="Smith" />

This addition has a couple of benefits. The first is the greater readability and greater ease in assuring the correct parameter values are matched to the correct query tokens.

The other is that each parameter can be used in the query multiple times. For example, in the following snippet, we want to ensure that the value is at least 0.

CASE WHEN (%(val))<=0 THEN trunc(%(val),6) ELSE 0.00 END

The query snippet could be written with positional parameters, but there would then be no way to ensure the same value is passed as both values, and a negative value could be returned. Using a named parameter ensures that parameter value is used both times.

Using your own SSL/TLS Certificate

2011-07-02T09:09:00.001-06:00

Rdbhost now supports your using your own SSL/TLS certificate for your rdbhost subdomain, to encrypt and authenticate data transfer with your account at www.rdbhost.com.

Why Use Certificates

The SSL/TLS certificates have two purposes, encryption and authentication. Any certificate, including a free self-signed certificate, will enable the server to encrypt data transferred between server and client. This encryption prevents data from being observed by eavesdroppers who might have access to your data stream.

However, a connection encrypted with a non-authenticated certificate is vulnerable to a type of attack known as a 'man in the middle' attack. If someone can insert a proxy into the network path between the client and the server, they can conduct a 'man in the middle' attack, impersonating each end of the connection to the other end. When the client (browser) requests a certificate, the proxy sends its own, and then requests a cert from the server. The proxy would hold a certificate for each half of the connection, and thus decrypt and encrypt all data passing through, recording whatever part it is interested in intercepting. This attack can happen because with an un-authenticated certificate, the client has no way of knowing that it is getting a cert from the proxy rather than the server.

The way to prevent this sort of 'man in the middle' attack, if it concerns you, is to use an authenticated, or 'trusted', certificate. If you purchase a 'trusted' certificate from vendor, they supposedly verify that you are indeed the owner of the site, and issue you a certificate that is digitally signed by them using their own trusted certificate. The client can verify the issuers signature with the issuer directly, and thus be assured that the certificate you present is your own. The 'man in the middle' attacker can present a certificate, but presumably would not be able to convince a trusted certificate issuer that they owned *your* domain, and thus could not present a certificate for your domain with a trusted issuer certificate.

There are a few certificate issuers that are trusted by all browsers as released. If your vendor is not one of those, they will have a certificate signed by one of those, sign your certificate with theirs, and send a bundle of certificates that establish a chain of trust from a trusted issuer to you. Godaddy.com is a very popular issuer of certificates, and they issue a bundle file with multiple certificates to establish the chain of trust. The client browser can see who signed your certificate, read the issuers certificate and find who signed it, and repeat until an intrinsically trusted issuer is found.

User Experience

Even if you are not very worried about someone conducting such an attack, you might still want a signed certificate to avoid browser warnings. Browsers, especially Firefox, can be very assertive about warning users of perceived limitations in a site's certificate. Having a trusted certificate avoids warnings and gives the user a smoother user experience. The warnings are so common these days, that many users are blase about it, but it is your call as to how tolerant your users will be.

The Rdbhost Way

Buy your trusted certificate from a vendor of your choice. There are websites that will create a public key pair and a CSR Certificate Signing Request for you, and you should create the CSR using your domain and the subdomain you use for Rdbhost server access, for example rdbhost.mydomain.com. The certificate vendor will create a certificate based on the CSR and make it available for download.

If you have not registered a domain alias on the site, do so now, registering the domain and subdomain, that you use for Rdbhost database access, for example 'rdbhost.mydomain.com'. The domain alias page is linked from the profile page.

If you have not entered the subdomain into the DNS zone manager of your domain registrar, you need to do so.

Visit the Certificate Manager on www.rdbhost.com and paste the certificate into the form field. If your vendor provides a chain (or 'bundle') file, paste it in after your certificate. Paste the private key in at the beginning or the end. Each of these files is a simple text file, so it can be opened in a text editor for cutting and pasting.

Within a minute or two after submitting the form, your account will be accessible using that domain, secured by SSL/TLS and your authenticated certificate.

Limitations

This SSL/TLS service relies on SNI Server Name Indication, an extension to the HTTP protocol. SNI is supported by many modern browsers, but not all. On Windows, notably, SNI handling is left to the OS by most browser brands, and while Vista and later support it, XP does not.

If your user is accessing the database using a browser that does not support SNI, they will get the default Rdbhost certificate, in lieu of your trusted certificate.

As always, comments are welcome.

Wizardry

2011-06-16T09:04:00.007-06:00

When we first went online, many months ago, with the MVP (minimum viable product), the profile page had three or four options, and no subordinate pages. As we have added features, the members area of Rdbhost.com has grown to include six detail pages in addition to the profile page itself, the Rdbadmin utility and the log display.

For any given user, some of those options are irrelevant, and it can be confusing to have to sort through them. We have just added a wizard, which steps the user through the process of configuring everything necessary for their application.

When a user first logs in, they are shown the wizard and it guides him or her through initial configuration. All configuration options are available from the members pages, outside the wizard, or the user can rerun the wizard at any time. Whatever suits each of you.

The wizard can be as brief as four screens, or as elaborate as eleven. Configuring for use from Python can be straightforward, and use from JavaScript is necessarily more elaborate, as the browser security constraints have to be navigated around. Where we cannot do everything required (subdomain configuration, for example) for you, the wizard gives you guidance on how to do the necessary tasks.

Rdbhost, of course, is at: http://www.rdbhost.com
and the wizard is at: http://www.rdbhost.com/mbr/wizard

Home, home on the Ra..

2011-06-14T09:01:00.001-06:00

Our server move is done, and Rdbhost.com now runs on a later version of PostgreSQL, a later OS version, and is on a new server company. We were looking to upgrade the software versions, and Slicehost announced their planned termination, so off we went.

The new server hosting is with Rackspace. They seem to have a good reputation, so hopefully this will work out for us.

Rdbhost.com now runs on PostgreSQL 9.0.4. This is an upgrade from 8.3, and provides us some useful new features.

We have, in the past, setup roles as an automated website operation, but had to handoff permission setting to the user, requiring you to use SQL to set permissions for each table, index, or other resource, for each role. PostgreSQL 9.0 supports setting permissions globally (within the schema) for a given role, and permits setting default permissions for resources not yet created. For now, that is useful to you in manually setting privileges, but we will be providing an automated setup to set permissions broadly for each role.

Rdbadmin has been upgraded to accommodate system table changes in the PostgreSQL upgrade.

David

Comments are welcome.

Not one, but two new videos

2011-05-24T08:18:00.027-06:00

It's been months since RDBHost added CORS support enabling cross-site AJAX requests, and even longer since we put the JavaScript module online for general use.

A few days ago, we put online a new video showing the complete process of creating a JavaScript app. As online videos go, it is a tad long at 11 minutes plus, but it does cover the whole process.

If you think 11:25 is just too long for a video, we put up another shorter video. It shows a few features of the Rdbhost database administration utility, RdbAdmin.

Link to JavaScript Video
Link to RdbAdmin Video
Link to Rdbhost.com

Host your database-backed site on GitHub, for free!

2011-05-05T10:15:00.009-06:00

We could say, "Host your github page's database on Rdbhost, for free", and be equally correct. But Github has a greater profile than we do, so they get first billing.

A database backed website is mostly in the database, but it cannot be done without some static content. The styling and the code are in plain files, which you have to host somewhere behind an accessible url. Rdbhost clients can server static content from the database, using plain urls, but you can reasonably want to host static content elsewhere. Github.com is a very well known git repository host that can also serve web pages. The web site is under your domain name, and the git repository hosting doubles as a handy deployment system for the site.

Github hosts the static pages, and Rdbhost hosts the database, for a database-backed web application at no cost¹. We describe how to set this up, in some detail, here.

We have a demonstration online, at http://gh.rdbhost.com/threefaves , of a database-backed site hosted on github. Three Faves is a little taste-association app, the simplest demonstration app I could think of that might be fun. The site content itself is in a public repository at https://github.com/rdbhost/rdbhost.github.com . All coding is in the repo, in one file, there for you to read and copy. The Rdbhost account white-lists SQL queries, so undesirable queries cannot be run against the threefaves account.

Links:
Rdbhost SQL Database Host
Threefaves Site
Threefaves Repo

¹ We say free, but that is qualified: Github will host non-private content for free, at apparently no volume limit; Rdbhost will host private or public data for free, but only at low volumes.

Minor gains

2011-04-28T16:30:00.000-06:00

The Rdbadmin utility has been upgraded again. I improve it from time to time, and sometimes I write aobut the changes.

A bug-fix corrected the problem that dropped schema names from function identifiers. This proved to be a problem in managing the log trimming functions for our sql logging facility.

We also added comment displays to all the items of the table structure display, including indexes and constraints. Only the table structure edit tool facilitates adding comments, but comments can be added to anything using SQL, like:

COMMENT ON INDEX idx_name IS 'this index is the primary key';

We have been adding comments to tables and other resources that Rdbhost creates in user accounts; When you go exploring existing tables using Rdbadmin, the comments displayed will help you, hopefully, understand how each resource was intended to serve.

SQL Logging, how mundane

2011-04-26T08:09:00.000-06:00

When you're sending requests to a database, or to any online API, it can be useful to be able to tell what requests are getting through, and whether results are being sent back to your client.

Rdbhost accounts now have SQL query logging. Each SQL query is stored in a table in your account, with arguments and contextual information. The log table is in a separate schema (called 'monitor') to minimize name collisions with your data tables, but you can run queries against the log table just like any other table. The page displaying recent queries is linked from the account profile page.

The logs are trimmed automatically, and you can redefine the trimming function to set your own criteria.

https://www.rdbhost.com/sqllogging.html

If you are unacquainted with Rdbhost, each account has one database, which can be accessed via a choice of client-side APIs, or from an administration program, RdbAdmin, that we host.

A post about Authentication

2011-03-31T06:50:00.002-06:00

At Rdbhost, there are passwords, and there are 'authcodes'. What is the difference?

Passwords are about 30 characters or less, memorable, and submitted with an email address as a login. The email/password login process is back-stopped by failure counters, that inhibit a brute force attack on the password by requiring increasing delays between tries.

Authcodes are 50 characters, base-64 character set, that are randomly generated and assigned to the account. Machine requests to the database are submitted with a role name and an authcode. There is a failure counting here, too, but for performance reasons there are multiple counters distributed around and they don't max out simultaneously, and the tolerated failure count for each is fairly high. The authcodes are fairly resistant to 'social engineering' type attacks, as nobody chose them and there is little reason to remember them. They get embedded in code, and forgotten.

Two roles have authcodes, and two do not. Other than the super role, only the auth role has an authcode.

The auth role is different from the super role in that it can only run white-listed queries, and a given white-listed query can be restricted to only run for that particular role. Since the auth role requires authentication, you can restrict its use to certain prequalified users, and give the role more Postgres privileges and its white-listed queries more power.

There has always been a way to change passwords, and now there is a way to reset authcodes, meaning to replace one with a new random authcode.

https://www.rdbhost.com/roles.html

More Backup Options

2011-03-28T12:15:00.005-06:00

This week, Rdbhost has upgraded its bulk data transfer tools to be able to save the database dump to Amazon's S3 service, and to restore from same.

The additions are found on the Bulk Transfer page, which is in the left-hand menu bar on most membership pages. There is an S3 configuration page, which askes for the access-key and secret-key, and the name of the bucket to store to. The transfer to S3 page itself will save to an object named for the account, something like 'rdbhost0000000002' for account 2. The S3 to rdbhost transfer asks for the object name.

The intended purpose for the tool is safe-keeping of snapshots of your database, for restoration in the case of data loss, but it is also useful for transferring databases quickly from one account to another.

The bulk transfer server uses streaming data flows (for all bulk transfers, not just S3), so no temporary copies of the dump are made anywhere, and the largest databases can be transferred. The server will open up to five simultaneous transfers to accelerate the data flow to S3, so the S3 backup will presumably be faster than saving to the browser, though we have not measured the improvement.

You do need an Amazon Web Services Account (see aws.amazon.com), before you can use this streaming to S3 service, and you should create a bucket to receive the object. We only create the object and put the data in it; the bucket must pre-exist.

https://www.rdbhost.com/bulktransfer.html

Update for RdbAdmin

2011-03-03T07:05:00.006-07:00

The RdbAdmin utility has been updated. This is the 3rd major revision, and adds quite a few nice features.

New features:

Supports use of back and forward buttons within the app. This was implemented using the sammy.js library.
Breadcrumb display to assist navigation.
New Function editor
New Trigger editor, linked from the table structure page.
Record Editing forms now include table and column comments; when adding or editing a record, you can see how the table owner described each field.
Record editing now supports a null checkbox for making value NULL.
Most forms (all except SQL-command) support dynamic display of SQL generated. As you fill in the form, you can see what SQL RdbAdmin will generate to change the database when you submit the form.
Most forms have an edit option, so that dynamically generated SQL can be edited in the SQL-command form. So if you need your query to include options not provided by the form, you can 'rough-in' the necessary query using the form, and fine-tune it by editing in the SQL-command form prior to submitting.
Most forms have an 'add query to lookup.queries' link that will display the record-insert form, with the dynamic SQL from the previous form pre-loaded into the record form. When you have a query perfected and tested, you can add it to the lookup.queries table in one step. The lookup.queries table enables your script to execute queries by keyword, and invoke queries using roles that cannot execute free-form queries directly.
In various forms interface now enables and disables buttons depending on relevance.
The '** keep same **' option in record-edit type lists is now gone, replaced by relevant custom type name.
In select panel, records are editable whenever a suitable index is available to uniquely identify unique records.
You cannot see this, but most classes have been refactored to make private attributes genuinely private, and minimize the number of global variables.

Todo:

Restore syntax highlighting; Previously, it previously used CodeMirror, but I had intermittent problems with code dependencies not getting defined.
Add type editing tool, for creating and modifying custom types.

Links:
Github repository
Sample Account

.

Welcome to the new world. HTTP Databases and JSON Storage. Quirkey

2011-01-12T08:49:00.004-07:00

Aaron Quint (Quirkey) blogged (more than a year ago, but still very relevant) about the transforming of web architecture.

Welcome to the new world. HTTP Databases and JSON Storage. The simple act of making the database and the browser more powerful on either end has destroyed the need for the middle tier. In the new architecture, Our database (JSON/HTTP based ...) serves data as JSON directly to the browser. On the browser side we create a much smaller/tighter ‘controller’ layer with JavaScript. This handles the directing of the user to the right place, the displaying of the data to the user, and the conversion of user interaction into state + data. This middle piece is jQuery + Sammy.js.

His blog post uses CouchDB and CloudKit as the server-side elements of the architecture, but his commentary on the appeal of omitting the whole custom server-side layer is just as relevant to Rdbhost as to either of those.

Rdbhost differs from CouchDB, in that the latter is a document database, and the former uses a relational database. For any particular application, one will be preferable to the other. I cannot speak to the authorization aspects of CouchDB as I have no experience with it, but several commenters on Quirkey's post had cautionary statements regarding the authentication/authorization.

Rdbhost supports multiple role-permission levels, and the ability to easily control what exact queries each role can execute. See training your server, and roles pages.

Upgrade

2011-01-07T09:45:00.001-07:00

I sent an email to all of our account holders about three weeks ago, informing them of a planned upgrade of our back-end server from PostgreSQL 8.3 to 9.0.1. We projected that conversion to happen the week after Christmas; that is now last week.

It didn't happen, because other stuff took longer than planned. We are now planning for this to happen the last week of January. That should allow time to get all the prerequisites done, and the conversion automated.

The four roles on each Rdbhost account

2010-12-01T09:08:00.001-07:00

Rdbhost provides up to four PostgreSQL roles for each database account. Each account has exactly one database, and the roles are associated exclusively with the database.

NAMING

They are named uniformly, so that when a role, a database name, or an account id is received by the server, the other identifiers (roles, db names, account id) can be deduced. The database name is the prefix 'db' followed by the account id (an integer) as a '0' left-padded 10 digit string. The role names are 's', 'a', 'p', or 'r' followed by the same '0' padded 10 digit string.

The 's' role (Super) is created with the database, and owns every object in the database, including the 'lookup' schema, if present. The other roles are optional, created at your request via the '/mbr/role_manager' page, and their privileges are whatever you set, via SQL 'GRANT' and 'REVOKE' commands. Below we present a guideline for how you should set privileges for each.

There are important differences between the roles, though, aside from the differing privileges you grant each. Some are permitted to submit arbitrary queries, and some are limited to pre-authorized queries. Let us look at each in turn.

ROLES

The 's'-role, as mentioned above, is the owner of everything in the database. The 's' is a mnemonic for Super, as it is the do-anything 'super' role within the database. An authcode must be submitted with each query request, and that authcode is your security, to prevent unauthorized users from damaging database resources. You do not need to create any privileges for this role, as its ownership of each resource permits any operation.
The 'a'-role, like the Super role, has an authcode, and that authcode must be provided with every request. 'a' is a mnemonic for Authenticated, as the authcode authenticates the requester to use this role. This role is limited to preauthorized queries (that is, queries found in either the lookup.queries or the lookup.preauth_queries tables), so it can be restrained to only run safe queries. The web site uses the name Auth to refer to this role.
The 'p' role is like the Auth role in that it only executes preauthorized queries, but is *not* authenticated. Any query preauthorized for this role can be executed by any member of the public. 'p' is a mnemonic for Preauthorized. (It could also be a mnemonic for Public, reflecting the unauthenticated quality, but don't confuse it with the PostgreSQL role 'public'). Securing this role is a combination of setting role privileges and prequalifying safe queries. For example a blog site might allow APPEND privilege to this role for the 'comments' table, so that anonymous readers of the blog can comment, and the query saved to the lookup.preauth_queries table would be written to require the user IP address to be entered into the 'comments' table to facilitate anti-spam maintenance. The web site uses the name Preauth to refer to this role.
Last but not least, the 'r'-role is also *not* authenticated, and allows free-form queries. Your only security with this role is in setting PostgreSQL privileges carefully. Generally, you would grant SELECT privilege on tables that anonymous users need to read, and no other privilege. 'r' is a mnemonic for Reader.

PRE-AUTHORIZED QUERIES

The Auth and Preauth roles may only execute queries that have been preauthorized by entering them into one of the lookup tables, lookup.queries or lookup.preauth_queries. The first is intended for manual entry, and the second is intended for automated entry.

Automated entry is done by the training mechanism. See link below for more on that.

Manual entry into the lookup.queries is done by the Super role, using either the SQL_Form or the RdbAdmin tool. Queries in the lookup.queries table must be accessed by their tag, not by the query string itself. Use the 'kw' request parameter rather than 'q'. Unfortunately, the Python DB API library (Rdbhdb) at this time does not support 'kw' lookups.

LINKS

Django?

2010-11-28T15:12:00.000-07:00

I suspect that the Django ORM gets more use than either SQLObject or SQLAlchemy, just because it is part of the Django stack, included in Django installs by default. For this reason, I would like to include Rdbhost support in Django.

A day plus spent in pursuit of that goal has been fruitless. This post is a summary of my experience.

Before I announce support for an ORM or framework, I try to ensure that as much as possible of the framework's tests pass with this database. I found Django's testing tools difficult to understand and difficult to use.

I started testing in an app, with 'manage.py test', until someone cued me in to the runtests.py utility; the Pip/easy_install version of Django does not include most of the test suite, so when I found out, I switched to the tarball version.

Testing with runtests.py worked better, but still not great. Firstly, it seemed to ignore the '--fastfail' switch when the failure was an exception (an Error, in testing parlance), rather than a negative result of an assertion (a Fail). Most 'failures', for me, will be exceptions, and --fastfail should abort on them, as do unittest and py.test with the -x switch.

runtests.py doesn't abort on Error, and its error message is just 'ERROR', without a test name or line number or anything. Aborting with a ctl-C will abort the test run, but will not display the test report for prior failures. So the only way to get a test result (with test name and stack-trace) displayed for an exception is to wait for the batch of tests to complete, which could be 20-50 tests and take upwards of 10 minutes. This is an extremely tedious way to diagnose problems.

When you do have the name of a test, like 'modeltests.aggregration.tests.BaseAggregateTestCase', and try to run it specifically, with 'runtests.py modeltests.aggregration.tests.BaseAggregateTestCase', you get an error about 'test labels' and 'app...'

When I tried to runtests.py with a simple 'sqlite3' based settings file, I still got many errors, failing on 25-35% of the tests. This casts doubt on whether the test suite itself is maintained, and it is very frustrating to try to get a component to comply with test expectations when you cannot be sure the tests aren't themselves broken. The test runner seemed to find the json fixtures when running with the sqlite3 settings, but not with the rdbhost settings. Why would retrieving data from a file in the distro depend on what backend was configured?

The test suite looks fairly comprehensive, but without a test runner that will run specific tests in a predictable way, developing to the tests is not practical. The Django compatibility effort here is on the shelf for a while.

Page hosting at Rdbhost

2010-11-23T09:21:00.002-07:00

We have long presented our service as a database hosting service, providing data querying for applications hosted somewhere else.

Now, you can host the whole thing right here.

Just stuff your html and JavaScript files into database blob fields, point your domain at our server
and register the domain name as belonging to your account here. The registration is done from the 'Domain Alias' page, linked from the profile page.

Rdbhost will interpret a plain-jane URL as identifying specific data elements, and deliver your html and JavaScript blobs as pages to your users' browsers.

Setting the whole thing up involves a few steps, so it is probably not the easiest plan to develop incrementally with an Rdbhosted configuration; instead, develop using a local server, as discussed in the DNS page, and move the JavaScript and html files to Postgresql field blobs for production deployment.

See a discussion of the features at:

http://www.rdbhost.com/user_domains.html

Query Timing

2010-11-22T09:43:00.002-07:00

A small change has been made to add a data element to the data returned by queries. The change applies to both the JSON variants and XML.

There is a new top-level element called 'times' which is an array of two decimal values. The first decimal value is the duration, in seconds, of the processing the website front-end does prior to passing the query to the database back-end. The second is the duration of the database operation in the database backend.

A sample excerpt for JSON:

"status": [
        "complete", 
        "OK"
    ], 
    "times": [
        "0.036002", 
        "0.000000"
    ]

There is a third major element in the on-site processing of the query, and that is the time spent serializing the results into JSON or XML. This time is not included in the time record, as the time array has to be populated prior to serializing.

You can measure the total round-trip time, yourself, from the client, but that time includes internet overhead. These data items may be helpful in optimizing your queries.

As always, comments are welcome.

How to Train Your Database Server

2010-11-15T10:29:00.013-07:00

Rdbhost allows SQL queries to be submitted from the browser and executed. A cursory consideration of that model suggests security problems, in that whatever is in the browser can be manipulated, and user-generated queries can be submitted by any user using authentication information extracted from your source code.

We thought of that, and provide means to authenticate individual queries and prevent execution of unapproved queries. The Rdbhost service supports four different roles; two of them allow free-form queries, and the other two only allow pre-authorized queries.

Roles for Free-form Queries
The first two, the s- (super) and r- (read) roles allow any arbitrary query to be executed. The first is all-powerful, owning every resource in the database, and relies on an authcode (a long non-memorable password) to prevent use by arbitrary non-authorized users. The second, the r- role, allows any query from any user, and relies on the Postgresql role privileges being set to prevent unwanted changes to the database. Basically the r- role would have only SELECT privileges on relevant tables, allowing data to be read, but erroring on attempts to change the data. The r- role provides the convenience of being able to change queries in your client source code without retraining the server. Web applications, in my experience, tend to have many more read queries than write queries, both in access count and in number of distinct query strings.

Roles for Mutating Queries
The other two roles allow only pre-authorized queries, and are the roles subject to training. These roles may
submit queries with either the SQL query verbatim, or with a keyword that retrieves the SQL from a lookup table. The query SQL itself must already be in either the keyword lookup table or in a table of pre-authorized queries. The keyword lookup table is called 'lookup.queries' and is intended for manual entry and maintenance. The other table is called 'lookup.preauth_queries', and is intended for automated query entry. These two roles are prefixed with 'p' and 'a'. 'p' is a mnemonic for public and has no authcode, while 'a' is a mnemonic for 'authenticated', and does have an authcode. Most apps will use 'r-' role and 'p-' roles exclusively.

Training
Queries are entered into the preauth_queries table via the training process. The website has a page in the members area for enabling training. Training is enabled for individual client IPs separately, so some clients can be using the table in the usual safe way, while your workstation client, registered as a trainer, is entering queries into it. Once your workstation's IP is added to the trainers list, you can run any query from any role; when a query is received for a p- or a- role it is added to the preauth_queries table before executing. So executing your apps test suite or simply exercising every feature of the app will put all necessary queries into the preauth_queries table. You can then disable the training mode, test your app again, and release it.

Overview
The general approach to application development in Javascript is:

Create an account, with its own database, on www.Rdbhost.com
Create necessary roles from role_manager page
Enable training for your workstation from training page
Configure your hosts file to include necessary local and Rdhost server entries
Run a local server; a tiny Python 8-liner will do
Develop your application
Develop your application (it's in here twice, because it's big)
Develop a test suite (optional)
Clear the preauth_queries table on the server; this removes development cruft
Run your test suite, or just exercise all SQL-using features of the app
Disable training for your workstation, from training page
Test application, either by test suite or manually
Release and distribute your app

A process of thirteen steps seems long, but only steps 6-8,10 and 12 are more than a few mouse-clicks worth of effort.

Related Links:

Training Page
PostgreSQL Roles on Rdbhost
Javascript API module for Rdbhost

Your comments are welcome, to dkeeney@rdbhost.com, or in the blogspot comment facility below.

Remote Database Access from the Browser Made Easy

2010-10-19T09:45:00.006-06:00

You can now access Rdbhost databases from your in-browser javascript code without any special domain pre-configuration.

jquery.rdbhost.cors.js

The jquery.rdbhost.cors.js library works in modern browsers only, those that support the CORS extension to HTTP. If you are building an html5'ish site, you are already constrained to modern browsers anyway, and this library may serve you very well.

The API, the set of functions and methods, is the same as our other javascript library, jquery.rdbhost.js. You can switch between them by changing the script tag; the remaining code will generally still be valid. Obviously there are differences, otherwise why have two library? Read on for more detail.

Since you, as a web developer, are very likely using the latest browser version, you can start using this library as a dropin. Setup an account on the www.rdbhost.com website, if you haven't already, add your development host url to the remote hosts config field on the website, add a script tag to your html page including the library, and you are in business.
If the project develops to the point where compatibility with older browsers matters, or if you eventually need features like binary uploading that ajax does not support, you then easily swap out the jquery.rdbhost.cors.js module for the jquery.rdbhost.js module, and your code will be otherwise unchanged.

Changes to the website to support this include adding the CORS compliant headers to those page requests that present 'Origin' headers, and adding a field to the account records that tracks which domains are permitted to access each account. There is a new status line on the profile page to report what domains are registered, and a new form page, 'remote_hosts', to permit changing that domain list.

Differences

The regular library is both more capable and more hassle to setup.

Requires your domain (possibly 'local.host') to have a subdomain that points at our server.
Allows binary data uploads to database using file fields, as well as raw binary downloads.
Works in nearly all browsers in common use, including Internet Explorer 6.

The cors library is easier to use, but has significant limits

Domain configuration is NOT necessary
No binary upload, though binary downloads of database contents is still possible.
Requires a newer browser, Firefox 3.5+, Chrome 5+, Safari 4+, maybe Opera

Links:

Information about DNS subdomain configuration
The library, in Git, on github.com
The w3c working draft on CORS
The Rdbhost Javascript howto page.

How does Rdbhost.com compare to other services?

2010-10-12T08:06:00.003-06:00

I get asked, occasionally, questions along the line of 'Why should I use Rdbhost instead of a VPS?' or '... instead of Appengine?'...

Now there is a page on the website that attempts to address that class of questions, at:

http://www.rdbhost.com/comparisons.html

I am not going to rehash each comparison here in this blog-post, but am going to cover some generalities.

Most of the competitors are variations of web hosting or web server accounts. Each involves a fair amount of setup and custom configuration before it serves useful data. Most involve setting up an SQL database, and then migrating the initial database tables, views and other resources to that database.

Rdbhost databases are created with JSON and XML encoding built-in. Figuratively, ready 'out of the box', but literally 'already in the box'. Http request parsing and database querying, already in there.

The SQL queries themselves, you have to write, as they are necessarily custom to the project. What we can setup generically, we do setup. We even provide a means to restrict specific database roles to running only pre-approved queries.

There is also a cost difference, in that the hosting or server accounts charge by the month, so the minimum cost is always non-zero. For most business developers, this would be the least concern.

One competitor I looked at provided a web-service specifically limited to databases. CloudDB seemed to use a non-standard query language, which would lock users in to their service, once the code was written. I say 'seemed', as they have not seen fit to give me an account. If any of you do have accounts there, I would appreciate reading a review, or at least, corrections or enhancements to what I have been able to glean from the documents.

Comments are welcome, especially those that suggest competitors thay might be more of a straight-up substitute for Rdbhost.com.

Thanks to Ben Nadel

2010-10-09T17:22:00.002-06:00

Firefox had been exhibiting an undesirable behavior on pages that used our javascript module, jquery.rdbhost.com. The module, under Firefox, would function correctly, but never seemed to detect that data reception had completed, but would show the busy spinner and 'loading...' indicators.

I googled in futility, I asked on stackoverflow, not constructively.

Branko Vukelic cued me to read Ben Nadel's blogpost on just that topic, which explained why Firefox was behaving that way.

It seems that Firefox acknowledges that an iframe document was completely received only after the onload handler returns. If the iframe is deleted from the DOM within the handler, Firefox never detects completion. The solution Mr Nadel suggested, and which I used, is to use the javascript timer to call a deletion function to run after a brief delay. This allows the handler to return while the iframe persists, but does not let the iframe linger around.

The fix has been pushed to the github repository for jquery.rdbhost.js, so feel free to grab the latest version.

jquery.rdbhost.com is out there.

2010-08-29T22:35:00.009-06:00

Javascript programmers dodging that whole server-side programming thing are a market for us.

Unfortunately, until now, a JS programmer wanting to query Rdbhost databases has had to write there own low-level javascript ajax code, and do the domain pointer management... I don't think anybody actually did all that.

Now, there is a module that makes accessing an Rdbhost.com database from your server easy. You still have to set up a subdomain for your domain, to point at our server, but after that it is pretty straightforward.

A page snippet might look like:

<script>
  $.ready( function () { 
    $.rdbhostConfig( { 'userName' : 's0000000002',
                       'authcode' : '-'   } );  
    $('table#big_cities').populateTable(
         'SELECT * FROM cities WHERE population > 10000000');
         // no pun intended
  };
</script>;

The library

New RdbAdmin Features

2010-08-11T08:08:00.003-06:00

As I mentioned a couple days ago, the Rdbadmin application has been revised. The general goals of the revision was to make the app more maintainable and remove a few bugs.

Specific goals were:

Access the host server via the new Rdb.js module, instead of $.ajax and custom code.
Make the application portable, so that it can be loaded from other servers under other domain names.
Move all html out of the javascript modules into the html host page itself. This makes a css 'skin' designer's job easier, as all the html elements are there in the page to see.
Move all the javascript out of the html host page, and into javascript files.
All javascript is to conform to jslint expectations.
Minimize the number of globals. The former version included a global object for each form and a few more, with references to those globals scattered all over.

These were all accomplished, with exceptions here and there. There is still a smidgen of login and initialization code in-line in the html file, and the javascript files still have some $('<option>') elements here and there, so the html/js separation is not pristine. JSlint compliance was with a custom configuration, not the default.

In addition to the above items, an additional couple of features wer added:

The table structure page now includes, in addition to the table columns and indexes, a list of constraints. There is a new form to add or drop constraints for a table. I also removed the 'primary key' and 'unique' columns from the table edit, as those constraints will be handled by the Constraints form. Eventually, they will be added back in.

Also, each form now features an SQL display box. As the form is filled in, the app continuously generates and displays the SQL that will perform the desired action. When filling in a create table form, for example, the SQL box will display the 'CREATE TABLE tablename...' code that will be sent to the server to generate the table. This might be useful to new programmers to understand how SQL syntax goes, and might help experienced programmers understand what, exactly, the form is doing.

Most forms include an 'Edit SQL' button, which will load the displayed SQL into the SQL editor for refinement and submission to the server. The PostgreSQL syntax for various operations, such as creating tables, is more capable and feature-rich than the RdbAdmin interface, so for some less common requirements, you might need to drop to the SQL editor to get what you need.

There is a login page now, if none of the automated login approaches work. The app can be loaded with a non-authenticated role in the URL, and that role will be used for the database connection, otherwise it presents a login app, for entry of email and password. When the app is loaded from Rdbhost itself, a cookie-based login is attempted, for users that have already logged in to Rdbhost itself.

The code is at: http://www.rdbhost.com/downloads/rdbadmin-0.8.zip
The latest code will always be available from subversion, at: http://www.assembla.com/code/rdbadmin/subversion/nodes

RdbAdmin, free at last!

2010-08-09T09:43:00.003-06:00

The Rdbadmin database administration has a couple of purposes.

Firstly, of course, it is a tool for administering Rdbhost databases. As an online application, it is available to all accounts immediately, without installing anything.

Secondly, it is an example of what can be done from Javascript to manipulate online databases. We could have done a server-side admin script, ala PhpMysqlAdmin, but that might seem like an expression of no faith in the Rdbhost design. If we are presenting the host as a do-anything-from-Javascript database host, shouldn't we walk the walk, and implement database tools in Javascript ourselves?

Rdbadmin has, thus, always been a Javascript application, and source code has been available for the borrowing. However, before now, it did not run on any server other than the rdbhost.com server, due to Javascript cross-site-scripting protections. That is about to change: I have been, over the last month, rewriting the Rdbadmin app to use the Rdb.js interface module, which makes it portable. By mid-week, you will be able to check-out from subversion a version that works hosted on any* server, to access databases hosted on Rdbhost. You will then have a working admin script on your server, subject to your evolutionary refinements, or to just borrow working code from for your own Rdbhost-based projects.

The Rdb.js module itself will be properly released this week as well, with documentation. It is available now, on github, but lacks documentation, and is still kindof crufty with dead code.

* The Javascript cross-site-scripting constraints have not gone away, so you will need to be able to create (and point offsite) a new subdomain for your host server; a how-to will be in the release package. We are also working on a CORS based approach, but that has its own limitations.

Improvements to Bulk Transfer

2010-07-19T08:11:00.001-06:00

We expected that the principle use of our bulk transfer page would be to backup databases and to restore a database from a prior backup.

It is also useful for initial loading of databases, and an improvement this weekend makes that easier.

When PostgreSQL dumps a database, it generally includes in the dump statements to assign each relation to a specific role as owner. This assumes that the same roles will be available on restore, an assumption justified by the use of dumps as backups.

When a dump made elsewhere is uploaded to an Rdbhost database, the roles embedded in ALTER.. OWNER statements are generally not available, so the tables end up inaccessible to any of the defined roles.

Now, at the end of a restore, any tables or views not owned by valid roles are given to the s-role for the account.

So you can export tables owned by any role into a dump file, upload it to Rdbhost, and end up with them all owned by the default role in your Rdbhost account.

JSOND

2010-06-17T09:10:00.001-06:00

In my last post, I introduced the Rdb.js library, which allows making requests of an Rdbhost database, provided that the Rdbhost server has been aliased to a subdomain of your domain.

The cross-site data retrieval relies on the data page containing a little executable javascript, that, when loaded into an iframe, changes the iframe 's document.domain to a right-hand subset of the domain that then matches the main frame, similarly treated.

The Rdb.js library handles all this for you, but I describe the method here in case anybody wants to create an alternate library, or modify the library.

The request goes to the server using the client's domain, and with the 'format' parameter set to 'jsond'. The data returned by the server will be formatted similar to:

<html>
<head>
  <script type="text/javascript" >
    window.document.domain=
    "window.document.domain.split('.').slice(-2).join('.');"
  </script></head>
<body>
  <script type="text/plain">
    { ...json here... }
  </script>
</body>
</html>

Lines 4 and 5 execute when the data is loaded into the iframe, changing the domain. It removes all but the rightmost two dot-delimited segments. For example, 'rdbhost.example.com' becomes 'example.com'.

The calling frame also executes the document.domain manipulation so the two have the same domain value. The caller can then load the data from the body script container, and convert it using a JSON parser.

The container is a script with type 'text/plain', as the browsers will leave the contents of such a container uninterpreted, a raw string. The body of the script tag is escaped by replacing all instances of '</' with '<\/'. The JSON parser, or Javascript's eval, will interpret the two identically, so no unescaping is required.

Rdb.js

2010-06-16T09:32:00.025-06:00

Cross-server data transfer is one of the more problematic areas of javascript in-browser programming today.

The obstacles are designed into the browser for good pragmatic security concerns, but sometimes you have legitimate need to retrieve data from multiple servers in one page.

Using Rdbhost databases from javascript applications is one such need, and we provide a library to make access easy and reliable. The method we recommend, the method our library supports, involves some domain name server (DNS) manipulation.

I assume you are hosting under your own domain name, and that your domain registrar provides a way to configure subdomains. Just create a subdomain of your domain that points at our server. The IP address of the Rdbhost server is on your profile page.

For example:

With that done, wait a few hours for the change to propagate across the internet.

Include the javascript module 'rdb.js' in your page, by reference, and create a javascript inline sequence to initialize an SQLEngine object. Here is a code sample, and line-by-line explanation follows

01 var uid = 'r0000000002';
02 var authcode = '2398473219847219834';
03 var rdb = new SQLEngine(uid,authcode,'rdbhost');
04 var query = 'SELECT * FROM css_data';
05 var res = rdb.query(  {'callback' : success,
06                        'q' : query } );
07 function success(json) {
08   // do something useful with data
09   for (var i=0; i<json.records.rows.length; i++) {
10     var val = json.records.rows[i];
11     alert('engine: '+val[0]);
12   }
13 }

Lines 01 and 02 just store the necessary user id and authcode, copied from the account_manager page.

Line 03 creates the SQLEngine object, which will handle making queries against the database.

Lines 04-06 query the database; the function success is called with a data structure containing the retrieved records.

Lines 07-13 implement the callback success, which simply loops over the list of rows, displaying an alert box with the value of the first field in each record.

Testing
The above script can be pasted into a script element in an html page, after a script element loading the rdb.js library, and (with a valid uid/authcode combo) will function. It does not need any supporting html, beyond the script container.

The jquery plugin DataTables works very well with Rdb.Js. See examples here:

JSON Example 1
JSON Example 2

The javascript database client code is inline in the source files, so view-source will be informative.

API
The SQLEngine object has two methods intended for client use. The query method, demonstrated above,
takes one object as a parameter, and expects at least two attributes in that object. See the documentation
page on site for more specifics.

The other method is queryByForm, which takes three parameters: the first is a string with the id of
the source form, the second is the success callback function, and the last is an optional error callback. This method is useful if you wish to send file-sourced data to the server; you can put file-fields in your form, and after the user has selected files, the form can be submitted using the queryByForm method. The form must have a field (hidden or otherwise) q for the query, and optionally format, arg### and argtype### fields.

Rdb.js documentation

PostgreSQL upgrade

2010-06-09T12:55:00.003-06:00

The Postgresql server behind Rdbhost has been upgraded to the latest 8.3 maintenance release, version 8.3.11.

YASOP

2010-06-08T21:20:00.004-06:00

Our Stackoverflow data dump hosting account has been updated to include the latest data.

Take a look at:

Rdbhost Stackoverflow Account

It now shows off a new custom look, designed for it by Branko Vukelic.

Each account can now host its own css file, which is loaded by the admin script in lieu of the default.

See this post on skinning.

Screencast is now shorter

2010-06-07T09:48:00.002-06:00

Rdbhost's introductory (and so far, only) screencast has been slimmed down, from 8 minutes to 4 ½.

http://www.rdbhost.com/screencasts/introscreencast.html

Hopefully, more folks will watch, and more will finish.

CSS skins on RdbAdmin

2010-05-21T09:16:00.007-06:00

Those of you who consider our default Rdbadmin 'look' to be unattractive, can now provide your own css 'skins' for your own accounts.

The admin program now checks for a query in the lookup.queries table called 'css'. If found, that query is requested and the contents used as a css style-sheet for the page. If none is found, the default stylesheet is used.

Two demonstrations:

The Stackoverflow account now has a look suggestive of the stackoverflow site.
A baseball data account has a distinctive green theme.

The baseball account has a logo image as part of its theme, and the theme is self-contained within the account. To accomplish this, I created a table in the lookup schema to hold the css stylesheet and the image file. I called this table 'pseudofiles', as it serves individual fields as though they were files. The css data and the image file went into separate records in the table, and the lookup.queries table gained an entry to retrieve the css, and another entry to retrieve the image file. The entries resembled:

tag: css
query: SELECT "body" FROM "lookup"."pseudofiles" WHERE "name" = 'css'
format: binary:text/css
nopermit: {} (empty set)

So the browser loads the rdadmin/main.html page, and embedded javascript loads the css stylesheet. The stylesheet contains a background image url, and that url includes a query for the background image. The result looks like this.

The stackoverflow account uses a slightly different approach. The 'stylesheet' was a one-liner, completely embedded in the lookup.queries query.

SELECT "@import url(http://www.freshfaves.com/css/stackoverflow.css);"

The real css style information is then stored in a regular file on the freshfaves.com server, and loaded by @import. This approach has the merit of keeping the true styles in a relatively easily edited format, but also the penalty of having to involve another server.

minor grammatical and line-break editing 10 June

TechZing

2010-05-07T09:29:00.002-06:00

TechZing, a podcast by Justin Vincent and Jason Roberts, has reviewed Rdbhost in their latest edition.

The review was a rambling half hour of the 90 minute podcast, and was quite interesting. The principal lesson I drew from it, is that it can be difficult to clearly communicate your USP (Unique Selling Proposition) for a web business.

Thank you to Mr Vincent and Mr Roberts for spending some time reviewing. I enjoy each of your podcasts, and this one no less.

What is my USP?

'SQL over HTTP, demand priced' .

Some competitors offer the webservice (HTTP) with a proprietary query language, and others provide the SQL database without the webservice interface. Demand pricing, with no minimum price, is not offered by any of them, that I know about. I do not use that USP phrasing in the website anywhere, because it is just too geeky; I try to phrase the website presentation from more of a 'what good is it?' perspective.

Rdbadmin now supports file fields

2010-05-03T09:40:00.002-06:00

Rdbadmin is our online database manipulation tool. It provides handy tools to create and edit tables, indexes, and views, but also provides for arbitrary free-form SQL (PL/pgSQL) queries.

The pages that support inserting records into tables and editing records have been enhanced to allow file uploads as field values. Any arbitrary data that you can put into a file, you can put into a database field using Rdbadmin.

The SQL panel has been enhanced similarly, in providing 8 argument fields. The SQL can include substitution parameters '%s'; these parameters are replaced, in sequence, with the values of fields 'arg000', 'arg001', etc. The field values are quoted appropriately to avoid SQL injections, and the fields can be file uploads as above.

The last improvement to report in this post is that the field in the SQL panel for entering the free-form queries now has syntax-coloring. The coloring is provided by CodeMirror and a plsql syntax file.

As always, comments are welcome.

http://marijn.haverbeke.nl/codemirror/

Binary data and file fields

2010-04-29T08:36:00.002-06:00

Rdbadmin has been improved to easily allow binary data to be entered into table insert and update forms.

Each input field in the record-insert and record-edit forms now has a 'file' button (which toggles to a text button), that converts the field to a file field, permitting you to upload a file to populate that field.

The record-edit and record-insert forms are also more content-aware now, changing size to suit the field data-type; large input fields for large db fields, small for small.

Technorati Claim Code

2010-04-07T19:03:00.001-06:00

This code BPKZ237VADQE helps Technorati verify that I own this blog, apparently. The *rest* of you can ignore it.

YASOP: Yet another Stackoverflow Post

2010-04-05T09:19:00.001-06:00

Our copy of the stackoverflow data-dump has been updated to the April release, containing data through March 2010.

See:
https://www.rdbhost.com/rdbadmin/main.html?r0000000767

Rdbadmin looks much better

2010-03-30T10:48:00.002-06:00

A couple of consultants have been working on the look of Rdbadmin lately, and it consequently looks much nicer.

Ryan Foran reworked the appearance to make it more strongly resemble the rest of Rdbhost, with the purple round-cornered title panel, and similar colors otherwise.

Branko Vukelic then reworked the link-buttons, so that they now look like buttons. I recruited Branko to do some other work on the admin program, but you will have to wait to read about that. I will tell you about it when it is pushed to the production server.

So, a big thanks to Ryan and Branko.

See http://www.rdbhost.com/rdbadmin/main.html?r0000000767 , to observe the improved admin look with the stackoverflow database.

Rdbhost module is in SQLObject trunk

2010-03-19T08:43:00.002-06:00

The Rdbhost connection module for the Object Relational Mapper (ORM) SQLObject, is now included in the subversion trunk.

Check out (or update) the latest from:
http://svn.colorstudy.com/SQLObject/trunk/

Using your Rdbhost database via SQLOBject is done like this:

from sqlobject import *

role = 's0000000849'
authcode = 'abc123~~~~~~~789xyz'
sqlhub.processConnection = \
connectionForURI('rdbhost://'+role+':'+authcode+'@www.rdbhost.com')

The role and authcode are found on your Rdbhost profile page after you log in.

Presumably, the Rdbhost module will be in the next packaged release. Until then, just check out HEAD from subversion to use SQLObject with Rdbhost.

Facebook

2010-03-11T12:49:00.002-07:00

Rdbhost now has a modest presence on Facebook. Created to catch the attention of any database customers who might be searching Facebook for postgresql or sql resources.

May amount to nothing... don't know.

PL/pgSQL on Rdbhost

2010-03-10T09:56:00.010-07:00

We have added the server-hosted programming language PL/pgSQL to all Rdbhost accounts.

It is enabled by default for new accounts, and can be enabled on existing accounts via the new Languages page, linked from the profile page.

PL/pgSQL supports variables and conditional execution, so you can write more sophisticated queries than with straight SQL. It is now possible to write sequences of operations to be executed in one request, that formerly required multiple requests, with client side intermediate evaluations between.

One example is illustrated here . Instead of doing an update, determining whether it succeeded, and conditionally doing an insert in a follow-up query, PL/pgSQL code can do the whole sequence on the server, and complete in one request.

http://www.rdbhost.com/plpgsql.html
http://www.postgresql.org/docs/8.2/interactive/plpgsql.html

SO March

2010-03-06T13:10:00.002-07:00

The data from the March 2010 data dump from the Stackoverflow website is online, at:

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

The above link is to an online administrative program. We also support access to the data via

a DP API module, at: http://www.rdbhost.com/howpython.html , and the API module supports ORMs, including SQLObject and SQLAlchemy.

Comments are welcome.

Rdbhost on twitter

2010-02-16T14:37:00.003-07:00

We are not really active on twitter, basically just letting twitterfeed create tweets based on blog posts here, but...

if you keep up with things via twitter rather than a blog-reader, then you can get announcements of new blogposts here from our twitter feed.

www.twitter.com/rdbhost

RdbHost on ProgrammableWeb blog

2010-02-16T14:22:00.003-07:00

We got a mention on ProgrammableWeb.com, in their API News page. Thanks, PW!!

See:

5 new APIs

SQLAlchemy and the Stackoverflow Data Dump

2010-02-13T14:04:00.007-07:00

We recently wrote an rdbhost module for SQLAlchemy (SQLA), allowing you to use Rdbhost databases through SQLAlchemy. It was mentioned in this blog about a month ago, here.

In the last couple of days I uploaded the February Stackoverflow data dump to our SO database here (account 767), and placed an announcement on meta.SO, including a mention of the DB API module. If you are an SQLAlchemy fan, and wish to explore the SO data via SQLAlchemy, this post will help get you started.

You do need SQLAlchemy installed, and it is installable from easy_install, or manually from a downloaded copy. You need the rdbhost-module, as well, placed in the databases folder within your SQLAlchemy install. SQLA will find it there.

Example:

Python 2.5.4 (r254:67916, Dec 23 2008, 15:10:54) [MSC v.1310 32 bit (Intel)] on win32
IDLE 1.2.4  
>>> from sqlalchemy import *
>>> role = 'r0000000767'
>>> authcode = '-'
>>> dsn = 'rdbhost://'+role+':'+authcode+'@www.rdbhost.com'
>>> db = create_engine(dsn)
>>> meta = MetaData(db)
>>> meta.reflect(schema='so')
... elided warnings from sqlalchemy about partial indexes ...
>>> for t in meta.tables:
....  print t

so.posts
so.badges
so.tags
so.users
so.votes
so.comments
so.tagging
>>> posts = meta.tables['so.posts']
>>> print posts.columns
['posts.id', 'posts.type', 'posts.parentid', 'posts.title', 
'posts.creation', 'posts.owner', 'posts.accepted_answer', 
'posts.score', 'posts.viewcount', 'posts.body',  
'posts.lasteditor', 'posts.lasteditorname', 
'posts.lasteditdate', 'posts.lastactivitydate', 
'posts.communityowneddate', 'posts.closeddate', 
'posts.tags', 'posts.answercount',  
'posts.commentcount', 'posts.favoritecount']
>>> from sqlalchemy import select
>>> users = meta.tables['so.users']
>>> s = select([users],users.c.reputation > 100000,limit=50)
>>> recs = db.execute(s)
>>> for user in recs:
.... print user.name, user.reputation

Marc Gravell 109683
Jon Skeet 134930
>>>

Bulk Data Transfer to/from Rdbhost

2010-02-12T06:59:00.003-07:00

An upgrade to the server recently added the ability to upload and download data in bulk.

Downloading works very similarly to pg_dump, providing any of the three formats that pg_dump supports.

a) plaintext
b) tar file
c) compressed format

Uploading works like psql for plaintext, and pg_restore for the other two formats. In fact, the server feeds the input to those two utilities behind the scenes, so the results you get are exactly the same as if you were to use the utility directly. If you are uploading, drop in advance any existing tables that are in the upload, as the upload does not drop them for you.

The bulk transfer page is linked from the profile page, after logging in.

The upload streams the data to PostgreSQL, so processing starts when the upload starts; if you cancel the upload (by closing the browser window, for example) partway done, you will likely leave the database in an incomplete state. Remember to reclean the database before retrying the upload.

Rdbhost API on ProgrammableWeb

2010-01-29T08:04:00.002-07:00

We are now listed in the APIs section of ProgrammableWeb!

This is a very cool event, as PW has a fairly high profile on the web, and should bring us more traffic and hopefully a few more ambitious developers.

See: http://www.programmableweb.com/api/rdbhost

SQLAlchemy and Rdbhost

2010-01-06T08:23:00.002-07:00

SQLAlchemy is an ORM, an object relational mapper. To some folks, that is a very meaningful phrase. To me, it is just another way to programmatically access SQL databases.

This week, I created a module to facilitate using Rdbhost databases from SQLAlchemy. See it documented on: http://www.rdbhost.com/orms.html . We provide one file, aptly called 'rdbhost.py' which you add to your SQLAlchemy's 'database' directory, and SQLAlchemy will then use it to open and access databases hosted on www.rdbhost.com.

When you call the create_engine function, you give it a connect string like: 'rdhbost://role:authcode@www.rdbhost.com' .

Once you have the engine object, thus created, you can create, drop, and query tables using the SQLAlchemy/Python syntax.

Here is an excerpt:

from sqlalchemy import *
role = 's0000000849'
authcode = 'h.89as9fa9dsaf.~~~~~~~~~~kajd8098ajsf'
dsn = 'rdbhost://'+role+':'+authcode+'@www.rdbhost.com'
db = create_engine(dsn)

metadata = MetaData()
users = Table('Users', metadata,
... Column('id', Integer, primary_key=True),
... Column('name', String),
... Column('fullname', String),
... )
addresses = Table('addresses', metadata,
... Column('id', Integer, primary_key=True),
... Column(user.id', None, ForeignKey('users.id')),
... Column('email_address', String, nullable=False)
... )
metadata.create_all(db)

Rdbhdb 0.9.2

2010-01-05T08:01:00.004-07:00

I've been working lately on making Rdbhost work better with ORMs, such as SQLObject and SQLAlchemy. One outcome of that effort is improvements to Rdbhdb.

A new release is out, version 0.9.2 with a couple of new features:

Fields fetched are now converted into datetime.Date, datetime.Time, datetime.Datetime, and decimal.Decimal objects, depending on type.
Times and Timestamps now have microsecond resolution.
Parameters provided to .execute* calls are now typed, so the server can return each data item, as necessary, in the appropriate type.

The module is available from here, or from PyPI.

http://www.rdbhost.com/downloads/rdbhdb-0.9.2.zip
http://pypi.python.org/pypi/rdbhdb/0.9.2

Server update

2010-01-04T21:24:00.002-07:00

The server software was updated yesterday.

The changes are:

The /db app recognizes data-type arguments, named like 'arg###type', and having values from ( 'NONE' 'STRING' 'NUMBER' 'DATETIME' 'ROWID' 'BINARY' 'DATE' 'TIME'). The type argument describes the argument with the same prefix; 'arg001type' describes 'arg001'. The type can be omitted, and defaults to STRING.
JSON date format now includes microseconds. Dates are just strings, like: '2009-12-31 12:59:00.000000.

The Rdbhdb DB API module has been updated to use these features. Expect it on PyPI soon, and a post here.

The lookup schema and 'borrowed' privilege

2009-12-22T17:15:00.008-07:00

In an earlier post, I discussed the need Rdbhost has for a sudo type function, where a lower privileged role can 'borrow' greater privilege for specific limited functionality.

What has been implemented (read on) can be used to that effect, but is not implemented quite that way. Instead, we created additional roles (with and without authentication) that are restricted to running predefined queries only. You can do most querying using a low privilege role (typically the Reader role), and then adopt one of these new roles to do predefined operations that require higher privilege. Because these roles are restricted to certain queries, they can be granted greater PostgreSQL privileges safely.

The two roles are Auth and Public, written like 'a0000000878' and 'p0000000878', incorporating the account id. Auth requires an authcode, and Public does not. They are both restricted, in that either one can only run queries from the lookup.queries table. The roles can be created from the /mbr/role_manager page, and when you create one, the lookup schema and the queries table are both created for you. Add records to them using your Super role; the Rdbadmin utility can be useful here.

Queries in the lookup.queries table are invoked just like free-form queries, except that a 'kw' parameter is provided in lieu of a 'q' parameter. The 'kw' parameter value has the tag name for the desired query. If there are any substitution tokens in the looked-up query, there must be a corresponding number of 'arg###' parameters.

There are now four roles defined for each account. They are Super, Reader, Auth, and Public. The first two can run arbitrary free-form queries against the database, in addition to the predefined lookup queries. Typically, the Reader role would have very restrictive permissions to protect the database, and the Super role would only be used by the account owner. The Auth and Super roles are authenticated, requiring an authcode to be submitted with the request. The Reader and Public roles are not authenticated.

An example of a lookup query, taken from www.Freshfaves.com, is:


tag: update

query: UPDATE faves f SET f.link = %s
   WHERE f.id = %s
     AND f.acctid IN (SELECT id FROM accounts WHERE key = %s)

The role used, Public, has privilege to UPDATE the faves table. But because the Public role can only execute queries from the lookup.queries table, the only updating the user can do is the query above, and that only changes a record if the key value matches. That is, the only records updateable are those for the account with the provided key. The table is safe from malicious or accidental changes to other users' accounts. The query above is invoked using a a javascript code sequence like the following (the example uses jQuery):

var dbUrl = 'http://www.rdbhost.com/db/p0000000878?callback=?';
$.getJSON(dbUrl,
      {kw:'update', arg000:link, arg001:id, arg002:key},
      function(d) {
        alert('account updated');
   };
);

The Freshfaves application just predefines all queries, but it could have defined the Reader role and done free-form queries with it.

var dbUrl = 'http://www.rdbhost.com/db/p0000000878?callback=?';
$.getJSON(dbUrl,
     {q:'SELECT * FROM faves WHERE id = %s', arg000:faveid},
     function(d) {
       // code here to handle data retrieved
   };
);

Between free-form queries on one unprivileged role, and predefined queries only on the other role, an application can manipulate databases as necessary without embedding any role authentication into the javascript.

Related Reading

Rdbhost Roles page

Rdbhost Lookup page

The FreshFaves website

FreshFaves source code .zip

Postgresql upgrade

2009-12-22T13:48:00.002-07:00

The Postgresql server behind Rdbhost has been upgraded to the latest 8.3 maintenance release, version 8.3.8.

Freshfaves: perishable bookmarks

2009-12-22T12:25:00.005-07:00

Fresh Faves

Freshfaves is an online bookmarking tool that features perishable bookmarks. Add pages to your bookmark list using a bookmarklet in your favorites/bookmarks list. The bookmarks are kept there for following later, and when a bookmark goes 30 days without being clicked, it gets dropped from the bookmarks page.

Hence, a clutter-free bookmark list. It is useful for keeping bookmarks that are of only temporary use, or are of uncertain usefulness.

Bookmarks of temporary value include shopping list type bookmarks, tracking a product option until you decide where to buy; once the purchase is done, the bookmarks lose their usefulness.

If you find a page is of uncertain value, you can bookmark it here; if you never find time to follow up, or if it proves on the first couple visits to not be worth keeping, it will go unvisited and drop off the list in time. If such a link does prove to be of value, we make it one-click easy to copy it to your accounts on other (more permanent) bookmarking sites. Of course, we also provide a delete function to remove it immediately, if you so choose.

There are no login accounts or passwords; the authentication information is in the bookmarklet itself. We do make it easy to email that bookmarklet to yourself (for safe-keeping) or a friend (for sharing).

Rdbhost

This service is implemented using Rdbhost, of course. I used the JSONP data format, as it allows making cross-site requests (in this case, from www.freshfaves.com to www.rdbhost.com). It does not permit POST mode requests, but for this low-security application, that is ok. I am working on an AJAX-Rdbhost toolkit to make cross-site requests straightforward in any mode, and that will be announced here in its time.

The source code is available in a zip file, here.

Sudo and SQL

2009-12-14T18:01:00.004-07:00

Rdbhost databases can be queried from Javascript applications. Aside from the cross-site-scripting safeties, there is one major design obstacle to hosting your javascript applications data here.

PostgreSQL provides the ability to setup multiple roles, but generally you do not want to create a new role for each web user. Most web users are anonymous, in fact, and could not be correlated with a particular role. Generally, for a web application, you would set up a small number of PostgreSQL roles, and then provide additional programming layers to authenticate users and allow each only appropriate operations on the database.

Unfortunately, for a Javascript application hosted on Rdbhost, you would have to embed the Postgres role password (authcode) in the javascript, in order to provide it to the server with each SQL request. Even if you did not distribute it with the javascript app, but retrieved it dynamically conditional on the user entering a passphrase, say, it would still be in the javascript temporarily. If the client were provided the authcode temporarily, for some legitimate purpose (say, to add themself to, or remove themself from, a members table), you could not prevent them from submitting that authcode with different SQL later, perhaps malicious SQL. If you were to provide them a role and authcode to remove their records from a table, you could not prevent them from removing others records as well.

Now, there are useful javascript apps that can be written to work safely under these constraints. For example a blog application could allow anonymous users to use a role that permits INSERT and SELECT on the comments table. The blogger himself or herself would use a super role that permits everything, but the anonymous web user would be able to add comments. That much can be controlled via PostgreSQL roles privileges. Many apps, though, need a more refined security model.

Unix and its kin have long had a tool called sudo. Sudo allows a user to run specific commands using the privileges of another user. The specific commands to be permitted must be listed, in advance, by a user with greater privilege.

Rdbhost needs a feature akin to sudo for use by javascript programmers. The programmer can configure the necessary queries that can be run, what role (privileges) they will run as, and who can be permitted to run them. Then the javascript app can use a low privilege role for most purposes, and then use the sudoish feature to do higher privilege operations that are predefined to operate safely. A javascript programmer can write a javascript application that is widely distributed and widely studied, and use a hosted shared database for application data, without exposing that database to malice.

Comments welcome.

Rdbhdb 0.9.1

2009-12-04T17:29:00.003-07:00

The DB API module, Rdbhdb, has been re-released. Only two changes in this version:

https (TLS) is used by default.
A bug causing incompatility with Python 2.4 was fixed. 2.4 does not have an 'any' builtin, so the module provisionally provides one.

Available here or from Pypi.

http://www.rdbhost.com/downloads/rdbhdb-0.9.1.zip
http://pypi.python.org/pypi/rdbhdb/0.9.1

More changes to Rdbadmin and Rdbhost

2009-11-23T22:07:00.007-07:00

A few recent improvements to Rdbadmin:

Main page shows tables and fields.
New section of left-hand menu shows schemas.
Menu options create and rename schemas.

Improvements to Rdbhost:

Login page (and login link from front page) is https.
Login cookie is 'secure' so will not be available to non-https pages.
Attempting to access another account while logged into yours will result in an error. You must log out of your account to access any other.

Improvements to Rdbadmin program

2009-11-18T19:34:00.006-07:00

Rdbhost sports its own admin utility, Rdbadmin, implemented in Javascript. It is useful to create and remove tables and views, adding and deleting data from tables, and performing queries.

Rdbadmin has been upgraded, with both new features and bug fixes.

New features include:

Table and view lists are sorted.
Tables and views can belong to schemas other than public. This will become more important with upcoming features.
New feature to set field defaults on tables.
HTML content is now escaped, so that it reads as the tags themselves, and the data inline formats cannot interfere with the display table structure.
In the select page, query data values are parameterized, so the data values cannot be misinterpreted by the SQL engine as statements.
The login and logout functionality has been removed. Logging in is done via the www.rdbhost.com front page (or the www.rdbhost.com login page), and logging out from the profile page. The admin script gets role and authcode from the rdhost member cookie, and just bounces the user to the login page if member cookie is missing. The 'guest_account' functionality is still present, where the 'r'-role can be provided on the url.

As always, the admin program is linked from the profile page. As always, your questions and criticisms are welcome.

Guest usage of Rdbhost accounts

2009-11-14T21:28:00.005-07:00

There is a handy way to provide other people read-only access to your Rdbhost database.

If you have enabled the 'r'-role, and given it reasonable (ie: only SELECT) permissions on the tables, you can allow other people to access your database using that role.

Just use the url for the rdbadmin page, and append the 'r'-role name to the url, with a '?'.

For example, our stackoverflow database is account 'db0000000767', and the 'r' role has been enabled and GRANTed appropriate privileges. The guest usage url is thus:
http://www.rdbhost.com/rdbadmin/main.html?r0000000767 .

Try it here.

There is no way to provide an authcode with the rolename, so it will not work for 's'-roles.

YASOP: Yet another Stackoverflow post

2009-11-09T19:16:00.003-07:00

The latest data dump from Stackoverflow has been placed in our stackoverflow account

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

I was just reading some old stuff on meta.stackoverflow.com about hosting a publicly query-able copy of the dump, with reporting or graphing options.

This account (at the above url) provides the data, with the flexible querying of SQL, Google's Appengine provides some charting tools, and our Rdbhdb module provides a DB API type interface for accessing the data from GAE.

Have fun.

Rdbhost.com

Google Appengine

Rdbhdb DB API module

Rdbhdb v 0.9

2009-10-26T10:19:00.011-06:00

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

2009-10-26T10:16:00.003-06:00

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

2009-10-23T17:15:00.013-06:00

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

2009-09-25T12:54:00.011-06:00

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

2009-09-07T12:51:00.000-06:00

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

2009-09-02T11:21:00.000-06:00

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

2009-08-28T23:44:00.000-06:00

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

2009-08-10T22:20:00.000-06:00

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

2009-08-03T10:31:00.005-06:00

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.

Parts is parts

2009-07-22T12:00:00.000-06:00

Rdbhost is a thin wrapper around the Postgresql database engine (8.3.7). The web server receives the request, authenticates the requester, and provides the query to the database engine. The results of the query are then converted to XML or JSON and sent to the requester.

Query results are formatted as XML or JSON, which are generated using the excellent Python libraries lxml and simplejson. The lxml library is a Python binding for libxml2 "the Gnome XML C parser and toolkit".

http://www.postgresql.org/ PostgreSQL

http://xmlsoft.org/ libxml2

http://code.google.com/p/simplejson/ SimpleJson

The post title is from a classic American television ad for automotive parts, wherein the negative credibility speaker suggests that all parts are equal.

A modest venture

2009-07-21T08:49:00.000-06:00

www.Rdbhost.Com provides a service that I have not seen available, in any similar form.

If you need an SQL database, and your web host does not provide or permit the installation of an SQL database, we can provide you one. As a web service, rdbhost databases are accessed via http requests. If you are coding in Python, we even have a DB API module for rdbhost databases; you can code your database usage without worrying about http or other networking issues.

Our pricing model is pay-as-you-go, so you only pay for actual bandwidth and diskspace used. To get you started, very low usage is free. To set up an account, we only ask of you a valid email address.

Set up an account for free, and dabble with it; see first hand how it works. If you agree with us that a true ACID-compliant SQL relational database is worth the modest additional setup effort, then go with it. When your account usage exceeds our free limits, we will sell you as much or as little additional bandwidth and space as you need.

We do support TLS encrypted https connections.

We provide tools for manipulating your database:

Rdbadmin - a database manager similar to PhpMinAdmin
A database dump tool to dump the entire database (as SQL) to your browser for backup purposes
A plain-jane html form for submitting queries to the database, and getting XML/JSON responses

A few starter links:

Front page

Questions and Answers

HowItWorks

Rdbhost

Named Query Parameters

Using your own SSL/TLS Certificate

Why Use Certificates

User Experience

The Rdbhost Way

Limitations

Wizardry

Home, home on the Ra..

Not one, but *two* new videos

Host your database-backed site on GitHub, for free!

Minor gains

SQL Logging, how mundane

A post about Authentication

More Backup Options

Update for RdbAdmin

Welcome to the new world. HTTP Databases and JSON Storage. Quirkey

Upgrade

The four roles on each Rdbhost account

Django?

Page hosting at Rdbhost

Query Timing

How to Train Your Database Server

Remote Database Access from the Browser Made Easy

How does Rdbhost.com compare to other services?

Thanks to Ben Nadel

jquery.rdbhost.com is out there.

New RdbAdmin Features

RdbAdmin, free at last!

Improvements to Bulk Transfer

JSOND

Rdb.js

PostgreSQL upgrade

YASOP

Screencast is now shorter

CSS skins on RdbAdmin

TechZing

Rdbadmin now supports file fields

Binary data and file fields

Technorati Claim Code

YASOP: Yet another Stackoverflow Post

Rdbadmin looks much better

Rdbhost module is in SQLObject trunk

Facebook

PL/pgSQL on Rdbhost

SO March

Rdbhost on twitter

RdbHost on ProgrammableWeb blog

SQLAlchemy and the Stackoverflow Data Dump

Bulk Data Transfer to/from Rdbhost

Rdbhost API on ProgrammableWeb

SQLAlchemy and Rdbhost

Rdbhdb 0.9.2

Server update

The lookup schema and 'borrowed' privilege

Postgresql upgrade

Freshfaves: perishable bookmarks

Sudo and SQL

Rdbhdb 0.9.1

More changes to Rdbadmin and Rdbhost

Improvements to Rdbadmin program

Guest usage of Rdbhost accounts

YASOP: Yet another Stackoverflow post

Rdbhdb v 0.9

SSL Downtime

User side testing and Twill

Multiple Recordsets

Stackoverflow Data

UserTesting

JsonP

Stackoverflow Data

Binary Content

Parts is parts

A modest venture

Not one, but two new videos