Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 36 Next »

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.

Show tables and columns

  • show tables

  • show columns from <table>

 Example

show tables

Run the statement above 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:

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.

 Example
SELECT
 'This is a string' as `Column Alias` 
 FROM 
  DUAL `Table alias`

Table, column and function names are case sensitive

  • Use Uppercase names

 Example

select * from PROJECT

will work.

Whereas

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

 Example
SELECT `KEY` FROM PROJECT
SELECT [KEY] FROM PROJECT

No schema support

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

 Read more...

We use the following convention for the table names:

  • No prefix: Jira Work

  • SF_ prefix: Jira Software

  • SM_ prefix: Jira Service Manager

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.

 Read more...

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

A clear example is:

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.

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).

This is very important to write working queries that perform in the right way.

 Read 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 here at this stage)

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

Aggregation conditions do not support aliases

 Example

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!

  • No labels