Writing queries and using the features of AlaSQL

AlaSQL support most normal SQL queries, including the creation of tables and insertion of data. As this is in memory you need to ensure you keep a copy of this data and execute it during the startup of the plugin using the settings or store the information in local storage.

The plugin already has a database in local storage called qatt that has the event log as well as the reference calendar for your convenience. See Adding tables to the local storage database for more details.

When writing queries you can use the inbuilt tables documented on Data Tables to search for data. These are all based on your notes and metadata in them.

Adding tables to the local storage database

To add tables and data to the local storage database so you can access it between loads of Obsidian you can use the following SQL commands as an example

CREATE TABLE IF NOT EXISTS qatt.MyCoolFacts (created DATETIME, fact STRING, source STRING)
INSERT INTO qatt.MyCoolFacts VALUES ( '2023-08-03', 'The Query All The Things plugin for Obsidian allows you to store information in tables for later use!', 'Query All The Things Wiki' )

Quirky things about AlaSQL

Ref: Quirky · AlaSQL/alasql Wiki (github.com)

Column names

You can use

[column]

or

`column`

for columns with spaces, special symbols, etc. This also applies to column named with AlaSQL Reserved Keywords independent of the letter case. So names like date, begin, content, last, max, min, count, natural, path, right, show, temp, timeout, target, top, value, and work needs to be wrapped.

Escaping characters

AlaSQL parses queries as JavaScript strings before parsing them as SQL. Because of this, you need to double escape special characters such as double quotes using two backslashes instead of one. For example:

alasql('SELECT "You should \\"double escape\\" double quotes"')

AlaSQL Reserved Keywords

Ref: AlaSQL Keywords · AlaSQL/alasql Wiki (github.com)

Please, use square brackets [name] or back-quotes `name` if you want to use these words for columns and variables. All AlaSQL keywords are case-insensitive.