Database Connector
From OpenLaszlo
Contents
|
Project Information
- Project page
- Project owner: Max Carlson
- Mailing list: laszlo-dev@openlaszlo.org
Mission Statement
The Laszlo Database Connector is designed to make it as easy as possible for Laszlo developers to use record-based datasets, in particular SQL databases. The goal is to have a common, open protocol for communicating incremental changes to and from record-based data sources.
This spec is inspired by the ActiveRecord pattern, in particular the Ruby on Rails implementation. However, since the communication happens entirely in XML, there should be no dependency in the Database Connector that binds it to any particular technology or server on the back end.
Requirements
(In progress:)
- The project must specify a protocal for querying, creating, reading and updating datasets but must not be dependent on any specific server-side technology.
- The project must be secure, by default, against SQL injection attacks. (Preferrably, it will not use SQL at all, except behind the scenes on the server.)
- The project must support a reasonable security model.
Projects
There are two projects underway that aim to meet the requirements implied by the mission statement.
- Laszlo on Rails will integrate Ruby on Rails on the server with OpenLaszlo on the client. This project has already made significant progress in that direction.
- The PHP Database Connector project is a PHP server library that will implement the requirements implied in the mission statement. This project has not actually been undertaken yet.
It is hoped that these two projects can share specifications and client-side work. The work that is common to these two projects is the subject of the Database Connector umbrella project.
In addition, RIFE/Laszlo is a ready-to-run source distribution and file structure that allows you to get started with a new OpenLaszlo application with RIFE in less then 2 minutes.
Specifications
The Database Connector could, more accurately, be described as an object broker. In response to a query for data, it returns a graph of value objects represented in XML datasets that conform to a common schema.
As data is changed on the client, the delta's will be stored and sent to the server when appropriate. See the Talk section of this page for some links to existing approaches to this problem.
If one or more errors occur when committing to the server, the client will roll back changes to the dataset to the last successful commit, thereby keeping both data models in sync.
Controller-URL mapping
Server-side controllers are accessed using a consistent URL mapping that ensures compatibility across implementations.
Controllers
Each controller is exposed at a separate path, e.g.
/contact
/address
/category
Calling Methods
Controller methods are called by listing them after the name of a controller, e.g.
/contact/list
Calls the list method in the contact controller.
Default Method Name
If a method name not provided, a default method called 'index' is called on the controller, e.g.
/contact
/contact/index
Both call the index method in /contact with no record id.
Methods are invoked my appending them to the path of a controller, e.g.:
/contact/10,20
Calls the index method in the contact controller with record ids 10 and 20.
Method arguments
Additional arguments can be passed to a controller on the query string, e.g.:
/contact/mymethod?foo=bar
Calls mymethod method on the contact controller, passing it the argument foo with the value bar
Some scaffolding methods allow ordering to be specified by adding the optional order attribute:
/contact/read?order=firstlastdesc
Reads all records from the contact controller, ordered by 'firstlastdesc'
Record IDs
Record IDs are always specified at the end of the path, e.g.
/contact/delete/52
Calls the delete method in the contact controller with record id 52.
/contact/delete/list/10,1,15
Calls the list method in the contact controller with record ids 10, 1 and 5.
ToDo: there is much more to be considered in terms of querying syntax. XPath seems like a good place to look to extend REST.
Some issues with this: Rails uses primary keys (id's) in the URI. So, for example this might actually be myapp.com/contact/1/address/category. The ugly thing about this is that an artificial single-field identifier breaks database normalization rules. For example, an enrollment in a class by a student would be identified by student_id + course_id + course_start_date. Adding a student enrollment id as a single field is unnecessary. OTOH, having the rules of data storage in a relational database dictating the structure of a REST API is inappropriate. So I would say that the contact *object* needs an identifier that can be used for purposes of the URI and that may, or may not, have meaning to the server in terms of mapping the object to its data.
What matters is that if I refer to /customer/[some key value], whether that key value is a single integer, a UUID or a concatenated string forming a composite key, the server can do the necessary update and *the Laszlo app need not have any inkling* of the actual database structure.
Return values
All methods must return valid XML.
Exceptions
Each exception must include an element named after the method called, with any arguments passed in. Exceptions are returned in the following format:
<exception> <methodname argname="value" argname="value".../> </exception>
Exceptions can optionally include text and/or subnode values:
<exception>
<methodname argname="value" argname="value"...>
User-visible message
</methodname>
</exception>
<exception>
<methodname argname="value" argname="value"...>
User-visible message
<contact id="2" updated_on="19239823' name="value".../>
<contact id="1" updated_on="19239825' name="value".../>
...
</methodname>
</exception>
Method call batching
Several method calls can be combined into single batch message. This is used for doing a series of operations atomically. Batches are sent as XML via HTTP POST to the batch url:
/controllername/batch
Each method call is written as XML in the following format:
<methodname argname="value" argname="value".../>
Method calls are grouped under elements that specify which controller should be used:
<controllername>
<methodname argname="value" argname="value".../>
...
</controllername>
This XML is sent via HTTP POST to the server-side controller. Each method is called in order, and responses (or exceptions) are returned in order. The server then returns XML that echos the call format, e.g.
<controllername>
<methodname>
User-visible message
<result name="value" name="value".../>
</methodname/>
<methodname>
<exception>
<methodname argname="value" argname="value"...>
User-visible message
<contact id="2" updated_on="19239823' name="value".../>
<contact id="1" updated_on="19239825' name="value".../>
...
</exception/>
</methodname>
</controllername>
Scaffolding
Each controller provides scaffolding methods to create, read, update and delete records in the underlying model. Each method provides two forms - a url for individual operations, and an XML tag for batch operations
Reading Records
Reads zero or more records. If no id is specified, all records are retrieved.
URL syntax
/controllername/read
Tag syntax
<read .../>
Returns
The response includes record elements with any related records listed as subnodes. Each record element is named after its controller and includes all fields as attributes. For example:
/contact/read
Calls the read method in the contact controller, which retrieves all contacts by default:
<records controller="contact" count="100" totalcount="10000" time="912991238">
<contact first_name="Max" last_name="Foo" id="1">
<address street="123 main st." city="San francisco" state="ca" zip="94114" id="1" updated_on="8120938"/>
<address street="123 foo st." city="San francisco" state="ca" zip="94114" id="2" updated_on="28713098123"/>
<company name="Foo Industries" id="1" updated_on="12739821">
<address street="123 Across 110th st." city="San francisco" state="ca" zip="94114" id="3" updated_on="12098309182"/>
</company>
<category name="Friend" id="2" updated_on="128829339"/>
<category name="Personal" id="5" updated_on="92813882103"/>
<category name="Work" id="17" updated_on="92139823182"/>
</contact>
...
</records>
Record elements must include id and updated_on fields:
| Name | Description |
|---|---|
| id | id of the record |
| updated_on | server epoch time when the record was last updated |
Each controller returns certain attributes in the top-level <records/> element:
| Name | Description |
|---|---|
| controller | name of the controller called |
| count | number of records retrieved |
| totalcount | total number of records in recordset |
| time | current server epoch time |
Exceptions
No records found:
<exception>
<read>
User-visible error message...
</read>
</exception>
Reading records by id
One or more ids can be specified, e.g.
/contact/read/1
Retrieves record id 1
/contact/read/1,3
Retrieves record ids 1 and 3
Exceptions
No records found:
<exception>
<read id="1,3...">
User-visible error message...
</read>
</exception>
Ordering records
Record ordering can be specified with the 'order' query string attribute, e.g.
/contact?order=firstlastdesc
returns contacts ordered by firstname, then lastname in descending order. The controller is responsible for determining how record ordering is determined.
Exceptions
Sorting by order failed:
<exception>
<read order="firstlastdesc">
User-visible error message
</read>
</exception>
Paging records
The page method allows ordered records to be retrieved by sort offset. If no order is specified, a default sort order is used.
URL syntax
/contact/page/1
Retrieves the first record in the default sort order
/contact/page/1,3?order=firstlastdesc
Retrieves the first and third record in sort order
/contact/page/1-10?order=firstlastdesc
Retrieves the first through the tenth records in sort order
/contact/page/-10?order=firstlastdesc
Retrieves the first through the tenth records in sort order
/contact/page/-10,14-17?order=firstlastdesc
Retrieves the first through the tenth and fourteenth through seventeenth records in sort order
/contact/page/11-?order=firstlastdesc
Retrieves the eleventh record onward in sort order
Tag syntax
<page id="-10,14-17" order="firstlastdesc"/>
Returns
XML in the same format as /read
Exceptions
Paging out of bounds:
<exception>
<page order="14-17">
User-visible error message
</page>
</exception>
Creating Records
Creates a new record.
Attributes consist of zero or more name/value pairs for the record fields to be created.
URL syntax
/controllername/create?name=value...
Tag syntax
<create name="value".../>
Returns
The id and the server epoch time the record was created, e.g.
<created id="1" created_on="128829339"/>
Exceptions
Record could not be created:
<exception>
<create name="value"...>
User-visible error message
</create>
</exception>
Updating Records
Updates an existing record by id.
Attributes consist of a record id and one or more name/value pairs for the record fields to be updated.
Includes the server-side epoch update time when the record was last sent to the client.
Throws an exception if the server record was updated since the last retrieval.
URL syntax
/controllername/update/id?updated_on=value&name=value...
Tag syntax
<update id="value" updated_on="value" name="value".../>
Returns
The id and server epoch time for the record updated, e.g.
<updated id="1" updated_on="128829339"/>
Exceptions
Record could not be updated:
<exception>
<update id="value" name="value"...>
User-visible error message
</update>
</exception>
Deleting records
Removes records from the controller.
Attributes consist of one or more record ids to be deleted
Includes the server-side epoch update time when the record was last sent to the client.
Throws an exception if the server record was updated since the last retrieval.
URL syntax
/controllername/delete/id?updated_on=value
Tag syntax
<delete id="id" updated_on="value"/>
<delete id="id,id,id,id..." updated_on="value,value,value,value¿"/>
Returns
The ids of the record(s) deleted
<deleted id="id">
<deleted id="id,id,id">
Exceptions
Record(s) could not be deleted:
<exception>
<delete id="id,id,id,id...">
User-visible error message
</delete>
</exception>
Committing Records
Attempts to commit all changes.
Throws an exception if the commit fails.
URL syntax
Since commits are meant to be atomic, only the tag syntax is specified.
Tag syntax
One or more commands can be included in a commit message, e.g.:
<commit>
<delete id="id" updated_on="value"/> <update id="value" updated_on="value" name="value".../> ...
</commit>
Returns
If successful, sends commit message to client
<committed/>
<committed>
<deleted id="id">
<updated id="1" created_on="128829339"/>
</committed>
If the commit fails, the server rolls back to the last commit - partial commits don't succeed.
Exceptions
Send failure/rollback message to client with new record values for conflict resolution
<exception>
<rollback>
<contact id="2" updated_on="19239823' name="value".../>
</rollback>
</exception>
Retreiving schemas
Each controller can be asked for a schema which includes a list of fields, field types and relationship declarations, e.g.
/schema
Returns the schemas for all controllers. By default, this simple format is returned:
<controller>
<contact>
<field name="first_name" type="varchar"/>
<field name="last_name" type="text"/>
<field name="email" type="int"/>
<field name="updated_on" type="datetime"/>
...
<metadata name="has_one">
<value>company</value>
<attribute name="class_name">companies</attribute>
</metadata>
<metadata name="has_many">
<value>address</value>
</metadata>
<metadata name="has_and_belongs_to_many">
<value>category</value>
</metadata>
</contact>
<address>
<field name="street" type="varchar"/>
<field name="city" type="text"/>
<field name="state" type="text"/>
<field name="zip" type="text"/>
...
<metadata name="belongs_to">
<value>contact</value>
</metadata>
<metadata name="belongs_to">
<value>company</value>
</metadata>
</address>
...
</controller>
A schema can be requested for a specific controller by appending a name to the /schema path, e.g
/schema/contact
Returns the schema for the contact controller:
<controller>
<contact>
<field name="first_name" type="varchar"/>
<field name="last_name" type="text"/>
<field name="email" type="int"/>
<field name="updated_on" type="datetime"/>
...
<metadata name="has_one">
<value>company</value>
<attribute name="class_name">companies</attribute>
</metadata>
<metadata name="has_many">
<value>address</value>
</metadata>
<metadata name="has_and_belongs_to_many">
<value>category</value>
</metadata>
</contact>
</controller>
Optional formatting
Schemas can be returned in a specific format by specifying the optional format attribute, e.g. format=DTD. The supported formats include DTD, XSD and RelaxNG.
Security
Method permissions
Method permissions are specified for each controller as a list of method names, or * for any method. If a method name is specified, the method may be called. Any attempt to call a method the user does not have access to results in an exception.
Per-record permissions
Each model may specify whether its records have per-user read or write permissions. If so, individual records will require a specific user or group id to be able to read or write them. Any attempt to read or write to a record the user does not have access for results in an exception.
Per-record permissions may have one of the following values: r (read) w (write)
Model permissions
Read and write operations on models may be restricted using permissions. Any attempt to read or write to a model field the user does not have access to results in an exception.
Each permission declaration specifies a list of field names, or * for all fields.
Model field permission may have one of the following values:
- (read/write)
r (read) w (write)
Groups
Each group represents a collection of method and model permissions.
Users
Each group represents a collection of method and model permissions.
Each user may be members of one or more groups.
If part of a group, they inherit the group's permissions. User permissions override group permissions.
Callbacks
After much dicussion, callbacks seem like too much rope - it's too easy to mess with data integrity. Given that this spec is about keeping data consistent on the server and the client, we'll punt on this for now.
Protecting against SQL injection attacks
Field values must be wrapped in quotes if they are string values. All Strings must be properly SQL escaped.
