Exolynk

SQL Queries

Exolynk SQL Queries

At multiple places within the Exolynk platform, it's possible to define custom SqlQuries to receive data from the database. This can either be archieved by the exo::SqlQuery object within scripting or string based queries.

Fetching of Data

Each record type (model) is stored within it's own database table within Exolynk. With the following query you can receive all file records from the db, like a normal sql query would.

  • SQL: SELECT * FROM file;
  • SqlQuery: exo::SqlQuery::new().model("file");

Exolynk allows to query records from multiple tables at once. This can be archieved, by naming the different models/tables directly.

  • SQL: SELECT * FROM file, role, user;
  • SqlQuery: exo::SqlQuery::new().model("file").model("role).model("user");

It's sometimes requiered to query over all existing records within one query, Exolynk allows this scenario with the following queries.

  • SQL: SELECT * FROM *;
  • SqlQuery: exo::SqlQuery::new();

Often only specific data is needed from the database and not the complete records. This scenario can archived by naming the variables, which should be received, like on normal sql.

  • SQL: SELECT ident, name FROM *;
  • SqlQuery: exo::SqlQuery::new().column("ident").column("name");

Conditions

It's possible to filter the SqlQuery directly down towards the needed records based upon conditions. Conditions are only allows to be chained by the AND keyword in Exolynk. Furthermore all data can be matched/checked against a string. For datatypes like Lists, Tables, etc. it's checked if one of their data enties fullfill the search requirement. The column/variable name always needs to be mentioned first.

  • SQL: SELECT * FROM * WHERE number >= 10 AND active == true;
  • SqlQuery: exo::SqlQuery::new().check("number", ">=", 10).check("active", "==", true);

Sometimes it's required to have a condition which checks against multiple possible values. This can be archieved with an array comparrission like this.

  • SQL: SELECT * FROM * WHERE status == any(["new", "old"]);
  • SqlQuery: exo::SqlQuery::new().check("status", "==", ["new", "old"]);

The following comparrsion operators are supported by Exolynk:

  • <= Smaller or Equal
  • < Smaller
  • >= Bigger or Equal
  • > Bigger
  • like Like (with % for placeholder), ignores upper/lower case
  • not like Not Like (with % for placeholder), ignores upper/lower case
  • == Equal

Aggregations

Like normal sql Exolynk supports aggregations directly wihtin the database. When a query use aggregations, it need to be applied for all columns. To count the number of individual records by it's uuid the following.

  • SQL: SELECT count(uuid) FROM *;
  • SqlQuery: exo::SqlQuery::new().aggregate("uuid", "count");

The following aggregations are supported by Exolynk:

  • count Count the number of different values
  • sum Sum the values of a column
  • avg Calculate the average of the column
  • min Get the min value of the column
  • max Get the may value of the column

Performance

To ensure the best performance possible, only query the data which is really needed for your specific task. This means:

  1. Specific exactly which tables to query from when possible
  2. Only receive the specific variables you need for the following task
  3. Filter the data from the database via the where conditions
  4. Aggregate numbers directyl withint he SqlQuery when possible

Security

The Exolynk SqlQuery only supports to retrieve data via the SELECT statement. Data manipulation or other common sql use-cases are not supported for security reasons. A normal SqlQuery can only find ans receive data for which the actual logged in user has the access for. When a user has no access for a specific variable, this variable will not be fetched from the database. Within services it's possible to access all available data, by gaining all previligies via the exo::db::set_sudo(true); command. Please use this with caution and protect your service against data leaks.

← Values
General →