How it Works

Beginning

To get started, you create an account via the familiar register, receive emailed password, _login routine.

RdbHost creates a database using the PostgreSQL engine exclusively for your use.

  • This database is empty and ready for you to create tables.
  • One database role is created for you, with all the privileges necessary to create tables and indexes. Three other roles can be added, with differing characteristics. See Roles page for more details.

Login

When you _login, your profile page will show you the role names and authcode. With these roles, you can start querying the database through our rdbhdb DB API module, JavaScript page requests, or through the RdbAdmin javascript admin program.

Roles

The brand new database has one role, but as many as three more can be created, each named using the account ID number:

  • The Super role s<id> is privileged to create databases and indexes. It owns the items created and can perform any operation on those items.
  • The Reader role r<id> has no privileges, but can have privileges GRANTed and REVOKEd by the s<id> role via SQL statements.
  • You can do all work with the s<id> role and leave the r<id> role disabled, or you can give ther<id> limited additional privileges, such as INSERT or SELECT. If you then use ther<id> account for general purposes, the data are protected by database privilege controls. You can even safely embed that r<id> role id in a publicly released JavaScript application or library.
  • The Preath p<id> and Auth a<id> roles differ from the above two, in that these can only execute queries that are predefined in a lookup table. See more details on the lookup.preauth_queries table.
  • Each role (except for Super) can be created, enabled, and disabled from the Role Manager Page.

Protocol

The easy way to access the database from Python is to use the DB API module. However, you can use the protocol described here and the URLLib or HTTPLib module directly. If you use JavaScript, you need to know the protocol.

Querying the database via the Webservice interface involves making an http request to www.rdbhost.com with the SQL and role information included as http parameters.

  • The action url includes the role name: http://www.rdbhost.com/db/s<id>
  • The authentication code, if necessary, is passed as the parameter authcode. The s<id> role must have an authcode, and the r<id> role must not.
  • The query, in SQL, is passed as the parameter q. It may include positional interpolation tokens '%s', named interpolation tokens '%(name)', cookie values '%{cookiename}', and cgi-variables %[cgi-var].
  • As an alternative to passing the query itself, you can provide a kw (keyword) parameter; the server will look up the query in the lookup.preauth_queriestable, and execute the query thus retrieved. The query must have been inserted into the lookup.queries table beforehand. The stored query may include substitution tokens as above.
  • If you would like RdbHost to safely interpolate parameters into the query string, they can be passed as numbered positional arguments, or as named arguments.
    Positional arguments are named arg001, arg002, up to arg099. They must be sequentially numbered, and the count must match the number of interpolation tokens. Type parameters, named argtype000, argtype001, can optionally be provided to indicate the type of the corresponding argument.
    Named arguments are sent as parameters named like 'arg:<name>', and replace substitution tokens named like '%(name)', with parenthesis. The corresponding type arguments are named 'argtype:<name>'.
  • The results of the query are returned as an XML or JSON document. The default is concise XML, so if you would like another alternative, use the format parameter to indicate your preference. See Result Formats page.
  • If the query might need more than 8 seconds to complete, it should be run in 'deferred' mode, using the parameter 'mode=deferred'. The query request will return a results page immediately, with status content. The query will be executed later, in its turn. If you provide a postback parameter with a url on your site, RdbHost will request that url with a POST query, providing the results page as a request parameter, 'json' or 'xml'.
  • See the Protocol page for a list of valid parameters.

Results

Each request will retrieve a page, in either XML or JSON. If XML, the top-level element will be a container called 'xml'. If JSON, the top level element is an anonymous hash, named by your code. In either case, there will always be a second-level element named 'status', indicating the success or failure of the request. See the Result Formats and the Python and JavaScript language-specific pages for details.

Unicode

All query strings, as well as substitution parameters, must be encoded in Unicode 'UTF8'. Data not decodable as 'UTF8' will raise an error. Binary data should generally be Base64-encoded before inserting, to avoid any possible inclusion of undecodable characters. Note: 7-bit ascii is a subset of 'UTF8', so SQL represented as 7-bit ascii will work fine.

Transactions

A 'query' can include multiple statements. The sequence of statements submitted as the q parameter is wrapped in a transaction. Any errors in the sequence result in a rollback, with no change to the database.

Examples

Precisely how to programmatically make a web-service query depends on the language and platform, but any legitimate web programming language will include a way in which to make http requests and a way to parse XML. We have examples for Python and JavaScript:

  • The How In Python page describes briefly the use of the rdbhdb DB API module.
  • The How in Python No DBI page describes how to access this webservice using the standard library modules.
  • AJAX programmers can find JavaScript examples here.