EBQuery

Table of Contents

Introduction - Enterprise Backend as a Service

Requirements

Getting started

Using EBQuery

Features

Introduction - Enterprise Backend as a Service

EBQuery is an Enterprise Backend as a Service along lines of Parse but for your own databases. It's like Heroku Dataclips but for non-cloud, on-premises databases.

EBQuery is a JavaEE application that's securely hosted on your servers. No data ever leaves your company!

Requirements

Getting started

Installation

EBQuery can be built and installed as a standard web application archive (WAR). Any JavaEE 7 or JavaEE 6 servlet container should work.

Configuration

Database

If you're using a dedicated database for EBQuery (as you probably should for production) you first need to run a DDL script that creates the necessary tables. DDL scripts for the following databases are included in src/main/resources/sql in the EBQuery directory:

• H2
• MySQL
• Oracle Database
• PostgreSQL

Then you need to set the system property DATABASE_URL according to your database connection settings.

How you set this system property depends on your environment. A common way for doing so is using the -D command line option for Java, e.g.:

java -DDATABASE_URL=postgres://username:password@localhost:5432/EBQuery ...

The format for the URL is

PROTOCOL://USERNAME:PASSWORD@HOST:PORT/DATABASE_NAME

A PostgreSQL DATABASE_URL for example would look like this:

DATABASE_URL=postgres://username:password@localhost:5432/EBQuery

The following protocols / RDBMS are available. Please note that only H2, MySQL and PostgreSQL drivers are included with EBQuery. If you want to use one of the other RDBMS you have to add the appropriate JDBC driver to the shared libraries directory of your Servlet container / application server:

• as400 (IBM DB2 AS400)
• db2 (IBM DB2)
• derby (Apache Derby)
• edbc (Ingres Database)
• firebirdsql (Firebird)
• h2 (H2)
• hsqldb (HSQLDB)
• mysql (MySQL)
• oracle (Oracle Database)
• postgres (PostgreSQL)
• sapdb (SAP DB)
• sqlserver (Microsoft SQL Server)
• sybase (Sybase)

Please note: Only H2, MySQL and PostgreSQL drivers are included with EBQuery. If you want to use one of the other RDBMS you have to add the appropriate JDBC driver to the shared libraries directory of your Servlet container / application server.

Security

In order to set your basic security preferences for EBQuery the environment variable spring.profiles.active has to be set to either 'local' or 'public', i.e.:

spring.profiles.active=local

or

spring.profiles.active=public

'local' will disable HTTP basic authentication whereas 'public' will enable it. The default password is 'pP2XLieKb6'. This password can (and should be) changed in spring-security-public.xml in src/main/webapp in the EBQuery directory. Please restart the server after having changed the password. Please note: If you turn HTTP basic authentication on your API calls will have to send an HTTP basic authentication header with the appropriate username / password, too.

How you set the spring.profiles.active system property depends on your environment. A common way for doing so is using the -D command line option for Java, e.g.:

java -Dspring.profiles.active=local ...

Using EBQuery

• database connections
• queries

Database Connections

After having entered a valid database connection EBQuery will automatically create _SELECT _ FROM* queries for each table and view in your database. If your database uses foreign keys EBQuery will extend those queries to include links to referenced database entity resources.

Queries

Alongside these options EBQuery displays REST API links for this query above and below the result set preview.

Arguments and variable interpolation

SELECT * FROM table WHERE field = ?
SELECT * FROM table WHERE field = ? OR another_field = ?

These arguments can then be supplied as additional URL parameters to your API calls.

Navigating entities referenced by foreign keys

REST API

EBQuery turns each SQL query into an easily accessible REST API endpoint that returns data in a variety of formats.

Parameters and formats

• /api/v1/resultSetForQuery/6.json (JSON)
• /api/v1/resultSetForQuery/6.xml (XML)
• /api/v1/resultSetForQuery/6.csv (CSV)
• /api/v1/resultSetForQuery/vertical/false/6.html (HTML list)
• /api/v1/resultSetForQuery/vertical/true/6.html (styled HTML list)
• /api/v1/resultSetForQuery/horizontal/false/6.html (HTML table)
• /api/v1/resultSetForQuery/horizontal/true/6.html (styled HTML table)

EBQuery also allows you to add arguments to an API call, which will be used for interpolating variables in the SQL query. The arguments are appended to the URL after the query ID. Multiple arguments are comma-separated. Moreover, you can also limit the size of the result set by adding a size parameter.

Again, these are a few example URLs:

• /api/v1/resultSetForQuery/6/45,SomeValue.json (JSON with interpolated variables)
• /api/v1/resultSetForQuery/6/size/3.json (JSON with number of results limited to 3)
• /api/v1/resultSetForQuery/6/45,SomeValue/size/3.json (JSON with interpolated variables and number of results limited to 3)

Features