Versions Compared

Old Version 17

changes.mady.by.user Pablo Beltran

Saved on

compared with

New Version Current

changes.mady.by.user Pablo Beltran

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 of the SQL language. HenceTherefore, the focus will be put on the minimal set of differences that you must know in order to write queries properly.

A pure JavaScript database

SQL Cloud is a 100% JavaScript database running on the browser (Chrome, Safari, Edge, Firefox…) and any device (PC, tablet, mobile..).

It is read-only at the moment (no INSERT, UPDATE, DELETE).

The engine and the full data model (tables, indexes, etc.) are created when the HTML page is opened and everything is destroyed when the page is closed.

All the users run their own SQL Cloud database instance fully isolated from the rest.

All tables have no data and they are populated in real-time fetching data over the Atlassian’s Jira public REST API to answer the users' queries.

Show tables and columns

Explore the data model with these commands:

  • show tables

  • show columns from <table>

Expand
titleExample

Code Block
languagesql
show tables

Run the statement above to list all the tables available in the database model

Image Added

And

Code Block
languagesql
show columns from <table>

to display the column and their metadata of a given table:

Image Added

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.

Show tables and columns

Run

Code Block
languagesql
show tables

to list all the tables available in the database model

...

Code Block
languagesql
show columns from <table>

to display the column and their metadata of a given table:

...

Expand
titleExample
Code Block
languagesql
SELECT
 'This is a string' as `Column Alias` 
 FROM 
  DUAL `Table alias`

...

Image Added

Table, column and function names are case sensitive

...

  • Use Uppercase names

Expand
titleExample

Code Block
languagesql
select * from PROJECT

will work.

Whereas

Code Block
languagesql
select * from project

will not.

Image Modified

...

KEY is a reserved word

As other database vendors, SQL Cloud does have reserved words that must be surrounded with accents or square brackets

Expand
titleExample
Code Block
languagesql
SELECT `KEY` FROM PROJECT
Code Block
languagesql
SELECT [KEY] FROM PROJECT

No schema support

Many database vendors group their tables into schemas (i.e.: PUBLIC). SQL Cloud does not support themschemes.

Expand
titleRead more...

We use the following convention for the table names:

  • No prefix: Jira

...

  • Core

  • SF_ prefix: Jira Software

  • SM_ prefix: Jira Service

...

  • Management

Reckless queries (AKA Full scans) are not allowed

Many times, users write reckless queries that requite to scan/fetch all the records on a table to be answered.

Expand
titleRead more...

This can cause series reliability problems and compromise the Jira server instance performance.

A clear example is:

Code Block
languagesql
SELECT * FROM ISSUE

What if there are millions of records/issues?

Therefore many queries performing full scans on tables are aborted by the SQL Cloud engine by raising a Full Scan error:

Image Added

Since SQL Cloud is a wrapper for the Atlassian’s Jira public REST API, it relies on the underlaying Atlassian’s API for that. In other words, if the Jira REST API allows querying for all the results (i.e.: PROJECTs), then SQL Cloud will allow it too. Otherwise (i.e.: ISSUEs) not. In this case the indexed column names are displayed (JQL and ID in the example above).

An indexed column in SQL Cloud corresponds to a filter parameter required by the Atlassian’s Jira public REST API to fetch data from Jira. For instance, you can search for issues in Jira by ID or by JQL query.

In general, the SQL Cloud is an straightforward implementation of the Jira REST API and inherits the same security and the rest of the constraints.

WHERE conditions are not the same as JOIN conditions

In SQL Cloud there is a big difference about how values are provided for dynamic table resolution (fetching data from Jira to populate them).

Understating this is very important to write working queries that perform optimally.

Expand
titleRead more...

These are the rules for conditions resolution:

  1. The table in FROM clause will get the conditions from the WHERE clause directly

  2. The rest of the tables will take them from the JOIN ON conditions only.

In other words: (order of execution)

  1. WHERE conditions will be applied only to the table only (FROM) at the beginning of the query resolution.

  2. Next, JOINs will be resolved taking only ON conditions into consideration (WHERE conditions will be ignored at this stage)

  3. The rest of the WHERE conditions will be applied after all the JOINS have been resolved

NOTE: A lot of database vendors work in a different way as they support extra query optimizations based on indexes, etc. They analyze the query syntax and some optimizations are applied in a sort of query internal re-writing before the execution starts. Thus, the users do not know the exact way how their queries will be really resolved and many of those database vendors provide an EXPLAIN command to show the real execution order to the user.

In SQL Cloud, queries are executed in the exact order described above. It is for that reason that is very important to understand the right execution order of all the conditions (WHERE and JOINS). This might result confusing for a lot of users used to popular databases supporting those internal re-writing optimizations and expecting the same behavior here. In fact, the SQL specs are agnostic in regard to how the engine will resolve the query since it is supposed that tables have records which is not true in SQL Cloud as all the bales are empty an populated in real time to answer the users' queries. Therefor users must instruct the engine about the right order to populate the tables, via the SQL query syntax.

Aggregation conditions do not support aliases

Expand
titleExample

Please pay attention to the having condition: count(*) > 2

Code Block
languagesql
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

Code Block
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:

  1. Performance and reliability

What would happen if a user performed this simple query on a Jira instance with millions of issues?

Code Block
languagesql
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:

https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-search/#api-rest-api-3-search-post

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:

Code Block
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!

...

!

...

The Query Builder: your best friend

...