The four roles on each Rdbhost account

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

1. 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.
2. 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.
3. 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.
4. 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

• The roles
• Training
• Lookup.queries

Django?

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

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

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

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.  

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.

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
  

PostgreSQL upgrade

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

YASOP

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

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

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

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

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

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

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

YASOP: Yet another Stackoverflow Post

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

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

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

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

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

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

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

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

See:

5 new APIs

SQLAlchemy and the Stackoverflow Data Dump

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

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

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

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

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

The server software was updated yesterday.

The changes are:

1. 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.
2. 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.