Architecture

restSQL may be deployed in two modes: a Web Service and a Java library. The Web Service is a standard JEE web application. This deployment is also known as WAR (Web application ARchive) mode. The following diagram depicts an application using the HTTP interface:

The other deployment mode is known as the JAR (Java ARchive) mode. The following diagram depicts an application using the Java interface:

The following diagram depicts the layers of the software stack:

Requirements

• Platform Independent - Pure Java solution
• Java Runtime Environment (JRE) - Tested with 1.6
• SQL Database - Tested with MySQL 5.5 and PostgreSQL 9.1; others are planned e.g. Oracle
• JEE Container for Web Service deployment mode - Tested with Apache Tomcat 6/7, JBoss EWP 5.1 and WebLogic Server 10.3.5 and 12.1.1
• Java Archive (JAR) Tool for deployment - Tested with 1.6
• Web Browser for SDK - Tested with IE 7, Firefox 4, Chrome 20

Extensibility

The framework is very extensible. The core API is a set of interfaces, exceptions and utilities. The implementation classes implement the core interfaces. Implementations are loaded through a factory utility. The factory is configured with the restSQL properties file. You can provide custom implementations and instruct the factory to use them by registering them in the restSQL properties file.

Database Interoperability

Currently MySQL and PostgreSQL are supported out-of-the-box by restSQL. The most common customization is expected to be for other databases. This will likely require development effort, however, this will not be a full-on project. restSQL has provided default implementations that may be extended using the template method pattern or whose core behavior may be overriden if that is more appropriate. The factory utility provides you the opportunity to add database support without changing restSQL itself. (Of course you are most encouraged to contribute your additions to the project!). Additionally, there is an extensive test harness that will be used to ensure compliance.

In order for restSQL work its magic, it needs to introspect the data dictionary and observe database-specific SQL rules. The following differences in database engines affect restSQL:

• Information schema is implemented differently, even if it follows the the ANSI/ISO 2003 information schema standard. For example, MySQL equates the schema concept to a database and PostgreSQL equates the catalog concept to a database.
• Table identification varies. For example, MySQL uses the form database.table while PostgreSQL uses database.schema.table.
• JDBC driver implementation varies. For example, PostgreSQL will return the column label on the standard ResultSetMetaData.getColumnName(). A driver-specific call must be used to get the actual column name.
• Non-core SQL varies. For example, pagination in MySQL and PostgreSQL uses the "LIMIT n OFFSET n" syntax whereas SQL Server uses "TOP n" and Oracle "ROWNUM < n".

Based on the differences seen between MySQL and PostgreSQL, a new database will likely need a new SqlResourceMetaData implementation. The AbstractSqlResourceMetaData may be extended instead of starting from scratch. It uses a template method design pattern for assisting in limited extensions. Additionally, a new SqlBuilder implementation may be necessary, for example in adding support for pagination. The default SqlBuilderImpl may be extended rather than starting from scratch.

Security

restSQL access may be encrypted, authenticated and authorized. restSQL provides resource authorization capability, ensuring users only access the SQL Resources and methods appropriate to their assigned role(s). restSQL provides a standard, maintainable role-based access approach to authorization. Role to SQL Resource and request type assignments (privileges) are provided via a simple configuration file.

In restSQL WAR mode, the JEE container provides encryption and authentication, as well as user to role assignments. The confidentiality of data and credentials in transport is defined in the restSQL Deployment Descriptor (web.xml). The transport can be coerced to HTTPS (HTTP over SSL/TLS). The credential can be encoded in HTTP Basic or Digest schemes. The container-provided realm is also defined in the Deployment Descriptor. The realm could be file-, RDBMS- or LDAP-based, providing both user credentials and mappings to roles. If a request passes realm authentication, the restSQL HTTP Service authorizes each SQL Resource request, forbidding those that are unauthorized.

In restSQL JAR mode, the app is responsible for authentication and user to role mapping and calls an Authorizer prior to executing SQL Resource requests.

Server-side input validation (data types, values, etc.) may be provided by application-provided, Java-based Triggers.