Every database vendor like Oracle, MSSQL, PostgreSQL, MySQL, and H2,… speaks the same principal language SQL with a lot of hues specific to each implementation forming a dialect. Like all of them, SQL Cloud has its own dialect.
Furthermore, the SQL Cloud engine has some other major differences compared to the rest about how it works, known as constraints.
You must know the dialect and the constraints in order to write queries in SQLC successfully. Otherwise, you might fall frustrated and wrongly conclude that the app does not work properly due to your misunderstanding, even if you are the more experienced user in SQL.
SQL Cloud - Dialect
This section is not intended to be a course in the SQL language. Hence, the focus will be put on the minimal set of differences that you must know in order to write queries properly.
Strings vs aliases
Like in the rest of the database implementations a string is enclosed between single quotes.
On the other hand aliases of column and table names must be surrounded by accent characters.
SELECT 'This is a string' as `Column Alias` FROM DUAL `Table alias`
Show tables and columns
Run
show tables
to list all the tables available in the database model
And
show columns from <table>
to display the column and their metadata of a given table:
Table, column and function names are case sensitive
In example:
select * from PROJECT
will work.
Whereas
select * from project
will not.
No schema names
Many database vendors group their tables into schemas (i.e.: PUBLIC). SQL Cloud does not support them.
We use the following convention for the table names:
No prefix: Jira Work
SF_ prefix: Jira Software
SM_ prefix: Jira Service Manager
Aggregation conditions do not support aliases
Please pay attention to the having condition: count(*) > 2
SELECT `i`.`KEY`as `Issue`, count(*) as `Num. comments` FROM ISSUE `i` LEFT JOIN ISSUECOMMENT `ic` ON `ic`.`ISSUEID` = `i`.`ID` WHERE `i`.`JQL` = 'PROJECT = TALH' GROUP BY `i`.`KEY` HAVING count(*) > 2
Works!
In this query the condition has been modified to uses column aliases: `Num. comments` > 2
SELECT `i`.`KEY`as `Issue`, count(*) as `Num. comments` FROM ISSUE `i` LEFT JOIN ISSUECOMMENT `ic` ON `ic`.`ISSUEID` = `i`.`ID` WHERE `i`.`JQL` = 'PROJECT = TALH' GROUP BY `i`.`KEY` HAVING `Num. comments` > 2
Not works!
SQL Cloud - Constraints
In this section, you will find relevant information specific to our SQL Cloud implementation.
Database users and permissions
Most database vendors support database users and database permissions. SQL Cloud does not support that kind of users or permissions.
In SQL Cloud all the Jira users become database users automatically. There are no database permissions on specific database objects like tables or schemas. Everybody can read all the tables. This does not mean that everybody can read all the data as it will be explained in further sections. In fact, SQL Cloud supports permissions in a much more exact and restrictive way than the rest of the vendors.
Dynamic table data
In SQL Cloud all the tables are empty and they are populated in real-time to answer the users' queries.
There are two great reasons for that:
Performance and reliability
What would happen if a user performed this simple query on a Jira instance with millions of issues?
SELECT * FROM ISSUE
Two things might happen and none of them is good.
a) The app fetches millions of records from Jira and loads them into a table on an HTML page to display all of them. No doubt, this would crash the users' browser and probably Jira too, or might cause significant performance issues.
b) Alternatively, the results might be displayed with pagination. But this is useless for many users, who want to access all those data at once. I.e: for exporting them into a file. Or whether an aggregation function like SUM is used with the GROU BY clause which would require fetching all the data from Jira, and loading it into the database engine to perform the required calculations.
2) Atlassian’s Jira public REST API
SQL Cloud is a virtual database powered by Atlassian's Jira public REST API, therefore it inherits all the same constraints brought by their API.
I.e: This is the REST API to get issues:
According to that, it is always mandatory to provide a JQL to search for issues (of course you might want to write a JQL able to fetch millions of issues, but this is out of the subject of this discussion at the moment).
So look at what happens when the query is executed in SQL Cloud:
You get a Full scan error! it means that you did not provide enough constraints in the WHERE or JOIN clauses to invoke the underlying Atlassian’s API. The public REST API requires a JQL or an issue ID always.
Those REST API constraints are known as indexes in SQL Cloud. You always must provide the constraints in some way in your SQL query in order to pass them to the underlying Jira REST API, to fetch the data.
In the message error above (red color) the indexes are the JQL or the ID columns. One of them.
Try now:
SELECT * FROM ISSUE WHERE JQL = 'reporter = currentUser()'
and you will see all the issues reported by you.
There are also 3 subtle things here:
a) You can use JQL in SQL to search for issues!
b) SQL Cloud runs in the context of the calling Jira user, therefore the currentUser() is always resolved correctly.
This is the reason due SQL Cloud honors the Jira permissions always. It’s because it runs in the user’s browser sharing the same Jira user’s HTTP session, created after the user has logged in to Jira!
c) Permissions are applied at the record level instead of the table level. Doesn't matter that all Jira users can read all the tables, as all of them are empty! And they are populated with data fetched via the REST API (honoring Jira permissions) in real-time. As every user owns their unique SQL Cloud database instance, every single user can load and see only authorized data, completely isolated from the rest of the users. So, data leaks/privacy threats among users are not possible!
The Query Builder: your best friend
Even if you are a veteran SQL user, the constraints explained above, may put you in frustration. So we have developed a built-in Query Builder: a non-code assistant that will help you to make working queries under any circumstance. It will also help you to discover the database model (tables) with clicks only. After, you can refine them from the Query Console and re-engineer them by changing column order, aliases, adding functions to transform data, and so on. But is easier always to get a working SQL query as a starting point for the advanced.