Boilerplate

Rdbhost is a tool for getting simple web apps going quickly.

In support of that position, we now have boilerplate bundles; these are downloadable zip files that can be unzipped to your harddrive, and immediately function as simple applications.  Your account information is already embedded in each, so it will just run, and access your Rdbhost account as necessary.

The wizard collects from you the necessary information to create the boilerplate bundle.  After the wizard completes, you can download the relevant bundles from the boilerplate page; each includes a README.txt with starter instructions.


For now, there are as many as three boilerplate bundles, depending on your wizard selections; one for the Python Rdbhdb module, one for Python's SQLObject, and one for a JavaScript web app.  There will be more, in time, including options for html boilerplate, and these (especially the JavaScript bundle) will be enhanced.

Try them out, let me know if they don't behave as expected.

David Keeney
dkeeney@rdbhost.com

Not one, but *two* new videos

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

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

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.

How to Train Your Database Server

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:

1. Create an account, with its own database, on www.Rdbhost.com
2. Create necessary roles from role_manager page
3. Enable training for your workstation from training page
4. Configure your hosts file to include necessary local and Rdhost server entries
5. Run a local server; a tiny Python 8-liner will do
6. Develop your application
7. Develop your application (it's in here twice, because it's big)
8. Develop a test suite (optional)
9. Clear the preauth_queries table on the server; this removes development cruft
10. Run your test suite, or just exercise all SQL-using features of the app
11. Disable training for your workstation, from training page
12. Test application, either by test suite or manually
13. 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:

1. Training Page
2. PostgreSQL Roles on Rdbhost
3. 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

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.  

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

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.

JSOND

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

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