Zoho CloudSQL Architecture

Developers can use Zoho CloudSQL to extend and interact with data stored in Zoho services. Zoho CloudSQL can be used through the following connectivity options:

• JDBC/ODBC Driver: Connect to the Zoho services for SQL querying, in the same way as they access a traditional, on-premise database server through JDBC or ODBC drivers.
• HTTP Web API: Connect to the Zoho services for SQL querying through the use of HTTP based Web APIs, sending their requests as SQL statements.

Zoho CloudSQL requests, which are SQL statements executed from within the application, are sent as HTTP requests to the Zoho CloudSQL middleware for execution. The Zoho CloudSQL middleware layer receives, processes and answers the Zoho CloudSQL requests from third party applications.

Zoho CloudSQL middleware is made of three core components: SQL Interpreter, Federation Logic and Entity Mapper.

SQL Interpreter

Zoho CloudSQL supports multiple database dialects: ANSI, Oracle, Microsoft SQL Server, IBM DB2, MySQL, PostgreSQL and Informix dialects. The main purpose of the SQL Interpreter component is to translate the SQL statements that are executed by the third party application into a neutral dialect that can be understood by the individual Zoho Services. The SQL Interpreter also generates an internal SQL model that is passed into the Federation layer.

Federation Layer

The federation layer understands the SQL Model passed by the SQL interpreter and handles the following two important functions:

• Service specific Query delegation: From the SQL model it will identify the Zoho services to which the query has to be delegated, and delegate the queries to the corresponding Zoho Service for execution.
• Result Aggregation: Once the query is executed across the Zoho Services, it will aggregate the results and send it back to the application as a unified result.

The federation layer actually enables a query to span across multiple Zoho services to fetch data in an aggregated manner, thus virtualizing the different Zoho Services so that they look like a single service from a query execution perspective.

Entity Mapper

To construct an SQL Statement a developer needs to identify the entities like database, table and columns available in a service for query execution. As each Zoho Service collects and mines information about various business entities, the exact correlation of what is a database or table or columns in the context of a Zoho Service (also known as the service metadata) is defined by the service. Developers have to use this metadata for constructing their SQL queries. (Refer to the corresponding CloudSQL documentation of each Zoho Service to know this mapping. Ex: Zoho Reports Entity Mapping)

Zoho Services:

Each of the specific Zoho Service like Zoho CRM, Zoho Creator, Zoho Reports etc., make up the last layer in the Zoho CloudSQL architecture. The services are the ones that actually collect, store and mine the respective business data consumed by Zoho users and developers.

The services, upon receiving the SQL Model from the Zoho CloudSQL Middleware, execute the query against their datastore and pass the results back to the CloudSQL middleware. The services also take care of authorizing each query execution. This verifies whether the user who is executing the query has the actual permission to access or manipulate the data on which the query is going to be executed.

External Web Services:

Zoho CloudSQL enables developers to query the data in the Zoho services using the standard SQL language. By extending the middleware layer of Zoho CloudSQL, this technology can also be used to query any other web services across the web. Right now we're focused on implementing Zoho CloudSQL across all the relevant Zoho products, but please contact us if you're interested in enabling SQL querying for your own web service.

Next: Developer Info