Remote Database Access from the Browser Made Easy

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?

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

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.

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

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:

1. Access the host server via the new Rdb.js module, instead of $.ajax and custom code.
2. Make the application portable, so that it can be loaded from other servers under other domain names.
3. 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.
4. Move all the javascript out of the html host page, and into javascript files.
5. All javascript is to conform to jslint expectations.
6. 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!

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

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.