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.

The lookup schema and 'borrowed' privilege

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

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

Freshfaves: perishable bookmarks

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.