SQL Cloud - Dialect (Must read)

Owned by Pablo Beltran
Last updated: Dec 03, 2022
5 min read

 

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>

 

show tables

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

show tables

 

And

show columns from <table>

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

show columns from ISSUE

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`

Table, column and function names are case sensitive

  • Use Uppercase names

 

will work.

Whereas

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

 

No schema support

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

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.

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.

Aggregation conditions do not support aliases

 

Â