Versions Compared

Old Version 40

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.

This section is not intended to be a course of the SQL language. Therefore, 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
select * from PROJECT

will work.

Whereas

Code Block
languagesql
select * from project

will not.

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
titleRead more...

We use the following convention for the table names:

  • No prefix: Jira WorkCore

  • SF_ prefix: Jira Software

  • SM_ prefix: Jira Service ManagerManagement

Reckless queries (AKA Full scans) are not allowed

...

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:

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.

...

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!