Architecture briefing
Powered by H2
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 answer the users' queries 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, 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.