...
Powered by H2
All the implementations of the SQL language have their own dialects: the syntax and the grammatic can vary among vendors (Oracle, MSSQL, PostgreSQL, MySQL, H2). This app is built on top of the H2 engine. Therefore it supports the H2 dialect.
There are two databases on Jira. The physical (native) database storing the Jira data and the SQL+JQL database: an H2 database instance running on the Jira memory. It is a virtual database because it stores no data. SQL+JQL is powered by the H2 database engine. Therefore SQL+JQL are essentially standard SQL supporting the H2 dialect and all the queries are resolved by the H” SQL engine.
Empty tables
The database is created in the Jira memory with a very small footprint (~3 Mb). All the tables are created when the app is enabled and destroyed when the app is disabled.
All the tables are empty and they are populated in real-time to resolve answer the users' queries . The app uses Atlassian's by fetching the data from Atlassian’s public Java APIs for Jira Core, Software, and Service Manager.
Each user’s query gets its own copy of the table that is populated in real-time. Once the query ends, the table is destroyed.
User’s context
The H2 database instance listens to the same HTTP port as Jira to achieve this.This brings all the robustness and functionality of the H2 engine to organizations. You can write really complex SQL queries that are resolved by the H2 engine and use a lot of H2 functions to work with strings, dates, etc, therefore no extra TCP/IP port has to be opened in the server. In other words, the app is SQL running over the HTTP protocol.
The database engine always checks the HTTP user’s session (AKA the current user or the logged-in Jira user) and populates the tables only with authorized data according to the user’s permissions in Jira.
All the SQL queries run in the context of the calling user (logged-in Jira user) regardless they are executed.
User authentication and data access authorization
Since that the SQL engine is Jira user’s session aware, SQL+JQL reuses the Jira user base. Any logged-in Jira user is automatically logged-in SQL+JQL too. No extra user management is required in Jira. SQL+JQL under authentication relies on Jira user authentication.
SQL+JQL Data access authorization is based on Jira permissions. As opposed to other databases where permissions are at table level, SQL+JQL does not care about table permissions because they are empty. SQL+JQL cares about record permissions instead, populating the tables with authorized data only in real-time.